MySql基础学习

发表于 | 分类于 ， | 热度: ℃
字数统计: 11.3k | 阅读时长 ≈ 45

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

MySql基础学习

一、引言

1.1 现有的数据存储方式有哪些？

  • Java程序存储数据（变量、对象、数组、集合），数据保存在内存中，属于瞬时状态存储。
  • 文件（File）存储数据，保存在硬盘上，属于持久状态存储。

1.2 以上存储方式存在哪些缺点？

  • 没有数据类型的区分。
  • 存储数据量极小。
  • 没有安全访问限制。
  • 没有备份、恢复机制。

二、数据库

2.1 概念

数据库是按照数据接口来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。

2.2 数据库的分类

  • 网状结构数据库：美国通用电气公司IDS（Integrated Data Store），以节点形式存储和访问。
  • 层次结构数据库：IBM公司IMS（Information Management System）定向有序的树状结构实现存储和访问。
  • 关系结构数据库：Oracle、DB2、MySQL、SQL Server，以表格（Table）存储，多表间建立关联关系，通过分类、合并、连接、选取等运算实现访问。
  • 非关系型数据库：ElastecSearch、MongoDB、Redis，多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据。

三、数据库管理系统

3.1 概念

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用、和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。

3.2 常见数据库管理系统

  • Oracle：被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作平台，完全支持所有的工业标准，并获得最高级别的ISO标准安全性认证。
  • DB2：IBM公司的产品，DB2数据库采用多进程多线索体系结构，其功能足以满足大中公司的需求，并可灵活地服务于中小型电子商务解决方案。
  • SQL Server： Microsoft公司退出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。
  • SQLLite：应用在手机端的数据库。

四、MySQL

4.1 简介

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

4.2 访问与下载

官网地址： https://www.mysql.com/

下载地址： https://dev.mysql.com/downloads/mysql/

mysql安装过程请查看mysql安装指南。

4.3 卸载

  • 控制台卸载。
  • 找到mysql的安装目录进行删除。
  • programdata删除mysql。
  • 如果卸载后，有未删除的MySQL服务，可采用手动删除。
  • 以管理员身份打开命令行，输入sc delete MySQL57，回车。

如果某个MySQL组件无法删除，可以尝试从注册表搜索信息删除相关文件夹。具体可百度。

4.4 配置环境变量

Windows

  • 创建MYSQL_HOME: C:\Program Files\MySQL\MySQL Server5.7
  • 追加PATH: %MYSQL_HOME%\bin;

4.5 MySQL目录结构

核心文件介绍：

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

4.6 MySQL配置文件

在MySQL安装目录中找到my.ini文件，并打开my.ini文件查看几个常用配置参数。

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

通过安装程序安装的MySQL配置文件在C盘的隐藏文件ProgramData中，点进去找到对应的MYSQL目录就能看到my.ini文件了。

客户端默认字符集和服务器端默认字符集必须保持一致，它们在初始的配置文件中都是被注释掉的，那么默认的字符集会跟随你的系统默认字符集。

五、SQL语言

5.1概念

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

  • 经验:通常执行对数据库的“增删改查”，简称c (create) R (Read) u (Update) D (Delete)。

5.2 MySql应用

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

5.3基本命令

查看MvSqL中所有数据库

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

数据库名称 作用
information_schema 信息数据库
mysql 核心数据库
performance_schema 性能优化
sakila 样板数据库
sys 系统数据库
world 国家的编号等

创建自定义数据库

1
2
3
create database mydb1; # 创建数据库
create database mydb2 character set gbk; # 编码格式为gbk
create database if not exists mydb4;# 如果mudb4不存在就创建他

查看数据库创建信息

1
show create database mydb2; # 查看数据库创建信息

修改数据库

1
alter database mydb2 character set utf8;

删除数据库

1
drop database mydb1; # 删除数据库

查看当前使用的数据库

1
select database();

使用数据库

1
use mydb2;

六、客户端工具

6.1 Navicate

Navicat是一套快速、可靠并价格相宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。

6.2 SQLyog

MySQL可能是世界上最流行的开源数据库引擎，但是使用基于文本的工具和配置文件可能很难进行管理。SQLyog提供了完整的图形界面，即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定义工具和查询、友好的视觉界面、类似Excel的查询结果编辑界面等优点。

七、数据查询【重点】

7.1 数据库表的基本结构

关系数据库是以表格(Table)进行数据存储,表格由“行”和“列”组成

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

7.2 基本查询

语法:SELECT列名FROM表名

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

7.2.1 查询部分列

1
2
3
# 查询员工表中所有员工的编号】你名字、邮箱
select employee_id fist_name, email
from t_employee

7.2.2查询所有列

1
2
3
4
5
# 查询所有列
# 1、使用*的方式
SELECT * FROM t_employees;
# 2、使用列名的方式
SELECT 所有列的列名 FROM t_employees;
  • 注意：生产环境下，优先使用列名查询，*的方式需要转换成全列名。效率低，可读性差

7.2.3 对列中的数据进行运算

1
2
# 查询员工的编号、名字、年薪
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY * 12 FROM t_employees
算数运算符 描述
+ 两列做加法运算
- 两列做减法运算
* 两列做乘法运算
/ 两列做除法运算
  • % 是占位符。而并非模运算符

7.2.4 列的别名

列 as 列名

1
SELECT EMPLOYEE_ID '编号',FIRST_NAME AS '姓氏',SALARY * 12 AS '年薪' FROM t_employees;

7.2.5 查询结果去重

distinct 列名

1
2
# 查询员工表中有多少个经理
SELECT DISTINCT MANAGER_ID FROM t_employees;

7.3 排序查询

语法:SELECT 列名 FROM 表名 ORDER BY排序列[排序规则]

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

7.3.1 依据单列排序

1
2
3
4
# 对员工工资进行升序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary + 0 ASC;
# 对员工工资进行降序排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary + 0 DESC;

7.3.2 依据多列排序

1
2
# 按照工资高低进行降序排序，如果工资相等，按照员工编号升序进行排序
SELECT EMPLOYEE_ID,salary FROM t_employees ORDER BY salary + 0 DESC, EMPLOYEE_ID DESC;

7.4 条件查询

语法: SELECT 列名 FROM 表名 WHERE 条件

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

7.4.1 等值判断

1
2
3
4
5
6
7
8
# 查询符合条件的数据
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY = 11000

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY = 24000
  • 注意：与java不同（==），mysql中等值判断使用=

7.4.2 逻辑判断（and 、or、 not）

1
2
3
4
5
6
7
8
9
10
11
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY = 11000 AND COMMISSION_PCT = 0.30 AND EMPLOYEE_ID = '148';

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY = 11000 OR COMMISSION_PCT = 0.30;

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE NOT SALARY = 11000;

7.4.3 不等值判断（>、 <、>=、<=、<>、!=）

1
2
3
4
5
6
7
8
9
10
11
12
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY != 11000;

SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY <> 11000;

# 查询工资区间在[6000, 10000]之间
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY >= 6000 AND salary <= 10000;

7.4.4 区间判断(between 起始and 终止)

1
2
3
4
# 查询工资区间在[6000, 10000]之间
SELECT EMPLOYEE_ID, FIRST_NAME,salary
FROM t_employees
WHERE SALARY BETWEEN 6000 AND 10000;
  • 注意：在区间判断中，小值在前，大值在后，反之，得不到结果

7.4.5 null值判断（is null、is not null）

1
2
3
4
5
6
7
8
# 查询出经理编号为null的员工信息
SELECT employee_id, FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NULL;

SELECT employee_id, FIRST_NAME,MANAGER_ID
FROM t_employees
WHERE MANAGER_ID IS NOT NULL;

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

1
2
3
4
5
6
7
8
9
# 查询部门编号为70,80,90 的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME,SALARY,department_id
FROM t_employees
WHERE department_id = 70 OR department_id = 80 OR department_id = 90;

SELECT EMPLOYEE_ID, FIRST_NAME,SALARY,department_id
FROM t_employees
WHERE department_id IN (70,80,90);
# in 的效率在大数据面前较低

7.4.7 模糊查询

  • LIKE_（单个任由字符）

​ 列名 LIKE ‘张__’

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

    列名 LIKE ‘张%’

  • 注意：模糊查询只能和LIKE关键字结合使用
1
2
3
4
5
6
7
8
# 查询L开头的三个字的员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM t_employees
WHERE FIRST_NAME LIKE 'L__';
# 查询所有以L开头的员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY
FROM t_employees
WHERE first_name LIKE '张%';

7.4.8 分支结构查询

1
2
3
4
5
6
CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END
  • 注意：通过使用case end进行条件判断，每条数据对应生成一个值
  • 经验：类似java中的switch
1
2
3
4
5
6
7
8
9
10
# 查询员工信息
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,department_id,
CASE
	WHEN SALARY >= 10000 THEN 'A'
	WHEN SALARY >= 8000 AND SALARY < 10000 THEN 'B'
	WHEN SALARY >= 6000 AND SALARY < 8000 THEN 'C'
	WHEN SALARY >= 4000 AND SALARY < 6000 THEN 'D'
	ELSE 'E'
END AS "level"
FROM t_employees;

7.5 时间查询

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

  • 经验：执行时间函数查询，会自动生成一张虚表
时间函数 描述
SYSDATE 当前系统时间（日月年时分秒）
CURDATE 获取当前日期
CURTIME 获取当前时间
WEEK(DATE) 获取指定日期为一年中的第几周
YEAR(DATE) 获取指定日期的年份
HOUR(TIME) 获取指定时间的小时值
MINUTE(TIME) 获取时间的分钟值
DATEDIFF(DATE,DATE2) 获取DATE1和DATE2之间相隔的天数
ADDDATE(DATE,N) 计算DATE加上N天后的日期 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 当前系统的时间
SELECT SYSDATE();
# 当前系统的ri期
SELECT CURDATE();
# 获取系统时间
SELECT CURTIME();
# 获取指定日期为一年中的第几周
SELECT WEEK(SYSDATE());
# 获取指定日期忠厚的年份
SELECT YEAR(SYSDATE());
# 获取小时值
SELECT HOUR(CURTIME());
# 获取分钟值
SELECT MINUTE(CURTIME());
# 指定日期之间的相隔天数
SELECT DATEDIFF('2020-4-1','2019-4-1');
# 计算date日期加上n天后的日期
SELECT	ADDDATE('2020-4-1', 5);

7.6 字符串查询

语法: SELECT 字符串函数([参数列表])

字符串函数 说明
concat(str1, …) 将多个字符串拼接
insert(str, pos, len, newStr) 将str中指定pos位置开始len长度的内容替换为newStr
lower(str) 将指定字符串转换诶小写
upper(str) 将指定字符串转换为大写
substring(str, num, len) 将str字符串指定num位置开始截取len个内容 
1
2
3
4
5
6
7
8
9
10
11
12
# 多个字符串拼接
SELECT CONCAT('My', 'S,', 'QAL');
SELECT CONCAT(FIRST_NAME,'-', last_name) FROM t_employees;
# 字符串替换
SELECT INSERT('这是一个数据库',3, 2, 'MySql')
# 字符串转小写
SELECT LOWER('LOWER')
# 字符串转大写
SELECT UPPER('shdiuashdui')
# 指定内容截取
SELECT SUBSTRING('JaavaMySqlStrSub',6,6);
# MySqlS

7.7 聚合函数

语法: SELECT 聚合函数(列名) FROM 表名;

  • 经验：对多条数据的单列进行统计，返回统计后的一行结果
聚合函数 说明
SUM（） 求所有行中单列结果的总和
AVG() 平均值
MAX() 最大值
MIN() 最小值
COUNT() 求总行数 
1
2
3
4
5
6
7
8
9
10
11
12
13
# 聚合函数
# 求单列所有数据的和
SELECT SUM(SALARY) FROM t_employees;
# 求单列所有数据的平均值
SELECT AVG(SALARY) FROM t_employees;
# 求单列最大值
SELECT MAX(SALARY + 0) FROM t_employees;
# 求单列最小值
SELECT MIN(salary + 0) FROM t_employees;
# 求总行数 员工总数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees;
# 统计有提成的人数 会自动忽略null值，不进行统计
SELECT COUNT(COMMISSION_PCT) FROM t_employees;
  • 注意：聚合函数会自动忽略null值，不进行统计

7.8 分组查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据(列);

关键字 说明
GROUP BY 分组依据，必须在WHERE之后生效 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 查询各个部门的额总人数
SELECT department_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
# 查询各个部门的平局工资
SELECT department_id, AVG(salary)
FROM t_employees
GROUP BY department_id;
# 查询各个部门、各个岗位的总人数
SELECT department_id,job_id, COUNT(employee_id)
FROM t_employees
GROUP BY department_id, job_id;
# 常见问题：查询各个部门id，总人数、first_name
SELECT department_id,COUNT(employee_id), first_name
FROM t_employees
GROUP BY department_id;
  • 分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列

7.9 分组过滤查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUNP BY 分组列 HAVING 过滤规则

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

统计部门的最高工资

1
2
3
4
5
6
7
8
9
10
#思路
#1.确定分组依据（DEPARTMENT_ID）
#2.对分组后的数据，过滤出部门编号是60、70、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过滤出60，70，90的部门
#SELECT查看部门编号和MAX函数

7.10 限定查询

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

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

7.10.1 查询前5行记录

1
2
#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;
  • 注意：起始行是从0开始，代表了第一行，第二个参数代表的是从指定行开始查询几行。

7.10.2 查询范围记录

1
2
#查询表中从第四行开始，查询10行
SELECT * FROM t_employees LIMIT 3,10;

7.10.3 LIMIT典型应用

分页查询：一页显示10条，一共查询三页。

1
2
3
4
5
6
#思路：第一页是从0开始，显示10条
SELECT * FROM t_employees LIMIT 0,10;
#第二页是从10条开始，显示10条
SELECT * FROM t_employees LIMIT 10,10;
#第三页是从20条开始，显示10条
SELECT * FROM t_employees LIMIT 20,10;
  • 在分页应用场景中，起始行是变化的，但是一页显示的页数是不变的

7.11 查询总结

7.11.1 SQL语句编写顺序

COPYSELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列(ASC|DESC) LIMIT 起始行,查询行数

7.11.2 SQL语句执行顺序

1
2
3
4
5
6
7
COPY1.FROM:指定数据来源表
2.WHERE:对查询数据第一次过滤
3.GROUP BY:分组
4.HAVING:对分组后的数据第二次过滤
5.SELECT:查询各字段的值
6.ORDER BY:排序
7.LIMIT:限定查询结果

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

语法：SELECT 列名 FROM 表名 WHERE 条件(子查询结果)

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

1
2
3
4
5
6
7
8
#1.先查询到Bruce的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';
#2.查询工资大于Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将前两句整合
SELECT * FROM t_employees 
WHERE SALARY > 
(SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');
  • 注意：将子查询“一行一列”的结果作为外部查询的条件，做第二次查询。
  • 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值判断条件

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

语法：SELECT 列名 FROM 表名 WHERE 列名 IN (子查询结果);

7.13.1 查询与名为“KING”同一部门的员工信息

1
2
3
4
5
6
7
8
9
#思路
#1.先查询KING所在的部门编号
SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'King';#80,90
#2.再查询80，90号部门的员工信息
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(80,90);
#3.SQL:合并
SELECT * FROM t_employees 
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'King');
  • 将子查询“多行一列”的结果作为外部查询的枚举查询条件，做第二次查询。

7.13.2 工资高于60编号部门的员工信息

1
2
3
4
5
6
7
8
9
10
#1.查询60编号部门所有人的工资（多行单列）
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60;
#2.查询工资高于60编号部门所有人的工资的员工信息（高于所有）
SELECT * FROM t_employees 
WHERE SALARY > 
ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
#3.查询工资高于60编号部门的员工信息（高于部分）
SELECT * FROM t_employees 
WHERE SALARY > 
ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);

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

  • ALL表示查询高于部门所有员工工资的员工信息；ANY表示查询只需要高于部门中任意一名员工工资的员工信息

7.14 子查询（作为一张表）

查询到一张多行多列的临时表，这张表也可以作为查询数据源。

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

7.14.1 查询员工表中工资排名前5名的员工信息

1
2
3
4
5
6
7
8
#思路：
#1.先对所有员工的薪资进行排序（排序后的临时表）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC;
#2.再查询临时表中前5行员工信息
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM (临时表) LIMIT 0,5;
#3.合并
SELECT * FROM 
(SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees ORDER BY SALARY DESC) AS 临时表 LIMIT 0,5;
  • 将子查询“多行多列“的结果作为外部查询的一张表，做第二次查询。
  • 注意：子查询作为临时表，需要为其赋予一个临时表名。

7.15 合并查询

  • SELECT * FROM 表名1 UNION SELECT * FROM 表名2
  • SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
  • 返回的查询结果是将表1的结果和表2的结果做了一个合并。

15.1 合并两张表的结果（去除重复记录）

1
2
#合并两张表的结果，去除重复记录
SELECT * FROM T1 UNION SELECT * FROM T2;
  • 注意：合并结果的两张表，列数必须相同，列的数据类型可以不同。

列数必须相同是因为该合并做了一个纵向合并，两个4列多行的结果集表合并后也是一个4列多行的表，但是合并后的列名以第一个结果集表为准。

7.15.2 合并两张表的结果（保留重复记录）

1
2
#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM T1 UNION ALL SELECT * FROM T2;

该合并结果会保留两张查询表中重复的记录，而UNION只保留第一个不重复的记录

7.16 表连接查询

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

7.16.1 内连接查询（INNER JOIN ON）

1
2
3
4
5
#1.查询所有员工的职员和职位信息 SQL标准
SELECT * FROM t_employees INNER JOIN t_jobs 
ON t_employees.JOB_ID = t_jobs.JOB_ID;
#2.查询所有员工的职员和职位信息 MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID;

如果没有指定连接条件，则会造成笛卡尔积的结果，第一张表中的每行数据都会和第二张表进行连接。

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

7.16.2 三表连接查询

1
2
3
4
5
6
7
#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT EMPLOYEE_ID,FIRST_NAME,t_departments.DEPARTMENT_NAME,t_locations.COUNTRY_ID 
FROM t_employees
INNER JOIN t_departments ON	
t_employees.DEPARTMENT_ID=t_departments.DEPARTMENT_ID
INNER JOIN t_locations ON	
t_departments.LOCATION_ID=t_locations.LOCATION_ID;

该语句查询出来的结果没有员工编号为178的员工信息，该名员工的部门ID为NULL，不符合连接条件，所以不包含在匹配出的结果集中，你可以自己动手尝试并查看结果。之所以提这个是因为可以与下节的语句区分开来。

7.16.3 左外连接（LEFT JOIN ON）

1
2
3
4
5
#查询所有员工的工号、名字、工资及其对应的部门名称（没有部门的员工也在查询结果中，部门名称以NULL值补充）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME 
FROM t_employees
LEFT JOIN t_departments 
ON t_departments.DEPARTMENT_ID=t_employees.DEPARTMENT_ID;

该语句查询出来的结果包含没有部门的178号员工，DEPARTMENT_NAME的值被填充为NULL。

  • 注意：左外连接，是以左表作为主表，依次向右匹配，匹配到则返回结果；
  • 匹配不到则返回NULL值填充。

7.16.4 右外连接（RIGHT JOIN ON）

1
2
3
4
5
#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门也在查询结果中，员工信息以NULL值填充）
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,DEPARTMENT_NAME 
FROM t_employees
RIGHT JOIN t_departments 
ON t_departments.DEPARTMENT_ID=t_employees.DEPARTMENT_ID;

该查询结果以部门表作为主表，有些部门没有员工，其员工信息全部为NULL。

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

八、DML操作【重点】

DDL（Data Manipulation Language）数据库操纵语言，是其中一类SQL语言，其实也就是增删改。

8.1 新增（INSERT）

语法：INSERT INTO 表名(列1,列2,列3......) VALUES(值1,值2,值3......);

8.1.1 添加一条信息

1
2
3
4
5
6
#添加一条岗位信息
INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) 
VALUES('JAVA_LE','Java Lecturer',6000,1200);
#添加一条员工信息
INSERT INTO t_employees(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES('194','Liu','YUNChe','xxx@qq.com','12345','2020-11-14','JAVA_LE','11000','0.6',null,'50');
  • 注意：表名后的列名要和VALUE里的值一一对应（个数、顺序、类型）。

8.2 修改（UPDATA）

1
2
3
4
#修改编号为100的员工工资为25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';
#修改编号为100的员工信息岗位编号为 ST_MAN，工资为3500
UPDATE t_employees SET JOB_ID = 'ST_MAN',SALARY = 3500 WHERE EMPLOYEE_ID = '135';
  • 注意：SET后多个列名=值，绝大多数情况下都要加WHERE条件指定修改，否则为整表更新。

8.3 删除（DELETE）

语法：DELETE FROM 表名 WHERE 条件;

1
2
3
4
#删除编号为135的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID = '135';
#删除姓Peter,并且名为Hall的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID = '135';
  • 注意：删除时，如果不加条件，则默认删除整张表。

用DELETE删除整表是不彻底的，它只是删除了内容，并没有改变表的结构。

8.4 清空整表数据（TRUNCATE）

1
COPYTRUNCATE TABLE 表名;

8.4.1 清空整张表

1
2
COPY#清空t1整张表
TRUNCATE TABLE t1;
  • 注意：与DELETE不加WHERE删除整表数据不同，TRUNCATE是把原表销毁，再按原表的格式创建一张新表。

九、数据表操作

9.1 数据类型

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

9.1.1 数值类型

类型 大小 范围（有符号） 范围（无符号） 用途
INT 4字节 (-2147483648,2147483647) (0,4294967295) 大整数值
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 小数值

9.1.2 日期类型

类型 大小 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DETETIME 8 1000-01-01 00:00:00/9999-12-31 23: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 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

9.1.3 字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
BLOB（binary large object） 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
  • CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同，CHAR(N)会将多余的存储位以空格补足，而VARCHAR(N)则会根据实际存储长度进行“压缩”。在存储或检索过程中不进行大小写转换。
  • BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

9.2 数据表的创建（CREATE）

1
2
3
4
5
6
CREATE TABLE 表名(
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ......
    列名 数据类型 [约束]#最后一行的末尾不加逗号
)[CHARSET=UTF8]#可根据需要指定表的字符编码集

括号内的内容为可选。

9.2.1 创建表

列名 数据类型 说明
subject_id INT 课程编号
subject_name VARCHAR(20) 课程时间
subject_hours INT 课程课长 
1
2
3
4
5
6
7
8
9
10
11
12
#根据上述表格创建数据库，并向表中插入3条测试语句
CREATE TABLE `t_subject` (
  `subject_id` int,
  `subject_name` varchar(20),
  `subject_hours` int(11)
)CHARSET=utf8;

INSERT INTO t_subject(subject_id,subject_name,subject_hours) 
VALUES(1,'JAVA',40);
INSERT INTO t_subject(subject_id,subject_name,subject_hours) 
VALUES(2,'MYSQL',30);
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(3,'JAVASCRIPT',20)

9.3 数据表的修改（ALTER）

语法：ALTER TABLE 表名 操作;

9.3.1 向现有表中添加列

1
2
#在课程表基础上添加grade_id列
ALTER TABLE t_subject ADD grade_id INT;

9.3.2 修改表中的列

1
2
#修改课程表中的课程名称长度为10个字符
ALTER TABLE t_subject MODIFY subject_name VARCHAR(10);
  • 注意：修改表中的某列时，也要写全列的名字，数据类型，约束。

9.3.3 删除表中的列

1
2
#删除课程表中grade_id列
ALTER TABLE t_subject DROP grade_id;
  • 注意：删除列时，每次只能删一列。

9.3.4 修改列名

1
2
#修改课程表中subject_hours列为class_hours
ALTER TABLE t_subject CHANGE subject_hours class_hours INT;
  • 注意：修改列名时，在给定列新名称时，要指定列的类型和约束。

9.3.5 修改表名

1
2
#修改课程表的t_subject为t_sub
ALTER TABLE t_subject RENAME t_sub;

9.4 数据表的删除（DROP）

语法：DROP TABLE 表名

9.4.1 删除课程表

1
2
#删除课程表
DROP TABLE t_sub;

十、约束

问题：在往已创建表中新增数据时，可不可以新增两行相同列的数据？如果可行，会有什么弊端？

这是可以的，但重复的数据不仅会浪费空间，而且对查询结果不利，也是多余的。在实际的存储中应尽量保证每条数据的唯一性。

10.1 实体完整性约束

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

10.1.1 主键约束

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

1
2
3
4
5
6
7
8
9
10
#为表中适用主键的列添加主键约束
CREATE TABLE t_subject(
#课程标号标识了每一个课程的编号唯一，且不能为NULL
subject_id INT PRIMARY KEY,
subject_name VARCHAR(20),
subject_hours INT
)CHARSET=utf8;
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);
#报错，主键subject_id不可重复
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);

作为主键的字段一般都是也应该是编号、学号、工号等字段。

10.1.2 唯一约束

关键字：UNIQUE唯一，标识表中的一行数据，不可重复，可以为NULL。

1
2
3
4
5
6
#为表中列值不允许重复的列添加唯一约束
ALTER TABLE t_subject MODIFY subject_name VARCHAR(20) UNIQUE;

INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);
#报错，课程名已存在
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(2,'JAVA',40);

10.1.3 自动增长列

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

1
2
3
4
#为表中主键列添加自动增长，避免忘记主键ID序号
ALTER TABLE t_subject MODIFY subject_id INT AUTO_INCREMENT;
#课程ID自动增长为2
INSERT INTO t_subject ( subject_name, subject_hours ) VALUES( 'HTML5', 40 );

10.2 域完整性约束

该约束限制单元格的数据正确性。

10.2.1 非空约束

语法：NOT NULL,非空，此列必须有值

1
2
3
4
#课程名称虽然添加了唯一约束，但是有NULL值存在的可能，要避免课程名称为NULL。
ALTER TABLE t_subject MODIFY subject_name VARCHAR(20) NOT NULL;
#报错，课程名不能为空
INSERT INTO t_subject(subject_name,subject_hours) VALUES (NULL,40);

10.2.2 默认值约束

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

1
2
3
4
#当存储课程信息时，若课程时长没有指定值，则以默认值进行填充
ALTER TABLE t_subject MODIFY subject_hours INT DEFAULT 40;
#不写默认课程时为40，也可以把值写成DEFAULT
INSERT INTO t_subject(subject_name) VALUES('JAVAWEB');

10.2.3 引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

解释：FOREIGN KEY引用外部表某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#创建专业表
CREATE TABLE t_speciality(
	special_id INT PRIMARY KEY AUTO_INCREMENT,
	special_name VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
#添加一些专业信息
INSERT INTO t_speciality(special_name) VALUES('HTML5');
INSERT INTO t_speciality(special_name) VALUES('JAVA');
INSERT INTO t_speciality(special_name) VALUES('JAVAWEB');
INSERT INTO t_speciality(special_name) VALUES('数据结构');
#subject_id引用special_id
ALTER TABLE t_subject ADD 
CONSTRAINT fk_subject_spacial FOREIGN KEY(special_id) REFERENCES t_speciality(special_id);
#添加数据成功
INSERT INTO t_subject(subject_name,special_id) VALUES('数据结构',4);
#添加失败，主表中不存在5
INSERT INTO t_subject(subject_name,special_id) VALUES('操作系统',5);
  • 注意：当两张表存在引用关系时，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）。

10.3 约束创建整合

创建带有约束的表。

列名 数据类型 约束 说明
grade_id INT 主键、自动增长 班级编号
grade_name VARCHAR(20) 唯一、非空 班级名称 
1
2
3
4
CREATE TABLE t_grade(
	grade_id INT PRIMARY KEY AUTO_INCREMENT,
	grade_name VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
列名 数据类型 约束 说明
student_id VARCHAR(20) 主键 学号
student_name VARCHAR(20) 非空 姓名
sex CHAR(2) 默认填充“男” 性别
born_date DATE 非空 生日
phone VARCHAR(11) 无 电脑
grade_id INT 非空、外键约束引用班级表的grade_id 班级编号 
1
2
3
4
5
6
7
8
9
CREATE TABLE t_student(
	student_id VARCHAR(20) PRIMARY KEY,
	student_name VARCHAR(20) NOT NULL,
	sex CHAR(2) DEFAULT '男',
	born_date DATE NOT NULL,
	phone VARCHAR(11),
	grade_id INT NOT NULL,
	CONSTRAINT fk_student_grade FOREIGN KEY (grade_id) REFERENCES t_grade(grade_id)
)CHARSET=utf8;
  • 注意：创建关系表时，一定要先创建主键，再创建从表。
  • 删除关系表时，先删除从表，再删除主表。

十一、事务【重点】

11.1 模拟转账

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

11.1.1 数据库模拟转账

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#A账户转账给B账户1000元
#B账户减1000元

#新建账户表
CREATE TABLE t_account(
	account_id INT PRIMARY KEY,
	money INT
)CHARSET=utf8;
#插入数据
INSERT INTO t_account(account_id,money) VALUES(1,5000);
INSERT INTO t_account(account_id,money) VALUES(2,5000);
#模拟转账
UPDATE t_account SET money=money-1000 WHERE account_id=1;#转账
UPDATE t_account SET money=money+1000 WHERE account_id=2;#收账
  • 上述代码完成了两个账户之间转账的操作。

11.1.2 模拟转账错误

1
2
3
UPDATE t_account SET money=money-1000 WHERE account_id=1;#转账
#断电，异常，出错...
UPDATE t_account SET money=money+1000 WHERE account_id=2;#收账
  • 上述代码在减操作后过程中出现了异常或加钱语句出错，会发现减钱成功了而加钱失败了！
  • 注意：每条SQL语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

11.2 事务的概念

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

11.3 事务的边界

  • 开始：连接到数据库，执行一条DML语句。上一个事务结束后，又输入了一条DML语句，即事务的开始。
  • 结束：
    1. 提交：
      • 显式提交：COMMIT；
      • 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）；
    2. 回滚：
      • 显式回滚：ROLLBACK；
      • 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

如11.1.2节中的两条语句就是两个事务，第一条语句执行便是事务的开始，分号便是事务的结束，执行完毕该语句就被提交了；而第二句由于执行失败，该语句的结果就是回滚，回到事务前的样子。

11.4 事务的原理

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

11.5 事务的特性（ACID）

  • Atomicity（原子性）

    表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。

  • Consistency（一致性）

    表示一个事务内有一个操作失败时，所有更改过的数据都必须回滚到修改前状态。

  • Isolation（隔离性）

    事务查看数据库操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事物修改它之后的状态，事务不会查看中间状态的数据。

  • Durability（持久性）

    持久性事务完成之后，它对与系统的影响是永久性的。

11.6 事务应用

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

11.6.1 事务完成转账

手动开启事务，将两个减钱和加钱的操作放到事务中，操作完之后再提交。

1
2
3
4
5
6
7
8
#开启事务
START TRANSACTION;
#执行减钱操作（两个账户初始钱都为5000）
UPDATE t_account SET money=money-1000 WHERE account_id = 1;
#执行加钱操作
UPDATE t_account SET money=money+1000 WHERE account_id = 2;
#查看该事务中账户数据
SELECT * FROM t_account;

以上SQL语句开启了事务，但并未进行提交，此时查看账户中的数据如下：

account_id money
1 4000
2 6000

发现数据已经变了，但该数据只是事务的中间状态。

然后在你的数据库管理系统中新建一个连接，新建连接名随便取一个，然后新建查询。这么做相当于模拟了另外一个客户端：

执行SELECT * FROM t_account;，发现表中的数据并没有改变：

account_id money
1 5000
2 5000

这是因为在第一个连接中开启了事务，但没有提交；而在another连接中，查询语句也是一个事务（没有显式开启和显式提交），该事务只能查看其它事务修改前后者修改后的状态，此处就是只能查看转账操作提交前的状态，两个账户都是5000，这也是事务的隔离性。

回到localhost连接中，提交事务；然后在another连接中再查询一次：

1
2
3
4
#localhost连接，提交事务
COMMIT;
#another连接，查询表
SELECT * FROM t_account;

此时的查询结果如下：

account_id money
1 4000
2 6000

发现数据已经正式修改完毕。

回滚操作类似，但它是取消掉事务的操作，回到事务前的状态。比如事务开启后，减钱成功，但是加钱失败了（可以用SQL语句某个单词写错来模拟），然后手动执行ROLLBACK，减钱操作就会被撤销。不再演示。

  • 此处的演示是手动进行了事物的开启和提交/回滚，在写程序时，这些操作都会在程序中进行控制。

十二、权限管理

12.1 创建用户

语法：CREATE USER 用户名 IDENTIFIED BY 密码

1
2
#创建一个用户
CREATE USER 'client1' IDENTIFIED BY '123456';

接下来验证用户是否添加成功，首先新建连接，在用户名和账号那里写上自己创建的用户和密码，测试一下是否连接成功。

然后点“确定”。但是打开该连接之后你会发现此连接中除了一个系统数据库，其他数据库都不存在。这是因为client1用户建立的连接没有得到任何授权，也就是没有对数据库的访问操作权限。

12.2 授权

语法：GRANT ALL ON 数据库.表 TO 用户名;

1
2
#将companydb下的所有表的权限都赋给client
GRANT ALL ON companydb.* TO client1;

该语句需要在root连接中执行，不能在client1连接中自己给自己授权。

12.3 撤销权限

语法：REVOKE ALL ON 数据库.表名 FROM 用户名

  • 注意：撤销权限后，账户需要重新连接客户端才会生效。
1
2
#将client1的companydb的操作权限撤销
GRANT ALL ON companydb.* TO client1;

虽然刷新之后表不见了，但是依然可以对表进行操作，只有在重新连接数据库的时候权限才会消失。

12.4 删除用户

语法：DROP USER 用户名

1
2
#删除用户client1
DROP USER 'client1';

十三、视图

13.1 概念

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

  • 和临时表很像，但临时表不会被保存，而视图是保存下来的表。

13.2 视图特点

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

13.3 视图的创建

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

13.3.1 创建视图

1
2
3
4
#创建t_emp_info的视图，其视图从t_employees表中查询员工编号、姓名、邮箱、工资
CREATE VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY FROM t_employees;

13.3.2 使用视图

1
2
#查询t_emp_info视图中编号为036的员工信息
SELECT * FROM t_emp_info WHERE EMPLOYEE_ID = 036;

使用视图可以简化查询的操作，将来视图应用最多的地方也是查询。

13.4 视图的修改

  • 方式一：CREATE OR REPLACE VIEW 视图名 AS 查询语句
  • 方式二：ALTER VIEW 视图名 AS 查询语句

方式一是在不明确视图是否存在时使用，如果存在则修改，否则创建；方式二是明确存在时进行修改。无论哪种方式都需要拼接一个完整查询语句。

1
2
3
4
5
6
7
8
#方式一，如果视图存在则修改，反之创建。
CREATE OR REPLACE VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY,JOB_ID FROM t_employees;
#方式二，对已存在的视图进行修改
ALTER VIEW t_emp_info
AS
SELECT EMPLOYEE_ID,FIRST_NAME,EMAIL,SALARY FROM t_employees;

13.5 视图的删除

语法：DROP VIEW 视图名

1
2
#删除视图
DROP VIEW t_emp_info;
  • 注意：删除视图不会影响原表。

13.6 视图的注意事项

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

十四、SQL语言分类

  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、UNPATE、DELETE；
  4. 事务处理语言TPL（Transaction Process Language）：COMMIT、ROLLBACK；
  5. 数据控制语言DCL（Data Control Language）：GRANT、REVOKE。

十五、综合练习

某网上购物商城数据库表的结构如下：

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
# 创建用户表
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)
);
SELECT * FROM USER;
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('张三','123','北京昌平','13541489201');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('李四','678','北京海定','13541489202');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('王五','123','北京朝阳','13541489203');
INSERT INTO USER(username, PASSWORD, address,phone) VALUES('赵柳','123','北京大兴','13541489204');
# 创建分类表
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY,
	cname VARCHAR(100) NOT NULL  # 分类名称
);

INSERT INTO category VALUES('c001','电器');
INSERT INTO category VALUES('c002','服饰');
INSERT INTO category VALUES('c003','化妆品');
INSERT INTO category VALUES('c004','书籍');

# 商品表
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)
);

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',800,'c001');
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('p0010','梅明子',200,NULL);
# 订单表
CREATE TABLE `orders` (
	`oid` VARCHAR(32) PRIMARY KEY,
	`totalprice` DOUBLE(12,2), # 总计
	`userId` INT,
	CONSTRAINT fk_orders_userId FOREIGN KEY(userId) REFERENCES USER(userId) # 外键
);

INSERT INTO orders VALUES('o6100',18000.50,1);
INSERT INTO orders VALUES('o6101',7200.35,1);
INSERT INTO orders VALUES('o6102',600.00,2);
INSERT INTO orders VALUES('o6103',1300.26,4);
SELECT * FROM orders;
# 订单详情表
CREATE TABLE orderitem(
	oid VARCHAR(32), # 订单id
	pid VARCHAR(32), # 商品id
	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 orderitem VALUES('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

15.1 综合练习1-【多表查询】

  1. 查询所有用户的订单

    1
    2
    3
    # 查询所有用户的订单
    # 用户	订单表
    SELECT * FROM USER INNER JOIN orders ON orders.`userId` = user.`userId`;

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

    1
    2
    3
    4
    5
    6
    7
    8
    # 查询用户id为1的所有订单详情
    # 用户	 订单 	订单详情表
    SELECT * FROM USER
    INNER JOIN orders
    ON orders.`userId` = user.`userId`
    INNER JOIN orderitem
    ON orders.`oid` = orderitem.`oid`
    WHERE user.`userId` = 1;

15.2 综合练习2-【子查询】

  1. 查看用户为张三的订单

    1
    2
    3
    4
    # 查看用户为张三的订单
    # 子查询单行单列
    SELECT userId FROM USER WHERE username='张三';
    SELECT * FROM orders WHERE userId=(SELECT userId FROM USER WHERE username='张三');

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

    1
    2
    3
    4
    # 查询出订单的价格大于800的所有用户的信息
    # 子查询	多行单列	枚举查询
    SELECT DISTINCT userId FROM orders WHERE totalprice > 800;
    SELECT * FROM USER WHERE userId IN (SELECT DISTINCT userId FROM orders WHERE totalprice > 800);

15.3 综合练习3-【分页查询】

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

    1
    2
    3
    4
    5
    # 查询所有订单信息，每页显示5条数据
    # 查询第一页
    SELECT * FROM orders LIMIT 0, 5;
    # 查询第二页
    SELECT * FROM orders LIMIT 5, 5;

至此，MySql的基础学习已经告一段落，继续加油吧，在计算机这篇星辰大海中继续遨游。！

-------------本文结束感谢您的阅读-------------
云澈 wechat
扫一扫，用手机访问哦
坚持原创技术分享，您的支持将鼓励我继续创作！
0%