SQL学习

SQL概述

数据库概念

DB
数据库(database):存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统(Database Management System)。数据库是通过DBMS创 建和操作的容器
SQL
结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。

SQL的优点:支持度高,简单易学(假的,没那么简单),灵活

SQL语言分类

1、DML(Data Mainipulation language):数据操纵语句,用于添 加、删除、修改、查询数据库记录,并检查数据完整性
2、DDL(Data Definition language):数据定义语句,用于库和 表的创建、修改、删除
3、DCL(Data Control language):数据控制语句,用于定义用 户的访问权限和安全级别

SQL安装与使用

MySQL版本

Windows平台下下载:http://dev.mysql.com/downloads/mysql

目前可以下载的版本:
5.5 5.6 5.7 8.0 测试版

解压安装后,环境变量设置Path为(与自己的版本号相对应)

C:\MySql\mysql-8.0.16-winx64\bin

接下来……

手动创建my.ini文件

位置: D:\MyConfiguration\用户名\Downloads\mysql-5.7.25-winx64

内容:
[mysqld]
port = 3306
basedir=D:/MyConfiguration/username/Downloads/mysql-5.7.25-winx64
datadir=D:/MyConfiguration/username/Downloads/mysql-5.7.25-winx64/data
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysql]
default-character-set=utf8
注意:basedir和datadir中的路径是”/“,写成 “ \ “ 可能引起不必要的错误。

以管理员身份运行cmd (Windows10系统可以右键点击屏幕左下角的WIN图标,点击Windows PowerShell(管理员)直接运行。)
切换到MySQL 安装目录的bin目录下,执行mysqld -install 命令(如果环境变量配置好了就不用切换了)

1563275771086

出现”Service successfully installed.” –>成功

PS:如果出现”Install/Remove of the Service Denied!”,请看一下你是不是在管理员模式

初始化(还是在bin目录下)

1
mysqld --initialize-insecure --user=mysql

目录里生成data目录并创建root用户
启动mysql服务
net start mysql
服务启动成功

目录里生成data目录并创建root用户
启动mysql服务
net start mysql
服务启动成功

进入mysql环境
mysql服务启动成功后,
mysql -u root -p
提示Enter password:(8.0版本直接敲回车就ok)

修改root用户密码:(一行一行来)

1
2
3
4
5
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码' PASSWORD EXPIRE NEVER;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

FLUSH PRIVILEGES;

登录

1
mysql –h 主机名 –u用户名 –p密码

退出

1
exit

MySQL的语法规范

不区分大小写
每句话用;或\g结尾
各子句一般分行写
关键字不能缩写也不能分行
用缩进提高语句的可读性

(图形化界面使用Navicat for MySQL,支持正版!!!如果不想花钱就走流程

数据处理

基本select语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
use nh;   /*使用特定的数据库*/

use myemployees;
show tables;
select * from jobs; /*查询所有*/

/*创建test01表,第一列为id,类型为int,第二列为name,类型为varchar*/
create table test01(id int , name varchar(32));

/*查看表中每一列的信息,包括名称,类型,是否允许为null,key值,默认值等*/
desc jobs;

/*删除表test01(不可恢复,谨慎操作)*/
drop table test01;

过滤和排序数据

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
use myemployees;
show tables;

select * from jobs;

/*where语句表示限定范围*/
select job_id , job_title , min_salary from jobs where min_salary > 5000;

/*选择出employee中的所有数据*/
select * from employees;

/*插入一条id为1,name为hyh的数据*/
insert into test01(id , name) values(1 , "hyh");

/*修改test01表中的数据*/
update test01 set name = "hyhnb" where id = 1;

/*主键为id 主键唯一且不能为空 primary key*/
create table test02(id int , name varchar(32) , primary key(id));

create table test03(id int primary key , name varchar(32));

/*auto_increment自动升序*/
create table test04(id int primary key auto_increment, name varchar(32));

/*插入数据*/
insert into test04(id , name) values(1 , "hyh");

insert into test04(name) values("hyhqiangqiangqiang");

delete from test04 where id = 2;

/*给查询出来的那一列取个别名*/
select salary 别名 from employees;

select salary "别名" from employees;

select salary as 别名 from employees;

/*选择特定范围并输出*/
select last_name , salary from employees
where salary > 10000;

select last_name , salary from employees
where salary > 2000 and salary < 3000;

select last_name , salary from employees
where salary between 2000 and 3000;

/*工资为1000 2000 3000 3100 的人的全部信息*/
select * from employees
where salary in (1000,2000,3000,3100);

/*模糊查询 查询表中名字内含有o字母的所有员工信息*/
/*%表示o的前面和后面的值都是任意的,表示是否含有o字母*/
select last_name from employees
where last_name like "%o%";

/*模糊查询 查询表中名字内第二个字母为o的所有员工信息*/
select last_name from employees
where last_name like "_o%"; /*_占位符*/
/*重要说明,如果想要查询_,可以使用\进行转义*/

/*输出commission_pct中为非空值的所有员工信息*/
select * from employees
where commission_pct is not null;

/*按salary排序*/
select last_name , salary from employees
where salary > 10000
order by salary; /*默认为升序 asc也表示这个意思*/

select last_name , salary from employees
where salary > 10000
order by salary desc; /*降序*/

/*排序 谁在前以谁为主,如果salary相等 就以后面的那个排*/
select department_id , last_name , salary from employees
where salary > 10000
order by salary desc, department_id desc;

/*and为且操作*/
select * from employees
where salary > 10000
and job_id like "%man%";

/*or为或操作*/
select * from employees
where salary > 10000
or job_id like "%man%";

/*字符串同样可以使用 not in 操作*/
select job_id , last_name , salary from employees
where job_id not in("IT_PROG" , "ST_CLERK" , "SA_REP");

/*计算员工年薪 并按年薪排序,部门id降序排列*/
/*其中ys为简称,可以改变输出的纵轴的索引 as 可有可无*/
select last_name , salary*12 as ys,department_id from employees
order by ys , job_id desc;

/*注意:where字句 中不能使用别名*/
select last_name from employees
where last_name like "%\_%"; /*_占位符*/

组函数

语法:(使用需要使用的,需要注意顺序)

1
2
3
4
select xxx from xxx
where xxx
group by xxx
order by xxx;

非法使用组函数的一些注意事项

不能在 where 子句中使用组函数。
可以在 having 子句中使用组函数(一般与group by 联合使用,先group by 后having)。

一些例子:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
/*显示降序排序后的前1行
(limit n 操作 后面为一个数字的时候为从第一条开始后的n条)*/
select salary , last_name from employees
order by salary desc limit 1;

/*salary中的最大值*/
select max(salary) from employees;

/*平均值,最大值,最小值,求和,最后一个salary可以用*来代替,count()表示行数*/
select avg(salary) a ,max(salary) mx , min(salary) mn , sum(salary) sm, count(salary) c from employees;

/*job_id 为字符串,他们的大小按字母顺序来算,首字母相等的话按之后的字符来排,依次类推*/
select min(job_id) , max(job_id) from employees;

select min(hiredate) , max(hiredate) from employees;

/*求出employees表中各部门的平均工资 group by*/
/*注意:group by 后的字段不能在上面使用组函数*/
select department_id , avg(salary) from employees
group by department_id;

/*先按group by后面的第一个分组,在每组中按job_id分组*/
select department_id , job_id , avg(salary) from employees
group by department_id, job_id
order by department_id;

/*where后不能使用组函数,但是having能*/
select department_id , max(salary) from employees
where max(salary) > 15000
group by department_id;

/*部门最高工资比 10000 高的部门 group by 1.每个部门的最高工资 2.having (一般和group by 连着使用) */
select department_id , max(salary) from employees
group by department_id
having max(salary) > 10000
order by department_id;
select beauty.name , boys.boyName from beauty , boys
where beauty.boyfriend_id = boys.id;

/*变成小写*/
select lower(last_name) from employees;

/*变成大写*/
select upper(last_name) from employees;

/*字符串拼接作为列索引显示*/
select concat(first_name , "_" , last_name) name from employees;

/*截取字符串前三个的字母substring*/
select last_name , substr(last_name , 1 , 3) from employees;

/*字符串长度*/
select first_name , email , length(email) from employees;

/*字符指定长度,不够补特定字符,不够就截取,往左补*/
select lpad(salary , 4 , "*") from employees;
select lpad(salary , 10 , "*") from employees;

/*往右补*/
select rpad(salary , 4 , "*") from employees;
select rpad(salary , 10 , "*") from employees;

/*字符指定长度,不够补特定字符,不够就截取,往左补*/
select lpad(salary , 4 , "*") from employees;
select lpad(salary , 10 , "*") from employees;

/*往右补*/
select rpad(salary , 4 , "*") from employees;
select rpad(salary , 10 , "*") from employees;

/*去除空格 只去除首尾的空格,中间不去*/
select trim(" A B C D A D C B A ");
/*去除空格 只去除首空格,中间不去*/
select ltrim(" A B C D A D C B A ");
/*去除空格 只去除尾空格,中间不去*/
select rtrim(" A B C D A D C B A ");

/*默认为both*/
select trim("A" from"A B C D A D C B A");
/*去首尾所有所选字符,直到遇到的不是这个字符为止*/
select trim(both "A" from "A B C D A D C B A");
/*去首所有所选字符,直到遇到的不是这个字符为止*/
select trim(leading "A" from "AAAA B C D A D C B A");
/*去尾所有所选字符,直到遇到的不是这个字符为止*/
select trim(trailing "A" from "A B C D A D C B AAAA");

/*将前面字符串中的中间的的字符串全部替换成后面的那个字符*/
select replace("HelloWorld" , "o" , "O");
select replace("HelloWorld" , "Hello" , "X");

/*四舍五入,最后的2表示2位小数*/
select round(123.456 , 2);
select round(123.455 , 2);
select round(123.454 , 2);

/*截取两位小数 不舍不入 2与-2表示从小数点开始的偏移量,正数表示向右数,负数表示向左数(例如第二个,显示结果为12300)*/
select truncate(12345.67890 , 2);
select truncate(12345.67890 , -2);

/*求前面那个数字除以后面那个数的余数*/
select mod(100 , 40);

/*获取当前日期时间*/
select now();

/*字符串->日期*/
select str_to_date("2019-07-16" , "%Y-%m-%d");

/*日期->字符串*/
select date_format("2018/6/6" , "%Y年%m月%d日");

/*去除空格 去除首尾的空格,中间不去*/
select trim(" A B C D A D C B A ");
/*去除空格 去除首空格,中间不去*/
select ltrim(" A B C D A D C B A ");
/*去除空格 去除尾空格,中间不去*/
select rtrim(" A B C D A D C B A ");

select trim("A" from"A B C D A D C B A"); /*默认为both*/
select trim(both "A" from "A B C D A D C B A");/*去首尾*/
select trim(leading "A" from "AAAA B C D A D C B A");/*去首*/
select trim(trailing "A" from "A B C D A D C B AAAA");/*去尾*/

select replace("HelloWorld" , "o" , "O"); /*将前面的那个全部替换成后面的那个*/
select replace("HelloWorld" , "Hello" , "X"); /*将前面的那个全部替换成后面的那个*/

select round(123.456 , 2); /*四舍五入*/
select round(123.455 , 2); /*四舍五入*/
select round(123.454 , 2); /*四舍五入*/

select truncate(12345.67890 , 2); /*截取两位小数 不舍不入*/
select truncate(12345.67890 , -2); /*截取两位小数 不舍不入*/

select mod(100 , 40); /*求余数*/

/*获取当前日期时间*/
select now();
/*字符串->日期*/
select str_to_date("2019-07-16" , "%Y-%m-%d");

/*日期->字符串*/
select date_format("2018/6/6" , "%Y年%m月%d日");

/*练习:查询部门号为 10, 20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门打印其工资的 1.3 倍。*/

select department_id , salary ,
case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
else salary
end ss
from employees;

多表查询

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
/*使用表的别名需要把所有使用表的地方换成表的别名*/
/*但是使用别名有个小缺点,就是按.不能自动补全,希望之后的版本可以加上这个人性化的操作*/
select bt.name , b.boyName from beauty bt, boys b
where bt.boyfriend_id = b.id;

/*内连接 [inner] join on 最后显示交集*/
select beauty.name , boys.boyName from beauty
/*inner*/ join boys
on beauty.boyfriend_id = boys.id; /*连接条件*/

/*左外连接 left outer join 之前的为左,之后的为右 显示左右交集和左*/
select beauty.name , boys.boyName from beauty /*左*/
left outer join boys /*右*/
on beauty.boyfriend_id = boys.id;

/*右外连接 显示左右交集和右*/
select beauty.name , boys.boyName from beauty
right outer join boys
on beauty.boyfriend_id = boys.id;

/*查询出公司员工的last_name , department_name , city*/
/*n个表相连接至少需要n-1个条件*/
select last_name , department_name , city from employees
inner join departments
on departments.department_id = employees.department_id
inner join locations
on locations.location_id = departments.location_id;

/*显示左侧去除交集*/
select beauty.name , boys.boyName from beauty
left outer join boys
on beauty.boyfriend_id = boys.id
where beauty.boyfriend_id is null;

/*显示右侧去除交集*/
select beauty.name , boys.boyName from beauty
right outer join boys
on beauty.boyfriend_id = boys.id
where beauty.name is null;

/*三重集合*/
select beauty.name , boys.boyName from beauty
left outer join boys
on beauty.boyfriend_id = boys.id
union /*将前面语句返回的表去重*/
select beauty.name , boys.boyName from beauty
right outer join boys
on beauty.boyfriend_id = boys.id

子查询(十分重要)

概念:出现在其他语句内部的select语句,称为子查询或内查询。内部嵌套其他select语句的查询,称为外查询或主查询。

注意事项

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询,多行操作符对应多行子查询

解决思路:

由内到外,分布解决。

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
/*查找谁的工资比Abel高*/
select salary from employees where last_name = "Abel";

select last_name , salary from employees
where salary > (select salary from employees where last_name = "Abel");

/*返回job_id与141号员工相同,salary比143号员工多的员工的姓名 job_id 工资*/
select job_id from employees where employee_id = 141;
select salary from employees where employee_id = 143;

select last_name , job_id , salary from employees
where job_id = (select job_id from employees where employee_id = 141)
and salary > (select salary from employees where employee_id = 143);

/*返回工资最少的员工的信息*/
select last_name , job_id , salary from employees
where salary = (select min(salary) from employees);

/*查询最低工资>40号部门最低工资的部门id和其最低工资*/

select min(salary) from employees
where department_id = 40
group by department_id;

select department_id , min(salary) sa from employees
group by department_id
having sa > (select min(salary) from employees
where department_id = 40);

/*返回location_id是1400或者1700的部门中的所有员工姓名*/
select last_name from employees
where department_id in (select department_id from departments where location_id in(1400, 1700));

/*返回其他部门中比job_id为"IT_PROG"部门任一工资低的员工的员工号 姓名 job_id salary*/
select employee_id , last_name , job_id , salary from employees
where department_id not in (select department_id from employees where job_id = "IT_PROG")
and salary < any(select salary from employees where job_id = "IT_PROG");

/*返回其他部门中比job_id为''IT_PROG"部门所有工资低的员工的员工号 姓名 job_id salary*/
select employee_id , last_name , job_id , salary from employees
where department_id not in (select department_id from employees where job_id = "IT_PROG")
and salary < all(select salary from employees where job_id = "IT_PROG");

创建和管理表

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
49
50
51
52
53
/*如果存在就销毁掉*/
drop table if exists t;

/*创建表*/
create table t(id int auto_increment key , name varchar(32) , email char);

/*删除表t*/
drop t;

create table t as select * from employees;
create table t as select * from employees where 1 = 2; /*不复制数据*/

/*复制表 1.like复制表结构 2.insert 插入数据*/
create table t like employees;
insert into t select * from employees;
insert into t select * from employees where department_id = 10;

/*添加列*/
alter table t add pwd varchar(32) default 0;
/*修改列 数据类型 尺寸 默认值*/
alter table t modify pwd char(12) default 100;
/*删除一个列*/
alter table t drop pwd;
/*重命名一个列*/
alter table t change email mail varchar(24);

/*删除表,数据和表结构都会被删除,不能回滚*/
drop table t;

/*清空表,删除数据但是不删除索引,无法恢复*/
truncate table t;

select * from t;

/*sql语句是否被写入到数据库的成功标志是此条sql语句的事物是否成功提交*/
begin;
select * from t;
delete from t;
select * from t;
rollback;
select * from t;
commit;

/*表示一个事务,只有事务结束才对表进行写入操作 begin 开始 commit 结束*/
begin;
select * from t;
delete from t;
select * from t;
commit;

alter table t rename to ttt;

select * from t;

数据类型

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
drop table if exists t;

/*默认有符号int*/
create table t(id tinyint(10));
insert into t values (100);
insert into t values (-128);
insert into t values (128); /*越界*/
delete from t where id = -128;

/*无符号int*/
alter table t modify id tinyint(10) unsigned zerofill;
insert into t values (255);


create table t(id int(10));
insert into t values(100);

/*float 从第6位有效位开始四舍五入存储 double 从第15位*/
create table t(id float(5 , 3));/*5 代表总的位数 3代表小数点后的位数*/
create table t(id float(8));
insert into t values(123);
insert into t values(123456);
insert into t values(123456789);
insert into t values(123456789.123456789);
insert into t values(1.23456789);

create table t(id double(7 , 3));/*5 代表总的位数 3代表小数点后的位数*/

insert into t values(123);
insert into t values(123456);
insert into t values(123456789);
insert into t values(123456789.123456789);
insert into t values(1.23456789);

create table t(id double);/*5 代表总的位数 3代表小数点后的位数*/

insert into t values(123);
insert into t values(123456);
insert into t values(123456789);
insert into t values(123456789.123456789);
insert into t values(1.23456789);

create table t(id decimal(10 , 3));/*5 代表总的位数 3代表小数点后的位数*/

insert into t values(123);
insert into t values(123456);
insert into t values(123456789);
insert into t values(12.123456789);
insert into t values(1.23456789);

create table t (id int auto_increment primary key , name varchar(32));
insert into t(name) values(123);
insert into t(name) values("xiaoming");
insert into t(name) values("xiao\'ming"); /*可以使用转义字符\对想要输出的特殊字符进行转义*/
/*insert into t(name) values("xiao\"hong");*/

/*二进制与十六进制显示*/
create table t(id bit(4));
insert into t values(4);
insert into t values(15);
select bin(id) , hex(id) from t;

create table t
(id int auto_increment primary key ,
sex enum("male" , "female") , /*enum 单选*/
hobby set("singing" , "jumping" , "rap" , "basketball")); /*set 多选*/
insert into t(sex , hobby) values("male" , "singing,jumping,rap,basketball");
insert into t(sex , hobby) values(1 , 1);
insert into t(sex , hobby) values(2 , 2);
insert into t(sex , hobby) values(2 , 3);
/*enum 1 2 3 4 , set 1 2 4 8 16...*/

create table t(sj date);
insert into t values(19900101);
insert into t values(1990101);
insert into t values(2019111);

create table t(sj datetime);
insert into t values(19900101001100);
insert into t values(19900101);
insert into t values(2019111);

drop table if exists t;

select * from t;

约束与分页

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
/*not null*/
create table t_user(id int primary key auto_increment , name varchar(32) not null , age int);

insert into t_user(name , age) values("" , 10); /*空字符串不是null*/

insert into t_user(age) values(10);
insert into t_user(name) values("Jack");

select * from t_user;

/*唯一性约束 unique*/
drop table if exists t_user;
create table t_user(id int primary key auto_increment , name varchar(32), email varchar(32) unique);

insert into t_user(name , email) values("Jerry" , "1234@qq.com");
insert into t_user(name , email) values("Tom" , "1234@qq.com");

create table t_user(id int primary key auto_increment , name varchar(32), email varchar(32) , unique(name , email));

/*同时一样就不能插入了,有一个一样仍然可以插入*/
insert into t_user(name , email) values("Tom" , "1234@qq.com");
insert into t_user(name , email) values("Jack" , "1234@qq.com");
insert into t_user(name , email) values("Jerry" , "8888@qq.com");
insert into t_user(name , email) values("Jerry" , "8888@qq.com");
select * from t_user;

create table t_user(id int primary key auto_increment ,
name varchar(32), email varchar(32) ,constraint t_user_n_e unique(name , email));

insert into t_user(name , email) values("Tom" , "1234@qq.com");
insert into t_user(name , email) values("Jack" , "1234@qq.com");
insert into t_user(name , email) values("Jerry" , "8888@qq.com");

/*表在设计时一定要有约束*/
create table t_user(id int ,
name varchar(32), email varchar(32) ,constraint t_user_pk primary key(id , name));

insert into t_user(id , name) values(1 , "张三");
insert into t_user(id , name) values(1 , "李四");
insert into t_user(id , name) values(2 , "李四");
insert into t_user(id , name) values(2 , "李四");
drop table if exists t_user;
select * from t_user;

drop table if exists t_student;
drop table if exists t_class;
create table t_class(cno int(10) primary key , cname varchar(128) not null unique);

/*外键*/
/*foreign key(classno) references t_class(cno)); 级联删除*/
create table t_student(sno int(10) primary key auto_increment,
sname varchar(32) not null , classno int(3),
foreign key(classno) references t_class(cno) on delete cascade);

create table t_student(sno int(10) primary key auto_increment,
sname varchar(32) not null , classno int(3),
foreign key(classno) references t_class(cno) on delete set null);

select * from t_class;
select * from t_student;

insert into t_class(cno , cname) values(100 , "xxx");
insert into t_class(cno , cname) values(200 , "xxx1");
insert into t_student(sname , classno) values("jack" , 100);
insert into t_student(sname , classno) values("lucy" , 100);
insert into t_student(sname , classno) values("king" , 200);

select s.* , c.* from t_student s
inner join t_class c
on s.classno = c.cno;

delete from t_class where cno = 100;

/*9:从第x个数据的下一条数据开始取 10:取y条数据*/
select * from employees limit 9 , 10;
select * from employees limit 10 , offset 10;

/*pageSize:每页显示多少条数据 pageNumber:页码*/
/*返回第pageNumber页 每页条数为pageSize的sql语句为
select * from xxx limit (pageNumber - 1)*pageSize , pageSize
*/

/*返回第5页,每页数据为20条的数据*/
select * from employees limit 80 , 20;

数据库的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没 有采取必要的隔离机制, 就会导致各种并发问题:
脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔 离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就 越好, 但并发性越弱.

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每 个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的 事务隔离级别.
查看当前的隔离级别:

1
SELECT @@tx_isolation;

设置当前 mySQL 连接的隔离级别:

1
set transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

1
set global transaction isolation level read committed;