SQL的分类
分类 | 说明 |
---|---|
DDL | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | 数据操作语言,用来对数据库中表的数据记录进行增删改 |
DQL | 数据查询语言,用来查询数据库中表的记录 |
DCL | 数据控制语言,用来创建和管理数据库用户,控制数据库的访问权限 |
1. 数据定义语言DDL
data definition language,数据定义语言,用来定义数据库对象(数据库,表,字段)
1.1 数据库操作
- 查询所有数据库
show databases;
- 查询当前数据库
select database();
- 创建数据库
create database [if not exists ] 数据库名 [default charset 字符集 ] [collate 排序规则];
- 删除数据库
drop database [ if exists ] 数据库名;
- 切换数据库
use 数据库名;
1.2 表操作
- 查询当前数据库所有表
show tables;
- 查询指定表结构
desc 表名;
- 查询指定表的建表结构
show creae table 表名;
- 创建表结构
create table 表名(
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
.
.
.
) [comment 表注释 ];
-
表操作-数据类型
1)数值类型
类型 | 大小 | 有符号(signed)范围 | 无符号(unsigned)范围 | 描述 |
---|---|---|---|---|
TINYINT | 1byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2byte | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3byte | (–8388608,8388607) | (0,16777215) | 大整数值 |
INT/INTEGER | 4byte | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8byte | (-2^63 , 2^63 -1) | (0,2^64-1) | 极大整数值 |
FLOAT | 4byte | (-3.402823466 E+38,3.402823466351 E+38) | (0和(1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE | 8byte | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | (0和(2.2250738585072014 E-308,1.7976931348623157 E+308) | 小整数值 |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精度确定点数) |
eg:
--年龄字段 不会出现符输,而且人的年龄不会太大
age tinyint unsigned
--符输 总分100分,最多出现一位小数
score double(4.1)
2)日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATA | 3 | 1000-01-01至9999-12-31 | YYYY-MM-DD | 日起值 |
TIME | 3 | -838:59:59至838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901至2155 | YYYY | 年份值 |
DATATIME | 8 | 1000-01-01 00:00:00至9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1907-01-01 00:00:01至2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
3)字符串类型
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXTT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-42949667295 bytes | 极大文本数据 |
- 表操作-修改
1)添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
eg:为emp表添加一个新的字段“昵称”为nickname,类型为varchar(20)
alter table emp add nickname varchar(20) comment '昵称';
2)修改数据类型
alter table 表名 modify 字段名 新数据类型(长度)
3)修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释];
4)删除字段
alter table 表名 drop 字段名;
5)修改字段名
alter table 表名 rename to 新表名;
- 表操作-删除
1)删除表
drop table [if exists] 表名;
2)删除指定表并创建表
truncate table 表名;
2. 数据操作语言DML
2.1 DML-添加数据(insert into)
insert into 表名(字段1,字段2,字段3,...) values (值1,值2,值3,...)
[,(值1,值2,值3,...),...];
2.2 DML-修改数据(update)
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3,...[where 条件];
eg:update emp set nickname='lisi' where id=1;
2.3 DML-删除数据(delete)
delete from 表名[where 条件];
eg:delete from emp where id=2;
注意:delete语句若没有条件,则会删除表中的所有数据,delete语句不能删除某一个字段。
3. 数据查询语言DQL
DQL的全称是data query language,数据查询语言,用来查询数据库中表的记录。
- 基本查询
- 条件查询(where)
- 聚合函数(count, max, min, avg, sum)
- 分组查询(group by)
- 排序查询(order by)
- 分页查询(limit)
DQL语法
select
字段名
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
3.1 DQL- 基本查询
- 查询多个字段
select 字段1, 字段2, 字段3, ..., from 表名;
select * from 表名;
- 设置别名
select 字段1[AS 别名1], 字段2[AS 别名2], ...from 表名;
//其中as可以省略
- 去除重复记录
select distinct 字段列表 from 表名;
3.2 DQL- 条件查询(where)
select 字段列表 from 表名 where 条件列表;
条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
between …and | 在某个范围之内(含最小,最大值) |
in(…) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配( _匹配单个字符,%匹配任意多个字符 ) |
is null | 是 null |
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且(多个条件同时成立 ) |
or 或 || | 或者(多个条件任意成立 ) |
not 或 ! | 非 |
练习
--1.查询年龄等于88的员工
select * from emp where age = 88;
--2.查询年龄小于20的员工信息
select * from emp where age < 20;
--3.查询没有身份证号的员工信息
select * from emp where idcard is null;
--4.查询有身份证号的员工信息
select * from emp where idcard is not null;
--5.查询年龄不等于88的员工
select * from emp where age != 88;
select * from emp where age <> 88;
--6.查询年龄在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;
--7.查询性别为女且年龄小于25岁的员工信息
select * from gender='女' and age < 25;
--8.查询年龄等于 18 或 20 或 40 的员工信息
select * from emp age = 18 or age = 20 或 age = 40;
select * from emp age in(18,20,40);
--9.查询姓名为两个字的员工信息
select * from emp where name like '_ _';
--9.查询身份号最后一位是x的员工信息;
select * from emp where idcard like '%x';
3.3 DQL- 聚合函数(count, max, min, avg, sum)
- 介绍:聚合函数将一列数据作为一个整体(都是作用于某一列),进行纵向计算。
- 常见的聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
注意:null值不参与函数运算
语法
select 聚合函数(字段列表)from 表名;
练习
--1.统计企业员工数量
select count(*) from emp;
select count(id) from emp;
--2.统计企业员工的平均年龄
select avg(age) from emp;
--3.统计该企业员工的最大年龄
select max(age) from emp;
--4.统计西安地区员工的年龄之和
select sum(age) from emp where workadress = '西安';
3.4 DQL- 分组查询(group by)
语法
select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];
where 与 having 区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
练习
--1.根据性别分组,统计男性员工 和 女性员工的数量
select gender count(*) from emp group by gender
--2.根据性别分组,统计男性员工 和 女性员工的平均年龄
select gender avg(age) from emp group by gender;
--3.查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workadress,count(*) from emp where age<45 group by workadress having count(*)>=3;
注意
- 执行顺序:where > 聚合函数 > having。
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
3.5 DQL- 排序查询(order by)
语法
select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式2;
排序方式
- ASC: 升序
- DESC: 降序
注意:如果多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
练习
--1.根据年龄对公司员工进行升序排序
select * from emp order by age asc;
select * from emp order by age ;
--2.根据入职时间,对员工进行降序排序
select * from emp order by entrydate desc;
--3.根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from emp order by age asc , entrydate desc;
3.6 DQL- 分页查询(limit)
语法
select 字段列表 from 表名 limit 起始索引,查询记录数
注意:
- 起始索引从0开始,起始索引=(查询页码-1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是limit。
- 如果查询的是每一页数据,起始索引可以省略,直接简写为limit 10。
练习
--1.查询第1页员工数据,每页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;
--2.查询第2页员工数据,每页展示10条记录
select * from emp limit 10,10;
案列
--1.查询性别为男,并且年龄再20-40岁(含)以内的姓名为三个字的员工
select * from emp where gender ='女' and age in(20,21,22);
--2.查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工
select * from emp where gender='男' and (age between 20 and 40) and name like '_ _ _';
--3.统计员工表中,年龄小于60岁,男性员工和女性员工的人数
select gender , count(*) from emp where age<60 group by gender;
--4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
select name,age from emp where age <= 35 order by age asc,entrydate desc;
--5.查询性别为男,并且年龄在20-40(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序
select * from emp where gender='男' and (age between 20 and 40) order by age asc,entrydate asc limit 5;
DQL-执行顺序
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
select
字段列表
order by
排序字段列表
limit
分页参数
4. 数据控制语言DCL
DCL全称是DATA CONTROL LANGUAGE,数据控制语言,用来管理数据库用户,控制数据库的访问权限。
4.1 DCL-管理用户
4.1.1 查询用户
use mysql;
select * from user;
4.1.2 创建用户
create user '用户名'@'主机名' identified by '密码';
4.1.3 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
4.1.4 删除用户
drop user '用户名'@'主机名';
注意:主机名可以使用%通配。这类SQL开发人员操作的比较少,主要是DBA(database administrator 数据库管理员)使用。
练习
--1.创建用户 kity; 只能在当前主机localhost访问,密码123456
create user 'kity'@'localhost' identified by '123456';
--2.创建用户yarm,可以在任意主机访问该数据库,密码123456
create user 'yarm'@'%' identified by '123456';
--3.修改用户kity的访问密码为1234
alter user 'kity'@'localhost' identified with mysql_native_password by '新密码';
--4.删除用户kity
drop user 'kity'@'localhost';
4.2 DCL-权限控制
MySQL中常用的权限有以下几种:
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
4.2.1 查询权限
show grants for '用户名'@'主机名';
4.2.2 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
4.2.3 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意:多个权限之间,使用逗号分隔。授权时,数据库和表名可以使用* 进行通配,代表所有。
4.2.4 总结
1.用户管理
create user '用户名'@'主机名' identified by '密码';
alter user '用户名'@'主机名' identified with mysql_native_password by '密码';
drop user '用户名'@'主机名';
2.权限控制
show grants for '用户名'@'主机名';
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';