常用SQL语句总结

一、SQL 语言是什么？

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容， 并删除条目等操作。 Create, Read, Update, and Delete 通常称为CRUD操作。

各数据库厂商都支持ISO的SQL标准(普通话），同时各数据库厂商在标准的基础上做了自己的扩展（方言）

二、SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。

sql语句以;结尾
mysql中的关键字不区分大小写

1. DDL

1.1 操作数据库

创建数据库

CREATE DATABASE语句用于创建新的数据库，下面列举了三种创建方式：

//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

编码方式有gb2312、utf-8、gbk、iso-8859-1 等，最常用的utf-8

查看数据库

查看当前数据库服务器中的所有数据库

show databases;

查看单条数据库详细的定义信息

show create database 数据库名;

修改数据库

alter database 数据库名 character set 编码方式

删除数据库

drop database 数据库名

查看当前使用的数据库

Select database();

切换数据库（可以不加;）

use 数据库名

1.2 操作表

创建新表

CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);

说明:

• 表名，列名是自定义，多列之间使用逗号间隔，最后一列的逗号不能写

• [约束] 可有可无

示例：

CREATE TABLE Employees(
    id INT ,
    age INT ,
    first VARCHAR(255),
    last VARCHAR(255)
)

常用数据类型

int：整型，固定长度为11位

double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为 999.99；默认支持四舍五入

char：固定长度字符串类型； char(10) 'aaa ’ 占10位

varchar：可变长度字符串类型； varchar(10) ‘aaa’ 占3位

text：字符串类型，比如小说信息；

blob：字节类型，保存文件信息(视频，音频，图片)；

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

查看当前数据库中所有表格

show tables;

查看表的字段信息

desc 表名；

查看表格的创建细节

show create table 表名;

修改表的字符集

alter table 表名 character set 编码方式

修改表名

alter table 旧表名 rename 新表名;

删除表格

drop table 表名;

1.3 操作列

在表格中增加一列

alter table 表名 add 新列名 新的数据类型;

修改列

alter table 表名 change 旧列名 新列名 新的数据类型;
	或者
alter table 表名 modify 旧列名 新的数据类型;

删除列，一次只能删除一列

alter table 表名 drop 列名

2. DML

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。 主要包括：INSERT 、UPDATE、 DELETE

（1）插入操作

insert into 表名(列名) values(数据值);

注意:

• 多列和多个列值之间使用逗号隔开

• 列名与列值的类型、个数、顺序要一一对应

• 非数值的列值两侧需要加单引号。（插入的日期和字符一样，都使用引号括起来。）

• 当给所有列添加数据的时候，添加数据的时候可以将列名省略此时列值的顺序按照数据表中列的顺序执行

• 可以添加同时添加多行数据

  insert into 表名(列名) 
  values(第一行数据),
  (第二行数据),
  (),
  (); 
  

• 参数值不要超出列定义的长度。 否则会爆 Data too long 的错误

• 如果插入空值，请使用null

（2）修改（更新）操作：UPDATE

​ 语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值

（3）删除操作：DELETE

​ 语法 ： DELETE from 表名 【WHERE 列名=值】

• DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
• TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。 删除的数据不能找回。执行速度比DELETE快。

注意

• 为空的条件：列名 is null or 列名=’’ （两个单引号表示空字符串，null表示还未赋值）

• 获取当前系统时间：select now();

• 使用quit指令退出系统

3. DCL

1、创建用户：

（1）create user 用户名@指定ip identified by 密码;

create user test123@localhost IDENTIFIED by 'test123' 

（2）create user 用户名@客户端ip identified by 密码; 指定IP才能登陆

create user test456@10.4.10.18 IDENTIFIED by 'test456'

（3） create user 用户名@‘% ’ identified by 密码 任意IP均可登陆

create user test7@'%' IDENTIFIED by 'test7'

2、用户授权：

grant 权限1,权限2,…,权限n on 数据库名.* to 用户名@IP;
给指定用户授予指定指定数据库权限（*表示所有表格）

grant select,insert,update,delete,create on
chaoshi.* to 'test456'@'127.0.0.1';

grant all on *.* to 用户名@IP 给指定用户授予所有数据库所有权限

grant all on *.* to 'test456'@'127.0.0.1'

3、用户权限查询：

show grants for 用户名@IP;

show grants for 'root'@localhost;

4、撤销用户权限：

revoke 权限1,权限2,…,权限n on 数据库名.* from 用户名@IP;

REVOKE SELECT ON *.* FROM 'test456'@'127.0.0.1'；

5、删除用户：

drop user 用户名@IP;

drop user test123@localhost

注意 用户名加不加单引号都不影响

6、 查询所有用户

SELECT user FROM mysql.user;

4. DQL

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。 查询返回的结果集是一张虚拟表(视图)。

查询关键字：SELECT 语法： SELECT 列名 FROM 表名 【WHERE --> GROUP BY–>HAVING–> ORDER BY】

* 表示所有列

SELECT 要查询的列名称
FROM 表名称
WHERE 限定条件 /*行条件*/
GROUP BY grouping_columns /*对结果进行分组*/
HAVING condition /*分组后进一步筛选*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start, row_count /*结果限定*/

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

• 比较运算：=、!=、<>、<、<=、>、>=;
• 范围查询：BETWEEN…AND； （包含临界值）
• 集合查询：IN(set)；
• 空值查询：IS NULL；
• 逻辑查询：AND；OR； NOT；
• 字符串匹配查询：LIKE

示例

查询姓名不为null的学生记录

SELECT * FROM stu WHERE NOT sname IS NULL;
	或者
SELECT * FROM stu WHERE sname IS NOT NULL;

模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

语法: 列名 like ‘表达式’ //表达式必须是字符串

通配符:

_(下划线): 任意一个字符

%：任意0~n个字符,‘张%’

字段控制查询

(1)去除重复记录 去除重复记录（两行或两行以上记录中系列的上的数据都相同），那么想去除重复记录，需要使用 DISTINCT：

SELECT DISTINCT 列名 FROM 表名;

把NULL转换成数值0的函数IFNULL(属性, 0)

排序

语法： order by 列名 asc/desc

asc 升序 desc 降序 默认不写的话是升序

示例

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

SELECT * FROM emp
ORDER BY sal DESC,empno ASC;

聚合函数

聚合函数是用来做纵向运算的函数：

COUNT(列名)：统计指定列不为NULL的记录行数； 列名为*号则表示记录数，若指定列，则结果表示该列不为null的个数

MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

示例：

SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

补充:

多列分组 ：统计出stu表中每个班级的男女生各多少人

select gradename,gender ,count(*) from stu 
group by gradename,gender

HAVING子句

HAVING是配合Group使用的，不会单独出现！

注：having与where的区别:

• having是在分组后对数据进行过滤,where是在分组前对数据进行过滤
• having后面可以使用分组函数(统计函数) ，而where后面不可以使用分组函数。 WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

示例：

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 9000;

LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。

格式1：limit 开始下标,显示条数；//开始下标从0开始

格式二：limit 显示条数；//表示默认从0开始获取数据

实现分页查询：

pageIndex 页码值 pageSize 每页显示条数

limit (pageindex-1)*pagesize,pagesize;

总结

查询语句书写顺序：select – from- where- groupby- having- order by-limit

查询语句执行顺序：from - where -group by -having - select - order by-limit

---