MySQL的学习

1.1简介

MdSQL是一个关系型数据库管理系统，由瑞典MySQLAB公司开发，属于Oracle旗下产品。MysQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的 RDBMS(Relational Database Management System，关系数据库管理系统)应用软件之一。

1.2访问与下载

官方网站:https://www.mysql.com/
下载地址:https://dev.mysql.com/downloads/mysql/

1.3配置环境变量

• 创建MYSQL_HOME: C:\Program Files\MySQLMySQL Server 5.7
• 追加PATH :9%MYSQL_HOME%\bin;

1.4MySQL目录结构

核心文件介绍

文件夹名称
bin	命令文件
lib	库文件
include	头文件
Share	字符集、语言等信息

1.5MySQL配置文件

参数	描述
default-character-set	客户端默认字符集
character-set-server	服务器端默认字符集
port	客户端和服务器端的端口号
default-storage-engine	MySQL默认存储引擎INNODB

SQL语言

2.1概念

​ sQL (Structured Query Language)结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

• 经验︰通常执行对数据库的“增删改查"，简称c(Create)R (Read) u (update) D (Delete)。

2.2MySQL应用

对于数据库的操作，需要在进入MySQL环境下进行指令输入，并在一句指令的末尾使用;结束

2.3基本命令

查看MysQL中所有数据库

mysql>SHOW DATABASES;#显示当前MySQL中包含的所有数据库

默认存在的数据库

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息(元数据)。 元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql	核心数据库，主要负责存储数据库的用户、权限设置、关键字等, 以及需要使用的控制和管理信息,不可以删除。
performance_schema	性能优化的数据库，MySQL 5.5版本中新增的一个性能优化的引擎。
sys	系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库 便于发现数据库的多样信息,解决性能瓶颈问题。

创建自定义的数据库

create database mydb1; #创建数据库
create database mydb2 character set gbk; #设置编码格式为gbk
create database if not exists mydb4;#如果不存在则创建，如果存在则不创建

查看数据库创建信息

show databases;#查看所有存在的数据库
show create database mydb2;#查看创建数据库时的基本信息

修改数据库

alter database mydb2 character set utf8;#修改数据库的编码格式

删除数据库

drop database mydb2;

查看当前所使用的数据库

select database();

使用数据库

use mydb1;#使用mydb1数据库

数据表操作

3.1数据类型

mysql 支持多种类型，大致可以分为三类：数值、日期/和字符串（字符）类型。对于我们约束数据的类型有很大帮助。

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
int	4字节	(-2147483648，2147483647)	(0，4 294967295)	大整数值
double	8字节	(-1.797E+308,-2.22E-308)	(0,2.22E-308,1.797E+308)	双精度浮点数值
double（M，D)	8字节，M表示长度，D表示小数位数	同上，受M和D的约束DOUBLE(5,2)-999.99-999.99	同上，受M和D的约束	双精度浮点数值
decimal(M,D)	Decimal（M,D）	依赖M和D的值，M最大值为65	依赖于M和D的值,M最大值为 65	小数值

3.2日期类型

类型	大小	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYY-MM-DD	日期值
time	3	"-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901/2155	YYY	年份值
datetime	8	1000-01-01 00:00:00/9999-12-3123:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00/2038结束时间是第2147483647秒北京时间2038-1-19 11:14:07，格林尼治时间2038年1月19日凌晨03:14:07	YYYYMMDDHHMMSS	混合时间，时间值，时间戳

字符串类型

类型	大小	用途
char	0-255字符	定长字符串char(10)10个字符
varchar	0-65535字节	变长字符串varchar(10)10个字符
blob(binary large object)	0-65535字节	二进制形式的长文本数据
text	0-65535字节	长文本数据

• CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同
  在存储或检索过程
  中不进行大小写转换。
• BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

数据表的创建（CREATE）

CREATE TABLE 表名（

列名数据类型[约束],

列名数据类型[约束],

…
列名数据类型[约束]//最后一列的未尾不加逗号)[charset=utf8]//可根据需要指定表的字符编码集

3.3创建表

列名	数据类型	说明
subjectId	int	课程编号
subjectName	varchar(20)	课程名称

#依据上述表格创建数据表,并向表中插入3条测试语句
CREATE TABLE subject(
subjectId INT,
subjectName VARCHAR(20) 
) charset=utf8 ;
#插入一条数据
INSERT INTO subject(subjectId, subjectName) VALUES(1 , ' Java' );

3.4数据表的修改（ALTER）

ALTER TABLE表名操作;

向现有表中添加列

#在课程表基础上添加gradeId列
ALTER TABLE subject ADD gradeId int;

修改表中的列

#修改课程表中课程名称长度为18个字符
ALTER TABLE subject MODIFY subjectName VARCHAR(10);

注意:修改表中的某列时，也要写全列的名字，数据类型,约束

删除表中的列

#删除课程表中gradeId列
ALTER TABLE subject DROP gradeId;

注意:删除列时，每次只能删除一列

修改列名

#修改课程表中subjectHours列为classHours
ALTER TABLE subject CHANGE subjectHours classHours int ;

• 注意:修改列名时,在给定列新名称时，要指定列的类型和约束

修改表名

#修改课程表的subject为sub
ALTER TABLE subject rename sub;

3.5数据表的删除（DROP）

DROP table 表名

删除学生表

#删除学生表
DROP TABLE subject;

DML操作

4.1新增（insert）

insert into 表名（列1，列2，列3…)values(值1，值2，值3…);

添加一条信息

insert into t_employee(pid,pname) values (1,'jack');
#注意：表名后的列名和values里的值要一一对应（个数 顺序 类型）

4.2修改（update）

UPDATE表名 SET列1=新值1,列2=新值2,…WHERE条件;

#修改编号为100的员工的工资为25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';
#注意:SET后多个列名=值，绝大多数情况下都要加WHERE 条件，指定修改，否则为整表更新

4.3删除（delete）

DELETE FROM表名WHERE条件;

删除一条信息

#删除编号为135的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135';
#注意:删除时,如若不加WHERE条件，删除的是整张表的数据

4.4清空整表数据（truncate）

truncate table 表名；

清空整张表

#清空t_countries整张表
TRUNCATE TABLE t_countries;

注意∶与DELETE 不加WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表

数据查询

数据库表的基本结构

• 关系结构数据库是以表格(Table)进行数据存储，表格由“行"和“列"组成

• 经验:执行查询语句返回的结果集是一张虚拟表。

5.1基本查询

语法：SELECT 列名 FROM 表名

关键字	描述
SELECT	指定要查询的列
FROM	指定要查询的表

查询部分列

#查询员工表中所有员工的编号、名字
select pid,pname
from t_employees;

查询所有列

#查询员工表中所有员工的所有信息（所有列）
select 所有列的列名 from t_employess; 
select *from t_employess;

注意：生产环境下，优先使用列名查询。*的方式需转换成全列名，效率低可读性差。

对列中的数据进行运算

#查询员工表中所有员工的编号、名字、年薪
select pid,pname salary*12 from t_employeess;

算数运算符	描述
+	两列做加法运算
-	两列做减法运算
*	两列做乘法运算
/	两列做除法运算

注意：%是占位符，而非模运算符。

列的别名

​ 列 as ’ 列名 ‘

#查询员工表中所有有员工编号和名字
select pid as "编号"，pname as "名字" from t_employees;

查询结果去重

distinct 列名

#查询员工表所有经理的ID
select distinct manager_id 
from t_employees;

5.2排序查询

语法：select 列名 from 表名 ORDER BY 排序列[排序规则]

排序规则	描述
ASC	对前面排序列做升序排序
DESC	对前面排序做降序排序

依据单列查询

#查询员工的编号，名字,薪资。按照工资高低进行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC;

依据多列排序

#查询员工的编号，名字，薪资。按照工资高低进行升序排序(薪资相同时，按照编号进行升序排序)
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;

5.3条件查询

语法：select 列名 from 表名 where 条件

关键字	描述
where条件	在查询结果中，筛选符合条件的查询结果，条件为布尔表达式

等值判断

#查询薪资是100的员工信息（编号，姓名）
select pid,pname from t_employee where salary = 100;

注意：与java不同（==），mysql中等值判断使用=；

逻辑判断（and or not）

#查询薪资是100并且提成是0.30的员工信息（编号、姓名）
select pid,pname from t_employee
where salary = 100 and pct =0.30;

不等值判断(>,<,<=,>=、!=、<>)

#查询薪资是100到200的员工信息（编号、姓名）
select pid,pname from t_employee
where salary >= 100 and  salary<= 200;

区间判断

#查询薪资是100到200的员工信息（编号、姓名）
select pid,pname 
from t_employee
where salary between 100 and 200;

注意：在区间判断时小值在前大值在后否则得不到正确结果

NULL值判断

• is null

  列名 is null

• is not null

  列名 is not null

#查询没有提成的员工信息
select  pid,pname 
from t_employee
where pct is null;

枚举判断（in（值1，值2，值3））

#查询标号为1 2 3的员工信息
select pid,pname 
from t_employee
where pid in(1,2,3);

注意：in的查询效率较低，可通过多条件拼接。

模糊查询

• like_(单个任意字符)

  列名 like ‘张_’

• like%(任意长度的任意字符)

  列名like ‘张%’

注意：模糊查询只能和like关键字结合使用。

#查询名字以"L"开头的员工信息（编号，名字，薪资，部门编号)
SELECT employee_id , first_name , salary , department_idFROM t_employees
WHERE first_name LIKE ‘L%’;
#查询名字以"L"开头并且长度为4的员工信息(编号，名字，薪资，部门编号)
SELECT employee_id , first_name , salary , department_idFROM t_employees
WHERE first_name LIKE ‘L---';

分支结构查询

CASE
WHEN条件1 THEN结果1
WHEN条件2 THEN结果2
WHEN条件3 THEN结果3 ELSE结果
END

• 注意:通过使用CASE END进行条件判断，每条数据对应生成一个值。

• 经验:类似Java中的switch。

#查询员工信息(编号，名子，新贫，新资级别<对应条件表达式生成>)
SELECT employee_id , first_name , salary , department_id ,
CASE
   WHEN salary>=1000 THEN ‘A’
   WHEN salary>=808 AND salary<10808 THEN‘B‘
   WHEN salary>=608 AND salary<8080 THEN'C'
   WHEN salary>=400 AND salary<6088 THEND 'D'
ELSE 'E'
   END as "LEVEL"
FROM t_employees;

5.4时间查询

语法：select 时间函数（[参数列表]）

经验：执行时间函数查询，会自动生成一张虚表（一行一列）

时间函数	描述
sysdate()	当前系统时间（日、月、年、时、分、秒）
curdate()	获取当前日期
curtime()	获取当前时间
week(date)	获取指定日期为一年中的第几周
year(date)	获取指定日期的年份
hour(time)	获取指定时间的小数值
minute(time)	获取时间的分钟值
datediff(date1,date2)	获取date1和date2之间相隔的天数
adddate(date,n)	计算data加上N天后的日期

#查询当前时间
SELECT SYSDATE();
#查询当前时间
SELECT NOW();
#获取当前日期
SELECT CURDATE();
#获取当前时间
SELECT CURTIME();

字符串查询

语法：select 字符串函数（[参数列表]）；

字符串函数	说明
concat(str1,str2.str…)	将多个字符串连接
insert（str，pos,len,newstr)	将str中指定pos位置开始len长度的内容替换为newStr
lower(str)	将指定字符串转换为小写
upper(str)	将指定字符串转换为大写
substring(str,num,len)	将str字符串指定num位置开始截取len个内容

#拼接内容
SELECT CONCAT('My ' , 'S' , 'QL ');
#字符串替换
SELECT INSERT('这是一个数据库'，3,2,'MySql');#结果为这是MySq1 数据库
#指定内容转换为小写
SELECT LOWER( ' MYSQL) ; #mysql
#指定内容转换为大写
SELECT UPPER(' mysql");#MYSQL
#指定内容截取
SELECT SUBSTRING( " JavaMysQLOracle' ,5,5);#MySQL

5.5聚合函数

语法：select 聚合函数（列名） from 表名

经验：对多条数据的单例进行统计，返回统计后的一行结果。

聚合函数	说明
sum（）	求所有行中单列结果的总和
AVG（）	平均值
Max()	最大值
min()	最小值
count()	求总行数

单列总和

#统计所有员工每月的工资总和
select sum（salary）
from t_employee;

总行数

#统计员工总数
SELECT COUNT(*)FROM t_employees;
#统计有提成的员工人数
SELECT COUNT (commission_pct)FROM t_employees;
#注意：聚合函数自动忽略null值，不进行统计

5.6分组查询

语法：select 列名 from 表名 where 条件 group by 分组依据（列）；

关键字	说明
group by	分组依据，必须在where之后生效

查询各部门的总人数

#思路:
#1.按照部门编号进行分组(分组依据是department_id)
#2.再针对各部门的人数进行统计(count)
SELECT department_id , COUNT (employee_id)
FROM t_employees
GROUPBY department_id;

查询各部门的平均工资

#思路:
#1.按照部门编号进行分组(分组依据是department_id)
#2.再针对各部门进行平均工资统计（avg)
SELECT department_id , avg(salary)
FROM t_employees
GROUPBY department_id;

查询各部门，各个岗位的人数

#思路:
#1.按照部门编号进行分组(分组依据是department_id)
#2.按照岗位名称进行分组(分组依据是job_id)
#3.针对每个部门中各个岗位进行人数统计(count)
SELECT department_id , job_id , COUNT ( employee_id)FROM t_employees
GROUP BY department_id , job_id;

常见问题

#查询各个部门id、总人数、first_name
SELECT department_id , COUNT(*), first_name
FROM t_employees
GROUP BY department_id; #error

注意：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

5.7分组过滤查询

语法：select 列名 from 表名 where 条件 group by 分组列 Having过滤规则

关键字	说明
Having过滤规则	过滤规则定义后对分组后的数据进行过滤

统计部门的最高工资

#统计68、70、90号部门的最高工资#思路:
#1)．确定分组依据(department_id)
#2)．对分组后的数据，过滤出部门编号是6日、78、90信息#3). max()函数处理
SELECT department_id , MAX( salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in(60,70,90)
# group确定分组依据department_id#having过滤出68 7898部门
#select查看部门编号和ma×函数。

限定查询

select 列名 from 表名 limit 起始行，查询行数

关键字	说明
LIMIT offset_start，row_count	限定查询结果的起始行和总行数

查询前5行记录

#查询表中前五名员工的所有信息
select * from t_employee limit 0,5；

查询记录范围

#查询表从第四条开始，查询10行
select * from t_employee limit 3,10；

LIMIT典型应用（分页查询）

一页显示十条，一共查询三页；

#思路:第一页是从8开始,显示1日条SELECT * FROM LIMIT 8,10;
#第二页是从第10条开始,显示1日条SELECT * FROM LIMIT 1日,10;
#第三页是从20条开始,显示10条SELECT * FROM LIMIT 20,10;
经验:在分页应用场景中，起始行是变化的，但是一页显示的条数是不变的

查询总结

SQL语句编写顺序

SELECT列名FROM表名WHERE条件GROUP BY分组HAVING过滤条件ORDER BY排序列（asc(desc)LIMIT起始行，总条数

SQL语句执行顺序

• from：指定数据来源表
• where：对查询数据做第一次过滤
• group by:分组
• having：对分组后的数据第二次过滤
• select：查询各字段的值
• order by：排序
• LIMIT：限定查询结果；

5.8子查询（作为条件判断）

select 列名 from 表名 where 条件（子查询结果）

查询工资大于Bruce的员工信息

#1.先查询到Bruce的工资(一行一列)
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce ';#工资是6880
#2.查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6808 ;
#3.将1、2两条语句整合
SELECT * FROM t_employees WHERE SALARY >(SELECT SALARY FROM t.employees WHERE FIRSTMANE = 'Bruce');
注意:将子查询”一行一列“的结果作为外部查询的条件，做第二次查询
子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

5.9子查询（作为枚举查询条件）

SELECT列名 FROM表名 Where列名 in(子查询结果);

#思路:
#1,先查询'King’所在的部门编号(多行单列)SELECT department_id
FROM t_employees
WHERE lastname = 'King' ; //部门编号:80、90
#2．再查询80、9日号部门的员工信息
SELECT employee_id , first_name , salary , department_idFROM t_employees
WHERE department_id in ( 80,90);
#3.SQL:合并
SELECT employee_id , first_name , salary , department_idFROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = ‘King');#N行一列
将子查询”多行一列“的结果作为外部查询的枚举查询条件，做第二次查询

工资高于60部门所有人的信息

#1.查询6日部门所有人的工资(多行多列)
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
#2.查询高于6日部门所有人的工资的员工信息(高于所有)
select * from t_employees where SALARY > ALL(select SALARY from t_employees WHERE DEPARTMENT_ID=60);
#。查询高于6日部门的工资的员工信息(高于部分)
select * from t.employees where SALARY > ANY(select SALARY from t_employees WHERE DEPARTMNENT_ID=60);
注意:当子查询结果集形式为多行单列时可以使用ANY或ALL关键字

5.10子查询（作为一张表）

SELECT列名 FROM(子查询的结果集)WHERE 条件;

#思路:
#1．先对所有员工的薪资进行排序(排序后的临时表)select employee_id , first_name , salaryfrom t_employees
order by salary desc
#2．再查询临时表中前5行员工信息
select employee_id , first_name , salaryfrom(临时表)
limit 8,5;
#SQL:合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as templimit 0,5;
将子查询”多行多列“的结果作为外部查询的一张表，做第二次查询。
注意:子查询作为临时表，为其赋予一—个临时表名

5.11合并查询

• SELECTFROM表名1UNION SELECTFROM表名2
• SELECT * FROM表名1UNION ALL SELECT *FROM表名2

#合并两张表的结果,去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
注意:合并结果的两张表，列数必须相同，列的数据类型可以不同

#合并两张表的结果,不去除重复记录(显示所有)
SELECT *FROM t1 UNION ALL SELECT * FROM t2;
经验:使用UNION合并结果集,会去除掉两张表中重复的数据

5.12表连接查询

select 列名 from 表1 连接方式 表2 on 连接条件

内连接查询

#1.查询所有有部门的员工信息(不包括没有部门的员工)SQL标准
SELECT * FROM t_employees INNER JOIN t.jobs ON t_employees .JOB_ID = t.jobs .JOB_ID
#2.查询所有有部门的员工信息(不包括没有部门的员工) MYSQL
SELECT * FROM t emplovees.t iobs WHERE t emplovees .JOB ID = t iobs.JOB ID

• 经验:在MySql中，第二种方式也可以作为内连接查询，但是不符合SQL标准
• 而第一种属于sQL标准，与其他关系型数据库通用

三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家
IDSELECT * FROM t_employees e
INNER JOIN t_departments d
on e.department_id =d.department_id工NNER JOIN t_locations l
ON d.location_id = l.location_id

左外连接（left join on)

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中，部门名称以NULL填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees eLEFT JOIN t_departments d
ON e.department_id = d.department_id;
#注意:左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果匹配不到，则返回NULL值填充

右外连接（right join on)

#查询所有部门信息，以及此部门中的所有员工信息(没有员工的部门，也在查询结果中，员工信息以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees eRIGHT JOIN t_departments d
ONe.department_id = d.department_id;
#注意:右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果
#匹配不到,则返回NULL值填充

约束

• 问题:在往已创建表中新增数据时，可不可以新增两行相同列值得数据?
• 如果可行,会有什么弊端?

6.1实体完整性约束

• 表中的一行数据代表一个实体（entity)，实体完整性的作用即是标识每一行数据不重复、实体唯一。

主键约束

• PRIMARY KEY 唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL

唯一约束

• UNIQUE 唯一，标识表中的一行数据,不可重复，可以为NULL

自动增长列

• AUTO_INCREMENT自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键配合。

6.2域完整性约束

限制列的单元格的数据正确性

非空约束

• NOT NULL非空,此列必须有值。

默认值约束

• DEFAULT值为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

引用完整性约束

语法:CONSTRAINT引用名FOREIGN KEY(列名) REFERENCES被引用表名(列名)
详解:FOREIGN KEY引用外部表的某个列的值,新增数据时，约束此列的值必须是引用表中存在的值。

#创建专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAR(20) UNIQUE NOT NULL)CHARSET=utf8;
#创建课程表(课程表的SpecialId引用专业表的id)
CREATE TABLE subject(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(20)UNIQUE NOT NULL,
    subjectHours INT DEFAULT 20，
    specialId INT NOT NULL,
CONSTRAINT fk_ subject.specialId FOREIGN KEY( specialId)REFERENCES Speciality(id)
    #引用专业表里的id 作为外键，新增课程信息时，约束课程所属的专业。
) charset=utf8;
#专业表新增数据
INSERT INTO Speciality (SpecialName )VALUES( 'Java ');INSERT INTO Speciality(SpecialName)VALUES( 'C#');
#课程信息表添加数据
INSERT INTO subject(subjectName, subjectHours) VALUES( 'Java' ,30,1);#专业 id 为1，引用的是专业表的 Java
INSERT INTO subject(subjectName , subjectHours) VALUES('c#wVC',10,2);#专业 id 为2，引用的是专业表的C#

注意:当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表)，再删除主表（被引用表)

6.3约束创建整合

创建带有约束的表

创建表

列名	数据类型	约束	说明
GradeId	int	主键，自动增长	班级编号
GradeName	varchar	唯一、非空	班级名称

CREATE TABLE Grade(
GradeId INT PRIMARY KEY AUTO_INCREMENT,GradeName VARCHAR(20) unique NOT NULL)CHARSET=UTF8;

CREATE TABLE student(
student_id varchar(50)PRIMARY KEY,
    student_name varchar ( 50)NOT NULL,
    sex CHAR(2)DEFAULT '男'
borndate date NOT NULL,
    phone varchar(11)，
gradeId int not null,
CONSTRAINT fk_student_gradeId FOREIGN KEY( gradeId）REFERENCES Grade(GradeId)
#引用Grade表的GradeTd列的值作为外键，插入时约束学生的班级编号必须存在。
);

• 注意：创建关系表时，一定要先创建主表，再创建从表
• 删除关系表，先删除从表，再删除主表

事务（重点）

模拟转账:生活当中转账是转账方账户扣钱，收账方账户加钱。我们用数据库操作来模拟现实转账。

数据库模拟转账

#A账户转账给B账户1008元。#A账户减180日元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
#B账户加1000元

#断电、异常、出错...

UPDATE account SET MONEY =MONEY+1808 WHERE id=2;
#上述代码完成了两个账户之间转账的操作。

• 上述代码在减操作后过程中出现了异常或加钱语句出错，会发现，减钱仍旧是成功的，而加钱失败了!
• 注意:每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

7.1事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败,整个事务都执行失败。

7.2事务的边界

• 开始:连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始

• 结束:

1).提交:
a.显示提交:commit;
b.隐式提交:一条创建、删除的语句,正常退出(客户端退出连接);2).回滚:
a.显示回滚:rollback;
b.隐式回滚:非正常退出(断电、宕机)，执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

7.3事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（commit)，才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚(rollback)。

7.4事务的特性

• Atomicity(原子性)
  表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败
• Consistency(一致性)
  表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
• lsolation(隔离性)
  事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不
  会查看中间状态的数据。
• Durability(持久性)
  持久性事务完成之后,它对于系统的影响是永久性的。

7.5事务应用

应用环境:基于增删改语句的操作结果(均返回操作后受影响的行数)，可通过程序逻辑手动控制事务提交或回滚

事务完成转账

#A账户给B账户转账。#1.开启事务
START TRANSACTION ; | setAutoCommit=0 ;#禁止自动提交setAutoCommit=1;#开启自动提交#
2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY =MONEY-1000 WHERE ID =1;UPDATE ACCOUNT SET MONEY =MONEY+1088 WHERE ID = 2;
#3.事务内语句都成功了,执行 COMMIT;
COMMIT;
#4.事务内如果出现错误,执行ROLLBACK;
ROLLBACK;

注意∶开启事务后，执行的语句均属于当前事务，成功再执行COMIIT，失败要进行ROLLBACK

权限管理

8.1创建用户

create user 用户名 identified by 密码

创建一个用户

#创建一个 zhangsan用户
CREATE USER`zhangsan IDENTIFIED BY '123';

8.2授权

grant all on 数据库.表 to 用户名；

用户授权

#将companyDB下的所有表的权限都赋给zhangsan
GRANT ALL ON companyDB.* TO zhangsan`;

8.3撤销权限

remove all on 数据库名.表名 from 用户名

• 注意：撤销权限后，账户要重新连接客户端才会生效

#将 zhangsan的companyDB的权限撤销
REVOKE ALL ON companyDB.* FROM `zhangsan`;

8.4删除用户

#删除用户zhangsan
DROP USER 'zhangsan`;

视图

9.1概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE,DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

9.2视图特点

• 优点
  • 简单化，数据所见即所得。
  • 安全性,用户只能查询或修改他们所能见到得到的数据。
  • 逻辑独立性，可以屏蔽真实表结构变化带来的影响。
• 缺点
  • 性能相对较差,简单的查询也会变得稍显复杂。
  • 修改不方便，特变是复杂的聚合视图基本无法修改。

9.3视图的创建

语法:CREATE VIEW视图名AS查询数据源表语句;

创建视图

#创建t_empInfo的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

使用视图

#查询t_empInfo视图中编号为101的员工信息
SELECT *FROM t_empInfo where employee_id = '101';

9.4视图的修改

方式一:CREATE OR REPLACE VIEW视图名AS查询语句

方式二: ALTER VIEW视图名AS查询语句

修改视图

#方式1:如果视图存在则进行修改，反之，进行创建
CREATE OR REPLACE VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY, DEPARTMENT_ID from t_employees;
#方式2:直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

9.5视图的删除

#删除t_empInfo视图
DROP VIEW t_empInfo;
#注意:删除视图不会影响原表

9.6视图注意事项

• 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
• 如果视图包含以下结构中的一种,则视图不可更新
  • 聚合函数的结果
  • DISTINCT 去重后的结果
  • GROUP BY分组后的结果
  • HAVING筛选过滤后的结果
  • UNION、UNION ALL 联合后的结果

SQL语言分类

• 数据查询语言DQL (Data Query Language) : select、where、order by、group by、having 。
• 数据定义语言DDL (Data Definition Language) : create、alter、drop。
• 数据操作语言DML (Data Manipulation Language) : insert、update、delete 。
• 事务处理语言TPL （Transaction Process Language): commit、rollback 。
• 数据控制语言DCL (Data Control Language) : grant、revoke。

综合练习

数据库表

#创建用户表
 create table user(
   userid int primary key auto_increment,
    username varchar(20) not null,
    password varchar(18) not null,
    address varchar(100),
   phone varchar(11));

#创建分类表
create table category(
cid varchar(32) PRIMARY KEY,
cname varchar ( 100) not null);

#创建商品表
CREATE TABLE products(
pid varchar(32) PRIMARY KEY,
name VARCHAR(40) ,
Price  DOUBLE(7,2),
category_id varchar(32),
constraint  fk_products_category_id  foreign key(category_id)  references category(cid));

#创建订单表
create table orders(
Oid varchar(32)PRIMARY KEY ,
totalprice double(12,2),
userId int,
constraint fk_orders_userId foreign key(userId) references user(userId));

#创建订单项
create table orderitem(
oid varchar(32),
pid varchar(32),
num int ,
primary key(oid, pid),
constraint fk_orderitem_oid foreign key(oid) references orders(oid),
constraint fk_orderitem_pid foreign key(pid) references products(pid));

#初始化用户数据
INSERT INTO USER(username,PASSWORD , address, phone) VALUES('张三', '123','北京昌平沙河','13812345678');
INSERT INTO USER(username , PASSWORD , address, phone)
VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username , PASSWORD , address, phone) VALUES('赵六','123','北京朝阳','13812340987' );
INSERT INTO USER(username,PASSWORD , address , phone) VALUES('田七','123','北京大兴','13812345687');


#给商品表初始化数据0
insert into products(pid, name , price,category_id) values('p001','联想',5000, 'c001');
insert into products(pid, name , price,category_id) values( 'p002','海尔' , 3000, 'c001');
insert into products(pid, name ,price,category_id) values( 'p003','雷神',5000, 'c001');
insert into products(pid , name,price,category_id) values( 'p004',' JACK JONES' , 800, 'c002');
insert into products(pid , name , price, category_id) values( 'p005','真维斯',200, 'c002');
insert into products(pid , name , price,category_id) values( 'p006','花花公子',440, 'c002');
insert into products(pid, name,price,category_id) values( 'p007','劲霸',2000, 'c002');
insert into products(pid , name, price,category_id) values( 'p008','香奈儿',800, 'c003');
insert into products(pid , name , price,category_id) values( 'p009','相宜本草',200, 'c003');
insert into products(pid, name , price, category_id) values( 'p010','梅明子' ,200 ,null);

#给分类表初始化数据
insert into category values( 'c001','电器');
insert into category values( 'c002' ,'服饰');
insert into category values( 'c003','化妆品');
insert into category values( 'c004','书籍');

#给订单表初始化数据
Insert into orders values( 't0001',100,1);
insert into orders values('t0002',200,2);
insert into orders values('t0003',300,3);
insert into orders values('t0004',400,4);

#给订单项初始化数据
Insert into orderitem values( 't0001','p001',2);
Insert into orderitem values( 't0001','p002',2);
Insert into orderitem values( 't0002','p003',1);
Insert into orderitem values( 't0002','p005',5);
Insert into orderitem values( 't0002','p006',6);
insert into orderitem values( 't0003','p002',1);
Insert into orderitem values( 't0003','p009',3);
Insert into orderitem values( 't0003','p007',2);
insert into orderitem values( 't0004','p001',1);
Insert into orderitem values( 't0004','p010',3);
Insert into orderitem values( 't0004','p008',2);

综合练习1[多表查询]

查询所有用户的订单

SELECT o.oid,o.totalprice，u.userId, u.username , u.phoneFROM orders o INNER JOIN USER u ON o.userId=u.userId;

查询用户id为1的所有订单详情

SELECT o.oid, o.totalprice，u.userId, u.username , u.phone ,oi.pidFROM orders o INNER JOIN USER u ON o.userId=u.userId
INNER JOIN orderitem oi ON o.oid=oi.oid
where u.userid=1;

综合练习2[子查询]

查看用户为张三的订单

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username='张三');

查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER WHERE userTd IN (SELECT DISTINCT userId FROM orders WHERE totalprice>800);

综合练习3[分页查询]

查询所有订单信息,每页显示5条数据

#查询第一页
SELECT * FROM orders LIMIT 0, 5;

mysql工具下载

下载地址