Java学习19：MySQL

MySQL

数据库安装

1.下载MySQL压缩文件：点击下载

2.把下载到的压缩文件解压到一个不含英文的路径

3.添加环境变量：此电脑-属性-高级系统设置-环境变量，在path环境变量中增加刚才解压的文件夹中的bin目录

4.在解压的mysql-5.7.19-winx64文件夹下，创建文件my.inl，下面是文件内容

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置为自己的MYSQL的安装目录
basedir=E:\lsgmysql\mysql-5.7.19-winx64\
# 设置为MYSQL的数据目录
datadir=E:\lsgmysql\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
# 跳过安全检查
skip-grant-tables

5.使用管理员身份打开cmd，并切换到mysql-5.7.19-winx64\bin目录下，执行命令

mysqld -install

6.初始化数据库

mysqld --initialize-insecure --user=mysql

7.如果执行成功，mysql-5.7.19-winx64文件夹下会生成data文件夹，data文件夹内容如下

8.启动mysql服务

# 启动mysql服务
net start mysql

如果成功启动mysql，任务管理器可以看到mysql服务

9.连接数据库

mysql -u root -p

执行下面命令后，会让你输密码，因为前面我们创建的my.inl文件中设置的是跳过安全检查，所以直接回车就可以

10.修改密码：依次执行下面三行命令，其中的密码可以设置成自己喜欢的

use mysql;
update user set authentication_string=password('root') where user='root' and Host='localhost';
flush privileges;

11.退出连接

quit

12.关闭mysql服务

# 关闭mysql服务
net stop mysql

13.修改前面创建的my.inl文件：把最后一行注释掉，注释掉后再连接数据库时，就需要输入正确的用户名和密码了

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置为自己的MYSQL的安装目录
basedir=E:\lsgmysql\mysql-5.7.19-winx64\
# 设置为MYSQL的数据目录
datadir=E:\lsgmysql\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
# 跳过安全检查
# skip-grant-tables

附加：安装msql过程中，出错了或者想重新再来一把，执行下面命令【删除已经安装好的mysql服务】

sc delete mysql

第一次登录

1.启动mysql服务

net start mysql

2.连接数据库

mysql -u root -p

执行命令后会让你输入密码，输入前面设置的密码即可。

也可以直接把密码写在-p后面，这样直接登录，不会再提示输入密码

mysql -u root -proot

3.退出连接

quit

认识数据库

所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS：database manage system），这个管理程序可以管理多个数据库。一个数据库中可以创建多个表，一个表可以保存多条数据。

MySQL数据库-普通表的本质仍是文件，在MySQL安装目录中，每个数据库，对应data目录下的一个文件夹，数据库文件夹下的一个frm文件对应一个表结构，一个ibd文件对应一张表的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-spZHRVqW-1636426670610)(E:\java\note\image\45.png)]

连接数据库（Dos）

mysql -h 主机IP -P 端口 -u 用户名 -p密码

注意：

1. -p密码中间不要有空格
2. -p不写密码，回车会要求输入密码
3. 如果没有写-h 主机，默认就是本机
4. 如果没有写-P 端口，默认就是3306
5. 在实际工作中，3306一般修改

SQL语句分类

DDL：数据定义语句（create 表，库）

DML：数据操作语句（增加 insert，修改 update，删除 delete）

DQL：数据查询语句（select）

DCL：数据控制语句（管理数据库：比如用户权限 grant revoke）

操作数据库

1. 创建数据库

   # 创建数据库
   CREATE DATABASE `lsg_db01`;
   # 创建数据库lsg_db02，指定字符集utf8，如果不指定字符集，默认utf8
   CREATE DATABASE `lsg_db02` CHARACTER SET utf8
   # 创建数据库lsg_db03，指定字符集utf8，并带有校对规则
   # 校对规则：utf8_bin 区分大小写  utf8_general_ci 不区分大小写
   # 创建数据库时不指定字符集和校对规则，默认字符集utf8，默认校对规则utf8_general_ci
   CREATE DATABASE `lsg_db03` CHARACTER SET utf8 COLLATE utf8_bin
   # 创建表默认字符集和校对规则为继承数据库的规则
   

2. 删除数据库

   # 删除数据库lsg_db01
   DROP DATABASE `lsg_db01`
   

3. 显示数据库

   # 查看当前数据库服务器的所有数据库
   show DATABASES
   

4. 显示数据库创建语句

   # 查看数据库的定义信息
   SHOW CREAT DATABASE `lsg_db01`
   

备份/恢复数据库

1.备份数据库

#备份数据库(dos命令下执行)
# mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
mysqldump -u root -p -B lsg_db02 lsg_db03 > e:\\backup.sql

2.备份表

# mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql
mysqldump -u root -proot lsg_db02 user > e:\\table.sql

3.恢复数据库

# 恢复数据库（mysql命令行下执行）
# source 文件名.sql
source e:\\backup.sql
# 或直接把sql文件的代码复制到mysql查询命令查询

操作表

创建表：

CREATE TABLE table_name
(
	filed1 datatype,
	filed2 datatype,
	filed3 datatype
)character set 字符集 collate 校对规则 engine 存储引擎
field:指定列名	datatype:指定列类型(字段类型)
character set:如果不指定默认为所在数据库字符集
collate:如果不指定默认为所在数据库规则
engine:引擎

删除表

DROP TABLE `goods`

显示表结构

DESC `user`

修改表

# 添加列
ALTER TABLE `emp` ADD(
image VARCHAR(255)
)

# 修改列
ALTER TABLE `emp` MODIFY job VARCHAR(80)

# 删除列
ALTER TABLE `emp` DROP sex

# 修改表名
RENAME TABLE `emp` TO `person`

# 修改字符集
ALTER TABLE person CHARSET gbk

# 修改列名
ALTER TABLE person CHANGE COLUMN `name` username VARCHAR(20)

# 修改存储引擎（CSV、Memory、ARCHIVE、MRG_MYISAM、MYSIAM、InnoDB）
ALTER TABLE person ENGINE = MYISAM

字段类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1IzSHCKR-1636426670615)(E:\java\note\image\46.png)]

注意：

1. bit字段显示时，按照二进制的方式显示，但查询时仍然可以用添加的数值
2. 如果一个值只有0、1，可以考虑使用bit(1)，可以节约空间
3. DECIMAL(M,D)中，M代表总位数，最大65，默认是10，D代表小数点后位数，最大30，默认是0
4. 数值类型的数据类型默认是有符号的，定义无符号的在数据类型后面加 unsigned，
5. char的255是字符数，varchar的65535是字节数
6. varchar 长度是0–65535字节，其中1–3个字节用于记录大小
7. varchar在utf8编码下，实际存放字符数 = (65535 - 3) / 3 = 21844
8. varchar在gbk编码下，实际存放字符数 = (65535 - 3) / 2 = 32766
9. char是固定长度，比如char(4)中，即使只插入”aa”，也会分配4个字符空间
10. char的查询速度更快，如果字符串长度确定，推荐使用char
11. 还有介于text和longtext之间的mediumtext，范围是0-2^24
12. 要时间戳在insert和update时自动更新，添加约束NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

补充类型

enmu：枚举

create table person(
	sex enum('男', '女') -- 枚举类型
)

操作数据

1.添加数据

# insert 添加数据
INSERT INTO `goods`(id, goods_name, price) VALUES(10,'华为手机', 2000)

# 添加多条数据
INSERT INTO `goods`(id, goods_name, price) VALUES(10,'华为手机', 2000),(10,'小米手机', 3000)

# 如果不写字段名，values中的值要和表中所有字段一一对应
INSERT INTO `goods` VALUES(10,'华为手机', 2000)

insert 语句的细节

#说明 insert 语句的细节 
-- 1.插入的数据应与字段的数据类型相同。 
比如 把 'abc' 添加到 int 类型会错误 
INSERT INTO `goods` (id, goods_name, price) 
VALUES('韩顺平', '小米手机', 2000); 

-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。**韩顺平循序渐进学** **Java** **零基础** 
INSERT INTO `goods` (id, goods_name, price) 
VALUES(40, 'vovo 手机 vovo 手机 vovo 手机 vovo 手机 vovo 手机', 3000); 

-- 3. 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。 
INSERT INTO `goods` (id, goods_name, price) -- 不对 
VALUES('vovo 手机',40, 2000); 

-- 4. 字符和日期型数据应包含在单引号中。 
INSERT INTO `goods` (id, goods_name, price)
VALUES(40, vovo 手机, 3000); -- 错误的 vovo 手机 应该 'vovo 手机' 

-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null) 
INSERT INTO `goods` (id, goods_name, price) 
VALUES(40, 'vovo 手机', NULL); 

-- 6. insert into tab_name (列名..) values (),(),() 形式添加多条记录 
INSERT INTO `goods` (id, goods_name, price) 
VALUES(50, '三星手机', 2300),(60, '海尔手机', 1800); 

-- 7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称 
INSERT INTO `goods` 
VALUES(70, 'IBM 手机', 5000); 

-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错 
-- 如果某个列 没有指定 not null ,那么当添加数据时，没有给定值，则会默认给 null 
-- 如果我们希望指定某个列的默认值，可以在创建表时指定 

2.更新数据

# update 更新数据
UPDATE goods SET price = 5000 WHERE goods_name = '华为手机'

# 如果没有 where 限定，则所有price都会改为1000
UPDATE goods SET price = 1000

# 更新多个字段：小米手机价格 + 1000，id改为20
UPDATE goods SET price = price + 1000,id = 20 WHERE goods_name = '小米手机'

细节：

1. UPDATE语法可以用新值更新原有表行中的各列。
2. SET子句指示要修改哪些列和要给予哪些值。
3. WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录)
4.如果需要修改多个字段，可以通过set 字段1 =值1,字段2-值....

3.删除数据

# delete 删除数据
DELETE FROM `goods` WHERE id = 10

# 没有 where修饰会删除表中所有数据
DELETE FROM `goods`

细节：

1.如果不使用where子句，将删除表中所有数据
2.delete语句不能删除某一列的值
3.使用delete语句仅删除记录，不删除本身。如果要删除表，使用drop table 语句

4.查找数据

# select 查询数据
# 查询表中所有字段
SELECT * FROM goods

# 查询价格为2000 的手机名
SELECT goods_name FROM goods WHERE price = 2000

# 查询手机的价格（DISTINCT 去掉重复数据）
SELECT DISTINCT price FROM goods

# 查询手机数,别名为手机种类数
SELECT COUNT(id) AS 手机种类数 FROM goods

# 查询goods表，把每个商品价格加200
SELECT id,goods_name,(price + 200) AS 真价格 FROM goods

运算符

1.普通计算

# 查询数学成绩比英语成绩多30的人
SELECT * FROM students WHERE math > (english + 30)

2.between …. and ……

# 查询英语成绩在80~90之间的人
SELECT * FROM students WHERE english BETWEEN 80 AND 90

3.in

# 查询 math 分数为 85,90,91 的人
SELECT * FROM students WHERE math IN (85,90,91)

# NOT IN 不在
SELECT * FROM students WHERE math NOT IN (85,90,91)

4.like

# % 代表多个字符
# _ 代表一个字符

# 查询所有姓关的人
SELECT * FROM students WHERE `name` LIKE '关%'

# NOT LIKE 不像
SELECT * FROM students WHERE `name` NOT LIKE '关%'

5.is null

# 查询name为空的人
SELECT * FROM students WHERE `name` IS NULL

# IS NOT NULL 不为空
SELECT * FROM students WHERE `name` IS NOT NULL

6.and

# 查询英语成绩在80~90之间的人
SELECT * FROM students WHERE english >= 80 AND english <= 90

7.or

# 查询姓刘或姓关的人
SELECT * FROM students WHERE `name` LIKE '关%' OR `name` LIKE '刘%'

8.not

# 查询英语成绩不在80~90之间的人
SELECT * FROM students WHERE NOT(english >= 80 AND english <= 90)

查询扩展

1.group by：分组

# 查询各班的英语平均分
SELECT class,AVG(english) FROM `students` GROUP BY class

2.having：对查询到的结果再次过滤

# 查询英语平均分大于 60 的班级
SELECT class,AVG(english) AS average FROM `students` GROUP BY class HAVING average > 60

3.order by：排序(默认是升序asc)

# 升序
SELECT * FROM students ORDER BY id ASC

# 降序
SELECT * FROM students ORDER BY math DESC

# 按id升序，id相同按math降序
SELECT * FROM students ORDER BY id ASC, math DESC

4.limit：限制显示行数

# limit start, rows
# 查询学生表，显示前三行
SELECT * FROM students LIMIT 0, 3

# 分页公式
# SELECT * FROM students LIMIT 每页显示的记录数 * (第几页 - 1), 每页显示记录数

函数

1.count：计数

# 统计学生数
SELECT COUNT(*) AS 学生数 FROM students

# count(列名)和count(*) 的区别是不会统计这个列中为null的行

2.sum：求和

# sum(列名) 把这一列相加起来
# 统计班级数学总成绩
SELECT SUM(math) AS 数学总分 FROM students

3.avg：求平均数

# avg(列名) 计算这一列的平均数

# 计算学生数学平均分
SELECT AVG(math) AS 数学平均分 FROM students

4.min：最小值

# min 计算这一列的最小值
SELECT MIN(math) AS 数学最低分 FROM students

5.max：最大值

# max 计算这一列的最大值
SELECT MAX(math) AS 数学最高分 FROM students

字符串相关函数

1.charset(字段名)：返回字符串字符集

SELECT CHARSET(`goods_name`) FROM goods

2.concat(字符串1，字符串2，…)：拼接字符串

SELECT CONCAT(`goods_name`, '是好手机') AS 标语 FROM goods

3.instr(str1, str2)：返回str2在str1中出现的位置(从1开始数)，没有返回0

SELECT INSTR(CONCAT(`goods_name`, '是好手机'),'手机') FROM goods

4.ucase(string)：转换成大写

# DUAL 亚元表，系统表，测试用
SELECT UCASE('dddAD') FROM DUAL

5.lcase(string)：转换成小写

SELECT LCASE('dddAD') FROM DUAL

6.left(string, length)：从字符串左边起，取length个字符

SELECT LEFT(goods_name, 2) FROM goods

7.right(string, length)：从字符串右边起，取length个字符

SELECT RIGHT(goods_name, 3) FROM goods

8.length(string)：获取字符串长度

SELECT LENGTH(goods_name) FROM goods

9.replace(str, search_str, replace_str)：在str中用replace_str替换search_str

# 把 手机 替换为 大哥大
SELECT REPLACE(goods_name, '手机', '大哥大') FROM goods

10.strcmp(string1, string2)：逐字比较两个字符串大小

# str1大为1， str2大为-1， 一样大为0
SELECT STRCMP('abc', 'aad') FROM DUAL

11.substring(str, position, [length])：从str的position开始（从1开始计算），取length个长度，不写length则去到结束

# 取 goods_name 的前三个字符
SELECT SUBSTRING(goods_name, 1, 3) FROM goods

12.去空格

# ltrim(string) 去前空格
SELECT LTRIM('   addd') FROM DUAL

# rtrim(string) 去后空格
SELECT RTRIM('sjdsa   ') FROM DUAL

# trim(string) 去前后空格
SELECT TRIM('  ffdf  ') FROM DUAL

数学相关函数

1.abs(num)：绝对值

SELECT ABS(id) FROM goods

2.bin(num)：十进制转二进制

SELECT BIN(id) FROM goods

3.ceiling(num)：向上取整

SELECT CEILING(5.2) FROM DUAL

4.conv(num, from_base, to_base)：进制转换

# num：数字，from_base：数字的进制，to_base：要转到的进制

# 把10进制的id转换成2进制
SELECT CONV(id, 10, 2) FROM goods

5.floor(num)：向下取整

SELECT FLOOR(5.2) FROM DUAL

6.format(num, decimal_places)：保留小数位(四舍五入)

# 保留小数点后两位，四舍五入
SELECT FORMAT(123.456789, 2) FROM DUAL

7.hex(num)：转16进制

SELECT HEX(id) FROM goods

8.least(num1, num2, …)：求最小值

SELECT LEAST(id, price) FROM goods

9.mod(num1, num2)：取余

# num1 % num2
SELECT MOD(price, id) FROM goods

10.rand([seed])：获取随机数 [0,1]

SELECT RAND() FROM DUAL

# 可以通过指定seed，保证获取到的随机数不会每次改变
SELECT RAND(3) FROM DUAL

时间日期函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lcs2DpvS-1636426670620)(E:\java\note\image\47.png)]

1.current_date()：当前日期

SELECT CURRENT_DATE() FROM DUAL
INSERT INTO `message` VALUES(1,'hello',CURRENT_DATE())

2.current_time：当前时间

SELECT CURRENT_TIME() FROM DUAL
INSERT INTO `message` VALUES(2,'how are you', CURRENT_TIME())

3.current_timestamp()：当前时间戳

SELECT CURRENT_TIMESTAMP() FROM DUAL
INSERT INTO `message` VALUES(3,'i am kangkang', CURRENT_TIMESTAMP())

4.date(datetime)：返回datetime的日期部分

SELECT DATE(send_time) FROM message

5.date_add(date, interval d_value d_type)：在date中加上日期或时间，interval后面可以是year、minute、second、day等

# 查询20分钟内的发送的消息
SELECT * FROM message WHERE DATE_ADD(send_time, INTERVAL 20 MINUTE) > NOW()

6.date_sub(date, interval d_value d_type)：在date上减去一个时间，interval后面可以是year、minute、second、day等

# 查询20分钟内的发送的消息
SELECT * FROM message WHERE send_time > DATE_SUB(NOW(), INTERVAL 20 MINUTE)

7.datediff(date1, date2)：两个日期差多少天 date1 - date2

# 计算当前自己活了多少天
SELECT DATEDIFF(NOW(), '1998-1-1 10:23:05')

8.now()：当前时间

SELECT NOW() FROM DUAL

9.year | month | date(datetime)：获取年|月|日

SELECT YEAR(send_time) FROM message -- 年
SELECT MONTH(send_time) FROM message -- 月
SELECT DAY(send_time) FROM message -- 日
SELECT HOUR(send_time) FROM message -- 时
SELECT MINUTE(send_time) FROM message -- 分
SELECT SECOND(send_time) FROM message -- 秒
SELECT DATE(send_time) FROM message -- 日期

10.unix_timestamp()：获取1970-1-1 到现在的秒数

SELECT UNIX_TIMESTAMP() FROM DUAL

11.from_unixtime(num, format)：把秒数格式化为日期

SELECT FROM_UNIXTIME(1234567899, '%Y-%m-%d') FROM DUAL

12.last_day(date)：可以返回该日期所在月份的最后一天

SELECT LAST_DAY('2018-05-04') FROM DUAL

加密和系统函数

1. user()：查询用户

SELECT USER()

1. database()：数据库名称

SELECT DATABASE()

1. md5(str)：为字符串算出md5加密的32位的字符串

INSERT INTO `user` VALUES(5,'jack',MD5('123456'),NOW())

1. password(str)：也是一个加密函数，mysql数据库用户密码默认使用加密方法

SELECT PASSWORD('123456') FROM DUAL

流程控制函数

1.if(expr1, expr2, expr3) 如果expr1为true，返回expr2，否则返回expr3，判断是否为null，使用is，不用==

# 查询goods表的商品名，并把小米手机替换成垃圾手机
SELECT IF(`goods_name` = '小米手机', '垃圾手机', `goods_name`) FROM goods

2.ifnull(expr1, expr2) 如果expr1不为null，返回expr1，否则返回expr2

# 查询goods表的商品名，把名字为null的替换成默认手机
SELECT IFNULL(`goods_name`, '默认手机') FROM goods

3.select case 字段名 when expr1 then expr2 when expr3 then expr4 else expr5 end，相当于switch结构

# 查询goods表的商品名，并把小米手机替换成好手机，把华为手机替换成垃圾手机，其他商品名都替换成默认手机
SELECT (SELECT CASE `goods_name`
	WHEN '小米手机' THEN '好手机'
	WHEN '华为手机' THEN '垃圾手机'
	ELSE '默认手机' END) FROM goods

多表查询

有时要查询的数据不在同一张表内，这时就需要使用多表查询

同时查询多张表，如果不加限制，得到的结果是第一张表(5行)的每一行，和第二张表（3行）的每一行分别匹配，组合成的新的表(5 * 3 = 15行)

# 查询李逵的手机号
SELECT `name`, phone FROM students, phone
	WHERE students.id = phone.sid
	AND `name` = '李逵'

自连接

多表查询也可以只用一张表，一般用于一张表的数据之间有关系

# 查询每个员工的上级
SELECT staff.`name` AS 下级, temp.`name` AS 上级
	FROM staff,staff temp
	WHERE staff.superior = temp.id
	
# 上面的查询语句查不到没有上级的员工，可以用外连接解决
SELECT staff.`name` AS 下级, temp.`name` AS 上级
	FROM staff LEFT JOIN staff temp ON staff.superior = temp.id

子查询

子查询是指嵌入在其他sql语句中的select语句，也称嵌套查询，子查询还可以当作临时表使用，具体为多表子查询

1. 单行子查询

   单行子查询是指只返回一行数据的子查询语句

   # 查询李逵的手机号(不用显示李逵)
   SELECT phone FROM phone
   	WHERE sid = (SELECT id FROM students
   	WHERE `name` = '李逵')
   

2. 多行子查询

   多行子查询指返回多行数据的子查询 使用关键字 in

   # 查询有手机号的学生信息
   SELECT * FROM students
   	WHERE id IN (SELECT sid FROM phone)
   

3. 多表子查询

   # 查询有手机号的学生信息，并显示出手机号
   SELECT students.*, temp.phone	
   	FROM students, (SELECT sid, phone FROM phone) temp	
   	WHERE id = sid
   

4. all操作符

   # 查询数学比3班所有人都高的学生
   SELECT * FROM students
   	WHERE math > ALL(SELECT math FROM students
                        WHERE class = 3)
                        
   # 换一种写法
   SELECT * FROM students
   	WHERE math > (SELECT MAX(math) FROM students
                        WHERE class = 3)
   

5. any操作符

   # 查询数学比3班数学最低分高的学生
   SELECT * FROM students
   	WHERE math > ANY(SELECT math FROM students
                        WHERE class = 3)
                        
   # 换一种写法
   SELECT * FROM students
   	WHERE math > (SELECT MIN(math)
   

6. 多列子查询

   # 查询英语和数学分和关羽一样的学生
   SELECT * FROM students	
   	WHERE (english, math) = (SELECT english, math                             FROM students                           
          	WHERE `name` = '关羽')
   

表复制

复制表结构

# 创建和students结构一样的表
CREATE TABLE newtable LIKE students

复制数据

# 把students的数据复制到newtable
INSERT INTO newtable SELECT * FROM students

自我复制

INSERT INTO newtable SELECT * FROM newtable

合并查询结果

1.union all：合并查询，不去重

# 分别查询数学大于60，英语大于60的人，并合并查询结果，不去重
SELECT `name`, math,english FROM students WHERE math > 60
UNION ALL
SELECT `name`, math,english FROM students WHERE english > 60

2.union：合并查询，去重

# 分别查询数学大于60，英语大于60的人，并合并查询结果，去重
SELECT `name`, math,english FROM students WHERE math > 60
UNION
SELECT `name`, math,english FROM students WHERE english > 60

外连接

1.左外连接：类似多表查询，不过左侧的表完全显示

# 查询每个员工的上级（前面多表查询的例子，用外连接没有上级的员工也能查到了）
# 由于没有匹配到上级的，查询结果上级为null
SELECT staff.`name` AS 下级, temp.`name` AS 上级
	FROM staff LEFT JOIN staff temp
	ON staff.superior = temp.id

2.右外连接：类似多表查询，不过右侧的表完全显示

# 查询每个员工的上级
SELECT staff.`name` AS 下级, temp.`name` AS 上级
	FROM staff temp RIGHT JOIN staff
	ON staff.superior = temp.id

约束

约束用于确保数据库的数据满足特定的商业规则。

在mysql中，约束包括：not null、unique、primary key、foreign和check五种

主键

1. 主键——primary key
2. 用于唯一的标识表行的数据
3. 设置主键的字段不能重复且不能为null
4. 一张表最多只能有一个主键，但可以是复合主键

设置主键：

# 法一
CREATE TABLE staff(
	id INT PRIMARY KEY, -- 主键
	`name` VARCHAR(32),
	superrior INT
)

# 法二，可以设置复合主键
CREATE TABLE staff(
	id INT,
	`name` VARCHAR(32),
	superrior INT,
	PRIMARY KEY (id, `name`) -- 设置复合主键
)

非空

1. 非空——not null
2. 设置了非空的字段，记录必须有数据

CREATE TABLE staff(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL, -- 非空
	superrior INT
)

唯一

1. 唯一——unique
2. 设置了唯一的字段，该列的值是不能重复的
3. 如果没有指定 not null，则unique字段可以有多个null

CREATE TABLE staff(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) UNIQUE, -- 唯一
	superrior INT
)

默认值

1. 默认值——default
2. 设置了默认值的字段，如果这个字段没有赋值，就取默认值

创建表：

CREATE TABLE goods(
	id INT,
	`name` VARCHAR(32) DEFAULT '默认商品' -- 默认值
)

添加数据：

INSERT INTO goods(id) VALUES(1)

外键

外键用于定义主表和从表之间的关系，从表设置外键约束的字段的数据，必须是主表中对应字段有的值，或者是null（前提是外键约束字段运行为null）

1. 外键——foreign key (本表字段名) references 主表名(主键名或unique字段名)
2. 外键约束定义在从表上
3. 主表对应的字段必须是主键或是unique约束
4. 表的类型必须是innodb，这样才支持外键
5. 从表外键约束字段的类型，必须和主表对应字段的类型一致（长度可以不同）
6. 从表外键约束字段的数据删除后，主表对应的数据才能删除

# foreign key (本表字段名) references 主表名(主键名或unique字段名)
# 主表——科目
CREATE TABLE subjects(
    id INT PRIMARY KEY,
    `sub_name` VARCHAR(32))
# 从表——学生
CREATE TABLE student(
    id INT PRIMARY KEY,
    `name` VARCHAR(32),
    favourite INT,
    FOREIGN KEY (favourite) REFERENCES subjects(id)  -- 设置外键)

check

用于限制数据必须满足某个条件，oracle 和 sql server 均支持check，但mysql5.7目前还不支持check，只做语法校验，不会生效

CREATE TABLE person(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK(sex IN ('man', 'woman')) -- check约束
)

在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成

自增长

我们希望某个字段从1开始，且每条记录自动增长，可以添加自增长约束

1. 自增长——auto_increment
2. 一般来说，自增长是和主键配合使用的
3. 自增长也可以单独使用（但需要配合一个unique）
4. 自增长修饰的字段是整形的（虽然小数也可以，但很少这么用）
5. 自增长默认从1开始，也可以如下命令修改

alter table 表名 auto_increment = 新的开始值

1. 如果添加数据时，给自增长字段指定的有值，则以指定的值为准

创建表:

CREATE TABLE t0(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 自增长约束
	`name` VARCHAR(32)
)

添加数据:

# 法一：给该自增长字段传参的位置传null
INSERT INTO t0 VALUES(NULL,'关羽')

# 法二：不给自增长字段赋值
INSERT INTO t0(`name`) VALUES('刘备')

# 法三：给自增字段指定值（以指定的值为准）
INSERT INTO t0 VALUES(5,'张飞')

索引

把某一个字段设置成索引，可以极大地提高查询速度（只对创建了索引的列有效），但是每设置一个索引，表文件就会变大，且修改、删除、插入语句效率降低(空间换时间)

索引原理

在没有索引时，每次查找都有全表扫描，查询速度慢，索引就是把为这个字段创建树形结构，这样可以极大加快查询速度，但是每次修改、删除、添加元素时，就需要对树进行维护，所以dml（修改、删除、添加）速度会变慢

是否添加索引

1. 频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，比如性别字段
3. 更新非常频繁的字段不适合创建索引
4. 不会出现再where子句中字段不该创建索引

索引类型

1. 主键索引，主键自动的为索引(类型Primary key)

   # 主键自动是主键索引
   # 创建表时添加主键索引
   CREATE TABLE t0(
   	id INT PRIMARY KEY, -- 主键索引
   	`name` VARCHAR(32)
   )
   
   # 给已有表添加主键索引
   ALTER TABLE t0 ADD PRIMARY KEY (id) -- 括号不能省略
   

2. 唯一索引(UNIQUE)

   # 添加唯一索引
   CREATE TABLE t0(
   	id INT,
   	`name` VARCHAR(32) UNIQUE -- 唯一索引
   )
   
   # 给已有表添加唯一索引
   # 法一：CREATE UNIQUE INDEX 索引名 ON 表名(字段名)
   CREATE UNIQUE INDEX id_index ON t0(id)
   
   # 法二：ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段名)
   ALTER TABLE t0 ADD UNIQUE INDEX id_index(id)
   

3. 普通索引(INDEX)

   # 法一：CREATE INDEX 索引名 ON 表名(字段名)
   CREATE INDEX id_index ON t0(id)
   
   # 法二：ALTER TABLE 表名 ADD INDEX 索引名 (字段名)
   ALTER TABLE t0 ADD INDEX id_index (id)
   

4. 全文索引(FULLTEXT)

   适用于MyISAM引擎，提高文本中字符串匹配速度，一般开发中，不使用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch

查询索引

# 查询索引
# 方式一
SHOW INDEX FROM t0

# 方式二
SHOW INDEXES FROM t0

# 方式三
SHOW KEYS FROM t0

# 方式四
DESC t0

删除索引

1.通用方法

# 通用方法：DROP INDEX 索引名 ON 表名
# 创建表没有写名字的主键索引叫PRIMARY, 唯一索引和字段同名
DROP INDEX `name` ON t0

2.删除主键索引

# 删除主键索引：ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE t0 DROP PRIMARY KEY;

事务

事务用于保证数据的一致性，它由一组相关的dml语句（修改、删除、添加）组成，该组的dml语句要么全部成功，要么全部失败。

当执行事务操作时(dml语句)，mysql会在表上加锁，防止其他用户修改表的数据。

示例

START TRANSACTION;
INSERT INTO t0 VALUES(1,'李华');
SAVEPOINT a;
INSERT INTO t0 VALUES(2,'小明');
ROLLBACK TO a;
ROLLBACK;
COMMIT;

核心语句

START TRANSACTION; -- 开始一个事务
SAVEPOINT 保存点名; -- 设置保存点
ROLLBACK TO 保存点名; -- 回退事务到指定保存点
ROLLBACK; -- 回退全部事务
COMMIT; -- 提交事务，所有的操作生效，不能再回退

细节讨论

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果没有创建保存点，可以执行rollback，默认就是回退到事务开始的状态
3. mysql的事务机制需要innodb的存储引擎
4. 开始一个事务：START TRANSACTION，SET AUTOCOMMIT=OFF
5. 当执行了commit语句之后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。
6. 回退到一个保存点更前面的保存点时，这个保存点就不能用了

隔离级别

多个连接开启各自事务操作数据库中的数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

如果不考虑隔离性，可能会引发如下问题（没有问题的情况：事务应该作为一个独立的存在，不受其他事务的影响）

1. 脏读：一个事务读取到另一个事务dml操作后，但尚未提交的结果
2. 不可重复读：一个事务读取到另一个事务修改或删除后，提交的的结果，导致返回不同的结果集
3. 幻读：一个事务读取到另一个事务插入操作后，提交的结果，导致返回不同的结果集

1. 查看当前隔离级别：默认级别是REPRTABLE-READ

   # 查看当前会话隔离级别
   SELECT @@tx_isolation
   
   # 查看系统当前隔离级别
   SELECT @@global.tx_isolation
   

2. 设置当前隔离级别

   # 设置当前会话隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
   
   # 设置当前系统隔离级别：SET GOLBAL TRANSATION ISOLATION LEVEL 隔离级别
   SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
   

3. 配置文件修改隔离级别：修改my.in配置文件，在[mysqld]下加上

   # transaction-isolation = 隔离级别
   transaction-isolation = REPEATABLE-READ
   

事务ACID特性

1. 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3. 隔离性（Isolation）：事务的隔离性使多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据干扰，多个并发事务之间要相互隔离
4. 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

1. MySQL的表类型由存储引擎决定，主要包括MyISAM、innoDB、Memory等
2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYSIAM、InnoDB
3. 这六种分为两类
   1. 事务安全型，比如InnoDB
   2. 非事务安全型，比如 mysiam 和 memory

显示当前数据库支持的存储引擎

SHOW ENGINES

存储引擎特点

1. MyISAM不支持事务、也不支持外键，但其访问速度块，对事务完整性没有要求
2. InooDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MySIAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引
3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常块，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。

存储引擎选择

1. MyISAM添加速度快，不支持外键和事务，支持表级锁，如果不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二的选择
2. InnoDB支持事务，支持外键，支持行级锁，如果需要支持事务，选择InnoDB
3. memory数据存储在内存中，关闭MySQL服务数据就会丢失（保留表结构），但速度很快（没有IO读写），默认支持索引（hash表），经典用法：用户在线状态

视图

视图是一个虚拟表，内容由查询者定义。同真实的表一样，包含列，数据来自对应的真实表(基表)

视图相当于把查询结果作为一张新的表，不过这张表中的数据是指向基表的

基本使用

1.创建视图

# CREATE VIEW 视图名 AS SELECT语句

# 把数学高于80分的学生做成math视图
CREATE VIEW math AS SELECT `name`,math FROM students
	WHERE math > 80

2.更新视图

# ALTER VIEW 视图名 AS SELECT语句

# 把math视图修改为数学高于90分的学生
ALTER VIEW math AS SELECT `name`,math FROM students
	WHERE math > 90

3.删除视图

# DROP VIEW 视图名1，视图名2
DROP VIEW math

4.增删改查数据

# 当做一个普通表即可
SELECT * FROM math

5.查看创建视图的指令

# SHOW CREATE VIEW 视图名
SHOW CREATE VIEW math

细节讨论

1. 视图是虚拟的表，数据来自基表
2. 修改视图的数据，就是修改基表的数据
3. 基表的数据改变，视图也会改变
4. 视图的文件只有一个结构文件(.frm)，没有数据文件(.ibd)

用途

1. 安全：一些数据表有着重要的信息，这些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段，这样用户就可以查询自己需要的字段，不能查看保密的字段
2. 性能：关系数据库的数据常常会分表存储，使用外键建立这些表之间的关系。这时，数据库查询通常会用到外连接，这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用外连接查询数据
3. 灵活：如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表，这样就可以少很多改动，也达到了升级数据表的目的

用户管理

MySQL中的用户，都存储在系统数据库mysql中的user表中，user表中有很多重要的字段

1. host：允许登录的地址，localhost表示该用户只允许本机登录，%表示所有ip都有连接权限，也可以指定ip地址，比如：192.168.1.100
2. user：用户名
3. authentication_string：密码，是通过mysql的password()函数加密之后的密码

管理用户

1. 创建用户

   # create user '用户名'@'允许登录位置' identified by '密码'
   CREATE USER 'lsg_user'@'localhost' IDENTIFIED BY '123456'
   # 不指定Host，Host则为%，不指定密码，则没有密码
   CREATE USER 'jack'
   # 也可以把IP限制到一定范围
   CREATE USER 'tom'@'192.168.1.%' IDENTIFIED BY '123456'
   

2. 删除用户

   # DROP USER '用户名'@'允许登录位置'
   DROP USER 'lsg_user'@'localhost'
   
   # 如果要删除的用户的Host为%，可以不写登录位置
   DROP USER 'jack' -- 默认就是DROP USER 'lsg_user'@'%'
   

3. 查询用户表

   SELECT * FROM mysql.user 
   

4. 修改密码

# 修改自己的密码：SET PASSWORD = PASSWORD('密码')
SET PASSWORD = PASSWORD('root')
# 修改他人密码：SET PASSWORD FOR '用户名'@'登录位置' = PASSWORD('密码')
SET PASSWORD FOR 'lsg_user'@'localhost' = PASSWORD('6666')

用户权限

权限不同的数据库用户，登录到DBMS后，可以操作的库和数据对象（表、视图、触发器）都不一样

1.授予权限

# GRANT 权限列表 ON 库名.数据对象名 TO '用户名'@'登录位置'
# 授予用户lsg_user查询数据库lsg_db02中user表的权限
GRANT SELECT ON lsg_db02.user TO 'lsg_user'@'localhost'

# GRANT 权限列表 ON 库名.数据对象名 TO '用户名'@'登录位置' IDENTIFIED BY '密码'
# 创建用户ming，并授予ming用户查询和删除所有数据库中所有表的数据
GRANT SELECT, DELETE ON *.* TO 'ming'@'localhost' IDENTIFIED BY '123456'

注意：

1. 权限列表，多个权限用逗号隔开
2. .：代表本系统中的所有数据库的所有对象（表、视图、存储过程）
3. 库.*：表示某个数据库中的所有数据对象（表、视图、存储过程等）
4. 带 IDENTIFIED BY 分两种情况
   1. 如果用户存在，就是修改该用户的密码
   2. 如果该用户不存在，就是创建该用户

2.回收权限

# REVOKE 权限列表 ON 库名.对象名 FROM '用户名'@'登录位置'
REVOKE SELECT ON lsg_db02.user FROM 'lsg_user'@'localhost'

3.权限生效指令

# 如果权限没有生效，可以执行下面指令
FLUSH PRIVILEGES

细节：

1. 在创建用户时，如果不指定Host，则为%，%表示所有IP都有连接权限
2. 也可以这样指定：creat user ‘xxx’@‘192.168.1.%’ 表示xxx用户在192.168.1.*的ip可以登录mysql
3. 在删除用户时，如果用户不是%，需要明确的指定 ‘用户’@‘host值’