常用SQL语法整理

此篇帖子,主要是对SQL的一些基础语法知识进行整理

SQL分类:

  1. DDL(Data Definition Language)数据定义语言

    用来定义数据库对象:数据库, 表, 列等. 关键字:create, drop, alter等

  2. DMl(Data Manipulation Language)数据操作语言

    用来对数据库中表的数据进行增删改. 关键字:insert, delete, update等

  3. DQL(Data Query Language)数据查询语言

    用来查询数据库中表的记录(数据). 关键字:select, where等

  4. DCL(Data Control Language)数据控制语言

    用来定义数据库的访问权限和安全级别, 以及创建用户. 关键字:grant, revoke等

DDL数据定义语言

DDL是用来操作数据库, 表

操作数据库

  1. 创建(Create)

    # 语法格式:
# 创建数据库
create database 数据库名称;
# 创建数据库,判断是否存在,再创建
create database if not exists 数据库名称;
# 创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
# 举例:创建一个test数据库,判断是否存在,并设定字符集为gbk
create database if not exists test character set gbk;

  2. 查询(Retrieve)

    # 语法格式:
# 查询所有数据库的名称
show databases;
# 查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;

  3. 修改(Update)

    # 语法格式:
# 修改某个数据库的字符集
alter database 数据库名称 character set 字符集名称;

  4. 删除(Delete)

    # 语法格式:
# 删除数据库
drop database 数据库名称;
# 判断数据库是否存在,存在再删除
drop if exists 数据库名称;

  5. 使用数据库

    # 语法格式:
# 查询当前正在使用的数据库名称
select database();
# 使用数据库
use 数据库名称;

操作表

  1. 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＝＂表注释＂;

  2. Retrieve(查找)

    # 语法格式:
# 查询某个数据库中所有的表名
show tables;
# 查询表结构
desc 表名;

  3. Update(修改)

    # 语法格式:
# 修改表名
alter table 表名 rename to 新的表名;
# 修改表的字符集
alter table 表名 character set 字符集名称;
# 添加一列
alter table 表名 add 列名 数据类型;
# 修改列名称
alter table 表名 change 列名 新列名 新数据类型;
# 修改列的数据类型
alter table 表名 modify 列名 新数据类型;
# 删除列
alter table 表名 drop 列名;

  4. Detele(删除)

    # 语法格式:
# 删除表
drop table 表名;
drop table if exists 表名;

DML增删改表中的数据

  1. 添加数据

    # 语法格式:
insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
# 注意事项:
	1. 列名要和值一一对应
	2. 如果表名后面不定义列名,则默认给所有列添加值
	3. 除了数字类型,其他类型需要使用引号引起来(单双都行)

  2. 删除数据

    # 语法格式:
delete from 表名 [where 条件];
# 注意事项:
	1. 如果不添加条件,则删除表中的所有记录
	2. 如果要删除所有记录,还有以下的方式
		1) delete from 表名;  --不推荐使用.有多少条记录就会执行多少次删除操作
		2) truncate table 表名; --推荐使用.效率更高,先删除表,然后再创建一张一样的表

  3. 修改数据

    # 语法格式:
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. 管理用户

    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;

  2. 权限管理

    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'@'%';

约束

概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性

分类如下:

  1. 主键约束 primary key
​	1). 非空且唯一

​	2). 一张表只能有一个字段为主键

​	3). 主键就是表中记录的唯一标识

```mysql
create table stu(
			id int primary key auto_increment,-- 给id添加主键约束,并自动增长
			name varchar(20)
		);

```
  1. 非空约束 not null
​	某一列的值不能为null

```mysql
CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name为非空
		);
```
  1. 唯一约束 unique
​	某一列的值不能重复

```mysql
CREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- 手机号
		);
# 注意:唯一约束可以有null值,但是只能由一条记录为null
```
  1. 外键约束 foreign key
​	让表与表之间产生关系,从而保证数据的正确性

```mysql
create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);
```

事务

概念:

​ 如果一个包含多个步骤的业务操作, 被事务管理,那么这些操作要么同时成功, 要么同时失败

事务的四大特征(ACID):

1. 原子性(Atomicity): 是不可分割的最小操作单位, 要么同时成功, 要么同时失败
2. 一致性(Consistency): 事务操作前后, 数据总量不变
3. 隔离性(Isolation): 多个事务之间相互独立
4. 持久性(Durability): 当事务提交或者回滚后, 数据库会持久化的保存数据

事务的隔离级别:

​ 概念:

​ 多个事务之间隔离的,相互独立的. 但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决一些问题.

​ 引发的问题:

​ 1). 脏读: 一个事务,读到另一个事务中还没有提交的数据

​ 2). 不可重复读(虚读): 在同一个事务中,两次读到的数据不一样

​ 3). 幻读: 一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改

​ 隔离级别:

  1. read uncommitted : 读未提交

    ​ 产生问题: 脏读, 不可重复读, 幻读

  2. read committed : 读已提交(Oracle)

    ​ 产生问题: 不可重复读, 幻读

  3. repeatable read : 可重复读(MySQL默认)

    ​ 产生问题: 幻读

  4. serializable : 串行化

    ​ 可以解决所有问题

注意:

​ 隔离级别从小到大安全性越来越高, 但是效率越来越慢

至此,SQL常用知识点整理完毕!

# sql # 数据库 # mysql
Logo
永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

cover

MySQL基础知识：SQL语法

avatar 永洪数据分析社区
cover

数据湖数据仓库数据集市数据清理以及DataOps

avatar 永洪数据分析社区
cover

杜邦分析模型

avatar 永洪数据分析社区