DDL、DML、DCL、DQL
操作语句分类
- DDL 数据定义语言 (Data Definition Language) 例如：建库，建表
- DML 数据操纵语言(Data Manipulation Language) 例如：对表中的数据进行增删改操作
- DQL 数据查询语言(Data Query Language) 例如：对数据进行查询
- DCL 数据控制语言(Data Control Language) 例如：对用户的权限进行设置
常见表常用数据类型
数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。有一些数据是要存储为数字的，数字当中有些是要存储为整数、小数、日期型等...
<1>整数型
类型 大小 范围（有符号） 范围（无符号unsigned） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32768，32767) (0，65535) 大整数值
MEDIUMINT 3 字节 (-8388608，8388607) (0，16777215) 大整数值
INT 4 字节 (-2147483648，2147483647) (0，4294967295) 大整数值
BIGINT 8 字节 （-92.......,922......） (0，2的64次方减1) 极大整数值
<2>浮点型
FLOAT(m,d） 4 字节 单精度浮点型 备注：m代表总个数，d代表小数位个数
DOUBLE(m,d） 8 字节 双精度浮点型 备注：m代表总个数，d代表小数位个数
<3>定点型
DECIMAL(m,d） 依赖于M和D的值 备注：m代表总个数，d代表小数位个数
<4>字符串类型
类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535字节 变长字符串
TINYTEXT 0-255字节 短文本字符串
TEXT 0-65535字节 长文本数据
MEDIUMTEXT 0-16777215字节 中等长度文本数据
LONGTEXT 0-4294967295字节 极大文本数据
char的优缺点：存取速度比varchar更快，但是比varchar更占用空间 char(10) 0000000012
varchar的优缺点：比char省空间。但是存取速度没有char快 varchar(10) 12
<5>时间型
数据类型 字节数 格式 备注
date 3 yyyy-MM-dd 存储日期值
time 3 HH:mm:ss 存储时分秒
year 1 yyyy 存储年
datetime 8 yyyy-MM-dd HH:mm:ss 存储日期+时间
timestamp 4 yyyy-MM-dd HH:mm:ss 存储日期+时间，可作时间戳
DDL 数据定义语言
创建数据
创建表
# 约束条件：
comment ----说明解释
not null ----不为空
default ----默认值
unsigned ----无符号（即正数）
auto_increment ----自增
zerofill ----自动填充
tinyint(5) zerofill 比如：id为1会变为 00001
unique key ----唯一值
# 表创建语法格式
CREATE TABLE 表名 (
字段名1 字段类型1 约束条件1 说明1,
字段名2 字段类型2 约束条件2 说明2,
字段名3 字段类型3 约束条件3 说明3
);
create table 新表名 as select * from 旧表名 where 1=2;(注意：建议这种创建表的方式用于日常测试，因为可能索引什么的会复制不过来)
create table 新表名 like 旧表名;
CREATE TABLE student (
id tinyint(5) zerofill auto_increment not null comment '学生学号',
name varchar(20) default null comment '学生姓名',
age tinyint default null comment '学生年龄',
class varchar(20) default null comment '学生班级',
sex char(5) not null comment '学生性别',
unique key (id)
)engine=innodb charset=utf8;
如何查看表
查看数据库中的所有表：show tables；
查看表结构：desc 表名;
查看创建表的sql语句：show create table 表名;
\G :有结束sql语句的作用，还有把显示的数据纵向旋转90度
\g :有结束sql语句的作用
表结构的维护和删除
##################### 修改表名
rename table 旧表名 to 新表名;
rename table student to user;
# 添加列 ：alter table 表名 add 列名 类型;
alter table user add addr varchar(50);
# 添加列的同时添加comment
alter table user add family varchar(50) comment '学生父母';
# 添加一列为表的第一列（最前面）
alter table user add job varchar(10) first;
# 添加一列（指定列的后面）
alter table user add servnumber int(11) after id;
##################### 修改列类型
alter table 表名 modify 列名 新类型;
alter table user modify servnumber varchar(20);
##################### 修改列名
alter table 表名 change 旧列名 新列名 类型;
alter table user change servnumber telephone varchar(22);
##################### 删除列
alter table 表名 drop 列名;
alter table user drop famliy;
##################### 修改字符集
alter table 表名 character set 字符集;
alter table user character set GBK;
##################### 表的删除
drop table 表名；
drop table user;
# 看表是否存在，若存在则删除表：drop table if exists 表名;
drop table if exists teacher;
DML 数据操纵语言
新增表数据
##################### 普通的插入表数据
# 方式一
insert into 表名（字段名） values（字段对应值）;
insert into employee (empno,ename,job,mgr,hiredate,sal,deptnu) values ('1000','小明','经理','10001','2019-03-03','12345.23','10');
# 方式二
insert into 表名 values（所有字段对应值）;
insert into employee values ('1001','小明','经理','10001','2019-03-03','12345.23','10');
##################### 蠕虫复制（将一张表的数据复制到另一张表中）
# 全表数据
insert into 表名1 select * from 表名2;
# 复制指定列数据
insert into 表名1（字段名1，字段名2） select 字段名1，字段名2 from 表名2;
insert into emp (empno,ename) select empno,ename from employee;
##################### 建表复制(在建表的时候进行数据复制)
# 建表全量复制
create table 表名1 as select * from 表名2 where 1=1;
create table emp as select * from employee where 1=1;
# 建表局部复制
create table 表名1 as select 字段名1，字段名2 from 表名2;
create table emp as select empno ,ename from employee
##################### 一次性插入多个数据
insert into 表名 (字段名) values (对应值1),(对应值2),(对应值3);
insert into employee (empno,ename,job,mgr,hiredate,sal,deptnu) values ('1000','小明','经理','10001','2019-03-03','1234.25','12'), ('1001','小红','总裁','9999','2019-02-03','12324.25','1')
##################### 建表语句
CREATE TABLE employee(
empno INT PRIMARY KEY comment '雇员编号',
ename VARCHAR(20) comment '雇员姓名',
job VARCHAR(20) comment '雇员职位',
mgr INT comment '雇员上级编号',
hiredate DATE comment '雇佣日期',
sal DECIMAL(7,2) comment '薪资',
deptnu INT comment '部门编号'
);
表数据的修改以及删除
- 在删改数据之前会对数据进行备份操作，以防万一，可以进行数据回退。
##################### 修改（更新）
update 表名 set 字段名1=值1 where 字段名=值;
update 表名 set 字段名1=值1,字段名2=值2 where 字段名=值;
update employee set sal='8888.8' where empno='1000'
##################### 删除
# 删除指定行
delete from 表名 where 字段名=值;
delete from employee where empno='1001'
# 删除表
truncate table 表名; #删除表数据，会把删除的操作记录给记录起来，以便数据回退，不会释放空间，不会删除表结构
delete from 表名; #删除表数据，但不会记录删除操作，会把表占用的空间恢复到最初，不会删除表结构
drop table 表名; #删除表数据和表结构
##################### 删除速度
drop > truncate > delete
汉字显示乱码问题
DDL 数据定义语言
准备数据
/*创建部门表*/
CREATE TABLE dept(
deptnu INT PRIMARY KEY comment '部门编号',
dname VARCHAR(50) comment '部门名称',
addr VARCHAR(50) comment '部门地址'
);
/*某个公司的员工表*/
CREATE TABLE employee(
empno INT PRIMARY KEY comment '雇员编号',
ename VARCHAR(50) comment '雇员姓名',
job VARCHAR(50) comment '雇员职位',
mgr INT comment '雇员上级编号',
hiredate DATE comment '雇佣日期',
sal DECIMAL(7,2) comment '薪资',
deptnu INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*创建工资等级表*/
CREATE TABLE salgrade(
grade INT PRIMARY KEY comment '等级',
lowsal INT comment '最低薪资',
higsal INT comment '最高薪资'
);
/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');
/*插入emp表数据*/
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
where 条件下的各种查询
group by 分组查询
- 作用：把行 按 字段 分组
- 语法：group by 列 1，列 2....列 N
- 适用场合：常用于统计场合，一般和聚合函数连用
# 统计各部门员工的数量
select deptnu,count(*) from employee group by deptnu;
# 统计各部门各职位的数量
select deptnu,job,count(*) from employee group by deptnu,job;
# 统计每个职位的数量
select job,count(*) from employee group by job;
having 条件查询
- 作用：对查询的结果进行筛选操作
- 语法：having 条件 或者 having 聚合函数 条件
- 适用场合：一般跟在 group by 之后
# 对职位进行分组，并删选出职位等于文员的员工数量
select job,count(*) from employee group by job having job ='文员';
# 对部门和职位进行分组，并筛选出那些同一部门中的同一职位员工数量大于等于2人的信息。
select deptnu,job,count(*) from employee group by deptnu,job having count(*)>=2;
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2;
order by 排序查询
- 作用：对查询的结果进行排序操作
- 语法：order by 字段 1,字段 2 .....
- 适用场合：一般用在查询结果的排序
- 默认排序规则：asc (升序)
# 对部门和职位进行分组，并筛选出那些同一部门中的同一职位员工数量大于等于2人的信息，并对结果基于部门变化进行重新排序显示。
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu desc; #降序
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu asc; #升序
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu;
顺序
where ---- group by ---- having ---- order by
limit 限制查询（限制)
- 作用：对查询结果起到限制条数的作用
- 语法：limit n，m n:代表起始条数值，不写默认为 0；m 代表：取出的条数
- 适用场合：数据量过多时，可以起到限制作用
# 取出第5页，每页数量为5条。
select * from employee limit 4,5;
exists 查询
- exists 型子查询后面是一个受限的 select 查询语句
- exists 子查询，如果 exists 后的内层查询能查出数据，则返回 TRUE 表示存在；为空则返回 FLASE 则不存在。
# 分为俩种：exists跟 not exists
select 1 from employee where 1=1;
select * from 表名 a where exists (select 1 from 表名2 where 条件);
# 查询出公司有或是没有员工的部门的详细信息
select * from dept a where exists (select 1 from employee b where a.deptnu=b.deptnu);
select * from dept a where not exists (select 1 from employee b where a.deptnu=b.deptnu);
左右连接查询
- 左连接称之为左外连接、右连接称之为右外连接、均属于外连接。
- 左连接关键字： left join 表名 on 条件 / left outer 表名 join on 条件。
- 右连接关键字：right join 表名 on 条件 / right outer 表名 join on 条件。
- 左连接说明： left join 是 left outer join 的简写，左(外)连接，左表(a_table)的记录将会全部表示出来， 而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL。
- 右连接说明：right join 是 right outer join 的简写，与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为 NULL。
# 列出部门名称和这些部门的员工信息，同时列出那些没有的员工的部门
select a.dname,b.* from dept a left join employee b on a.deptnu=b.deptnu;
select b.dname,a.* from employee a right join dept b on b.deptnu=a.deptnu;
内连接查询
- 内连接：获取两个表中字段匹配关系的记录
- 主要语法：INNER JOIN 表名 ON 条件
# 查出员工张飞的所在部门的地址
select b.ename,a.addr from dept a inner join employee b on a.deptnu=b.deptnu and b.ename='张飞';
select b.ename,a.addr from dept a,employee b where a.deptnu=b.deptnu and b.ename='张飞'
联合查询
- 联合查询：就是把多个查询语句的查询结果结合在一起
- 主要语法 1：... UNION ... （去除重复） 主要语法 2：... UNION ALL ...（不去重复）
# union查询的注意事项
(1)两个select语句的查询结果的“字段数”必须一致；
(2)通常，也应该让两个查询语句的字段类型具有一致性；
(3)也可以联合更多的查询结果；
(4)用到order by排序时，需要加上limit（加上最大条数就行），需要对子句用括号括起来
# 对销售员的工资从低到高排序，而文员的工资从高到低排序
(select * from employee a where a.job = '销售员' order by a.sal limit 999999) union (select * from employee b where b.job = '文员' order by b.sal desc limit 99999);
DQL 查询练习
# 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
select a.deptnu,a.dname,a.addr,b.zongshu from dept a, (select deptnu,count(*) as zongshu from employee group by deptnu) b where a.deptnu = b.deptnu;
# 列出薪金比安琪拉高的所有员工。
select * from employee where sal > (select sal from employee where ename='安琪拉')
# 列出所有员工的姓名及其直接上级的姓名。
select a.ename,ifnull(b.ename,'BOSS') as leader from employee a left join employee b on a.mgr=b.empno
# 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select a.empno,a.ename,c.dname from employee a left join employee b on a.mgr=b.empno left join dept c on a.deptnu=c.deptnu where a.hiredate < b.hiredate;
# 列出所有文员的姓名及其部门名称，所在部门的总人数。
select a.ename,b.dname,a.job,c.zongshu from employee a, dept b,(select deptnu,count(*) as zongshu from employee group by deptnu) c where a.deptnu=b.deptnu and a.job='文员' and a.deptnu=c.deptnu;
# 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
select job,count(*) from employee group by job having min(sal) > 15000;
# 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
select ename from employee where deptnu=(select deptnu from dept where dname='销售部');
# 列出与诸葛亮从事相同工作的所有员工及部门名称。
select a.ename,b.dname from employee a,dept b where a.deptnu=b.deptnu and a.job=(select job from employee where ename='诸葛亮');
# 列出薪金比在部门30工作的员工最高薪金还高的员工姓名和薪金、部门名称。
select a.ename,a.sal,b.dname from employee a,dept b where a.deptnu=b.deptnu and sal > (select max(sal) from employee where deptnu=30)
# 列出每个部门的员工数量、平均工资。
select deptnu,count(*),avg(sal) from employee group by deptnu;
# 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
select a.*,c.dname,b.ename as leader,d.grade from employee a,employee b,dept c,salgrade d where a.mgr=b.empno and a.deptnu=c.deptnu and a.sal > (select avg(sal) from employee) and a.sal between d.lowsal and d.higsal;
DCL 数据控制语言
数据控制语言（DCL：Data Control Language）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括 GRANT、DENY、REVOKE 等语句
限制 root 用户指定 ip 登录
修改用户密码
# 第一种方式：set password for 用户@ip = password('密码');
set password for root@localhost = password('123');
# 第二种方式：mysqladmin -u用户 -p旧密码 password 新密码
新密码不会暴露
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysqladmin /usr/bin/mysqladmin #解决mysqladmin命令丢失问题
mysqladmin -ur -p旧密码 password 新密码
mysqladmin -urootmysqladmin -uroot -p123 password #提示输入 新密码 1234
# 第三种方式
update mysql.user set authentication_string=password('1234') where user='root' and host='localhost';
忘记密码
# 第一步：修改配置文件my.cnf (默认在/etc/my.cnf)
[mysqld] #添加如下配置
skip-grant-tables # 跳过权限的意思
# 第二步：重启mysql服务
[root@localhost ~]# systemctl restart mysql
# 第三步：无需密码登录进入
[root@localhost ~]# mysql -uroot -p
# 第四步：修改密码
set password for root@localhost = password('123');
创建新用户并限制 ip 网段登录
库、表的授权与回收