SQL语言基础

终于把笔记做的差不多了，现在开始每天把自己的笔记往博客上写点，也方便以后没事的看一下。

顺序是SQL—> Python—>统计学。后面学数据结构和算法以及机器学习的时候也准备直接把笔记做在这上面了。

希望后面工作了还能抽出时间每天看看东西做点笔记学习。

1.select
查阅、检索

select *
from 表名
where 条件;

2.distinct
只返回唯一不同值

select distinct CustomerID
from Produce;

不能部分使用distinct。 比如：

select distinct CustomerID,Produce
from Produce

这样的话会返回所有行。

3.top
限制最多返回多少行。

select top 5 ProduceName
from Product

4.注释
‘–’两个连字符，–之后就是注释
‘#’后整行为注释。
比如：

select prod_name  --这是一条注释
#这是一条注释
from Prod

注释能对程序描述和说明，也可以暂时停止要执行的SQL代码。

5.order by
order by:按照条件排序（默认升序（ASC）,如果要降序则在最后用（DESC））
★要保证order by子句在select语句中的最后一条子句！

select prod_name
from Product
order by prod_name

select prod_name
from Product
order by prod_name desc

按多个列名排序：在order by后接多个列名，列名与列名之间用逗号隔开。
order by prod_name,prod_price;

也可以按照列相对的位置来排序
order by 2,3;

先用prod_name降序排列，再同时用prod_price升序排列
order by prod_name desc,prod_price;

★在多个列上进行降序排序时，需要在每一列后都加上desc！

6.创建表格

creat table XXX (
列名 数据类型 列约束（可选），
列名 数据类型 列约束，
列名 数据类型 列约束
）；

列约束有：
primary —— 主键
null —— 可有空值
not null —— 必填，不能有空值
unique —— 只能填唯一值

数据类型有：
char——字符长度一样的文本
varchar——字符长度不一样的文本，如邮件等
int——整数
numeric——所有数字
datetime——日期

创建表格时，最后一列不加逗号，直接右括号+分号结束。

7.
select count(*)：查询该表中有多少有效数据行
select columns(*)：查询该表中有多少有效数据列

模糊查询：like
where like a_c –查询a开头c结尾，且字符串长度为3的字符串
一个下划线表示一个字符，一个星号表示一个或多个字符。

8.
where 关键字后条件如果时char和varchar类型，则必须加引号，如果是int和numeric类型则不加。
where FirstName = 'Bush'
where year > 1965

9.插入数据和between … and …用法
insert into 表名 values(值1，值2,......):在指定表中插入数据
insert into 表名 (列1，列2,......) values (值1，值2，......）：指定表中的列插入数据.

between … and …:选取介于两个值之间的数据范围。这些值可以是数值、文本或日期等。

select *
from Person
where LastName between 'Adams' and 'Carter';

输入结果是：

不同的数据库对between … and… 的处理方式有差异
Access中介于两者之间且不包括两者。
MySql，Sql Server：包括边界。

10.修改值和删除列

update 表名
set 原值 = 新值
where 列名= 值所在列;

修改更新数据用法：

update 表名
set 原值 = 新值，原值 = 新值
where 列名= 值所在列;

删除列的用法 ：

delete
from 表名
where 列名 = 要删除的列名;

（可以在不删除表的情况下删除所有行。这意味这表的结构，属性和索引都是完整的）

delete
from 表名;

delete *
from 表名;

原表：

update Person
set Address = 'ZhongShan 23' , City = 'Nanjing'
where LastName = 'Wilson'；

修改后：

11.in
‘in’该操作符允许我们在where子句中规定多个值。
指定该列中值只能是后面括号中约束的值。
用法：

select column_names(s)
from table_name
where column_name in (value1,value2,...)

'Not in'则相反，表示不在约定值之中。

12.Alter
添加列用法：

add column_name datatype(数据类型）; 

MySQL用法

alter table 表名
modify column 表名 datatype;

改变表中列的数据类型

alter table 表名
alter column 列名 datatype ;

删除列

alter table 表名
drop column 列名;

13.check约束
在表中已存在的列创建约束。
用于限制列中值的范围

alter table Persons
add check (Id_Person>0);

如果需要命名check约束，以及为多个列定义check约束：

alter table Persons
add constraint chk_Person check (Id_Person>0 and City = 'Sandnes')

撤销check约束：
SQL Server/Oracle/Ms Access 中这样使用

alter table Persons
drop constraint chk_Person

MySQL中这样使用

alter table Persons
drop check chk_Person

14.default
default约束用来在列中插入默认值（没有填入值的话就自动填充默认值）
创建表时设置default：

create table 表名(
列名 数据类型 列约束
列名 数据类型 default‘Char’
）；

当表已经被创建好后添加default约束：
MySQL用法

alter table 表名
alter 列名
set default '默认值';

SQL Server / Ms Access 用法

alter table 表名
add constraint df_表名_列名
default('默认值') for 列名；

撤销default约束：
MySQL用法

alter table 表名
alter 列名 drop default;

SQL Server / Oracle / Ms Access 用法

alter table 表名
alter column 列名 drop default;

15.having
在SQL中增加having子句的原因是：where关键字无法与聚合函数一起使用。having子句可以让我们筛选分组后的各组数据。
用法：

select 列名，聚合函数(列名)
from 表名
where 条件
group by 列名
having 聚合函数(列名)  运算符  值；

16.join
SQL join 子句用于把基于表与表之间共同字段的条件下把两个表或多个表的行结合起来。

select 列名1，列名2，列名3 ...
from 表A inner join 表B on 表A.列名 = 表B.列名

left join 关键字从左表返回所有的行，即使右表中没有匹配（会用null填充）

identity：约束，后接（int，int）表示从几开始，每次递增多少。
eg：

create table 表名(
列名 数据类型 not null  identity(1,1)
）

17.group by
用于结合合计函数，根据一个或多个列对结果集分组。
eg:

select 列名，聚合函数（列名）
from 表名
where 列名 运算符 值
group by 列名；

在有聚合函数情况下选取重复值：

select PetBread,avg(PetWeight) as 平均体重
from Pet_3
group by PetBread
having count(PetBread) > 1；

18.limit
limit子句：后接数值表示限制最多返回多少行。
limit int offset int : 第一个int为要检索多少行，第二个int为从哪行开始。—放在order by语句后。
limit int，int ： 第一个int为从哪行开始，第二个int为要检索多少行。—MySQL支持该用法

与top相同：限制最多返回多少行。

19.where子句操作符

 <>          <=        ! >       ! <
 =           !=         >         <
 >=         between   is null

20.and，or语句
在where子句中，and在求值过程中优先级更高。
如：列出价格10美元以上，且由‘DLL01’或‘BRS01’制造的产品。
错误：

 select prod_name,prod_price
 from Products
 where vend_id = 'DLL01' or
       vend_id = 'BRS01' and 
       prod_price >= 10;

正确写法：

select prod_name,prod_price
from Products
where (vend_id = 'DLL01' or vend_id = 'BRS01')
      and  prod_price >= 10;

括号具有比and或or操作符更高的求值顺序。任何时候使用具有and和or操作符的where子句，都应该用括号明确分组操作符。

21.通配符
通配符中‘%’不会匹配Null。
eg：
where prod_name like '%'
([ ])用来指定一个字符集，它必须匹配指定位置（通配符位置）的一个字符。—Access和SQL Server支持。如：

select cust_contact
from Customers
where cust_contact like '[JM]%'  --找出由J或M开头的联系人。
order by cust_contact;

此通配符可以用前缀字符 ’ ^ ’ 来否定(脱字符号)检阅来表示除 [ ] 中给定值以外的值 。 [^JM]
当然也可以用NOT。
eg：
where not cust_contact like '[JM]%'

22.创建组合值
将表中两列以上拼接为一列时。用 ’+‘ 来拼接。—支持Access和SQL Server
eg:

select 列名 + ' ( ' + 列名 + ' ) '
from 表名
order by 列名;

显示为：
列值 （列值）
列值 （列值）
列值 （列值）
……

Trim函数
Ltrim() :去掉左边空格
Rtrim():去掉右边空格
Ttrim():去掉两边空格
综上：

select rtrim(列名) + ' ( ' + ltrim(列名) + ' ) '
from 表名
order by 列名；

显示为：
列值(列值)
列值(列值)
列值(列值)

23.重点!SQL执行顺序
7.Select 8.Distinct 10.< top num >
1.from 表名
3.< Join Type > 表名
2.on < Join Condition>
4.where
5.group by
6.having
9.order by
Join type :连接类型（left ,right ,inner)
join condition : 连接条件

24.soundex
Soundex():检索括号内类型发音。 —Access中不适用
eg:

select cust_name,cust_contact
from Customers
where soundex(cust_contact) = soundex('Michael Green');

输出：

cust_name            cust_contact
Kids Place          Michelle Green

25.日期和时间处理函数
如在Orders表中检索2012年所有订单
SQL Server:

select order_num
from Orders
where datepart(yy,order_date) = 2012;

Access:

select order_num
from Orders
where datepart('yyyy',order_date) = 2012;

MySql,MariaDB:

select order_num
from Orders
where year(order_date) = 2012;

26.补充
max()函数:对非数值数据使用时，直接返回该列排序后的最后一行，且会忽略列值为Null的行。
min()函数则相反。

对只包含不同值使用聚集函数时，需指定Distinct参数。—Access中不适用
eg：

select avg(distinct prod_price) as avg_price
from Products
where vend_id = 'DLL01';

27.group by 补充
1.group by 子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式，且不能使用别名。
2.大多数SQL实现不允许group by列带有长度可变的数据类型（如文本或备注型字段）。
3.除聚集计算语句外，select语句中的每一列都必须在group by子句中给出。
4.如果分组列中包含具有null值的列，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。
5.group by子句必须出现在where子句之后，order by子句之前。

28.where和having的区别
having支持所有where操作符
having 和 where 的差别：
1.where在数据分组前进行过滤，having在数据分组后进行过滤。
2.where排除的行不包括在分组中，这可能会影响计算值，从而影响having子句中基于这些值过滤掉的分组。

29.子查询注意事项和substring函数
作为子查询的select语句只能查询单个列，企图检索多个列将返回错误。
子查询常用于where子句的in操作符中，作为计算字段使用子查询。

substring(列名,num,num）表示从第几个字符开始选取几个字符。
如：
select substring('abcdefg',2,3)
输出：’bcd’

30.外键约束和临时表用法
1.可在创建时加入外键约束：
eg：

create table Orders (
order_num int not null,primary key,
order_date datetime not null,
cust_id char(10) not null references Customers(cust_id) 
#表明Orders表中cust_id的任何值都必须是Customers表中cust_id中的值
);

2.用Alter用法加入外键约束。
eg：

alter table Orders
add constraint
foreign key (cust_id) references Customers(cust_id);

其他约束：
unique:唯一不重复
check（列名 运算符 value）：保证该列中每个值都要符合括号里的要求
identity（num，num）：从几开始，每次递增几

创建索引：

Creat index 索引名
on 表名（要创建索引的列名）

临时表用法：
比如求每个部门里最高薪水的员工：

select 部门id，员工名称，员工薪水
from 员工表 join （select 部门id，max(salary)
                  from 表名
                  group by 部门id） as t 
                  on 员工表.部门id = t.部
where t.max(salary) = 员工表.salary;

基础篇结束,开始每天更一点点SQL进阶的内容…