MySQL 学习笔记——基础SQL

DDL（Data Definition Language）

DDL（Data Definition Language），数据定义语言，用来定义数据库对象，包括数据库、表、字段

查询所有数据库

show databases

查询当前数据库

select database()

创建数据库

create database [if not exists] 数据库名 [default charset 字符集] [default collate 排序规则]

删除数据库

drop database [if exists] 数据库名

使用数据库

use 数据库名

查询当前数据库所有表

show tables

查询表结构

desc 表名

查询指定表的建表语句

show create table 表名

创建表

create table 表名 (
	字段名 字段类型 [comment 字段注释],
    字段名 字段类型 [comment 字段注释],
) [comment 表注释]

在表中添加字段

alter table 表名 add 字段名 字段类型 [comment 字段注释] [约束]

修改字段类型

alter table 表名 modify 字段名 新数据类型 [约束]

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 字段类型 [comment 字段注释] [约束]

删除字段

alter table 表名 drop 字段名

修改表名

alter table 表名 rename to 新表名

删除表

drop table [if exists] 表名

清空表中数据

truncate table 表名

---

DML（Data Manipulation Language）

DML（Data Manipulation Language），数据操作语言，用来对表中的数据进行增删改操作

增加一条记录

指定部分字段，甚至可以字段顺序不与表中字段顺序保持一致

insert into 表名(字段名1, 字段名2, ……) values(值1, 值2, ……)

必须填入全部字段

insert into 表名 values(值1, 值2, ……)

增加多条记录

指定部分字段，甚至可以字段顺序不与表中字段顺序保持一致

insert into 表名(字段名1, 字段名2, ……) values(值1, 值2, ……), (值1, 值2, ……)

必须填入全部字段

insert into 表名 values(值1, 值2, ……), (值1, 值2, ……)

修改记录

update 表名 set 字段名1 = 值1, 字段名2 = 值2, …… [where 条件]

删除记录

delete from 表名 [where 条件]

---

DQL（Data Query Language）

DQL（Data Query Language），数据查询语言，用来查询表的记录

查询格式

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

基本查询

查询多个字段

select 字段1, 字段2, 字段3, …… from 表名
select * from 表名

设置别名

select 字段1 [as 别名1], 字段2 [as 别名2], …… from 表名

去除重复记录（多个 null 也会被去重）

select distinct 字段列表 from 表名

条件查询

select 字段列表 from 表名 where 条件列表

运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between ... and ...	在 ... 范围之间，含边界值
in(...)	在列表中
like	模糊匹配（_匹配单个字符，#匹配任意个字符）
is null	为空
and 或 &&	与
or 或 ||	或
not 或 !	非

聚合查询

select 聚合函数(字段列表) from 表名

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

count() 函数效率问题

InnoDB下，效率：count(字段) < count(主键字段) < count(1) ≈ count(*)

• count(*)：不做是否为 null 判断，统计所有行数（MySQL做了优化）
• count(1)：不做是否为 null 判断，统计所有行数
• count(主键字段)：取出对应的主键并计数 + 1，因为主键会走索引，所以效率稍高
• count(字段)：取出对应的字段，判断是否为null，不为 null 才计入

可通过 count(dinstinct 字段名) 查询不重复的字段行数

MyISAM 将一个表的总行数存在了磁盘中，执行 count(*) 时会直接返回这个数，效率很高

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

where 中不能使用聚合函数，而 having 中可以

查询员工年龄小于 45，且员工数量大于 3 的工作地点

select work_address from employee where age < 45 group by work_address having count(*) > 3

排序查询

多字段排序时，当第一个字段相同，才会进行第二个字段排序

• asc：升序（默认）
• desc：降序

select 字段列表 from 表名 order by 字段1 排列方式1, 字段2 排列方式2

分页查询

起始索引省略时，默认为 0

select 字段列表 from 表名 limit [起始索引]，查询记录数

---

DCL（Data Control Language）

DCL（Data Control Language），数据控制语言，用来创建数据库用户，控制数据库的访问权限

查询用户

use mysql;
select * from user;

创建用户

mysql 通过用户名 + 主机名唯一标识一个用户，若想要在任意主机访问，需使用通配符 % 表示主机名

create user '用户名'@'主机名' identified by '密码'

删除用户

drop user '用户名'@'主机名'

查询权限

show grants for '用户名'@'主机名'

授予权限

数据库名和表名可以使用通配符 *

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

权限列表

权限	说明
all，all privileges	所有权限
select	查询记录
insert	增加记录
update	修改记录
delete	删除记录
alter	修改表
drop	删除数据库、表、视图
create	创建数据库、表