MySQL笔记

一、数据库

1、数据存储方式

存储方式	优点	缺点
保存在内存	内存速度快	断电/程序退出，数据就清除了，内存价格贵
保存在普通文件	永久保存	增删查改数据麻烦，效率低
保存在数据库	永久保存，通过SQL语句操作数据库数据

2、数据库管理系统、数据库和表的关系

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

DBMS可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。

先有数据库 → 再有表 → 再有数据
一个库包含多个表

二、SQL语句

结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。

1、SQL语句分类

SQL语句	说明	意义
DDL	数据定义语言	用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等
DML	数据操作语言	用来对数据库中表的数据进行增删改。关键字：insert, delete, update等
DQL	数据查询语言	用来查询数据库中表的记录(数据)。关键字：select, where等
DCL	数据控制语言	用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE等

DDL(Data Definition Language)数据定义语言
DML(Data Manipulation Language)数据操作语言
DQL(Data Query Language)数据查询语言
DCL(Data Control Language)数据控制语言(了解)

2、SQL语法

不区分大小写，关键字建议使用大写。（大小写效果一样，但是在编译的时候，会把关键字从小写转化为大写，所以直接大写效率会高。）

3、DDL语句

DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等

3-1、DDL操作数据库 DATABASE

（1）创建数据库 CREATE

// 1、直接创建
CREATE DATABASE 数据库名;
CREATE DATABASE db1;
// 2、判断是否存在并创建
CREATE DATABASE IF NOT EXISTS 数据库名;
CREATE DATABASE IF NOT EXISTS db2;
// 3、创建数据库并指定字符集（编码表）
CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE db3 CHARACTER SET gbk;

（2）查看数据库 SHOW

// 1、查看所有的数据库
SHOW DATABASES;
// 2、查看某个数据库的定义信息
SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE db1;

（3）修改数据库字符集格式 ALTER

// 比创建数据库并指定字符集（编码表）多了DEFAULT
ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
ALTER DATABASE db3 DEFAULT CHARACTER SET utf8;

（4）删除数据库 DROP

DROP DATABASE 数据库名;
DROP DATABASE db2;

（5）使用数据库 USE

// 1、查看正在使用的数据库
SELECT DATABASE();
// 2、使用/切换数据库
USE 数据库名;

3-2、DDL操作表 TABLE

前提是先使用某个数据表。

（1）创建表 CREATE

// CREATE 创建 TABLE 表
CREATE TABLE 表名 (字段名1 字段类型1, 字段名2 字段类型2 ...);
//一般写成以下格式：
CREATE TABLE 表名 (
	字段名1 字段类型1,
	字段名2 字段类型2,
	......
);

常用的字段数据类型：

类型	描述
int	整型
double	浮点型
varchar	字符串型
date	日期类型：yyyy-MM-dd

例子：创建student表包含id,name,birthday字段

CREATE TABLE student (
	id INT,
	name VARCHAR(20),
	birthday DATE
);

创建了一个含有id,name,birthday等字段（表头）的表，没有数据。

（2）查看表 SHOW DESC

// 1、查看该数据库所有的表
SHOW TABLES;
// 2、查看表结构
DESC 表名;
// 3、查看创建表的SQL语句
SHOW CREATE TABLE 表名;

（3）创建一个表结构相同的表 CREATE … LIKE

CREATE TABLE 新表名 LIKE 旧表名;
CREATE TABLE student1 LIKE student;

（4）删除表 DROP

// 1、直接删除表
DROP TABLE 表名;
// 2、判断表是否存在并删除表
DROP TABLE IF EXISTS 表名;
// 注意：此处与判断是否存在数据库并创建的语法不同之处在于NOT。

（5）修改表结构 ALTER

// 1、添加表列 ADD
ALTER TABLE 表名 ADD 字段 类型;
// 为studen表添加一个新的字段remark，类型为varchar(20)
ALTER TABLE student ADD remark VARCHAR(20)

// 2、修改列类型 modify
ALTER TABLE 表名 MODIFY 字段 新的类型;
// 将student表的remark类型修改为varchar(100)
ALTER TABLE student MODIFY remark VARCHAR(100);

// 3、修改列名 change
ALTER TABLE 表名 CHANGE 旧字段 新字段 类型;
// 将student表的remark字段改为mark，类型为varchar(30)
ALTER TABLE student CHANGE remark mark VARCHAR(30);

// 4、删除列（字段）drop
ALTER TABLE 表名 DROP 字段;
// 将student表中的mark字段删除
ALTER TABLE student DROP mark;

// 5、修改表名 rename ... to
RENAME TABLE 旧表名 TO 新表名;
// 将student表名修改为teacher
RENAME TABLE student TO teacher;

// 6、修改字符集 character
ALTER TABLE 表名 CHARACTER SET 字符集;
ALTER TABLE teacher CHARACTER SET gdk;

注意：
①一般删除在最前面用DROP，但在表中删除字段时，用ALTER 再DROP。
②修改一般是用ALTER，但修改表名的时候是使用RENAME

4、DML语句

DML(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改。关键字：insert, delete, update等

4-1、插入记录 INSERT

INSERT INTO 表名
(字段名1, 字段名2 ...)
VALUES (值1, 值2 ...)

注意：
①值与字段必须对应，个数相同，类型相同
②值的数据大小必须在字段的长度范围内
③除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
④如果要插入空值，可以不写字段，或者插入null

// 1、插入全部字段
// 1-1、所有的字段名均写出
INSERT INTO 表名 (字段名1, 字段名2 ...) VALUES(值1, 值2 ...);
// 1-2、不写字段名
INSERT INTO 表名 VALUES(值1, 值2 ...);
// 为teacher插入数据
INSERT INTO teacher VALUES(1, '老王', '99-02-03');
SELECT * FROM teacher;

对插入数据的格式要求：

INSERT INTO teacher VALUES(1, '老吴', '99-02-03');
INSERT INTO teacher VALUES(2, '老王', '99-2-3');
INSERT INTO teacher VALUES(3, '小吴', '9-02-03');
// 如果date格式不符合（x-x-x），将会报错，如果符合（x-x-x）而不符合（xxxx-xx-xx）,则系统会默认补齐。
SELECT * FROM teacher;

// 2、插入部分数据
INSERT INTO 表名 (字段名1, 字段名2 ...) VALUES(值1, 值2 ...);
// 没有添加数据的字段会使用NULL

// 3、蠕虫复制
// 在已有的表数据基础上进行复制，插入到对应表字段中
// 语法 将表名2的数据复制到表名1中
INSERT INTO 表名1 SELECT * FROM 表名2;
// 具体实现：
// 创建student表student1表与teacher表结构一致
CREATE TABLE student LIKE teacher;
CREATE TABLE student1 LIKE teacher;
// 将teacher所有的数据复制到student中
INSERT INTO student SELECT * FROM teacher;
// 将teacher中id,name复制到student1中
INSERT INTO student1(id, name) SELECT id, name FROM teacher;

4-2、更新表记录 UPDATE

// UPDATE：修改数据；SET：修改哪些字段；WHERE：指定条件。
// 1、不带条件修改数据
UPDATE 表名 SET 字段名=值;

// 2、带条件的修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

// 3、修改多个字段或指定多个字段
UPDATE 表名 SET 字段名1=值1,字段名2=值2 ...;
UPDATE 表名 SET 字段名1=值1,字段名2=值2 ... WHERE 字段名=值;
UPDATE 表名 SET 字段名=值 WHERE 字段名1=值1,字段名2=值2 ... ;

// 4、删除表中所有数据
// 4-1、delete
DELETE FROM 表名; // 删除的是表中的数据，DROP TABLE 表名; 删除的是整个表
// 4-2、truncate
TRUNCATE TABLE 表名;
/*
 * delete是将表中的数据一条一条删除（不重置AUTO_INCREMENT的值）
 * truncate是将整个表摧毁，再重新创建一个与原表结构一致的表（AUTO_INCREMENT重置为1）
 */

5、DQL语法

DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据)。关键字：select, where等

5-1、查询 SELECT

// 1、查询所有数据
SELECT * FROM 表名;

// 2、查询部分数据
SELECT 字段名1, 字段名2 ... FROM 表名;

// 3、别名查询
// 使用别名的好处是方便观看和处理查询到的数据
// 原字段名显示为别名
SELECT 字段名1 AS 别名, 字段名2 AS 别名 ... FROM 表名;
// AS可以省略不写
SELECT id AS 号码, name AS 姓名 FROM teacher;
SELECT id 号码, name 姓名 FROM teacher;

// 4、清除重复值 DISTINCT
SELECT DISTINCT 字段名 FROM 表名;

SELECT DISTINCT * FROM teacher;
SELECT DISTINCT birthday FROM teacher;
SELECT DISTINCT id,birthday FROM teacher;

由上可知，若查询的字段有多个，需查询的所有字段的值重复才删去。

// 5、查询结果参与运算
// 5-1、某列数据和固定值运算
SELECT 字段名1 + 固定值 FROM 表名;

// 5-2、某列数据和其他列数据参与运算
SELECT 字段名1 + 字段名2 FROM 表名;
/*
 * 将显示新的字段名：字段名1+字段名2
 */
SELECT 字段名1 + 字段名2 别名 FROM 表名;
/*
 * 将显示新的字段名：别名
 */
SELECT *, 字段名1 + 字段名2 别名 FROM 表名;
/*
 * 查询所有列与字段名1 + 字段名2 的和并使用别名”别名”
 */

注：参与运算的必须是数值类型。

5-2、条件查询 SELECT … WHERE

// 查询到满足条件的一条条数据
SELECT 字段名 FROM 表名 WHERE 条件;
// 流程：取出表中的每条数据，满足条件的记录就返回

比较运算符	意义
>	大于
<	小于
<=	小于等于
>=	大于等于
=	等于
<> 、!=	不等于

逻辑运算符	意义
and	与
or	或
not	不满足

关键字in

// 其实in就是=操作加or操作
SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2 ...);

// 查询id为1或3或5
SELECT * FROM hero WHERE id in (1,3,5);
// 查询id不是1或3或5
SELECT * FROM hero WHERE id not in (1,3,5);

5-3、范围查询BETWEEN … AND …

// 表示从值1到值2的范围，包头又包尾，闭区间
SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN 值1 AND 值2;
// 查询年龄在10到50之间的记录
SELECT * FROM hero WHERE age BETWEEN 10 AND 50;

5-4、模糊查询LIKE

通配符	意义
%	表示0个或多个字符（任意个字符）
_	表示一个字符

举例：
①’孙%’：开头为孙，后跟任意字符均符合。
②’%孙%’：整个字符串中有“孙”字即符合。
③’孙__’：（此处有两个_）开头为孙，后跟两个字符即符合。

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE '通配符字符串';

// 查询姓孙的
SELECT * FROM hero WHERE name LIKE '孙%';
// 查询姓名中包含“孙”字的
SELECT * FROM hero WHERE name LIKE '%孙%';
// 查询姓孙并且名字为三个字的
SELECT * FROM hero WHERE name LIKE '孙__';

5-5、排序ORDER BY

通过ORDER BY可以将查询结果进行排序（此处排序只是显示时排序，不会影响数据库中数据的顺序）

ASC: 升序, 默认是升序
DESC: 降序

// 单列排序
SELECT 字段名 FROM 表名 WHERE 字段名=值 ORDER BY 字段名 升降序或不写;
// 查询年龄小于等于30的，按年龄升序排列
SELECT * FROM hero WHERE age <= 35 ORDER BY age ASC;
SELECT * FROM hero WHERE age <= 35 ORDER BY age;

// 组合排序
// 先按第一个字段排序，若第一个字段相同，则按第二个字段排序，依次类推
SELECT 字段名 FROM 表名 WHERE 字段名=值 ORDER BY 字段名1 升降序或不写, 字段名2 升降序或不写;

5-6、聚合函数

聚合函数：之前做的查询都是横向查询，是根据条件一行一行判断，二聚合函数是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值。

聚合函数	说明
count	统计指定列记录数，记录为NULL的不统计
sum	计算指定列的数值和，如果不是数值类型，则计算结果为0
max	最大值
min	最小值
avg	计算指定列的平均值，如果不是数值类型，则计算结果为0

SELECT 聚合函数(字段名) FROM 表名;
// 也可加上指定条件
SELECT 聚合函数(字段名) FROM 表名 WHERE 条件;

// 1、统计student表的数量
SELECT COUNT(*) FROM student; // 3
// 2、统计age的数量（非数值的不统计）
SELECT COUNT(age) FROM student; // 2
// 3、统计age>9的数量
SELECT COUNT(age) FROM student WHERE age>9; // 1
// 4、计算age的总数
SELECT SUM(age) FROM student; // 18
// 5、计算age的平均值（非数值的不统计，因此sum为18，个数为2）
SELECT AVG(age) FROM student; // 9.0000
// 6、age的最大值
SELECT MAX(age) FROM student; // 10
// 7、age的最小值
SELECT MIN(age) FROM student; // 8

5-7、分组查询 GROUP BY

SELECT 字段名 FROM 表名 GROUP BY 分组字段 [HAVING 条件];
// 返回的是每组的第一条记录，而不是各个分组的记录。所以单独使用没有意义

// 分组查询一般与聚合函数一起使用
SELECT 聚合函数(字段) FROM 表名 GROUP BY 分组字段 [HAVING 条件];
// 同时，我们应把分组的字段显示出来，不然不知道聚合函数的计算结果分别为谁的结果
SELECT 分组字段, 聚合函数(字段) FROM 表名 GROUP BY 分组字段 [HAVING 条件];

// 男女分组，统计各组的个数
SELECT sex, COUNT(*) FROM student GROUP BY sex;
// 男女分组，计算age的总数
SELECT sex, SUM(age) FROM student GROUP BY sex;

// 查询年龄小于11岁的人，按性别分组并统计各组的人数
SELECT sex, COUNT(*) FROM student WHERE age<11 GROUP BY sex;
// 查询年龄小于11岁的人，按性别分组并统计各组的人数,并只显示性别人数大于2的数据
SELECT sex, COUNT(*) FROM student WHERE age<11 GROUP BY sex HAVING COUNT(*)>2;

having与where的区别：

having	where
在分组后对数据进行过滤.	在分组前对数据进行过滤
having后面可以使用聚合函数	where后面不可以使用聚合函数

5-8、限制显示 LIMIT

限制显示的查询到记录的条数
LIMIT语句一般用在最后面，因为要把前面各种查询执行完，才能限制显示。

// offset：偏移量，跳过几条显示，默认为0，不写即为默认
// length：显示的总条数
LIMIT offset,length
LIMIT length

/*
 * ①查询年龄小于11岁的人
 * ②按性别分组并统计各组的人数
 * ③只显示性别人数大于2的数据
 * ④只显示从第二条开始的五条数据（不够五条则能显示到哪就显示到哪。）
 */
SELECT sex, COUNT(*) FROM student WHERE age<11 GROUP BY sex HAVING COUNT(*)>2 LIMIT 1,5;

一般用于分页显示。比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

5-9、连接 UNION

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。

SELECT 字段名 FROM 表名 UNION SELECT 字段名 FROM 表名;
// 但有时候会过长，不便于阅读，因此用换行来写，注意分号
SELECT 字段名 FROM 表名
UNION
SELECT 字段名 FROM 表名

UNION会将重复的字段删除，只显示一个。

UNION ALL将全部显示。

5-10、连接 JOIN … ON

用来连接指定的列。ON后跟两表相同部分。

// 1、内连接（等值连接）[INNER] JOIN ... ON
// 获取两个表中字段匹配关系的记录
SELECT a.id, a.age, b.height FROM student a JOIN student1 b ON a.id = b.id; 
// 因为一般表名较长不方便同一行命令多次书写，因此用别名代替。

// 2、左连接 LEFT JOIN ... ON
// 获取左表所有记录，即使右表没有对应匹配的记录
SELECT a.id, a.age, b.height FROM student a LEFT JOIN student1 b ON a.id = b.id;

// 3、右连接（同理左连接） RIGHT JOIN ... ON
SELECT a.id, a.age, b.height FROM student a RIGHT JOIN student1 b ON a.id = b.id;

三、数据库约束

对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

约束种类	说明
PRIMARY KEY	主键
UNIQUE	唯一
NOT NULL	非空
DEFAULT	默认
FOREIGN KEY	外键

1、PRIMARY KEY 主键

用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。
一般将id设置为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

// 1、创建hero表，包含id,name,age字段，并设置id为主键。
CREATE TABLE hero(
	id INT PRIMARY KEY,
	name VARCHAR(20),
	age INT
);

// 2、删除主键
ALTER TABLE hero DROP PRIMARY KEY;

// 3、主键自增 AUTO_INCREMENT 默认情况下，从1开始自增+1
// 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值。（字段类型必须为整数类型）
// 创建hero表，包含id,name,age字段，并设置id为主键，且主键自增。
CREATE TABLE hero(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	age INT
);
// 修改自增起始值
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

在上面记录的DELETE和TRUNCATE的区别中，还有一个关于自增的区别：
①DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。
②TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1

2、UNIQUE 唯一

在该表中的字段的值不能重复。
字段名 字段类型 UNIQUE

3、NOT NULL 非空

这个字段必须设置值，不能是NULL。
字段名 字段类型 NOT NULL

4、DEFAULT 默认

给字段设定一个默认值
字段名 字段类型 DEFAULT 默认值

5、FOREIGN KEY 外键

四、MySQL处理

1、NULL值处理

当使用SQL语句来读取表中数据时，可能会去判断数据是否为NULL。

运算符	意义
IS NULL	当列的值是 NULL,此运算符返回 true。
IS NOT NULL	当列的值不为 NULL, 运算符返回 true。
<=>	比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

注意：当student.id = NULL 或者是student.id != NULL时，该语句是不起作用的。

2、正则表达式 REGEXP

与之前模糊查询LIKE所使用到的通配符类似。

模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
[^…]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]’ 可以匹配 “plain” 中的’p’。
p1 l p2 l p3	匹配 p1 或 p2 或 p3。例如，‘z l food’ 能匹配 “z” 或 “food”。’(z l f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 “z” 以及 “zoo”。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
-	表示一个范围。例如，REGEXP ‘[a-e]’ 匹配 a、b、c、d 和 e。
?	匹配 0 次或 1 次。例如，'abc?‘匹配’ab’和’abc’
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

// 1、查找name字段中以'st'为开头的所有数据：
SELECT name FROM student WHERE name REGEXP '^st';
// 2、查找name字段中以'ok'为结尾的所有数据：
SELECT name FROM student WHERE name REGEXP 'ok$';
// 3、查找name字段中包含'mar'字符串的所有数据：
SELECT name FROM student WHERE name REGEXP 'mar';
// 4、查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
SELECT name FROM student WHERE name REGEXP '^[aeiou]|ok$';

注意：
MySQL 的正则表达式匹配不区分大小写（即大写和小写都匹配）。为区分大小写，可以使用 BINARY 关键字，例如：

WHERE name REGEXP BINARY '^St'。

3、排名

MySQL中没有Rank排名函数，当我们需要查询排名时，只能使用MySQL数据库中的基本查询语句来查询普通排名。

CREATE TABLE allStudent (
  id INT NOT NULL AUTO_INCREMENT,
  className VARCHAR(20) NOT NULL,
  name VARCHAR(20) NOT NULL,
  Chinese INT NOT NULL,
  Math INT NOT NULL,
  English INT NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO allStudent VALUES
	(1, '一班', '杜兰特', 88, 70, 99),
	(NULL, '一班', '哈登', 80, 79, 85),
	(NULL, '一班', '欧文', 98, 61, 70),
	(NULL, '一班', '哈里斯', 55, 89, 70),
	(NULL, '一班', '格里芬', 65, 70, 49),
	(NULL, '二班', '卡哇伊', 79, 82, 66),
	(NULL, '二班', '乔治', 86, 87, 60),
	(NULL, '二班', '雷击', 59, 75, 58),
	(NULL, '二班', '考辛斯', 55, 42, 46),
	(NULL, '三班', '东契奇', 91, 79, 80),
	(NULL, '三班', '波神', 67, 75, 54),
	(NULL, '三班', '哈达威', 65, 48, 76);

排行链接