[持续更新]Mysql知识点

一、三大范式

1.第一范式（1NF）

必须有主键，列不可分，数据库表中的任何字段都是单一属性的，不可再分。

-- 不满足第一范式
create table aa(id int,NameAge varchar(100))
insert aa values(1,''无限-女 '')

-- 满足第一范式
create table aa(id int,name varcahr(10),age char(2))
insert aa values(1,''无限'',''女 '')

​2.第二范式（2NF）

满足第一范式且表中的每一个非主属性，必须完全依赖于主键。

-- 不满足第二范式： 
-- 非关键字属性credit仅函数依赖于cno，也就是credit部分依赖组合关键字（sno,cno）
-- 课程(cno)1---1学分(credit)
-- 学生(sno)n---n课程(cno)
-- 学生+课程--->分数(grade)
create table sci(
   sno int(32),cno int(32),grade int(32),credit int(32),
primary key sno,cno
)

-- 满足第二范式
sc1（sno,cno,grade），c2（cno,credit）

3.第三范式（3NF）

满足第二范式且所有非主属性对任何候选关键字都不存在传递依赖；

-- 不满足第三范式
-- 学号 姓名 所在系 系名称 系地址
-- 关键辽 SNO 对 LOCATION 函数决定是通过传递依赖,SNO不直接决定非主属性LOCATION. 
S1（SNO，SNAME，DNO，DNAME，LOCATION)

-- 满足第三范式
S（SNO，SNAME，DNO），D（DNO，DNAME，LOCATION）  

二、基本操作

1.数据库相关操作

登陆mysql：./mysql -u root -p       
创建数据库：create database 数据库名 ;       
查看mysql中所有数据库：show databases;       
切换数据库：use 数据库名;       
查看当前正在使用的数据库：select database();       
删除数据库：drop database 数据库名;       

-- 登陆mysql
mengting@mengtingdeMacBook-Pro bin % ./mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 101
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

-- 创建数据库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

-- 查看mysql中所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

-- 切换数据库
mysql> use db1;
Database changed

-- 查看当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

-- 删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

2.数据表相关操作

创建数据表：craete table 表名(字段1 数据类型,...字段n 数据类型);
查看数据库中所有数据表：show tables;
查看表的字段信息：desc 表名;
修改数据表名：alter table 当前表名 rename to 新表名;
修改表字段名：alter table 表名 change 当前字段名 新字段名 数据类型;
修改字段数据类型：alter table 表名 modify 字段名 数据类型;
新增字段：alter table 表名 add 字段名 数据类型;
删除字段：alter table 表名 drop 字段名;
删除数据表：drop table 表名

-- 创建数据表
mysql> create table player(id int,name varchar(20),sex varchar(10),age int);
Query OK, 0 rows affected (0.03 sec)

-- 查看当前数据库中所有表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| player        |
+---------------+

-- 查看表的字段信息
mysql> desc player;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 修改表名
mysql> alter table player rename to user;
Query OK, 0 rows affected (0.01 sec)

-- 修改字段名
mysql> alter table user change name sname varchar(20);
Query OK, 0 rows affected (0.02 sec)

-- 修改字段数据类型
mysql> alter table user modify sex int;
Query OK, 0 rows affected (0.03 sec)

-- 新增字段
mysql> alter table user add bir date;
Query OK, 0 rows affected (0.01 sec)

-- 删除字段
mysql> alter table user drop age;
Query OK, 0 rows affected (0.03 sec)

-- 删除数据表
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)

避免创建数据库及表出现中文乱码方法
创建数据库追加：CHARACTER SET 'utf8’COLLATE ‘utf8_general_ci’;
建表追加：ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、数据表的约束

约束条件	说明	语法
PRIMARY KEY	主键约束	字段名 数据类型 primary key;
FOREIGN KEY	外键约束
NOT NULLY	非空约束	字段名 数据类型 NOT NULL;
UNIQUE	唯一性约束	字段名 数据类型 UNIQUE;
DEFAULT	默认值约束	字段名 数据类型 DEFAULT 默认值；
1.创建外键约束

在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)
将创建数据表创好后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);
删除外键：
alter table 从表名 drop foreign key 外键名；

-- 创建一个学生表
create table student05(
id int primary key,
name varchar(20)
);

-- 创建一个班级表
create table class(
classid int primary key,
studentid int
);

-- 学生表作为主表，班级表作为副表设置外键
alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

关于外键约束需要注意的细节
1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

四、增、改、删、查

1.增

所有字段插入数据：INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
指定字段插入数据：INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);
同时插入多条数据：INSERT INTO 表名 (字段名1,...) VALUES (值 1,…),(值 1,...),(.....);

-- 插入一条数据
mysql> insert into player (id,name,balance) values (1,"tom",10);
Query OK, 1 row affected (0.00 sec)

-- 插入指定数据
mysql> insert into player (id,name) values (1,"davi");
Query OK, 1 row affected (0.01 sec)

-- 插入多条数据
mysql> insert into player (id,name,balance) values (2,"a",10),(3,"b",10),(4,"c",100);
Query OK, 3 rows affected (0.00 sec)

-- 查看数据表所有数据
mysql> select * from player;
+------+------+---------+
| id   | name | balance |
+------+------+---------+
|    1 | tom  |      10 |
|    1 | davi |    NULL |
|    2 | a    |      10 |
|    3 | b    |      10 |
|    4 | c    |     100 |
+------+------+---------+
5 rows in set (0.00 sec)

2.改

更新部分数据：UPDATE 表名 SET 字段名1=值1,字段名2 =值2..., [WHERE 条件表达式];
更新全部数据：UPDATE 表名 SET 字段名1=值1;

-- 更新指定数据
mysql> update player set id=5 where name="davi";
Query OK, 1 row affected (0.00 sec)

-- 更新全部数据
mysql> update player set balance=10000;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

-- 
mysql> select * from player;
+------+------+---------+
| id   | name | balance |
+------+------+---------+
|    1 | tom  |   10000 |
|    5 | davi |   10000 |
|    2 | a    |   10000 |
|    3 | b    |   10000 |
|    4 | c    |   10000 |
+------+------+---------+
5 rows in set (0.00 sec)

3.删

删除部分数据：DELETE FROM 表名 [WHERE 条件表达式];
删除全部数据：DELETE FROM 表名;

-- 删除部分数据
mysql> delete from player where name="a";
Query OK, 1 row affected (0.01 sec)

mysql> select * from player;
+------+------+---------+
| id   | name | balance |
+------+------+---------+
|    1 | tom  |   10000 |
|    5 | davi |   10000 |
|    3 | b    |   10000 |
|    4 | c    |   10000 |
+------+------+---------+
4 rows in set (0.00 sec)

-- 删除全部数据
mysql> delete from player;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from player;
Empty set (0.00 sec)

4.查

查询所有数据：select * from 表名;
查询指定数据：select 字段名1,字段名2... from 表名；
过滤重复数据：select distince 需要过滤的字段名 from 表名;
使用运算符查询：select 字段名(+ - * / %) from 表名；

-- 查询所有数据
mysql> select * from player;
+------+------+---------+
| id   | name | balance |
+------+------+---------+
|    1 | hh   |   10000 |
|    2 | hh   |     100 |
+------+------+---------+
2 rows in set (0.00 sec)

-- 查询指定数据
mysql> select name from player;
+------+
| name |
+------+
| hh   |
| hh   |
+------+
2 rows in set (0.00 sec)

-- 过滤重复数据
mysql> select distinct name from player;
+------+
| name |
+------+
| hh   |
+------+
1 row in set (0.00 sec)

-- 对查询结果使用运算符
mysql> select balance-100 from player;
+-------------+
| balance-100 |
+-------------+
|        9900 |
|           0 |
+-------------+
2 rows in set (0.00 sec)

mysql> select balance+100 from player;
+-------------+
| balance+100 |
+-------------+
|       10100 |
|         200 |
+-------------+
2 rows in set (0.00 sec)

mysql> select balance*100 from player;
+-------------+
| balance*100 |
+-------------+
|     1000000 |
|       10000 |
+-------------+
2 rows in set (0.00 sec)

mysql> select balance/100 from player;
+-------------+
| balance/100 |
+-------------+
|         100 |
|           1 |
+-------------+
2 rows in set (0.00 sec)

mysql> select balance%100 from player;
+-------------+
| balance%100 |
+-------------+
|           0 |
|           0 |
+-------------+
2 rows in set (0.00 sec)

五、聚合函数

count()：统计指定列其值不为NULL的数据个数；
max()：计算指定列的最大值；
min()：计算指定列的最小值；
sum()：计算指定列的数值和；
avg()：计算指定列的平均值；

mysql> select count(*) from player;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select max(balance) from player;
+--------------+
| max(balance) |
+--------------+
|        10000 |
+--------------+
1 row in set (0.00 sec)

mysql> select min(balance) from player;
+--------------+
| min(balance) |
+--------------+
|          100 |
+--------------+
1 row in set (0.00 sec)

mysql> select sum(balance) from player;
+--------------+
| sum(balance) |
+--------------+
|        10100 |
+--------------+
1 row in set (0.00 sec)

mysql> select avg(balance) from player;
+--------------+
| avg(balance) |
+--------------+
|         5050 |
+--------------+
1 row in set (0.00 sec)

六、条件查询

通过WHERE子句指定查询条件对查询结果进行过滤

1.使用关系运算符查询​​​​​​​

-- 查询age大于16的数据
mysql> select * from student where age >16;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
4 rows in set (0.00 sec)

2.IN关键字查询

用于判断某个字段的值是否在指定集合中

mysql> select * from student where gender in ("female");
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1004 | hfgs  |   17 | female |
| S_1006 | zxsd  |   19 | female |
| S_1008 | tyop  |   15 | female |
| S_1010 | xdfv  |   17 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
6 rows in set (0.00 sec)

3.使用BETWEEN AND关键字查询

用于判断某个字段的值是否在指定的范围之内

mysql> select * from student where age between 12 and 18;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
| S_1012 | lili  |   14 | male   |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
11 rows in set (0.00 sec)

4.使用空值查询

在MySQL中，使用 IS NULL关键字判断字段的值是否为空值。
请注意：空值NULL不同于0，也不同于空字符串

mysql> select * from student where age is not null;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
| S_1012 | lili  |   14 | male   |
| S_1013 | wang  |   15 | female |
| S_1015 | wqq   |    0 | male   |
+--------+-------+------+--------+
13 rows in set (0.00 sec)

5.使用AND\OR关键字查询

使用AND关键字可以连接两个或者多个查询条件， 同时满足时返回。
使用OR关键字连接多个査询条件，满足其一即可。

mysql> select * from student where age is not null and gender in ("male");
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1003 | tywd  |   16 | male   |
| S_1005 | qwer  |   18 | male   |
| S_1007 | hjop  |   16 | male   |
| S_1009 | nhmk  |   13 | male   |
| S_1012 | lili  |   14 | male   |
| S_1015 | wqq   |    0 | male   |
+--------+-------+------+--------+
7 rows in set (0.00 sec)

mysql> select * from student where age is not null or gender in ("male");
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
| S_1012 | lili  |   14 | male   |
| S_1013 | wang  |   15 | female |
| S_1014 | wq    | NULL | male   |
| S_1015 | wqq   |    0 | male   |
+--------+-------+------+--------+
14 rows in set (0.00 sec)

6.使用LIKE关键字查询

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配

7.含有%通配的字符串

%用于匹配任意长度的字符串

8.含有_通配的字符串

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符

mysql> select * from student where sname like "wang";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where sname like "w";
Empty set (0.00 sec)

mysql> select * from student where sname like "w%";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
| S_1014 | wq    | NULL | male   |
| S_1015 | wqq   |    0 | male   |
+--------+-------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from student where sname like "%g";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where sname like "%a%";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where sname like "w_";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1014 | wq    | NULL | male   |
+--------+-------+------+--------+
1 row in set (0.00 sec)

mysql> select * from student where sname like "w__";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1015 | wqq   |    0 | male   |
+--------+-------+------+--------+
1 row in set (0.00 sec)

mysql> select * from student where sname like "w___";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where sname like "___g";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student where sname like "_a__";
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1013 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

9.使用LIMIT限制查询结果的数量

mysql> select * from student where age > 10 limit 2;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

10.使用GROUP BY进行分组查询

mysql> select count(*),gender from student group by gender;
+----------+--------+
| count(*) | gender |
+----------+--------+
|        6 | female |
|        8 | male   |
+----------+--------+
2 rows in set (0.01 sec)

11.使用ORDER BY对查询结果排序

mysql> select * from student order by age asc;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1014 | wq    | NULL | male   |
| S_1015 | wqq   |    0 | male   |
| S_1009 | nhmk  |   13 | male   |
| S_1001 | lili  |   14 | male   |
| S_1012 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1008 | tyop  |   15 | female |
| S_1013 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1007 | hjop  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1010 | xdfv  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
+--------+-------+------+--------+
14 rows in set (0.00 sec)

mysql> select * from student order by age desc;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1006 | zxsd  |   19 | female |
| S_1005 | qwer  |   18 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1010 | xdfv  |   17 | female |
| S_1003 | tywd  |   16 | male   |
| S_1007 | hjop  |   16 | male   |
| S_1002 | wang  |   15 | female |
| S_1008 | tyop  |   15 | female |
| S_1013 | wang  |   15 | female |
| S_1001 | lili  |   14 | male   |
| S_1012 | lili  |   14 | male   |
| S_1009 | nhmk  |   13 | male   |
| S_1015 | wqq   |    0 | male   |
| S_1014 | wq    | NULL | male   |
+--------+-------+------+--------+
14 rows in set (0.00 sec)

七、多表连接查询

1.内连接查询

对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。
SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表	

mysql> select employee.ename,employee.eage,department.dname from employee join department on employee.departmentid = department.did;
+--------+------+-----------+
| ename  | eage | dname     |
+--------+------+-----------+
| 王五   |   20 | 财务部    |
| 李四   |   18 | 技术部    |
| 张三   |   19 | 行政部    |
| 赵六   |   20 | 生活部    |
+--------+------+-----------+

2.左连接查询和右连接查询

返回包括左表中的所有记录和右表中符合连接条件的记录。
返回包括右表中的所有记录和左表中符合连接条件的记录。
SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

mysql> select employee.ename,employee.eage,department.dname from employee left join department on employee.departmentid = department.did;
+--------+------+-----------+
| ename  | eage | dname     |
+--------+------+-----------+
| 王五   |   20 | 财务部    |
| 李四   |   18 | 技术部    |
| 张三   |   19 | 行政部    |
| 赵六   |   20 | 生活部    |
+--------+------+-----------+
4 rows in set (0.00 sec)

mysql> insert into employee(eid,ename,eage,departmentid) values(5,"一一",12,0000);
Query OK, 1 row affected (0.00 sec)

mysql> select employee.ename,employee.eage,department.dname from employee left join department on employee.departmentid = department.did;
+--------+------+-----------+
| ename  | eage | dname     |
+--------+------+-----------+
| 王五   |   20 | 财务部    |
| 李四   |   18 | 技术部    |
| 张三   |   19 | 行政部    |
| 赵六   |   20 | 生活部    |
| 一一   |   12 | NULL      |
+--------+------+-----------+
5 rows in set (0.00 sec)

重要（从关键字分析）：
查询语句的书写顺序和执行顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi