SQL-通用语言以及分类
文章目录
SQL通用语法
- SQL语句可以单行或多行,以分号结尾
- SQL语句可以使用空格/缩进来增强语句可读性
- MySQL数据库的SQL语句部分大小写,关键字建议大写
- 注释:
- 单行注释:–注释内容或#注释内容(MySQL特有)
- 多行注释 /* 注释内容 */
SQL分类
- DDL (Data Definition Language) 数据定义语言
- DML(Data Manipulation Language) 数据操作语言
- DQL(Data Query Language) 数据查询语言
- DCL(Data Control Language) 数据控制语言
DDL
功能:表的定义,数据的定义等。
第一部分
-
查询
查询所有数据库
Show Databases;
查询当前数据库
Select Database();
-
创建
Create Database [If not exists] 数据库名 [Defualt Charset 字符集] [Collate 排序顺序];
If not exists
:如果不存在创建Defualt Charset 字符集
:设置字符集 比如Defualt Charset utf8
Collate 排序顺序
: -
删除
Drop Database[If exists]数据库名;
-
使用
USE 数据库名;
第二部分
-
DDL-表操作-查询
-
查询当前数据库所有表
show tables;
-
查询表结构
desc 表名;
desc
应该是 describe -
查询指定表的建表语句
show create table 表名
-
-
DDL-表操作-创建
create table 表名{ 字段1 字段1类型[comment 字段1注释], 字段2 字段2类型[comment 字段2注释], 字段3 字段3类型[comment 字段3注释], .... 字段n 字段n类型[comment 字段n注释] }[comment 表注释];
第三部分
-
DDL-表操作-数据类型
主要分为三类:数值类型、字符串类型、日期时间类型。
- 数据类型
类型 大小 有符号范围 无符号范围 描述 TINYINT 1byte (-128,127) (0,255) 小整数值 SMALLINT 2byte (-32768,32767) (0,65535) 大数数值 MEDIUMINT 3byte (-8388608,8388607) (0,16777215) 大数数值 INT 或INTEGER 4byte (-2147483638,2147483647) (0,4294967295) 大数数值 BIGINT 8byte (-263,263-1) (0,2^64-1) 极大数数值 FLOAT 4byte (-3.402823466 E+38,3.402823466351 E+38) 0和(1.175494351 E-38,3.402823466 E+38) 单精度浮点数值 DOUBLES 8byte -1.7976931348623157 E+308,1.7976931348623157 E+308 0和2.2250738585072014 E-308,1.7976931348623157 E+308 双精度浮点数值 DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 小数值(精确定点数) -
字符串类型
类型 大小 描述 char 0-255byte 定长字符串 varchar 0-65535byte 变长字符串 tinyBlob 0-255byte 不超过255个字符的二进制数据 tinyText 0-255byte 短文本字符串 blob 0-65 535byte 二进制形式的长文本数据 text 0-65 535byte 长文本数据 mediumBlob 0-16 777 215byte 二进制形式的中等长度文本数据 mediumText 0-16 777 215byte 中等长度文本数据 longBlob 0-4 294 967 295byte 二进制形式的极大数据文本 longText 0-4 294 967 295byte 极大文本数据 -
日期类型
类型 大小 格式 描述 data 3 YYYY-MM-DD 日期体 time 3 HH:MM:SS 时间值或持续时间 year 1 YYYY 年份值 dataTime 6 YYYY-MM-DD HH:MM:SS 混合日期和时间 timeStamp 4 YYYY-MM-DD HH:MM:SS 混合日期和时间值,时间戳
-
DDL-表操作-修改
添加字段
alter table 表名 ADD 字段名 类型(长度) [comment 注释] [约束];
-
DDL-表操作-修改
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 类型(长度) [comment 注释] [约束];
-
DDL-表操作-修改
删除字段
alter table 表名 drop 字段名;
修改表名
alter table 表名 rename to 新表名;
-
DDL-表操作-删除
删除表
drop table [if exists] 表名;
删除指定表,并重新创建该表(相当于清空数据,保留表结构)
truncate table 表名;
注:[]中的内容为选填内容
DML
功能:用来对数据库中表的数据记录进行增删改操作。
- 添加数据: insert
- 修改数据::update
- 删除数据: delete
DML-添加数据
1.给指定字段添加数据
insert into 表名 (字段名1,字段名2,....) values(值1,值2);
2.给全部字段添加数据
insert into 表名 values(值1,值2,...);
3.批量添加数据
insert into 表名 (字段名1,字段名2,....) values (值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into 表名 values (值1,值2,...),(值1,值2,...),(值1,值2,...);
注意:
- 插入数据时,指定的字段顺序需要与值的顺序是一 一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
DML-修改数据
update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];
注意:修改语句的where
条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
DML-删除数据
delete from 表名 [where 条件];
注意:
- delete语句的
where
条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。 - delete语句不能删除某一个字段的值(可以使用
update
)。
DQL
功能:数据查询语言,用来查询数据库中表的记录。
-
关键字:select
-
DQL-语法
------------------------------- select 字段列表 from 表名列表 ------------------------------- where 条件列表 ------------------------------- group by 分组字段列表 having 分组后条件列表 ------------------------------- order by 排序字段列表 ------------------------------- limit 分页参数 -------------------------------
-
基本查询
-
条件查询 (where)
-
聚合查询 (count,max,min,avg,sum)
-
分组查询 (group by)
-
排序查询 (order by)
-
分页查询 (limit)
-
DQL-基本查询
1.查询多个字段
select 字段1,字段2,字段3,... from 表名;
select * from 表名;
2.设置别名
select 字段1 [as 别名1],字段2 [as 别名2],字段3 [as 别名3],... from 表名;
3.去除重复记录
select distinct 字段列表 from 表名;
DQL-条件查询
1.语法
select 字段列表 from where 条件列表;
2.条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
between…and… | 在某个范围之内(含最小、最大值) 【小值在前,大值在后】 |
in(…) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配( _ 匹配单个字符,%匹配任意个字符) |
is null | 是NULL |
逻辑运算符 | 功能 |
---|---|
and 或 && | 与(多个条件同时成立) |
or 或 || | 或(多个条件任意一个成立) |
not 或 ! | 非 |
like 占位符示例(以employee示例)
select * from employee where name like '___';
DQL-聚合函数
1.常见聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
2.语法
select 聚合函数(字段列表) from 表名;
注:所有 NULL 值不参与聚合函数的运算。
DQL-分组查询
1.语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
2.where
和 having
区别
-
执行时机不同:
where
是分组之前进行过滤,不满足where
条件,不参与分组;而having是分组之后对结果进行过滤。 -
判断条件不同:
where
不能对聚合函数进行判断,而having
可以。例:
select gender,count(*) from employee group by gender having count(*)>=3;
select gender,count(*) from employee where gender='男';
注意:
- 执行顺序:
where
> 聚合函数 >having
。 - 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无意义。
DQL-排序查询
1.语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
2.排序方式
-
asc:升序(默认值) [ ascend ]
-
desc: 降序 [ descend ]
例:
select * from employee order by age asc;
desc
同理组合:
-- 根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行排序 select * from employee order by age asc , entrydata desc ;
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
DQL-分页查询
1.语法
select 字段列表 from 表名 limit 起始索引,查询记录数;
例:
-- 查询第一页员工数据,每页展示3条记录
select * from employee limit 0,3;
-- 查询第二页员工数据,每页展示10条记录---->limit (查询页码-1)* 每页显示记录数,每 页记录
select * from employee limit 3,3;
注意:
-
起始索引 从0开始(数组),起始索引 = (查询页码-1) 每页显示记录数。*
(注:对这句话的理解,第一个参数是偏移行数【从首行开始你不想要几行就去掉】,
第二个参数是限制返回行数【你要多少行数据】
limit n,m; 【从第n条开始展示m条记录】)
-
分页查询是数据库的方言,不同的数据库有不同的实现,mysql中是limit。
-
如果查询的是第一页的数据,起始索引可以省略,直接简写为limit 10。
DQL-执行顺序
- 根据英语语法
DCL
功能:来管理数据库用户、控制数据库的访问权限
DCL-管理用户
-
查询用户
use mysql; select * from user;
-
创建用户
create user '用户名'@'主机名' identified by '密码';
-
修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
-
删除用户
drop user '用户名'@'主机名';
注意:
- 主机名可以使用%通配。
- 这类SQL开发人员操作比较少,主要是DBA(Database Administrator 数据库管理员)使用。
DCL-权限控制
MySQL中定义了很多种权限,但是常用的就一下这几种:
权限 | 说明 |
---|---|
all,all privileges | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库/表 |
-
查询权限
show grants for '用户名'@'主机名';
-
授予权限
grant 权限 on 数据库名.表名 to '用户名'@'主机名';
-
撤销权限
revoke 权限 on 数据库名.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号分隔。
- 授权时,数据库名和表名可以使用*进行通配,代表所有。
l,all privileges | 所有权限 |
| select | 查询数据 |
| insert | 插入数据 |
| update | 修改数据 |
| delete | 删除数据 |
| alter | 修改表 |
| drop | 删除数据库/表/视图 |
| create | 创建数据库/表 |
-
查询权限
show grants for '用户名'@'主机名';
-
授予权限
grant 权限 on 数据库名.表名 to '用户名'@'主机名';
-
撤销权限
revoke 权限 on 数据库名.表名 from '用户名'@'主机名';
注意:
- 多个权限之间,使用逗号分隔。
- 授权时,数据库名和表名可以使用*进行通配,代表所有。