MySQL笔记-编程学习网

短信预约信息系统项目管理师报名、考试、查分时间动态提醒

MySQL笔记

著名数据库（了解）
概念
组成（了解即可）
注意点
DDL操作
- 操作数据库
- 数据类型（列类型）
- 操作表
DML（Data Manipulation Language）
DCL（Data Control Language）
DQL（Data Query Language）
编码
备份与恢复
约束
- 主键约束
- 非空约束和唯一约束
- 外键
多表查询
- 合并结果集
- 连接查询（一次查询多张表）

著名数据库（了解）

公司	数据库软件
IBM	DB2
微软	SQL Server、Access^[1]
Oracle	Oracle、MySQL
Sybase	Sybase

概念

RDBMS（Ralational DataBase Management System）
= Manager + Database
Database = n tables
table = 表结构 + 表关系

组成（了解即可）

数据查询语言（DQL:Data Query Language）：其语句也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
事务处理语言（TPL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
数据控制语言（DCL）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
数据定义语言（DDL）：其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。
指针控制语言（CCL）：其语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单行的操作。

注意点

MySQL中不区分大小写，但建议关键字全大写，数据库、表名等全小写，以提高可读性
MySQL进行WHERE条件匹配、ORDER By排序时默认不区分大小写。若需区分大小写，可在字符串前增加BINARY
清屏命令：Linux上system clear；Windows上system cls (需要MySQL 8以上)

DDL操作

操作数据库

显示数据库：SHOW DATABASES;
选择数据库：USE 表名;
创建数据库：CREATE DATABASE [IF NOT EXISTS] 表名 [CHARSET=utf8];
删除数据库：DROP DATABASE [IF EXISTS] 表名;
修改数据库编码：ALTER DATABASE 表名 CHARACTER SET utf8;

数据类型（列类型）

必须为每一列指定数据类型

注意：MySQL中字符串只能加单引号

数据类型	说明
int	整型
double(5,2)	浮点型，5表示最大总位数（不包括小数点），2表示小数点后位数
decimal	浮点型，不损失精度，表单中钱用得较多
char(255)	固定长度字符，长度不足时补足，超出长度报错。最大支持255个字节
varchar(65535)	变长字符，长度不足时不补足，超长报错。最大支持65535 bytes。会额外使用1个字节存储长度信息
text	不在SQL标准中（标准里的是clob），分tinytext (2^8^-1，255B)、text (2^16^-1，65k)、mediumtext (2^24^-1，16M)、longtext (2^32^-1，4G)
blob	用于存储二进制数据，分tinyblob (2^8^-1)、blob (2^16^-1)、mediumblob (2^24^-1)、longblob (2^32^-1)
date	格式：YYYY-MM-DD
time	格式：hh:mm:ss
timestamp	date + time
...	...

操作表

显示表：SHOW TABLES;
显示表结构：DESC 表名;

创建表

CREATE TABLE [IF NOT EXISTS] 表名(
	列名 列类型,
	列名 列类型，
	...
	列名 列类型
);

删除表：DROP TABLE [IF EXISTS] 表名;
修改表（前缀：ALTER TABLE 表名）：
- 添加列
```
ALTER TABLE 表名 ADD(
	列名 列类型,
	列名 列类型，
	...
	列名 列类型
);
```
- 删除列：ALTER TABLE 表名 DROP 列名;
- 修改列类型：ALTER TABLE 表名 MODIFY 列名列类型;
- 修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列类型;
- 修改表名称：ALTER TABLE 表名 RENAME TO 新表名;

DML（Data Manipulation Language）

插入一行record
- INSERT INTO 表名(列名1, 列名2, 列名3, ...) VALUES(值1, 值2, 值3, ...) →不要求包含所有列及列顺序
- INSERT INTO 表名 VALUES(值1, 值2, 值3, ...) → 要求包含所有列，且按默认顺序
修改数据
- UPDATE 表名 SET 列名=值 WHERE 条件 （如果没有where部分则会修改所有行）
- 条件为boolean值，运算符有 =、!=、<>、<、>、<=、>=、BETWEEN ... AND ...（包含开始和结束值）、IN(...)、IS NULL、NOT（MySQL中NOT只限用于IN、BETWEEN、EXISTS取反）、AND、OR （注意： 判断是否为NULL不能用= NULL，而必须用IS NULL；AND优先级高于OR）
删除数据
- DELETE FROM 表名 WHERE 条件（如果没有WHERE则删除表中所有数据！）

DCL（Data Control Language）

一个项目创建一个用户，只对应一个数据库，这个用户只能对这个数据库有权限，其他数据库无法操作

创建用户

CREATE USER 用户名@IP（或localhost) IDENTIFIED BY "密码";
-- 用户只能在指定的IP上登录
CREATE USER 用户名@"%" IDENTIFIED BY "密码";
-- 用户可以在任意IP上登录

给用户授权

GRANT 权限1[, 权限2, ...] ON 数据库.* TO 用户名@地址
-- 例： GRANT SELECT, CREATE, ALTER, DROP, INSERT, UPDATE, DELETE ON mydb1.* TO user1@localhost;
GRANT ALL ON 数据库.* TO 用户名@IP地址

撤销授权

REVOKE 权限1[, 权限2, ...] ON 数据库.* FROM 用户名@IP地址;

查看权限 SHOW GRANTS ON 用户名@IP地址;
删除用户 DROP USER 用户名@IP地址;

DQL（Data Query Language）

查询不会修改数据库表记录

字段（列）控制

查询所有列 SELECT * FROM 表名;
查询指定列 SELECT 列1[, 列2, ...] FROM 表名;
完全重复的记录只显示一次 SELECT DISTINCT * | 列1[, 列2, ...] FROM 表名;

列运算

数值类型的列可以做加、减、乘、除运算，如SELECT *，salary*1.5 FROM mydb1;，非数值类型按0处理，NULL值与任何值运算都得到NULL
字符串连接用CONCAT(str1, str2, ...)函数(其他多数DBMS采用+或||连接字符串)
NULL与任何值运算、连接字符串都得NULL，处理NULL值可用IFNULL(列名, 默认值)替换NULL值为默认值
LTRIM(字符串) 可以删除字符串左侧空格，RTRIM为右侧，TRIM为两侧

可用不加FROM的语句SELECT 列运算式 进行测试运算，如

SELECT 2*3;
SELECT Trim(" abc ");
SELECT Now(); #返回当前日期和时间

其他函数^[2]

文本处理函数

函数	说明
Left(str, len) / Right(str, len)	返回字符串左侧 / 右侧len个字符
Length(str)	返回字符串长度
LTrim(str) / RTrim(str) / Trim(str)	删除字符串左 / 右 / 两侧字符串
Soundex(str)	返回str的soundex值（发音）
Lower(str) / Upper(str)	转换字符串为小写 / 大写
Substring(str, n, len)	返回第n个字符（包含）开始的len个字符的字串，n为负值时表示倒数第n个
Locate(substr, str)	返回字串第一次出现在str中的位置

时间处理函数

函数	说明
CurDate() / CurTime() / Now()	返回当前日期 / 时间 / 日期时间
Date(datetime) / Time(datetime)	提取日期时间的日期部分 / 时间部分
Year(dt) / Month(dt) / Day(dt)	返回日期时间的年 / 月 / 日
Hour(dt) / Minute(dt) / Second(dt)	返回日期时间的时 / 分 / 秒
DayOfWeek(dt)	返回星期几（星期日为1，星期六为7）
DateDiff(end_dt, start_dt)	返回两日期时间差
AddDate(dt,INTERVAL expr type)	增加一个expr type日期和时间
AddTime(dt, expr)	增加一个expr(hh:mm:ss)时间

数值处理函数

函数	说明
Sin(x) / Cos(x) / Tan(x)	正弦值 / 余弦值 / 正切值
Abs(x)	绝对值
Pi()	圆周率
Mod()	模运算，相当于%
Rand()	返回0~1之间的随机数
Sqrt(x)	平方根
Exp(x)	求自然常数e的x次方

更多函数及用法可参阅：MySQL常用函数大全

起别名：(AS 可以省略)，也可用于给表起别名

SELECT 列名 (AS) 别名 FROM 表名; # as关键字可以省略
-- 如 SELECT salary*1.5 as 工资 FROM employees;

条件控制

条件查询（添加WHERE条件）
模糊查询：通过LIKE关键字和_%通配符实现(_匹配一个字符；%匹配0到n个字符，但不匹配NULL)
- 模糊搜索的时间开销相比一般条件查询更大
- 不要过度使用通配符，若能用其他操作符实现，应使用其他操作符
- 使用通配符时，除非绝对有必要，否则不要将通配符放置在搜索模式的开始处，这样搜索起来最慢
```
SELECT * FROM employees WHERE name LIKE "张%"; #匹配如张、张三、张某某
SELECT * FROM employees WHERE name LIKE "张__";  # 匹配如张某某，但不匹配张三
```

正则表达式：通过 REGEXP 匹配字符串 实现

与LIKE的区别：LIKE是整行匹配；REGEXP只要行内字串满足匹配字符串即可，若匹配字符串首尾同时添加 ^ 和 $ 则变成整行匹配
与多数其他软件不同，匹配特殊字符，如.[]()|等，需要添加两个反斜杠\，如\.，\n，\（匹配本身）、\1（反向引用）等（一个自己解释一个，正则表达式库解释另一个）

字符集

类	说明
`[:alpha:]`	任意字符（同`[a-zA-Z]`）
`[:digit:]`	任意数字（同`[0-9]`）
`[:alnum:]`	任意字符和数字（同`[a-zA-Z0-9]`）
`[:xdigit:]`	任意十六进制数字（同`[a-fA-F0-9]`）
`[:lower:]`	任意小写字母（同`[a-z]`）
`[:upper:]`	任意大写字母（同`[A-Z]`）
`[:blank:]`	空格和制表符（同`[ \t]`)
`[:space:]`	包含空格在内的任意空白字符（同`[\f\n\r\t\v]`）
`[:print:]`	任意可打印字符
`[:graph:]`	除空格外的任意可打印字符
`[:cntrl:]`	ASCII控制字符（ASCII 0-31和127）
`[:punct:]`	即不在`[:alnum:]`，也不在`[:cntrl:]`中的字符

正则表达式内容较多，用法可参见正则表达式30分钟入门教程
MySQL中可用SELECT "待匹配字符串" REGEXP "匹配样式字符串" 简单测试（结果1为匹配，0为不匹配）

排序：ORDER BY 列名1 [ASC/DESC], ... （ASC升序，DESC降序，省略时默认ASC）
- 先按第一个条件排序，第一个条件相同时，按第二个条件，依此类推。
聚合查询：利用聚合函数纵向查询某一列的非空行数COUNT、SUM、MAX、MIN、AVG
注意：
- COUNT(列名) 记录该列非NULL的行数，若为COUNT(*) 则为包含NULL的所有行数；
- 聚合查询一般忽略NULL值
- 函数参数可为 DISTINCT 列名，此时重复值只计算一次
```
SELECT COUNT(salary) 计数,SUM(DISTINCT salary) 总和, MAX(salary) 最高, MIN(salary) 最低, AVG(salary) 平均 
FROM employees 
WHERE department = "财务部";
```
分组查询 GROUP BY -- 只用于查询组信息
- 用法：SELECT 列名1[,列名2，...], 聚合函数 FROM 表名 GROUP BY 列名1[,列名2,...]`
- 在GROUP BY前通过WHERE 列条件过滤行，之后则使用HAVING 关键字过滤组信息（除位置外，HAVING用法与WHERE基本相同）
- GROUP BY 列 可接WITH ROLLUP，增加一行汇总行
- 结果不一定有序，最好结合ORDER BY
```
-- 列出各个岗位里工资大于15000的人数，且满足要求的岗位人数必须大于1人
SELECT job, COUNT(job) FROM employees 
WHERE salary > 15000 
GROUP BY job 
HAVING count(job) > 1;
```
MySQL方言之LIMIT（仅MySQL支持）
- LIMIT [row - 1,] n 显示从第row行（下标0为第一行）开始的n行数据

编码

显示编码方式 SHOW VARIABLES LIKE "char%";

设置编码方式：

若在交互窗口修改，则仅当次有效，退出mysql即恢复原样；若在my.ini中修改，则为永久生效


SET character_set_client=utf8; #设置客户端编码方式
SET character_set_results=utf8; #设置mysql返回的数据编码格式


default-character-set=utf8 # 修改默认字符集，包括client、results、server
character-set-server=utf8

备份与恢复

备份
- 在cmd中使用mysqldump -u用户 -p[密码] 数据库名> 保存路径（含文件名）
  如：mysqldump -uroot -p123 mydb1 > C:/a.sql
- 只是保持数据库内容，不保存数据库本身。后期恢复时需要先CREATE DATABASE
恢复
- 若数据库不存在，需要先创建
- 方式1：cmd输入mysql -u用户 -p 数据库名 < 导入sql文件路径（含文件名）
  如：mysql -uroot -p123 mydb1 < C:/a.sql
- 方式2：mysql窗口使用source 导入的sql文件路径
  如：source C:/a.sql

约束

主键约束

含义：唯一标识一行记录的列元素（不建议用自然主键，如身份证号，避免后期出错）
特点：
- 非空
- 唯一
- 可以被引用（外键）

使用方式

# 创建表时指定的两种方式：
CREATE TABLE emp(
	empno INT PRIMARY KEY,
    ename VARCHAR(50)
)

CREATE TABLE emp(
	empno INT,
    ename VARCHAR(50)，
    PRIMARY KEY(empno)
)

# 通过修改表指定/删除
ALTER TABLE emp ADD PRIMARY KEY(empno);
ALTER TABLE emp DROP PRIMARY KEY;

自增长
- 自动在前一个记录的基础上，该列数值加1（要求该列必须为INT型）
- 分布式系统中不建议自增长，可改用UUID
```
CREATE TABLE emp(
	empno INT PRIMARY KEY AUTO_INCREMENT, 
    ename VARCHAR(50)
)
```

非空约束和唯一约束

非空约束NOT NULL，唯一约束UNIQUE

CREATE TABLE emp(
	empno INT PRIMARY KEY, 
    ename VARCHAR(50) NOT NULL UNIQUE
)

外键

关系模型：
- 一对一（较少见）
- 一对多
- 多对多
外键特点：
- 外键必须引用本表或另一张表的主键（多方引用一方）
- 外键可以为空、可以重复，但所引用的值必须存在
- 一张表中可以存在多个外键

建立方式

一对多

-- 创建时定义：
CREATE TABLE emp(
	empno INT PRIMARY KEY, # 职员编号
    ename VARCHAR(50), # 职员姓名
    dept_no INT, # 所属部门
    CONSTRAINT 自定义约束名 FOREIGN KEY(dept_no) REFERENCES dept(deptno); 
    # 引用dept表的deptno列
)
-- 修改已有列：
ALTER TABLE emp 
ADD CONSTRAINT 自定义约束名 FOREIGN KEY(dept_no) REFERENCES dept(deptno);

一对一

CREATE TABLE husband(
	hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(50)
)
CREATE TABLE wife(
	wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hus FOREIGN KEY(wid) REFERENCES husband(hid)
)
# wid对应hid。由于wid为主键，故唯一非空。wid又为husband表外键（引用hid），故引用值必须存在。由此可实现一对一关系

多对多
需要借助第三张表作为关联表，记录两张表主键的关系

CREATE TABLE teacher(
	tid INT PRIMARY KEY AUTO_INCREMENT, # 老师ID
    name VARCHAR(50)
)
CREATE TABLE student(
	sid INT PRIMARY KEY AUTO_INCREMENT,　# 学生ID
    name VARCHAR(50)
)
CREATE TABLE tie(  # 关联表
    tid INT,
	sid INT,
    CONSTRAINT fk_tea FOREIGN KEY(tid) REFERENCES teacher(tid)
    CONSTRAINT fk_stu FOREIGN KEY(sid) REFERENCES student(sid)
)

多表查询

合并结果集

用于将两个结果集拼接在一起显示
要求结果集列数&列类型相同

语法: （通过UNION [ALL] 拼接多个SELECT ... FROM ... 语句，不加ALL会对紧邻的两个结果集的行进行去重）

CREATE TABLE ab(a INT, b VARCHAR(50));
CREATE TABLE cd(c INT, d VARCHAR(50));
...  # 此处添加数据
SELECT * FROM ab UNION ALL SELECT * FROM cd;
SELECT a FROM ab UNION SELECT c FROM cd UNION ALL SELECT a FROM ab;

连接查询（一次查询多张表）

多表查询注意使用表名对列进行限定

内连接（从多张表的笛卡尔积^[3]中筛选出满足条件的表）
- 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx = 别名2.xx
- 标准（推荐）：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx = 别名2.xx
  - 大于两张表连接时，每连接一张表，后面就用ON条件限定一次。若有WHERE条件，则放在所有连接之后
- 自然（可读性降低）：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2 （由系统自动根据两表相同的列名连接）
```
# 查找满足条件的所有员工的名称、工资、及所在部门信息
# 方言
SELECT ename, salary, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
# 标准
SELECT ename, salary, dname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
# 自然
SELECT ename, salary, dname
FROM emp e NATURAL JOIN dept d
```
外连接
- 左外连接
  - 左表无论是否满足条件都会查询出来（至少显示1次），而右表只有满足条件才能出来。左表中不满足条件的记录，右表部分均为NULL
  - SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx = 别名2.xx
- 右外连接：SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx = 别名2.xx
- 全外连接
  - 左右表记录无论是否满足条件都至少显示一次，不满足条件的，另一部分补NULL
  - MySQL中不支持，但可以通过UNION连接左外连接和右外连接得到
- 左外自然连接和右外自然连接（不需要写ON条件）：NATURAL LEFT/RIGHT OUTER JOIN
- 大于两张表连接时，每连接一张表，后面就用ON条件限定一次。若有WHERE条件，则放在所有连接之后
```
# 查找所有员工的名称、工资、及所在部门信息（无论员工是否满足条件）
SELECT ename, salary, dname
FROM emp e LEFT OUTER JOIN dept d #左外连接
ON e.deptno = d.deptno

# 显示所有部门的名称，及部门职工名称、工资（无论部门是否满足条件）
SELECT ename, salary, dname
FROM emp e RIGHT OUTER JOIN dept d #右外连接
ON e.deptno = d.deptno

# 全外连接
SELECT ename, salary, dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
UNION  #不加ALL以去重
SELECT ename, salary, dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
```

子查询（查询中嵌入查询，需要包裹在圆括号中）

SELECT语句出现在WHERE之后，作为条件存在

单行单列：作为值存在，可用 =、!=、<>、<、>、<=、>=比较

SELECT * FROM 表1 别名1 
WHERE 列1 [=、!=、>、<、>=、<=] (SELECT 列 FROM 表2 别名2 WHERE 条件);
 -- 例：查询公司中工资最高的员工信息
SELECT * FROM emp 
WHERE sal = (SELECT MAX(sal) FROM emp);

多行单列：作为多个值存在，可在括号前加ALL、ANY、IN

SELECT * FROM 表1 别名1 
WHERE 列1 [ALL、ANY、IN] (SELECT 列 FROM 表2 别名2 WHERE 条件);

单行多列：作为一个对象存在

SELECT * FROM 表1 别名1 
WHERE (列1,列2) IN (SELECT 列1,列2 FROM 表2 别名2 WHERE 条件);

SELECT语句出现在FROM之后，作为表存在（多行多列）
```
SELECT * FROM 表1 别名1, (SELECT ...) 别名2 WHERE 条件
```

（未完待续...)

Access为桌面型数据库，主要用于局域网；其余的为C/S型（客户端/服务器型） ↩︎
函数在不同DBMS间可移植性较差，编写sql脚本时最好标注清楚 ↩︎
笛尔积：{a, b, c} x {1, 2} = {a1, a2, b1, b2, c1, c2} ↩︎

文章详情

MySQL笔记

著名数据库（了解）

概念

组成（了解即可）

注意点

DDL操作

操作数据库

数据类型（列类型）

操作表

DML（Data Manipulation Language）

DCL（Data Control Language）

DQL（Data Query Language）

编码

备份与恢复

约束

主键约束

非空约束和唯一约束

外键

多表查询

合并结果集

连接查询（一次查询多张表）

软考中级精品资料免费领

相关文章

猜你喜欢

mysql笔记-

MySQL笔记

【MySQL】笔记（1）-

【MySQL】笔记（2）-

MySql笔记(二)

【MySQL】笔记（4）-

【MySQL】笔记（3）-

MySql笔记(一)

MySQL学习笔记

MySQL 学习笔记

mysql索引笔记

MySQL基础笔记

MySQL 学习笔记 （一）

MySQL学习笔记 初涉MySQL

MySQL笔记免费送

MySQL学习笔记（17）：MySQL Utilities

MySQL 学习笔记（五）

MySQL学习笔记-day01

MySQL学习笔记-day03

mysql分区表笔记

MySQL 学习笔记（一）

MySQL学习笔记初涉MySQL