MySQL-基础语法DDL、DML、DQL、DCL

DDL：

• DDL(Data Definition Language)数据库定义语言
• 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

语法

DML语句操作	关键字
添加	INSERT INTO 表名 (字段名) VALUES (值);
修改	UPDATE 表名 SET 字段名=新的值;
删除	DELETE FROM 表名;
查询	SELECT * FROM 表名;

含义	SQL语句
查询所有的数据库	SHOW DATABASES;
查询当前正在使用的数据库	SELECT DATABASE();
使用数据库	USE 数据库名;
查询某个数据库的定义信息	SHOW CREATE DATABASE 数据库名;
创建数据库	CREATE DATABASE 数据库名;
判断是否存在并创建数据库	CREATE DATABASE IF NOT EXISTS 数据库名;
创建数据库并指定字符集(编码表)	CREATE DATABASE 数据库名 DEFAULT CHARACTER SET 编码;
判断数据库不存在再创建数据库并指定字符集	CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 编码;
修改数据库字符集	ALTER DATABASE 数据库名DEFAULT CHARACTER SET 新字符集;
删除数据库	DROP DATABASE 数据库名;
如果数据库存在再删除数据库	DROP DATABASE IF EXISTS 数据库名;

演示：

-- 查询所有的数据库
SHOW DATABASES;

-- 查询当前正在使用的数据库
SELECT DATABASE();

-- 切换/使用数据库
USE db1;

-- 查询数据库的定义信息
SHOW CREATE DATABASE mysql;

-- 创建数据库
CREATE DATABASE db1;

-- 判断是否存在并创建数据库
CREATE DATABASE IF NOT EXISTS db2;

-- 创建数据库并指定字符集(不指定的话默认的是UTF-8)
CREATE DATABASE db3 CHARACTER SET utf8;

-- 判断数据库不存在再创建数据库并指定字符集	
CREATE DATABASE IF NOT EXISTS db3 CHARACTER SET utf8;

-- 修改数据库字符集
ALTER DATABASE db1 CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE db3;

-- 如果数据库存在再删除数据库
DROP DATABASE IF EXISTS db2;

操作表：

含义	SQL语句
查看数据库中的所有的表	SHOW TABLES;
查看表结构	DESC 表名;
查看创建表的SQL语句	SHOW CREATE TABLE 表名;
查看数据表信息	SHOW TABLE STATUS FROM 数据库 LIKE 表名;
创建表	CREATE TABLE 表名(列名 数据类型 约束,列名 数据类型 约束);
快速创建一个表结构相同的表	CREATE TABLE 表名 LIKE 其他表;
直接删除表	DROP TABLE 表名;
判断表是否存在并删除表	DROP TABLE IF EXISTS 表名;
添加表一列	ALTER TABLE 表名 ADD 字段名 字段类型;
修改列类型	ALTER TABLE 表名 MODIFY 字段名 新类型;
修改列名	ALTER TABLE 表名 CHANGE 老字段名 新字段名 类型;
删除列	ALTER TABLE 表名 DROP 字段名;
修改表名	RENAME TABLE 表名 TO 新表名;
修改表名	ALTER TABLE 表名 RENAME TO 新表名;
修改表的字符集	ALTER TABLE 表名 DEFAULT CHARACTER SET 新字符集;

演示：

USE db1;

-- 查看数据库中的所有的表
SHOW TABLES;

-- 查看表结构
DESC Student;

-- 查看创建表的SQL语句	
SHOW CREATE TABLE Student;

-- 查看数据表信息
SHOW TABLE STATUS FROM db1 LIKE 'Student';

-- 创建数据表
CREATE TABLE Student(
	id INT,
	name VARCHAR(20),
	age TINYINT,
	gender VARCHAR(1),
	gold FLOAT,
	address VARCHAR(50)
);

-- 判断是否存在并创建数据表
CREATE TABLE IF NOT EXISTS Student(
	id INT,
	name VARCHAR(20),
	age TINYINT,
	gender VARCHAR(1),
	gold FLOAT,
	address VARCHAR(50)
);

-- 快速创建一个表结构相同的表
CREATE TABLE Student2 LIKE Student;

-- 删除数据表
DROP TABLE Student2;

-- 判断表是否存在并删除表
DROP TABLE IF EXISTS Student2;

-- 添加表中的列
ALTER TABLE Student ADD list VARCHAR(20);

-- 修改列的类型
ALTER TABLE Student MODIFY list INT;

-- 修改列名	
ALTER TABLE Student CHANGE list Newlist VARCHAR(20);

-- 删除列，Mysql没有直接的语法判断列是否存在，可以通过存储过程完成
ALTER TABLE Student DROP Newlist;

-- 修改表名
RENAME TABLE Student To NewStudent;

-- 修改表的字符集
ALTER TABLE NewStudent DEFAULT CHARACTER SET utf8;

DML：

• DML(Data Manipulation Language)数据操作语言
• 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

含义	SQL语句
给全部列添加数据	INSERT INTO 表名 VALUES (值1, 值2, 值3, …);
给指定列添加数据	INSERT INTO 表名 (字段名1, 字段名2, 字段名3, …) VALUES (值1, 值2, 值3, …);
不带条件修改数据	UPDATE 表名 SET 字段名=值;
带条件修改数据	UPDATE 表名 SET 字段名=值 WHERE 字段名=值;
不带条件删除数据	DELETE FROM 表名;
带条件删除数据	DELETE FROM 表名 WHERE 字段名=值;
truncate删除表记录	TRUNCATE TABLE 表名;

演示：

• 列名和值的数量以及数据类型要对应，除了数字类其他数据类型的数据都需要加单引号
• 修改语句中必须加条件，如果不加条件会把所有的数据都修改/删除

-- 给全部列添加数据
INSERT INTO Student VALUES(0,'韩信',99,'男',10211,'打野');

-- 给指定列添加数据
INSERT INTO Student (id,name,age,gender,gold,address) VALUES (1,'李白',89,'男',9999,'打野');
INSERT INTO Student (id,name) VALUES (2,'露娜');

-- 给全部列批量添加数据
INSERT INTO Student VALUES 
(3,'宫本武藏',79,'男',89982,'打野'),
(4,'娜可露露',95,'女',12312,'打野'),
(5,'孙悟空',123,'男',62142,'打野'),
(6,'不知火舞',61,'女',21316,'中路'),
(7,'干将莫邪',43,'男',12312,'中路'),
(8,'貂蝉',21,'女',72622,'中路'),
(9,'姜子牙',99,'男',67811,'中路'),
(10,'李信',12,'男',7262,'上单'),
(11,'花木兰',18,'女',876123,'上单'),
(12,'杨戬',99,'男',89123,'上单'),
(13,'关羽',88,'男',31227,'上单'),
(13,'百里守约',72,'男',23123,'射手'),
(13,'马可波罗',102,'男',55645,'射手'),
(13,'孙尚香',56,'女',34534,'射手'),
(13,'公孙离',77,'女',323217,'射手');


-- 不带条件修改数据
UPDATE Student SET address = '王者峡谷';

-- 带条件修改数据
UPDATE Student SET address = '王者峡谷' WHERE age = 19;
UPDATE Student SET address = '王者峡谷' WHERE name = '李白' or name = '露娜';

-- 不带条件删除表中所有数据
DELETE FROM Student;

-- 带条件删除数据
DELETE FROM Student WHERE name = '李白';

DQL：

• (Data Query Language)数据查询语言
• 用来查询数据库中表的记录(数据)。关键字：select, where 等

DQL的编写顺序：

SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY > LIMIT

DQL的执行顺序：

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT

单表数据查询语法：

• SELECT 字段列表
• FROM 表名列表
• WHERE 条件列表
• GROUP BY 分组字段列表
• HAVING 分组后的过滤条件
• ORDER BY 排序
• LIMIT 分页

基础查询：

SQL语句	含义
SELECT * FROM 表名;	查询全部表数据
SELECT 列名1,列名2,…FROM 表名;	查询指定字段的表数据
SELECT DISTINCT 列名1,列名2,…FROM 表名;	去除重复查询
SELECT 列名1 运算符(+ — * /) 列名2 FROM 表名;	计算列的值（四则运算）
SELECT IFNULL 列名(要替换的列,要替换的值)FROM 表名;	查询时进行null的判断
SELECT 列名 AS 别名 FROM 表名;	起别名查询

演示：

-- 查询全部表数据
SELECT * FROM Student;

-- 查询指定字段的表数据
SELECT id,name,age FROM Student;

-- 去除重复查询
SELECT DISTINCT address FROM Student;

-- 起别名查询，可省略AS
SELECT address AS '位置' FROM Student;
SELECT address '位置' FROM Student;

条件查询：

语法：

SELECT 列名列表 FROM 表名 WHERE 条件;

条件：

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN … AND …	在某个范围之内(都包含)
IN(…)	多选一
LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 `
NOT 或 ! 非	不是

演示：

-- 查询年龄大于80的用户
SELECT * FROM Student WHERE age > 80;

-- 查询年龄等于19且姓名是韩信的用户
SELECT * FROM Student WHERE age = 19 AND name = '韩信';

-- 查询年龄在20和80之间的用户信息     AND &&  BETWEEN都可以
SELECT * FROM Student WHERE age >= 20 AND age <= 80;
SELECT * FROM Student WHERE age >= 20 && age <= 80;
-- 注意BETWEEN后跟的是最小值，AND后面是最大值，不能弄反
SELECT * FROM Student WHERE age BETWEEN 20 AND 80;

-- 查询id是1，2，3的用户
SELECT * FROM Student WHERE id = 1 OR id = 2 OR id =3;
SELECT * FROM Student WHERE id = 1 || id = 2 || id =3;
SELECT * FROM Student WHERE id IN(1,2,3);

-- 查询age是null的用户
SELECT * FROM Student WHERE age IS NULL;

-- 查询age不为null的用户
SELECT * FROM Student WHERE age IS NOT NULL;

-- 查询年龄不等于19的	!= 和 <>都可以
SELECT * FROM Student WHERE age != 19;
SELECT * FROM Student WHERE age <> 19;

-- 查询姓韩的用户  %：匹配多个任意字符
SELECT * FROM Student WHERE name LIKE '韩%';

-- 查询名字最后一个是信的
SELECT * FROM Student WHERE name LIKE '%信';

-- 查询名字有信的用户  _：匹配一个任意字符
SELECT * FROM Student WHERE name LIKE '_信';
SELECT * FROM Student WHERE name LIKE '%信%';

-- 查询名字是两个的用户
SELECT * FROM Student WHERE name LIKE '__';

聚合函数查询：

聚合函数：将一列数据作为一个整体，进行纵向的计算，null值不参与聚合函数的运算

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

-- 计算总记录条数
SELECT COUNT(*)  FROM student;

-- 获取最大年龄
SELECT MAX(age) FROM student;

-- 获取最小年龄
SELECT MIN(age) FROM student;

-- 获取所有年龄和
SELECT SUM(age) FROM student;

-- 获取地址为王者峡谷用户年龄和
SELECT SUM(age) FROM student WHERE address = '打野';

-- 获取地址为王者峡谷用户平均年龄
SELECT AVG(age) FROM student WHERE address = '打野';

分组查询：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段无意义。

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
SELECT后面跟分组字段名和GROUP BY后面的要一样

where和having区别：

执行时机不同：where是分组查询之前进行过滤，不满足where条件的不参与分组，having是分组后对结果的过滤。

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

执行顺序：

where > 聚合函数 > having

演示：

-- 根据性别分组，统计男女英雄数量
SELECT gender, COUNT(*) FROM Student GROUP BY gender;

-- 根据性别分组，统计男女英雄平均年龄
SELECT gender, AVG(age) FROM Student GROUP BY gender;

-- 查询年龄小于60的英雄，并根据位置分组，获取英雄数量大于2的位置
SELECT address, COUNT(*) FROM Student WHERE age <= 60 GROUP BY address HAVING COUNT(*) >= 2;

排序查询：

有多个排序条件时，当前边的条件值一样时，才会判断第二个条件

排序方式：

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

含义	SQL语句
排序查询	SELECT 列名 FROM 表名 WHERE 条件 ORDER BY 列名1 排序方式1,列名2 排序方式2;

演示：

-- 根据年龄升序排序，ASC是默认的排序方式，可以省略不写
SELECT * FROM Student ORDER BY age ASC;
SELECT * FROM Student ORDER BY age;

-- 根据年龄降序排序
SELECT * FROM Student ORDER BY age DESC;

-- 查询名字带信的用户，根据年龄升序排序
SELECT * FROM Student WHERE name LIKE '%信%' ORDER BY age ASC;

-- 按照年龄升序排序，如果年龄相同按照id升序排序 
SELECT * FROM Student ORDER BY age ASC, id ASC;

-- 按照地址分组，获取每组的年龄和
SELECT address,SUM(age) FROM Student GROUP BY address;

-- 年龄大于60的进行分组，按照地区分组，获取每组的年龄和
SELECT address,SUM(age) FROM Student WHERE age > 60 GROUP BY address;

-- 年龄大于60的进行分组，按照地区分组，获取每组的总年龄，只显示总年龄大于200的
SELECT address,SUM(age) AS getSum FROM Student WHERE age > 60 GROUP BY address HAVING getSum > 200;

-- 年龄大于60的进行分组，按照地区分组，获取每组的总年龄，只显示总年龄大于200的，并按照总年龄降序排序
SELECT address,SUM(age) AS getSum FROM Student WHERE age > 60 GROUP BY address HAVING getSum > 200 ORDER BY getSum DESC;

分页查询：

数据多的时候使用，就像百度去搜东西一样分页显示，LIMIT 当前页数,每页显示的条数

含义	SQL语句
	SELECT 列名 FROM 表名 LIMIT LIMIT 当前页数,每页显示多少行数据
起始索引	起始索引从0开始，当前页 = (当前页数-1) * 每页显示的条数，如果查询第一页的数据，可以省略当前页数

演示：

-- 查询第一页，显示10条数据，当前页可省略
SELECT * FROM Student LIMIT 0,10;
SELECT * FROM Student LIMIT 10;

-- 查询第二页显示10条数据
SELECT * FROM student LIMIT 10,10;

DCL：

DCL（数据库控制语言）是用来管理数据库用户、控制数据库访问权限的。

管理用户：

查询用户

USE mysql; -- 切换数据库
SELECT * FROM user; -- 查看mysql数据库所有用户

创建用户，默认无权限

-- 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

-- 创建用户，只能在本机访问数据库
CREATE USER 'itzhuzhu'@'localhost' IDENTIFIED BY '123456';

-- 登录主机为%表示可通过任意主机登录
CREATE USER 'itzhuzhu2'@'%' IDENTIFIED BY '123456';

查看权限

--语法：SHOW GRANTS FOR '用户名'@'主机名';

SHOW GRANTS FOR 'itzhuzhu'@'localhost';

授权用户，多个权限使用逗号分割

权限	说明
ALL,ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

-- 语法：GRANT 权限1,权限2,权限N ON 数据库名.表名 TO '用户名'@'主机名';

-- 给用户授权
GRANT SELECT ON mysql.db TO 'itzhuzhu'@'localhost';

-- 给用户授权mysql数据库的全部表权限
GRANT SELECT ON mysql.* TO 'itzhuzhu'@'localhost';

-- 给用户授权全部数据库全部表的SELECT权限
GRANT SELECT ON *.* TO 'itzhuzhu'@'localhost';

取消授权

-- 语法：REVOKE 权限1,权限2,权限N ON 数据库名.表名 FROM '用户名'@'主机名';

REVOKE SELECT ON mysql.* FROM 'itzhuzhu'@'localhost';
REVOKE SELECT ON *.* FROM 'itzhuzhu'@'localhost';

修改用户密码

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

ALTER USER 'itzhuzhu'@'localhost' IDENTIFIED WITH mysql_native_password BY '666666';

删除用户

-- 语法：DROP USER '用户名'@'主机名';

DROP USER 'itzhuzhu'@'localhost';

-- 判断是否存在再删除
DROP USER IF EXISTS 'itzhuzhu'@'localhost';