MySQL基础总结(DDL、DML、DQL、DCL)

什么是SQL

Structured Query Language：结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 3种注释
   1. 单行注释：
      -- 注释内容
# 注释内容(mysql特有)
   2. 多行注释：
      /* 注释 */

SQL分类

1. DDL(Data Definition Language)数据定义语言
   用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等
2. DML(Data Manipulation Language)数据操作语言
   用来对数据库中表的数据进行增删改。关键字：insert，delete，update等
3. DQL(bata Query Language)数据查询语言
   用来查询数据库中表的记录(数据)。关键字：select，where等
4. DCL(Data control Language)数据控制语言(了解)
   用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

DDL(Data Definition Language)数据定义语言

操作数据库：CRUD

1. C（Create）：创建
   1. 创建数据库
      create database 数据库名称;
   2. 创建数据库，判断是否存在，再创建
      create database if not exists 数据库名称;
   3. 创建数据库，并指定字符集
      create database 数据库名称 character set 字符集名;
   4. 创建数据库，判断是否存在，并指定字符集
      create database if not exists 数据库名称 character set 字符集名;
2. R（Retrieve）：查询
   1. 查询所有数据库的名称
      show databases;
   2. 查询某个数据库的字符集
      show create database 数据库名称;
3. U（Update）：更新（修改）
   1. 修改数据库的字符集
      alter database 数据库名称 character set 字符集名称;
4. D（Delete）：删除
   1. 删除数据库
      drop database 数据库名称;
   2. 判断数据库是否存在，再删除
      drop database if exists 数据库名称;
5. 使用数据库
   1. 使用其他数据库
      use 数据库名称;
   2. 查询当前正在使用的数据库名称
      select database();

例子

-- 创建数据库

-- 如果该数据库存在会报错
CREATE DATABASE db1;

# 因此就推出了判断数据库存在再创建
CREATE DATABASE if NOT EXISTS db1;

-- 如果不指定字符集是默认utf-8
CREATE DATABASE db2 CHARACTER set gbk;

-- 综合运用。先判断该数据库是否存在，再指定字符集
create database if not exists db3 character set gbk;

-- 查询所有数据库的名称
show DATABASES;

-- 查询某个数据库的字符集
show create database db1;

-- 修改数据库的字符集
alter database db1 CHARACTER set utf8;

-- 删除数据库跑路
drop database db3;

-- 删除之前。判断是否存在该数据库
drop database if EXISTS db2;

-- 使用其他数据库
use db1;

-- 查询当前真正使用的数据库名称
select database();

操作表：CRUD

1. C（Create）：创建
   1. 语法：

create table 表名{
	列名1 数据类型1, 
	列名2 数据类型2,
	...
	列名n 数据类型n
}

    2. 数据库常用数据类型：

书写规则	解释	样例
int	整数类型	列名 int
double	小数类型	列名 double(小数共多少位包含小数点，小数点后保留多少位)
date	日期(格式：YYYY-MM-DD)	列名 date
datetime	日期(格式：YYYY-MM-DD HH:MM:SS)	列名 datetime
timestamp	时间戳类型(格式：YYYY-MM-DD HH:MM:SS)	列名 timestamp
varchar	字符串	列名 varchar(20)：字符串最大20个字符

    3. 创建表：

create table student(
	id int,
	name varchar(32),
	age int,
	score double(5,2),
	birthday date,
	times timestamp
);

    4. 复制表：
    create table 表名 like 被复制的表名;

2. R（Retrieve）：查询
   1. 查询某个数据库中所有的表名称
      show tables;
   2. 查询表结构
      desc 表名;
3. U（Update）：更新（修改）
   1. 修改表名
      alter table 表名 rename to 新的表名;
   2. 修改表的字符集
      alter table 表名 character set 字符集名称;
   3. 添加一列
      alter table 表名 add 列名 数据类型
   4. 修改列名称及数据类型
      alter table 表名 change 列名 新列名 新数据类型
   5. 只修改数据类型
      alter table 表名 modify 列名 新数据类型
4. D（Delete）：删除
   1. 删除表
      drop table 表名;
   2. 判断是否存在表，再删除表
      drop table if exists 表名;
   3. 删除列
      alter table 表名 drop 列名;

例子

use db1;
-- 创建表.
-- 这里演示不同的数据类型而已
create table student (
	id int,
	name varchar(32),
	age int,
	score double(5,2),
	birthday date,
	times timestamp
);

-- 复制表
-- 复制表的格式。但不复制数据
create table copystudent like student;

-- 查询该数据库所有表名称
show TABLES;

-- 查询表结构(如：数据类型，主键，外键，是否能为空值)
desc student;

-- 修改表名
alter table student rename to newstudent;

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

-- 添加一列
alter table newstudent add mathscore int;

-- 修改列名称及数据类型
alter table newstudent change mathscore newmathscore double(5,2);

-- 只修改数据类型
alter table newstudent modify newmathscore int;

-- 删除表（如果不存在会报错）
drop table newstudent;

-- 删除表之前判断是否存在
drop table if exists newstudent;

-- 删除表中一列
alter table copystudent drop times;

DML(Data Manipulation Language)数据操作语言

1. 添加数据
   1. 语法
      insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
   2. 注意事项
      1. 列名对应的数据类型和值一一对应。
      2. 如果定义了表名后，不定义列名，则默认给所有列添加值。
         insert into 表名 values(值1,值2,...,值n);
      3. 除了数字类型，其他类型需要使用引号(单双都可)引起来。
2. 删除数据
   1. 语法
      delete from 表名 [where 条件];
   2. 注意事项
      1. 如果不加条件，则删除表中所有记录
      2. 如果要删除所有记录
         1. delete from 表名;
         2. truncate table 表名;
         3. 两者区别，建议使用truncate已经效率高，他是直接删掉整张表，再创建这样一个空表。而delete效率很低，你表有多少个数据，底层执行了多少次删除操作。
3. 修改数据
   1. 语法
      update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件]
   2. 注意事项
      1. 如果不加任何条件，则会将表中所有记录修改

例子

-- 添加一条数据
insert into copystudent values(2,'张三',18,95,'2020-8-28');
insert into copystudent values(3,'李四',19,92,'2020-8-29');

-- 删除所有数据(其实执行了删除这么多行语句)
delete from copystudent;

-- 删除所有数据(效率高，直接删除整张表，再创建一个空表)
truncate table copystudent;

-- 修改某一个列名下所有数据
-- 一般和where语句结合使用，因为不加where会修改所有数据
update copystudent set id = 5;

DQL(bata Query Language)数据查询语言

1. 语法

select 
	字段列表
from
	表名列表
where
	表名列表
group by
	分组列表
having
	分组之后的条件列表
order by
	排序
limit
	分页限定

2. 基础查询

   1. 多个字段的查询
      select 字段名1,字段名2,... from 表名;
      注意事项：
      1. 如果想要查询所有字段，则可以用*来代替字段列表
   2. 去除重复
      select distinct 字段名1,字段名2,... from 表名;
   3. 计算列
      一般可以使用四则运算计算一些列的值。（仅限于数值型的计算）
      我们计算的时候有时候会出现数值为null的情况，这个时候四则运算会等于null。因此提供了ifnull表达式。
      ifnull(表达式1,表达式2)
      1. 表达式1 判断哪个字段需要判断是否为null
      2. 表达式2 如果该字段判断为null，就将null替换为表达式2的值
   4. 起别名
      1. 中括号里面的别名根据需求填写即可。
         select 字段名1 [取别名],字段名2 [取别名],... from 表名;
      2. 加上as关键字
         select 字段名1 as [取别名],字段名2 as [取别名],... from 表名;

3. 条件查询

   1. 比较运算符（>、<、<=、>=、=、!= ( <> )）
   2. between…and
   3. in（集合）
   4. like（模糊查询）
      占位符：
      1. _ ：匹配单个任意字符 [ 1 ]
      2. %：匹配多个任意字符 [0 , n]
   5. is null
   6. and 或 &&
   7. or 或 ||
   8. not 或 |

4. 排序查询

   1. 语法:order by子句
      order by 排序字段1 排序方式1，排序字段2 排序方式2...
   2. 排序方式:
      1. ASC： 升序，默认的。
      2. DESC： 降序。
   3. 注意事项:
      1. 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

5. 聚合函数

   1. 概述：将一列数据当做一个整体，进行纵向计算
   2. 语法：
      select 聚合函数( 列 ) from 表名 [where条件];
   3. 一般有下列几种常用计算
      1. count：计算个数
         1. 一般选择非空的列：主键
         2. count(*)
      2. max：求某一列最大值
      3. min：求某一列最小值
      4. sum：求某一列和
      5. avg： 求某一列平均值
   4. 注意事项
      1. 聚合函数的计算，会排除null值。
      2. 因此一般聚合函数结合ifnull进行综合使用或者不使用有空的某一列进行聚合计算。

6. 分组查询

   1. 语法:
      select 聚合函数( 列 ) 或者 分组字段 from 表名 [where条件] group by 列 [having条件];
   2. 注意事项:
      1. 分组之后查询的字段：分组字段、聚合函数
   3. where 和 having 的区别
      1. where 在分组之前进行限定，如果不满足条件，则不参与分组。
      2. having在分组之后进行限定，如果不满足结果，则不会被查询出来
      3. where后不可以跟聚合函数，having 可以进行聚合函数的判断。

7. 分页查询

   1. 语法：
      limit 开始的索引,每页查询的条数
   2. 公式：
      开始的索引 = (当前的页码-1) * 每页显示的条数

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

    3. 分页操作是一个MySQL "方言”

例子

-- 创建表的格式
create table student (

	id int,								-- 编号
	name varchar(20),					-- 姓名
	age int,							-- 年龄
	sex VARCHAR(5),						-- 性别
	address VARCHAR(100),				-- 地址
	math int,							-- 数学
	english int							-- 英语

);

-- 插入一定的数据。后面才好做查询练习
insert into student VALUES(1,'马云',55,'男','杭州',66,78);
insert into student VALUES(2,'马化腾',45,'女','深圳',98,87);
insert into student VALUES(3,'马景涛',55,'男','香港',56,77);
insert into student VALUES(4,'柳岩',20,'女','湖南',76,65);
insert into student VALUES(5,'柳青',20,'男','湖南',86,null);
insert into student VALUES(6,'刘德华',57,'男','香港',99,99);
insert into student VALUES(7,'马德',22,'女','香港',99,99);
insert into student VALUES(8,'德玛西亚',18,'男','南京',56,65);


-- 基础查询
-- 		1. 多个字段的查询
SELECT * from student;

-- 		2. 查询的时候去除重复值(默认所有列都要一一对应)
SELECT DISTINCT * from student;

-- 		3. 对列里面的数据进行四则运算
--        	会发现，English有一项存在null值。最后相加之和也是null。我们就需要ifnull判断，然后给一个替换值
--  		默认列名是查询时的书写情况，如果想要重命名加 as 或者 不写。即可
SELECT math,english,math + english from student;
SELECT math,english,math + ifnull(english,0) from student;
SELECT math,english,math + ifnull(english,0) as 总成绩 from student;



-- 条件查询
--		 1. 查询年龄大于20岁的所有信息
SELECT * from student where age > 20;

--		 2. 查询年龄等于20岁的所有信息
SELECT * from student where age = 20;

-- 		 3. 查询年龄不等于20岁的所有信息
SELECT * from student where age != 20;
SELECT * from student where age <> 20;

-- 		 4. 查询年龄大于等于20 小于等于30的所有信息
SELECT * from student where age >= 20 && age <= 30;
SELECT * from student where age >= 20 and age <= 30;
SELECT * from student where age BETWEEN 20 and 30;

-- 		 5. 查询年龄22岁，18岁，25岁的所有信息
SELECT * from student where age = 22 || age = 18 || age = 25;
SELECT * from student where age = 22 or age = 18 or age = 25;
SELECT * from student where age in(22,18,25);

--		 6. 查询英语成绩为null(注意：null不能使用 =、!=、<>进行判断)
SELECT * from student where english is null;

--   	 7. 查询英语成绩不为null
SELECT * from student where english is not null;


-- 模糊查询（like）
-- 		 1. 查询姓马的有哪些 
SELECT * from student where name like '马%';

--		 2. 查询第二个字是化的人
SELECT * from student where name like '_化%';

--		 3. 查询姓名是三个字的人
SELECT * from student where name like '___';

--		 4. 查询姓名中包含德的人
SELECT * from student where name like '%德%';


-- 排序查询
--		 1. 按数学成绩进行升序排序
select * from student order by math ;
select * from student order by math asc;

--     2. 按数学成绩进行降序排序，如果成绩一样，则将英语成绩进行降序
select * from student order by math desc , english desc;


-- 聚合函数
--		 1. 查询有多少个人
SELECT count(id) from student;

-- 		 2. 查询数学成绩最高分
SELECT max(math) from student;

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

-- 		 3. 查询数学成绩总分和
SELECT sum(math) from student;

-- 		 4. 查询数学成绩平均值
SELECT avg(math) from student;

--		 5. 查询英语成绩平均值
--		    如果不做任何步骤就会排除null值。对结果会有一定影响
SELECT avg(english) from student;
SELECT avg(ifnull(english,0)) from student;

-- 分组查询
--		 1. 根据性别进行分组
select sex from student group by sex;

--		 2. 根据性别进行分组,查询该性别数学总成绩
select sex,sum(math) from student group by sex;

--		 3. 根据性别进行分组,查询该性别数学总成绩,总人数
select sex,sum(math),count(*) from student group by sex;

--		 4. 根据性别进行分组,查询该性别数学总成绩,总人数,筛选数学成绩大于70分进行求和计算
select sex,sum(math),count(*) from student where math > 70 group by sex;

--		 5. 根据性别进行分组,查询该性别数学总成绩,总人数,筛选数学成绩大于70分进行求和计算
select sex,sum(math),count(*) from student where math > 70 group by sex;

--		 6. 根据性别进行分组,查询该性别数学总成绩,总人数,筛选数学成绩大于70分进行求和计算且人数大于2
select sex,sum(math) as 数学总成绩,count(*) as people from student where math > 70 group by sex having people > 2;


-- 分页查询
-- 		 1. 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 1,4; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

DCL(Data control Language)数据控制语言

管理用户

1. 添加用户
   create user '用户名'@'主机名' IDENTIFIED by '密码;
2. 删除用户
   drop user '用户名'@'主机名';
3. 修改用户密码
   alter user '用户名'@'主机名' identified by '新密码';
4. 查询用户
   1. 切换到mysql数据库
      use mysql;
   2. 查询user表中数据即可
      select * from mysql;

例子

use mysql;
select * from user;

-- 添加用户
-- %：通配符。 代表：任意主机都可登陆。密码是123
create user '张三'@'%' IDENTIFIED by '123';

-- 删除用户
drop user '张三'@'%';

-- 修改用户密码
/*
	从8.0版本开始。修改密码的方式改变了。操作如下
*/
alter user '张三'@'%' identified by '456';

授权管理

1. 查询权限
   show grants for '用户名'@'主机名';
2. 授予权限
   grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
3. 撤销权限
   revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

例子

use mysql;
select * from user;

-- 查询某个用户权限
/*
	 创建的新用户。默认只有登陆权限。其余的啥也没有
*/
show grants for 'root'@'localhost';
show grants for '张三'@'%';

-- 授予权限
grant select,update on db1.student to '张三'@'%';

-- 撤销权限
--   注意观察。程序员也是非常细心的。
--	 授予权限使用to。撤销权限使用from
revoke update on db1.student from '张三'@'%';