mysql基础

• 基本命令
• • 服务和登录
  • SQL注释
  • SQL分类
  • 创建自定义数据库
  • 查看数据库创建信息
  • 修改数据库
  • 删除数据库
  • 查看当前使用的数据库
  • 使用数据库
  • 查看表的结构
  • 查看表的字符集
  • 查看所有表信息
• 数据类型
• • 数值类型
  • 日期类型
  • 字符串类型
• 数据表的创建（create）
• • 数据表的修改(ALTER)
  • 向现有表中添加列
  • 修改表中的列
  • 删除表中的列
  • 修改列名
  • 修改表名
  • 数据表的删除
• 约束
• 添加信息
• • 新增(insert)
• 修改(update)
• 删除(DELETE)
• • 删除一条信息
• 数据查询
• • 基本查询语句
  • 查询部分列
  • 查询所有列
  • 对列中数据进行运算
  • 列的别名
  • 查询结果去重
  • 排序查询
  • • 依据单列排序
    • 依据多列排序
  • 条件查询
  • 等值判断
  • 逻辑判断（and,or,not）
  • 区间判断（between and）
  • NULL值判断（IS NULL , IS NOT NULL）
  • 枚举查询（IN （值1，值2，值3））
  • 模糊查询
  • 分支结构查询
  • 时间查询
  • 字符串应用
  • 聚合函数
  • 分组查询
  • 分组过滤查询
  • 限定查询
  • sql 语句的编写顺序
  • 子查询（作为条件判断）
  • 子查询（作为枚举查询条件）
  • 子查询（作为一张表）
  • 合并查询
  • 表连接查询
  • 内连接查询（INNER JOIN ON）
  • 三表连接查询
  • 左外连接（left join on）
  • 右链接查询(right join on)
• 蠕虫复制
• 事务的概念
• • 事务的边界
  • 事务的原理
  • 事务的特性
  • 事务的应用
• 权限管理
• • 创建用户
  • 创建一个用户
  • 授权
  • 撤销用户权限
  • 删除用户
• 视图
• • 视图特点
  • 视图的创建
  • 创建视图
  • 视图的修改
  • 修改视图
  • 删除视图
  • 视图的注意事项
• 练习

基本命令

服务和登录

1.cmd中打开服务窗口的命令
net start mysql
2.cmd中停止mysql服务的命令
net stop mysql
3. 使用root账号root密码登录的命令
mysql -u root -p
4. 退出登录的命令
quit
EXIT
\q

SQL注释

单行注释: -- 你好
多行注释: /* 巴拉巴拉 */
MySQL 独有的单行注释: # 哈哈哈哈

SQL分类

• DDL（Data Definition Language）数据定义语言

  • 用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter 等

• DML（Data Manipulation Language）数据操作语言

  • 用来对数据库中表的数据进行增删改。关键字：insert，delete，update 等

• DQL（Data Query Language）数据查询语言

  • 用来查询数据库中表的记录（数据）。关键字：select等

• DCL（Data Control Language）数据控制语言（了解）

  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

  1. 数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。

  2. 数据定义语言DDL（Data Definition Language）：create、alter、drop。

  3. 数据操作语言DML（Data Manipulation Language）：insert、update、delete 。

  4. 事务处理语言TPL（Transaction Process Language）：commit、rollback 。

  5. 数据控制语言DCL（Data Control Language）：grant、revoke。

  查看MYSQL所有的数据库
  SHOW DATABASES ; #显示当前MYSQL中包含的所有数据库

创建自定义数据库

CREATE DATABASE mydb1; #创建名为mydb1的数据库
CREATE DATABASE mydb2 CHARACTER SET gbk ;#创建名为db2的数据库并设置编码格式为gbk
CREATE DATABASE mydb2  CHARSET gbk
CREATE DATABASE IF NOT EXISTS mydb3 ;#如果db3不存在,则创建,如果存在,则不创建
CREATE DATABASE IF NOT EXISTS mydb3  CHARACTER SET gbk

查看数据库创建信息

SHOW CREATE DATABASE db3; #查看创建数据库时的基本信息

修改数据库

注意mysql中utf-8 是utf8

ALTER DATABASE db3 CHARACTER SET gbk #修改创建数据库时的基本信息

删除数据库

DROP DATABASE db3 #删除数据库

查看当前使用的数据库

SELECT DATABASE(); #查看当前使用的数据库

使用数据库

USE db2; #使用db2数据库

查看表的结构

desc `表名`;
describe `表名`;
DESC t_employees #查看表的信息

查看表的字符集

show CREATE TABLE t_employees

查看所有表信息

选择数据库后，才能查看表

show tables;

数据类型

MYSQL支持多种类型,大致可以划分为三类:数值，日期/时间和字符串(字符)类型

数值类型

日期类型

字符串类型

• CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
• BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
• BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
• 有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

数据表的创建（create）

#数据表的创建（CREATE）
CREATE TABLE 表名(
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ...
    列名 数据类型 [约束] #最后一列的末尾不加逗号
 )[CHARSET=utf8] #可以根据需要指定的表的字符编码集

字符集如果不指定, 默认继承库的字符集

CREATE TABLE SUBJECT(
   subjectid INT,
   subjectname VARCHAR(20),
   subjecthours INT
)CHARSET=utf8;

数据表的修改(ALTER)

#alter TABLE 表名 操作;

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

向现有表中添加列

#在课程表中添加 score列
ALTER TABLE SUBJECT ADD score INT

修改表中的列

#修改课程表中课程的字符长度为10个字符
ALTER TABLE SUBJECT MODIFY subjectname VARCHAR(10)

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

删除表中的列

#删除表中的score 列 
ALTER TABLE SUBJECT DROP score

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

修改列名

#修改课程表中 subjecthours 为 classhours
ALTER TABLE SUBJECT CHANGE subjecthours classhours INT

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

修改表名

#修改表名为sub
ALTER TABLE `subject` RENAME sub

数据表的删除

删除课程表

DROP TABLE 表名
DROP TABLE sub;

约束

实体完整性约束
表中的一行数据代表一个实体(entity) ,实体完整性的作用是标识每一行的数据不重复，实体唯一。

#实体完整性约束
#主键约束
PRIMARY KEY 唯一,标识表中的一行数据，此列不可重复,且不能为null
#唯一约束
UNIQUE 唯一,标识表中的一行数据,不可重复，可以为NULL
AUTO_INCREMENT 自动增长,给主键数值列添加自动增长,从1开始,每次加1，不能单独使用，和主键使用
#域完整性约束： 限制列的单元格的数据正确性
NOT NULL 非空,此列必须有值
DEFAULT 值  为此列赋予默认值,当新增数据不指定值时,书写DEFAULT以指定的默认值进行填充
引用完整性的约束
FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值
CREATE TABLE SUBJECT(
  subjectid INT PRIMARY KEY AUTO_INCREMENT,#课程编号标识的编号唯一,且不能为NULL,自动增长会从1开始自动增长，每次增加1
  subjectname VARCHAR(10) UNIQUE NOT NULL,#课程名称唯一,课程名不能为空
  subjecthours INT DEFAULT 20 #默认是学时20个小时
)CHARSET=utf8
CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名,列名
CREATE TABLE sutdent(
   stuid INT AUTO_INCREMENT PRIMARY KEY,
   stuname VARCHAR(10),
   subjectid INT NOT NULL,
   CONSTRAINT fk_student_stuid FOREIGN KEY(subjectid) REFERENCES `subject`(subjectid)
   #和课程表中的subjectid相关联
   )

注意：建立关系时一定要先创建主表，在创建重表
删除关系时，先删除重表，再删除主表

添加信息

新增(insert)

INSERT INTO 表名（列1,列2,列3.....）values(值1,值2,值3......)
-- 按照指定字段, 一次插入多行
insert into `表名` (字段1, 字段2 ...) values (值1, 值2, ...), (值1, 值2, ...);
-- 指定全部字段, 一次插入多行
insert into `表名` values (null, 值1, 值2, ...), (null, 值1, 值2, ...);
INSERT INTO SUBJECT(subjectname,subjecthours) VALUES('语文',30);
INSERT INTO SUBJECT(subjectname,subjecthours) VALUES('数学',DEFAULT);#默认课时是20个小时

注意:表名后的列名和values里的值要一一对应(个数，顺序，类型)
如果设置了正增长则不能手动添加对应的列

修改(update)

UPDATE    表名 SET 列1=新增1,列2=新增2....where 条件
UPDATE SUBJECT SET subjectname='英语' WHERE subjectid=1  #将课程编号为1的课程名改为英语
UPDATE SUBJECT SET subjectname='物理',subjecthours=36 WHERE subjectid=2 #可以一次性改多个值
UPDATE `subject` SET  subjecthours=40 #如果不加条件,在没有违反约束的前提下会修改整张表

注意: set后有多个列名=值 绝大多数的情况下都要加where条件制定修改，否则为整表更新

删除(DELETE)

DELETE FROM 表名 WHERE 条件

删除一条信息

DELETE FROM SUBJECT WHERE subjectid=2

注意:删除时，如果不加where 删除的是整张表的数据

清空整张表数据(TRUNCATE)

TRUNCATE TABLE 表名

注意:与delete不加where删除数据不同，TRUNCATE是把表销毁，再按原表格式创建一个新表

数据查询

执行查询语句返回的结果是一张虚拟表

基本查询语句

select 列名 from 表名

查询部分列

#从员工表中查询所有员工的id，姓和名
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees;

查询所有列

#查询员工表中所有的信息(所有列)
SELECT * FROM t_employees
*是通配符代表着所有，效率低,可读性差

对列中数据进行运算

每次查询相当于是一张虚拟的表，对原本表里面的内容没有影响

#查询员工表中的id和年薪
SELECT EMPLOYEE_ID,SALARY*12 FROM t_employees

注意:%是站位运算符，而不是取余运算符

列的别名

列 as '列名'
SELECT EMPLOYEE_ID '员工id',SALARY*12 AS '年薪' FROM t_employees

as可以省略

查询结果去重

#DISTINCT 去重
SELECT DISTINCT MANAGER_ID FROM t_employees

排序查询

#select 列名 from 表名 order by 排序列[排序方式]
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC

注意:排序方式可以省略那么默认是升序排列

依据单列排序

#按员工月薪降序排列
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC

依据多列排序

#按员工月薪降序排列当员工月薪相同时按员工id升序排列（薪资相同时，按照编号进行升序排序)
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC,EMPLOYEE_ID ASC

当有多个列排序时先依据前面的先排，相同时在根据后面的排

注意：
1.排序方式有2种：升序 asc  降序 desc ，默认升序
2.如 字段1 和 字段2 排序有冲突，字段1 优先满足。
3.如字段1数据相同，再按照字段2的排序方式排序。
-- 注意：字符串也是可以排序的，排序依据为字符编码的二进制值
select name from student3  order by name;

条件查询

SELECT 列名 from 表名 where 条件

等值判断

#查询员工月薪为400的信息（员工id，月薪）
SELECT EMPLOYEE_ID ,SALARY FROM t_employees WHERE SALARY = 4000

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

逻辑判断（and,or,not）

#查询员工月薪为11000并且员工号为114 的信息
SELECT * FROM t_employees WHERE SALARY = 11000 AND EMPLOYEE_ID=114

不等值判断（>,<,>=,<=,!=.<>）

区间判断（between and）

#查询薪资在4000到11000之间的员工信息
SELECT * FROM t_employees WHERE SALARY BETWEEN 4000 AND 11000
SELECT * FROM t_employees WHERE SALARY>=4000 AND SALARY<=11000

注意：在区间判断语法中，小值在前，大值在后，反之得不到正确结果

NULL值判断（IS NULL , IS NOT NULL）

IS NULL
列名 is NULL
IS NOT NULL
列名 is NOT    NULL
#查询没有提成的员工信息
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NULL

枚举查询（IN （值1，值2，值3））

#查询部门编号为70,80,90的员工信息
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(70,80,90)
SELECT * FROM t_employees WHERE DEPARTMENT_ID=70 OR DEPARTMENT_ID=80 OR DEPARTMENT_ID=90

in 表示包含这些条件的
not in 表示除去这些条件以外的，也就是不包含这些条件的
注意 in的查询效率比较低，可以通过多条件拼接

模糊查询

LIKE _(单个任意字符)
列名 LIKE '张_'
这样就只能匹配到例如张三两个字的名字，就不能匹配3个或4个字的名字
LIKE %(任意长度的任意字符串)
列名 LIKE '张%'
这样可匹配姓张的所有姓名，无论是几个字的姓名

注意: 模糊查询只能与LIKE关键字结合使用

#查询姓中以l开头所有员工信息
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'L%'
#查询姓中以l开头并且长度为4的所有员工信息
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'l___'

分支结构查询

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END as '列名'

注意：通过使用CASE END进行条件判断，每条数据对应生成一个值，与java中的switch类似
后面最好在取一个别名否则列名是对应的表达式

#根据薪水划分薪水等级
SELECT * ,
CASE 
     WHEN SALARY>=1000 AND SALARY <2000 THEN 'A'
     WHEN SALARY>=2000 AND SALARY <5000 THEN 'B'
     WHEN SALARY>=5000 AND SALARY <9000 THEN 'C'
     ELSE 'D'
END AS '月薪等级'
FROM t_employees

时间查询

#当前系统时间 
SELECT NOW();
#当前系统时间 
SELECT SYSDATE(); #2020-09-23 22:42:25
#当前系统日期 
SELECT CURDATE(); #2020-09-23
#当前系统时间
SELECT CURTIME(); #22:44:55
#获取指定日期中的年份
SELECT YEAR('2020-09-23'); #2020
#获取小时值
SELECT HOUR(CURTIME()); #22
#获取分钟值
SELECT MINUTE(CURTIME()) #48
#指定日期之差的相隔天数
SELECT DATEDIFF('2020-10-30','2019-10-30'); #366
SELECT DATEDIFF('2019-10-30','2020-10-30'); #-366
#计算Date日期加上N天后的日期
SELECT ADDDATE('2020-10-30',40); #2020-12-09
SELECT ADDDATE('2020-10-30',-40); #2020-09-20

字符串应用

#多个字符串拼接
SELECT CONCAT('hello','world','java');#helloworldjava
SELECT CONCAT(FIRST_NAME,LAST_NAME) AS '姓名' FROM t_employees;
#字符串大写转换
SELECT LOWER('HELLO'); #hello
#字符串小写转换
SELECT UPPER('hello'); #HELLO
#字符串替换
SELECT INSERT('dyk的数据库',1,3,'mysql'); #mysql的数据库
#指定内容的获取
SELECT SUBSTRING('dyk的mysql数据库',5,8);  #mysql数据库

mysql中字符串下标是从1开始的

聚合函数

SELECT 聚合函数(列名) FROM 表名
对多条数据进行的单列进行统计，返回统计后一行的结果

#聚合函数
SELECT 聚合函数(列名) FROM 表名
#求单列所有数据的和
#SUM()函数返回一组值的总和，SUM()函数忽略`NULL`值。**如果找不到匹配行，则SUM()函数返回`NULL`值。
SELECT SUM(SALARY) FROM t_employees
#求单列所有数据的平均值
#AVG()函数计算一组值的平均值**。 它计算过程中是忽略`NULL`值的
SELECT AVG(SALARY) FROM t_employees
#求单列的最大值
#MAX()函数返回一组值中的最大值
SELECT MAX(SALARY) FROM t_employees 
#求单列的最小值
#MIN()函数返回一组值中的最小值
SELECT MIN(SALARY) FROM t_employees 
#总行数 员工的数量
#COUNT()函数返回结果集中的行数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees
#统计有提成的人数
SELECT COUNT(COMMISSION_PCT) FROM t_employees

注意：聚合函数会自动忽略null值,不进行统计

分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组

select 列名 from 表名 where 条件 group by 分组依据(列)

或者在分组之后用having来进行筛选

#查询各部门的总人数
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再针对各部门的人数进行统计
SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID) FROM t_employees GROUP BY DEPARTMENT_ID
#查询各部门的平均工资
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再对每个部门的工资进行统计
SELECT DEPARTMENT_ID,AVG(salary) FROM t_employees GROUP BY DEPARTMENT_ID
#查询各个部门,各个岗位的人数
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再按照岗位进行分组（分组的依据是JOB_ID）
#3.最后按照各部门各个岗位的人数进行统计
SELECT DEPARTMENT_ID,JOB_ID,COUNT(EMPLOYEE_ID) FROM t_employees GROUP BY DEPARTMENT_ID,JOB_ID

分组查询中,select显示的列只能是分组的依据列或者聚合函数列不能出现其他列
换句话说就是select查询的列中如果不是聚合函数就一定也要在GROUP BY后面写上相应的列

-- 查询address
select  address from student3;
-- 使用distinct关键字
select distinct address from student3;
-- group by 分组查询
--这其实跟distinct返回的结果一致
select address from student3 group by address;
-- 当查询多个列的数据时
-- 查询name和address
select  name,address from student3;
-- 用distinct试试
-- 这里只是去重了name
select distinct  name,address from student3;
-- 用group by 试试
-- 结果集name,address一组都相同才会去重
select  name,address from student3 group by name,address;

分组过滤查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

#统计10,80,90部门的最高工资
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再根据过滤条件过滤出部门编号为70,80,90的信息
#3.再求出最大值
SELECT DEPARTMENT_ID,MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID IN(70,80,90)

分组前条件 和 分组后条件 过滤结果集

• where 分组前条件
• having 分组后条件

注意：

1.where 在分组前进行条件过滤，不满足条件的记录不参与分组，不能跟 聚合函数
2.having 在分组后进行条件限定，不满足限定的记录不会被查询出来， 可以跟 聚合函数

限定查询

SELECT 列名 FROM 表名 LIMIT 起始行,查询行数

#查询员工表中前5名员工的信息
SELECT * FROM t_employees LIMIT 0,5

mysql中没有 top和sql server 不同

mysql中字符串是从1开始的，但是这个起始行还是从0开始的

注意：起始行是从0开始的，代表了第一行，第二个参数代表的是从指定行开始查询几行

-- offset 起始行数，从 0 开始计数，如果省略，默认就是 0
-- size 每页展示的记录数 
limit offset,size

公式（记住即可）

起始行数 start=(currPage-1)*size
- currPage 当前页数
- size 每页展示的记录数
注意：limit 语法是mysql的方言，其它数据库，对于分页有不同的实现方式。

找出当前页数和起始行数之间的规律

sql 语句的编写顺序

#sql 语句的编写顺序
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排列顺序（asc|desc） LIMIT 起始行,查询行数
1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY ： 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果

• 注意：将子查询 ”一行一列“的结果作为外部查询的条件，做第二次查询
• 子查询得到一行一列的结果才能作为外部查询的等值判断条件

子查询（作为条件判断）

SELECT 列名 FROM 表名 Where 列名 [in(子查询结果);](
#查询工资大于Bruce的员工信息
#1.先查Bruce的工资
SELECT SALARY FROM t_employees WHERE FIRST_NAME='Bruce'
#2.再查工资大于Bruce的员工信息
#合并后的语句为
SELECT * FROM t_employees WHERE SALARY>(SELECT SALARY FROM t_employees WHERE FIRST_NAME='Bruce')

注意: 将子查询” 一行一列”的结果作为外部查询的条件，做第二次查询
子查询表得到的一行一列的结果才能为外部查询的等值判断条件或不等值条件判断

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

SELECT 列名 FROM 表名 WHERE 列名 IN(子查询结果)
#查询与名为King 同一部门的员工信息
#1.先查询King所在的部门编号
#2.再查询员工信息
#3.合并
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME='King')

将子查询多行一列的结果作为外部枚举查询条件，做第二次查询

#查询工资高于60部门所有人的信息(高于所有)
SELECT * FROM t_employees WHERE SALARY>ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60 )
#查询工资高于60部门的信息（高于部分）
SELECT * FROM t_employees WHERE SALARY>ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60 )

注意：当子查询结果集形式为多行单列时可以使用ANY或ALL关键字

子查询（作为一张表）

SELECT 列名 FROM (子查询的结果集) WHERE 条件
#1.先对所有员工表的薪资进行排序（排序后的临时表）
#2.再查询临时表的前5行员工信息
SELECT * FROM(SELECT * FROM t_employees ORDER BY SALARY DESC) AS temp LIMIT 0,5
SELECT * FROM t_employees ORDER BY SALARY DESC LIMIT 0,5

将子查询多行多列的结果作为外部查询的一张表，做第二次查询，

注意：子查询作为临时表，为其赋予一个临时表名，否则会报错

合并查询

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。如果允许重复的值，请使用 UNION ALL。

SELECT * FROM 表名1 UNION SELECT * FROM 表名2
SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
#合并两张表的结果（去除重复记录）
SELECT * FROM t1 UNION SELECT * FROM t2
#合并两张表的结果（保留重复记录）
SELECT * FROM t1 UNION ALL SELECT * FROM t2

注意： 合并结果的两张表，列数必须相同，列的数据类型可以不同
使用union合并结果集，会去除两张表中重复的数据

注意：
​1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
​2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。union只关注数据类型，数据业务含义是否相同不管。

表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

内连接查询（INNER JOIN ON）

SELECT * FROM t_employees  a INNER JOIN t_jobs  b ON a.JOB_ID=b.JOB_ID
SELECT * FROM t_employees a,t_jobs b WHERE a.JOB_ID=b.JOB_ID

三表连接查询

SELECT * FROM t_employees  a INNER JOIN t_departments  b ON a.DEPARTMENT_ID=b.DEPARTMENT_ID INNER JOIN t_locations c ON b.LOCATION_ID=c.LOCATION_ID

3张表两两相关联

左外连接（left join on）

#查询所有员工信息及所对应的部门名称
SELECT * FROM  t_employees a LEFT JOIN t_departments b ON a.DEPARTMENT_ID=b.DEPARTMENT_ID

注意:左连接查询是以左表为主表，依次向右匹配，匹配到返回结果，匹配不到则返回NULL值填充

右链接查询(right join on)

#查询所有员工信息及所对应的部门名称
SELECT * FROM  t_employees a RIGHT JOIN t_departments b ON a.DEPARTMENT_ID=b.DEPARTMENT_ID

注意右连接查询是以右表为主表，依次向左匹配，匹配到返回结果，匹配不到则返回NULL值填充

蠕虫复制

什么是蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中
语法格式：INSERT INTO 表名1 SELECT * FROM 表名2;
作用:将表名2中的数据复制到表名1中
具体操作:

• 创建student2表，student2结构和student表结构一样

  CREATE TABLE student2 LIKE student;

将student表中的数据添加到student2表中

INSERT INTO student2 SELECT * FROM student;

事务的概念

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

事务的边界

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

结束:
1提交：
a.显示提交:commit
b.隐式提交：一条创建，删除的语句，正常退出（客户端退出连接）；
2回滚：
a.显示回滚：rollback;
b.隐式回滚：非正常退出（断电,）执行了创建，删除的语句,但是失败了,回味这个无效的执行回滚

事务的原理

数据库会为每一个客户端都维护一个独立的缓存区（回滚段中）只有事务所有sql语句均正常结束（commit）才会将回滚段中的数据同步到数据库，否者无论因为那种原因失败，整个事务将回滚（rollback）

事务的特性

事务的应用

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

CREATE TABLE bank(
      id INT,
      money INT
)
INSERT INTO bank(id,money) VALUES(1,1000)
INSERT INTO bank(id,money) VALUES(2,500)
#1账户给2账户转钱
START TRANSACTION #开启事务
#事务内数据操作语句
UPDATE bank SET money=money-200 WHERE id=1;
UPDATE bank SET money=money+200 WHERE id=2;
#事务内语句执行成功了 执行commit
COMMIT
#事务内如果出现错误，执行rollback
ROLLBACK
SELECT * FROM bank

注意:开启事务后，执行的语句均属于当前的事务，成功再执行commit,失败要进行rollback

权限管理

创建用户

CREATE USER 用户名 IDENTIFIED BY 密码

创建一个用户

#创建一个zhangsan用户
CREATE USER `zhangshan` IDENTIFIED BY '123456'

授权

GRANT ALL ON 数据库.表 TO 用户名
#将db3 下的所有表的权限都赋值给zhangshan
GRANT ALL ON db3.* TO `zhangshan`

撤销用户权限

#将zhangshan的db3权限取消
REVOKE ALL ON db3.* FROM `zhangshan`;

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

删除用户

DROP USER 用户名
DROP USER `zhangshan`

视图

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

视图特点

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

视图的创建

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

创建视图

#创建视图
CREATE VIEW t_empinfo AS  SELECT * FROM t_employees;
#使用视图
SELECT * FROM t_empinfo

视图的修改

方式一 ： create or replace view
方式二 ：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;

删除视图

注意：删除视图不会影响原表

#删除t_empInfo视图
DROP VIEW t_empInfo;
在这里插入代码片

视图的注意事项

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

练习

# 服务和登录
# 1.cmd中打开服务窗口的命令
 net START mysql
# 2.cmd中启动和停止mysql服务的命令
net STOP mysql
# 使用root账号root密码登录的命令
mysql -u root -p
# 退出登录的命令
quit
EXIT
\q
# 库的操作
# 查询mysql服务器中一共有多少个数据库
SHOW DATABASES
# 查询当前正在使用的数据库
SELECT DATABASE()
#创建一个数据库db1，并判断该库是否不存在，指定字符集为utf8:
CREATE DATABASE IF NOT EXISTS db1 CHARACTER SET utf8
#使用db1,然后查看当前使用的数据库是否为db1:
USE db1
SELECT DATABASE()
# 修改db1的字符集为gbk
ALTER DATABASE db1 CHARACTER SET gbk
ALTER DATABASE db1 CHARSET gbk
# 查看db1的字符集：
SHOW CREATE DATABASE db1
# 再修改db1的字符集为utf8
ALTER DATABASE db1 CHARACTER SET utf8
ALTER DATABASE db1 CHARSET utf8
# 再查看db1的字符集
SHOW CREATE DATABASE db1
# 删除db1，并判断是否存在
DROP  DATABASE IF EXISTS db1
#表的操作
#新建一个数据库db1
CREATE DATABASE IF NOT EXISTS db1
#使用数据库db1
USE db1
#查看db1中有多少张表
SHOW TABLES
# 新建一个学员表student,有学员编号id,学员姓名name,年龄age三个列
CREATE TABLE student(
    id INT PRIMARY KEY ,
    NAME VARCHAR(20) NOT NULL,
    age INT 
)CHARSET=utf8
#将student表复制一份，新表命名为stu
CREATE TABLE stu LIKE student
#查看stu表的结构
DESC stu
# 查看stu表的创建语句
SHOW CREATE TABLE stu
#修改stu表名为stu1
ALTER TABLE stu RENAME stu1
#修改stu表的字符集为gbk
ALTER TABLE stu CHARACTER SET gbk
#查看stu表的字符集
SHOW CREATE TABLE stu
#给stu添加一个列，生日birthday
ALTER TABLE stu ADD birthday DATE
#修改birthday为birth
ALTER TABLE stu CHANGE birthday birth DATE
#删除生日这一列
ALTER TABLE stu DROP birth
#删除student这个表，并判断是否存在
DROP TABLE IF EXISTS student
#dml
#1. 给stu表新增一条数据，所有的列都要有数据
INSERT INTO stu(id,NAME,age) VALUES(1,'dyk',18)
#2. 给stu表新增一条数据，只给id和name:
INSERT INTO stu(id,NAME) VALUES(2,'cb')
#3. 给stu表一次新增3条数据：
INSERT INTO stu(id,NAME,age) VALUES(3,'dyk',18),(4,'dyk2',19),(5,'dyk3',20)
#4. 修改stu表中id为1的记录，姓名改为tom,年龄改为20：
UPDATE stu SET NAME='tom',age=20 WHERE id=1
#5. 删除id为2的记录：
DELETE FROM stu WHERE id=2
#6. 删除所有的记录：
DELETE FROM stu
#7. 给stu表新增一条数据，只给id和name:
INSERT INTO stu(id,NAME) VALUES(2,'cb')
#8. 删除所有记录，使用truncate:
TRUNCATE stu
CREATE DATABASE FwDB
#用户信息表(发表出租房屋信息的用户)UserInfo：
CREATE TABLE UserInfo(
  UserId INT PRIMARY KEY AUTO_INCREMENT,
  UserName VARCHAR(30) NOT NULL,
  UserTel VARCHAR(11) NOT NULL
)CHARSET=utf8
# 房屋类型表(FwLx)
CREATE TABLE FwLx(
  LxId INT PRIMARY KEY AUTO_INCREMENT,
  LxName VARCHAR(20) NOT NULL UNIQUE
)CHARSET=utf8
#房屋信息表(FwXx)
CREATE TABLE FwXx(
  FwId INT PRIMARY KEY AUTO_INCREMENT,
  LxId  INT  REFERENCES Fwlx(LxId),
  FwDiZhi VARCHAR(60) NOT NULL,
  shi INT NOT NULL,
  ting INT NOT NULL,
  ZuJin INT NOT NULL,
  FwDesp VARCHAR(500),
  userId INT  REFERENCES UserInfo(UserId) 
)CHARSET=utf8
INSERT INTO UserInfo(UserName,UserTel) VALUES('dyk','13797892836'),('cb','17362942385'),('wl','13477358481');
INSERT INTO FwLx(LxName) VALUES('平房'),('地下室'),('公寓')
INSERT INTO FwXx(LxId,FwDiZhi,shi,ting,ZuJin,FwDesp,userId) VALUES(1,'wh',1,2,10000,'很好',1)
INSERT INTO FwXx(LxId,FwDiZhi,shi,ting,ZuJin,FwDesp,userId) VALUES(2,'bj',1,2,10000,'很好',3)
INSERT INTO FwXx(LxId,FwDiZhi,shi,ting,ZuJin,FwDesp,userId) VALUES(3,'sh',1,2,7000,'很好',2)
# 1.删除租金大于8000的房屋信息
DELETE FROM  FwXx WHERE ZuJin>8000
#2.查询所有1室2厅的房屋信息
SELECT * FROM FwXx WHERE shi=1 AND ting=2
# 3.查询租金在200-500之间的房屋信息
SELECT * FROM FwXx WHERE ZuJin BETWEEN 200 AND 500
#4.查询房屋类型为‘地下室’的房屋信息
SELECT * FROM FwXx INNER JOIN FwLx ON FwXx.LxId=FwLx.LxId WHERE FwLx.LxName='地下室'
#5.查询房屋说明中有“好”字的房屋编号、租金和联系人姓名以及电话
SELECT FwXx.FwId,ZuJin,UserName,UserTel FROM FwXx INNER JOIN UserInfo ON FwXx.userId=UserInfo.UserId WHERE FwDesp LIKE '%好%'
# 6.查询房屋类型为“公寓”或“别墅”的房屋信息
SELECT * FROM FwXx INNER JOIN FwLx ON FwXx.LxId=FwLx.LxId WHERE LxName IN('公寓','别墅')
#7.查询房屋类型为“平房”的房屋的编号、租金、联系人姓名以及电话
SELECT FwLx.LxId,FwXx.ZuJin,UserInfo.UserName,UserInfo.UserTel FROM UserInfo INNER JOIN FwXx ON FwXx.userId = UserInfo.UserId
INNER JOIN FwLx ON FwLx.LxId=FwXx.LxId WHERE LxName='平房'
#8.查询房屋地址在光谷的房屋信息，并按租金降序显示查询结果
SELECT * FROM FwXx WHERE FwDiZhi='光谷' ORDER BY ZuJin DESC
#9.查询所有房屋的数量，平均租金，最高租金，以及最低租金
SELECT COUNT(FwId),AVG(ZuJin),MAX(ZuJin),MIN(ZuJin) FROM FwXx
# 10.查询房屋类型是“平房”的最高租金
SELECT MAX(ZuJin) FROM FwXx INNER JOIN FwLx ON FwXx.LxId=FwLx.LxId WHERE LxName='平房'
# 11.查询每种类型的房屋的数量，平均租金，最高租金，以及最低租金
SELECT COUNT(FwId),AVG(ZuJin),MAX(ZuJin),MIN(ZuJin) FROM FwXx GROUP BY LxId
# 13.查询房屋的编号，房号地址，房屋租金，类型名称、发布用户的姓名和联系电话
SELECT FwId,FwDiZhi,ZuJin,FwLx.LxName,UserInfo.UserName,UserInfo.UserTel FROM UserInfo INNER JOIN FwXx ON FwXx.userId = UserInfo.UserId
INNER JOIN FwLx ON FwLx.LxId=FwXx.LxId
# 14.查询联系人姓名为“张三”的所有的房屋信息
SELECT * FROM FwXx INNER JOIN UserInfo ON FwXx.userId=UserInfo.UserId WHERE UserName='张三'
# 15.查询没有发布房屋信息的联系人信息
SELECT * FROM  UserInfo INNER JOIN FwXx ON FwXx.userId=UserInfo.UserId WHERE FwDesp IS NULL