MySQL笔记

本文主要是介绍MySQL笔记，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、数据库是什么

（一）概念

DB（database）：存储数据的“仓库”，保存了一系列有组织的数据；
DBMS（Database Management System）：数据库管理系统，数据库是通过DBMS创建和操作的容器；
SQL（Structure Query Language）：结构化查询语言，专门用来与数据库通信的语言。

（二）MySQL语法规范

不区分大小写，建议关键字大写；
关键字不能被缩写也不能分行；
字符串型和日期时间类型使用单引号（’ '）；
别名，尽量使用双引号（" "）；
每条命令最好用分号；
注释：
- 单行注释：#注释文字
- 单行注释：-- 注释文字（注意中间有空格）
- 多行注释：/* 注释文字 */

（三）SQL分类

DDL（Data Definition Language）：数据定义语言。定义了不同的数据段、数据库、表、列、索引等数据库对象。
- 主要语句关键字有CREATE，DROP，ALTER等。
DML（Data Manipulation Language）：数据操作语言。用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 主要语句关键字有INSERT，DELETE，UPDATE，SELECT等。
DCL（Data Control Language）：数据控制语言。用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全权限。
- 主要语句关键字有GRANT，REVOKE，COMMIT，ROLLBACK，SAVEPOINT等。

二、数据的查询

（一）基本的SELECT语句

基本使用

SELECT id,name,email FROM employees;

# *号代表全部字段
SELECT * FROM employees;

列的别名

# AS : Alias的缩写
# 可以使用一对双引号给字段起别名
# 当使用双引号时，调用别名时也需要加上双引号
SELECT id employee_id,name AS employee_name,email "employee_email" FROM employees;

去除重复行

# 去除表中id重复的数据
# 通常只针对一个字段进行操作
SELECT DISTINCT id FROM employees;

空值问题

# 空值表示没有赋值，null
# 空值参与运算：结果也为null
SELECT id,commission_pct,salary,salary * (1 + commission_pct) AS "salary_sum" FROM employees;

# 应该写成
SELECT id,commission_pct,salary,salary * (1 + IFNULL(commission_pct,0)) AS "salary_sum" FROM employees;

显示表结构

DESC employees;
DESCRIBE employees;

（二）过滤数据

不能在过滤条件中使用别名

where

# 查询id=100的员工
SELECT id,name,email FROM employees WHERE id = 100;

between … and …

# 查询id在 [0,100] 的员工
SELECT id,name,email FROM employees WHERE id BETWEEN 0 AND 100;

in(set)

# 查询id是10，20，30的员工
SELECT id,name,email FROM employees WHERE id IN (10,20,30);

like

# 查询姓名中包含字符'a'的员工
# '%'表示0个，1个或多个字符
SELECT id,name,email FROM employees WHERE name LIKE '%a%';

# 查询姓名中包含字符'a'和'e'的员工
SELECT id,name,email FROM employees WHERE name LIKE '%a%' AND name LIKE '%e%';

# 查询姓名中第二个字符为'a'的员工
# '_'表示一个字符
SELECT id,name,email FROM employees WHERE name LIKE '_a%';

# 查询姓名中第二个字符为'_'，第三个字符为'a'的员工
# '\'表示转义字符
SELECT id,name,email FROM employees WHERE name LIKE '_\_a%';

is null

# 查询id为null的员工
SELECT id,name,email FROM employees WHERE id IS NULL;

# 查询id为不为null的员工
SELECT id,name,email FROM employees WHERE id IS NOT NULL;

（三）排序操作

升序降序

# 按照员工的工资从高到低排序
# DESC-降序，ASC-升序
SELECT id,salary FROM employees ORDER BY salary DESC;

SELECT id,salary FROM employees WHERE salary > 1000 ORDER BY salary DESC;

二级排序

# 先按照工资降序排列，再按照姓名升序排列
SELECT id,name,salary FROM employees ORDER BY salary DESC,name ASC;

（四）分页操作

# 每页显示20条记录，先显示第1页
SELECT id,name FROM employees LIMIT 0,20;
# 显示第2页
SELECT id,name FROM employees LIMIT 20,20;
# (pageNo - 1) * pageSize,pageSize

# 查询工资最高的10名员工
SELECT id,salary FROM employees ORDER BY salary DESC LIMIT 0,20;

（五）多表查询

分类：

等值连接 / 非等值连接；
自连接 / 非自连接；
内连接 / 外连接
- 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另外一个表不匹配的行（只查得出匹配的数据）
- 外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行，称为左（右）外连接。没有匹配的行时，返回null。

# 笛卡尔积错误
# 每一个员工都和每一个部门匹配
SELECT id,name,department_name FROM employees,departments;

# 正确写法
# 如果查询的字段在多个表中出现，则一定要指明来自于哪个表
# 可以使用表的别名
SELECT e.id,e.name,d.department_name 
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

# 不等值连接
# 根据员工的工资划分等级
SELECT employee_id,salary,grade_level
FROM employee e,job_grade j
WHERE e.`salary` BETWEEN j.`lowest_level` AND j.`highest_level`;

# 自连接
# 查询员工的id，name和其管理者的id，name
SELECT emp.id,emp.name,mgr.id,mgr.name
FROM employees emp,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`

# 外连接
# 员工表中有一个员工没有部门，查询出来的数据少一条，这就是内连接
# 使用外连接，需要使用sql-99的相关结构

# 左外连接
SELECT e.id,e.name,d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右外连接
# RIGHT OUTER JOIN

# 满外连接,但是MySQL不支持
# FULL JOIN
# 左外连接去重 + 右外连接 使用UNION ALL（UNION会去重，效率低）
SELECT e.id,e.name,d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT e.id,e.name,d.department_name 
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

三、单行函数

函数：

单行函数：
- 操作数据对象；
- 接收参数返回一个结果；
- 只对一行进行变换；
- 每行返回一个结果；
- 可以嵌套；
- 参数可以是一列或者一个值。
多行函数：

（一）字符串函数

//连接str1，str2...为一个字符串
CONCAT(str1,str2...)

//同CONCAT函数，但是每个字符之间要加上s
CONCAT_WS(s,str1,str2...)

//字符长度
CHAR_LENGTH(s)
//字符长度，和字符集有关（utf-8中，每个汉字占三个字节）
LENGTH(s)

//将字符串str从第index个开始，len长度换成instr
//sql中索引从1开始
INSERT(str,index,len,instr)
  
//转换大小写
UPPER(s)
LOWER(s)

//返回字符串最左（右）边n个字符
LEFT(s,n)
RIGHT(s,n)
  
//左边补齐，用字符串pad填充，直到长度为len
LPAD(str,len,pad)
//右边补齐
RPAD(str,len,pad)
  
//去空格
LTRIM(s)
RTRIM(s)
TRIM(s)
  
//去字符
TRIM(s1 FROM s)//开头和结尾
TRIM(LEADING s1 FROM s)//开头
TRIM(TRAILING s1 FROM s)//结尾
  
//返回str重复n次
REPEAT(str,n)
  
//用字符串b替换字符串str中出现的字符串a
PEPLACE(str,a,b)
  
//比较大小
STRCMP(s1,s2)
  
//返回从字符串s的index位置长度为len的字符
SUBSTRING(s,index,len)

（二）数值函数

//绝对值
ABS()
  
//大于x的最小整数
CEIL(x)
//小于x的最大整数
FLOOR(x)
  
//返回x/y模，符号和被模数一致
MOD(x,y)
  
//随机值
RAND()
  
//四舍五入，返回参数x的四舍五入的y位小数的值
ROUND(x,y)
  
//返回数字x截断为y位小数的结果
TRUNCATE(x,y)
  
//平方根
SQRT(x)
  
//x的y次方
POW(x,y)

（三）日期函数

//返回当前日期
CURDATE()
  
//返回当前时间
CURTIME()
  
//返回当前日期时间
NOW()
  
//返回具体的时间值
YEAR(date)
MONTH(date)
DAY(date)
HOUR(time)
MINUTE(time)
SECOND(time)
  
//一年中的第几周
WEEKOFYEAR(date)
//第几周，周日是1
DAYOFWEEK(date)
//第几周，周日是0
WEEKDAY(date)
//返回星期名：MONDAY，TUESDAY...
DAYNAME(date)
  
//格式化
DATE_FORMAT(datetime,fmt)
//解析
STR_TO_DATE(str,fmt)

SELECT DATE_FORMAT(NOW(),'%Y/%m/%d %H:%i:%s') FROM DUAL;

SELECT STR_TO_DATE('2021/03/03 09:34:34','%Y/%m/%d %H:%i:%s') FROM DUAL;

（四）流程控制

SELECT id,name,salary,
IF(salary > 1000,'高工资','低工资') AS salary_details,
IF(commission_pct IS NOT NULL,commission_pct,0) AS commission_details
FROM employees;

ifnull

SELECT id,name,salary,
IF(salary > 1000,'高工资','低工资') AS salary_details,
IFNULL(commission_pct,0) AS commission_details
FROM employees;

case when … then … when … then … (else) … end

SELECT id,name,salary,CASE WHEN salary > 10000 THEN '第一等级'
													 WHEN salary > 5000 THEN '第二等级'
                           WHEN salary > 1000 THEN '第三等级'
                           ELSE '底层' END AS details
FROM employees;

case … when … then … when … then … else … end

SELECT id,name,salary,CASE name WHEN 'tom' THEN '帅哥'
																WHEN 'jerry' THEN '还行'
																END AS details
FROM employees;

（五）其他函数

//返回当前数据库名
database()
  
//返回数据库版本
version()
  
//返回当前登陆的用户名
user()
  
//返回字符串str的加密版本，41位长的字符串
password(str)
  
//返回字符串str的md5值
md5(str)

四、分组函数

avg() / sum()

# 只适用于数值类型的字段
SELECT AVG(salary),SUM(salary) FROM employees;

min() / max()

# 适用于数值类型，字符串类型，日期时间的字段
SELECT MIN(salary),MAX(salary),MIN(name),MAX(hire_date) FROM employees;

count()

# 适用于数值类型，字符串类型，日期时间的字段
# 计算个数时，null值不考虑在内，比如commission_pct字段
SELECT COUNT(salary),COUNT(name),COUNT(commission_pct) FROM employees;

# 可以使用COUNT(1),COUNT(2),COUNT(*)
# 表示遍历时取的值是1或2或*，只要非空就加1，直到遍历完整张表

avg = sum / count

avg 和 sum 在运算的时候，null值都不参与运算

group by

# 查询公司的平均工资
SELECT AVG(salary) FROM employees;

# 查询各部门的平均工资
SELECT department_id,AVG(salary) 
FROM employees
GROUP BY department_id;

#查询各部门的各工种的平均工资
SELECT department_id,job_id,AVG(salary) 
FROM employees
GROUP BY department_id,job_id;

having

# 如果过滤条件出现了组函数，则需要使用having替换where实现过滤
# 如果过滤条件中没有组函数，推荐放在where中，因为效率高
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 10000

五、sql语句的执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

六、子查询

（一）单行子查询

# 问题：谁的工资比Allen高
# 自连接
SELECT e1.name,e1.salary
FROM employees e1 JOIN employees e2
ON e1.`salary` > e2.`salary`
WHERE e2.name = 'Allen';

# 子查询
SELECT name,salary
FROM employees
WHERE salary >(
  						SELECT salary
  						FROM employees
  						WHERE name = 'Allen'
							);

# 练习：返回job_id与140号员工相同，salary比141号员工多的员工的name，job_id，salary
SELECT name，job_id，salary
FROM employees
WHERE job_id = (
  						SELECT job_id
  						FROM employees
  						WHERE id = 140
							);
AND salary > (
  						SELECT salary
  						FROM employees
  						WHERE id = 141
							);

（二）多行子查询

in：等于列表中的任意一个

SELECT id,name
FROM employees
WHERE salary in (
  						SELECT MIN (salary)
  						FROM employees
  						GROUP BY department_id
							);

any：和子查询返回的某一个值比较

# 返回其他job_id中比job_id等于'IT_PROG'部门任一工资低的员工id，name，job_id以及salary
SELECT id,name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
  						SELECT salary
  						FROM employees
  						WHERE job_id = 'IT_PROG'
							);

all：和子查询返回的所有值比较

# 返回其他job_id中比job_id等于'IT_PROG'部门的所有工资都低的员工id，name，job_id以及salary
SELECT id,name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
  						SELECT salary
  						FROM employees
  						WHERE job_id = 'IT_PROG'
							);

（三）相关子查询

# 不相关子查询
# 查询员工中工资大于公司平均公司的员工的name，salary和department_id
SELECT name,salary,department_id
FROM employees
WHERE salary > (
  						SELECT AVG(salary)
  						FROM employees
							);
							
# 相关子查询
# 查询员工中工资大于本部门平均公司的员工的name，salary和department_id
SELECT name,salary,department_id
FROM employees e1
WHERE salary > (
  						SELECT AVG(salary)
  						FROM employees e2
              WHERE e2.department_id = e1.department_id
							);

# 查询员工的id，name，要求按照department_name从小到大排序
SELECT id,name
FROM employees e
ORDER BY (
  				SELECT department_name
  				FROM departments d
  				WHERE e.department_id = d.department_id
					) DESC;

（四）EXISTS

# 查询公司管理者的id,name,job_id,department_id
# 方式一：
SELECT id,name,job_id,department_id
FROM employees
WHERE id IN (
  						SELECT DISTINCT manager_id
  						FROM employees
						);
						
# 方式二：
SELECT id,name,job_id,department_id
FROM employees e1
WHERE EXISTS (
  						# 找到就返回true
  						SELECT '*'
  						FROM employees e2
  						WHERE e1.id = e2.manager_id
						);

七、创建和管理表

数据库对象：表、视图、函数、触发器、存储过程等

# 创建库
CREATE DATABASE mydatabase;
# 使用库
USE mydatabase;
# 删除库
DROP DATABASE mydatabase;

（一）创建表

# 方式一：
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  email VARCHAR(20),
  salary DOUBLE(10,2),
  birthday DATE
);

# 方式二：基于现有的表创建新表
CREATE TABLE mytable2
AS 
SELECT id,name,salary
FROM employees;

（二）修改表

# 增加一个字段
ALTER TABLE mytable
ADD age INT;

# 删除一个字段
ALTER TABLE mytable
DROP age;

# 修改字段
ALTER TABLE mytable
MODIFY name VARCHAR(10);

# 重命名字段
ALTER TABLE mytable
CHANGE birthday birth DATE;

（三）重命名表

RENAME TABLE mytable2 TO mytable3;

（四）删除表

DROP TABLE mytable3;

（五）清空表

# 清空表中的数据，但是表结构保留
# 会自动commit，一旦操作，就不可以回滚数据
TRUNCATE TABLE mytable;

八、数据的增删改

# 根据已有的表
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE
);

（一）添加数据

# 添加数据
INSERT INTO mytable(id,name,email,birthday)
VALUES (1,'tom','tom@qq.com','2000-02-03')

# 基于现有的表
INSERT INTO mytable(id,name)
SELECT id,name
FROM employees
WHERE department_id IN (10,20,30);

（二）删除数据

# 支持回滚
DELETE FROM mytable
WHERE id = 1;

（三）修改数据

UPDATE mytable
SET name = 'jerry',email = 'jerry@126.com'
WHERE id = 1;

九、约束constraint

（一）什么是约束

为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制
- 实体完整性：同一个表中，不能存在两条完全相同的数据；
- 域完整性：年龄范围0～120，性别男/女；
- 引用完整性：员工所在部门，在部门表中要能找到这个部门；
- 用户自定义完整性：用户名唯一，密码不能为空。

（二）非空约束：not null

# 创建表的时候添加约束
CREATE TABLE mytable(
  id INT NOT NULL,
  name VARCHAR(15) NOT NULL,
  email VARCHAR(20),
  birthday DATE
);

# 修改表的时候删除约束
ALTER TABLE mytable
MODIFY name VARCHAR(15) NULL；

（三）唯一性约束：unique

在创建唯一约束时，如果不给唯一约束起名，则默认与列名相同；
MySQL会给唯一约束的字段默认创建一个唯一索引；
删除唯一约束只能通过删除唯一索引的方式。

# 创建表的时候添加约束
CREATE TABLE mytable(
  # 列级约束
  id INT UNIQUE,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE,
  # 表级约束
  CONSTRAINT mytable_email_uni UNIQUE (email)
);

# 修改表的时候删除约束
# 删除索引
ALTER TABLE mytable
DROP INDEX mytable_email_uni;

ALTER TABLE mytable
DROP INDEX id;

（四）主键约束：primary key

主键约束，既满足唯一性，也满足非空性。一个表中只能声明一个主键约束

# 通常在创建表的时候添加约束
# 列级约束
CREATE TABLE mytable(
  id INT PRIMARY KEY AUTO_INCREMENT,# 自增长
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE
);

# 表级约束
CREATE TABLE mytable(
  id INT AUTO_INCREMENT,
  name VARCHAR(15),
  email VARCHAR(20),
  birthday DATE,
  CONSTRAINT mytable_id_pri PRIMARY KEY (id)
);

# 删除主键
# 如果设置了自动增长，则会删除失败
# 删除主键之后，字段还会保留not null的约束
ALTER TABLE mytable
DROP PRIMARY KEY;

# 添加主键
ALTER TABLE mytable
ADD CONSTRAINT mytable_id_pri PRIMARY KEY (id);

（五）外键约束：foreign key

外键约束：在表1的字段a声明有一个外键约束，与表2的字段b相关联，则字段a在操作数据时，其数据一定是字段b中出现的数据

要求：要想关联成功，必须要求字段b声明有主键约束或者唯一性约束。

CREATE TABLE dept(
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(10)
);

CREATE TABLE emp(
  emp_id INT,
  emp_name VARCHAR(15),
  dept_id INT,
  
  # 声明外键
  CONSTRAINT emp_dept_id_for FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
);

（六）检查约束：check

# 对MySQL失效
CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  salary DOUBLE(10,2) CHECK (salary > 3000) # 添加的工资要大于3000
);

（七）默认值约束：default

CREATE TABLE mytable(
  id INT,
  name VARCHAR(15),
  salary DOUBLE(10,2) DEFAULT 2500 # 提供默认值
);

这篇关于MySQL笔记的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！