首页 > 资讯 > 数据库 >MySQL笔记

532

分享到

MySQL笔记

MySQL笔记 2019-03-29 01:03:07 532人浏览猪猪侠

摘要

目录著名数据库（了解）概念组成（了解即可）注意点DDL操作操作数据库数据类型（列类型）操作表DML（Data Manipulation Language）DCL（Data Control Language）DQL（Data Que

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} ↩︎

您可能感兴趣的文档:

点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: MySQL笔记

本文链接: https://www.lsjlt.com/news/7234.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档

去做题

oracle怎么显示行数

2024-05-14

oracle怎么显示百分比

2024-05-14

sql怎么查看字段类型

2024-05-14

sql怎么查看视图表

2024-05-14

sql怎么查看创建的视图

2024-05-14

sql怎么查看表的索引

2024-05-14

sql怎么打开代码界面

2024-05-14

oracle怎么查看数据库有哪些表

2024-05-13

oracle怎么拼接字符串和数字

2024-05-13

oracle怎么导入dbf文件

2024-05-13

热门问答

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布