MySQL数据库基础知识（一）——DDL、DML、DQL、DCL

一、DDL——数据定义语言

DDL（Data Definition Language）语言是对数据库及表进行定义操作的语言。可对数据库进行查询、删除、创建、使用操作，对数据库表进行创建、查询、修改、删除操作。

DDL——对数据库进行操作

1. 查询数据库

SHOW DATABASES -- 查询全部数据库，显示MySQL中全部数据库的名称
SELECT DATABASE() -- 查询当前数据库，显示当前数据库的名称

2.创建数据库

CREATE DATABASE abc -- 创建一个数据库abc
CREATE DATABASE IF NOT EXISTS abcd -- 如果不存在数据库abcd创建一个
CREATE DATABASE IF NOT EXISTS abcde DEFAULT CHARSET utf8mb4 -- 如果不存在数据库abcde创建一个并设置字符集为utf8mb4

3.删除数据库

DROP DATABASE abc -- 删除数据库abc
DROP DATABASE IF EXISTS abcd -- 如果存在abcd就删除
DROP DATABASE IF EXISTS abcde -- 如果存在abcde就删除

4.使用数据库

USE mysql -- 使用该语句后，将会定位到mysql数据库，接下来的操作都将是针对mysql数据库

DDL——对数据库表进行操作

1.查询表

USE mysql  -- 定位到mysql数据库

SHOW TABLES -- 查询当前数据库的所有表，显示当前数据库所有表名

DESC emp -- 通过 desc 表名可以查看当前表的结构（字段名、类型、主键、外键、默认值等）

SHOW CREATE TABLE emp -- 同desc emp 类似，建议动手尝试

2.创建表

-- 通过 create table 表名（字段名 字段类型 ...）可以创建表
-- comment 注释 可以对字段加注释也可以对表加注释
CREATE TABLE emp(
id INT COMMENT '编号',
workno VARCHAR(10) COMMENT '工号',
NAME VARCHAR(10) COMMENT '姓名',
gender CHAR(1) COMMENT '性别',
age TINYINT UNSIGNED COMMENT '年龄',
idcard CHAR(18) COMMENT '身份证号',
workaddress VARCHAR(50) COMMENT '工作地址',
entrydate  DATE COMMENT '入职时间'
)COMMENT '员工表'

3.修改表

 -- 通过 alter table 表名 add 字段名 字段类型 可以为表添加新字段
ALTER TABLE emp ADD nickname VARCHAR(20) COMMENT '昵称'

-- 通过 alter table 表名 modify 字段名 字段类型（长度）
-- 可以修改已创建的表指定字段类型
ALTER TABLE emp MODIFY age INT (10) 

-- 通过 alter table emp change 旧字段名 新字段名 字段类型（长度）
-- 可以修改已创建表的字段名和字段类型
ALTER TABLE emp CHANGE age ages TINYINT(3) 


-- 通过alter table 表名 drop 字段名 可以删除表中已有字段
ALTER TABLE emp DROP workno

-- 通过 alter table 表名 rename to 新表名 可以更改已存在表的名称
ALTER TABLE emp RENAME TO employee

4. 删除表

-- 通过 drop table if exist 表名可以删除已存在的表
DROP TABLE IF EXISTS emp

-- 通过 truncate table 表名 
-- 可以删除当前表并重新创建一个表名相同的表，实际是清除了数据
TRUNCATE TABLE emp 

二、DML ——数据操作语言

DML（Data Manipulation Language）语言是一种对数据库中表数据进行操作的语言，它主要包括增加、修改、删除。

1.对数据库表填加数据

-- 通过 insert into 表名（字段名1，字段名2...）values(值1，值2，...)
--可以对数据库添加一条数据
INSERT INTO emp (id,workno,NAME,gender,age,idcard,workaddress,entrydate) VALUES(1,1,'ll','男',18,123456789987654321,'中国','2022-01-02')

-- 通过 insert into 表名 values(值1，值2，值3...) 
-- 可以添加全部字段的值
INSERT INTO emp VALUES(1,11,'ee','女',19,1234569874561237,'西安','2022-03-04')

-- 通过  insert into 表名(字段名1，字段名2...) values(值1，值2,...),(值1，值2,...),(值1，值2,...)...
-- 可以批量添加数据
INSERT INTO emp (id,workno,NAME,gender,age,idcard,workaddress,entrydate)
VALUES(1,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),(1,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),
(1,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),(2,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),
(3,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),(4,1,'qq','男',16,123456789987654321,'中国','2022-02-03'),

2.修改数据库表中数据

-- 通过 update 表名 set 字段名=值 where 字段名
-- 可以将表中特定的一条数据的某一字段的值修改为目标值
UPDATE emp SET NAME='itheima' WHERE id=2

3.删除数据库表中数据

-- 通过 delete from 表名 where 条件
-- 可以将表中特定的数据删除
DELETE FROM emp WHERE gender='女'

三、DQL——数据查询语言

1.普通查询及简单条件查询

/*
select 字段列表  from 表名 where 条件列表  
group by 分组字段 having 分组条件 
order by 排序字段 (asc 升序，desc降序)
limit 分页参数
*/
SELECT * FROM emp -- 查询emp 表中全部数据

SELECT NAME ,workno,age FROM emp -- 查询emp 中的name 和workno信息

SELECT workaddress AS '工作地址' FROM emp -- 字段名 as '内容' 可以给字段取别名

SELECT DISTINCT workaddress '工作地址' FROM emp -- distinct 不显示重复的字段内容（去重）

/*
between ... and 在该范围内 包含两侧值
in（值1，值2，值3） 多选一，有一个条件满足就显示
like 占位符 分为（_匹配单个字符，%匹配任意字符）
is null 是null
and 和 && 并且表示两个条件都要满足
or 或|| 多个条件任意一个成立
not 或 ！非，不是
*/

SELECT * FROM emp WHERE idcard IS NULL -- 查询出emp表中idcard是null的数据信息

SELECT * FROM emp WHERE idcard IS NOT NULL -- 查询出emp表中idcard不是null的数据信息

-- 查询出age大于等于15小于等于20 的三种方法
SELECT * FROM emp WHERE age>=15 && age<=20

SELECT * FROM emp WHERE age>=15 AND age<=20

SELECT * FROM emp WHERE age BETWEEN 15 AND 20

-- 查询出age=18,age=20,age=40 的数据信息 的两种方法
SELECT * FROM emp WHERE age=18 OR age=20 OR age=40

SELECT * FROM emp WHERE age IN(18,20,40)

-- 模糊查询姓名为两个字符的数据信息
SELECT * FROM emp WHERE NAME LIKE '__'

-- 模糊查询 身份证号以x 结尾的数据信息
SELECT * FROM emp WHERE idcard LIKE '%x'

2.DQL函数应用及复杂条件查询

在进行分组查询时需要注意：

1.group by … having中having后接的是分组后的条件筛选，where后接的是分组前的条件筛选
2.在SQL语句同时有 group by 、order by、limit时，编写顺序是先分组（group by）再排序（order by ）最后分页（limit）

where和having区别：

1.where 不能用聚合函数，而having可以
2.条件筛选时不满足 where 条件不能进行分组
3.两者顺序是先where后having

/*
count 统计数量 min最小值 max最大值 avg 平均值 sum 求和
*/
-- 求在西安工作的人的年龄总和
SELECT SUM(age) FROM emp WHERE workaddress='西安'

-- 按照性别分组，求男女性别的人数分别是多少
SELECT  gender,COUNT(*) FROM emp GROUP BY gender 

-- 按照性别分组，求男女两种性别的平均年龄是多少
SELECT gender,AVG(age) FROM emp GROUP BY gender

-- 按照工作地址分组，查询在同一工作地址上工作人数大于3人并且工作人员年龄小于45
SELECT  workaddress,COUNT(*) FROM emp WHERE age<45 GROUP BY workaddress HAVING COUNT(*)>3

-- 按照年龄进行升序排序
SELECT * FROM emp ORDER BY age ASC

-- 按照入职时间进行降序排序
SELECT * FROM emp ORDER BY entrydate DESC

-- 将emp表中数据进行分页，并显示第二页信息
SELECT * FROM emp LIMIT 1,10

-- 按照年龄升序排序，相同年龄的按照入职时间降序排序
SELECT * FROM emp ORDER BY age ASC ,entrydate DESC

四、DCL——数据控制语言

DCL（Data Command Language）语言主要是用来管理数据库用户，控制数据库使用权限的。

1.用户管理

-- 创建用户itcast 可以在本机使用，密码是123456
CREATE USER 'itcast'@'localhost' IDENTIFIED BY '123456'

-- 创建用户 任意主机都可以访问
CREATE USER 'itheima'@'%' IDENTIFIED BY '123456'

-- 修改密码
ALTER USER 'itheima'@'%' IDENTIFIED WITH mysql_native_password BY '1234'

-- 删除用户
DROP USER 'itheima'@'%'

2.数据库权限控制

/*
all,allprivleges 所有权限   select 查询数据   insert 插入数据
update 修改数据   delete 删除数据 alter修改表权限  drop 删除数据库/表/视图  create 创建数据库/表
*/

-- 查询某一个用户的权限
SHOW GRANTS FOR '用户名'@ '主机名'

-- 给某一个用户添加权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'

-- 撤销某一个用户的权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'