SQL Server基础（二）

三、数据的增删改查

5. 数据的删除

--删除员工表所有记录
delete from employee
--删除部门编号为3中工资大于1万的
delete from employee where DepartmentId = 3 and EmployeeSalary >10000

Attachment:
drop delete truncate区别：
（1）drop table employee --删除表对象，整张表不存在
（2）delete from employee --删除employee中所有数据，但是表结构依然存在
（3）truncate table employee --删除employee中所有数据，但是表结构依然存在
delete与truncate的区别：
（1）delete能够实现有条件的删除，而truncate不行
（2）truncate 清空数据后，再添加数据编号仍然是1，2，3，4，5，6
（3）delete删除数据后，删除的自动编号永远不存在，编号会变成7，8，9，10，11

For more detailed information, click https://so.youkuaiyun.com/so/search?q=delete&t=blog&u=JJJikerUPUP

6.数据的查询
基本查询

--查询表中的所有行与所有列
select * from Department
--有条件的查询 查询指定列
select EmployeeName,EmployeeSex,EmployeeSalary from employee
--查询指定列 并显示其别名
select EmployeeName 姓名,EmployeeSex 性别,EmployeeSalary 月薪 from employee
--查询员工所在的城市并删除重复值
select distinct EmployeeAddress from employee 
--查询出如果薪资上调20%的结果
select EmployeeName,EmployeeId,EmployeeSalary*1.2 加薪后薪资 from employee 

条件查询

！=：不等于
IS NULL：比较为空
IS NOT NULL：比较不为空
in ：是否在其中
like：模糊查询
between…and …：是否在两者之中

Emp：
（1）and or 的用法

--查询部门ID为1的员工信息
select * from employee where DepartmentId = 1
--查询工资大于7000且部门ID为1的
select * from employee where DepartmentId = 1 and EmployeeSalary >= 7000
--查询工资大于7000且出生日期在2000年之前的员工或者depId为2的
select * from employee where DepartmentId = 2 or 
(EmployeeSalary >= 7000 and EmployeeBirth < '2000-01-01')

（2）between and

--月薪在10000到15000的员工信息
select * from employee where EmployeeSalary >=10000 and EmployeeSalary <=15000
--等同做法
select * from employee where EmployeeSalary between 10000 and 15000

（3）in

--in的用法
select * from employee where EmployeeAddress in('北京','成都')

（4）排序

--排序  不加关键词 默认升序
select * from employee order by EmployeeSalary desc
select * from employee order by EmployeeSalary asc

工资最高的几人：top

--查出工资最高的2人
select top 2 * from employee order by EmployeeSalary desc 
--查出工资最高的10%的员工信息
select top 25 percent * from employee order by EmployeeSalary desc

名字长度：函数len

--根据名字长度排序 降序
select * from employee order by len(EmployeeName) desc

（5）null
null前面要用is不能用‘=’

--查询出地址没有填写的员工信息
select * from employee where EmployeeAddress is null
--查询出地址填写的员工信息
select * from employee where EmployeeAddress is not null

（6）函数year() month() day()

--查询90后
select * from employee where EmployeeBirth between '1990-1-1' and '1999-12-31'
select * from employee where YEAR(EmployeeBirth) between 1990 and 1999

--查询20-30岁且薪资在10000到15000之间的员工信息
select * from employee where 
(year(GETDATE())-year(EmployeeBirth) between 20 and 30)
and
(EmployeeSalary between 10000 and 15000)

--查询是巨蟹座的员工 （6.22-7.22）
select * from employee where
(month(EmployeeBirth)=6 and day (EmployeeBirth) >=22)
or
(month(EmployeeBirth)=7 and day (EmployeeBirth) <=22)

（7）子查询

--查询工资比b高的员工信息
select * from employee where EmployeeSalary > 
(select EmployeeSalary from employee where EmployeeName = 'b')

（8）查询生肖

--查询属牛的员工信息

--鼠 (子)、牛 (丑)、虎 (寅)、兔 (卯)、龙 (辰)、蛇 (巳)、马 (午)、羊 (未)、猴 (申)、鸡 (酉)、狗 (戌)、猪 (亥)
--  4        5       6        7        8        9       10       11      12       1       2        3
select * from employee where
year(EmployeeBirth) % 12 = 5

给员工表添加一列显示其生肖

--给员工表添加一列显示其生肖
select *,
case 
  when year(EmployeeBirth) % 12 = 4 then '鼠'
  when year(EmployeeBirth) % 12 = 5 then '牛'
  when year(EmployeeBirth) % 12 = 6 then '虎'
  when year(EmployeeBirth) % 12 = 7 then '兔'
  when year(EmployeeBirth) % 12 = 8 then '龙'
  when year(EmployeeBirth) % 12 = 9 then '蛇'
  when year(EmployeeBirth) % 12 = 10 then '马'
  when year(EmployeeBirth) % 12 = 11 then '羊'
  when year(EmployeeBirth) % 12 = 12 then '猴'
  when year(EmployeeBirth) % 12 = 1 then '鸡'
  when year(EmployeeBirth) % 12 = 2 then '狗'
  when year(EmployeeBirth) % 12 = 3 then '猪'
  else ''
end
生肖
  from employee
--简略用法
select *,
case year(EmployeeBirth) % 12
  when  4 then '鼠'
  when 5 then '牛'
  when 6 then '虎'
  when 7 then '兔'
  when 8 then '龙'
  when 9 then '蛇'
  when 10 then '马'
  when 11 then '羊'
  when 12 then '猴'
  when 1 then '鸡'
  when 2 then '狗'
  when 3 then '猪'
  else ''
end
生肖
  from employee

（9）模糊查询

%表示匹配0、1、多个字符
_表示匹配有且只有一个字符
[]表示匹配范围内
[^]表示不在范围内

--查询第一个字母是a的员工信息
  select * from employee where EmployeeName  like 'a%'
  --查询出名字中含e的员工
  select * from employee where EmployeeName  like '%e%'
  --查询出名字中含有e或a的员工
  select * from employee where EmployeeName  like '%a%'
  or EmployeeName  like '%e%'
  --查询第一个字母是a且名字长度为2的员工信息
  select * from employee where EmployeeName  like 'a_'
   --或者
  select * from employee where substring(EmployeeName,1,1) = 'a'
  and len(EmployeeName) = 2

 --查询电话号码以138开头，第四位7或者8，最后一位是5
  select * from employee where EmployeePhoneNum  like '138[7,8]%5'
  --开头138 第四位2-5 最后一个不是2和3
  select * from employee where EmployeePhoneNum  like '138[2,3,4,5]%[^2,3]'

（10）聚合函数

count：求数量
max
min
sum
avg

基本查询

--员工总数
  select count(*) 人数 from employee
  --求最大工资
  select max(EmployeeSalary) 最高工资 from employee
  --求最小工资
  select min(EmployeeSalary) 最低工资 from employee
  --求平均工资,并保留2位小数点 
  select round(avg(EmployeeSalary),2) 平均工资 from employee
  --综合
  select count(*) 人数,max(EmployeeSalary) 最高工资,min(EmployeeSalary) 最低工资 ,round(avg(EmployeeSalary),2) 平均工资 from employee

有条件的查询

--查出北京地区员工人数等
  select count(*) 人数,max(EmployeeSalary) 最高工资,min(EmployeeSalary) 最低工资 ,round(avg(EmployeeSalary),2) 平均工资 
  from employee where EmployeeAddress = '北京'
  --求出工资比平均工资高的人员信息
  select * from employee where EmployeeSalary >
  (select round(avg(EmployeeSalary),2) from employee)
  --求数量，年龄最大值，最小值，年龄总和，平均值，一行显示
  --scheme 1
  select count(*) 数量,
         max(year(getdate()) - year(EmployeeBirth)) 最高年龄,
		 min(year(getdate()) - year(EmployeeBirth)) 最低年龄,
		 sum(year(getdate()) - year(EmployeeBirth)) 年龄总和,
		 avg(year(getdate()) - year(EmployeeBirth)) 平均年龄
 from employee
 --scheme 2
 select count(*) 数量,
         max(DATEDIFF(year,EmployeeBirth,getdate())) 最高年龄,
		 min(DATEDIFF(year,EmployeeBirth,getdate())) 最低年龄,
		 sum(DATEDIFF(year,EmployeeBirth,getdate())) 年龄总和,
		 avg(DATEDIFF(year,EmployeeBirth,getdate())) 平均年龄
 from employee
 --加上条件
 where EmployeeAddress = '北京'

（11）分组查询

--根据员工所在地统计员工人数、综合、最大、最小工资
 select EmployeeAddress 地区, count(*) 人数,max(EmployeeSalary) 最高工资,min(EmployeeSalary) 最低工资 ,round(avg(EmployeeSalary),2) 平均工资 
 from employee
 group by EmployeeAddress

条件语句where需要放在group by 的前面

--根据员工所在地统计员工人数、综合、最大、最小工资，2000年及其以后的员工不参与
 select EmployeeAddress 地区, count(*) 人数,max(EmployeeSalary) 最高工资,min(EmployeeSalary) 最低工资 ,round(avg(EmployeeSalary),2) 平均工资 
 from employee
 where EmployeeBirth < '2000-1-1'
 group by EmployeeAddress

where中不能使用聚合函数，要用having语句放在group by后

 --根据员工所在地统计员工人数、综合、最大、最小工资，2000年及其以后的员工不参与，且员工人数至少2人
 select EmployeeAddress 地区, count(*) 人数,max(EmployeeSalary) 最高工资,min(EmployeeSalary) 最低工资 ,round(avg(EmployeeSalary),2) 平均工资 
 from employee
 where EmployeeBirth < '2000-1-1'
 group by EmployeeAddress
 having count(*)>=2

四、DCL数据控制语言

1. 创建用户
CREATE USER ‘用户名’@地址 IDENTIFIED BY ‘密码’

create user 'user1'@localhost identified by '111'

2. 用户授权
GRANT 权限 1, … , 权限 n ON 数据库.* TO ‘用户名’@地址

grant create,alter,drop,insert,update,delete on praticeDB.* to 'user1'@localhost

3. 撤销授权
REVOKE 权限 1, … , 权限 n ON 数据库.* FROM ‘用户名’@地址

revoke create,alter,drop,insert,update,delete on praticeDB.* from 'user1'@localhost

4. 查看用户权限
SHOW GRANTS FOR ‘用户名’@地址;

show grants for 'user1'@localhost

5. 删除用户

drop user 'user1'@localhost

6. 修改用户密码
alter user ‘用户名’@localhost identified by ‘新密码’

alter user 'user1'@localhost  identified by '1111111'