黑马程序员---sql基础
- 1.主键(PrimaryKey)
- 主键有两种选择策略:业务主键和逻辑主键。
- 业务主键:是使用有业务意义的字段做主键,比如省份证号,银行账号等;
- 逻辑主键:是使用没有任何业务意义的字段做主键,完全给程序看,业务人员不会看的数据。 (推荐使用)
- 外键(ForeignKey)
- 2.SQLServer 的管理
- 数据由DBMS统一管理
- 常用字段类型:bit(可选值0、1),datetime,int,varchar,nvarchar(可能含有中文用)
- varchar、nvarchar和char(n)的区别:char(n)不足长度n的部分用空格填充。
- varchar:可变长度 (var:Variable,可变的)
- 3.MSSQL的特点和特性
- 特点:1、综合统一 2、高度非过程化 3、面向集合的操作方式 4、语言简单易学易用
- 特性:1、原子性 2、分离性 3、一致性 4、持久性
- 4.SQL基础 1
- 主键选择:两种常用的主键数据类型:int(或bigint)+标识列(又称增长字段)
- 标识列可以避免并发等问题,不需要开发人员控制自增,一个表只能有一个标识列。
- Guid:
- 获取guid值语法是:newid();
- 数据插入:
- insert 用法:
- insert into Person(Name,Age) values('lily',18); //id 自动增长的情况下,不用指定主键
- insert into Person values('lucy',18); //可以省略表名后的字段名
- 使用Guid作唯一性标识:
- insert into Person(Id,Name,Age) values(newid(),'tom',30);
- 数据更新:
- update Person set Age=30;
- update Person set Age=50,Name='Lucy';
- update Person set Age=Age+1;
- update Person set NickName=N'年轻人'where Age>=20; 【where Age>20 and Age<30 or (Age =80) (and ,or ,<>)】
- 5.SQL基础 2
- 删除表中全部数据: delect from T_Person
- delect from T_Person where Age>20
- drop 删除表
- 数据检索:
- select Fname as 姓名,Fage as 年龄, Fsalary as 月薪 from T_Employee where Fsalary<5000 //给需要查询显示的字段起个别名
- select也可以用于与任何一个表不相关的查询: select 1+2 select newid()(随机产生个newid) select @@Version (查数据库的版本) select getdata() 产生当前时间
- select count(*) from T_Employee 查询数据表中有多少条数据
- select max(FSalary) from T_Employee 最高工资, min(FSalary)最低工资 avg(Fsalary)平均工资 sum(FAge)所有年龄的和 这些就叫做聚合函数
- select count(*) from T_Employee where Fsalary>=5000; 查询工资大于5千的
- 数据排序:
- select * from T_Employee order by Fsalary ASC 按照工资升序排序 DESC降序
- select * from T_Employee order by FAge DESC,FSalay ASC 先按照年龄降序排序,年龄相同的在按照薪水升序排列
- 通配符过滤 like:
- 单字符匹配:select * from T_Employee where Fname like '_erry' 以任意字符开头,剩余部分为‘erry’的
- 多字符匹配(半角%):select * from T_Employee where FName like '%n%' 名字中含有n的
- select * from T_Employee where FNumber like 'DEV%' 所有产品开发部的
- 空置处理:
- null:表示不知道
- select * from T_Employee where Fname is null; 查询FName为空的
- select * from T_Employee where Fage in (23,25,28) 年龄在这三个之中的
- select * from T_Employee where Fage between 20 and 30 20 到30 之间的
- 6.数据分组
- group by
- select FAge,count(*) from T_Employee group by Fage 按年龄进行分组,每个年龄的个数
- 7.限制结果集查询
- select top 3 * from T_Employee
- order by Fsalay desc //按工资从高到低检索前三个
- 8.去掉数据重复
- alter table T_Employee add FSubCompany varchar(20); //alter table 修改表,增加varchar类型的FSubCompany列
- select distinct Fdepartment from T_Employee //查询员工部门,distinct 去除重复
- select distinct Fdepartment,FSubCompany from T_Employee //去除部门及子公司完全重复的项
- 9.联合结果集(Union)
- select Fname,Fage from T_TempEmployee
- union all //默认情况下,union 自动去除完全重复的行,但是加上 all,可以不去重复
- select Fname,Fage from T_Employee //两个表信息的联合查询,上下查询的类型一样,个数相同
- 或者
- select Fnumber Fname,Fage,Fdepartment from T_Employee
- union
- select FIdCardNumber,Fname,Fage,'临时工,无部门' from T_TempEmployee //对于没有的列,可以自由填充
- select '正式员工最高年龄',MAX(FAge) from T_Employee
- union all
- select '正式员工最低年龄',MIN(FAge) from T_Employee
- union all
- select '临时员工最高年龄',MAX(FAge) from T_Employee
- union all
- select '临时员工最低年龄',MIN(FAge) from T_Employee
- select Fnumber,Fsalay from T_Employee
- union
- select '工资合计',sum(FSalary) from T_Employee
- 10.数据库函数
- ABS() 求绝对值 例:select abs(-3) //3
- ceiling()舍入到最大整数
- floor() 舍入到最小整数
- round() 四舍五入
- 字符串函数:len() 计算字符串长度 select Fname ,len(Fname) from T_Employee 查询用户名,及用户名的长度
- lower() ,upper() 转小写,转大写
- ltrim() 字符串左侧的空格去掉
- rtrim() 字符串右侧的空格去掉
- ltrim(rtrim(' b ')) 去掉两边的空格
- substring(string,start_position,length) //截取字符串
- 日期函数:
- getdate() 取当前日期函数
- dateadd()
- select DateAdd(day,3,getdate()) //在当前日期上加三天
- select dateadd(hh,1,getdate()) //在当前时间加一个小时
- datediff(datepart,startdate,enddate) //计算两个日期之间的差额
- select datediff(hh,getdate(),dateadd(day,-3,getdate())) //计算前三天跟当前时间相差几个小时
- selet Fname,Findate,DateDiff(year,Findate,getdate()) from T_Employee //查询入职时间
- // 在表中新加字段时,应:表/修改/添加列名,关闭/表/打开表/在所添加的列中添加字段/
- select Datediff(year,Findate,getdate()),count(*) from T_Employee
- group by DataDiff(year,Findate,getdate()) //统计不同工龄的员工个数
- datepart(datepart.date) //返回一个日期的特定部分
- select DatePart(year,getdate()),DatePart (month,getdate()) //取出当年的年份和月份
- select Datepart(year,Findate()),count(*)
- from T_Employee
- group by DatePart(year,Findate) //查询每一年入职的员工个数
- 类型装换函数:
- Cast(被转换表达式 as 要转换成的数据类型)
- convert(要转换成的数据类型 , 被转换的表达式)
- select cast('123' as int),cast('2008-08-08' as datetime),
- convert(datetime,'2009-09-09'),convert(varchar(50),123)
- 应用:
- select datepart(year,cast('2008-08-08' as datetime))
- 11.流控函数
- 空置处理函数:
- select ISNULL(Fname,'佚名') as 姓名 from T_Employee // 如果Fname 为空(null),则返回佚名 ; ISNULL(Fname,'佚名') as 姓名,作为列名
- case:
- case 表达式
- when 值1 then 返回值1
- when 值2 then 返回值2
- when 值3 then 返回值3
- else 默认值
- end
- select Fname
- {
- case Flevel
- when 1 then '普通客户'
- when 2 then '会员'
- when 3 then 'VIP'
- else '未知客户类型'
- end
- } as 客户类型
- from T_Cusomer
- select Fanme
- {
- case
- when Fsalary<2000 then '低收入'
- when Fsalary>=2000 and Fsalary<=5000 then '中等收入'
- else '高收入'
- end
- } as 收入水平
- from T_Employee
- 13. 练习
- select Name,
- {
- case Score
- when N'胜' then 1
- else 0
- end
- } as 胜,
- {case Score
- when N'负'then 1
- else 0
- end} as 负 // 胜的都标记为1,负的都标记为0
- from T_Scores
- select Name,
- Sum{
- case Score
- when N'胜' then 1
- else 0 }
- Sum{
- case Score
- when N'负' then 1
- else 0 }
- from T_Scores
- group by Name //求胜的和,负的和
- 14.练习3
- //所有数据中通话时间最长的5条记录 orderby datediff
- 1. select Datediff(second,StartDateTime,EndDateTiem) from T_CallRecords1 //查询出所有用户以秒为单位的通话时长
- 2. select top 5 * from T_CallRecords1
- order by DateDiff(second,StartDateTime,EndDateTime) DESC
- //输出所有数据中拨打长途号码(对方号码以0开头)的总时长。 Like sum
- 1. select * from T_CallRecords1
- where TelNum like'0%' // 第一步先取出以0开头的所有记录
- 2. select sum(datediff(second,StartDateTime,EndDateTime)) from T_CallRecords1
- where TelNum like'0%' //第二步求和
- //输出本月通话时长最多的前三个呼叫员的编号 datediff(month…),sum,order by
- select Datediff(second,StartDateTiem,EndDateTime) from T_CallRecords1
- 15.索引 Index
- 右键 索引键/选择索引列/更改标识名称
- 一般,经常进行查询的,就简历索引(即经常在where后出现的)
- 索引优点:提高查询效率
- 缺点:占据空间,会降低Insert,Update,Delete 的速度
- 因此,只在经常索引的字段上创建索引
- 16.表连接 Join
- select o.BillNo,c.Name,c.Age // as 给表起别名
- from T_Orders as o join T_Customers as c on o.CustomerId=c.Id //两表之间的关联,表1 join 表2 on 关联条件
- //显示所有年龄大于15岁的顾客购买的订单号,客户姓名,客户年龄
- select o.BillNo,c.Name,c.age
- from T_Orders as o join T_Customers as c on o.CustomerId=c.Id
- where c.Age>15
- //要求显示年龄大于平均年龄的顾客购买的订单
- select o.BillNo,c.Age
- from T_Order as o join T_Customers as c on o.CustomerId=c.id
- where c.Age>(select avg(Age) from T_Customers)