2012-05-03 SQL 学习笔记

2012.5.3 SQL 学习笔记

——张伟

顺序：建数据库->建表->设主键


数据类型：
->bit 相当于布尔类型（只能是0或1）
->char（10）限制长度只能10个字符
->bigint 大整数时候用
->nvarchar(50)
->nvarchar(MAX)无限长的字符
备注：加n表示可能含有中文
->varchar不含中文信息的字符

varchar和char的区别：
->char当数据长度不满足时，自动用空格填充
->varchar按当前输入的长度存储

SQL语句：是和数据库交谈的语句
->语句中字符串用单引号
->关键字不区分大小写，但字符串值（单引号括起来的）区分
->create table +表名（创建表）
->drop table+表名（删除表）
->insert into person1(number,name,age,nickname) values(3,‘张三’，20，‘aa’)插入语句

SQL分为：
->DDL数据定义语言
->create table, drop table，alter table等
->DML数据操作语言
->select，insert等


主键的选择策略：
->必须满足唯一，不能有重复，通常用int，bigint或uniqueidentify数据类型

主键自增：（标识列：又称自动增长字段）
->在标识规范里可改动，即把标识规范设置为是
->标识增量：用于按指定的大小增长
->一个表只能有一个标识列（通常都用在主键上）

GUID生成器：
->一种算法，每次生成的字符串永远不会重复
->用网卡MAC、纳秒级时间、芯片ID算出的
->SQL中生成GUID函数:newid()
->C#中生成GUID方法:Guid.NewGuid()，返回是GUID类型

自动增长字段优缺点:
->优点：占用空间小，无需人员干涉，容易读懂
—> 缺点：效率低，数据导入导出不方便

GUID优缺点：
->优点：效率高，数据导入导出方便
->缺点：占用空间大，不易读
->做主键时与插入的数据顺序正好相反

业界主流倾向于使用GUID


数据插入：
->insert into person3(name,age) values('aa',36)或insert into values ('bb',23)有自动增长的字段变量不需要写
->insert into person4(id,name,age) values(newid(),'aa',23)设置没有字段增长时需要写

数据更新：
->update person3 set age=30;把所有age设置为30
->update person3 set age=50,name='a';将所有的数据age设置为50，name设置为a
->update person3 set age=age+2;可以单独统一增长设置
->update person3 set Nicname=N'a' where age>=20;把年龄大于20岁的人的nicname都设置为a（前面加N是为了防止有中文发生错误）
->update person3 set Nicname=N'a' where age=20;把年龄等于20岁的人的nicname设置为a，注意只有一个=号，不同于C#
->update person3 set Nicname=N'a' where age=20 or age=30(或表达式，还有and，not)把年龄等于20或等于30岁的、、、、

删除数据：
->delete from person
->delete只是删除数据，表还在，drop表示彻底消除了
->delete from person where age>20;把person表中年龄大于20岁的数据删掉

数据检索：
->select * from person;把person表中所有数据检索出来
->select name from person;把person表中所有的名字显示出来
->select * from person where age>20;把person表中年龄大于20岁的数据显示出来
->select name as 姓名，age as 年龄 from person where age>20;按自己易懂的方式显示出年龄大于20岁的成员数据
->select newid(),select 1+3,select getdate(),select 00version这些语句可以检索和表无任何关系的内容
->select count(*) from person;统计表中所有数据条数
->select max(age) from person；求年龄最大值
>select min(age) from person；求年龄最小值
>select avg(age) from person；求年龄平均值
>select sum(age) from person；求年龄和
->select count(*) from person where age>20;统计person表中年龄大于20岁的人的个数

数据汇总：
->即聚合函数：MAX,MIN,SUM,AVG等

数据排序：（order by）
->select * from person order by age asc;按年龄排序由小到大
->asc升序
->select * from person order by age desc;按年龄排序由大到小
->desc降序
->select * from person order by age asc,salary desc;将person表中的年龄按升序排列，如果年龄相同则工资按降序排列（用逗号隔开，靠前的优先）
->select * from person where age>20 order by age asc,salary desc;筛选数据后的排序，where一定要在order by之前

通配符匹配查询：
->单字符：半角下划线_，只代表一个字符
->select * from person where name like '_ucy';检索出名字中只包含四个字符且最后为ucy字符的人
->多字符：用百分号%，代表0或多个字符
->select * from person where name like '%n%';检索出名字中所有包含n字符的人名

空值处理：null（表示不知道，不是表示没有）
->有null参与的所有运算结果都是nulll
->select *from person where name is null;检索出名字为不知道的。注意用is，不为空就用is not null，不能用=或！=

多值匹配：
->select * from person where age=20 or age=30 or age=32;检索出年龄为20岁，30岁和32岁的人
->select * from person where age in(20,30,32);同上，检索出年龄为20岁，30岁和32岁的人
->select * from person where age>20 and age<30;检索出person表中年龄在20岁和30岁之间的人
->select * from person where age between 20 and 30;同上，检索出person表中年龄在20岁和30岁之间的人

数据分组：group by 
->select count(*) from person;显示出所有person表中数据的条数
->select age,count(*) from person group by age;按年龄检索出数据，且显示每个年龄的条数
->group by 必须放到where语句之后
->select age,avg(salary) from person group by age;按年龄检索出数据，且显示每个年龄段的平均工资。注意：没有出现在group by子句中的列是不能放到select语句后的列名表中的（聚合函数中除外）

Having 语句：
->在where函数中不能使用聚合函数，必须使用having 
->select age,count(*) from person group by age having count(*)>1;按年龄检索，且显示出每个年龄段的人数大于1的组

where和having区别：
->where：对分组前数据进行过滤
->having:对分组后数据进行过滤

去掉重复数据：distinct
-> select department from person;显示出person表中department的所有信息
->select distinct department from person;只显示出person表中不同department的信息

联合结果集：union（合并后会自动去掉重复的）、union all（所有的都在，不去掉）
->select name, age from tempemployee;把零时工的年龄和姓名显示出来
->select name,age from tempemployee union select name,age from employee;把零时工和正式工的年龄和姓名合为一个结果 显示出来
->select name,age,department from employee union select name,age,'临时工'，‘部门’ from tempemployee;前后两个的变量数量和变量类型要相同，不够的要补全

案例1：
要求查询员工的最低年龄和最高年龄，零时工和正式工要分别查询
->select '正式员工最高年龄' max(age) from employee;
union all
select '正式员工最低年龄' min(age) from employee;
union all
select '零时工最低年龄' max(age) from tempemployee;
union all
select '零时工最低年龄' min(age) from tempemployee;

案例2：
查询每位正式员工的信息，包括工号，工资，并且在最后一行加上所有员工工资额合计
->select number,salary from employee union select '工资额合计'，sum(salary) from employee;
数字函数：
->abs():求绝对值
->ceiling():舍入到最大整数，例如3.33被变成4，-3.6被变成-3
->floor():舍入到最小整数
->round():四舍五入

字符串函数：
->len（）：字符串长度
->lower(),upper():转小写，大写
->ltrim(),rtrim():去左空格，去右空格
->substring():取子字符串

日期函数：
->getdate():当前日期
->dateadd（datepart,number,date）:date为计算的日期，number为增量，datepart为计量单位。例如：dateadd(day,3,date)即计算日期date的3天后的日期，dateadd(month,-8,date)为计算日期date的8个月前的日期
->datediff(datepart,startdate,enddate):计算两个日期之间的差额
->datepart(datepart,date):返回一个日期的特定部分，例如：datepart(month,getdate())为返回当前时间的月份

类型转换函数：
->cast(expression as date_type)：例如cast('123',as int),cast('2008-03-09' as datetime)
->convert（date_type,expression）:例如：convert(datetime,'2008-03-03'),convert(varchar(50),13)

空值处理函数：
->isnull(name,新名字)：即如果为空就编程新的名字，不是的还是保持原来的名字

case函数：
->类似于c#中switch case语法:例如
select name
{
case level
when 1 then a
when 2 then b
when 3 then c
else default
end
} as 等级 from person

select name,
{
case
when salary<2000 then '低收入'
when salary>=2000 and salary<=5000 then '中等收入'
else '高收入'
}as '收入水平' from person


case函数案例：
表中有ABC散列，用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列是选择B列否则选择C列。
->select(case when a>b then a else b end),(case when b>c then b else c end) from talbe

表连接：join 
->select o.billnumber,c.name,c,age from orders as o join customers as c on o.customerID=c.ID;当中o和c分别为另起的别名，这样就把订单表和客户表当中的ID对应起来了，并显示出与各自ID对应的账单号，姓名和年龄
->select o.billnumber,c.name,c,age from orders as o join customers as c on o.customerID=c.ID where c.age>15;显示出所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄
->select o.billnumber,c.name,c,age from orders as o join customers as c on o.customerID=c.ID where c.age>(select avg(age) from customers);显示年龄大于平均年龄的顾客购买的订单 
 
子查询：
将一个查询语句当做一个结果集供其他SQL语句使用
->select * from reader where yearofjoin in{select distinct yearpublished from book};显示出书出版那一年加入的会员
->select * from{select row_number() over(order by salary desc) as 列，number,name,salary,age from employee} as 表2 where 表2.列>=3 and 表2.列<=5;当中row_number（）为数据库2005版以上新增的功能，即能增加一列并自动递增行号。