1
2
3
4
5
6
# 查看数据库
show databases; 
# 选择某一个数据库
use database名字;
# 查看数据库中的表格
show tables;

[!info]
Java英文字母区分大小写，MySQL英文字母是不区分大小写的。

1. DDL数据定义语言

用来定义数据库中的对象（database table）

1.1 创建数据库

1
2
3
create database 数据库名;
# 创建数据库的时候设置默认的字符集
create database 数据库名称 default character set='utf8';

image.png

1.2 创建表格

1
2
3
4
5
6
7
8
9
10
11
12
create table 表格名称(
	列名 数据类型(长度),
	列名 数据类型(长度),
	列名 数据类型(长度)
)

# 创建表格的时候给单独一个表格设置字符集
create table 表格名称(
	列名 数据类型(长度),
	列名 数据类型(长度),
	列名 数据类型(长度)
)character set utf8;

image.png

1.3 数据库中的数据类型

类型
整数型 tinyint smallint mediumint int bigint
小数型 float double decimal numeric
字符串 char varchar binary（二进制） varbinary（二进制） blob(二进制大文本)/text（正常字符大文本）
日期/时间 date(日期) time(时间) datetime(日期时间) timestamp(时间戳)

[!float]
float类型： float(m,n) 总共可以存储m位数字，小数点之后又n位。m取值范围是1-65 n取值范围是0-30。 默认m是10 默认n是0

1.4 创建一个学生表格

  • [?] 创建一个学生表格，用来记录学生信息（学号、姓名、性别）
1
2
3
4
5
create table student(
	sid int(12),
	sname varchar(5),
	ssex varchar(4)
);

1.5 修改表格结构

1
2
3
4
5
6
7
8
9
10
11
# 修改表名
alter table 源表名 rename to 新表名;

# 修改列名
alter table 表名 change 旧列名 新列名 类型（长度）;

# 添加列
alter table 表名 add 列名 类型(长度);

# 删除原有得列
alter table 表名 drop 列名;

1.6 删库、删表

1
2
3
4
# 删表
drop table 表名;
# 删库
drop database 库名;

2. DML数据操作语言

用来操作数据库表格中的数据信息

2.1 新增记录

1
2
3
4
5
6
7
8
9
10
# 新增一条记录
insert into 表名(列名, 列名....) values(值，值...);

# 当每一列都有数据的时候，可以不写列名，但是values的值要和列名对应上
insert into 表名 values(值,值,值);

# 增加多条记录
insert into 表名 values(值,值,值),(值,值,值);


image.png

2.2 查询记录

1
2
3
4
5
select 列名,列名,列名 from 表名;

select * from 表名;
# 查询数据库字符集
select schema_name, default_character_set_name from information_schema.schemata where schema_name = '现在的数据库名称';

2.3 删除记录

1
2
# 如果不加where条件，则删除所有数据
delete from 表名 [where...];

2.4 修改记录

1
update 表名 set 列名 = 值, 列名 = 值 [where...];

2.5 小任务

  • [?] 创建一张表格，用来记录员工信息，表格名称叫做EMP(Employee)
empno（编号） ename（姓名） job（职务） mgr（上级） hiredate（入职日期） sal（月薪） comm（佣金） deptno（部门）
7369 SMITH CLERK 7902 1980-12-17 800 null 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30
7521 WARD SALESMAN 7698 1981-2-22 1250 500 30
7566 JONES MANAGER 7839 1981-4-2 2975 NULL 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-5-1 2850 NULL 30
7782 CLARK MANAGER 7839 1981-6-9 2450 NULL 10
7788 SCOTT ANALYST 7566 1987-4-19 3000 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000 NULL 10
7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30
7876 ADAMS CLERK 7788 1987-5-23 1100 NULL 20
7900 JAMES CLERK 7698 1981-12-3 950 NULL 30
7902 FORD ANALYST 7566 1981-12-3 3000 NULL 20
7934 MILLER CLERK 7782 1982-1-23 1300 NULL 10 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建表格
create table EMP(
	empno	int(10),
	ename varchar(20),
	job varchar(20),
	mgr int(10),
	hiredate date,
	sal float(3),
	comm float(3),
	deptno int(4)
);

# 插入数据
insert into emp values(7499,'ALLEN','SALESMAN' ,7698,'1981-2-20' ,1600,300 ,30),(7521,'WARD'   ,'SALESMAN',7698,'1981-2-22' ,1250,500 ,30), (7566,'JONES'  ,'MANAGER'  ,7839,'1981-4-2'  ,2975,NULL,20), (7654,'MARTIN' ,'SALESMAN' ,7698,'1981-9-28' ,1250,1400,30),(7698,'BLAKE'  ,'MANAGER'  ,7839,'1981-5-1'  ,2850,NULL,30),(7782,'CLARK'  ,'MANAGER'  ,7839,'1981-6-9'  ,2450,NULL,10),(7788,'SCOTT'  ,'ANALYST'  ,7566,'1987-4-19' ,3000,NULL,20),(7839,'KING'   ,'PRESIDENT',NULL,'1981-11-17',5000,NULL,10),(7844,'TURNER' ,'SALESMAN' ,7698,'1981-9-8'  ,1500,0   ,30),(7876,'ADAMS'  ,'CLERK'    ,7788,'1987-5-23' ,1100,NULL,20),(7900,'JAMES'  ,'CLERK'    ,7698,'1981-12-3' ,950 ,NULL,30),(7902,'FORD'   ,'ANALYST'  ,7566,'1981-12-3' ,3000,NULL,20),(7934,'MILLER' ,'CLERK'    ,7782,'1982-1-23' ,1300,NULL,10);

3. DQL DML下分支

3.1 条件筛选 where

除了insert以外的其他三个语句都可以做筛选，where拼接在除了insert语句以外的其他语句基本结构之后。

1
2
3
delete from 表 where ...;
update 表 set 列 = 值 where...;
select 列 from 表 where...;
  1. 比较运算符

> < >= <= != =

1
2
3
4
5
# 查找语文成绩大于95分的
select * from student where chinese > 95;

select * from student where english <= 85;
select * from student where physical = 100;
  1. 算数运算符

+ - * /

1
2
# 语文成绩加10分能优秀的
select * from student where chinese+10 >= 80;
  1. 逻辑运算符

and or not and优先级高于or，or高于not

1
2
3
4
# 语文和英语成绩都在95之上的
select * from student where chinese >95 and english >95;
# 男的或者语文成绩不及格的
select * from student where s_sex = 'nan' or chinese < 60;

[!and]
and在sql底层的原理，先把文件读取出来，放到集合里面。然后循环集合筛选出语文成绩大于95分的。然后用筛选之后的数据进行循环，在找出英语成绩大于95分的。所以：为了提高效率，尽量将条件苛刻的写在前面。

4. [not] between and 在…之间的

1
2
3
4
# 物理成绩小于等于八十五并且大于等于六十
select * from student where physics <= 85 and physics >= 60;
# between and 效率更高一点
select * from student where physics between 60 and 85;
  1. [not] in 满足其中一个条件就可以
1
2
3
4
# 语文成绩等于78 或者 语文成绩等于87
select * from student where chinese = 78 or chinese = 87;
# in ()
select * from student where chinese in (78, 87);
  1. [not] like 模糊查询

% 用来代替0-n个字符的。_ 用来代替1个字符（有且只有一个）

1
2
3
4
5
6
# 查找c开头的
select * from student where s_name like 'c%';
# 查找第二个字符是h的
select * from student where s_name like '_h%';
# 查找只要带n的就可以
select * from student where s_name like '%n%';

3.2 排序

order by排序，升序排序 asc (默认) , 降序排序desc

1
2
3
4
5
6
# 语文成绩按照降序排序
select * from student order by chinese desc;
# 语文成绩升序排序
select * from student order by chinese; # 默认就是升序
# 语文成绩大于80并且按照日期的升序排序
select * from student where chinese > 80 order by s_data asc;
1
2
# 联合排序 如果第一个条件一样，可以增加第二个条件
select * from student order by chinese desc, english asc;

3.3 小任务

  • [?] 创建一个person表，共有4列，并向表中插入数据：
username address salary department
张三 武当 6000 保洁部
张无忌 魔教 6000 事业部
张三丰 武当 4000 产业部
周芷若 峨眉 8000 事业部
谢逊 魔教 5000 产业部
杨逍 魔教 7000 事业部
  1. 假设person表中所有人每个月还有100元奖金，查询出所有人的姓名，地址，年薪。
  2. 假设person表中所有人每年有1000元奖金，查询出所有人的姓名，地址，年薪。
  3. 请以下面的方式查询出所有信息：
姓名 居住地 月薪 年薪
张三 武当 6000 7200
  1. 查询出所有部门为事业部的所有人信息。
  2. 查询出所有地址为魔教的所有人信息。
  3. 查询出所有工资为6000元的人的所有信息。
  4. 查询出所有工资在6000-8000之间的所有人的信息。
  5. 查询出所有工资是6000，7000，8000的所有人的信息。
  6. 查询出所有工资在6000-8000之间，或者姓张的所有人的信息。
  7. 查询出所有工资为6000，7000，8000，或者姓张，并且不叫张三丰的所有人的基本信息。
  8. 查询出所有工资为5000，或者叫张三的人的所有信息。
  9. 查询出所有魔教人的信息，按照人名升序排序。
  10. 查询出所有工资高于5000的人员信息，按照address降序排序。
  11. 查询出所有人的信息，先按照salary降序排序，后按照人名升序排序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 创建表格
create table person(
	username varchar(10),
	address varchar(10),
	salary float,
	department varchar(10)
)
# 添加数据
insert into person values('张三'  ,'武当',6000,'保洁部'), ('张无忌','魔教',6000,'事业部'),('张三丰','武当',4000,'产业部'),('周芷若','峨眉',8000,'事业部'),('谢逊'  ,'魔教',5000,'产业部'), ('杨逍'  ,'魔教',7000,'事业部');
# 1.
select username, address, (salary + 100)*12 as salary from person;
# 2.
select username, address, salary *12 + 1000 as salary from person;
# 3.
select username as '姓名', address as '居住地', salary as '月薪', salary * 12 as '年薪',department as '部门' from person;
# 4.
select * from person where department = '事业部';
# 5.
select * from person where address = '魔教';
# 6. 
select * from person where salary = 6000;
# 7. 
select * from person where salary between 6000 and 8000;
# 8.
select * from person where salary in (6000, 7000, 8000); 
# 9.
select * from person where salary between 6000 and 8000 or username like '张%';
# 10.
select * from person where salary in (6000,7000,8000) or username like '张%' and username != '张三丰';
# 11.
select * from person where salary = 5000 or username = '张三';
# 12.
select * from person where address = '魔教' order by username;
# 13.
select * from person where salary > 5000 order by address desc;
# 14.
select * from person order by salary desc, username asc;

3.4 函数

  • [?] 函数可以放置在什么位置上？
1
2
3
4
# 查询中用来显示的部分
select 函数(列) from 表格;
# 放到where条件的后面
select 列 from 表格 where salary > 函数(值);
  1. 比较函数
1
isnull(值); 是空值返回1  不是空值返回0
  1. 数学函数（数值函数）
1
2
3
4
5
abs(); 绝对值
floor(); 向下取整
mod(); 取余数
pow(); 求平方
round(); 随机数
  1. 日期和时间
1
2
3
4
5
now(); 当前时间
year(date); 年
month(date); 月
day(date); 日
week();
  1. 控制流程函数（转换函数）
1
2
3
4
5
if(条件,a,b);
select sname, if(age>18, '大', '小') from student;

ifnull(值， v); 如果第一个值要是空的就显示第二个，反之则展示
select sname, ifnull(chinese, 0)+ifnull(english, 0) from student;
  1. 字符串函数
sql String
length() length()
concat() concat()
substr() substring()
instr() indexOf()
replace() replace()
upper() toUpperCase()
lower() toLowerCase()
ltrim()/rtrim() trim()
lpad()/rpad()
reverse() 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 # length
select s_name, length(s_name) from student;
# concat
select s_name, s_sex, concat(s_name, s_sex) from student;
# substr
select s_name, substr(s_name, 1, 3) from student;
# instr
select s_name, instr(s_name, 'a') from student;
# replace
select s_name, replace(s_name, 'a', 'A') from student;

# lpad(s_name, 5, '*') 一定显示五个字符，如果长度不够在左侧补'*'，如果要是长度太长，就从反方向截取（chenjie, chenj）
select s_name, lpad(s_name, 5, '*') from student;
select s_name, rpad(lpad(s_name, length(s_name)+4, '*'), length(s_name)+8, '*') from student;
  1. 分组函数（聚合函数）

distanct 列，列 可以去掉重复元素。用法类似于order by

1
2
3
4
5
6
7
8
9
10
11
# 他会先去查stdent文件,最后会展示出很多相同的时间
select now() form student;
# 结果是只展示一个时间， 但是还是会去查student表，效率会慢点 
select distanct now() form student;
# 注意：只有mysql支持
select now();

# 报错：名字是所有行，但是性别只能出现两行，对不上
select s_name, distanct s_sex from student;
# 没有问题，如果说名字出现相同得，再去看性别是否相同，如果全部相同才符合
select distanct s_name, s_sex from student;

分组函数+分组条件

分组函数：
count()
max() min() avg() sum()

分组条件：

如果sql语句中一旦搭配了分组条件，能展示得信息只有两种： 分组条件、分组函数

group by 列 having与where一样，但是他的优先级低于group by where是高于group by

1
2
3
4
5
6
7
8
9
10
# 1.查询student表格中每一个班级有多少同学
# 通过group by classid 根据id进行分组，然后在将每组得班级id和总人数，算出来
select classid, count(s_id) from student group by classid;
# 2. 查询student表中每一个班级得语文平均分 先分组后排序
select classid, avg(chinese) from student group by classid order by avg(chinese) desc;
# 3. 查询student中得所有男同学，看看他们都在那个班级 where group by having order by
select classid,count(s_id) from student where s_sex = 'nan' group by classid;

# 4. 查询student表中语文平均成绩高于80分得班级
select classid, avg(chinese) from student group by classid having avg(chinese) >80;

嵌套

在一个完整得sql语句中，嵌套了另一个完整得sql语句

1
2
3
4
5
# 查询student表格中语文成绩最高得人
select max(chinese) from student;
select * from student where chinese = ??;  # 将问号替换成查最大值得语句
=>
select * from student where chinese = (select max(chinese) from student);
1
2
3
4
5
6
7
8
9
10
# 查询在深圳班级上课得同学有哪些
create table myclass(
	classid int(4),
	classname varchar(10),
	class_address varchar(20)
)

insert into myclass values(1, '深圳校区', '深圳'), (2, '北京校区', '北京');

select * from student where classid = (select classid from myclass where class_address = '深圳');

3.5 小任务

员工编号 姓名 部门 生日 工资 职务 信息更新时间
1 赵一一 C ‘1980-10-11’ 10000 程序员 系统当前时间
2 钱二二 C ‘1981-10-12’ 20000 程序员 系统当前时间
3 孙三三 C ‘1982-9-1’ 30000 项目经理 系统当前时间
4 李四四 JAVA ‘1983-9-2’ 40000 程序员 系统当前时间
5 周五五 JAVA ‘1984-11-1’ 50000 程序员 系统当前时间
6 吴六六 JAVA ‘1985-1-1’ 70000 项目经理 系统当前时间
7 郑七七 JAVA ‘1986-11-2’ 50000 程序员 系统当前时间
  1. 请建立User表保存员工的相关信息，生日，信息更新时间用date类型。
  2. 查询每个部门最高工资，最低工资，平均工资。
  3. 给所有项目经理工资涨10000。
  4. 给工资低于30000的员工工资涨到30000。
  5. 查询各部门的工资总额，平均工资，最高工资，最低工资。
  6. C部门的项目经理离职，删除其信息。
  7. C部门的程序员全部转入JAVA部门，更改信息。
  8. 查询11月过生日的员工。
  9. 查询本月过生日的员工。设计一个通用的语句，不要用11月判断。
  10. 查询整合部门后表中记录的所有记录，按照薪资从高到低排布。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 1.
create table user(
	bianhao int(4),
	xingming varchar(10),
	bumen varchar(10),
	shengri date,
	gongzi int(255),
	zhiwu varchar(20),
	update_time date
)
insert into user values(1,'赵一一','C'   ,'1980-10-11',10000,'程序员'  ,now()),(2,'钱二二','C'   ,'1981-10-12',20000,'程序员'  ,now()),(3,'孙三三','C'   ,'1982-9-1'  ,30000,'项目经理',now()),(4,'李四四','JAVA','1983-9-2'  ,40000,'程序员'  ,now()),(5,'周五五','JAVA','1984-11-1' ,50000,'程序员'  ,now()),(6,'吴六六','JAVA','1985-1-1'  ,70000,'项目经理',now()),(7,'郑七七','JAVA','1986-11-2' ,50000,'程序员'  ,now())

# 2.
select bumen, max(gongzi) as '最高工资', min(gongzi) as '最低工资', avg(gongzi) as '平均工资' from user group by bumen;
# 3.
update user set gongzi = gongzi+10000, update_time = now() where zhiwu = '项目经理';
# 4.
update user set gongzi = 30000, update_time = now() where gongzi < 30000;
# 5.
select bumen, sum(gongzi) as '工资总额', avg(gongzi) as '平均工资', max(gongzi) as '最高工资', min(gongzi) as '最低工资' from user group by bumen;
# 6.
delete from user where bumen = 'c' and zhiwu = '项目经理';
# 7.
update user set bumen = 'JAVA', update_time = now() where bumen = 'C';
# 8.
select * from user where month(shengri) = 11;
# 9.
select * from user where month(shengri) = month(now());
# 10.
select * from user order by gongzi desc;

3.6 终极任务

  • [?] DEPT部门表
deptno dname loc
10 ‘ACCOUNTING’ ‘NEW YORK’
20 ‘RESEARCH’ ‘DALLAS’
30 ‘SALES’ ‘CHICAGO’
40 ‘OPERATIONS’ ‘BOSTON’
  • [?] 条件筛选练习（EMP表）
  1. 查询部门30中得雇员
  2. 查询所有办事员（CLERK）得姓名、编号、部门
  3. 查询佣金（comm）高于薪资（sal）得雇员
  4. 查询那些人没有佣金（comm）
  5. 查询佣金（comm）高于薪金（sal）60%的雇员
  6. 显示所有人的姓名，月薪，年薪
  7. 找出部门10中所有经理和部门20中的所有办事员的详细资料
  8. 找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员，但其薪金>=2000的所有雇员的资料
  9. 找出不收取佣金或收取的佣金低于500的雇员
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 1.
select * from emp where deptno = 30;
# 2.
select ename, empno, deptno from emp where job = 'CLERK';
# 3.
select * from emp where comm > sal;
# 4.
select * from emp where isnull(comm) = 1;
# 5.
select * from emp where comm - sal*0.6 > 0;
# 6.
select ename as '姓名', sal as '月薪', sal*12 as '年薪' from emp;
# 7.
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK');
# 8.
select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (job != 'MANAGER' and job != 'CLERK' and sal >= 2000);

select * from emp where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or (job not in('MANAGER', 'CLERK') and sal >= 2000);
# 9.
select * from emp where isnull(comm) = 1 or comm < 500;
  • [?] 排序练习（EMP表）
  1. 查询所有人员信息，按照工资升序排列
  2. 查询所有人员姓名，工资，佣金，按照工资降序排列，若工资相同则按照佣金升序排列
  3. 查询工资大于两千的所有员工，按照工资降序排列
  4. 查询所有人的信息，按照姓名排序
  5. 查询工资在2000-3000之间的员工信息，按照雇佣日期升序排列
1
2
3
4
5
6
7
8
9
10
# 10.
select * from emp order by sal;
# 11.
select ename, sal, comm from emp order by sal desc, comm asc;
# 12.
select * from emp where sal > 2000 order by sal desc;
# 13.
select * from emp order by ename;
# 14.
select * from emp where sal between 2000 and 3000 order by hiredate asc;
  • [?] 函数练习（EMP表）
  1. 显示正好为6个字符的雇员姓名
  2. 显示所有雇员的姓名的前三个字符
  3. 显示所有雇员的姓名，用a替换所有A
  4. 显示不带有R的雇员姓名
  5. 显示只有首字母大写的所有雇员的姓名（MySQL不区分）
  6. 找出早于35年之前受雇的雇员
  7. 显示出所有雇员的姓名以及满10年服务年限的日期
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 15.
select ename from emp where length(ename) = 6;
# 16.
select substr(ename, 1, 3) from emp;
# 17.
select replace(ename, 'A', 'a') from emp;
# 18.
select ename from emp where instr(ename, 'R') =0;

select ename from emp where ename not like '%R%';
# 19.
select * from emp where ename = concat(upper(substr(ename, 1, 1)), substr(ename, 2));
# 20.
select * from emp where year(hiredate) > 35;
# 21.
select ename,hiredate,date_add(hiredate, interval 10 year) from emp;
  • [?] 分组函数练习（EMP表）
  1. 显示每种工作的人数
  2. 显示工作人数大于3的工作的平均工资
  3. 显示出经理有几种不同的工资
  4. 显示出30号部门有几种不同的工作
  5. 显示出emp表数据中不同月份受雇用的人数
  6. 显示出每个管理者手下带了多少个员工
  7. 显示出收取佣金的雇员的不同工作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 22.
select job, count(*) from emp group by job;
# 23.
select job, avg(sal) from emp group by job having count(*) > 3;
# 24.
select ename, sal, job from emp group by sal having job = 'MANAGER';
select ename, sal, job from emp where job = 'MANAGER' group by sal;
# 25.
select job, deptno from emp where deptno = 30 group by job;
# 26.
select ename, count(*), hiredate from emp group by month(hiredate);
# 27.
select * from emp;
select count(*), mgr, job, ename from emp group by mgr;
# 28.
select * from emp where isnull(comm) != 1 group by job;
  • [?] 嵌套查询练习（EMP表）
  1. 查询平均工资大于900的部门中的所有员工
  2. 查询工资比ALLEN多的所有员工
  3. 查询薪金高于工资平均薪金的所有员工姓名，部门编号，具体薪资
  4. 列出与scott从事相同工作的所有员工信息
  5. 查询薪金大于部门30中的员工最高薪金的所有员工的姓名，薪金和部门编号
  6. 查询在部门sales工作的员工的姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
# 29.
select avg(sal), deptno from emp group by deptno;
select * from emp where deptno in(select deptno from emp group by deptno having avg(sal) > 900);
# 30.
select * from emp where sal > (select sal from emp where ename = 'ALLEN');
# 31.
select ename, deptno,sal from emp where sal > (select avg(sal) from emp);
# 32.
select * from emp where job = (select job from emp where ename = 'SCOTT');
# 33.
select ename, sal, deptno from emp where sal > (select max(sal) from emp where deptno = 30);
# 34.
select ename, deptno from emp where deptno = (select deptno from dept where dname = 'SALES');

3.7 关键字使用 in any some all

in : 满足查询子集的某一个即可 (=)，in后面的括号内可以是常量固定值，也可以是sql语句查询出来的结果。

1
2
3
4
5
select * from student where loc in (1, 2);
select * from studemt where classid in (select classid from myclass where loc in ('深圳','北京'));

# 查询不在北京的
select * from student where classid not in (1);

下面三个关键字后面不允许写固定值，只能写sql语句

any : 满足查询子集中的某一个即可（> <）

1
2
3
# select classid from myclass where classid > 2  结果 3，4
# >any(select classid from myclass where classid > 2)  只有4大于3满足条件，1，2，3号<=3所以不满足条件
select * from  student where classid >any(select classid from myclass where classid > 2);

some : 与any完全一致

1
select * from  student where classid >some(select classid from myclass where classid > 2);

all : 满足查询子集的全部才可以

1
select * from student where classid >all(select classid form myclass where classid < 2);

3.8 集合操作-并集union

将两个表的数据合并成一个表

1
select s_id, s_name, s_sex from student union select classid, classname,class_address from myclass;

image.png

[!union]
注意： 查询的两个表的列数必须是一致的，每一列的类型是否相同是没有要求的。

[!union和union all区别]
注意： union会把两条重复的数据做处理，只保留一条(性能慢，记录的是第一次出现的记录)；而union all不做任何处理，两条重复的数据全部保留。

3.9 列的约束[[#1. DDL数据定义语言]]

  1. 主键约束

每一个表格内，只能有一个列被设置为主键约束，主键约束通常是用来标记表格中数据的唯一存在。
要求： 要求当前列不能为null值，值是唯一的不能重复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 标准写法
alter table 表名 add constraint 约束名字 约束类型 (列);
alter table myclass add constraint pk_myclass primary key(classid);
# 简写
alter table 表名 add primary key(列);

# 添加号主键约束之后可以通过以下语句查看
desc myclass;
show keys from myclass;

# 设置主键自增
alter table myclass modify classid int(4) auto_increment;  # 如果不写默认从1开始自增
# 删除主键约束(不重复特性取消了，但是非空的约束还在)
alter table myclass drop primary key;

# 更改列可以为空
alter table myclass modify classid int(4) null;
  1. 唯一约束

可以为表格中的某一列添加唯一约束，约束与主键类似。唯一约束表示的是列的值不能重复，但是可以为空。唯一约束在表格中可以存在多个列

1
2
3
4
5
6
7
8
# 标准写法
alter table 表名 add constraint 约束名字 约束类型 (列);
alter table myclass add constraint uk_myclass unique key(loc);
# 简写
alter table 表名 add unique key(列);  # 约束名默认是列名

# 删除唯一约束
alter table myclass drop index 约束名;
  1. 非空约束

在表格的某一个列上添加非空约束，当前列的值不能为null

1
2
3
4
alter table 表名 modify 原列名 原列类型 not null;
alter table myclass modify cname varchar(20) not null;

alter table myclass modify cname varchar(20) not null default 'alvis.org.cn:7500';  # 给个默认值
  1. 外键约束

约束自己表格内的信息不能随意填写，受到另外一个表格某一列的影响。表格内的信息，得是另一个表格得列。另一张表的列是唯一约束（主键，唯一）

表格中可以有多个列被设置为外键约束。

1
2
3
4
5
6
alter table 表名称 add constraint fk_当前表_关联表 foreign key(列) references 另一个表(列); 

# 删除外键
alter table 表名 drop foreign key 约束名字;  # 外键没有删掉（外键约束删除了，没有约束规则了）
# 自动在表格内添加一个新的key
alter table 表名 drop key 约束名;
  1. 检查约束

小任务

  • [?] 设计一张表格myclass, 用来记录班级的信息
  1. classid :班级编号 --> 主键
  2. cname : 班级名称 --> 非空
  3. loc : 班级位置城市 --> 唯一
  4. 删除三个约束，将表格还原成最原始的格式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
create table myclass(
	classid int(4),
	cname varchar(20),
	loc	varchar(20)
)
# 创建主键
alter table myclass add constraint pk_myclass primary key(classid);
# 设置主键自增
alter table myclass modify classid int(4) auto_increment;
insert into myclass(cname,loc) values('classOne', '北京'); # 主键id是一条一条的增加

# 设置非空约束（如果为空则展示默认值）
alter table myclass modify cname varchar(20) not null default 'alvis.org.cn:7500';
insert into myclass(loc) values('北京');

# 设置唯一约束
alter table myclass add constraint uk_myclass unique key(loc);
insert into myclass values(13, 'classOne','天津');

# 查看三个约束
desc myclass;
show keys from myclass;

# 删除三个约束
# 取消主键的自增
alter table myclass modify classid int(4);
# 删除主键
alter table myclass drop primary key;
# 取消主键的非空约束
alter table myclass modify classid int(4) null;

# 删除唯一约束
alter table myclass drop index uk_myclass;

# 删除非空约束
alter table myclass modify cname varchar(20) null;

image.png

image.png

  • [?] 设计一个存储学生信息的表格student

sid sname sage ssex classid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table student(
	sid	int(4),
	sname varchar(20),
	sage int(2),
	ssex varchar(4),
	classid int(4)
)
# myclass表设置主键
alter table myclass add constraint pk_myclass primary key(classid);
alter table myclass modify classid int(4) auto_increment;
# student表设置外键和mycalss表关联
alter table student add constraint fk_student_myclass foreign key(classid) 
references myclass(classid);

# student表设置主键
alter table student add constraint pk_student primary key(sid);
alter table student modify sid int(4) auto_increment;


select * from myclass;
select * from student;
insert into student values(1,'alvis', 18, 'nan', 9);

3.10 联合查询

  1. 广义笛卡尔积：
1
2
3
4
5
# 将两张表或者多张表拼接成一张表，列相加，行是相乘的关系
select * from A,B;

# 性能慢，先将表整合到一起，然后where条件在进行循环处理
select * from A,B where 条件;

image.png

  1. 外连接
1
2
3
4
5
6
7
8
# A表格先出现，A的列就在左边展示。反之则展示B表格的列在做左边
# left和right用来控制以哪一个表格的数据作为基准，作为基准的数据必须全部展示出来，非基准的表格则按照on条件与之拼接。如果找到条件拼接则正常展示，如果找不到满足条件的则展示null;
select * from A left/right outer join B on 条件；

# 往emp表格中增加一条没有部门的数据
insert into emp(empno, ename) values (999,'ceshi_join');

select * from test.emp e left outer join test.dept d on e.deptno = d.deptno;

image.png

1
select * from test.emp e right outer join test.dept d on e.deptno = d.deptno;

image.png

  1. 内连接（自连接）
1
2
# 内连接和笛卡尔积连接一样，但是性能比笛卡尔积连接性能更高
select * from A inner join B on 条件;

3.11 联合查询练习

  • [?] 设计表关系如下：

国家表（Country）

国家cid（主键） 国家名称（cname）
1 中国
2 美国
3 日本

地区表（Area）

地区aid（主键） 地区名称aname 国家cid（外键）
1 北方 1
2 南方 1
3 西部 2
4 东部 2
5 北海道 3
6 四国 3

城市表（city）

城市cityid（主键） 城市名称（cityname） 人口数量（citysize） 地区（aid外键）
1 哈尔滨 750 1
2 大连 50 1
3 北京 2000 1
4 上海 1500 2
5 杭州 800 2
6 洛杉矶 1200 3
7 休斯顿 750 3
8 纽约 1000 4
9 底特律 500 4
10 东京 1500 6
11 名古屋 50 5
12 大版 20 6
  1. 查询人口数在1000到2000之间得城市所属在那个地区
  2. 查询每个国家得城市个数，按照城市个数升序排序
  3. 查询各地区城市人口平均数，按照人口平均数降序排列
  4. 查询哈尔滨所在国家得名字
  5. 查询各个地区名字和人口总数
  6. 查询美国有哪些城市 列出城市名
  7. 查询人口最多得城市在那个国家
  8. 查询每个国家得人口总数
  9. 查询城市人口总数为1500得国家名字
  10. 查询个地区总人数 按照人口数总量降序排序
  11. 查询人口总数超过5000得国家名称
  12. 查询人口数大于杭州的城市有哪些
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
create table country(
	cid int(4),
	cname varchar(10)
);
create table area(
	aid int(4),
	aname varchar(10),
	cid int(4)
);
alter table country add constraint pk_country primary key(cid);
alter table area add constraint pk_area primary key(aid);
alter table area add constraint fk_area_country foreign key(cid) references country(cid);
create table city(
	cityid int(4),
	cityname varchar(10),
	citysize int(6),
	aid int(4)
);
alter table city add constraint pk_sity primary key(cityid);
alter table city add constraint fk_city_area foreign key(aid) references area(aid);
insert into country values(1, '中国'),(2, '美国'),(3, '日本');
insert into area values(1,'北方'  ,1),(2,'南方' ,1),(3,'西部'  ,2),(4,'东部'  ,2),(5,'北海道',3),(6,'四国'  ,3);
insert into city values(1 ,'哈尔滨',750 ,1 ),(2 ,'大连'  ,50  ,1 ),(3 ,'北京'  ,2000,1 ),(4 ,'上海'  ,1500,2 ),(5 ,'杭州'  ,800 ,2 ),(6 ,'洛杉矶',1200,3 ),(7 ,'休斯顿',750 ,3 ),(8 ,'纽约'  ,1000,4 ),(9 ,'底特律',500 ,4 ),(10,'东京'  ,1500,6),(11,'名古屋',50  ,5 ),( 12,'大版'  ,20  ,6);

# 1.查询人口数在1000到2000之间得城市所属在那个地区
select * from area inner join city on area.aid = city.aid where citysize between 1000 and 2000;
# 2.查询每个国家得城市个数，按照城市个数升序排序
select country.cname, count(city.cityname) from country inner join area on country.cid = area.cid inner join city on  area.aid = city.aid  group by country.cname order by count(city.cityname);
# 3.查询各地区城市人口平均数，按照人口平均数降序排列
select area.aname, avg(city.citysize) from area inner join city on area.aid = city.aid group by area.aname order by avg(city.citysize) desc;
# 4.查询哈尔滨所在国家得名字
select country.cname from city, area,country where country.cid = area.cid and area.aid = city.aid and cityname = '哈尔滨'; 
# 5.查询各个地区名字和人口总数
select area.aname, sum(city.citysize) from area inner join city on area.aid = city.aid group by area.aname;
# 6.查询美国有哪些城市 列出城市名
select cname, cityname from country,area,city where country.cid = area.cid and area.aid = city.aid and country.cname='美国';
# 7.查询人口最多得城市在那个国家
select * from country,area,city where country.cid = area.cid and area.aid = city.aid and citysize = (select max(citysize) from city);
# 8.查询每个国家得人口总数
select cname, sum(citysize) from country,area,city where country.cid = area.cid and area.aid = city.aid group by cname;
# 9.查询城市人口总数为1500万得国家名字
select * from country,area,city where country.cid = area.cid and area.aid = city.aid and citysize = 1500;
# 10.查询个地区总人数 按照人口数总量降序排序、
select aname, sum(citysize) from area inner join city on area.aid = city.aid group by aname order by sum(citysize) desc;
# 11.查询人口总数超过5000得国家名称
select cname, sum(citysize) from country,area,city where country.cid = area.cid and area.aid = city.aid group by cname having sum(citysize) > 5000; 
# 12.查询人口数大于杭州的城市有哪些
select * from city where citysize > (select citysize from city where cityname = '杭州');

3.12 行列互换+分页查询

行列互换示意图：
image.png

1
2
3
4
5
6
7
8
9
10
11
12

create table store(
	warehouse varchar(10),
	inventory int(4),
	wmonth varchar(10)
);

insert into store values('A', 10, '一月份'),('B', 100, '一月份'),('C', 1000, '一月份'),('A', 20, '二月份'),('B', 200, '二月份'),('C', 2000, '二月份'),('A', 30, '三月份'),('B', 300, '三月份'),('C', 3000, '三月份');

select * from store;

select warehouse as '仓库名', max(if(wmonth = '一月份', inventory, 0)) as '一月份', max(if(wmonth = '二月份', inventory, 0)) as '二月份', max(if(wmonth = '三月份', inventory, 0)) as '三月份' from store group by warehouse;

分页查询 limit

1
2
select * from emp; order by sal desc;
select * from emp order by sal desc limit 5,5; # 从索引5开始，limit5个数据

4. DCL数据控制语言

用来控制数据库中用户的权限

4.1 配置mysql全局环境变量

  1. 找到mysql的位置(复制路径)

C:\Program Files\MySQL\MySQL Server 5.7\bin

image.png

  1. 配置环境变量（找到path路径，然后将复制的mysql路径添加进去）

image.png

4.2 mysql创建用户

  1. root账号可以查看mysql数据库中的user表格。里面记录着所有用户信息
    user 列 host 列 password 列 authentication_string 列

  2. 先创建一个新的用户

[!info]
新创建的用户只有一个默认的权限，Usage只允许登录，不允许做其他事情

1
2
3
4
5
6
7
create user '用户名'@'IP' identified by '密码';
create user 'alvis'@'localhost' identified by 'chenjie+00';

select user,host,authentication_string from mysql.user;

# 查看用户的权限
show grants for '用户名'@'IP';

4.3 给新用户赋予权限

1
2
3
4
5
grant 权限 on 数据库名.表 '用户名'@'IP';
grant all on *.* to 'alvis'@'localhost';

# 赋予权限之后最好做一个刷新
flush privileges;

4.4 回收用户权限

1
revoke 权限 on 数据库。表名 from '用户名'@'IP';

4.5 修改用户密码

1
update user表 set authentication_string = password('123456');

5. TPL事务处理语言

可以理解为多线程并发操作同一个文件

事务的四大特性（ACID）
A：Atomicity --> 原子性
一个事务中的所有操作是一个整体，不可再分割。事务中的所有操作要么成功，要么都失败。
B：Consistency --> 一致性
一个用户操作了数据，提交以后另一个用户看到的数据与之前用户看到的数据效果是一致的。
I：Isolation --> 隔离性 --> 事务隔离级别
指的是多个用户并发访问数据库时，一个用户操作数据库，另一个用户不能有所干扰，多个用户之间的数据事务操作要互相隔离。
D：Durability --> 持久性
指的是一个用户操作数据的事务一旦被提交（缓存–>文件）他对数据库底层真实的改变是永久性的，不可返回。

  1. 开启一个事务

    • 每一次执行的一条sql语句之前mysql数据库都会默认开启
    • 事务中的所有操作要么都成功，要么都失败
    • begin手动开启事务
    • start transaction;和beigin一样

  2. 执行操作

    • insert update delete
    • select
    • 可能不止一条语句

  3. 事务的处理

    • 提交/回滚/保存还原点
    • mysql数据库会默认执行提交事务

[!info]
mysql数据库事务管理默认的效果是可以更改的

  • [>] 更改mysql数据库事务管理状态
1
2
3
4
5
6
7
8
9
10
# 自动提交事务变量
autocommit变量=ON

# 查看自动提交事务变量
show variables like %commit%;

# 设置自动提交事务关闭
set autocommit = off;

# 我们可以使用手动的开启事务和提交事务，覆盖掉默认的事务管理
1
2
3
4
5
6
7
8
9
10
# 创建表格
create table test_bank(
	aname int(4),
	apassword varchar(20),
	abalance int(4)
);
# 添加主键
alter table test_bank add primary key(aname);
# 插入一条数据
insert into test_bank values(1,'alvis', 1000);
1
2
3
4
5
6
7
8
# 事务的原子性
# 事务中的所有操作要么都成功，要么都失败
start transaction; # 开启事务
insert into test_bank values(4,'lisi', 4000), (1,'wangwu', 5000); # 执行操作，注意1这个主键已经存在改代码会报错
commit;

select * from test_bank; # 当其中一条sql报错的时候，所有的数据都插入不进去

image.png

image.png

1
2
3
4
5
# 事务的一致性
# 一个用户操作了数据，提交以后另一个用户看到的数据与之前用户看到的数据效果是一致的。
start transaction;  # 开启事务
insert into test_bank values(4, 'lisi', 4000); # 插入数据
commit; # 提交事务

image.png

事务隔离级别

级别 解释
Serializable 最高，可以避免所有问题，性能慢
Repeatable Read 可重复读（mysql默认管理级别）
Read Committed 读已提交
Read UnCommitted 读取未提交

级别低的会产生问题

  • 脏读
    • 一个人读到了另外一个人还没有提交的数据
    • [!] A B在操作同一张表，A修改了数据没有提交，B读取到了。A不提交了，回滚回来。B读取到的数据就是无用数据
  • 不可重复读
    • [!] A B在操作同一张表格，A先读取了一些数据，读完之后B在此时将数据做了修改/删除。A在按照之前的条件重新读一遍，与第一次读取的不一致
  • 幻读（虚读）
    • [!] A B 操作同一个表格，A先读取了一些数据，读完之后B在此时将数据做了新增，A在按照之前的条件重新读一遍，与第一次读取的不一致

修改隔离级别

1
set session transaction isolation level xxx;
1
2
set session transaction isolation level read uncommited;
select @@tx_isolation; # 查看隔离级别

image.png

1
2
3
4
5
6
7
8
# 脏读
A : 将abalance=1000;更改为10;在后在rollback
start transaction;
update test_bank set abalance = 10 where aname = 1; 
rollback;

B : 在A更改之后查询表格数据
select * from test_bank;  # 会脏数据读出来

read committed解决脏读

1
2
3
4
5
6
7
8
9
10
11
12
A、B:都设置隔离等级
set session transaction isolation level read committed;
select @@tx_isolation; # 查看隔离级别


A:将abalance=1000;更改为10;在后在rollback
begin;
update test_bank set abalance = 10 where aname = 1; 
rollback;

B:在A更改之后查询表格数据
select * from test_bank;  # 发现如果A事务没有提交的话，B在查数据的时候不会受到影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 不可重复读
A:将abalance=1000;更改为10;事务不进行提交
begin;
update test_bank set abalance = 10 where aname = 1; 


B：在A更改数据之后，但是在提交事务之前，B用户开启事务查询表格
begin;
select * from test_bank; # 数据没有发生变化，因为A用户没有提交

A: 
commit;  # 将A事务提交了

B：将数据查询出来了，查出来的时候是A变更之后的
select * from test_bank;  # B还是同一个事务，但是这此查出来的和之前查出来的不一样，因为A事务更改了

repeatable read解决不可重复读

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
set session transaction isolation level repeatable read;

A:将abalance=1000;更改为10;事务不进行提交
begin;
update test_bank set abalance = 10 where aname = 1; 

B：在A更改数据之后，但是在提交事务之前，B用户开启事务查询表格
begin;
select * from test_bank; # 数据没有发生变化，因为A用户没有提交

A: 
commit;  # 将A事务提交了 数据彻底发生了变化

B：将数据查询出来了，查出来的时候是A变更之后的
select * from test_bank;  # B还是同一个事务，查询结果还是第一次的结果，没有被A事务影响