关键字建议用大写
DDL
数据库操作
- SHOW DATABASES;--》显示存在的数据库
- CREATE DATABASE 数据库表名(not exist);--》创建数据库
- USE 数据库名;--》切换数据库使用数据库
- SELECT DATABASE();--》查看当前属于那个数据库当中
- DROP DATABASE 数据库名称;(if exist)--》删除数据库
表操作
- SHOW TABLE;--》查看表,显示当前存在的表
- CREATE TABLE 表名(字段 字段类型,字段 字段类型);--》创建表
- DESC 表名;--》查看表内字段(desc employee)
- SHOW CREATE TABLE 表名;--》查看这张表的建表字段
- ALTER TABLE 表名 ADD/MODIFY/DROP/RENAME TO/CHANGE…;--》添加字段/修改字段类型/删除字段/修改表名/修改字段名称及类型
- DROP TABLE 表名;--》删除表
图形化工具
DML数据操作语言,对数据记录的增删改查
Insert | Update | Delete |
- INSERT INTO 表名(字段1,字段2,…)VALUE(值1,值2,…);--》给指定字段添加数据
- INSERT INTO表名VALUE(值1,值2,…);--》给全部字段添加数据
- 批量要添加数据
- INSERT INTO表名(字段1,字段2,…)VALUE(值1,值2,…),(值1,值2,…),(值1,值2,…);
- INSERT INTO表名VALUE(值1,值2,…),(值1,值2,…),(值1,值2,…);
- UPDATE 表名 SET 字段名1=值1,字段名2=值2,….[WHERE 条件];//修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
- DELETE FROM 表名 [WHERE 条件];
Delete from employee where gender='女';
//delete语句的条件有无都行,如果没有条件,则会删除整张表的所有数据。delete语句不能删除某一个字段的值(可以使用update)。
DQL数据查询语言
SELECT | FROM | WHERE | GROUP BY | OREDER BY | LIMIT |
- 基础查询
SELECT 字段1,字段2,字段3…FROM 表名;//查询多个字段
SELECT * FROM 表名;//查询多个字段
SELECT 字段1 [AS 别名],字段2 [AS 别名] …FROM 表名;//设置别名 (AS可以省略)
SELECT DISTINCT 字段列表FROM 表名;//去除重复记录(distinct)
- 条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
查询年龄等与18或20或40的员工信息 |
Select * from emp where age=18 or age=20 or age=40; |
Select *from emp where age in (18,20,40); |
查询姓名为两个字的员工信息 |
Select * from emp where name like '--'; |
查询身份证号最后一位是X的员工信息 |
Select * from emp where idcard like '%X'; |
Select * from emp where idcard like '-----------------x'; |
- 聚合函数 NULL不参与聚合函数计算的
Count() | Max () | Min() | Avg() | Sum() |
统计西安地区员工的年龄之和 |
SELECT SUM(AGE) FROM EMP WHERE WORKADDRESS = '西安'; |
- 分组查询
where分组之前过滤 | having分组之后对结果进行过滤 |
不能对聚合函数进行判断 | having可以 |
查询年龄小于45岁的员工,并根据工作地址分组,获取员工数量大于等于三的工作地址 |
Select workaddress,count(*) as address_count from emp where age < 45 group by workaddress having address_count>=3; |
- 排序查询
Desc | asc默认值(可省略) |
根据年龄对公司的员工进行升序排序,年龄相同的情况按照入职时间进行降序排序 |
Select * from emp order by age asc,entrydate desc; |
- 分页查询
SELECT 字段列表 FROM 表名LIMIT 起始索引,查询记录数;
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数。
分页查询是数据库的方言,MySQL的是limit。
如果查询的是第一页数据,起始索引可以省略,直接省略为limit10。
查询第2页员工数据,每页显示10条记录。------->(页码-1)*也展示记录数 |
Select * from emp limit 10,10; |
总结案例
查询年龄20,21,22,23岁的女性员工信息。 |
Select * from emp where gender='女' and age in(20,21,22,23); |
查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工。 |
Select * from emp where gender ='男' and( age between 20 and 40 )and name like='---'; |
统计员工表中,年龄小于60岁的,男性员工和女性员工的人数。 |
Select gender, count(*) from emp where age < 60 group by gender; |
查询所有年龄小于等于35岁的员工的姓名和年龄,并对查询结果按照年龄升序排序,如果年龄相同按照入职时间降序排序。 |
Select name, age from emp where age<=35 order by age asc,entrydate desc; |
查询性别为男,且年龄在二十到四十之间的前五个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间排序。 |
Select *from emp where gender = '男'and age between 20 and 40 order by age asc,entrydate asc limit 5; |
DQL数据控制语言
管理用户
- 查询用户
Use mysql;
SELECT * FROM user;
- 创建用户
CREATE USER '用户名'@'主机名' IDENTFIED BY '密码';
- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTFIED WITH mysql_native_password BY '新密码';
- 删除用户
DROP USER '用户名'@'主机名';
Create user 'itcast'@'localhost' identified by '123456'; | 创建用户itcast,只能够在当前主机localhost访问 |
Create user 'heima'@'%' identified by '123456'; | 创建用户heima,可以在任意主机访问该数据库,密码123456 |
Alter user 'heima'@'%' identified with mysql_native_poassword by '1234'; | 修改用户heima的访问密码为123 |
Drop user 'itcast'@'localhost'; | 删除itcast'@'localhost用户 |
DCL权限控制
- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
- 授权权限
GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
Grants all on itcast.* to 'heima'@'%'; | 授权权限给黑马 |
Revoke all on itcast.* from 'heima'@'%'; | 把黑马的权限撤销 |
Show grants for 'heima'@'%'; | 查询权限 |
函数函数
字符串函数 |
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个长度的字符串 |
数值函数 | |
CEIL | 向上取整 |
FLOOR | 向下取整 |
MOD | 返回X/Y的模(取余) |
RAND | 返回0~1内的整数,返回随机数 |
ROUND | 四舍五 |
案例
Select ceil(1.1);-->2
Select floor(1.1)-->1
Select mod(6.4)-->2
Select round(2.345,2(保留几位小数))-->2.35
通过数据库的函数,生成一个六位数的随机验证码
Select Lpad(round(rand()*1000000,0),6,'0');
日期函数 | |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
YEAR(date) | 获取指定date年份 |
MONTH(date) | 获取指定date月份 |
DYA(date) | 获取指定date日期 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
查询所有员工的入职天数,并按入职天数按降序排序。
Select name,datediff(cuedate(),entrydate) from emp;
流程函数 | |
IF(VALUE , t, f) | 如果value为true,则返回t,否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回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默认值 |
练习:统计班级各个学员的成绩,展示的规则如下:
- >=85,展示优秀
- >=60,展示及格
- 否则,展示不及格
Select id, name,
(case when Math >=85 then '优秀' when Math >=60 then '及格' else '不及格' end) '数学',
(case when English >=85 then '优秀' when English >=60 then '及格' else '不及格' end) '英语',
(case when Chinese>=85 then '优秀' when Chinese >=60 then '及格' else '不及格' end) '语文',
From score;
约束
约束是作用于表中字段上的规则,用于限制存储在表中的数据
目的:保证数据库中的数据的正确、有效性和完整性。
非空约束 | Not null | 限制字段数据不能为null |
唯一约束 | unique | 保证字段的所有数据都是唯一,不重复 |
主键约束 | primary | 逐渐使一行数据的唯一标识,要求非空且唯一 |
默认约束 | default | 如果未指定该字段的值,就采用默认值 |
检查约束 | check | 保证字段值满足某一个条件 |
外键约束 | Foreign key | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 |
练习:根据需求,完成表结构的创建
字段名 | 字段含义 | 字段类型 | 约束条件 |
Id | id唯一标识 | Int | 主键,并且自动增长 |
Name | 姓名 | varchar(10) | 不为空,并且唯一 |
Age | 年龄 | Int | 大于0,并且小于等于120 |
Status | 状态 | char(1) | 如果没有指定该值,默认为1 |
Gender | 性别 | char(1) | 无 |
Create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check (age > 0 && age <= 120) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
)comment'用户表';
外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
添加外键:alter table emp add_constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
删除外键:alter table emp drop foreign key fk_emp_dept_id;
多表查询
多表关系
- 一对多
部门和员工的关系(一个部门对应多个员工,一个员工对应一个部门)
实现:在多的一方建立外键,指向一的一方的主键
- 多对多
学生和课程之间的关系(一个学生可以选择多个课程,一个课程可以被多个学生选择)
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。
- 一对一
用户和用户详情的关系(多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率)
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(uique)。
多表查询概述
Select * from 表一,表二;
内连接
1.查询每一个员工的姓名,及关联的部门名称(隐式内连接实现)
表结构:emp,dept
连接条件:emp.dept_id = dept.id
Select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
Select e.name, d.name from emp e, dept d where e.dept_id = d.id;
2.查询每一个员工的姓名,及关联的部门的名称(显式内连接实现)…inner join…on…
表结构:emp,dept
连接条件:emp.dept_id = dept.id
Select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
Select e.name, d.name from emp e join dept d on e.dept_id = d.id;
外连接
自连接
Select 字段列表 from 表A 别名A join 表A 别名A on 条件…; |
自连接查询,可以是内连接查询,也可以是外连接查询。 |
1.查询员工及其所属领导的名字
表结构:emp
Select a.name, b.name from emp a, emp b where a.managerid =b.id;
2.查询所有员工emp 及其领导的名字emp,如果员工没有领导,也需要查询出来。
Select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;
联合查询
union(会去重),union all(会将全部数据直接合并在一起); |
注意:对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致 |
将年龄大于50和薪资低于5000的员工查询出来。
Select * from emp where age>50;
Union all
Select * from emp salary<=5000;
(输出结果会有重复值)
------------------------------------------
Select * from emp where age>50;
Union
Select * from emp salary<=5000;
union会去重(输出的结果不会有重复值)
子查询
SQL语句中嵌套SELECT语句,称为嵌套查询也叫子查询 |
SELECT * FROM T1 WHERE column1=(SELECT column1 FROM T2); |
子查询外部的语句可以是insert/update/delect/select的任何一个。 |
1.标量子查询
根据销售部门id,查询员工信息。 | 查询销售部部门id |
Select * from emp where name = '销售部'; | Select id from dept where name = '销售部'; |
- 查询销售部的所有员工信息
Select * from emp where dept_id = (select id from dept where name = '销售部');
2.列子查询
- 查询销售部和市场部的所有员工信息
Select dept_id from emp where name ='销售部' or name = '市场部';(查询销售部和市场部的部门id)
Select * from emp where dept_id in (2,4);(根据部门id,查询员工信息)
Select * from emp where dept_id in (select id from dept where name ='销售部' or name ='市场部');
- 查询比财务部所有人工资都高的员工信息
Select id from dept where name ='财务部'; (查询财务部所有人的员工信息)
Select salary from emp where dept_id = (select id from dept where name ='财务部'); (查询财务部所有人的工资)
Select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));
- 查询比研发部其中任意一个人工资高的员工信息
Select salary from emp where dept_id = (select id from dept where name ='研发部'); (查询研发部所有人的工资)
Select * from emp where salary > any/some (Select salary from emp where dept_id = (select id from dept where name ='研发部'));
3.行子查询
返回的结果是一行,可以是多列 |
常用标识符:= ,<>,IN,NOT IN |
- 查询与张无忌的薪资及直属领导相同的员工信息;
Select salary, managerid from emp where name ='张无忌';(查询张无忌的薪资和直属领导id)
Select * from emp where (salary, managerid) = (select salary , managerid from emp where name = '张无忌');
4.表子查询
- 查询与张三和李四职位和薪资相同的员工信息;
Select salary ,job from emp where name = '张三' or name = '李四';(查询张三合李四的职位和薪资)
Select * from emp where (salary , job) in (select salary , job from emp where name = '张三' or name = '李四');
- 查询入职日期是2006-01-01之后的员工信息,机器部门信息;
Select * from emp where entrydate > '2006-01-01';(查询在2006年1月1日之后入职的员工)
Select * from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;(查询这些员工对应的部门信息)
Select e.* ,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
多表查询练习
Create table salgrade(
Grade int,
Grade int,
Hisal int,
)comment '薪资登记表';
Insert into salgrade values (1,0,3000);
Insert into salgrade values (2,3001,5000);
Insert into salgrade values (3,5001,8000);
Insert into salgrade values (4,8001,10000);
Insert into salgrade values (5,10001,15000);
Insert into salgrade values (6,15001,20000);
Insert into salgrade values (7,20001,25000);
Insert into salgrade values (8,25001,30000);
- 查询员工的姓名、年龄、职位、部门信息(隐式内连接)
表:emp,dept
连接条件:emp.dept_id = dept_id
Select e.name , e.age , e.job ,d.name from emp e , dept d where e.dept_id = d.id;
2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息(显式内连接)
表:emp,dept
连接条件:emp.dept_id = dept_id
Select e.name, e.age, e.job, d.name from emp e join dept d on e.dept_id = d.id where e.age <30;
3.查询所有员工的部门id、部门名称
表:emp,dept
连接条件:emp.dept_id = dept_id
Select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id ;
4.查询所有年龄大于40岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来
表:emp,dept
连接条件:emp.dept_id = dept_id(需要左外连接)
Select d.name, e.* from emp e left join dept d on e.dept_id = d.id where e.age > 40;
5.查询所有员工的工资等级
表: emp ,salgrade
连接条件:emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
Select e.*, s.grade from emp e, salgrade s where e.salgrade between s.losal and s.hisal;
6.查询研发部所有员工的信息及工资等级
表: emp ,salgrade , dept
连接条件:between salgrade.losal and salgrade.hisal, emp.dept_id = dept.id (n-1个连接条件)
查询条件:dept.name = 研发部
Select e.*, s.grade from emp e, salgrade s, dept d where e.dept_id = d.id and( e.salary between s.losal ) and d.name e.dept = '研发部';
7.查询研发部员工的平均工资
表:emp, dept
查询条件:emp.dept_id = dept_id
Select salary(avg) from emp, dept where dept.name = 研发部 and emp.dept_id = dept_id;
8.查询工资比灭绝高的员工信息
表:emp
查询条件:salary > (Select salary from emp where emp.name = '灭绝')
Select * from emp where salary > (Select salary from emp where emp.name = '灭绝');
9.查询比平均薪资高的员工信息
表:emp
查询条件:salary > avg(salary)
Select * from emp where salary >(select avg(salary) from emp);
10.查询低于本部门平均工资的员工信息
表:emp , dept
查询条件:salary < avg(salary)
Select * from emp where salary < (select avg(salary) from dept where dept.id = '研发部');
11.查询所有的部门信息,并统计部门的员工人数
Select d.id, d.name ,(select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;
12.查询所有学生的选课情况,展示出学生名称,学号,课程名称
表:student, course , student_course
连接条件:student.id = student_course.studentid, course.id = student_course.courseid
Select student.name, student.no, course.name from student, student_course, course where student.id = student_course.studentid, course.id = student_course.courseid;
事务
- 简介
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求, 即这些操作要么同时成功,要么同时失败。 |
默认mysql的事务是自动提交的,也就是说,当执行一条dml语句,mysql会立即隐式的提交事务。 |
2. 事务操作
查看/设置事务提交方式(默认是1自动提交)
Select @@autocommit;
Set @@autocommit = 0;(改为手动提交)
开启事务 | Start teansaction 或 begin |
提交事务 | Commit |
回滚事务 | rollback |
3.事务的四大特性(ACID)
原子性(Atomicity) | 事务是不可分割的最小单元,要么全部成功,要么全部失败。 |
一致性(Consistency) | 事务完成时,必须使所有的数据都保持一致状态。 |
隔离性(Isolation) | 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。 |
持久性(Durability) | 事务一旦提交或回滚,它对数据库的改变就是永久的。 |
4.并发事务问题
脏读 | 一个事务读到另一个事务没有提交的事务 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但在插入数据时,又发现这行数据已经存在 |
5.事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(默认) | × | × | √ |
Serializable | × | × | × |
- 查看事务隔离级别
Select @@transaction_isolation;
- 设置事务隔离级别
Set [session(针对当前客户端窗口) | global(针对所有客户端)] transaction isolation level {Read uncommitted | Read committed | Repeatable Read | Serializable} ;