MySQL入门篇_mysql查询所有商品名称,商品价格

前言

MySQL在面试和工作中非常重要，故在此整理学习MySQL时的知识点，作为一个学习笔记。

文中内容出自尚硅谷，链接：尚硅谷MySQL数据库教程天花板，mysql安装到mysql高级。

非常感谢尚硅谷的视频教程！

目录

前言

一、SQL语句

二、运算符

1.比较运算符

2.安全等于运算符

3.非符号类型运算符

三、排序、分页

四、多表查询

1.笛卡尔积

2.内连接、外连接

3. union

4. SQL JOINS 

5. 99语法新特性

五、函数

1. 数值函数

2. 字符串函数

3. 时间函数

六、聚合函数

1. 聚合函数类型

2. count(*)，count(1)，count(column)区别

3. group by

4. having

5. sql执行原理

七、子查询

1. 什么是子查询

2.注意事项

3.子查询的分类

4. case中的子查询

5. 多行子查询

6. 自连接和子查询哪个好？

八、表的创建与管理

1. 数据存储的过程

2. 数据库、表、字段命名规范

3. 创建、管理数据库

4. 表

九、增删改

1. 插入

2. 更新

3. 删除

十、数据类型

1. MySQL中数据类型

2. char、varchar

3. float、double

4. 整数类型的区别

5. 浮点类型

6. 定点数类型

7. 日期时间类型

十一、约束

1. 什么是约束

2. 为什么需要约束

3. 约束的分类

4. 非空约束

5. 唯一约束

6. 主键约束

7. 自增约束

8. 外键约束

9. 默认约束

十二、视图

1. 什么是视图

2. 特点

3. 为什么要使用视图

4. 创建视图

5. 修改视图

6. 删除视图

7. 视图的优点

8. 视图的缺点

十三、储存过程、函数

1. 什么是存储过程？

2.存储过程的好处

3.与视图的区别

4. 创建存储过程

5. 调用存储过程

总结

---

一、SQL语句

1.基本sql语句

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件;

2.给列起别名

SELECT 字段1,字段2 "字段2的别名"
FROM 表名
WHERE 过滤条件;

3.去重

SELECT DISTINCT 字段1
FROM 表名;

4.空值参与运算

 books表：

• 查询书本打折后的价格

#IFNULL(字段, 1) 如果字段不为空，则使用字段原有值；如果字段为空，则用1替换
SELECT bookname, price * IFNULL(discount,1) "cost"
FROM books;

5.显示表结构

DESC 表名;

二、运算符

1.比较运算符

比较运算符用来对表达式左边的操作数和游标的操作数进行比较，比较结果为真返回1，比较结果为假返回0，其他情况返回NULL。

比较运算符经常被用来作为select查询语句的条件来使用，返回符合条件的结果记录。

SELECT 0 = 'a' # 整数与字符串比较，mysql会将字符串转化为数字进行比较
FROM dual;
 
 
SELECT 'a' = 'b' # 字符串之间比较，其比较的是每个字符串中字符的ASCII编码是否相等
FROM dual;

2.安全等于运算符

安全等于运算符(<=>)与等于运算符作用相似，唯一的区别就是安全等于可以用来对NULL进行判断。使用安全等于运算符时，两边的操作数都为NULL时，返回的结果为1；当一个操作数为NULL时，其返回值为0。

SELECT NULL = NULL, NULL <=> NULL, 1 = NULL, 1 <=> NULL;

3.非符号类型运算符

• 查询books表discount不为空的数据

SELECT bookName 
FROM books 
WHERE discount IS NOT NULL;

• 查询 book表中discount为空的数据

SELECT bookName 
FROM books 
WHERE discount IS NULL;

• 查询books表中价格在50-70之间的书名

SELECT bookName, price 
FROM books 
WHERE price 
BETWEEN 50 AND 70;

• 查询books表中书价为50，60的书本信息

SELECT bookName, price 
FROM books 
WHERE price IN (50,60);

• 查询books表中包含字符 'i' 的书名

SELECT bookName 
FROM books 
WHERE bookName 
LIKE '%i%';

• 查询books表中第三个字符是 'n' 的书名

SELECT bookName 
FROM books 
WHERE bookName
# _ 代表一个不确定的字符
LIKE '__n%';

三、排序、分页

使用limit的好处：约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果知道返回结果只有一条，就可以使用limit 1，告诉select语句只需要返回一条记录即可。好处是select不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

limit后第一个参数表示开始的下标，第二个参数表示个数

注意：limit必须放在整个select语句的最后。

• 排序分页的sql语句

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
ORDER BT 字段 ASC/DESC
LIMIT (pageNum - 1) * pageSize, pageSize;

• 查询书名按照价格降序排序

SELECT bookName, price 
FROM books 
ORDER BY price DESC;

• 每页显示10条记录，显示第二页

SELECT bookName, price 
FROM books 
LIMIT 10,10;

四、多表查询

• emp员工表

• dept部门表

• location位置表

1.笛卡尔积

1.1 什么是笛卡尔积

多表查询的结果数为多个表中数据条目数的乘积，笛卡尔积会把第一张表的每一个值与第二张表的每一个值进行匹配。

1.2 什么情况下会出现笛卡尔积的错误

1. 省略多个表的连接条件
2. 连接条件无效
3. 所有表中的所有行互相连接      

为避免笛卡尔积，可以在where加入有效的连接条件

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

2.内连接、外连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表或右表中不满足条件的行，这种连接也称为左外连接或右外连接。没有匹配的行时，结果表中对应的列为空。MySQL不支持sql92的外连接写法。

左外连接连接条件左表的表为主表，右边的表为从表；右外连接则相反。

外连接的分类：左外连接、右外连接、满外连接

• 左外连接

SELECT e.last_name, e.dept_id, d.dept_name
FROM emp e
LEFT OUTER JOIN dept d
ON (e.dept_id = d.dept_id);

• 右外连接

SELECT e.last_name, e.dept_id, d.dept_name
FROM emp e
RIGHT OUTER JOIN dept d
ON (e.dept_id = d.dept_id);

3. union

union和union all的区别：union会执行去重操作；union all不会执行去重操作。

4. SQL JOINS 

• 内连接 A∩B

SELECT emp_id,last_name,dept_name
FROM emp a JOIN dept b
ON a.`dept_id` = b.`dept_id`;

---

• 左外连接

SELECT emp_id,last_name,dept_name
FROM emp a LEFT JOIN dept b
ON a.`dept_id` = b.`dept_id`;

---

•  右外连接

SELECT emp_id, last_name, dept_name
FROM emp a RIGHT JOIN dept b
ON a.`dept_id` = b.`dept_id`;

 

---

•  A - A∩B

SELECT emp_id,last_name,dept_name
FROM emp a LEFT JOIN dept b
ON a.`dept_id` = b.`dept_id`
WHERE b.`dept_id` IS NULL;

---

• B - A∩B

SELECT emp_id,last_name,dept_name
FROM emp a RIGHT JOIN dept b
ON a.`dept_id` = b.`dept_id`
WHERE a.`dept_id` IS NULL;

---

• 满外连接

SELECT emp_id,last_name,dept_name
FROM emp a LEFT JOIN dept b
ON a.`dept_id` = b.`dept_id`
WHERE b.`dept_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT emp_id,last_name,dept_name
FROM emp a RIGHT JOIN dept b
ON a.`dept_id` = b.`dept_id`;

 

---

• (A - A∩B)∪(B - A∩B)

SELECT emp_id,last_name,dept_name
FROM emp a LEFT JOIN dept b
ON a.`dept_id` = b.`dept_id`
WHERE b.`dept_id` IS NULL
UNION ALL
SELECT emp_id,last_name,dept_name
FROM emp a RIGHT JOIN dept b
ON a.`dept_id` = b.`dept_id`
WHERE a.`dept_id` IS NULL

---

5. 99语法新特性

5.1 自然连接

sql99的自然连接可以理解为sql92中的等值连接，自然连接可以自动查询两张表中所有相同的字段，然后进行等值连接。

sql92：

SELECT emp_id,last_name,dept_name
FROM emp a JOIN dept b
ON a.`dept_id` = b.`dept_id`
AND a.`manager_id` = b.`manager_id`;

sql99:

SELECT emp_id,last_name,dept_name
FROM emp a NATURAL JOIN dept b;

5.2 using连接

sql99支持using指定数据表里的同名字段进行等值连接，但是只能配合join一起使用。USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。

SELECT emp_id,last_name,dept_name
FROM emp a JOIN dept b
USING (dept_id);

等同于:

SELECT emp_id,last_name,dept_name
FROM emp a ,dept b
WHERE a.dept_id = b.dept_id;

五、函数

1. 数值函数

• ABS(x)：返回x的绝对值
• SIGN(x): 返回x的符号，正数返回1，负数返回-1，0返回0
• LEAST(v1,v2,v3)：返回v1,v2,v3中的最小值
• GREATEST(v1,v2,v3)：防御列表中的最大值
• MOD(x,y)：返回x除以y后的余数
• RAND()：返回0~1的随机值
• SQRT(x)：返回x的平方根，x为负数时返回NULL
• CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数
• FLOOR(x) 返回小于或等于某个值的最大整数

2. 字符串函数

• REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
• UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母
• LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母
• LEFT(str,n) 返回字符串str最左边的n个字符
• RIGHT(str,n) 返回字符串str最右边的n个字符
• LTRIM(s) 去掉字符串s左侧的空格
• RTRIM(s) 去掉字符串s右侧的空格
• TRIM(s) 去掉字符串s开始与结尾的空格

3. 时间函数

• CURDATE() ，CURRENT_DATE()：返回当前日期，只包含年、 月、日
• CURTIME() ， CURRENT_TIME()： 返回当前时间，只包含时、 分、秒
• NOW() / CURRENT_TIMESTAMP() ：返回当前系统日期和时间

六、聚合函数

1. 聚合函数类型

• AVG(column)：求平均数
• SUM(column)：求和
• MAX(column)：求最大值
• MIN(column)：求最小值
• COUNT(column/1/*)：记录总数

2. count(*)，count(1)，count(column)区别

对于myisam来说没有区别，因为myisam内部有计数器在维护行数；而对于innodb来说，是用count(*),count(1)直接读行数，复杂度是O(n)，但优于count(column)；count(*)会统计值为 NULL 的行，而 count(column)不会统计此列为 NULL 值的行，所以不能用count(column)替换count(*)。

3. group by

mysql中可以使用group by子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

注意：

• select中出现的非组函数字段必须声明在group by中，而group by中声明的字段可以不出现在select中
• group by声明在from后面、where后面、order by前面、limit前面
• rollup和group by是互相排斥的

4. having

作用：过滤数据

例子1：查询各个部门最高工资高于10000的部门信息

SELECT dept_id, MAX(salary)
FROM emp
GROUP BY dept_id
HAVING MAX(salary)>10000;

如果使用了聚合函数，就必须使用having来替换where

例子2：查询部门id为10，20，30中最高工资大于10000的部门信息

#方式一
select dept_id,max(salary)
from emp
where dept_id in (10,20,30)
group by dept_id
having max(salary) > 10000;
 
#方式二
select dept_id,max(salary)
from emp
group by dept_id
having max(salary) > 10000 and dept_id in (10,20,30);

推荐方式一，方式一执行效率较高。

where与having的区别：

• where可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；having必须与group by配合使用，可以把分组计算的函数和分组字段作为筛选条件。having可以完成where完成不了的任务，在查询语句中，where在group by之前，所以无法对分组结果进行筛选。
• 在关联查询中，需要通过连接从关联表中获取需要的数据，where是先筛选后连接，用一个筛选后较小的数据集和关联表进行连接，占用的资源少，效率也更高；而having先连接后筛选，用未筛选的数据集进行关联，然后再对大的数据集进行帅选，占用资源多，效率也比较低。

5. sql执行原理

sql语句的完整结构

#sql92语法
select ... , ...
from table1, table2
where 过滤条件(不能包含聚合函数)
group by ...
having 过滤条件(可以包含聚合函数的)
order by ...
limit ...;
 
 
#sql99语法
select ... , ...
from table1 (left/right) join table2 on 连接条件
where 过滤条件(不能包含聚合函数)
group by ...
having 过滤条件(可以包含聚合函数的)
order by ...
limit ...;

select语句的执行顺序：

1. from
2. where
3. group by
4. having
5. select
6. distinct
7. order by
8. limit

知道了select语句的执行顺序以后，我们就可以搞清楚以下几个问题了

• 为什么包含聚合函数的过滤条件不能写在where里？

聚合函数的使用前提是必须有结果集，select语句第二步执行到where时还没有结果集。而执行到第三步group by的时候进行分组，分组之后便有了结果集（如果没有group by则执行到select才会有结果集），所以having就可以使用包含聚合函数的过滤条件。

• 为什么字段别名不能在where条件中使用，而能在order by中使用？

因为字段别名是在select中起的，而where执行时还不存在别名，自然就不能使用别名。

七、子查询

1. 什么是子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

SELECT last_name, job_id, salary
FROM emp
WHERE salary = (
        SELECT MIN(salary)
        FROM emp
    );

2.注意事项

• 子查询在主查询之前执行完成
• 子查询的结果被主查询使用
• 子查询要包含在括号内
• 单行操作符对应单行子查询，多行操作符对应多行子查询

3.子查询的分类

• 根据子查询返回结果的条目数，可分为单行子查询和多行子查询
• 根据子查询的执行次数，分为相关子查询和不相关子查询

4. case中的子查询

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800 的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT emp_id, last_name,
(CASE dept_id
WHEN
(SELECT dept_id FROM dept
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM emp;

5. 多行子查询

5.1 什么是多行子查询

多行子查询就是子查询返回了多行，此时应该使用多行比较符。

5.2 多行比较操作符

• in：等于列表中的任意一个
• any：需要和单行比较操作符一起使用，和子查询返回的某一个值比较。假设有几个妹子追你，你觉着随便一个做你女朋友就可以了，不挑。
• all：需要和单行比较操作符一起使用，和子查询返回的所有值比较。假设有几个妹子追你，你全都要！
• some：any的别名

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

select emp_id,last_name,job_id,salary 
from emp
where salary < any (
        select salary 
        from emp 
        where job_id = 'IT_PROG'
    )
and job_id <> 'IT_PROG';

6. 自连接和子查询哪个好？

对于可以使用自连接又可以使用子查询的题目，一般建议使用自连接，因为在许多DBMS的处理过程中，对于自连接的处理速度比子查询快。子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分DBMS中对自连接处理进行了优化。

八、表的创建与管理

1. 数据存储的过程

在MySQL中， 一个完整的数据存储过程总共有 4 步，分别是创建数据库、确认字段、创建数据表、插入数据。

MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、数 据表的 行与列 。

2. 数据库、表、字段命名规范

数据库名、表名不得超过30个字符

只能包含A–Z, a–z, 0–9, _共63个字符

数据库名、表名、字段名中间不能包含空格

同一个MySQL软件中，数据库名不能同名；同一个库中，表名不能重复；同一个表中，字段名不能重复

建议字段中没有与保留字冲突的字段，否则需要使用着重号(``)引起来

3. 创建、管理数据库

3.1 创建数据库

• 方式1：直接创建

create database 数据库名;

• 方式2：创建并指定字符集

create database 数据库名 character 字符集;

• 方式三：不存在数据库则创建

create database if not exist 数据库名;

3.2 使用数据库

• 查看所有库

show databases;

• 查看当前使用的库

select database();

• 查看指定库下的表

show tables from 数据库名;

• 切换数据库

use 数据库名;

3.3 修改数据库

• 更改数据库字符集

alter database 数据库名 character set 字符集;

3.4 删除数据库

• 删除指定数据库

drop database if exists 数据库名;

4. 表

4.1 创建表

• 语法格式：

create table [if not exists] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    例如：id int(2) auto_increment,
    ……
    [表约束条件]
);
 
 
# 创建一个表，并且复制另一个表的结构、数据
create table 新表名
as
select *
from 表名;
 
# 创建一个表，并且复制另一个表的结构，不要数据
create table 新表名
as
select *
from 表名
where 1=2;

4.2 查看表结构

desc 表名;                 #方式1
describe 表名;             #方式2
show create table 表名;    #方式3

4.3 修改表

• 添加一个列

# 语法格式
alter table 表名 add [column] 字段名 字段类型 [first|after 字段名];
# 举例
alter table  books add author1 varchar(20);

• 修改一个列

# 语法格式
alter table 表名 modify [column] 字段名1 字段类型 [default 默认值][first|after 字段名
2];
 
# 举例
alter table books modify author CHAR;

        说明：修改列可以修改列的数据类型，长度，默认值和位置

• ​​​​重命名一个列

# 语法格式
alter table 表名 change [column] 列名 新列名 新数据类型;
 
# 举例
alter table books change author author_name varchar(20);
# 或
alter table books change column author authorvarchar_name varchar(20);

• 删除一个列

# 语法格式
alter table 表名 drop [column]字段名;
 
 
# 举例
alter table books drop author;

• 重命名表

# 方式1
rename table  `books` to books1;
 
# 方式2
alter table books1 rename books2;

4.4 删除表

# 语法格式
drop table [if exists] 数据表1 [, 数据表2, …, 数据表n];
 
# 举例
drop table if exists books2;

# MySQL5.7
drop table books1,books2;
 

在MySQL5.7中上述语句等价于

drop table books1;
drop table books2;

而在MySQL8.0中新增了DDL的原子化如果其中一张表不存在，则另一张表不会删除。

说明：

•  当前表与其他表没有关联时，才可以删除
•  删除表时，数据和结构都被删除
•  所有正在运行的相关事务都被提交
•  所有相关索引都被删除
• drop table语句不能回滚

4.5 清空表

truncate table books;
 
delete from books;

• truncate语句将删除表中所有数据，释放表的存储空间

4.6 delete、truncate和drop

• delete、truncate都可以删除表中所有的数据，而drop删除整张表，包括表数据和结构
• truncate、drop语句不能回滚,而delete语句删除数据可以回滚，delete是dml语句，要保证在dml之前设置set autocommit = false才能回滚。
• truncate table比delete速度快，而且使用的系统和事务日志资源少，但truncate无事务且不触发trigger，有可能造成事故，不建议使用truncate。总体执行速度：drop>truncate>delete

4.7 清空表、删除表需谨慎

表删除操作将把表结构和数据一起删除，并且没有提示。删除表之前应该对表数据进行备份。

在使用 ”alter table 表名 drop 列名“ 进行表的基本修改时，在执行操作之前也应该对表进行备份。

九、增删改

1. 插入

语法格式：

• 方式1

insert into 表名
values (value1,value2,....);

方式1需要为表中每一个字段指定值，并且顺序要与表中字段一致

• 方式2

insert into 表名(column1 , column2, ...)
values (value1 ,value2, ...);
 
# 多行插入
insert into 表名(column1, column2...)
values 
(value1, value2...),
(value1, value2...);

多行插入时，MySQL会返回：

• records：插入的条目数
• duplicates：被忽略的条目数

2. 更新

语法格式：

update 表名
set column1 = value1, column2 = value2,...
where 条件;

• 如果需要回滚数据，需要保证在DML前，进行设置：set autocommit = false;

3. 删除

语法格式：

delete from 表名 where 条件; 

• where为可选参数，如果没有where，则会删除表中所有记录

十、数据类型

1. MySQL中数据类型

说明：红色为常用类型

整数类型：tinyint、smallint、mediumint、int(范围-2^31~2^32-1,存储大小为4个字节)、bigint

浮点型：float、double

定点数类型：decimal

日期类型：year、time、date、timestamp、datetime

文本字符串类型：char、varchar、tinytext、text(长文本数据，最大4G)、mediumtext、longtext

2. char、varchar

char：定长字符数据，保存时数据的实际长度比char类型声明长度小，则会在右侧填充空格以达到指定长度；如未指定长度，默认为1个字符，最大长度255，速度比varchar快，适用于速度要求高的场景。

varchar：可变长字符数据，根据字符串实际长度保存，必须指定长度

3. float、double

float：单精度，占用4个字节

double：双精度，占用8个字节

4. 整数类型的区别

整数类型如何选择？

在选择整数类型时，需要考虑存储空间和可靠性问题，占用字节数少的可以节省存储空间，但是容易有超出范围的情况出现系统故障，影响可靠性。在实际场景中，出现系统故障的成本大于增加几个字段存储空间的成本，建议使用不会超出取值范围的数据类型。

5. 浮点类型

5.1 float和double的区别

float占用字节数少，取值范围小；double占用字节数多，取值范围大。

5.2 使用浮点数的误差问题

浮点数时不准确的，应该避免使用 ”=“来判断两个数是否相等，在对精度要求比较高的场景，最好不要使用浮点数，浮点数容易造成结果错误。建议使用定点数类型decimal。

6. 定点数类型

6.1 为什么定点数是精准的

定点数在MySQL内部是以字符串的形式存储的，所以一定是精准的。

6.2 浮点数、定点数的使用场景

浮点数：在长度一定的情况下，浮点数的取值范围比定点数大，但是不精准，所以适用于需要取值范围大，又可以容忍微小误差的场景。

定点数：取值范围相对小，但是精准，适用于对精度要求极高的场景。

7. 日期时间类型

datetime、timestamp的区别：timestamp多了一个时区

十一、约束

1. 什么是约束

约束是表记的强制规定，可以在创建表时规定约束，或者在表创建之后通过alter table语句规定。

2. 为什么需要约束

为了保证数据完整性，SQL规范以约束的方式对表数据进行额外的条件限制。

完整性的体现：

• 实体完整性，同一个表中不能存在两条完全相同无法区分的记录
• 域完整性，例如年龄范围应该在合理范围内，0-120
• 引用完整性，通过员工表查询到员工部门编号，必须要在部门表中找到这个部门
• 用户自定义完整性，例如用户名唯一，密码不能为空等

3. 约束的分类

根据约束数据列的限制分为：

• 单列约束，每个约束只约束一个列
• 多列约束，每个约束可以约束多个列

根据作用范围分为：

• 列级约束，作用在一个列上，跟在列的定义后面
• 表级约束，可以作用在多个列上，单独定义

根据作用分为：

• 非空约束(not null)，规定字段不能为空
• 唯一约束(unique)，规定字段在表中是唯一的
• 主键约束(primary key)，规定字段非空且唯一
• 外键约束(foreign key)
• 默认约束(default)，规定字段的默认值

4. 非空约束

4.1 作用

限制某个字段的值不能为空

4.2 特点

• 默认，所有类型的值都可以是null
• 只能某个列单独限定非空，不能组合非空
• 注意，空字符和0都不等于null

4.3 语法格式

# 方式1,建表时
create table 表名 (
    字段名 数据类型 not null
);
 
 
# 方式2,建表后
alter table 表名 modify 字段名 数据类型 not null;

• 删除非空约束

alter table 表名 modify 字段名 数据类型;

5. 唯一约束

5.1 作用

限制某个字段在同一张表中不能重复。

5.2 特点

• 一个表可以有多个唯一约束
• 唯一约束可以时多个列组合的值唯一
• 唯一字段值允许为空
• MySQL会给唯一约束的字段默认创建一个唯一索引
• 如果没有给唯一约束命名，则默认和列名相同
• 添加唯一性约束的列上也会自动创建唯一索引
• 删除唯一约束只能通过删除唯一索引的方式删除
• 删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样

5.3 添加唯一约束

# 方式1, 建表时
create table 表名 (
    字段名 数据类型 unique, # 列级约束
    constraint [约束名] unique(字段名1,字段名2) # 表级约束
);
 
# 方式2, 建表后
alter table 表名 modify 字段名 字段类型 unique;
# 或者
alter table 表名 add unique key(字段名);

5.4 删除唯一约束

alter table 表名
drop index 唯一约束名;

6. 主键约束

6.1 作用

用来唯一标识表中的一行记录。

6.2 特点

• 主键约束相当于唯一约束+非空约束的组合，不允许重复不允许为空
• 表中最多只能有一个主键约束
• 复合主键约束对应表中多列，并且这些列的值不允许为空，并且组合的值不允许重复
• 当创建主键约束时，系统会默认在所在列或列组合上建立对应的主键索引。如果删除主键约束，对应的主键索引也会删除。
• 如果修改了主键的值就可能会破坏数据的完整性

6.3 添加主键约束

# 列级
create table 表名 (
    字段1 数据类型 primary key,
    字段2 数据类型
);
 
 
# 表级
create table 表名 (
    字段1 数据类型,
    字段2 数据类型,
    primary key(字段名)
);
 
# 建表后添加
alter table 表名 add primary key(字段名);

6.4 添加复合主键

create table 表名(
    字段1 数据类型,
    字段2 数据类型,
    primary key(字段1,字段2)
);

6.5 删除主键约束

alter table 表名 drop primary key;

7. 自增约束

7.1 作用

字段的值自增。

7.2 特点

• 一个表只能有一个自增列
• 自增约束的列的数据类型必须是整数类型
• 自增列必须是主键列或者唯一列

7.3 添加自增约束

# 方式1,建表时
create table 表名 (
    字段1 int primary key auto_increment,
    字段2 数据类型
);
# 或者
create table 表名 (
    字段1 int unique key auto_increment,
);
 
# 方式2,建表后
alter table 表名 modify 字段名 int auto_increment;

7.4 删除自增约束

alter table 表名 modify 字段名 数据类型;

8. 外键约束

8.1 作用

限定某个表的某个字段引用的完整性。

8.2 主表、从表

被参考的表为主表，参考别的表为从表。例如：

 

 员工表的员工所在部门需要参考部门表，则部门表为主表，员工表是从表

8.3 特点

• 从表的外键列(上图中emp表的dept_id)，必须参考主表(上图中dept表)的主键或者唯一约束列，因为被参考的值必须是唯一的
• 创建外键约束时，如果不指定外键约束名，默认不是列名，会自动生成一个外键名
• 创建表时指定外键约束的话，先创建主表再创建从表
• 删除表时先删除从表在删除主表
• 当主表的记录被从表参考时，主表记录不允许被删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
• 外键约束在从表中创建，一个表可以建立多个外键约束
• 从表的外键类型必须与主表中被参考的键的类型一致，名称可以不一致
• 创建外键约束时，系统默认在该列上建立普通索引
• 删除外键约束后，必须手动删除对应的索引
• 添加外键约束后，主表的修改和删除数据受约束。从表的添加和修改该受约束

8.4 添加外键约束

# 方式1,建表时
create table 主表 (
    字段1 数据类型 primary key,
    字段2 数据类型
);
 
create table 从表 (
    字段1 数据类型 primary key,
    字段2 数据类型,
    foreign key (从表字段) references 主表(主表被参考字段) 
);
 
 
# 方式2,建表后
alter table 从表 add foreign key (从表字段) references 主表(主表被参考字段);

8.5 有和没有外键约束有什么区别

有外键约束：创建表、删除表、添加、修改、删除会受到限制，例如在员工表中不能添加一个员工信息，他的部门在部门表中找不到。

没有外键约束：创建表、删除表、添加、修改、删除不受限制，要保证数据的引用完整性只能依靠程序员自觉。

9. 默认约束

9.1 作用

给某个字段指定默认值，一旦设置默认值，在插入默认值时，如果字段没有显式赋值，则赋值为默认值。

9.2 添加默认约束

# 方式1, 建表时
create table 表名 (
    字段1 数据类型 primary key,
    字段2 数据类型 not null default 默认值
);
 
# 方式2, 建表后
alter table 表名 modify 字段名 数据类型 default 默认值;

9.3 删除默认约束

# 删除默认值约束，也不保留非空约束
alter table 表名 modify 字段名 数据类型;
 
# 删除默认值约束，保留非空约束
alter table 表名 modify 字段名 数据类型 not null;
 

9.4 建表时，为什么要加not null default '' 或default 0

不想让表中出现null值，因为null值不好比较，null时特殊值，比较时只能用专门的is null或者 is not null比较。碰到运算符，通常返回null。而且效率不高，影响提高索引效果，所以往往在建表时加not null default '' 或default 0。

9.5 auto_increment约束的字段值是从1开始的吗

设置自增约束时，如果指定了第一条插入记录的自增字段的值，后面新插入的记录的自增字段值会从初始值开始，例如插入第一条记录指定自增字段值为5，那么后面插入就会从6开始。

9.6 每个表都可以任意选择存储引擎吗

外键约束不能跨引擎使用。如果多表之间需要关联外键，这些表必须使用相同的存储引擎。

十二、视图

1. 什么是视图

视图是一种虚拟表，不存储数据，占用很少的内存空间，向视图提供数据内容的语句为select语句，可以将视图理解为存储起来的select语句。视图的建立是在已有表的基础上，视图依赖建立的那些表称为基表。

2. 特点

视图的创建和删除只影响视图本身，不影响对应的基表。但是对视图中的数据进行增删改操作时，数据表中的数据会发生变化。

3. 为什么要使用视图

视图可以帮助我们使用表的一部分，而不是所有的表，同时还可以针对不同用户定制不同的查询视图。在数据表比较复杂的情况下，视图可以帮助我们吧经常查询的结果放到虚拟表中，提升使用效率。

4. 创建视图

create view 视图名
as 查询语句;
 
# 查看创建的视图
select * from 视图名;
 
# 查看视图的结构
desc 视图名;

5. 修改视图

alter view 视图名
as
查询语句;

6. 删除视图

drop view if exists 视图名;

7. 视图的优点

• 操作简单，开发人员不需要关心视图对应的数据表结构、数据表之间的查询条件，只需要操作视图即可
• 减少数据冗余，视图和数据表不同，视图储存的是查询语句，使用时，只需要通过视图的查询语句获取结果集，视图本身不储存数据，不占用储存资源，减少了数据冗余
• 数据安全，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息，在一定程度上保障了数据表中数据的安全性
• 灵活多变，当业务需求发生改变后，如果需要修改数据表的结果，工作量会比较大，此时可以使用视图来减少改动的工作量，在实际工作中使用较多
• 将查询简单化，数据库中如果存在复杂的查询逻辑，可以将问题分解，创建多个视图获取数据，再将多个视图结合起来完成复杂的查询逻辑

8. 视图的缺点

在数据表上创建了视图，如果数据表的结构发生改变，就需要对相关视图进行维护，特别是嵌套的视图，维护比较复杂，可读性差。另外视图太多会导致数据库维护成本加大。

十三、储存过程、函数

1. 什么是存储过程？

一组经过预编译的SQL语句的封装

2.存储过程的好处

• 简化操作，提高SQL语句的重用性，减少了开发的压力
• 减少操作过程中的失误，提高效率
• 客户端不需要把所有的SQL语句通过网络发送给服务器，减少了网络传输量
• 减少了SQL语句暴露在网上的风险，提高了数据查询的安全性

3.与视图的区别

视图是虚拟表，通常不对底层数据表直接操作。

存储过程时程序化SQL，可以直接操作底层数据表，能够实现一些更复杂的数据处理。存储过程一旦被创建，使用时直接调用存储过程名即可。

4. 创建存储过程

create procedure 存储过程名(in/out/inout 参数名 参数类型,...)
begin
    存储过程
end
 
 
# 参数修饰符的含义
# 1. in ：当前参数为输入参数
# 2. out ：当前参数为输出参数
# 3. inout ：当前参数既可以为输入参数,也可以作为输出参数

5. 调用存储过程

call 存储过程名(参数)

总结

本文介绍了mysql的基础知识，文中如有不足还望指教。