补充记录sql基础语法（未完待续8/25）

好久没弄这个了，顺便整理下，之前面试有被问到

一、distinct

1）作用

去除重复的项

2）语法

select distinct 列名字  from  表名;
1

• 举例

使用前:(这里可以看到3重复出现)
select 客户端ID from Orders;
1
2

使用后:（这里可以看到只出现了一次3）
select distinct 客户ID from Orders;
1
2

3）缺点

当选出两列，第一列无去重，但是第二列的时间戳是唯一的话，也是有可能的

二、top的用法

1）作用

获取前几名或是前百分比的行数据

2）语法

select top 数字 列的名字  
from  表的名字;
或
select top 百分比  列的名字  
from  表的名字;
1
2
3
4
5

3）使用举例

1）获取表前3条
select top 3 * 
from Customers;
2）获取表后40%的记录(后表示用倒序)
select top 40 Percent * 
from Customers order by 客户ID  desc;
3）获取随机40%的记录
select top 40 Percent * 
from Customers 
order by 客户ID NEWID();
1
2
3
4
5
6
7
8
9
10

三、and和or

• 使用举例

1)and举例，or也一样
select * from Customers 
where 姓名='张三' and 城市='北京'
2)and和or联合使用(条件用括号隔离起来)
select * from Orders 
where (客户端ID=3 OR 客户ID=1) AND 发货ID=4
1
2
3
4
5
6

四、group by

1）作用

与聚合函数相结合，根据一个或多个列对比结果集进行分组

2）语法

select 列的名字，
聚合函数（列名）
FROM  表名
WHERE  列名  运算符  值
GROUP BY 列名
1
2
3
4
5

3）使用举例

查询所有城市数据
1

1)查询居住在各个城市的客户分别有多少个？
SELECT 城市,
COUNT(*) AS 客户数量
FROM Customers
GROUP BY 城市
2)不同城市最大的客户ID是多少？
SELECT 城市,
MAX(列名，比如说客户ID)  AS 最大的客户ID
FROM Customers
GROUP BY 城市
1
2
3
4
5
6
7
8
9
10

1）统计居住在各个城市的客户数量

2）查看不同城市里面最大的客户ID是多少

五、having

1）作用

主要是对分组后的数据进一步的过滤，找符合分组条件的记录
(having一般和group by联合使用，通常用在聚合函数前面，对分组后的数据进行过滤，（MAX、MIN、COUNT、SUM等)

2）语法（顺序：在group by之后，order by之前）

SELECT column1,column2
FROM table1,table2
WHERE [conditions]
GROUP BY column1,column2
HAVING [conditions]
ORDER BY column1,column2
1
2
3
4
5
6

3）使用举例

1)查询城市人数大于或等于2的所有记录
SELECT 城市,COUNT(*) 人数  FROM Customers
GROUP BY 城市
HAVING COUNT(1) >1
2)--统计员工人数大于300的部门
select 
  deptName, 
  count(deptName) 
from 
  user 
having 
  count(deptName) > 300
1
2
3
4
5
6
7
8
9
10
11
12

六、order by

1）作用

对结果排序

2）语法

SELECT 列名字 FROM 表名
ORDER BY 列名字 ASC|DESC
1
2

3）使用举例

1）升序
SELECT * FROM Customers
ORDER BY 省份 ASC
2）降序
SELECT * FROM Customers
ORDER BY 省份 DESC
3）混合升序和降序
SELECT * FROM Customers
ORDER BY 省份 ASC, 姓名DESC
1
2
3
4
5
6
7
8
9

1）升序

2）降序

七、LIKE

1）作用

在WHERE字句中用来指定模式来搜索数据，通常称为模糊查找

2）语法

SELECT column1,column2,...
FROM table_name 
WHERE columnN LIKE 模式;

两个常用通配符与LIKE运算符一起
% : 百分号表示零个、一个或多个字符
_ : 下划线表示单个字符
1
2
3
4
5
6
7

• LIKE运算符使用方法

WHERE 地址 LIKE 'a%'  查找以a开头的任何值
WHERE 地址 LIKE '%a'  查找以a结尾的任何值
WHERE 地址 LIKE '%or%'  任意位置查找含有or的值
WHERE 地址 LIKE '_r%'  在第二个位置查找任何具有r的值
WHERE 地址 LIKE 'a_%_%' 查找以a开头且长度为3个字符的值
WHERE 地址 LIKE 'a%o'  找到以a开头且以o结尾的值
1
2
3
4
5
6

八、IN

1）作用

允许在WHERE字句中指定多个值，可以理解为OR条件的简写

2）语法

1）语法一
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,...);
2）语法二
SELECT column_name(s) FROM table_name
WHERE column_name IN (SELECT STATEMENT);
1
2
3
4
5
6

3）使用例子举例

1）查找省份位于'上海市'，‘北京市’，‘广东省’的所有用户
SELECT * FROM Customers
WHERE 省份 IN ('上海市' , '北京市' ,'广东省');
2）查找不在‘浙江省’，‘上海市’或‘北京市’中的所有客户
SELECT * FROM Customers
WHERE 省份 NOT IN ('浙江省','上海市','北京市');
3）查找来供应商‘Suppliers’和客户都来自同一城市的客户信息
SELECT * FROM Customers
WHERE 城市 IN (SELECT 城市 FROM Suppliers);
1
2
3
4
5
6
7
8
9

九、BETWEEN AND

1）作用

查找介于两个值之间的所有数据，包括开始值和结束值，等价于>= and <=

2）语法

SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2;
1
2

3）举例使用

1）查找价格介于30到60的商品
SELECT * FROM Products
WHERE 价格 BETWEEN 30 AND 60;
2）查找价格不介于30到60之间的商品
SELECT * FROM Products
WHERE 价格 NOT BETWEEN 30 AND 60;
3）日期边界问题(查找订单日期,记住，默认日期是00:00:00)
SELECT * FROM Orders
WHERE 订单日期 BETWEEN '2018-06-28' AND '2018-09-28';
1
2
3
4
5
6
7
8
9

十、UNION

1）作用

组合两个或更多SELECT语句的结果集

2）使用前提

1）UNION中的每个select语句必须具有相同的列数
2）这些列也必须具有相似的数据类型
3）每个SELECT语句中的列也必须以相同的顺序排列

3）语法

SELECT column_name(s) FROM table1
UNION (ALL)
SELECT column_name(s) FROM table2;
1
2
3

4）使用举例

• 数据源（两张表）
  
  

1）选择两表不同的城市（只有不同的值）
SELECT 城市 FROM customers_bak
UNION   #对两个结果去重
SELECT 城市 FROM Suppliers
2）从customers_bak和Suppliers中选择所有城市（排出所有值，包括重复的值）
SELCET 城市 FROM customers_bak
UNION ALL  #两个结果不去重，拼接起来
SELECT 城市 FROM Suppliers;
1
2
3
4
5
6
7
8

1）

2）

十一、JOIN

1）作用

• 概念
  join用于把来自两个或多个表的行结合起来，基于这些表的共同字段做一个关联关系
• 常见的join类型
  1）INNER JOIN 内连
  2）LEFT JOIN 左连
  3）RIGHT JOIN 右连
  4）FULL JOIN
  5）CROSS JOIN

2）语法

• 内连接

SELECT A.Num_a,B.Num_B
FROM A   #A表内联B表，这里的INNER可以省略
INNER JOIN B
ON A.Num_A = B.Num_B  #ON 表示用这个列进行关联
1
2
3
4

• 左链接（先把左表都展示出来，然后匹配右类似的值）

SELECT A.Num_a,B.Num_B
FROM A  #坐标都会展示出来，匹配不到就显示NULL
LEFT JOIN B
ON A.Num_A = B.Num_B;
1
2
3
4

• FULL JOIN(两个表都显示出来，没匹配就显示NULL)

SELECT A.Num_a,B.Num_B
FROM A 
FULL JOIN B
ON A.Num_A = B.Num_B;
1
2
3
4

• CROSS JOIN(乘积匹配)

SELECT A.Num_a,B.Num_B
FROM A 
CROSS JOIN B;
1
2
3

• 解释
  A表每个参数与B表的每个参数匹配

3）使用举例

十二、SELECT INTO(sql server专用，掠过)

• 作用
  从一个表中复制数据，然后将数据插入到另一个新表中

十三、CREATE

1）作用

创建数据库、表、索引等

2）使用举例

1)创建数据库
CREATE DATABASE SQL_Dev;
2)创建表
Use SQL_Dev  --在这个数据库内(DATABase内)
CREATE TABLE table_name(
	客户ID  INT  NOT  NULL,
	姓名  VARCHAR(10) NULL,
	地址  VARCHAR(50) NULL,
	城市  VARCHAR(20) NULL,
	邮编  CHAR(6)  NULL,
	省份 VARCHAR(20) NULL
);
1
2
3
4
5
6
7
8
9
10
11
12

十四、VIEW视图用法

1）作用和解析

①提高了重用性

• 步骤

1)就像一个函数。如果要频繁获取user的name和goods的name。就应该使用以下sql语言。示例：
select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
2)但有了视图就不一样了，创建视图other。示例
create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
3)创建好视图后，就可以这样获取user的name和goods的name。示例：
select * from other;
以上sql语句，就能获取user的name和goods的name了。
1
2
3
4
5
6
7

②对数据库重构，却不影响程序的运行
对数据库重构，却不影响程序的运行。假如因为某种需求，需要将user拆房表usera和表userb，该两张表的结构如下：

测试表:userA有id，name，age字段
测试表:userB有id，name，sex字段
1
2

• 问题
  这时如果php端使用sql语句：select * from user;那就会提示该表不存在，这时该如何解决呢。解决方案：创建视图。以下sql语句创建视图：

1)create view user as select a.name,a.age,b.sex from usera as a, userb as b where
 a.name=b.name;
2)以上假设name都是唯一的。此时php端使用sql语句：select * from user;就不会报错什么的。
这就实现了更改数据库结构，不更改脚本程序的功能了。
1
2
3
4

③提高了安全性能，可以对不同的用户，设定不同的视图
提高了安全性能。可以对不同的用户，设定不同的视图。例如：某用户只能获取user表的name和age数据，不能获取sex数据。则可以这样创建视图。示例如下

create view other as select a.name, a.age from user as a;
1

④让数据更加清晰
让数据更加清晰。想要什么样的数据，就创建什么样的视图。

十五、约束的用法

1）常见约束

1)NOT NULL 约束:保证列中数据不能有NULL值
2)DEFAULT 约束:该列未指定值时设置默认值
3)UNIQUE 约束:保证列中的所有数据各不相同
4)主键约束:唯一标识数据表中的行/记录
5)CHECK约束:此约束保证列中的所有值满足某一条件
6)主键约束，不能为空且唯一
1
2
3
4
5
6

十六、SQL执行顺序

1）顺序排行

8)SELECT 
9)DISTINCT
11)<TOP Num> <select list>
1)FROM [left_table]
3)<join_type> JOIN <right_table>
2)ON <join_condition>
4)WHERE <where_condition>
5)GROUP BY <group_by_list> --列的分组
6)WITH <CUBE | RollUP>
7)HAVING <having_condition>  --条件过滤
10)ORDER BY <order_by_list>
1
2
3
4
5
6
7
8
9
10
11

2）使用举例

SELECT TOP 10 number FROM master.spt_values
WHERE type = 'P'
order by number desc
1
2
3