SQL语句的整理以及总结
 
 
目录
 

 
 
SQL语句的整理以及总结
   
目录
$\color{lime}{SQL语句的基本类型}$
$\color{lime}{特殊关键字}$
$\color{lime}{创建/插入/删除/修改}$
$\color{lime}{SQL查询语句-单表查询}$
$\color{lime}{SQL查询语句-多表查询}$
$\color{lime}{附加运算关键字}$
$\color{lime}{聚集函数运算}$
 
 
 
 
 
$\color{lime}{SQL语句的基本类型}$
 
类型
含义
char(n)
存放固定长度的字符串，用户指定长度为n。如果没有使用n个长度则会在末尾添加空格。
varchar(n)
可变长度的字符串，用户指定最大长度n。char的改进版，大多数情况下我们最好使用varchar。
int
整数类型
smallint
小整数类型
numeric(p,d)
定点数，精度由用户指定。这个数有p位数字（包括一个符号位）d位在小数点右边。
real ,double precision
浮点数和双精度浮点数。
float(n)
精度至少位n位的浮点数
 
$\color{lime}{特殊关键字}$
 
类型
含义
实例
primary key
主键
primary key（student_id）
foreign key references
外键，括号中为外键，references后为外键的表
foreign key(course_id) references Course
not null
不为空，前面为属性的定义
name varchar(10) not null
 
$\color{lime}{创建/插入/删除/修改}$
 
类型
含义
create table
创建一张表
insert into…values
向表中插入一条信息
delete from
从表中删除一条信息
update…set…where
在where的位置，更新内容为set的值
drop table
删除表
alter table…add
向表中添加某个属性
alter table…drop
删除某个属性
truncate
清空内容
 
$\color{lime}{SQL查询语句-单表查询}$
 
类型
含义
select
表示要查找表所含有的属性
from
表示要操作的表
where
判断条件，根据该判断条件选择信息
distinct
表示将结果去重
all
表示不去重
and
在where后使用and表示将判断条件连接起来
or
在where中使用or表示判断条件多选一
not
在where中使用not表示判断条件相反
 
$\color{lime}{SQL查询语句-多表查询}$
 
类型
含义
A,B
在from后面通过逗号连接多张表，表示将这些表进行笛卡尔积运算
natural join
将natural join关键字前后的两张表进行自然连接运算
left outer join
左外连接，以左边为基准进行连接，右边没有的用NULL代替
right outer join
右外连接，以右边为基准进行连接，左边没有的用NULL代替
full join
全然外连接，将两表完全合并，没有的用NULL代替
join/inner join
内连接，只连接两表皆有，其他删除
corss join
交叉连接，结果为两表的笛卡尔积
A join B using(c)
将A和B通过c属性自然连接
 
$\color{lime}{附加运算关键字}$
 
类型
含义
as
将as前的关系起一个别名，在此语句中可以用别名来代指这个表
*
在select中表示找出这个表所有的属性
order by
让查询结果中的信息按照给定的属性排序（默认升序，上小下大）
desc
在排序后使用，表示采用降序
asc
在排序后使用，表示采用升序
between
在where中使用between表示一个数在两个数值之间取值
not between
between的反义词，在两个数之外取值
union/union all
将连个SQL语句做并运算，并且自动去重，添加all表示不去重
intersect/intersect all
将两个SQL语句做交运算，并自动去重，添加all表示不去重
except/except all
将两个SQL语句做差运算，并且自动去重，添加all表示不去重
is null
在where中使用is null表示这个值是空值
is not null
在where中使用is not null表示这个值不是空值
 
$\color{lime}{聚集函数运算}$
 
类型
含义
avg
平均值
min
最小值
max
最大值
sum
总和
count
计数
distinct
表示将后面的属性去重
group by
将在group by上取值相同的信息分在一个组
having
对group by产生的分组进行筛选，可以使用聚集函数

Select 查询
 
查询表中的所有数据：SELECT * FROM 表名；
 查询表中的A1,A2列的数据：SELECT A1,A2 FROM 表名；
 
where 条件语句
 
查询表中A1大于1的所有数据：SELECT * FROM 表名 WHERE A1>1;
 查询表中A1大于1且A2大于1的所有数据：SELECT * FROM 表名 WHERE A1>1 AND A2>1;
 
子查询语句
 
id的结果从另一个语句中获取：SELECT * FROM A WHERE Id in (SELECT consignmentid FROM B);
 
ORDER BY 排序（desc降序 asc升序）
 
order by语句放在所有关键字以后，如条件语句（where），分组语句（group by）
 根据字段CreationTime 升序：SELECT * FROM testconsignment ORDER BY CreationTime ASC;
 根据字段CreationTime 升序：SELECT * FROM testconsignment ORDER BY CreationTime DESC;
 语句放在分组和条件语句之后：SELECT count(*),LoginAccountName FROM driver GROUP BY LoginAccountName,IsStart HAVING IsStart=1 ORDER BY CreationTime DESC;
 
GROUP BY 分组查询
 
通过某字段或多个字段来分组，分组查询必须有如“count()”类似的函数，用于对结果集进行分组（例子：比如学习按照性别来统计人数，男性：30；女性：30用sql表示就是：
 SELECT count(),sex FROM student GROUP BY sex;
 分组查询：SELECT count(),LoginAccountName FROM driver GROUP BY LoginAccountName
 分组查询带条件就不再用“where”，而是用HAVING：
 SELECT count(*),LoginAccountName FROM driver GROUP BY LoginAccountName,IsStart HAVING IsStart=1 ORDER BY CreationTime DESC;
 
Having 条件关键字
 
用于有GROUP BY 分组查询后，用的条件语句：
 SELECT count(*),LoginAccountName FROM driver GROUP BY LoginAccountName,IsStart HAVING IsStart=1 ORDER BY CreationTime DESC;
 
Left Join…On左关联/ Right Join…On右关联
 
左关联：返回左表（table1）所有行，即使没有匹配上右边表（table2）的的数据；
 SELECT * FROM table1 A LEFT JOIN table2 B ON A.AccountName = B.LoginAccountName;
 右关联：关键字会右表 (table2) 那里返回所有的行，即使在左表 (table1) 中没有匹配的行
 SELECT * FROM table1 A LEFT JOIN table2 B ON A.AccountName = B.LoginAccountName;
 
sql书写顺序
 

 以下两种表达查询结果一样：（以PlateNumber，CreateTime分组查询，并筛选条件CustomerId ，CreateTime，InterfaceType，再排序且聚合函数COUNT(PlateNumber)大于1的，最后取出两条数据）
 
SELECT PlateNumber,CreateTime,COUNT(PlateNumber) as num FROM
 trackconsumelog WHERE CustomerId = “dxj00024” AND
 CreateTime>“2020-10-13” AND InterfaceType=1 GROUP BY
 PlateNumber,CreateTime HAVING COUNT(PlateNumber)>1 order by
 CreateTime desc limit 2；
select * from ( SELECT PlateNumber,CreateTime,count(PlateNumber)
 as num,InterfaceType FROM trackconsumelog WHERE CustomerId =
 “dxj00024” and CreateTime >“2020-10-13” InterfaceType=1 group by
 PlateNumber,CreateTime order by CreateTime desc ) tt where tt.num >
 1 limit 2;

SQL常用的一些关键字
 
1、distinct关键字
 
显示没有重复记录的商品名称，商品价格和商品类别列表
 
select distinct ware_name,price from t_ware;
 
2、使用计算列
 
查询所有商品价格提高20%后的价格
 
select ware_id,ware_name,price*1.2 from t_ware'
 
3、列的别名
 
a) 不使用as
 
select ware_id,ware_name,price*1.2 price_raise 
from t_ware;
 
b)使用as
 
select ware_id,ware_name,price*1.2 price_raise 
from t_ware;
 
4、使用逻辑表达式
 
 a)not 显示商品价格不大于100的商品
 
select ware_id,ware_name,price,category_id 
from t_ware 
where not price>100;
 
 b)or 显示商品类别编号为5或6或7的商品
 
select ware_id,ware_name,price,category_id 
from t_ware 
where category_id=5 or category_id=6 
or category_id=7;
 
 c)and 显示商品价格大于100且商品类别编号为5的商品
 
select ware_id,ware_name,price,category_id 
from t_ware 
where not price>100 and category_id = 5;
 
5、使用between关键字
 
显示商品价格在200元至1000元之间的商品（留心一下，是半开区间还是封闭区间？）
 
select ware_id,ware_name,price,category_id 
from t_ware 
where price between 200 and 1000;
 
6、使用in关键字
 
显示商品类别为5,6,7且价格不小于200元的商品
 
select ware_id,ware_name,price,category_id 
from t_ware 
where category_id in (5,6,7) and price>=200;
 
7、使用like子句进行模糊查询
 
 a)%（百分号）表示0到n个任意字符
 
select ware_id,ware_name,price,category_id 
from t_ware 
where ware_name like '%纯棉%';
 
 b)_（下划线）表示单个的任意字符
 
select ware_id,ware_name,price,category_id 
from t_ware 
where ware_name like  '%长袖_恤%';
 
8、转义字符escape的使用
 
select ware_id,ware_name,price,category_id 
from t_ware 
where ware_name like '%\%%' escape '\';
 
9、使用order by给数据排序
 
	select * from t_ware_category 
	where parent_id = 0 order by ware_id ;
	--------
	select * from t_ware_category 
	where parent_id = 0 order by ware_id asc;
	---------
	select * from t_ware_category
	where parent_id = 0 order by ware_id desc ;
 
rownum
 
 a)查询前20条商品记录
 
select ware_id,ware_name,price 
from t_ware 
where rownum <= 20;
 
 b)查询第11条至第20条记录
 
select ware_id,ware_name,price
from t_ware 
where rownum<=10 and ware_id 
not in(select ware_id from t_ware where rownum<=10);
 
10、常用统计函数
 
a)sum()返回一个数字列或计算列的总和
select sum(price) from t_ware;
 
b)avg()对一个数字列或计算列球平均值
c)min()返回一个数字列或一个数字表达式的最小值
d)max()返回一个数字列或一个数字表达式的最大值
e)count()返回满足select语句中指定的条件的记录值
11、多表查询和笛卡尔乘积
 
查询商品编号，商品名称，商品价格和商品类别名称
 
select 
t_ware.ware_id, t_ware.ware_name, t_ware.price ,t_ware_category_name 
from t_ware, t_ware_category  
where t_ware.category_id=t_ware_category.category_id;
 
使用join
 
 a)左连接
 
select 
t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name 
from t_ware 
left join t_ware_category 
on t_ware.category_id=t_ware_category.category_id;
 
select w.ware_id,w.ware_name,w.price,wc.category_name 
from t_ware w 
left join t_ware_category wc 
on w.category_id=wc.category_id;
 
 b) 右连接
 
select t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name
from t_ware 
left join t_ware_category 
on t_ware.category_id=t_ware_category.category_id;
 
12、使用union
 
select ware_id,ware_name 
from t_ware 
where ware_name like '%T恤%' 
union 
select ware_id,ware_name
 from t_ware 
 where ware_name like '%手提包%'
 
13、使用group by
 
 a)统计每个二级类别下有多少商品，以及商品总价值
 
select w.category_id,wc.category_name,
count(w.ware_id),sum(w.price) 
from t_ware w 
left join t_ware_category wc 
on w.category_id=wc.category_id 
group by w.category_id,wc.category_name;
 
 b) 统计每个一级类别下有多少商品，以及商品总价值
 
select wc2.category_id,wc2.category_name,sum(w.price) 
from t_ware w 
left join t_ware_category wc 
on w.category_id=wc.category_id 
left join t_ware_category wc2 
on wc.parent_id=wc2.category_id 
group by wc2.category_id,wc2.category_name;
 
14、使用having对结果进行筛选
 
–举例子说明：查询table表查询每一个班级中年龄大于20，性别为男的人数
 
select COUNT(*)as '>20岁人数',classid  
from Table1 
where sex='男' 
group by classid,age having age>20 
 
需要注意说明：当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
执行where子句查找符合条件的数据；
使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。

--语 句　　　　　　　　　　　　　　功 能
--数据操作
SELECT　　　--从数据库表中检索数据行和列
INSERT　　　--向数据库表添加新数据行
DELETE　　　--从数据库表中删除数据行
UPDATE　　　--更新数据库表中的数据 
--数据定义 
CREATE TABLE　　--创建一个数据库表
DROP TABLE　　 --从数据库中删除表 
ALTER TABLE　　 --修改数据库表结构
CREATE VIEW　　 --创建一个视图 
DROP VIEW　　 --从数据库中删除视图
CREATE INDEX　　--为数据库表创建一个索引
DROP INDEX　　 --从数据库中删除索引 
CREATE PROCEDURE　 --创建一个存储过程 
DROP PROCEDURE　　--从数据库中删除存储过程
CREATE TRIGGER　　--创建一个触发器 
DROP TRIGGER　　--从数据库中删除触发器
CREATE SCHEMA　　--向数据库添加一个新模式
DROP SCHEMA　　 --从数据库中删除一个模式
CREATE DOMAIN　　--创建一个数据值域
ALTER DOMAIN　　--改变域定义
DROP DOMAIN　　 --从数据库中删除一个域
--数据控制 
GRANT　　　--授予用户访问权限
DENY　　　--拒绝用户访问 
REVOKE　　　--解除用户访问权限
--事务控制
COMMIT　　　--结束当前事务
ROLLBACK　　 --中止当前事务 
SET TRANSACTION　　--定义当前事务数据访问特征
--程序化SQL 
DECLARE　　　--为查询设定游标 
EXPLAN　　　--为查询描述数据访问计划
OPEN　　　--检索查询结果打开一个游标
FETCH　　　--检索一行查询结果 
CLOSE　　　--关闭游标
PREPARE　　　--为动态执行准备SQL 语句 
EXECUTE　　　--动态地执行SQL 语句
DESCRIBE　　 --描述准备好的查询 
---局部变量
declare @id char(10) 
--set @id = ’10010001’ 
select @id = ’10010001’

---全局变量 
---必须以@@开头