测试环境:https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc
1. 基本概念
表中的一行为一条数据,一列即为一个字段(可以认为相当于java中的属性)
1.1. SQL的种类
- DDL(data definition language):数据定义语言
- create:创建表(不局限于表)
- join:关联多个表
- drop:删除表(不局限于表)(不可回滚)
- alter:修改表的定义(不局限于表)
- truncate:删除数据,但不删除表定义(不可以指定行)(不可回滚)
- DML(data manipulation language):数据操作语言
- select:查询
- insert:添加数据
- update:修改数据
- delete:删除数据,但不删除表定义(可以指定行)
- DCL(data control language):数据控制语言
- grant:添加权限
- revoke:移除权限
- begin:事务开始
- commit:事务提交
- rollback:事务回滚
2. DML
2.1. select
SQL的执行顺序
-
from, join(inner join, left join ...),on
生成一个临时表
-
where
筛选from指定的数据元
-
group by
分组,分组列意外的数据只能在聚合函数中使用
-
having
对于分组后的数据筛选
-
select
抽出需要的列
-
distinct
去重
-
order by
对于结果排序
-
limit
截取部分数据
2.1.1. 基础版
-
将Customers表中的所有列无条件地取出
-- from用来指定数据元(意为取什么表的数据) select * from Customers;
-
将Customers表中的指定列无条件地取出
-- 可以指定多个列 select customerid, customername, contactname, address from Customers
2.1.2. where
-
将Customers表中的指定列的有条件地取出
-- 单一条件 -- 客户id大于等于40 select customerid, customername, contactname, address from Customers where customerid >= 40; -- 客户id等于40 select customerid, customername, contactname, address from Customers where customerid = 40; -- 客户id不等于40 select customerid, customername, contactname, address from Customers where customerid != 40; -- 多条件结合1 select customerid, customername, contactname, address from Customers where customerid >= 40 and customerid <= 60; -- 多条件结合2 select customerid, customername, contactname, address from Customers where customerid between 40 and 60; -- 多条件结合3 select customerid, customername, contactname, address from Customers where customerid >= 40 or customerid <= 60; -- 多条件结合4 select customerid, customername, contactname, address from Customers where customerid >= 40 or customerid >= 30 and customerid = 20; -- 多条件结合5(条件可以指定多个不同的列)(实际上也可以不使用select中指定的列) select customerid, customername, contactname, address, country from Customers where customerid >= 40 and country = 'USA'; -- 特殊条件 略(见统合) -- 以上条件都是精准的,还有一种模糊的条件 select customerid, customername, contactname, address from Customers where customername like 'M%'; -- %:占位符,代表0~N个任意字符,M%:M后有0~N个任意字符的都符合条件
2.1.3. order by
-
将select的结果排序
-- 单一条件升序 select customerid, customername, contactname, address from Customers where customerid >= 40 or customerid <= 60 order by customerid; -- 不指定ASC(升序)或者DESC(降序)时,默认ASC -- 单一条件降序 select customerid, customername, contactname, address from Customers where customerid >= 40 or customerid <= 60 order by customerid desc; -- 多条件排序 select customerid, customername, contactname, address, country from Customers where customerid >= 40 order by country desc, customername; -- 字典序 ascii
2.1.4. group by
-
将数据分组
-- 不使用group by,不分组? select count(0) as "record数" from Customers where customerid >= 40; -- 根据单一列分组 select count(0) as "record数", country from Customers where customerid >= 40 group by country; -- 分组的意义:统计每个组的数据 -- 根据多列分组 select count(0) as "record数", country, city from Customers where customerid >= 40 group by country, city;
2.1.5. 聚合函数
-
统计分组后各组的数据
-- 统计每组数据量 select country, city, count(0) as "人数" from Customers group by country, city; -- 统计每组指定列最大值和最小值 select categoryid, max(price) as "最大价格", min(price) as "最小价格" from Products group by categoryid; -- 统计每组指定列的平均值 select categoryid, avg(price) as "最大价格" from Products group by categoryid; -- 统计每组指定列的合计 select categoryid, sum(price) as "合计价格" from Products group by categoryid;
2.1.6. having
-
对于分组后的数据进行筛选
-- 取出合计价格大于50的组(商品表)NG select categoryid, sum(price) as "sum_price" from Products group by categoryid where sum_price >= 50; -- 对于已经分组后的数据,不能使用where作为筛选条件 -- 取出合计价格大于50的组(商品表)OK select categoryid, sum(price) as "sum_price" from Products group by categoryid having sum_price >= 50; -- 以上补充,无别名写法 select categoryid, sum(price) as "sum_price" from Products group by categoryid having sum(price) >= 50; -- 并不推荐,只了解就好
2.1.7. 子查询
-
在原有查询基础上再查询(将内层查询的结果作为外层查询的数据元)
-- 取出最大价格和最小价格差值最大的组(商品表) select categoryid, max(max_price - min_price) as "最大差价" from (select categoryid, max(price) as "max_price", min(price) as "min_price" from Products group by categoryid);
2.1.8. 表关联
-
cross join
-- 将两个及以上表做笛卡尔积(A表的每一条数据都与B表的所有数据匹配一次) select * from orders cross join shippers; -- 虽说确实得到了结果,但是稍微观察后我们可以发现,结果中有很多无效的数据。
-
inner join
-- 数据准备 insert into shippers values (4, 'Test Shipper1', null); -- 为解决cross join的无效解问题 select * from orders a inner join shippers b on a.shipperid = b.shipperid; -- 如何知道结果有多少条数据(用sql)
-
outer join
-- left outer join select * from orders a left outer join shippers b on a.shipperid = b.shipperid; -- 左表为主表保留未匹配的数据 -- right outer join select * from orders a right outer join shippers b on a.shipperid = b.shipperid; -- 右表为主表保留未匹配的数据 -- full outer join select * from orders a full outer join shippers b on a.shipperid = b.shipperid; -- 以上两种的交集 -- ※outer可以省略 -> left join, right join, full join
2.1.9. 补充
-
其他语法
-- 查询指定列为null的数据 select * from shippers where phone = 'null'; -- NG select * from shippers where phone is null; -- OK -- 查询指定列不为null的数据 select * from shippers where phone is not null; -- 查询结果去重 select distinct country from customers; -- distinct必须放在所有指定列的最前面
2.2. insert
-
不指定列名
-- 不指定列名,在指定表中插入一条数据,必须传入所有列的值 insert into shippers values (5, 'Test Shipper2', '');
-
指定列名
-- 不指定列名,在指定表中插入一条数据,只需要传入指定列的值 insert into shippers (shipperid, shippername) values (6, 'Test Shipper2');
2.3. update
-
不使用条件语句
-- 不使用条件语句的update update shippers set shippername = 'Test Shipper1';
-
使用条件语句(推荐)
-- 使用条件语句的update update shippers set shippername = 'Test Shipper1' where shipperid = 4;
2.4. delete
-
不使用条件语句
-- 不使用条件语句的delete delete from shippers;
-
使用条件语句(推荐)
-- 使用条件语句的delete delete from shippers where shipperid = 4;