补充记录sql基础语法

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

一、distinct

1）作用

去除重复的项

2）语法

select distinct 列名字  from  表名;

• 举例

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

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

3）缺点

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

二、top的用法

1）作用

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

2）语法

select top 数字 列的名字  
from  表的名字;
或
select top 百分比  列的名字  
from  表的名字;

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();

三、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

四、group by

1）作用

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

2）语法

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

3）使用举例

查询所有城市数据

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

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

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

六、order by

1）作用

对结果排序

2）语法

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

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）降序

七、LIKE

1）作用

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

2）语法

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

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

• 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结尾的值

八、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);

3）使用例子举例

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

九、BETWEEN AND

1）作用

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

2）语法

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

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';

十、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;

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）

十一、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 表示用这个列进行关联

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

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

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

SELECT A.Num_a,B.Num_B
FROM A 
FULL JOIN B
ON A.Num_A = B.Num_B;

• CROSS JOIN(乘积匹配)

SELECT A.Num_a,B.Num_B
FROM A 
CROSS JOIN B;

• 解释
  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
);

十四、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了。

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

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

• 问题
  这时如果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;就不会报错什么的。
这就实现了更改数据库结构，不更改脚本程序的功能了。

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

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

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

十五、约束的用法

1）常见约束

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

2）使用举例

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