DDL

DDL-数据库操作

查询

查询所有数据库
show databases;
查询当前数据库
select database();

创建

create database[IF NOT EXISIT]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];

create database itcase;
create database itheima dafault charset utf8mb4;

注意点：

-UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集
dafault charset utf8mb4 表示utf8mb4字符集

删除

drop database[IF EXISTS] 数据库名;

drop database itcase

使用

use 数据库名；

use itcase;

DDL-表操作-查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

DDL-表操作-创建

create table 表名(
字段1 字段1类型[comment 字段1注释],
字段2 字段2类型[comment 字段2注释],
字段3 字段3类型[comment 字段3注释],
.......
字段n 字段n类型[comment 字段n注释]
)[comment 表注释]

注意：[....]为可选参数，最后一个字段后面没有逗号

举例：

create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
）comment '用户表';

DDL-表操作-数据类型

MySQL中数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数据类型视频

DDL-表操作-修改

添加字段

alter table 表名 add 字段名类型（长度）[commment 注释][约束];

案例：为emp表增加一个新的字段“昵称”为nickname，类型为varchar（20）
alter table emp add nickname varchar(20) comment '昵称'；

修改数据类型

alter table 表名 modify 字段名新数据类型（长度）;

修改字段名和字段类型

alter table 表名 change 旧字段名新字段名类型（长度）[commment 注释][约束];

案例：将emp表的nickname字段修改为username，类型修改为varchar（30）。
alter table emp change nickname username varchar(30) comment '用户名';

删除字段

alter table 表名 dorp 字段名;

案例：将emp表的字段username删除
alter table emp dorp username;

修改表名

alter table 表名 rename 新表名;

案例：将emp表的表名修改为employee
alter table emp rename employee;

DDL-表操作-删除

删除表

drop table[if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;

注意：在删除表时，表中的全部数据也会被删除

DML

DML-介绍

DML英文全称是Data Manipulation Language（数据操纵语言），用来对数据库中表的数据记录进行增删改操作

添加数据（insert）
修改数据（updata）
删除数据（delete）

DML-添加数据

给指定字段添加数据

insert into 表名（字段名1，字段名2，....） values(值1，值2，.....);

给全部字段添加数据

insert into 表名 values(值1，值2，......);

批量添加数据

insert into 表名（字段1，字段2，....）values（值1，值2，....）,(值1，值2，.....);
insert into 表名 values(值1，值2，......),(值1，值2，......),(值1，值2，......);

注意：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

DML-修改数据

updata 表名 set 字段名1=值1,字段名2=值2，......[where 条件];

案例：将employee表的所有员工入职日期（anteydate）修改为2008-01-01
update employee set anteydate='2008-01-01';
案例：修改id为1的数据，将name修改为itheima
update employee set name='itheima' where id=1;

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的数据。

DML-删除数据

delete from 表名 [where 条件];

案例:删除employee表中gender为女的员工
delete from employee where gender='女';
案例：删除所有员工
delete from employee;

注意：

delete语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据
delete语句不能删除某一个字段的值（可以使用update）

DQL

DQL-介绍

DQL英文全称是Data Query Language（数据查询语言），数据查询语言，用来查询数据库中表的记录。
查询关键字：select

DQL-语法

select

字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数

DQL-基本查询

查询多个字段

select 字段1，字段2，字段3....from 表名;
select * from 表名;

设置别名

select 字段1[as 别名1],字段2[as 别名2].....from 表名;
as可以省略

去除重复记录

select distinct 字段列表 from 表名;

DQL-条件查询（where）

语法

select 字段列表 from 表名 where 条件列表;

条件

比较运算符	功能
＞	大于
＞=	大于等于
<	小于
<=	小于等于
=	等于
<>或！=	不等于
between...and...	在某个范围之内（含最小、最大值）
in(...)	在in之后的列表中的值，多选一
like 占位符	模糊匹配（ _ 匹配单个字符，%匹配任意个字符）
is null	是null

逻辑运算符	功能
and或&&	并且（多个条件同时成立）
or或“两竖”	或者（多个条件任意一个成立）
not或！	非，不是

案例：在emp表中查询年龄在15~20岁之间的员工信息
select * from emp where age >= 15 && age <= 20;
select * from emp where age between 15 and 20;

案例：在emp鸟中查询年龄等于18或20或40的员工信息
select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in(18,20,40);

案例：在emp表中查询姓名为两个字的员工信息
select * from emp where name like '__'; (这有两个下划线)

案例：在emp表中查询身份证号最后一位是x的员工信息
select * from emp where idcard like '%x';
select * from emp where idcard like '__________x';(这有17个下划线)

DQL-聚合函数（count、max、min、avg、sum）

介绍

将一列数据作为一个整体，进行纵向计算。

常见聚合函数

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

语法

select 聚合函数（字段列表） from 表名;

案例：统计该企业员工数量
select count(*) from emp;
select count(idcard) from emp;

案例：统计该企业员工的平均年龄
select ave(age) from emp;

案例：统计该企业员工的最大最大年龄
select max(age) from emp;

案例：统计西安地区员工的年龄之和
select sum(age) from emp where workaddress = '西安';

DQL-分组查询（group by）

语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [haivng 分组后过滤条件];

where和having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

案例：根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;

案例：根据性别分组，统计男性和女性的平均年龄
select gender,avg(age) from emp group by gender;

案例：查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workddress,count(*) from emp where age <45 group by workddress having count(*) >= 3;

注意：

执行顺序：where>聚合函数>having。
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

DQL-排序查询（order by）

语法

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

排序方式

asc：升序（默认值）
desc：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

案例：根据年龄对公司的员工进行升序排列
select * from emp order by age asc;

案例：根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排列
select * from emp order by age asc, entrydate desc;

DQL-分页查询（limit）

语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

案例：查询第一页员工数据，每页展示10条记录。
select * from emp limit 10;
select * from emp limit 0,10;

案例：查询第二页员工数据，每页展示10条记录。
select * from emp limit 10,10;

DQL-练习

案例：查询年龄为20,21,22,23岁的女性员工信息
Select * from emp where age in (20,21,22,23) && gender=’女’;

案例：查询性别为男，并且年龄在20~40岁（含）以内的姓名为3个字的员工
select * from emp where sex = ‘男’ and age between 20 and 40 && name like ‘___’;

案例：统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。
select count(*),sex from emp where age <60 group by sex;

案例：查询所有年龄小于等于35岁的员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name , age from emp where age <=35 order by age asc , entrydate desc;

案例：查询性别为男，且年龄在20~40岁（含）以内的前5个员工信息，对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select * from emp where sex=’男’ and age between 20 and 40 order by age asc , entrydate desc limit 5;

DQL-执行顺序

SQL执行顺序.png

DCL

DCL-介绍

DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

查询用户

use mysql;
select * from user;

创建用户

create user '用户名'@'主机名' identified by '密码';

修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

drop user '用户名'@'主机名';

练习

案例：创建用户itcast，只能在当前主机localhost访问，密码为123456；
create user 'itcast'@'localhost' identified by '123456';

案例：创建用户heima，可以在任意主机上访问该数据库，密码为123456；
create user 'heima'@'%' identified by '123456';

案例:修改用户heima的访问密码为1234；
alter user 'heima'@'%' identified with mysql_native_password by '1234';

案例：删除itcast@localhost用户
drop user 'itcast'@'localhost';

DCL-权限控制

MySQL中定义了很多种权限，但是常用的就以下几种

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

查询权限

show grantsn for '用户名'@'主机名';

授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

多个权限之间，使用逗号分隔。
授权时，数据库名和表名可以使用*进行通配，代表所有。

DDL、DML、DQL、DCL笔记

DDL