Database system learning | Anthony2004

type

status

date

slug

summary

1.基础知识

数据库(Database DB):存储数据的仓库，有组织的进行存储

数据库管理系统(Database management system DBMS):操作和管理数据库的集成软件

SQL(structed query language):一种编程语言，用来管理数据库

server———>DBMS———>DB———>tables

2.SQL通用语法(DDL,DML,DQL,DCL)

DDL(data definition language)

数据库操作

这一类为定义类的定义数据库，表，字段等等。

数据库查询

查询数据库 show databases;

查询当前数据库 select database();

数据库创建

create database [if not exist] 数据库名 [字符集][排序规则];

数据库删除

drop database[if exist] 数据库名;

数据库使用

use 数据库名；

表操作

表查询

查询当前数据库内所有的表 show tables;

查询表结构 desc 表名；

查询指定表的建表语句 show create table 表名；

表的创建

create table 表名(

age int,

name varchar(50),

gender varchar(1)

);

表的修改

添加字段 alter table 表名 add 字段名类型;

修改数据类型 alter table 表名 modify 字段名新的数据类型;

修改字段名和字段类型 alter table 表名 change 旧的字段名新的字段名新的类型;

删除字段 alter table 表名 drop 字段名;

修改表名 alter table 表名 rename to 新的表名;

删除表 drop table[if exist] 表名;

删除指定表，并重新创建该表(注意：只是将表内的数据清空，但是字段结构还保留) truncate table 表名

DML(data manipulation language)

数据操作语言，对数据进行修改，关键词 insert update delete

添加数据

给指定字段添加数据 insert into 表名(字段1,字段2,…) values(值1,值2,…);

给全部字段添加数据 insert into values(值1,值2,…);

批量添加，与上述语法一致（注意一一对应），类：insert into 表名(字段1,字段2,…) values(值1,值2,…)，(值1,值2,…)，(值1,值2,…)…;

修改数据

更新数据 update 表名 set 字段1=值1，字段2=值2，…[where 条件]；where用于告诉计算机哪些数据需要修改，如果不填写where则全部字段的全部数据都修改。

删除数据 delete from 表名 [where]; 注意区别，该方法只能删除一行一行的数据，不能删除单个字段的数据，如需删除单个字段的数据，采用上一行的语句将数据update为0或null

DQL(data query language)

用来查询数据的语言，关键词：select

语法结构（编写顺序）

select 字段列表

from 表名列表

where 条件列表

group by 分组字段列表，分组通常会配合聚合函数（count,max,min,avg,sum）

having 分组后条件列表

order by 排序字段列表

limit 分页参数

基本查询

查询多个字段 select 字段1,字段2，… from 表名;

可以设置别名(增强字段的可读性) select 字段1[as 别名1],字段2[as 别名2]，… from 表名;

查询全部字段 select * from 表名;

去除重复记录（比如某个字段有很多相同的值，此时只返回一个） select distinct 字段列表 from 表名；

查询

需要注意的几个条件为： between … and … 在左值与右值之间（左大右小，包含最大最小值）

in(…) 在括号内的多个值中匹配一个，多选一

like 占位符模糊匹配，例如，like ‘_’匹配单个字符，like ’%’匹配任意个字符，like ’%X’匹配最后一个字符为X

is null 是null，为空

逻辑运算符与c语言类似：and或&&，or或||，not或！

<>或！= 不等于

聚合函数（都是作用于表中的某一列数据，也就是一个字段的所有数据,使用聚合函数时，所有的null值是不参与计算的）： count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和使用如下： select count(*) from emp；

select count(age) from emp;

select avg(age) from emp;

条件查询 select 字段列表 from 表名 where 条件列表;

分组查询 select 字段列表 from [where 条件] group by 分组字段名 [having 分组过滤条件]； 注意：where和having都是用于过滤，但是时机不同，where是分组前过滤，having是分组后对结果进行过滤；where不能对聚合函数进行判断，having可以。例如，要实现查询年龄小于45岁的员工，并根据工作城市分组，获取员工数量大于等于三的工作地址： select workaddress , count(*) from emp where age<45 group by workaddress having count() >= 3;下面的使用的是别名，address_count,效果相同 select workaddress , count(*) address_count from emp where age<45 group by workaddress having address_conut >= 3; 以上是根据工作城市查询的，如果加上于分组不相关的字段，无任何意义

排序查询 select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2…; 只有两种排序：asc(升序默认值)，desc(降序) 多字段排序规则：当字段1排序相同时，再按照字段2进行排序，…

分页查询 select 字段列表 from 表名 limit 起始索引，查询记录数； 起始索引从0开始，起始索引等于（查询页码数-1）*每页显示记录数；分页查询是数据库的方言，不同数据库不同，MySQL中是limit 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

执行顺序：1，from 2，where 3，group by 4，select 5，order by 6，limit 7，having

DCL(data control language)

数据控制语言，用来管理数据库用户，控制数据库的访问权限

一个用户权限由host和user共同决定

管理用户（该部分主要为数据库管理员使用）

查询用户 use mysql； select * from user；

创建用户 create user ‘用户名’@‘主机名’ identified by ‘密码’； ‘主机名’为localhost指本地主机，不能远程访问，为%指任意主机访问

修改用密码 alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码‘；

删除用户 drop user ‘用户名’@’主机名‘；

权限控制

权限列表

权限	说明
al , all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

查询权限 show grants for ‘用户名‘@’主机名‘;

授予权限 grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;

撤销权限 revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’；

3.函数

是一段可以被直接调用的程序或代码

字符串函数

函数	功能
concat（s1,s2,…sn）	字符串拼接
lower(str)	将字符串全部转为小写
upper(str)	将字符串全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串首尾的空格
substring(str,start,len)	字符串截取，从start起，截取len个，str的首字母序号为1

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y为小数

日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程函数

函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1,value2)	如果value不为空，返回value1，否则返回value2
case when[val1] then[res1]…else[default] end	如果val1为true，返回res1，…,否则返回default默认值
case[expr] when[val1] then[res1]…else[default] end	如果expr的值等于val1，返回res1，…，否则返回default默认值

4.约束

作用于字段的一种规则，来限制存储在表中的数据

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一的，不能重复	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据的时候，如果没有指定该字段的值，则使用默认值	default
检查约束	保证字段满足某个条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key

example：

create table user(

id int primary key auto_increment, #主键，并且自动增长

name varchar(10) not null unique, #不为空，并且唯一

age int check (age>0 && age<= 120), #大于0，小于等于120

status char(1) default ‘1’, #如果没有指定的值，默认为1

gender char(1) #无

);

外键约束

外键用来让两张表的数据之间构建连接

具有外键的表为子表；子表所关联的表为父表

添加外键

方法一：在创建表的时候直接添加

create table …(

…

[constraint] [外键名] foreign key(外键字段名) references 主表（主表列名）

)

方法二：表创建好后再添加一个外键

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)； 表名：子表

删除外键

alter table 表名 drop foreign key 外键名称;

删除/更新行为 alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名（主表字段名） on update cascade on delete cascade;

5.多表查询

多表关系

一对多/多对一

外键约束可以实现

多对多

构建第三张中间表，中间表至少包含两个外键，分别关联两方主键