从零开始的SQL语言(MySQL)数据库全解（含例题）

基本操作

SQL (Structured Query Language)语言

组成

数据定义语言, DDL (Data Definition Language, DDL)

数据操纵语言 DML (Data Manipulation Lauaguage, DML)

数据控制语言DCL (Data Control Lauguage, DCL)

基本功能

数据定义

CREATE

DROP

ALTER

数据查询

SELECT

数据操纵

INSERT

UPDATE

DELETE

数据控制

GRANT

REVOKE

MySQL基本操作

创建数据库

创建数据库命令

CREATE DATABASE newdatabase;

设置数据库字符集的命令

ALTER DATABASE newdatabase CHARACTER SET utf8;

查看数据库文件存放路径的命令

SHOW VARIABLES LIKE 'datadir'

查看数据库

（查看当前MySQL服务器上的数据库列表的命令）

SHOW databases

选择当前数据库

USE A;

删除数据库

DROP DATABASE A;

创建数据库表

CREATE TABLE [IF NOT EXISTS] 表名
( 字段名1 数据类型 [ [ 约束] ]]
, [ , 字段名2 数据类型 [ [ 约束] ]]
……
, [ , 字段名n 数据类型 [ [ 约束] ]]
[ [ 其他约束条件] ]
[ )[

eg

use bookstore;
create table users
( 
uid int not null primary key auto_increment,
name varchar(20) not null unique,
pwd varchar(20) not null,
sex char(2)
);

常用关键字

AUTO_INCREMENT

用于设置整数类型字段的自动增量属性。 AUTO_INCREMENT 字段 必须被索引 ，而且必须为NOT NULL 。每个表 最多只能有一个字段 具有AUTO_INCREMENT

eg

uid int not null primary key auto_increment

DEFAULT

表中添加新行时给表中某一字段指定的默认值。使用 DEFAULT 定义，一是可以避免 NOT NULL 值的数据错误；二是可以加快用户的输入速度

eg

pwd varchar(20) not null default female

NOT NULL

指定 NOT NULL 属性的字段，不能有 NULL

name varchar(20) not null unique

UNSIGNED

表示该值不能为负数

PRIMARY KEY

主键

uid int not null primary key auto_increment

UNIQUE

唯一索引

name varchar(20) not null unique

FOREIGN KEY

create table emp
( 
foreign key(uid) references user(uid),
##emp.uid参照user.uid
...
);

查看数据库表结构

describe users;

显示表

修改数据库表结构

ALTER TABLE 表名 ACTION [,ACTION]…

✓ 添加、修改、删除表属性字段
✓ 添加、修改、删除表约束条件
✓ 删除索引
✓ 修改表名
✓ 修改表的存储引擎

删除数据库表

DROP TABLE [IF EXISTS] 表名 1 [, 表名,...];

eg

DROP TABLE users;

管理表记录

MySQL基本数据类型

整数类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sy0WKH4w-1677846347000)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119170211899.png)]

默认情况下，整数类型既可以表示正整数，也可以表示负整数

只希望表示正整数

age tinyint unsigned

指定其显示宽度

int(8)
##数值宽度小于8 8 位时在数字前面填满宽度

zerofill：在数字位数不够时需要用“ 0” 填充

插入的整数位数大于指定的显示宽度时，将按照整数的实际值进行存储

AUTO_INCREMENT ：在需要产生唯一标识符或顺序值时，可以利用此属性，该属性只适用于整数类型。一个表中最多只能有一个 AUTO_INCREMENT 字段，该字段应该为 NOT NULL ，并且定义为 PRIMARY KEY 或UNIQUE 。AUTO_INCREMENT 字段值从1开始，每行记录其值增加1 。当插入 NULL 值到一个AUTO_INCREMENT 字段时，插入的值为该字段中当前最大值加1。

小数类型

浮点数和定点数

浮点数包括单精度浮点数 FLOAT 类型和双精度浮点数DOUBLE 类型，定点数为 DECIMAL 类型

定点数在 MySQL 内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据

浮点数和定点数都可以在类型后面加上（ M ，D ）来表示，M 表示该数值一共可显示 M 位数字，D 表示该数值小数点后的位数

在类型后面指定（ M ，D ）时，小数点后面的数值需要按照D来进行四舍五入

当不指定（ M ，D D ）时，浮点数将按照实际值来存储，而DECIMAL 默认的整数位为 10 ，小数位为0

字符串类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xvrmJey4-1677846347002)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119171204030.png)]

CHAR 与 VARCHAR 都是用来保存 MySQL 中较短的字符串，二者的主要区别在于存储方式不同。CHAR(n) 为定长字符串类型，n n 的取值为0 0 ～ 255 ；VARCHAR(n) 为变长字符串类型，n n 的取值为0 0 ～ 255 （ 5.0.3 版本以前）或0 0 ～ 65535 （ 5.0.3 版本以后）。 CHAR(n) 类型的数据在存储时会删除尾部空格，而 VARCHAR(n) 在存储数据时则会保留尾部空格。

日期时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XkvjFjQ5-1677846347003)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119171350322.png)]

在 YEAR 类型中，年份值可以为2位或4位，默认为4位。在4位格式中，允许值的范围为 1901 ～ 2155 。在2 位格式中，取值为 70 ～ 99 时，表示从 1970 年～ 1999 年；取值为 01 ～ 69 时，表示从 2001 年～ 2069 年

DATETIME 与 TIMESTAMP 都包括日期和时间两部分，但TIMESTAMP 类型与时区相关，而 DATETIME则与时区无关

如果在一个表中定义了两个类型为 TIMESTAMP 的字段，则表中第一个类型为 TIMESTAMP 的字段其默认值为
CURRENT_TIMESTAMP ，第二个 TIMESTAMP 字段的默认值为 0000- - 00- - 00 00:00:00 。

复合类型

MySQL 中的复合数据类型包括： ENUM 枚举类型和 SET 集合类型。ENUM 类型只允许从集合中取得某一个值， SET 类型允许从集合中取得多个值。 ENUM 类型的数据最多可以包含 65535 个元素， SET 类型的
数据最多可以包含 64 个元素。

MySQL运算符

算术运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fMDzEs9S-1677846347003)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119171940623.png)]

比较运算符

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e3RoSean-1677846347004)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119172026215.png)]

varchar_field like '%Li' 
##字符串是否以“ Li ”结尾

varchar_field regexp '^Mr'
##字符串是否以“ Mr ”开头

varchar_field regexp 'Li$' 
##字符串是否以“ Li ”结尾

逻辑运算符

逻辑非（ NOT 或！）、逻辑与（ AND 或 && ）、逻辑或（ OR 或 || ）和逻辑异或（ XOR）

逻辑异或（ XOR ）：当任意一个操作数为 NULL 时，逻辑异或的返回值为 NULL 。对于非 NULL 操作数，如果两个操作数的逻辑真假值相异，则返回结果为1 ；否则返回值为0。

位运算符

按位与（& ）、按位或（|）、按位取反（~）、按位异或（^）

左移（ << ）和右移（ >> ）

运算符的优先级

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bxRhk6dB-1677846347004)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119172733495.png)]

字符集设置

**字符排序规则（ collation ）**是指在同一字符集内字符之间的比较规则，一个字符集可以包含多种字符排序规则，每个字符集会有一个默认的字符排序规则

MySQL 中字符排序规则命名方法为：以字符排序规则对应的字符集开头，中间是国家名（或 general ），以ci 、cs 或 bin 结尾。以ci 结尾的字符排序规则表示大小写不敏感，以cs 结尾的字符排序规则表示大小写敏感，以 bin 结尾的字符排序规则表示按二进制编码值进行比较。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H0Xkykva-1677846347005)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119173105758.png)]

增添表记录

INSERT

使用 INSERT 语句可以将一条或多条记录插入表中，也可以将另一个表中的结果集插入到当前表中。

INSERT INTO employee
VALUES(19, null, '四', '李', '2022-9-22', '出纳员', 1, 2, 4);

insert into course values(NULL,'C 程序设计 ',default,'0412893402');
insert into course( course_name,teacher_id ) values('Java 程序设计','0412893401');
insert into course values( NULL,'MySQL 数据库‘,60,'0412893403');

INSERT INTO employee(EMP_ID,END_DATE,FIRST_NAME,LAST_NAME,START_DATE,TITLE,ASSIGNED_BRANCH_ID,SUPERIOR_EMP_ID,DEPT_ID)
SELECT 20,NULL,FIRST_NAME,LAST_NAME,'2022-9-26','出纳员'，1，2，4 FROM individual WHERE CUST_ID=1;

REPLACE

使用 REPLACE 语句也可以将一条或多条记录插入表中，或者将一个表中的结果集插入到目标表中。

使用 REPLACE 语句添加记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同时，则
已有记录被删除后再添加新记录。

replace into teacher values('0412893404',' 唐明明','13401234567');

UPDATE

当记录插入后，可以使用 UPDATE 语句对表中的记录进行修改

UPDATE employee
SET TITLE = '出纳主任'
WHERE EMP_ID = 19;

删除表记录

DELETE

DELETE from table_name
[WHERE condition]

在上面 DELETE 语句中，如果没有指定 WHERE 子句，则表中所有记录都被删除，但表结构仍然存在

使用 DELETE删除数据时，会有一个返回值，其含义是被删除的记录数目

eg

DELETE FROM acc_transaction
Where TXN_DATE < '2012-01-01';

TRUNCATE

TRUNCATE [table] table_name

TRUNCATE table 语句清空表记录后会重新设置自增型字段的计数起始值，但 DELETE 语句则不会。

检索表记录

单表查询

基本查询语句

SELECT…FROM查询语句

SELECT [ALL | DISTINCT] < 目标列表达式> [ ，< 目标列表达式>]
FROM < 表名或视图名>[ ，< 表名或视图名>]
[WHERE < 条件表达式>]
[GROUP BY < 列名1> [HAVING < 条件表达式>]]
[ORDER BY < 列名2> [ASC | DESC]]
[LIMIT [start,] count];

ALL 关键字表示将会显示所有检索的数据行，包括重复的数据行（默认）

DISTINCT 关键字表示仅显示不重复的数据行，对于重复的数据行，则只显示一次

LIMIT 10,20 表示从结果集的第11 行记录开始返回20行记录

条件查询语句

where 子句

使用关系表达式查询

关系表达式是指在表达式中含有关系运算符

常见的关系运算符有：= （等于）、> （大于）、< （小于）、>= （大于等于）、<= （小于等于）、!= 或**<>** （不等于）

SELECT B_ID,B_Name, B_SalePrice FROM BookInfo
WHERE B_SalePrice>40;

使用逻辑表达式查询

常用的逻辑运算符有AND 、OR 和NOT

当一个WHERE 子句同时包括若干个逻辑运算符时，其优先级从高到低依次为NOT 、AND 、OR

如果想改变优先级，可以使用括号

SELECT B_ID,B_Name, B_SalePrice
FROM BookInfo
WHERE B_SalePrice>=20 AND B_SalePrice<=40;

设置取值范围的查询

谓词BETWEEN …AND 和NOT BETWEEN …AND 可以用来设置查询条件。（ 建议使用>=, <= ）

SELECT B_ID,B_Name, B_SalePrice
FROM BookInfo
WHERE B_SalePrice BETWEEN 20 AND 40;

空值查询

NULL 是特殊的值，代表“无值”，与0 、空字符串或仅仅包含空格都不相同

在涉及空值的查询中，可以使用IS NULL 或者IS NOT NULL

SELECT U_ID,U_Name
FROM Users 
WHERE U_Phone IS NULL;

模糊查询

要实现模糊查询，必须使用通配符，利用通配符可以创建和特定字符串进行比较的搜索模式

%:代表任意多个字符

_:代表任意的一个字符

如果查询条件中使用了通配符，则操作符必须使用LIKE 关键字，用于搜索与特定字符串相匹配的字符数据，其基本的语法形式为

[NOT] LIKE < 匹配字符串>

SELECT B_Name,B_Publisher,B_SalePrice
FROM BookInfo
WHERE B_Name LIKE '%MySQL%';

SELECT B_Name,B_Author,B_Publisher
FROM BookInfo
WHERE B_Author LIKE '_ 国%';

如果要查询的字符串本身就含有通配符，此时就需要用ESCAPE 关键字，对通配符进行转义

SELECT * FROM Users
WHERE U_Name LIKE '%/_%' ESCAPE '/';
##查询会员名中含有“_” 的会员信息

分组查询语句

聚集/合函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sdSioJ7g-1677846347005)(C:\Users\Fang\AppData\Roaming\Typora\typora-user-images\image-20230119190231017.png)]

除了Count(*) 之外聚合函数忽略列值为NULL 的行

SELECT COUNT(*) FROM Users;
##统计Users 表中会员的数量
SELECT COUNT(U_Phone) FROM Users;
##统计填写电话号码的会员个数

Group by 子句

SELECT B_Publisher ,COUNT(*) AS 总数
FROM BookInfo
GROUP BY B_Publisher;
## 检索BookInfo 表，查询每个出版社出版的图书的数量

SELECT B_Publisher,MAX(B_MarketPrice) AS 最高价格,MIN(B_MarketPrice) AS 最低价格
FROM BookInfo
GROUP BY B_Publisher;
##检索BookInfo 表，查询每个出版社图书的最高价格和最低价格

having子句

如果分组以后要求按一定条件对这些组进行筛选 ，则需要使用HAVING 子句指定筛选条件

SELECT B_Publisher ,COUNT(*) AS 总数
FROM BookInfo
WHERE B_MarketPrice>=50
GROUP BY B_Publisher
HAVING COUNT(*)>=2;
##检索BookInfo 表，查询出版了2本及2本以上并且价格大于等于50元的图书信息。

Having 子句 vs. where 子句

HAVING 子句和WHERE 子句都是设置查询条件，但两个子句的作用对象不同,WHERE 子句作用的对象是基本表或视图 ，从中选出满足条件的记录；而HAVING 子句的作用对象是分组 ，从中选出满足条件的分组，WHERE 在数据分组之前进行过滤，而HAVING 在数据分组之后进行过滤

多表查询

表连接

SELECT <查询列表>
FROM <表名1> [连接类型] JOIN <表名2> ON <连接条件>
WHERE <查询条件>

连接类型 有3 种：内连接（INNER JOIN ）、外连接（OUTER JOIN ）和交叉连接（CROSS JOIN ）

用来连接两个表的条件称为连接条件 ，通常是通过匹配多个表中的公共字段来实现的

内连接（INNER JOIN ）

内连接是最常用的连接类型，也是默认的连接类型，在FROM 子句中使用INNER JOIN （INNER 关键
字可以省略）来实现内连接

SELECT B_Name, BookInfo.BT_ID, BT_Name
FROM BookInfo INNER JOIN BookType
ON BookInfo.BT_ID= BookType.BT_ID
ORDER BY BT_ID;
##检索BookInfo和BookType表，查询每本图书所属的图书类别

SELECT U.U_Name,O.O_ID, O.O_Time, O.O_TotalPrice
FROM Users U INNER JOIN Orders O
ON U.U_ID = O.U_ID
WHERE O_TotalPrice>100;
##检索Users 和Orders 表，查询订单总价超过100

外连接（OUTER JOIN ）

使用外连接时，以主表中每行的数据去匹配从表中的数据行，如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行仍然保留，并且返回到结果集中，相应的从表中的数据行被填上NULL 值后也返回到结果集中

外连接有3 种类型，分别是左外连接（LEFT OUTER JOIN ）、右外连接（RIGHT OUTER JOIN ）和全外连接（FULL OUTER JOIN ）

MySQL 暂不支持全外连接

左外连接（LEFT OUTER JOIN ）

左外连接的结果集中包含左表（JOIN 关键字左边的表）中所有的记录，如果右表中没有满足连接条件的记录，则结果集中右表中的相应行数据填充为NULL

SELECT U.U_ID,U.U_Name, O.O_Time,O.O_TotalPrice
FROM Users U LEFT OUTER JOIN Orders O
ON U.U_ID = O.U_ID
ORDER BY U_ID;

右外连接（RIGHT OUTER JOIN ）

右外连接的结果集中包含满足连接条件的所有数据和右表（JOIN 关键字右边的表）中不满足条件的数据，左表中的相应行数据为NULL

SELECT OD.OD_ID,OD_Number,BI.B_ID,BI.B_Name
FROM OrderDetails OD RIGHT OUTER JOIN BookInfo BI
ON OD.B_ID = BI.B_ID;

自连接（非自然连接）

在同一个表中进行的连接被称为自连接，可以看作是这张表的两个副本之间进行的连接，必须为该表指定两个别名

SELECT B2.B_ID,B2.B_Name,B2.B_SalePrice
FROM BookInfo B1 INNER JOIN BookInfo B2
ON B1.B_Name='C# 基础与案例开发详解' AND
B1.B_SalePrice<B2.B_SalePrice
ORDER BY B2.B_SalePrice DESC;
##要查询BookInfo 表中高于“C# 基础与案例开发详解”会员价格的图书号、图书名称和图书会员价格，查询后的结果集要求按会员价格降序排列

交叉连接(CROSS JOIN)

如果不带WHERE 子句时，则返回的结果是被连接的两个表的笛卡尔积

如果交叉连接带有WHERE 子句时，则返回结果为连接两个表的笛卡尔积减去WHERE 子句所限定而省略的行数

SELECT O.O_ID,OD.OD_ID
FROM Orders O CROSS JOIN OrderDetails OD;

子查询

子查询是指在一个外层查询中包含另一个内层查询，即在一个SELECT 语句中的WHERE 子句 中，包含有另一个SELECT语句

外层的SELECT 语句称为主查询 ，WHERE 子句中包含的SELECT 语句称为子查询

一般将子查询的查询结果作为主查询的