SQL 学习指南：语法、分类与典型查询示例-洪萨配资

1、什么是SQL？

Structured Query Language:结构化查询语言

其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2、SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
可使用空格和缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
3种注释：
单行注释：-- 注释内容或 # 注释内容（mysql特有）
多行注释： /* 注释 */

3、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：操作数据库、表

操作数据库：CRUD

C(Create)：创建

创建数据库

createdatabase数据库名称;

创建数据库，判断不存在，再创建：

createdatabase[ifnotexists]数据库名称;

创建数据库，并指定字符集：

createdatabase数据库名称characterset字符集名;

练习：创建db4数据库，判断是否存在，并指定字符集为gbk

createdatabaseifnotexistsdb4charactersetgbk;

R(Retrieve)：查询
查询所有数据的名称：

showdatabases;

查询某个数据库的字符集：查询某个数据库的创建语句

showcreatedatabase数据库名称;

查看表信息
desc 表名

U(Update)：修改
修改数据库的字符集：

alterdatabasedb4charactersetutf8;

D(Delete)：删除
删除数据库：

dropdatabase数据库名称;

判断数据库存在，存在再删除：

dropdatabaseifexists数据库名称;

使用数据库

use数据库名称;

查询当前正在使用的数据库名称

selectdatabase();

2 操作表 CRUD

C(Create)：创建

-- 语法：createtable表名(列名1数据类型1， 列名2数据类型2，......列名3数据类型n);--- 注意：最后一列，不需要加逗号

数据类型：

int:整数类型
age int,
double：小数类型
score double(5,2)
date：日期，只包含年月日，yyyy-MM-dd
datetime：日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp：时间戳类型包含年月日时分秒 yyyy-MM-dd HH:mm:ss
varchar：字符串
name varchar(20)：姓名最大20字符
练习：创建表

createtablestudent(idint,namevarchar(32),ageint,scoredouble(4,1),birthdaydate,insert_timetimestamp);

复制表
– 仅复制表结构

createtableifnotexists表名like被复制的表名;

– 整体复制

createtableifnotexists表名asselect语句

R(Retrieve)：查询
- 查询某个数据库中所有的表名称

show tables;
```

查询表结构
– 仅查看表结构

desc表名;

-- 生成DDL

show create table 表名;
U(Update)：修改
修改表名

altertable表名renameto新表名;renametable表名to新表名

修改表的字符集

altertable表名characterset字符集名称;

查看某张表的字符集

showcreatetable表名;

添加一列

altertable表名add列名 数据类型;

修改列名称类型

-- 字段名和数据类型altertable表名 change 列名 新列名 新数据类型;-- 只修改数据类型altertable表名modify列名 新数据类型;

删除列

altertable表名drop列名;-- drop table/database table_name/database_name

D(Delete)：删除

droptable表名;droptableifexists表名;

DML:增删改表中数据

1.添加数据：
语法

insertinto表名(列名1，列名2，列名3，......,列名n)values(值1,值2,......值n);0|null,值2,......值n# insert select 将select查询出来的数据插入到目标表中insertinto表名selectstatement;

注意：

1.列名和值要一一对应;只需要对应字段数量相同2.如果表名后，不定义列名，则默认给所有列添加值```insertinto表名values(值1,值2,值3,......值n);```3.除了数字类型，其他类型需要使用引号（单双都可以）引起来

2.删除数据：
语法：

deletefrom表名[where条件]

注意：

1.如果不加条件，则删除表中所有记录2.如果要删除所有记录deletefrom表名;--不推荐使用。有多少条记录就会执行多少次删除操作truncatetable表名;--推荐使用，效率高 先删除表，然后再创建一张一样的表。

3.修改数据：

语法：

update表名set列名1=值1,列名2=值2,......[where条件];

注意：
1.如果不加任何条件，则会将表中所有记录全部修改。

DQL：查询表中的记录

1.语法：

select字段列表from表名列表where条件列表groupby分组字段having分组之后的条件orderby排序limit分页限定

2.基础查询

1.多个字段的查询

select字段名1，字段名2...from表名;

注意：如果查询所有字段，则可以使用*来替代字段列表。

2.去除重复：distinct

3.计算列

一般可以使用四则运算计算一些列值。（一般只会进行数值型的计算）

null参与的运算，计算结果都为null

4.起别名：
as

as也可以省略
3.条件查询

1.where子句后跟条件
2.运算符

>、<、<=、>=、=、<>、<=>BETWEEN...ANDIN(集合)LIKE：模糊查询 _:单个任意字符%：多个任意字符ISNULLISNOTNULL注意null和 空字符串''的区别andornot

示例

SELECT*FROMstudentWHEREage>20;SELECT*FROMstudentWHEREage>=20;SELECT*FROMstudentWHEREage=20;SELECT*FROMstudentWHEREage!=20;SELECT*FROMstudentWHEREage<>20;SELECT*FROMstudentWHEREage>=20&&age<=30;SELECT*FROMstudentWHEREage>=20ANDage<=30;SELECT*FROMstudentWHEREageBETWEEN20AND30;SELECT*FROMstudentWHEREage=22ORage=20ORage=25;SELECT*FROMstudentWHEREageIN(22,18,55);SELECT*FROMstudentWHEREenglishISNOTNULL;SELECT*FROMstudentWHERENAMELIKE'马%';SELECT*FROMstudentWHERENAMELIKE"_化";SELECT*FROMstudentWHERENAMELIKE"___";SELECT*FROMstudentWHERENAMELIKE"%景%"showvariableslike"%char%"

4.排序查询

- 语法：order by 子句
order by 排序字段1 排序方式1，排序字段2 排序方式2…
排序方式：
- ASC：升序，默认的
- DESC：降序
注意：
如果又多个排序条件，则当前边的条件值一样时，才会判断第二条件

5.聚合函数

将一列数据作为一个整体，进行纵向的计算
注意：聚合函数的计算，排除null值（可使用ifnull函数）

count:计算个数
一般选择非空的列：主键
例：SELECT COUNT(NAME) FROM student;
max:计算最大值
例：SELECT MAX(math) FROM student;
min:计算最小值
例：SELECT MIN(math) FROM student;
sum:计算和
例：SELECT SUM(english) FROM student;
avg:计算平均值
例：SELECT AVG(math) FROM student;

分组查询

语法：group by 分组字段
注意：
1.分组之后查询的字段：分组字段、聚合函数
2.where和having的区别？
where在分组之前进行限定，如果不满足条件，则不参与分组。
having在分组之后进行限定，如果不满足结果，则不会被查询出来
where后不可以跟聚合函数，having可以进行聚合函数的判断
例：
- 按照性别分组，分别查询男、女同学的平均分
  SELECT sex,AVG(math) FROM student GROUP BY sex;
- 按照性别分组，分别查询男、女同学的平均分，人数

SELECTsex,AVG(math),COUNT(id)FROMstudentGROUPBYsex;

按照性别分组，分别查询男、女同学的平均分，人数要求：分数不低于70分的人，不参与分组。

SELECTsex,AVG(math),COUNT(id)FROMstudentWHEREmath>70GROUPBYsex;

按照性别分组，分别查询男、女同学的平均分，人数要求：分数不低于70分的人，不参与分组，分组之后，人数大于2人。

SELECTsex,AVG(math),COUNT(id)FROMstudentWHEREmath>70GROUPBYsexHAVINGCOUNT(id)>2;

分页查询：

语法：limit 开始的索引，每页查询的条数
公式：开始的索引 = （当前的页码 -1） * 每页显示的条数
例：
- 每页显示3条记录

SELECT*FROMstudentLIMIT0,3;第一页SELECT*FROMstudentLIMIT3,3;第二页SELECT*FROMstudentLIMIT6,3;第三页

DCL:管理用户，授权

1.管理用户

添加用户：
- 关闭密码复杂验证

setglobalvalidate_password_policy=0;setglobalvalidate_password_length=1;

语法：

CREATEUSER'用户名'@'主机名'IDENTIFIEDBY'密码';

例子：

CREATEUSER'zhangsan'@'localhost'IDENTIFIEDBY'123';CREATEUSER'lisi'@'%'IDENTIFIEDBY'123';

删除用户：

语法：

DROPUSER'用户名'@'主机名';

例子：

DROPUSER'base'@'%';

修改用户密码：

语法：

-- 老版本的写法-- UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';SETPASSWORDFOR'用户名'@'主机名'=PASSWORD('新密码');

例子：

setpasswordfor'lisi'@'%'=password('234567');

查询用户：

1.切换到mysql数据库

USEmysql;

2.查询user表

select*fromuser;

通配符： % 表示可以在任意主机使用用户登录数据库

2.权限管理：

查询权限：
- 语法：

SHOW GRANTS FOR ‘用户名’@‘主机名’;

SHOW GRANTS FOR ‘lisi’@‘%’;

授予权限：
语法：

grant权限列表on数据库名.表名to'用户名'@'主机名'[withgrantoption];

例子：

-- 将表db3.account的SELECT ,DELETE, UPDATE权限授予用户'lisi'@'%'GRANTSELECT,DELETE,UPDATEONdb3.accountTO'lisi'@'%';-- 给zhangsan用户所有权限GRANTALLON*.*TO'zhangsan'@'localhost'

撤销权限：

语法：

revoke权限列表on数据库名.表名from'用户名'@'主机名';

例子：

-- 将用户'lisi'@'%'对于表db3.account的更新权限撤销REVOKE UPDATE ON db3.account FROM 'lisi'@'%';-- 给lisi用户撤销所有权限REVOKE ALL ON *.* FROM 'lisi'@'%';

多表查询

准备sql:

-- 创建部门表CREATETABLEdept(idINTPRIMARYKEYauto_increment,NAMEVARCHAR(20));-- 插入数据INSERTINTOdept(NAME)VALUES('开发部'),('市场部'),('财务部');-- 创建员工表CREATETABLEemp(idINTPRIMARYKEYauto_increment,NAMEVARCHAR(10),genderCHAR(1),-- 性别salaryDOUBLE,-- 工资join_dateDATE,-- 入职日期dept_idINT,FOREIGNKEY(dept_id)REFERENCESdept(id)-- 外键，关联部门表的主键);-- 插入数据INSERTINTOemp(NAME,gender,salary,join_date,dept_id)VALUES('孙悟空','男',7200,'2013-02-24',1),('猪八戒','男',3600,'2010-12-02',2),('唐僧','男',9000,'2008-08-08',2),('白骨精','女',5000,'2015-10-07',3),('蜘蛛精','女',4500,'2011-03-14',1);

笛卡尔积：

有两个集合A,B 取这两个集合的所有组成情况。
例如：
A：(a,b,c)
B：(1,2,3)
A与B作笛卡尔积—> a,1 a,2 a,3 b,1 b,2 b,3 c,1 c,2 c,3
要完成多表查询，需要消除无用的数据

1.内连接查询：
- 隐式内连接：使用where条件消除无用数据

-- 查询所有员工信息和对应的部门信息SELECT*FROMemp,deptWHEREemp.dept_id=dept.id;-- 查询员工表的名称，性别。部门表的名称SELECTemp.`NAME`,emp.gender,dept.`NAME`FROMemp,deptWHEREemp.dept_id=dept.id;-- 或者SELECTt1.`NAME`,t1.gender,t2.`NAME`FROMempast1,deptast2WHEREt1.dept_id=t2.id;

显式内连接：

--语法：select 字段列表 from 表名1 [inner] join 表名2 on 条件SELECT*FROMempINNERJOINdeptONemp.dept_id=dept.id;

2.外连接查询：

左外连接：

--语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；--查询的是左表所有数据以及其交集部分。

右外连接：

--语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；--查询的是右表所有数据以及其交集部分。

3.子查询：
查询中嵌套查询，称嵌套查询为子查询。

-- 查询工资最高的员工信息-- 1.查询最高的工资是多少 9000SELECTMAX(salary)FROMemp;-- 2.查询员工信息，并且工资等于9000的SELECT*FROMempWHEREemp.salary=9000;-- 一条sql就完成这个操作SELECT*FROMempWHEREemp.salary=(SELECTMAX(salary)FROMemp);

子查询不同情况:

子查询的结果是单行单列的：

--子查询可以作为条件，使用运算符去判断。 运算符：> >= < <= =--查询员工工资小于平均工资的人SELECT*FROMempWHEREemp.salary<(SELECTAVG(salary)FROMemp);

子查询的结果是多行单列的：

--子查询可以作为集合，使用in、not int--查询财务部和市场部所有员工信息SELECTidFROMdeptWHERE`NAME`='财务部'OR`NAME`='市场部';SELECT*FROMempWHEREdept_id=3ORdept_id=2;--使用子查询SELECT*FROMempWHEREdept_idin(SELECTidFROMdeptWHERE`NAME`='财务部'OR`NAME`='市场部');

子查询的结果是多行多列的

--子查询可以作为一张虚拟表参与查询--查询员工入职日期是2011-11-11日之后的员工信息和部门信息-- 子查询select*fromdept t1join(select*fromempwhereemp.join_date>'2011-11-11')t2wheret1.id=t2.dept_id;--普通内连接查询select*fromemp t1,dept t2wheret1.dept_id=t2.idandt1.join_date>'2011-11-11'