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';