数据库基础
概念
数据库：存储数据的仓库
数据库管理系统：操作和管理数据库的软件。
SQL：操作关系型数据库的编程语言，定义了一系列标准。
关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
net start mysql80#启动数据库
net stop mysql80#关闭数据库
SQL语句
DDL：数据定义语言，用来定义数据库对象。
DML：数据操作语言，用来对数据库中的数据进行增删改。
DQL：数据查询语言，用来查询数据库中的表的记录。
DCL：数据控制语言，用来创建数据库用户以及访问权限。
DDL
库操作
查询：
-
查询所有数据库
show databases; -
查询当前数据库
select database();
创建：
-
create database [if not exists]数据库名 [default charset 字符集][collate 排序规则];
删除：
-
drop database [if exists]数据库名;
使用：
-
use 数据库名;
表操作
查询：
-
查询当前数据库所有表
show tables; -
查询表结构
desc 表名; -
查询指定表的建表语句
show create table 表名;
创建：
-
语法
create table 表名( 字段1 字段1类型 [comment字段1注释], 字段2 字段2类型 [comment字段2注释], ...... 字段n 字段n类型 [comment字段n注释] )[comment 表注释]; -
字段类型
数值类型，加unsigned表示无符号。
数值类型 大小 描述 tinyint 1byte 小整数值 smallint 2bytes 大整数值 mediumint 3bytes 大整数值 int/integer 4bytes 大整数值 bigint 8bytes 极大整数值 float 4bytes 单精度浮点数值 double 8bytes 双精度浮点数值 decimal MD(精度和标度) 小数值(精确定点数) 字符串类型
字符串类型 大小 描述 char（定长-性能好） 0-255bytes 定长字符串 varchar（变长-性能差） 0-65535bytes 变长字符串 tinyblob 0-255bytes 不超过255个字符的二进制数据 tinytext 0-255bytes 短文本字符串 blob 0-65535bytes 二进制形式的长文本字符串 text 0-65535bytes 长文本字符串 mediumblob 0-16777215bytes 二进制形式的中等长度文本数据 mediumtext 0-16777215bytes 中等长度文本数据 longblob 0-4294967295bytes 二进制形式的极大文本数据 longtext 0-4294967295bytes 极大文本数据 日期类型
事件类型 大小 格式 描述 data 3 YYYY-MM-DD 日期值 time 3 HH:MM:SS 时间值或持续时间 year 1 YYYY 年份值 datetime 8 YYYY-MM-DD HH:MM:SS 混合日期和时间值 timestamp 4 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳 #例子 create table emp( id int, workno varchar(10), name varchar(10) comment '姓名', gender char(1), age tinyint unsigned comment '年龄', idcard char(18), entrydate date )comment '员工表';
修改：
-
添加字段
alter table 表名 add 字段名 类型(长度)[comment 注释][约束]; -
修改字段
#修改数据类型 alter table 表名 modify 字段名 新数据类型(长度); #修改字段名和字段类型 alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束]： -
删除字段
alter table 表名 drop 字段名： -
修改表名
alter table 表名 rename to 新表名：
删除：
-
删除表
drop table[if exists]表名： -
删除指定表并重新创建该表
#为了删除其中数据 truncate table 表名;
DML
增加数据(INSERT)，字符串和日期类型应该用引号引起来。
-
给指定字段增加数据
insert into 表名 (字段名1，字段名2,...)values(值1,值2...); -
给全部字段增加数据
insert into 表名 values(值1,值2...); -
批量增加数据
#部分字段 insert into 表名(字段名1,字段名2,...)values(值1,值2,...),(值1,值2,...),(值1,值2,...); #全部字段 insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
修改数据(UPDATE)
-
修改数据
update 表名 set 字段名1=值1,字段名2=值2,...，[where 条件];
删除数据(DELETE)
-
删除数据
delete from 表名 [where 条件];
DQL
查询语句
-
select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数
条件查询
-
语法
select 字段列表 from 表名 where 条件列表; -
条件
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <>或!= 不等于 between…and… 在某个范围之间 in(…) 在列表中 like 占位符 模糊匹配(_一个字符,%任意个字符) is null 是null and或&& 且 or或|| 或 not或! 非
聚合函数
-
介绍：将一列数据作为一个整体，进行纵向计算，null不进行聚合运算。
-
语法
select 聚合函数(字段列表) from 表名 -
常见聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和
分组查询
-
语法
select 字段列表 from 表名 [where 条件]group by 分组字段名[having 分组后过滤条件]; -
where和having区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
-
注意
- where>聚合函数>having；
- 分组之后，一般查询聚合函数和分组字段，其他字段无意义；
排序查询
-
语法
#当第一个排序相同后才会进行第二个排序 select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2; -
排序方式
ASC：升序（默认值）。
DESC：降序。
分页查询
-
语法
select 字段列表 from 表名 limit 起始索引，查询记录数; -
注意
- 起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
- 分页查询是数据库的方言，不同数据库不同实现。
- 如果查询第一页数据，起始索引可以省略，直接写limit 10。
例子
-
查询年龄为20，21，22，23的女员工信息。
select * from where age in (20,21,22,23) and gender='女'; -
查询性别为男，年龄在20-40的姓名为三个字的员工。
select * from emp where gender='男' and age beteen 20 ans 40 and name like '___'; -
统计员工表中，年龄小于60的男员工和女员工的人数。
select gender,count(*) as '数量' where age<60 group by gender; -
查询所有年龄小于35的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同则按入职时间降序排序。
select name,age from emp where age<35 order by age asc,entrydata desc; -
查询性别为男，年龄在20-40以内的前五个员工信息，并对结果按照年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender='男' and age between 20 and 40 order by asc,entrydata asc limit 5;
执行顺序
-
from
- where
- group by
- select
- order by
- limit
DCL
用户控制
-
查询用户
use mysql; select * from user; -
创建用户
create user '用户名'@'主机名' identified by '密码'; #主机名为localhost为本机 #主机名为%为任意机器 -
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码' -
删除用户
drop user '用户名'@'主机名';
权限控制
| 权限 | 说明 |
|---|---|
| ALL，ALL PRIVIEGES | 所有权限 |
| SELECT | 查询权限 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除库/表/视图 |
| CREATE | 创建数据库/表 |
-
查询权限
show grants for '主机名'@'用户名' -
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
函数
函数是指一段可以直接被另一程序直接调用的程序和代码。
字符串函数
| 函数 | 功能 |
|---|---|
| concat(S1,S2,…SN) | 将S1-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) | 返回str从start起len个长度的字符串 |
数值函数
| 函数 | 功能 |
|---|---|
| ceil(x) | 向上取整 |
| floor(x) | 向下取整 |
| mod(x,y) | 返回x/y的模 |
| rand() | 返回0-1随机数 |
| round(x,y) | 求x的四舍五入值，保留y位小数 |
#生成六位数随机验证码
select lpad(round(rand(),6)*1000000,6,0);
日期函数
| 函数 | 功能 |
|---|---|
| curdate() | 返回当前日期 |
| curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 |
| year(date) | 获取date的年 |
| month(data) | 获取date的月 |
| day(date) | 获取date的日 |
| date_add(date,interval expr type) | 返回一个日期/时间加上一个时间间隔expr后的时间值 |
| datediff(date1,date2) | 返回date1到date2之间的天数，date1减去date2的时间 |
流程控制函数
| 函数 | 功能 |
|---|---|
| if(value,t,f) | 如果value为true，返回t，否则返回f |
| ifnull(t,f) | 如果t不为空，则返回t，否则返回f |
| 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默认值 |
#查询emp的员工姓名和工作地址(北京/上海--->一线程序，其他--->二线城市)
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)as '城市级别'
from emp;
#统计班级学院成绩，>=85 优秀 >=60 及格 否则不及格
select
id,
name,
(case when math>=85 then '及格' when math>60 then '及格' else '不及格' end) as '数学',
(case when english>=85 then '及格' when english>60 then '及格' else '不及格' end) as '英语',
(case when chiness>=85 then '及格' when chiness>60 then '及格' else '不及格' end) as'语文'
from score;
约束
概念：约束时作用于表中字段的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确，有效性和完整性。
分类：
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段不能为null | not null |
| 唯一约束 | 保证该字段的所有数据都是唯一，不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 如果未指定该字段的值，则采用默认值 | default |
| 检查约束 | 保证字段值满足某一个条件 | check |
| 外键约束 | 用来让两张表数据之间建立连接，保证数据一致性和完整性 | foreing key |
| 自动增长 | 让某一个字段进行自动增长 | auto_increment |
例子
create table student(
id int primary key auto_increment,
name varchar(10) not null unique,
age int check (age>0 and age<120),
status char(1) default '1',
gender char(1)
)comment '学生';
外键约束
-
增加外键
#语法一 create table 表名( [constranint][外键名称] foreing (外键字段名) references 主表(主列表名); ) #语法二 alter table 表名 add constraint 外键名称 foreing key(外键字段名) references 主表(主表列名); -
删除外键
alter table 表名 drop foreing key 外键名称; -
删除/更新行为
行为 说明 no action/restrict 父表删除/更新时，如果该记录有对于外键，则不允许删除/更新。 cascade 父表删除/更新时，如果该记录有对于外键，则也删除/更新子表的记录。 set null 父表删除/更新时，如果该记录有对于外键，则子表将其设置为null，必须要求子表允许null。 set default 父表有变更时，子表将外键设置为一个默认值(innodb不支持) alter table 表名 add constraint 外键名称 foreing key(外键字段) references 主表(主表字段名) on update cascade on delete cascade; #update 为更新时 #delete 为删除时
多表查询
多表关系
一对一：任何一张表中增加外键，一般用来进行单表拆分，将一张表的基础字段存放在一张表中，其他详情字段存放在另一张表中，以提高效率。
一对多：在多的表中增加外键，往一的表中进行对应。
多对多：增加中间表，中间表中两个外键，每个外键对应一个表。
多表查询
从多张表中进行查询，进行笛卡尔积得出结果。
连接查询
-
内连接
内连接查询的是两张表相交的部分。如果表名过长，可以起别名，但是起别名后不能使用原表名。
隐式内连接
select 字段列表 from 表1,表2 where 条件...；显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...; #inner可不写 -
外连接
左外连接
查询表1（左表）的数据和表1和表2交集的部分，即使不满足on的条件，但表1的数据一定全部会出现。
select 字段列表 from 表1 left [outer] join 表2 on 条件...;右外连接
查询表2（右表）的数据和表1和表2交集的部分，即使不满足on的条件，但表2的数据一定全部会出现。
select 字段列表 from 表1 right [outer] join 表2 on 条件...; -
自连接
自己和自己进行连接，必须起别名，否则无法进行区分。
select 字段列表 from 表A 别名A join 表A 别名B on 条件...; -
联合查询-union，union all
对于联合查询，就是将多次查询的结果合并起来，形成一个新的查询结果集，all不会去重。
select 字段列表 from 表A ... union [all] select 字段列表 from 表B ...;#查询薪资低于5000 和年龄大于50的员工 select * from emp where money<5000 union all select * from emp where age>50 #查询薪资低于5000 和年龄大于50的员工且去处重复 select * from emp where money<5000 union select * from emp where age>50 #字段列表和类型必须保持一致
子查询
又称嵌套查询，即将select查询的结果再次进行使用。
-
标量子查询（子查询结果为单个值）
即子查询的结果只有一条记录。然后根据这个记录继续进行查询。
#查询销售部的所有员工信息 select * from emp where dept_id = (seect id from dept where name='销售部') -
列子查询（子查询结果为一列）
查询的结果是一列的，这种子查询称为列子查询，由于结果是一列的，则可以将其结果当正常的列使用。
操作符 描述 in 在指定的集合范围之内，多选一 not in 不在指定的集合之内 any 子查询返回列表中，有任意一个满足即可 some 与any等同，使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足 #查询销售部和市场部的所有员工信息 select * from emp where demp_id in( select id from dept where name='销售部' or name='市场部' ); #查询比财务部所有人工资都高的员工信息 select * from emp where salary >all( select salary from emp where dept_id =( select id from dept where name='财务部' ) ); #查询比研发部任意一人工资高的员工信息 select * from emp where salary >any( select salary from emp where dept_id=( select id from demp where name='研发部' ) ); -
行子查询（子查询结果为一行）
子查询返回的结果为一行的，称为行子查询,由于结果是一行的，只能进行简单操作。
如：=，<>，IN，NOT IN等。
#查询与’刘雪倩‘薪资及直属领导相同的员工信息 select * from emp where (salary,managerid)=( select salary,managerid from emp where name='刘雪倩' ); -
表子查询（子查询结果为多行多列）
子查询返回的结果为多行多列，及结果还是一张表。
#查询与’张璇‘，’刘雪倩‘的职位和薪资相同的员工信息 select * from emp where (job,salary)in( select job,salary from emp where name='张璇' or name='刘雪倩' ); #查询入职日期是’2006-01-01‘之后的员工信息及其部门信息 select e.*,d.* from ( select * from emp where entrydate>'2006-01-01' ) e left join dept d on e.dept_id=d.id; 从上述例子可以看出，当作where是表子查询就是简单结合了行子查询和列子查询的信息，当作from即是一张表。
SQL练习
-
查询员工的姓名、年龄、职位、部门信息。
select e.age,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id; -
查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select e.name ,e.age,e.job from emp e join dept d on e.dept=d.id where e.age<30; -
查询拥有员工的部门ID、部门名称。
select distinct d.id,d.name from emp e,dept d where e.dept=d.id; #distinct 去重 -
查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where e.age>40; -
查询所有员工的工资等级。
select e.*,s.grade from emp e,salgrade s where s.salary >=s.losal and e.salary <=s.hissal; -
查询“研发部”所有员工的信息及工资等级。
select e.*,s.grade from emp e ,salgrade s where e.dept=( select id from dept where name ='研发部' ) and s.salary >=s.losal and e.salary <=s.hissal; -
查询“研发部”员工的平均工资。
select avg(emp.salary) from emp,dept where emp.dept=dept.id and dept.name='研发部'; -
查询工资比“张璇”高的员工信息。
select * from emp where salary>( select salary from emp where name='张璇'; ) ; -
查询比平均薪资高的员工信息。
select * from emp where salary >( select avg(salary) from emp ); -
查询低于本部门平均工资的员工信息。
select * from emp e,( select id,avg(salary)as avg_salary from emp,demp where emp.dept_id=dept.id ) b where e.salary<b.avg_salary and e.dept_id =b.id; -
查询所有的部门信息，并统计部门的员工人数。
select d.id,d.name ,(select count(*)from emp e where e.dept_id=d.id)'人数' from dept d;
事务
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
事务操作
-
查看/设置事务提交方式
select @@autocommit;#mysql默认自动 set @@autocommit=0; #为1自动提交，为0手动提交 -
开始事务
当提交方式自动时，默认每次执行完一条sql后提交事务，如果需要自己管理则需要手动开启事务。
当提交方式为手动时，执行完后并不会提交事务，需要手动进行提交，所以无需手动开始事务。
begin; #或者 start transaction; -
提交事务
commit; -
回滚事务
rollback;
事务的四大特性
原则性(A)：事务是不可分隔的最小操作单元，要么全部成功，要么全部失败。
一致性©：事务完成时，必须使所有的数据都保持一致。
隔离性(I)：数据库系统提供的隔离机制，保证事务在不受外界并发操作影响的独立环境中运行。
持久性(D)：事务一旦提交或者回滚，对数据库中的改变是持久的。
事务的并发问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没有提交的数据。 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据，但是在插入数据时，又发现这行数据已经存在，好像出现了幻觉。 |
事物的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 未提交读(read uncommitted) | Y | Y | Y |
| 提交读(read committed) | X | Y | Y |
| 可重复读(repeatable read)(mysql默认) | X | X | Y |
| 可串行化(serializable) | X | X | X |
查看事物的隔离级别
select @@transaction_isolation;
设置事务的隔离级别
set [session|global]transaction isolation level[read uncommitted|read committed | repeatable read|serializable]
#session 表示当前会话
#global 对于所有会话
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
