MySQL基础重要知识点及面试点--第一篇

目录

SQL分类：

DDL语句

DML语句

DQL 查询语句，语法结构如下：

DCL语句

函数

字符串函数

日期函数

约束

删除约束

复合主键

查询

多表查询分类

连接查询-内连接

连接查询-外连接

子查询

事务

事务操作

---

SQL分类：

DDL、DML、DQL、DCL

DDL语句

表操作-修改

添加字段：

ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改字段类型：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段：

ALTER TABLE 表名 DROP 字段名;
ALTER TABLE emp DROP username;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;
ALTER TABLE emp RENAME TO employee;

DML语句

添加、删除、修改数据

删除

DELETE FROM 表名 [ WHERE 条件 ] ;
delete from employee where gender = '女';

delete语句不能删除某一个字段的值，可以使用update将该字段的值置为null

DQL 查询语句，语法结构如下：

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数

去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

where与having区别？

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组

之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

DQL语句在执行时的执行顺序

DCL语句

英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访

问权限。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

函数

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

字符串函数

日期函数

常见的日期函数如下：

约束

分类:

删除约束

如何删除自增长？

直接更改具有自增长约束的主键

ALTER TABLE t_user MODIFY id INT 

如何删除主键约束？

ALTER TABLE t_user DROP PRIMARY KEY 

如何删除唯一约束？

ALTER TABLE t_user DROP INDEX username

如何删除非空约束？

ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;

复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

check约束

MySQL5.7 可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警

告

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

带AUTO_INCREMENT约束的字段值是从1开始的吗？

在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性（

AUTO_INCREMENT）的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一条记录，同时指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性。

查询

多表查询

当执行多表查询时会出现类似于笛卡尔积这种现象。只需在多表查询中加上连接条件就可以消除笛卡尔积。

//会出现笛卡尔积
select * from emp , dept

select * from emp , dept where emp.dept_id = dept.id;

多表查询分类

连接查询

内连接：相当于查询A、B交集部分数据

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

连接查询-内连接

内连接的语法分为两种: 隐式内连接、显式内连接。

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

连接查询-外连接

左外连接语法格式

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接语法格式

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

联合查询

语法

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

注：union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

子查询分类：

根据子查询的结果不同分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

列子查询

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

any用法举例

any一般配合>符号进行使用

查询比研发部任一员工工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id =
(select id from dept where name = '研发部') );

all用法举例

查询比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

查询与 "张无忌" 的薪资及直属领导相同的员工信息

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );

事务

事务默认是自动提交

事务操作

控制事务

开启事务

START TRANSACTION 或 BEGIN ;

提交事务

commit

回滚事务

ROLLBACK;

举例

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;