我的139道MySQL题

查询关键字：

1. 删除重复内容：distinct

2. 模糊查询：like

3. between and

4.筛选查询条件在in括号里面的人的信息

5. 查询员工表中年龄字段为（或者不为）null的员工信息

6. 查询员工工资从高到低（倒序）排序显示：正序、倒序

7. 字符串长度：length

8. 拼接字符串：concat

9. 小写转大写：upper

10. 大写转小写：lower

11. 截位：substring

12.去掉空格或者其它字符：trim

13. 替换字符：replace

14. 四舍五入：round

15. 向上取整、向下取整：ceil、floor

16. 截断：truncate

17. 截取：substr

18. 取余函数：mod

19. 日期函数：

此刻时间：now

返回日期，不包含时间：curdate

返回时间，不包含日期：curtime

返回年：year

返回月：month

格式化日期：date_format

字符串转日期：str_to_date

20. if函数

21. case语句

22. 分组函数：

求和函数：sum

平均数：avg

最大值：max

最小值：min

求一列有多少条数据：count

23.分组查询：分组查询，分组查询必须和分组函数一起来用才可以（用来查询分组信息）

24. 过滤条件：having。可以放分组函数，where不可以

25.多表连接

26.交叉连接

27.避免交叉连接，即内连接

28. 等值内连接

29. 不等值内连接

30. 外连接、左外连接、右外连接

31. 联合查询

32. 自连接

33. 子查询

34. 标量子查询

35. all、any、in

36. 列子查询

37. 行子查询可以使用等号

38. select后子查询

39. from后子查询

40. 相关子查询：exists

41. 分页：limit

42. 关键字的书写顺序及执行顺序

43. 插入语句

44. 插入多条数据

45. 删除数据

46. 更新数据

47. 开启/回滚事务

48. 创建视图

49. 级联视图

50. 本地检查

51. 级联检查

52. 更新视图

53. 删除视图

54. 查看视图

55. 查看视图的定义

56.约束（非空约束、唯一约束、主键约束、默认约束、外键约束、级联删除）

57.DDL（创建数据库、查看有几个数据库、使用数据库、创建一个表、查看当前数据库有哪些表、删除当前数据库中的表）

58.修改表的属性（加字段、修改表的字段名称和类型、删除列、）

59.变量（系统变量：全局变量、会话变量；自定义变量：用户变量、局部变量）

60.查看数据库中的变量（全局变量、会话变量、模糊查询、给全局变量或者会话变量赋值，）

61.存储过程（创建存储过程）

62. INOUT的使用

63. PLSQL：过程语言

64. 游标



习题索引：

1. //查询一个数据库中的所有内容

2. //删除重复的内容

3. //查询名字为De Haan，并且工资为90000的人的信息。

4. //查询员工表中，名字为Hunold或Ernst的人的信息

5. // 给查询的列明换一个别名

6. //查询员工表中名字不等于周杰伦的人的信息

7. //查询工资大于1000的人

8. //查询部门编号等于100的人

9. //查询部门编号是50的部分的名称

10. //查询年龄大于30岁并且部门编号不等于50的人

11. //模糊查询：查询员工表中叫周什么的人的信息

12. //查询员工表中叫周_(姓名只有两个字)的信息

13. //查询员工表中以D开头的人的信息

14. //查询员工姓名带有k字母的人

15. //查询姓名第二位为a的员工的工资、姓名和年龄

16. //查询部门表中的所有信息

17. //查询部门名为a开头的部门名称

18. //查询员工工资在10000到100000之间的人的信息，between and 包含临界值

19. //查询员工id是100或106的人的信息

20. //查询姓名为k开头的员工的年龄为30或者35的人的信息

21. //查询员工表中年龄字段为null的员工信息

22. //查询员工表中年龄字段不为null的员工信息

23. //查询员工工资从高到低（倒序）排序显示

24. //查询员工工资从低到高（正序）排序显示，如果不写排序关键字的话就是按照正序输出

25. //显示出表employees部门编号在80-100之间的姓名、职位

26. //查询名字包含k的员工信息，并先按工资降序输出

27. //显示出表employees的manager_id是100,101,110的员工姓名、职位

28. //选择工资不在5000到12000的员工的姓名和工资，不包括边界值

29. //选择姓名中有字母a员工姓名

30. //选择在20或50号部门工作的员工姓名和部门号

31. //查询员工表中的员工姓名的长度包括空格

32. //查询员工表中工资大于5000的人的人的姓名加上好厉害呀

33. //将员工的姓名按首字母排序，并输出姓名的长度和员工姓名

34. //将部门名称中首字母带有A(a)的部门名称，将所有A(a)替换为B然后把该部门名称输出

35. //查询出emp表中，工资不在1000到3000之间的员工，其工资和姓名（姓名要求全部大写，工资包含临界值）

36. //查询出emp表中，工资大于10000或者小于3000的员工，其姓名从1到5截位后的结果，及其职位的长度（工资包含临界值）

37. //查询出emp表中,所有人工资除以3后,保留2位小数后结果的长度（需要精准2位）。

38. //查询出员工表中, 所有员工的编号, 姓名(要求所有字母大写,只要1到4位),职位长度除以3取余。

39. //查询员工表中名字为Ernst 的 编号, 姓名, 职位, 及部门编号。

40. //查询员工表中, 工资在10000到20000之间的员工,其姓名, 工资除以3后保留两位小数的结果。

41. //查询名字是De Haan的员工，则工资加666，否则工资不变。

42. //查询员工表中工资等于4200的人，输出你好棒，工资等于4800的人，输出太棒了，否则输出加油

43. //查询员工表中，如果工资大于10000的人，输出嘻嘻，否则输出哈哈

44. //查询员工表中工资大于10000，工资列显示“高”否则显示低。

45. //当员工表中存在工资为9000的人，输出其姓名，否则输出工资

46. //当员工年薪大于288000的人输出高，有小于204000.00的人输出较低，否则输出继续

47. //工资是10000-20000输出A，否则输出B，并在显示中加入员工年龄，员工工资。

48. //查询员工表中工资最高的人的信息。

49. //查询员工表中年龄大于30岁的人的平均工资

50. //查询员工表中年龄等于30岁的一共多少人。

51. //查询公司员工工资的最大值，最小值，平均值，总和

52. //查询员工数量和工资平均值

53. //查询每个部门的人数

54. //查询每个部门下的工资总和

55. //查询人数大于10的部门的信息

56. //查询具有各个职位的员工人数。

57. //查询各职位的员工工资的最大值，最小值，平均值，总和。

58. //查询各部门平均工资小于30000的部门编号。

59. //求出各部门中平均工资大于6000的部门以及其平均工资

60. //查询各部门部门编号,员工数量和工资平均值

61. //查询员工所对应的部门的名称

62. //查询员工所对应的工资等级alter

63. //查询员工所在部门的部门名字、员工的名字。

64. //查询年龄为30岁的人对应的部门名称，部门编号

65. //查询员工姓名为Kochhar的人对应的部门名称

66. //查询部门所在城市名称

67. //查询部门名为SAL或IT的员工信息

68. //查询90号部门job_id和location_id

69. //查询名称为"Mar"的部门所对应的城市名称、部门编号

70. //查询员工所对应的职位全称、员工姓名、职位id（没有对应则不显示）

71. //查询员工所对应的部门名称、职位全称（没有对应则不显示）

72. //查询年龄大于30的人所对应的部门名称、员工姓名、员工编号，薪资

73. //查询年薪大于120000的人的部门编号、部门名称、员工姓名

74. //查询员工表中对应的部门名称（员工信息全部展示）

75. //查询各部门的最大工资，平均工资。

76. //查询各部门员工工资小于5000的人数。

77. //查询员工对应的职位全称、员工姓名，员工薪资（将员工信息全部展示出来）

78. //查询员工所对应的职位全称、员工姓名、职位id

79. //查询员工所对应的部门名称、职位全称

80. //查询员工姓名，老板姓名在一列显示出来；员工年龄，老板年龄在一列显示出来

81. //查询员工表中员工以及对应的经理的姓名

82. //查询哪个城市没有部门，并输出其城市的名称，位置编号。 子查询

83. //查询出emp表中工资大于5000

,并且小于10000的员工的姓名和工资

84. //查询员工编号为100的人所对应的部门编号、部门名称

85. //查询老板年龄和员工年纪，在一列显示出来（不去重复）

86. //查询员工对应的部门编号和部门名称。

87. //查询工资比105号员工高的所有人信息

88. //查询工资比Austin高的的人的信息

89. //查询工资为10000的人的部门名称，员工姓名。

90. //查询年薪比100号员工高的所有人信息->

91. //查询工资最低的员工信息

92. //查询比103号或104号员工高的员工的信息

93. //查询年龄、工资都与Hunold相同的员工信息

94. //年龄比100号或106号员工小的员工的信息

95. //查询工资和部门都与De Haan相同的员工信息

96. // location_id是1400或1700的部门中的所有员工姓名

97. //查询员工工资比本部门平均工资高（不包含临界值）的员工的员工号，姓名和工资

98. //查询比103号年龄大，并且工资高的人的信息

99. //查询与103号年龄和工资都相同的人的信息

100. //查询部门名为 SAL 或IT 的员工信息。

101. //查询 90 号部门job_id 和 location_id

102. //查询名称为“Mar”的部门所对应的城市名称、部门编号。

103. //查询比103号员工年薪高，年龄大的员工编号、姓名

104. //利用部门表查询每个部门的员工个数、部门编号、部门名称

105. //查询每个职位的员工个数、职位编号、职位名称

106. //查询工资最高的员工的姓名，要求列名显示为“姓名”

107. //查询员工所对应的部门名称、职位全称（没有对应则不显示）。

108. //查询位置表中每个位置的位置编号、部门数量、国家名。

109. //每个部门的平均工资的工资等级

110. //查询部门表是否存在年龄大于21岁的员工，如果存在则输出该部门的信息。

111. //查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名。

112. //查询和 Hunold 相同部门的员工姓名和工资

113. //查询工资比公司平均工资高的员工的员工号，姓名和工资

114. //查询平均工资最高的部门信息

115. //查询所有部门所对应的城市的位置

116. //查询比105号员工的名字长度长并且工资高的员工信息

117. //查询年龄大于20岁的员工部门

118. //查询员工表中前四条数据

119. //查询员工名字里带a，并且年龄在25-40之间的人的信息

120. //每个部门的平均工资的工资等级

121. //查询城市名称是Tokyo的部门编号

122. //查询部门表是否存在年龄大于30岁的员工，如果存在则输出该部门的信息

123. //查询各部门工资总和以及部门编号

124. //查询具有各个职位的员工人数

125. //查询各职位的员工工资的最大值、最小值、平均值、总和

126. //查询各部门平均工资小于5000的部门编号

127. //查询员工表中从第4条开始，到第7条的数据

128. //查询各部门编号，员工数量和工资平均值

129. //查询所有部门所对应的员工的部门的名称、部门号和员工信息

130. //查询各部门员工工资小于5000的人的平均工资

131. //查询所有部门的编号、员工数量、和平均工资

132. //查询员工编号最小，并且工资最高的员工信息

133. //查询各部门中年龄大于30岁的人的平均工资

134. //求出各部门中平均工资大于7000的部门以及其平均工资

135. //将员工表中工资比较高的5个人显示出来

136. //将员工表中工资最高的5个人按照编号降序查询

137. //查询员工表中id为103号员工的信息

138. //用存储过程显示名字为“De Haan”的人的信息

139. //用存储过程实现根据名字“Hunold”的员工，返回该员工所对应的部门名称


第一天：

// 查询一个数据库中的所有内容

SELECT *

FROM employees;


// 删除重复的内容

SELECT distinct last_name

from employees;



-- 查询名字为De Haan，并且工资为90000的人的信息。

select *

from employees

where last_name = 'De Haan'

and salary = 17000;


-- 查询员工表中，名字为Hunold或Ernst的人的信息

select *

from employees

where last_name = 'Hunold'

or last_name = 'Ernst';


// 给查询的列明换一个别名

select distinct last_name as '员工姓名'

from employees;


-- 查询员工表中名字不等于周杰伦的人的信息

select *

from employees

where last_name <> '周杰伦';


-- 查询工资大于1000的人

select *

from employees

where salary > 1000;


-- 查询部门编号等于100的人

select *

from employees

where department_id = 100;


-- 查询部门编号是50的部分的名称

select department_name

from departments

where department_id = 50;


-- 查询年龄大于30岁并且部门编号不等于50的人

select *

from employees

where year > 30 and department_id<> 50;


-- 模糊查询：查询员工表中叫周什么的人的信息

select *

from employees

where last_name like '周%';


-- 查询员工表中叫周_(姓名只有两个字)的信息

select *

from employees

where last_name like '周_';


-- 查询员工表中以D开头的人的信息

select *

from employees

where last_name like 'D%';


-- 查询员工姓名带有k字母的人

select *

from employees

where last_name like '%k%';


-- 查询姓名第二位为a的员工的工资、姓名和年龄

select salary,last_name,year

from employees

where last_name like '_a%';


-- 查询部门表中的所有信息

select *

from departments;


-- 查询部门名为a开头的部门名称

select department_name

from departments

where department_name like 'a%';


-- 查询员工工资在10000到100000之间的人的信息，between and 包含临界值

select *

from employees

where salary between 10000 and 100000;


-- 和下面这条语句相同

select *

from employees

where salary >= 10000 and salary <=100000;


-- 筛选查询条件在in括号里面的人的信息

-- 查询员工id是100或106的人的信息

select *

from employees

where employee_id in(100,106,101,100,104,105,107);



-- 查询姓名为k开头的员工的年龄为30或者35的人的信息

select *

from employees

where year in (30, 35) and last_name like'k%';


-- 查询员工表中年龄字段为null的员工信息

select *

from employees

where year is null;


-- 查询员工表中年龄字段不为null的员工信息

select *

from employees

where year is not null;


-- 查询员工工资从高到低（倒序）排序显示

select *

from employees

order by salary desc;


-- 查询员工工资从低到高（正序）排序显示，如果不写排序关键字的话就是按照正序输出

select *

from employees

order by salary asc;


-- 显示出表employees部门编号在80-100之间的姓名、职位alter

select last_name,job_id

from employees

where department_id between 80 and 100;


-- 查询名字包含k的员工信息，并先按工资降序输出

select *

from employees

where last_name like '%k%'

order by salary desc;


-- 显示出表employees的manager_id是100,101,110的员工姓名、职位alter

select last_name,job_id

from employees

where manager_id in (100,101,110);


-- 选择工资不在5000到12000的员工的姓名和工资，不包括边界值

select *

from employees

where salary not between 5000 and 12000;


-- 选择姓名中有字母a员工姓名

select last_name

from employees

where last_name like '%a%';


-- 选择在20或50号部门工作的员工姓名和部门号

select last_name,department_id

from employees

where department_id in (60,100);




-- 查询员工表中的员工姓名的长度包括空格

select length(last_name),last_name

from employees;


-- length()包括空格

select length('tom');


-- 查询员工表中工资大于5000的人的人的姓名加上好厉害呀

select concat(last_name,'好厉害呀')

from employees

where salary > 5000;


-- 将小写转大写

select upper(last_name)

from employees;


-- 将大写转小写

select lower(last_name)

from employees;


-- substring，第一位索引为1，第二个参数是索引开始位置，第三个参数是长度

select substr('我是小明',2);

select substr('我是小明',2,2);


-- trim去掉空格

select trim('        小明           ');

select length(trim('        小明           '));


-- trim去掉字符，both的效果和不写是一样的

select trim('a' from 'aaaaaaaaaa小明aaaaaaaaaaa');

select trim(leading 'a' from 'aaaaaaaaaa小明aaaaaaaaaaa');

select trim(trailing 'a' from 'aaaaaaaaaa小明aaaaaaaaaaa');

select trim(both 'a' from 'aaaaaaaaaa小明aaaaaaaaaaa');



-- replace

select replace('我是小明','小明','小红');


-- 作业

-- 将员工的姓名按首字母排序，并输出姓名的长度和员工姓名

select length(last_name),last_name

from employees

order by last_name asc;


-- 将部门名称中首字母带有A(a)的部门名称，将所有A(a)替换为B然后把该部门名称输出


select *

from departments;


select replace(upper(department_name),'A','B')

from departments

where department_name like 'a%';


-- 查询出emp表中，工资不在1000到3000之间的员工，其工资和姓名（姓名要求全部大写，工资包含临界值）

select salary,upper(last_name)

from employees

where salary not between 1000 and 3000;


-- 查询出emp表中，工资大于10000或者小于3000的员工，其姓名从1到5截位后的结果，及其职位的长度（工资包含临界值）

select *

from employees;

select substr(last_name,1,5) ,length(job_id)

from employees

where salary >= 10000 or salary <=3000;




第二天：

-- round，一个参数直接四舍五入返回整数，两个参数的话第二位为保留小数的位数

select round(12.5533, 2);


-- 向上取整数

select ceil(1.5);


-- 向下取整

select floor(-1.5);


-- 截断truncate，从哪里开始就不要了，是数学函数,作用于小数部分，可以理解为保留几位小数

select truncate(12.589,1);

-- 截取，从哪里开始要，是字符函数

select substr('我是小明',2);


-- 取余函数mod，被除数的正负和余数的正负一致

select mod(-10,3);


-- 作业------------------------

-- 查询出emp表中,所有人工资除以3后,保留2位小数后结果的长度（需要精准2位）。

select *

from employees;

select length(truncate(salary/3,2)),salary

from employees;


-- 查询出员工表中, 所有员工的编号, 姓名(要求所有字母大写,只要1到4位),职位长度除以3取余。

selectemployee_id,substr(upper(last_name),1,4),mod(length(job_id),3)

from employees;


-- 查询员工表中名字为Ernst 的编号, 姓名, 职位, 及部门编号。

selectemployee_id,last_name,job_id,department_id

from employees

where last_name = 'Ernst';


-- 查询员工表中, 工资在10000到20000之间的员工,其姓名, 工资除以3后保留两位小数的结果。

selectlast_name,salary,truncate(salary/3,2)

from employees

where salary between 10000 and 20000;

-----------------------------


-- 日期函数

-- 此刻时间

select now();

-- 返回日期，不包含时间

select curdate();

-- 返回时间，不包含日期

select curtime();


-- 返回年，括号里面必须要有时间的参数

select year(now());

select year('2017/6/24 13/25.26');


-- 返回月alter

select month(now());

select month('2017/6/24 13/25.26');


-- 格式化日期alter

select date_format(now(),'%Y,%m,%d');

select date_format(now(),'%Y:%m:%d%H:%i:%s');


-- 字符串转日期alter

select str_to_date('2017-6-24 13:25:26','%Y-%m-%d');


-- if函数alter

select if(1=1,true,'FF');

select if(1=2,1,0);


-- 查询名字是De Haan的员工，则工资加666，否则工资不变。

select *

from employees;

select if(last_name='De Haan', salary +666,salary),last_name

from employees;


-- case语句alter

-- 第一种格式：相当于switch

-- 查询员工表中工资等于4200的人，输出你好棒，工资等于4800的人，输出太棒了，否则输出加油alter

select *

from employees;


select

case salary

when 4800

then '太棒了'

when 4200

then '你好棒'

else '加油哦'

end

from employees;


-- 第二种格式：相当于if语句

-- 查询员工表中，如果工资大于10000的人，输出嘻嘻，否则输出哈哈

select

case when salary > 10000

then '嘻嘻'

else '哈哈'

end

from employees;


-- 作业--------------------------------

-- 查询员工表中工资大于10000，工资列显示“高”否则显示低。

-- 第一种

select if(salary > 10000,'工资高','工资低'),salary

from employees;


-- 第二种

select

case when salary > 10000

then '高'

else '低'

end

salary

from employees;



-- 当员工表中存在工资为9000的人，输出其姓名，否则输出工资

select if(salary = 9000,last_name,salary)

from employees;


-- 当员工年薪大于288000的人输出高，有小于204000.00的人输出较低，否则输出继续加油。

select

case when salary * 12 > 288000

then '高'

when salary * 12 < 204000.00

then '低'

else '继续加油'

end

from employees;


-- 工资是10000-20000输出A，否则输出B，并在显示中加入员工年龄，员工工资。

select year,salary,

case when salary > 10000 and salary <20000

then 'A'

else 'B'

end

salary

from employees;

-- 作业----------------------------------


-- 分组函数

-- 求和函数，一个单元格就是一个标量，分组函数是排除null的

select sum(salary)

from employees;


-- 字符串或者数字加上null值都会变成null值

 select 10 + null;


 --求平均值，默认情况下，除数不会把null计算在内

 select *

 fromemployees;


 --这样写，就可以把null计算在内

 select avg(if(salary is null,0,salary))

 fromemployees;


 select avg(salary)

 fromemployees;


 --求最大值

 select max(salary)

 fromemployees;


 --求最小值alter

 select min(salary)

 fromemployees;


 --max和min也可以用于计算字符串的大小alter

 --最大的是汉字，最小的是数字，中间是英文字符，

 --以a——z排序，增大，从第一位开始比，第一位相同，比较第二位

select min(last_name)

from employees;


select min(last_name)

from employees;


-- 在数据库里，两个字符串相直接相加，结果为0

-- 必须用concat函数

-- sum和avg不可以用于求字符串，max和min可以


-- count()括号里面可以写*，同样可以查询一列有多少条数据，但效率更低

-- 求一列有多少条数据

select count(employee_id)

from employees;


-- 作业----------------------------

-- 查询员工表中工资最高的人的信息。

select *

from employees;

-- 1.查询最高工资alter

-- 2.工资等于最高工资的人的信息alter


select *

from employees

where salary = (select max(salary)

from employees);


-- 查询员工表中年龄大于30岁的人的平均工资

select avg(salary)

from employees

where year > 30;


-- 查询员工表中年龄等于30岁的一共多少人。

select count(year)

from employees

where year = 30;


-- 查询公司员工工资的最大值，最小值，平均值，总和

selectmax(salary),min(salary),avg(salary),sum(salary)

from employees;


-- 查询员工数量和工资平均值

select count(last_name),avg(salary)

from employees;

-- ----------------------------------------------


-- 分组查询，分组查询必须和分组函数一起来用才可以

-- 查询每个部门的人数

select department_id,count(*)

from employees

group by department_id;


--如果这么用的话，那么只会显示每组的信息的第一条，其余信息均不显示

select *

from employees

group by department_id;


-- 查询每个部门下的工资总和

select department_id,sum(salary)

from employees

group by department_id;


-- having 过滤条件，having可以放分组函数，where不可以

-- 查询人数大于10的部门的信息

select department_id

from employees

group by department_id

having count(employee_id) > 1;


-- 作业-----------------------------------alter

-- 查询具有各个职位的员工人数。

select *

from employees;

select job_id,count(employee_id)

from employees

group by job_id;


-- 查询各职位的员工工资的最大值，最小值，平均值，总和。

selectjob_id,max(salary),min(salary),avg(salary),sum(salary)

from employees

group by job_id;


-- 查询各部门平均工资小于30000的部门编号。

select *

from employees;


select department_id

from employees

group by department_id

having avg(if(salary is null, 0, salary))< 30000;


-- 求出各部门中平均工资大于6000的部门以及其平均工资

select department_id,avg(salary)

from employees

group by department_id

having avg(if(salary is null, 0, salary))> 6000;


-- 查询各部门部门编号,员工数量和工资平均值

selectdepartment_id,count(last_name),avg(salary)

from employees

group by department_id;

----------------------------------------------------


-- 多表连接

-- 交叉连接，要避免交叉连接

select count(*)

from employees cross join departments;

-- 可以用，号代替crossjoin

select count(*)

from employees, departments;


-- 避免交叉连接，加上有效的限制条件

-- 即，所谓的内连接

select *

from employees inner join departments

on employees.department_id =departments.department_id


第三天：

-- 等值内连接

-- 查询员工所对应的部门的名称

select departments.department_name

from employees inner join departments

on employees.department_id =departments.department_id;


-- 另外一种简写的方法

select d.department_name

from employees as e, departments as d

where e.department_id = d.department_id;


-- 不等值内连接

-- 查询员工所对应的工资等级alter

select j.grade_level,e.last_name

from employees as e inner join job_gradesas j

on e.salary between j.lowest_sal andj.highest_sal;


-- 作业-------------------------------------------------

-- 查询员工所在部门的部门名字、员工的名字。

select *

from departments;


select d.department_name, e.last_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id;


-- 查询年龄为30岁的人对应的部门名称，部门编号

select d.department_name, d.department_id

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where e.year = 30;


-- 查询员工姓名为Kochhar的人对应的部门名称。

select d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where e.last_name = 'Kochhar';


-- 查询部门所在城市名称alter

select *

from locations;


select d.department_name,l.city

from departments as d inner join locationsas l

on d.location_id = l.location_id;


-- 查询部门名为SAL或IT的员工信息alter

select *

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where d.department_name in ('SAL','IT');


select *

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where d.department_name in ('SAL','IT');


select *

from employees as e inner join departmentsas d

on e.department_id = d.department_id

and d.department_name in ('SAL','IT');


-- 查询90号部门job_id和location_id

select *

from departments;


select *

from employees;


select d.location_id, e.job_id

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where d.department_id = 90;


-- 查询名称为"Mar"的部门所对应的城市名称、部门编号alter

select d.department_id,l.city

from departments as d inner join locationsas l

on d.location_id = l.location_id

where d.department_name = 'Mar';


-- 查询员工所对应的职位全称、员工姓名、职位id（没有对应则不显示）alter

select *

from jobs;


select j.job_title,j.job_id

from employees as e inner join jobs as j

on e.job_id = j.job_id;


-- 查询员工所对应的部门名称、职位全称（没有对应则不显示）

select *

from departments;


select *

from employees;


select *

from jobs;


select e.last_name, d.department_name, j.job_title

from employees as e inner join departmentsas d inner join jobs as j

on e.job_id =j.job_id and e.department_id =d.department_id;


-- 查询年龄大于30的人所对应的部门名称、员工姓名、员工编号，薪资alter

selectd.department_name,e.last_name,e.employee_id,e.salary

from employees as e inner join departmentsas d

on e.department_id = d.department_id

and e.year >30;


-- 查询年薪大于120000的人的部门编号、部门名称、员工姓名alter

selecte.last_name,d.department_id,d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where salary * 12 > 120000;

---------------------------------------------------------------



-- 外连接

-- 左外连接：左侧的表所有记录都会展示出来，符合条件的记录会与右侧的表相关联，

-- 不符合条件的记录将会和右侧的表空记录相关联。


-- 查询员工表中对应的部门名称（员工信息全部展示）

-- 内连接的情况，必须一一对应才显示

select e.last_name,d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id;

-- 左外连接：左边的相关信息全部显示，右边的没有以null代替

select e.last_name,d.department_name

from employees as e left join departmentsas d

on e.department_id = d.department_id;


-- 右外连接alter

select e.last_name,d.department_name

from employees as e right join departmentsas d

on e.department_id = d.department_id;



-- 作业-----------------------------------------------alter

-- 查询各部门的最大工资，平均工资。

select department_id,max(salary),avg(salary)

from employees

group by department_id;


-- 查询各部门员工工资小于5000的人数。

select department_id,count(employee_id)

from employees

where salary < 5000

group by department_id;


-- 这是错的

select department_id,count(salary <5000)

from employees

group by department_id;


select *

from employees;


-- 查询员工对应的职位全称、员工姓名，员工薪资（将员工信息全部展示出来）

select e.last_name, j.job_title, e.salary

from employees as e left join jobs as j

on e.job_id = j.job_id;


-- 查询员工所对应的职位全称、员工姓名、职位id 。

select e.last_name, j.job_title, e.job_id

from employees as e left join jobs as j

on e.job_id = j.job_id;


-- 查询员工所对应的部门名称、职位全称。

select *

from departments;


select e.last_name, j.job_title,d.department_name

from employees as e inner join jobs as jinner join departments as d

on e.job_id = j.job_id and e.department_id= d.department_id;


-- 联合查询

-- 查询员工姓名，老板姓名在一列显示出来；员工年龄，老板年龄在一列显示出来

-- 放在同一列的最好是同一类型的数据

select last_name,year from employees

union

select name, boss_year from boss;


-- union可以去除重复数据，union

all不会

select last_name,year from employees

union all

select name, boss_year from boss;


-- 自连接

-- 查询员工表中员工以及对应的经理的姓名

select emp.last_name as '员工姓名',

emp.manager_id as '员工经理编号',

manager.last_name as '经理的姓名',

manager.employee_id as '经理的编号'

from employees as emp inner join employeesas manager

on emp.employee_id = manager.manager_id;4


-- 作业------------------------------------------------alter


-- 查询哪个城市没有部门，并输出其城市的名称，位置编号。 子查询

select *

from locations;


select *

from departments;


select d.department_id, d.location_id,l.location_id

from departments as d left join locationsas l

on d.location_id = l.location_id;


-- 查询出emp表中工资大于5000 ,并且小于10000的员工的姓名和工资。

select last_name, salary

from employees

where salary > 5000 and salary <10000;


-- 查询员工编号为100的人所对应的部门编号、部门名称。

select *

from employees;


select e.last_name, d.department_id,d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where e.employee_id = '1';


-- 查询老板年龄和员工年纪，在一列显示出来（不去重复）。

select *

from boss;


select name, boss_year

from boss

union all

select last_name,year

from employees;



-- 查询员工对应的部门编号和部门名称。

selecte.last_name,e.department_id,d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id;


-----------------------------------------------------

-- 子查询

-- 查询工资最高的人的信息alter

-- 1. 查询最高工资alter

-- 2. 查询1.的信息alter



select *

from employees

where salary = (select max(salary)

from employees);


-- 所谓标量：一行一列这么一个单元格就称之为一个标量

-- 子查询的子句返回的结果集是一个标量，这个子查询就是标量子查询

select max(salary)

from employees;


-- 查询工资比105号员工高的所有人信息alter

select salary

from employees

where employee_id = 105;


select *

from employees

where salary > (select salary

from employees

where employee_id = 105);


-- 作业----------------------------------

-- 查询工资比Austin高的的人的信息

select salary

from employees

where last_name = 'Austin';


-- 必须保证大于一个值，即一个标量

-- any的用法

-- any：大于最小，大于最小的。

select *

from employees

where salary > any (select salary

from employees

where last_name = 'Austin');


-- all的用法

select *

from employees

where salary > all (select salary

from employees

where last_name = 'Austin');


-- 等于的情况：等于any 和 in 是等价的

select *

from employees

where salary in(select salary

from employees

where last_name = 'Austin');


-- 查询工资为10000的人的部门名称，员工姓名。

select *

from employees;


select e.last_name, d.department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where e.salary = 24000;


-- 查询年薪比100号员工高的所有人信息->

select *

from employees

where (salary * 12) > (

select salary * 12

from employees

where employee_id = 1);


-- 查询工资最低的员工信息

select min(salary)

from employees;


select *

from employees

where salary = (select min(salary)

from employees);


-- 列子查询：子查询的结果是一列多行

-- 查询比103号或104号员工高的员工的信息alter

select salary

from employees

where employee_id in(103,104);


select *

from employees

where salary > any(select salary

from employees

where employee_id in(103,104));


-- 作业-------------------------------------------alter


-- 查询年龄、工资都与Hunold相同的员工信息alter

select *

from employees;


select salary

from employees

where last_name = 'Hunold';


select year

from employees

where last_name = 'Hunold';


select *

from employees

where year = (select year

from employees

where last_name = 'Hunold')

and salary = (select salary

from employees

where last_name = 'Hunold');

-- 年龄比100号或106号员工小的员工的信息alter

select year

from employees

where employee_id in(100,106);


select *

from employees

where year > any(select year

from employees

where employee_id in(100,106));


-- 查询工资和部门都与De Haan相同的员工信息

select *

from employees;


select *

from employees

where salary in(select salary

from employees

where last_name = 'De Haan')

and department_id in (select department_id

from employees

where last_name = 'De Haan');


-- location_id是1400或1700的部门中的所有员工姓名alter

select e.last_name

from departments as d inner join employeesas e

on e.department_id = d.department_id

where d.location_id in(1400, 1700);


-- 查询员工工资比本部门平均工资高（不包含临界值）的员工的员工号，姓名和工资

select department_id, avg(salary)

from employees

group by department_id;


select employee_id, last_name, salary

from employees

where salary > any (select department_id,avg(salary)

from employees

group by department_id)

group by department_id;


-- 百度上的答案

SELECTemployee_id,last_name,salary,e.department_id

FROM employees e

INNER JOIN (

   SELECT AVG(salary) ag,department_id

   FROM employees

   GROUP BY department_id

) ag_dep

ON e.department_id = ag_dep.department_id

WHERE salary > ag_dep.ag;


-- 最后一行的where条件也可以写在on里面，用and连接

SELECTemployee_id,last_name,salary,e.department_id

FROM employees e

INNER JOIN (

   SELECT AVG(salary) ag,department_id

   FROM employees

   GROUP BY department_id

) ag_dep

ON e.department_id = ag_dep.department_id

and salary > ag_dep.ag;



-- ceshi

select *

from employees

group by department_id;


select *

from employees;



第四天：

select *

from employees

where employee_id = 1;


-- 查询比103号年龄大，并且工资高的人的信息alter

-- 行子查询不可以使用大于、小于、大于等于、小于等于这种比较符号

select year, salary

from employees

where employee_id = 1;


-- 这种情况是不可以的

select *

from employees

where (year, salary) >

(select year, salary

from employees

where employee_id = 1);


-- 需要把两个条件分开

select *

from employees;


select *

from employees

where year > (select year from employeeswhere employee_id = 1)

and salary > (select salary fromemployees where employee_id = 1);


-- 行子查询可以使用等号

-- 查询与103号年龄和工资都相同的人的信息alter

select *

from employees

where (year, salary) =

(select year, salary

from employees

where employee_id = 1);


-- 作业--------------------------------------------------------alter

-- 查询部门名为 SAL 或 IT 的员工信息。

select *

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where d.department_name in ('SAL', 'IT');


-- 查询 90 号部门 job_id 和 location_id。

select *

from departments;


select e.department_id,e.job_id,d.location_id

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where e.department_id = 90;


-- 查询名称为“Mar”的部门所对应的城市名称、部门编号。

select *

from departments;


select *

from locations;


select l.city, d.department_id

from departments as d inner join locationsas l

on d.location_id = l.location_id

where d.department_name = 'Mar';


-- 查询比103号员工年薪高，年龄大的员工编号、姓名。

select *

from employees;


select employee_id, last_name

from employees

where year > (select year from employeeswhere employee_id = 103)

and salary > (select salary fromemployees where employee_id = 103);

-------------------------------------------------------


-- select后子查询


-- 利用部门表查询每个部门的员工个数、部门编号、部门名称

select d.department_id, d.department_name,

(select count(employee_id)

from employees

where d.department_id =employees.department_id)

from departments as d;



-- 作业----------------------------------------------------------alter


-- 查询每个职位的员工个数、职位编号、职位名称。

select *

from departments;


select *

from jobs;


select j.job_id, j.job_title,

(select count(job_id)

from employees

where j.job_id = employees.job_id)

from jobs as j;



-- 查询工资最高的员工的姓名，要求列名显示为“姓名”

select max(salary)

from employees;


select last_name as '姓名'

from employees

where salary = (select max(salary)

from employees);


-- 查询员工所对应的部门名称、职位全称（没有对应则不显示）。

-- 内连接

select e.last_name, d.department_name,j.job_title

from employees as e inner join departmentsas d inner join jobs as j

on e.department_id = d.department_id ande.job_id = j.job_id;



-- 查询位置表中每个位置的位置编号、部门数量、国家名。

select *

from locations;


select l.location_id, l.country_id,

(select count(department_id)

from departments as d

where d.location_id = l.location_id)

from locations as l;

----------------------------------------------------------------------


-- from后子查询


-- 每个部门的平均工资的工资等级

-- on既可以写两个表之间的相同列明，也可以写，一个表的某列，在另一个表的两列之间（此处是指的所有on）

-- 一般将分组查询的表格作为from后子查询的子表

-- select后子查询，两表之间的关联写在子句里面，from后子查询两表之间的关联写在外面



select *

from job_grades;


select avg(salary)

from employees

group by department_id;


select *

from (select avg(salary) as ag

from employees

group by department_id) as ag_emp innerjoin job_grades as j

on ag_emp.ag between j.lowest_sal andj.highest_sal;


-- 相关子查询

-- 相关子查询的子表写在where后面

-- 相关子查询的效率特别高，输出只有两个布尔值，ture和false

-- 查询部门表是否存在年龄大于21岁的员工，如果存在则输出该部门的信息。

select *

from departments

where exists (

select *

from employees

where employees.department_id =departments.department_id

and year > 21

);


-- 作业------------------------------------------------------------------alter


-- 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名。

select department_id

from employees

where last_name like '%u%';


select employee_id, last_name

from employees

where department_id in (selectdepartment_id

from employees

where last_name like '%u%');




-- 查询和 Hunold 相同部门的员工姓名和工资

select department_id

from employees

where last_name = 'Hunold';


select last_name, salary

from employees

where department_id = (select department_id

from employees

where last_name = 'Hunold');


-- 查询工资比公司平均工资高的员工的员工号，姓名和工资。

select avg(salary)

from employees;


select employee_id, last_name, salary

from employees

where salary > (select avg(salary)

from employees);


-- 查询平均工资最高的部门信息

select department_id, avg(salary)

from employees

group by department_id;


select max(aa),department_id

from (select department_id, avg(salary) asaa

from employees

group by department_id) as avg_sal;


select max(aa),department_id as dd

from (

select department_id, avg(salary) as aa

from employees

group by department_id) as avg_sal;


select d.*

from departments as d inner join (

select max(aa),department_id as dd

from (

select department_id, avg(salary) as aa

from employees

group by department_id) as avg_sal) bb

on d.department_id = bb.dd;


-- 老师的写法

select d.*

from departments as d, employees as e

where d.department_id = e.department_id

group by d.department_id

having avg(e.salary) =


(select max(emp_ag.ag)

from (select avg(salary) ag

from employees

group by department_id) as emp_ag);



-----------------------------------------------------------------------------


-- 练习题---------------------------------------------------------------------alter


-- 4.查询所有部门所对应的城市的位置alter

select department_id, department_name, city

from departments as d inner join locationsas l

on d.location_id = l.location_id;


-- 5.查询比105号员工的名字长度长并且工资高的员工信息alter

select length(last_name)

from employees

where employee_id = '105';


select salary

from employees

where employee_id = '105';


select *

from employees

where length(last_name) > (selectlength(last_name)

from employees

where employee_id = '105')

and salary > (select salary

from employees

where employee_id = '105');


-- 6.查询年龄大于20岁的员工部门alter

select department_id

from employees

where year > 20;


select last_name, e.department_id,department_name

from employees as e inner join departmentsas d

on e.department_id = d.department_id

where year > 20;


-- 7.查询员工表中前四条数据alter

select *

from employees

limit 0,4;


-- 8.查询员工名字里带a，并且年龄在25-40之间的人的信息

select *

from employees

where year between 25 and 40;


select *

from employees

where last_name like '%a%' and

year between 25 and 40;




-- 9.每个部门的平均工资的工资等级alter

select avg(salary) as ag

from employees

group by department_id;


select aa.department_id, ag, grade_level

from (select avg(salary) as ag,department_id

from employees

group by department_id) as aa inner joinjob_grades as j

on aa.ag between j.lowest_sal andj.highest_sal;


-- 10.查询城市名称是Tokyo的部门编号

select *

from departments;


select *

from locations;


select *

from locations

where city =  'Tokyo';


select department_id

from departments as d inner join locationsas l

on d.location_id = l.location_id

where l.city = 'Tokyo';


-- 11.查询部门表是否存在年龄大于30岁的员工，如果存在则输出该部门的信息alter

select *

from departments

where exists (

select *

from employees

where employees.department_id =departments.department_id

and year > 30

);



-- 12.查询各部门工资总和以及部门编号alter

select department_id, count(salary)

from employees

group by department_id;


-- 13.查询具有各个职位的员工人数alter

select job_id, count(employee_id)

from employees

group by job_id;


-- 14.查询各职位的员工工资的最大值、最小值、平均值、总和alter

select job_id, max(salary), min(salary),avg(salary), sum(salary)

from employees

group by job_id;


-- 15.查询各部门平均工资小于5000的部门编号alter

select avg(salary)

from employees

group by department_id;


select *

from employees

group by department_id

having salary < (select avg(salary)

from employees

group by department_id);


select avg(aa.ss),department_id

from (select department_id, avg(salary) asss

from employees

group by department_id) as aa

group by department_id

having avg(aa.ss) < 5000;


select department_id, avg(salary) as ss

from employees

group by department_id;


select department_id

from employees

group by department_id

having avg(salary) < 5000;


-- 16.查询员工表中从第4条开始，到第7条的数据alter

select *

from employees;


select *

from employees

limit 3,4;


-- 17.查询各部门编号，员工数量和工资平均值alter

select department_id, count(employee_id),avg(salary)

from employees

group by department_id;


select d.department_id,count(e.employee_id), avg(e.salary)

from employees as e right join departmentsas d

on e.department_id = d.department_id

group by department_id;


-- 18.查询所有部门所对应的员工的部门的名称、部门号和员工信息alter

select d.department_name, d.department_id,e.*

from departments as d left join employeesas e

on d.department_id = e.department_id;


-- 19.查询各部门员工工资小于5000的人的平均工资

select *

from employees

where salary < 5000;


select aa.dd, avg(aa.ss)

from (select department_id as dd, salary asss

from employees

where salary < 5000) as aa

group by aa.dd;


select avg(salary)

from employees

where salary < 5000

group by department_id;


-- 20.查询所有部门的编号、员工数量、和平均工资alter

select d.department_id,count(e.employee_id), avg(e.salary)

from employees as e right join departmentsas d

on e.department_id = d.department_id

group by department_id;


selectavg(salary),department_id,count(employee_id)

from employees

group by department_id;


-- 21.查询员工编号最小，并且工资最高的员工信息alter

select min(employee_id)

from employees;


select max(salary)

from employees;


select *

from employees

where employee_id = (

select min(employee_id)

from employees)

and salary = (select max(salary)

from employees);


-- 22.查询各部门中年龄大于30岁的人的平均工资alter

select department_id, avg(salary)

from employees

group by department_id

having year > 30;


-- 23.求出各部门中平均工资大于7000的部门以及其平均工资

select department_id, avg(salary) as ss

from employees

group by department_id;


select avg(aa.ss),department_id

from (select department_id, avg(salary) asss

from employees

group by department_id) as aa

group by department_id

having avg(aa.ss) > 7000;


select department_id,avg(salary)

from employees

group by department_id

having avg(if(salary is null, 0, salary))> 7000;



-----------------------------------------------------------


-- 分页：limit一般写在所有的关键字的最后面

-- 一个参数limit，从头开始取几条数据

select *

from employees

limit 5;


-- 两个参数.索引从第几位开始，第一位的索引为0，取几条数据

select *

from employees

limit 0,5;


/*一般来说，关键字的顺序

select

from

where

group by

having

order by

limit

*/


/*

sql的执行顺序：其它按顺序执行完之后，最后执行select

*/


select *

from employees;


select avg(last_name)

from employees;


第五天：

-- 事务

-- 插入语句

select *

from boss;


-- 第一种写法

insert into boss

(id, name, boss_year) values (99,'大吉哥', 22);


-- 第二种写法

insert into boss

set id = 12, name = '张老板', boss_year = 35;


-- 前两种写法不用每个字段都插入值，第三种写法必须全部写，如果id列为自增的话，那么最好写default，而不是死值

-- 第三种写法alter

insert into boss

values (22, '小兰',31);


insert into boss

values (default, '小兰', 31);


-- 插入多条数据alter

insert into boss

(name, boss_year) values ('小利', 80), ('小郭', 75), ('小王', 88);


-- 删除数据，一般只能写id等唯一标识字段

delete from boss

where id = 123;


-- 更新数据

-- 删除和更新都必须写限制条件where

update boss

set name = '大吉哥'

where id = 100;


-- 事务的commit和rollback

-- 开启事务alter

select *

from employees;


begin;

update employees

set salary = 0

where employee_id = 103;


update employees

set salary = salary + 9000

where employee_id = 104;


rollback;


-- 事务的隔离级别


-- 1.读未提交————可能读到垃圾数据：事务A未提交的数据，事务B可以读取到

-- 这里读取到的数据叫作“脏数据”

-- 脏读是指一个事务处理过程里读取了另一个未提交的事务中的数据alter


-- 2.读已提交：事务A提交的数据，事务B才能读取到

-- 这种隔离级别高于读未提交alter

-- 换句话说，对方事务提交之后的数据，当前事务才能读取到alter

-- 这种级别可以避免脏数据alter

-- 这种隔离级别会导致“不可重复读取”：意思就是，数据变了之后，读取也会读取变了的数据

-- Oracle默认隔离级别


-- 3.可重复读

-- 事务A提交之后的数据，事务B读取不到alter

-- 事务B是可重复读取数据alter

-- 换句话说，对方提交之后的数据，我还是读取不到alter

-- MySQL默认级别alter

-- 虽然可以达到可重复读取，但是会导致“幻读”

-- 幻读：事务A把表中的全部信息更新掉，这个过程中事务B插入一条信息，事务A查的时候发现了一个多的没有改掉，以为自己产生了幻觉


-- 4.串行化：事务A在操作数据库时，事务B只能排队等待alter

-- 这种隔离级别很少使用，吞吐量太低，用户体验差alter

-- 这种级别可以避免“幻读”，每一次读取的数据库中真实存在的数据，事务A和事务B串行而不是并发


-- inner join两表连接是没有对应就不显示吗？是的

select *

from employees;


select *

from departments;


select *

from employees as e inner join departmentsas d

on e.department_id = d.department_id;


-- 事务的属性alter

-- 1.原子性alter

-- 原子性的事务是一个不可分割的工作单位，单位中的操作要么都发生，要么都不发生alter


-- 2.一致性alter

-- 事务必须使数据库从一个一致性状态变换到另外一个一致性状态alter


-- 3.隔离性alter

--


-- 视图：存在的是sql的逻辑。合作的时候，对访问进行封装，不直接开放整个表。

-- 创建视图

create view my_view1

as

select * from employees;


select *

from my_view1;


create view my_view2

as

select * from boss;


select *

from my_view2;


/*

select *

from my_view1;


insert into

my_view1 (last_name)

values ('小明');

*/


select *

from my_view2;


insert into

my_view2 (name)

values ('小明');


-- 作业------------------------------------------------------------alter


-- 将员工表中工资比较高的5个人显示出来alter

create view my_view3

as

select *

from employees

order by salary desc

limit 5;


select*

from my_view3;


-- 将员工表中工资最高的5个人按照编号降序查询

select *

from employees

order by salary desc

limit 5;



select aa.*

from (select *

from employees

order by salary desc

limit 5) as aa

order by aa.employee_id desc;


create view my_view4

as

select aa.*

from (select *

from employees

order by salary desc

limit 5) as aa

order by aa.employee_id desc;


select *

from my_view4;

-------------------------------------------------------------------------



-- 级联视图alter

-- my_view5父视图

create view my_view5

as

select *

from employees

where salary > 10;


select *

from my_view5;


-- my_view6子视图

create view my_view6

as

select *

from my_view5

where salary > 20000;


select *

from my_view6;


-- 本地检查：local检查只考虑自己本身的限制条件，不考虑父视图的限制条件

-- 视图本身也可以插入数据，并且会检查自己范围是否符合要求

create view my_viewF

as

select *

from employees

where salary > 10;


insert into my_viewf (last_name, salary)

values ('珠兰', 15);


select *

from my_viewF;



create view my_viewS

as

select *

from my_viewF

where salary < 20

with local check option;


insert into my_viewS (last_name, salary)

values ('杨金祥', 5);


select *

from my_viewS;


-- 级联检查：不仅考虑本视图的限制条件，而且考虑父视图的限制条件


create view my_views2

as

select *

from my_viewf

where salary < 20

with cascaded check option;


insert into my_viewS (last_name, salary)values ('fdasf', 25);


select *

from my_views2;


-- 更新视图：如果里面有多表连接或者子查询等关键字均不可更新视图

update my_views2

set last_name = '小李'

where employee_id = 103;


-- 删除视图

drop view my_view2, my_view3, my_view4;

drop view my_view5, my_view6, my_viewf,my_views, my_views2;


-- 查看视图

create view my_view1

as

select *

from boss;

--有哪些视图

show tables;


select *

from my_view1;


--sql（查询语言）

-- 查看视图的定义

show create view my_view1;


第六天：

-- 约束alter

-- 限制一列或者几列的数据不能插入null值


-- 非空约束（NN）

select *

from boss;


insert into boss

(name, boss_year) values ('李老板', 23);


insert into boss

(name) values ('刘老板');


insert into boss

(name, boss_year, bosscol) values ('非老板', 23, 23);


-- 唯一约束（UQ）


-- 主键约束：限制一列数据——非空约束 + 唯一约束——相当于一行的唯一标识（）

-- 数据库内的每张表必须要有主键（主键可以是一个字段，也可以是多个字段）

-- 一张表内主键有多个字段，这样的主键叫作复合主键





-- 默认约束（default/Expresssion），一般来说字符型默认null，整数默认0，小数默认0.0；

delete from boss where name = '小兰';

select *

from newboss;


insert into newboss values (11, 22);



insert into newboss

(boss) values (11);


-- 外键约束：限制两个表的数据，从表的外键来自于主表关联列的值，

-- 并且不能直接删除主表里的值，必须先删除对应从表里的数据

-- 级联删除：直接删除主表中的值，那么对应从表中的值也就跟着没了


select *

from classes;


select *

from student;


--

delete from classes where id = 2;

delete from student where class_id = 3;


delete from classes where id = 1;



-- 列级约束：限制单列的约束就是列级约束

-- 表级约束：限制多个列的约束就是表级约束


-- DDL（数据定义语言）

-- create database test;

-- SQL（数据查询语言）

-- DML（数据操纵语言）


-- 创建数据库


create database test;


-- 查看有几个数据库

show databases;use test;


-- 在mysql里，schema和database意义相同，作用相同

create schema test2;

show schemas;


-- 使用数据库

use test;


-- 创建一个表alter

create table ddl_table(

id int primary key auto_increment,

name varchar(20) not null

);


-- 查看当前数据库有哪些表alter

show tables;


select *

from ddl_table;


-- 删除数据库中的表

-- 1.ddl方式：删除表中所有的数据，只删除数据，不删除表的结构

truncate ddl_table;

show tables;

-- 2.删除此表，删除表的结构

drop table ddl_table;

-- 3.dml方式：删除数据，不删除表的结构

delete from ddl_table;


-- 3种方式的共同点：都可以删除数据：

-- 不同点：①delete是dml语言，可以回滚。truncate和、drop是ddl语言，不能回滚。

-- ②delete和truncate只删除数据，不删除表的结构。而drop是既删除数据又删除表结构。

-- delete不加where，即便不选安全模式，也不能删除整张表。


insert into ddl_table(name) values ('小明'),('小红');


select * from ddl_table;


begin;

drop table ddl_table;

rollback;


select *

from ddl_table;


-- 修改表的属性

-- 加字段

alter table ddl_table

add year int;


select *

from ddl_table;


-- 添加多个字段

alter table ddl_table

add(

       classs_idint,

   card_id int

);


-- 修改表的字段名称和类型

-- modify可以修改类型

alter table ddl_table

modify name varchar(30);

-- modify既可以修改字段类型，也可以修改字段名称

alter table ddl_table

change name test_name varchar(30);


-- 删除列

-- 删除一列（好像只删除数据，不能删除列结构）

alter table ddl_table

drop column card_id;


alter table ddl_table

drop column class_id;

-- 删除多列alter

alter table ddl_table

drop column id,

drop column test_name;


select *

from ddl_table;


-- DDL: creat，truncate，drop


-- 存储过程

-- 变量有系统变量和自定义变量之分。系统变量有全局变量和会话变量；自定义变量有用户变量和局部变量。

-- 会话就是一个连接

-- 全局变量用于整个服务，

-- 会话变量用于当前会话

-- 自定义变量也只在当前会话有效，关了会话之后就没了




-- 查看数据库的全局变量

show global variables;

-- 查看数据库的会话变量

show session variables;

-- 会话变量和全局变量是一样的，一旦关闭workbench就没了。

-- 全局变量更改之后需要重启数据库的服务才可以恢复。但工作的时候不可能重启数据库



-- 查看数据库的字符集全局变量，用模糊查询

show global variables like '%char%';

-- 查看数据库的事务隔离级别

show global variables like '%iso%';


-- 查看某个全局变量的值

select @@global.transaction_isolation;

select @@session.transaction_isolation;



-- 给一个全局变量赋值

set @@global.transaction_isolation ='READ-COMMITTED';

set @@session.transaction_isolation ='READ-COMMITTED';



-- 定义一个自定义变量：作用于当前会话

-- 赋值与声明

set @test_param := '小明';

-- 调用alter

select @test_param;


-- 自定义两个变量，然后求它们的和

set @sum_param := 1;

set @sum2 := 2;

select @sum_param + @sum2;


-- 存储过程：事先经过编译好的sql语句的结合

-- 好处：①提高代码重用性；②简化操作；③减少编译次数。


-- 创建存储过程

-- 可以在左侧的Stored

Procedures查看所有的存储过程

-- 参数模式有三种类型：IN OUTINOUT

-- IN：输入模式

-- OUT：输出模式


delimiter $$

create procedure my_por1( IN in_param  varchar(30))

-- 存储过程体

begin

-- 查询员工表中id为103号员工的信息

select *

from employees

where employee_id = in_param;

end $$

delimiter ;


-- 调用存储过程

call my_por1(103);


select *

from employees;


-- 输出模式

delimiter $$

create procedure my_por2( IN in_param  varchar(30),

                                            OUT out_param varchar(30))

-- 存储过程体

begin

-- 查询员工表中id为103号员工的信息

select last_name into out_param

from employees

where employee_id = in_param;

end $$

delimiter ;



set @param := null;

call my_por2(103, @param);


select @param;



-- 作业----------------------------------------------------------

-- 用存储过程显示名字为“De

Haan”的人的信息

delimiter $$

create procedure my_por3( IN in_param  varchar(30))

-- 存储过程体

begin

select *

from employees

where last_name = in_param;

end $$

delimiter ;


call my_por3('De Haan');


select *

from employees;


-- 用存储过程实现根据名字“Hunold”的员工，返回该员工所对应的部门名称


delimiter $$

create procedure my_por4( IN in_param  varchar(30),

                                            OUT out_param varchar(30))


begin

select d.department_name into out_param

from employees as e inner join departmentsas d

on e.department_id = d.department_id

and e.last_name = in_param;

end $$

delimiter ;


set @my_out1 := null;

call my_por4('Hunold', @my_out1);


select @my_out1;




select *

from employees;


select *

from departments;



-- 如果要返回一行多列的数据

delimiter $$

create procedure my_por5( IN in_param  varchar(30),

                                            OUT out_param varchar(30),

                         OUT out_param1varchar(30),

                          OUT out_param2varchar(30))


begin

select d.department_name, d.department_id,d.location_id   into out_param,out_param1, out_param2

from employees as e inner join departmentsas d

on e.department_id = d.department_id

and e.last_name = in_param;

end $$

delimiter ;


set @my_out1 := null;

set @my_out2 := null;

set @my_out3 := null;

call my_por5('Hunold', @my_out1, @my_out2,@my_out3);


select @my_out1, @my_out2,@my_out3;



select *

from employees;


select *

from departments;


-- 用存储过程实现根据名字“Hunold”的员工，返回该员工所对应的工资等级

delimiter $$

create procedure my_por6( IN in_param  varchar(30),

                                          OUTout_param varchar(30))


begin

select j.grade_level into out_param

from employees as e inner join job_gradesas j

on e.salary between j.lowest_sal andj.highest_sal

and e.last_name = in_param;

end $$

delimiter ;


set @my_out4 := null;

call my_por6('Kochhar', @my_out4);


select @my_out4;


select j.grade_level

from employees as e inner join job_grades asj

on e.salary between j.lowest_sal andj.highest_sal

where e.last_name = 'Kochhar';


select *

from employees;


select *

from job_grades;


------------------------------------------------------------------------


第七天：

-- 传入一个参数，如果大于3则返回100，否则返回500

-- 用户变量作用于当前会话，局部变量作用于begin和end之间

-- INOUT的使用

delimiter $$

create procedure my_pro8(INOUT in_paramvarchar(20))

begin

       declareparam varchar(20);

   if in_param > 3

   then set param := 100;

   else set param := 500;

   end if;


   set in_param := param;


end $$

delimiter ;


set @param = 3;

call my_pro8(@param);


select @param;


-- PLSQL while

-- PLSQL：过程语言，在存储过程中使用的语言，也就是和Java一样有循环、分支这些语句，但是很少用到


delimiter $$

create procedure my_pro11_while()

begin

       declarei int;

   set i := 0;


   while i < 10 do


              insertinto boss (name) values (i);

       set i = i + 1;

       endwhile;



end $$

delimiter ;


call my_pro11_while();


select *

from boss;


-- 游标：遍历表中的每一行，并且把每一行查到的数据都用一个单独的表显示出来


DELIMITER $$

create PROCEDURE my_pro12()

begin

-- 声明一个标志done，用来判断游标是否遍历完

DECLARE done INT DEFAULT 0;

-- 声明一个变量，用来存放从游标中提取的数据

-- 特别注意这里的名字不能与由游标中使用的列明相同，

-- 否则得到的数据都是NULL

DECLARE lastName varchar(30) DEFAULT NULL;

DECLARE salaryParam int DEFAULT NULL;

-- 声明游标对应的 SQL 语句

DECLARE cur CURSOR FOR

select last_name, salary from employees;

-- 在游标循环到最后会将 done 设置为1

DECLARE CONTINUE HANDLER FOR NOT FOUND SETdone = 1;

-- 执行查询

open cur;

-- 遍历游标每一行

REPEAT

-- 把一行的信息存放在对应的变量中

FETCH cur INTO lastName, salaryParam;

if not done then

-- 这里就可以使用 lastName， salaryParam 对应的信息了

select lastName, salaryParam;

end if;

UNTIL done END REPEAT;

CLOSE cur;

end $$

DELIMITER ;


call my_pro12();


-- JDBC（Java数据库连接）

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 200,045评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,114评论 2赞 377
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 147,120评论 0赞 332
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,902评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,828评论 5赞 360
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,132评论 1赞 277
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,590评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,258评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,408评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,335评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,385评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,068评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,660评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,747评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,967评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,406评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,970评论 2赞 341