mysql基础语法（DDL、DML、DQL、DCL）

一、SQL语句分类

1. Data Definition Language：简称DDL；数据定义语言，用于建库，建表等操作。
2. Data Manipulation Language：简称DML；数据操纵语言，用于对表中记录的增删改操作
3. Data Query Language：简称DQL；数据查询语言，用于查询记录
4. Data Control Language：简称DCL；数据控制语言，用于对用户进行操作，如：创建用户，给用户权限

二、DDL操作数据库

2.1 创建数据库

• 创建数据库

CREATE DATABSE 库名；

create database db01;

• 判断数据库是否已经存在，若不存在这创建数据库

CREATE DATABASE IF NOT EXISTS 库名

create database if not exists db01;

• 创建数据库并指定字符集

CREATE DATABASE 库名 CHARACTER SET 字符集

create database db01 character set utf8;

2.2 查看数据库

• 查看所有的数据库

show databases;

• 查看某个数据库的DDL语句

show create database db01;

2.3 修改数据库

• 修改数据库默认字符集

ALTER DATABASE 数据库 CHARACTER SET 字符集

alter database db01 character set utf8;

2.4 删除数据库

DROP DATABASE 数据库名;

drop database db01;

2.5 使用数据库

• 查看正在使用的数据库

select DATABASE();

• 使用/切换数据库

USE 数据库名;

use db01;

三、DDL操作表结构

3.1 创建表

• 创建表的格式

create table 表名(
字段名 字段类型,
字段名 字段类型,
字段名 字段类型
);
或 create table 表名(字段名 字段类型,字段名 字段类型);

注：字段名就是表中的列名，字段类型指数据类型。

create table students(
		id int(5) primary key,   --primary key是表明主键的意思
		`name` varchar(10),      -- 重音符号（与关键词相同）：在Esc下面，~ 在同一个键
		sex char(2),
		age int(5)
);

3.2 查看表

• 查看某个数据库中的所有表

show tables;

• 查看表结构

desc 表名;

desc emp;

• 查看创建表的SQL语句

show create table 表名;

show create table emp;

• 复制表结构

CREATE TABLE 新表 LIKE 旧表;

create table e1 like emp;    -- 创建e1表，e1表结构和emp表结构相同
desc e1;                    -- 查看e1表的表结构

3.3 删除表

• 直接删除表

DROP TABLE 表名;

drop table e1;

• 判断表是否存在，如果存在则删除表

DROP TABLE IF EXIST 表名;

drop table if exists e1;

3.4 修改表结构

• 添加表列ADD

ALTER TABLE 表名 ADD 列名 类型;

alter table emp add age int;

• 修改列类型MODIFY

只修改数据类型
ALTER TABLE 表名 MODIFY列名 新的类型;

alter table emp modify address varchar(100);

• 修改列名 CHANGE

既修改字段名又修改类型
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

alter table emp change address addr varchar(80);

• 删除列 DROP

ALTER TABLE 表名 DROP 列名;

alter table emp drop age;

• 修改表名

RENAME TABLE 表名 TO 新表名;
注：MySQL中没有直接修改库名的语句

-- 将emp表改为employee
rename table emp to employee;

-- 将employee表改为emp
rename table employee to emp;

• 修改字符集character set

ALTER TABLE 表名 character set 字符集;

-- 查看emp表的建表语句
show create table emp;

-- 修改emp表的字符集为gbk
alter table emp character set gbk;

-- 修改emp表的字符集为utf8
alter table emp character set utf8;

四、DML操作表中的数据

4.1 插入记录

• 插入全部字段

INSERT INTO 表名 VALUES (字段值1，字段值2…);

insert into emp values(1,'张三','男','河南汤阴',39);

• 插入部分数据

INSERT INTO 表名 (字段名1,字段名2) VALUES (值1，值2);

-- 插入指定的字段
insert into emp(id,name,sex) values(2,'李四','男');

-- 也可以不按照列的顺序插入
insert into emp(id,name,addr) values(3,'王五','湖南邵阳');

注：没有添加数据的字段会使用NULL

• 插入多条记录

INSERT INTO 表名 values (值1,值2),(值1,值2),(值1,值2);

INSERT INTO emp
VALUES
	( 4, '小红', '女', '四川南充', 25 ),
	( 5, '小明', '女', '河南平顶山', 24 ),
	( 6, '小李', '男', '陕西榆林', 20 );

4.2 更新表记录

• 不带条件修改数据

UPDATE 表名 SET 字段名=值,字段名=值;

update emp set sex='女',addr='广西来宾';

• 带条件修改数据

UPDATE 表名 SET 字段名=值 WHERE 条件表达式;

-- 一次修改一列
update emp set addr='甘肃白银' where id=2;

-- 一次修改多列
update emp set sex='男',addr='陕西忻州' where id=5;

4.3 删除表记录

• 不带条件删除数据

DELETE FROM 表名;

delete from emp;

注：不带条件的删除会删除表中的所有记录，慎用

• 带条件删除

DELETE FROM 表名 WHERE 条件表达式;

delete from emp where id=5;

• truncate清空表

truncate 表名;

truncate emp;

注：先删除表结构,再创建一个相同的表结构 相当于drop table emp，再create table emp

五、DQL查询表中的数据

5.1 查询表所有行和列的数据

SELECT * FROM 表名;

select * from emp;

5.2 查询指定列

SELECT 字段名1, 字段名2, 字段名3, … FROM 表名;

select id,name,addr from emp;

5.3 指定列的别名进行查询

SELECT 字段名1 AS 别名, 字段名2 AS 别名… FROM 表名;

--使用关键字as
select id as 编号,name as 姓名,addr as 地址 from emp;

--直接指定别名
select id 编号,name 姓名,addr 地址 from emp;

5.4 清楚重复值

SELECT DISTINCT 字段名 FROM 表名;

-- 去重id相同的行
select distinct id from emp;

5.5 查询结果参与运算

• 某列数据和固定值运算

SELECT 列名1 + 固定值 FROM 表名;

select id,name,addr,age+1 from emp;

• 某列数据和其他列数据参与运算

SELECT 列名1 + 列名2 FROM 表名;

select id,name,addr,id+age from emp;

六、DQL条件查询

6.1 比较运算符

比较运算符	说明
>、<、<=、>=、=、<>	<>是不等于，在mysql中也可使用!=

-- 查询年龄大于25岁的学生
select * from student where age>25;

-- 查询数学成绩大于等于80分的学生
select * from student where math>=80;

-- 查询英语成绩小于等于90的学生
select * from student where english<=90;

-- 查询数学成绩大于英语成绩的学生
select * from student where math>english;

-- 查询性别不为男的学生
select * from student where sex!='男';

-- 查询性别不为男的学生(第二种写法)
select * from student where sex<>'男';

6.2 逻辑运算符

逻辑运算符	说明
and 或 &&	与，全真为真
or 或 ||	或，见真为真
not 或 !	非，取反

-- 查询年龄大于22岁并且性别为男的学生
select * from student where age>22 and sex='男';

-- 查询数学成绩大于80并且性别为女的学生
select * from student where english>80 and sex='女'

-- 查询数学成绩大于90或者英语成绩大于90的学生
select * from student where math>60 or english>60;

6.3 in关键字

SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2…);

-- 查询id是1或2或5的学生
select * from student where id in (1,2,5);

-- 查询id不是1或2或5的学生
select * from student where id not in (1,2,5);

注：in里面的每个数据都会作为一次条件，只要满足条件的就会显示

6.4 范围查询

SELECT 查询显示的字段名 FROM 表名 WHERE 判断的字段名 BETWEEN 值1 AND 值2；

-- 查询english成绩大于等于60，且小于等于90的学生
select * from student where english between 60 and 90;

select * from student where english >=60 and english <=90;

注：between的范围是包头又包尾的，如between 3 and 9;等同于 3<= x <=9;

6.5 like关键字

• MySQL通配符

通配符	说明
%	匹配任意多个字符
_	匹配一个字符

• like表示模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE ‘通配符字符串’;

-- 查询地址为'广'开头城市的学生
select * from student where address like '广%';

-- 查询地址中包含有'西'城市的学生
select * from student where address like '%西%';

-- 查询六个字地址城市的学生
select * from student where address like '_____';		-- 五个_

6.6 查询为空的列 IS NULL

-- 查询性别成绩为NULL的学生
select * from student where sex is null;

-- 错误的
select * from student where sex = null;

-- 查询英语成绩不为NULL的学生
select * from student where sex is not null;

6.7 排序查询

注：排序本身不会影响到表中的记录位置，只是查询结果变成有序的。默认是升序，从小到大。数字和字符都有大小的。汉字默认按拼音顺序。

SELECT * FROM 表名 ORDER BY 字段名 [ASC/DESC]

升序：ASC（默认值） 降序：DESC

-- 查询所有数据,使用年龄升序排序
-- 排序默认是升序(asc),因此asc也可以不写
select * from student order by age asc;

-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;

6.8 分组

SELECT * FROM 表名 WHERE 条件 GROUP BY 字段名 [HAVING 条件] GROUP BY 分组 HAVING 分组以后得到结果再进行过滤

• having与where的区别

子名	作用
where子句	先过滤掉行上的一些数据，再进行分组操作。(先过滤再分组)
having子句	先分组后得到的结果上再进行过滤的操作。(先分组再过滤)

---   查询年龄大于23岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据  
SELECT sex, COUNT(*) FROM student WHERE age > 23 GROUP BY sex having COUNT(*) >2;

注：where语句的三处需要注意的问题：
（1）where后面是不能再跟where语句的；
（2）在where后面是不可以写聚合函数的；
（3）分组后面不能使用where语句。

6.9 五个聚合函数

SQL中的聚合函数	作用
count	统计个数，如果这一列有NULL，null不会参与统计
max	找这一列中的最大值，一般是数值类型进行操作。
min	找这一列中的最小值
sum	求这一列的总和
avg	求这一列的平均，返回值小数average

SELECT 聚合函数(字段名) FROM 表

-- 查询学生人数总数
select count(id) sum from student;

-- 查询年龄大于24的总数
select count(*) from student where age > 24;

-- 查询数学成绩总分
select sum(math) from student;

-- 查询学生的平均年龄
select avg(age) from student;

-- 查询数学成绩最高的分数
select max(age) from student;

-- 查询数学成绩最低分
select min(math) from student;

6.10 limit语句

• 默认情况下查询所有行，限制查询记录的条数

select * from table LIMIT offset,length
（1）offset：跳过多少条记录，默认是0
（2）length：返回多少条记录

• LIMIT的使用场景：
  分页：比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。
  假设我们一每页显示5条记录的方式来分页。

-- 每页显示3条

-- 第一页：跳过0条，显示3条
select * from student3 limit 0,3;

-- 如果第1个参数是0，可以省略
select * from student3 limit 3;

-- 第二页：跳过3条，显示3条
select * from student3 limit 3,3;

-- 第三页：跳过6条，显示3条(如果没有这么多记录，有多少条显示多少条)
select * from student3 limit 6,3;

-- 公式: select * from student limit (当前页-1)*页大小,页大小

七、DCL数据库控制语言

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

7.1 创建用户

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

注：用户名、主机名和密码都应该加上单引号

• 关键字说明

关键字	说明
‘用户名’	将要创建的用户名
‘主机名’	指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
‘密码’	该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

（1） 案例：创建zhangsan用户，只能在localhost这个服务器登录mysql服务器，密码为123456

create user 'zhangsan'@'localhost' identified by '123456';

flush privileges;   -- 刷新权限

（2）案例：创建lisi用户可以在任何电脑上登录mysql服务器，密码为admin

create user 'list'@'%' identified by 'admin';

7.2 给用户授权

用户创建之后，没什么权限，所以需要给用户授权

GRANT 权限1, 权限2… ON 数据库名.表名 TO ‘用户名’@‘主机名’;

• 关键字说明：

关键字	说明
GRANT…ON…TO	授权关键字
权限	授予用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、DELETE等，如果要授予所有的权限则使用ALL。
数据库名.表名	该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
‘用户名’@‘主机名’	给哪个用户授权，要加上单引号。与创建用户时的用户名和主机名要相同。

（1）案例：给zhangsan用户分配对test这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询

-- 给zhangsan授权
grant create,alter,insert,update,select on db03.* to 'zhangsan'@'localhost';

登录zhangsan用户,执行增删操作

从上图可看出zhangsan用户可以执行创表和插入数据的操作，而执行delete语句时报错，这是权限不足所致的错误

（2）案例：给lisi用户分配所有权限，对所有数据库的所有表

grant all on *.* to 'lisi'@'%';

7.3 撤销授权

REVOKE 权限1, 权限2… ON 数据库.表名 FROM ‘用户名’@‘主机名’;

• 关键字说明：

关键字	说明
REVOKE…ON…FROM	撤销授权的关键字
权限	用户的权限，如CREATE、ALTER、SELECT、INSERT、UPDATE、SELECT等，所有的权限则使用ALL
数据库名.表名	对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用*表示，如*.*
‘用户名’@‘主机名’	给哪个用户撤销，要加上单引号。与创建用户时的用户名和主机名要相同。

（1）案例：撤销zhangsan用户对db03数据库的修改和查询权限

revoke update on db03.* from 'zhangsan'@'localhost';

revoke select on db03.* from 'zhangsan'@'localhost';

注：用户名和主机名要与创建时相同，各自要加上但引号

回收之后，需要重启客户端才能刷新权限；

7.4 查看用户权限

show grants for ‘用户名’@‘主机名’;

查看zhangsan的权限

分析：可看出zhangsan用户只有create，alter，insert这三个功能权限，而update和select权限已经被收回了。

7.5 删除用户

DROP USER ‘用户名’@‘主机名’;

• 案例：删除list用户

drop user 'list'@'%';

再次使用list账号登录：

7.6 修改管理员密码

mysqladmin -uroot -p password 新密码

注：在mysql/bin文件夹下可执行文件，不用登录，但要指定管理员的密码

例子：

回车后要输出原密码，才能更改成功。如果原密码不正确，则修改失败。