1.DQL:
<1>语法:
/*
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
*/
(1)基本查询:
-
查询多个字段
SELECT 字段1,字段2,字段3……FROM 表名;
SELECT * FROM 表名;
-
设置别名:
SELECT 字段1[AS 别名1],字段2[AS 别名2] …… FROM 表名;
-
去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
(2)条件查询:
-
语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-
条件:
(3)聚合函数:
-
将一列数据作为一个整体,进行纵向计算;
-
常用聚合函数:
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 -
注意:所有的null值不参与聚合函数运算
-
语法:
SELECT 聚合函数(字段列表)FROM 表名;
(4)分组查询:
-
SELECT 字段列表 FROM 表名 [WHERE 条件]GROUP BY 分组字段名 [HAVING 分组后过滤条件]
-
having和where区别:
-
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤;
-
判断条件不同:where不能对聚合函数进行判断,而having可以;
-
-
注意:
-
执行顺序:where>聚合函数>having;
-
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义;
-
(5)排序查询:
-
语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2,排序方式2;
-
排序方式:
-
desc:降序;
-
asc:升序(默认值)
-
-
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序;
(6)分页查询:
-
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
-
注意:
-
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数;
-
分页查询是数据库的方言,不同的数据库有不同的实现,mysql中是limit;
-
如果查询的是第一页数据,起始索引可以省略,直接简写为:limit 10;
-
例:查询第二页员工数据,每页展示10条记录;
-
select * from emp limit 10,10;
-
(7)执行顺序:
from>where>group by>having>select >order by>limit
2.DCL:
(1)管理用户:
-
查询用户:
-
USE mysql; SELECT * FROM user;
-
-
创建用户:
-
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-
-
修改用户密码:
-
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
-
-
删除用户:
-
DROP USER '用户名'@'主机名';
-
-
注意:
-
主机名可以使用%通配;
-
(2)权限控制:
-
查询权限:
-
SHOW GRANTS FOR'用户名'@'主机名';
-
-
授予权限:
-
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-
-
撤销权限:
-
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-
-
注意:
-
多个权限之间,使用逗号分隔;
-
授权时,数据库名和表名可以使用*进行通配,代表所有;
-
3.函数:
-
函数:指一段可以直接被另一端=段程序调用的程序或代码;
-
select 函数(参数);
(1)字符串函数
-
函数 功能 concat(s1,s2,……Sn) 字符串拼接,将s1,s2,……Sn拼接成一个字符串 lower(str) 将字符串str全部转为小写 upper(str) 将字符串str全部转换为大写 lpad(str,n,pad) 左填充,用字符串pad对str左边进行填充,达到n个字符串长度 rpad(str,n,pad) 右填充,用字符串pad对str右边进行填充,达到n个字符串长度 trim(str) 去掉字符串头部和尾部的空格 substring(str,start,len) 返回从字符串str从start位置起的len个长度的字符串 -
例:select concat('hello','mysql');
-
-- 由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部前面补0; -- 例:1号员工的工会应该是00001
update emp set sno=lpad(sno,5,'0');
(2)数值函数
-
函数 功能 ceil(x) 向上取整 floor(x) 向下取整 mod(x,y) 返回x/y的模 rand() 返回0~1的随机数 round(x,y) 求参数x的四舍五入的值,保留y位小数 -
例:通过数据库函数,生成一个六位数的随机验证码。
select lpad(round(rand()*1000000,0),6,'0');
(3)日期函数
-
函数 功能 day(date) 获取指定date的日期 curdate() 返回当前日期 curtime() 返回当前时间 now 返回当前日期和时间 year(date) 获取指定date的年份 month(date) 获取指定date的月份 date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值 datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数 -
例:查询所有员工的入职天数,并根据入职天数倒序排序;
select name,datediff(curdate(),entrydate()) from emp order by entrydays desc;
(4)流程函数
-
函数 功能 if(value,t,f) 如果value为true,则返回t,否则返回f ifnull(value1,value2) 如果value1不为空,返回value1,否则返回value2 case when [val1] then [res1] …… else[defalut] end 如果val1为true,则返回res1,……否则返回default默认值 case [expr] when[val1] then[res1] …… else[defalut] end 如果expr的值等于val1,返回res1,……否则返回default默认值 -
案例:统计班级各个学员的成绩,展示规则如下:
-- >=85优秀; >=60 及格;否则:不及格
select
case when score>=85 then '优秀' when score>=60 then '及格' else '不及格' end,
from score
4.约束:
(1)概念:
约束是作用于表中字段上的规则,用于限制存储在表中的数据;
-
目的:保证数据库中数据的正确性、有效性和完整性;
(2)常用约束:
-
注意:约束是作用于表中字段上的,可以创建表/修改表的时候添加约束;
-
例:
(3)外键约束:
-
添加外键:
-
第一种情况:
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.多表查询:
(1)多表关系
-
分类:
-
一对一
-
案例:用户与用户详情的关系
-
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率;
-
实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(unique)
-
-
多对多
-
案例:学生和课程的关系
-
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
-
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键;
-
-
一对多(多对一)
-
案例:部门与员工的关系
-
关系:一个部门对应多个员工,一个员工对应一个部门;
-
实现:在多的一方建立外键,指向一的一方的主键;
-
-
(2)多表查询概述
-
概述:指从多张表中查询数据;
-
笛卡尔积:两个集合A和集合B的所有组合情况(在多表查询中,需要消除笛卡尔积);
-
分类:
-
连接查询:
-
内连接:相当于A、B交集部分数据
-
外连接:
-
左外连接:查询坐标所有数据,以及两张表交集部分数据;
-
右外连接:查询右表所有数据,以及两张表交集部分数据;
-
-
自然连接:当前表与自身的连接查询,自连接必须使用表别名;
-
-
子查询:
-
(3)内连接
-
查询语法:
-
隐式内连接:
-
select 字段列表 from 表1,表2 where 条件……;
-
-
显示内连接:
-
select 字段列表 from 表1 [inner]join 表2 on 连接条件……;
-
-
(4)外连接
-
语法:
-
左外连接:
select 字段列表 from 表1 left[outer]join 表2 on 条件……;
-
相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据;
-
-
右外连接:
select 字段列表 from 表1 right[outer]join 表2 on 条件……;
-
(5)自连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件……;
-
自连接查询,可以是内连接查询,也可以是外连接查询;
(6)联合查询(union,union all)
-
对于union查询,就是把多次查询的结果合并起来,行成一个新的查询结果集;
-
select 字段列表 from 表A…… union[all] select 字段列表 from 表B……;
-
例:将emp表中薪资低于5000的员工,和年龄大于50岁的员工全部查询出来;
select * from emp where salary<5000 union select * from emp where age>50;
-
对于联合查询的多张表的列数必须保持一致,子弹类型也需要保持一致;
-
union会对合并的数据进行去重,而union all是会将全部数据直接合并在一起;
(7)子查询
-
概念:sql语句中嵌套select语句,称为嵌套查询,又称为子查询;
select * from t1 where column1=(select column1 from t2);
-
子查询外部语句可以是insert/update/delete/select的任何一个;
-
根据子查询结果不同,分类为:
-
标量查询(子查询结果为单个值):
-
常用操作符:=,<>,>,>=,<,<=
-
-
列子查询(子查询结果为一列):
-
常用操作符:
操作符 描述 in 在指定的集合范围之内,多选一 not in 不在指定的集合范围内 any 子查询返回列表中,有任意一个满足即可 some 与any等同,使用some的地方都可以使用any all 子查询返回列表的所有值都必须满足
-
-
行查询(子查询结果为一行):
-
常用操作符:=,<>,in,not in
-
-
表子查询(子查询结果为多行多列):
-
常用操作符:in
-
-
-
根据子查询位置,分为:where之后、from之后、select之后.
6.事务:
(1)概念:
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败;
-
注意:默认mysql事务是自动提交的,也就是说,当执行一条DML语句,mysql会立即隐式的提交事务。
(2)操作:
<1>方式一:
-
查看/设置事务提交方式:
-
select @@autocommit;-- 查看事务提交方式:1自动,0:手动; set @@autocommit=0;
-
-
提交事务:
-
commit;
-
-
回滚事务:(出现异常时使用)
-
rollback;
-
-
例:
-- 转账操作(zs给ls转1000
select @@autocommit;
set @@autocommit=0;
-- 查询zs账户的余额、
SELECT * FROM account WHERE name='zs';
-- 将zs账户余额-1000
update account set money=money-1000 WHERE name='zs';
程序出错...
-- 将ls账户余额+1000
update account set money=money+1000 WHERE name='ls';
-- 提交事务
commit;
-- 回滚事务
rollback;
<2>方式二:
-
-- 开启事务: start transaction 或 begin; -- 提交事务: commit; -- 回滚事务:(出现异常时使用): rollback;
例:
-
start transaction; -- 查询zs账户的余额、 SELECT * FROM account WHERE name='zs'; -- 将zs账户余额-1000 update account set money=money-1000 WHERE name='zs'; 程序出错... -- 将ls账户余额+1000 update account set money=money+1000 WHERE name='ls'; -- 提交事务 commit; -- 回滚事务 rollback;
(3)事务四大特性(ACID):
-
原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败;
-
一致性(Consistency):事务完成时,必须使索引的数据保持一致状态;
-
隔离性(lsolation):数据库系统提供隔离机制,保证事务在不受外部并发操作影响的独立环境下运行;
-
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的;
(4)并发事务问题:
问题 | 描述 |
---|---|
脏读 | 一个事务读到另一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。 |
幻读 | 一个事务按照条件查询数据是,没有对应的数据行,但在插入数据是,有发现这行数据已经存在,好像出现了“幻影”; |
(5)事务的隔离级别:
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别 session(当前会话级别) global(所有客户端会话窗口有效)
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};
-
注意:事务隔离级别越高,数据越安全,但是性能越低;