常用SQL语法整理
此篇帖子,主要是对SQL的一些基础语法知识进行整理
SQL分类:
-
DDL(Data Definition Language)数据定义语言
用来定义数据库对象:数据库, 表, 列等. 关键字:create, drop, alter等
-
DMl(Data Manipulation Language)数据操作语言
用来对数据库中表的数据进行增删改. 关键字:insert, delete, update等
-
DQL(Data Query Language)数据查询语言
用来查询数据库中表的记录(数据). 关键字:select, where等
-
DCL(Data Control Language)数据控制语言
用来定义数据库的访问权限和安全级别, 以及创建用户. 关键字:grant, revoke等
DDL数据定义语言
DDL是用来操作数据库, 表
操作数据库
-
创建(Create)
# 语法格式: # 创建数据库 create database 数据库名称; # 创建数据库,判断是否存在,再创建 create database if not exists 数据库名称; # 创建数据库,并指定字符集 create database 数据库名称 character set 字符集名; # 举例:创建一个test数据库,判断是否存在,并设定字符集为gbk create database if not exists test character set gbk; -
查询(Retrieve)
# 语法格式: # 查询所有数据库的名称 show databases; # 查询某个数据库的字符集:查询某个数据库的创建语句 show create database 数据库名称; -
修改(Update)
# 语法格式: # 修改某个数据库的字符集 alter database 数据库名称 character set 字符集名称; -
删除(Delete)
# 语法格式: # 删除数据库 drop database 数据库名称; # 判断数据库是否存在,存在再删除 drop if exists 数据库名称; -
使用数据库
# 语法格式: # 查询当前正在使用的数据库名称 select database(); # 使用数据库 use 数据库名称;
操作表
-
Create(创建)
# 语法格式: # 创建表 create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n ); # 注意:最后一列的后面不需要添加逗号(,) # 复制表 create table 表名 like 被复制的表名; # 一般建表会添加约束,注释和引擎这些(一般都大写) CREATE TABLE 表名( 列名1 数据类型1 约束 COMMENT＝＂字段１注释＂, 列名2 数据类型2 COMMENT＝＂字段２注释＂, ... 列名n 数据类型n COMMENT＝＂字段n注释＂ ) ENGINE = 引擎名称 COMMENT＝＂表注释＂; -
Retrieve(查找)
# 语法格式: # 查询某个数据库中所有的表名 show tables; # 查询表结构 desc 表名; -
Update(修改)
# 语法格式: # 修改表名 alter table 表名 rename to 新的表名; # 修改表的字符集 alter table 表名 character set 字符集名称; # 添加一列 alter table 表名 add 列名 数据类型; # 修改列名称 alter table 表名 change 列名 新列名 新数据类型; # 修改列的数据类型 alter table 表名 modify 列名 新数据类型; # 删除列 alter table 表名 drop 列名; -
Detele(删除)
# 语法格式: # 删除表 drop table 表名; drop table if exists 表名;
DML增删改表中的数据
-
添加数据
# 语法格式: insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n); # 注意事项: 1. 列名要和值一一对应 2. 如果表名后面不定义列名,则默认给所有列添加值 3. 除了数字类型,其他类型需要使用引号引起来(单双都行) -
删除数据
# 语法格式: delete from 表名 [where 条件]; # 注意事项: 1. 如果不添加条件,则删除表中的所有记录 2. 如果要删除所有记录,还有以下的方式 1) delete from 表名; --不推荐使用.有多少条记录就会执行多少次删除操作 2) truncate table 表名; --推荐使用.效率更高,先删除表,然后再创建一张一样的表 -
修改数据
# 语法格式: update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件]; # 注意事项: 如果不添加条件,则会修改表中所有记录
DQL查询表中的记录
# 基础语法格式:
select * from 表名;
# 常用语法格式:
select
字段表名
from
表名列表
where
条件列表
group up
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
/* 说明:
1. 如果是查询所有字段,则可以使用*来代替字段列表
2. select 后面可跟上一个distinct,用于去除相同的数据
3. 起别名可直接再字段后面加一个空格,然后字段名;也可以使用as间隔
4. where子句后面跟条件
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合)
* LIKE：模糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
5. group by 分组字段
分组之后查询的字段只有:分组字段和聚合函数(也就意味着查其他的字段会报错)
6. where 和 having 的区别
1). where在分组之前进行限定,如果条件不满足,则不参与分组.having在分组之后进行限定,如果不满足条件,则不会被查询出来
2). where后面不可以跟聚合函数,having可以进行聚合函数的判断
7. order by 排序
语法如下:
order by 排序字段1 排序方式1, 排序字段2 排序方式2,...
排序方式:
ASC ： 升序,默认排序
DESC ：降序
注意：如果有多个排序条件，只有在前面的条件值是一样时，才会判断下一个条件
8. limit 开始的索引, 每页查询的条数
公式:
开始的索引 = (当前页码 - 1) * 每页显示的条数
注意:limit是MySQL特有的
*/
# 常见的聚合函数(聚合函数就是将一列数据作为一个整体,进行纵向的计算)
1. count(): 计算个数(一般选择非空的列)
2. max():计算最大值
3. min():计算最小值
4. sum():计算和
5. avg():计算平均值
多表查询
内连接
1. 隐式内连接
# 语法格式:
使用where关键字
2. 显式内连接
# 语法格式:
select 字段列表 from 表名1 [inner] join 表名2 on 条件;
外连接
1. 左外连接 -- 查询的是左表所有数据以及交集部分
# 语法格式:
select 字段列表 from 表1 left [outer] join 表2 on 条件;
2. 右外连接 -- 查询的是右表所有数据及其交集部分
# 语法格式:
select 字段列表 form 表1 right [outer] join 表2 on 条件;
子查询
# 查询中嵌套查询,被嵌套的查询称为子查询
DCL管理用户和权限管理
这一部分我们开发时很少接触的,一般是公司的DBA来操作
-
管理用户
1. 添加用户： 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 2. 删除用户： 语法：DROP USER '用户名'@'主机名'; 3. 修改用户密码： 语法：UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; 语法：SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); 4. 查询用户： 1). 切换到mysql数据库 USE myql; 2). 查询user表 SELECT * FROM USER; -
权限管理
1. 查询权限： 语法: SHOW GRANTS FOR '用户名'@'主机名'; SHOW GRANTS FOR 'lisi'@'%'; 2. 授予权限： 语法: grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; -- 给张三用户授予所有权限，在任意数据库任意表上 GRANT ALL ON *.* TO 'zhangsan'@'localhost'; 3. 撤销权限： 语法: revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
约束
概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性
分类如下:
- 主键约束 primary key
1). 非空且唯一
2). 一张表只能有一个字段为主键
3). 主键就是表中记录的唯一标识
```mysql
create table stu(
id int primary key auto_increment,-- 给id添加主键约束,并自动增长
name varchar(20)
);
```
- 非空约束 not null
某一列的值不能为null
```mysql
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
```
- 唯一约束 unique
某一列的值不能重复
```mysql
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 手机号
);
# 注意:唯一约束可以有null值,但是只能由一条记录为null
```
- 外键约束 foreign key
让表与表之间产生关系,从而保证数据的正确性
```mysql
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
```
事务
概念:
如果一个包含多个步骤的业务操作, 被事务管理,那么这些操作要么同时成功, 要么同时失败
事务的四大特征(ACID):
1. 原子性(Atomicity): 是不可分割的最小操作单位, 要么同时成功, 要么同时失败
2. 一致性(Consistency): 事务操作前后, 数据总量不变
3. 隔离性(Isolation): 多个事务之间相互独立
4. 持久性(Durability): 当事务提交或者回滚后, 数据库会持久化的保存数据
事务的隔离级别:
概念:
多个事务之间隔离的,相互独立的. 但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决一些问题.
引发的问题:
1). 脏读: 一个事务,读到另一个事务中还没有提交的数据
2). 不可重复读(虚读): 在同一个事务中,两次读到的数据不一样
3). 幻读: 一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
隔离级别:
-
read uncommitted : 读未提交
产生问题: 脏读, 不可重复读, 幻读
-
read committed : 读已提交(Oracle)
产生问题: 不可重复读, 幻读
-
repeatable read : 可重复读(MySQL默认)
产生问题: 幻读
-
serializable : 串行化
可以解决所有问题
注意:
隔离级别从小到大安全性越来越高, 但是效率越来越慢
至此,SQL常用知识点整理完毕!
275
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
