• MySQL之CRUD

    目录

    一、基础查询

    二、过滤和排序数据

    三、Order by子句

    四、排序案例

    五、拓展案例

    一、基础查询

    1、语法：select  查询列表  from 表名

    2、特点：

    2.1、查询列表可以是：表中的字段、常量值、表达式、函数

    2.2、查询的结果是一个虚拟的表格

    3、实例：

    1. 3.1.查询表中的单个字段
    2. ​
    3. SELECT last_name FROM t_mysql_employees;
    4. ​
    5. 3.2.查询表中的多个字段
    6. SELECT last_name,salary,email FROM t_mysql_employees;
    7. ​
    8. 3.3.查询表中的所有字段
    9. ​
    10. 方式一：
    11. SELECT
    12.   `employee_id`,
    13.   `first_name`,
    14.   `last_name`,
    15.   `phone_number`,
    16.   `last_name`,
    17.   `job_id`,
    18.   `phone_number`,
    19.   `job_id`,
    20.   `salary`,
    21.   `commission_pct`,
    22.   `manager_id`,
    23.   `department_id`,
    24.   `hiredate`
    25. FROM
    26.   t_mysql_employees ;
    27. 方式二：  
    28. SELECT * FROM t_mysql_employees;
    29.  
    30. 3.4.查询常量值
    31. SELECT 100;
    32. SELECT 'john';
    33.  
    34. 3.5.查询表达式
    35. SELECT 100%98;
    36.  
    37. 3.6.查询函数
    38.  
    39. SELECT VERSION();

    3.7：起别名：

    目的：①便于理解
               ②如果要查询的字段有重名的情况，使用别名可以区分开来

    3.8：去重：把表格中重复的字段去掉

    SELECT DISTINCT department_id FROM t_mysql_employees;

    3.9：+号的作用:说起加号，就要和JAVA中的加号进行对比了。
                java中的+号：
                        ①运算符，两个操作数都为数值型
                        ②连接符，只要有一个操作数为字符串
               mysql中的+号：仅仅只有一个功能：运算符。

    3.9.1+号的几种情况：

        1、两个操作数都为数值型，则做加法运算

    2、只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算

    3、如果转换失败，则将字符型数值转换成0 

     4、只要其中一方为null，则结果肯定为null

    二、过滤和排序数据

    1、过滤
    2、在查询中过滤行
    3、where子句
    4、比较运算
    5、between
    6、in
    7、like
    8、null
    9、逻辑运算

    1. 语法：
    2. select
    3. 查询列表
    4. from
    5. 表名
    6. where
    7. 筛选条件;
    8. ​
    9. 分类：
    10. 一、按条件表达式筛选
    11.  
    12. 简单条件运算符：> < = != <> >= <=
    13.  
    14. 二、按逻辑表达式筛选
    15. 逻辑运算符：
    16. 作用：用于连接条件表达式
    17. && || !
    18. and or not
    19.  
    20. &&和and：两个条件都为true，结果为true，反之为false
    21. ||或or： 只要有一个条件为true，结果为true，反之为false
    22. !或not： 如果连接的条件本身为false，结果为true，反之为false
    23.  
    24. 三、模糊查询
    25. like
    26. between and
    27. in
    28. is null
    29.  
    30. */
    31. #一、按条件表达式筛选
    32. ​
    33. #案例1：查询工资>12000的员工信息
    34. ​
    35. SELECT
    36. *
    37. FROM
    38. t_mysql_employees
    39. WHERE
    40. salary>12000;
    41.  
    42.  
    43. #案例2：查询部门编号不等于90号的员工名和部门编号
    44. SELECT
    45. last_name,
    46. department_id
    47. FROM
    48. t_mysql_employees
    49. WHERE
    50. department_id<>90;
    51. ​
    52. ​
    53. #二、按逻辑表达式筛选
    54. ​
    55. #案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
    56. SELECT
    57. last_name,
    58. salary,
    59. commission_pct
    60. FROM
    61. t_mysql_employees
    62. WHERE
    63. salary>=10000 AND salary<=20000;
    64. #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
    65. SELECT
    66. *
    67. FROM
    68. t_mysql_employees
    69. WHERE
    70. NOT(department_id>=90 AND department_id<=110) OR salary>15000;
    71. #三、模糊查询
    72. /*
    73. like
    74. ​
    77. between and
    78. in
    79. is null|is not null
    80. ​
    81. */
    82. #1.like
    83. /*
    84. 特点：
    85. ①一般和通配符搭配使用
    86. 通配符：
    87. % 任意多个字符,包含0个字符
    88. _ 任意单个字符
    89. *、
    90. ​
    91. #案例1：查询员工名中包含字符a的员工信息
    92. ​
    93. select
    94. *
    95. from
    96. employees
    97. where
    98. last_name like '%a%';#abc
    99. #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
    100. select
    101. last_name,
    102. salary
    103. FROM
    104. t_mysql_employees
    105. WHERE
    106. last_name LIKE '__n_l%';
    107. ​
    108. ​
    109. ​
    110. 案例3：查询员工名中第二个字符为_的员工名
    111. ​
    112. SELECT
    113. last_name
    114. FROM
    115. t_mysql_employees
    116. WHERE
    117. last_name LIKE '_$_%' ESCAPE '$';
    118. 2.between and
    119. /*
    120. ①使用between and 可以提高语句的简洁度
    121. ②包含临界值
    122. ③两个临界值不要调换顺序
    123. ​
    124. */
    125. ​
    126. ​
    127. #案例1：查询员工编号在100到120之间的员工信息
    128. ​
    129. SELECT
    130. *
    131. FROM
    132. t_mysql_employees
    133. WHERE
    134. employee_id <= 120 AND employee_id>=100;
    135. #----------------------
    136. SELECT
    137. *
    138. FROM
    139. t_mysql_employees
    140. WHERE
    141. employee_id BETWEEN 100 AND 120;
    142. ​
    143. ​
    144. #3.in
    145. /*
    146. 含义：判断某字段的值是否属于in列表中的某一项
    147. 特点：
    148. ①使用in提高语句简洁度
    149. ②in列表的值类型必须一致或兼容
    150. ③in列表中不支持通配符
    152. ​
    153. */
    154. #案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    155. ​
    156. SELECT
    157. last_name,
    158. job_id
    159. FROM
    160. t_mysql_employees
    161. WHERE
    162. job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
    163. ​
    164. ​
    165. #------------------
    166. ​
    167. SELECT
    168. last_name,
    169. job_id
    170. FROM
    171. t_mysql_employees
    172. WHERE
    173. job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
    174. ​
    175. #4、is null
    176. /*
    177. =或<>不能用于判断null值
    178. is null或is not null 可以判断null值
    179. ​
    180. ​
    181. ​
    182. ​
    183. */
    184. ​
    185. #案例1：查询没有奖金的员工名和奖金率
    186. SELECT
    187. last_name,
    188. commission_pct
    189. FROM
    190. t_mysql_employees
    191. WHERE
    192. commission_pct IS NULL;
    193. ​
    194. ​
    195. #案例1：查询有奖金的员工名和奖金率
    196. SELECT
    197. last_name,
    198. commission_pct
    199. FROM
    200. t_mysql_employees
    201. WHERE
    202. commission_pct IS NOT NULL;
    203. ​
    204. #----------以下为×
    205. SELECT
    206. last_name,
    207. commission_pct
    208. FROM
    209. t_mysql_employees
    210. ​
    211. WHERE
    212. salary IS 12000;
    213.  
    214.  
    215. #安全等于 <=>
    216. ​
    217. ​
    218. #案例1：查询没有奖金的员工名和奖金率
    219. SELECT
    220. last_name,
    221. commission_pct
    222. FROM
    223. t_mysql_employees
    224. WHERE
    225. commission_pct <=>NULL;
    226.  
    227.  
    228. #案例2：查询工资为12000的员工信息
    229. SELECT
    230. last_name,
    231. salary
    232. FROM
    233. t_mysql_employees
    234. ​
    235. WHERE
    236. salary <=> 12000;
    237.  
    238. ​
    239. #is null pk <=>
    240. ​
    241. IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
    242. <=>   :既可以判断NULL值，又可以判断普通的数值，可读性较低

    三、Order by子句

    1. ​1、按单个字段排序
    2. 案例：按员工表薪资排序
    3. ​SELECT * FROM t_mysql_employees ORDER BY salary DESC;
    4. 2、添加筛选条件再排序
    5. 案例：查询部门编号>=90的员工信息，并按员工编号降序
    6. ​SELECT * FROM t_mysql_employees WHERE department_id>=90 ORDER BY employee_id DESC;
    7. 3、按表达式排序
    8. 案例：查询员工信息 按年薪降序
    9. ​​SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM t_mysql_employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    10. 4、按别名排序
    11. 案例：查询员工信息 按年薪升序
    12. ​​​​SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM t_mysql_employees
    13. 5、按函数排序
    14. 案例：查询员工名，并且按名字的长度降序
    15. SELECT LENGTH(last_name),last_name FROM t_mysql_employees ORDER BY LENGTH(last_name) DESC;
    16. 6、按多个字段排序
    17. ​案例：查询员工信息，要求先按工资降序，再按employee_id升序
    18. SELECT * FROM t_mysql_employees ORDER BY salary DESC,employee_id ASC;

    四、排序案例

    1. 1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
    2. select last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) from t_mysql_employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc,last_name asc
    3. 2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
    4. SELECT last_name,salary from t_mysql_employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc,salary not in(8000,17000)
    5. 3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
    6. SELECT * from t_mysql_employees where email like '%e%' ORDER BY LENGTH(email) desc ,department_id asc

    五、拓展案例

    1、首先建立四个表并且分别插入数据

    1. -- 1.学生表-t_student
    2. -- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
    3. CREATE TABLE t_student(
    4. 	sid VARCHAR(20),
    5. 	sname VARCHAR(20),
    6. 	sage date,
    7. 	ssex VARCHAR(20)
    8. )
    9.  
    10. -- 2.教师表-t_teacher
    11. -- tid 教师编号,tname 教师名称
    12. create table t_teacher(
    13. 	tid VARCHAR(20),
    14. 	tname VARCHAR(20)
    15. )
    16.  
    17. -- 3.课程表-t_course
    18. -- cid 课程编号,cname 课程名称,tid 教师名称
    19. create table t_course(
    20. 	cid VARCHAR(20),
    21. 	cname VARCHAR(20),
    22. 	tid VARCHAR(20)
    23. )
    24.  
    25. -- 4.成绩表-t_score
    26. -- sid 学生编号,cid 课程编号,score 成绩
    27. CREATE table t_score(
    28. 	sid VARCHAR(20),
    29. 	cid VARCHAR(20),
    30. 	score INT
    31. )
    32.  
    33.  
    34.  
    35.  
    36.  
    37. -- 学生表
    38. insert into t_student values('01' , '赵雷' , '1990-01-01' , '男');
    39. insert into t_student values('02' , '钱电' , '1990-12-21' , '男');
    40. insert into t_student values('03' , '孙风' , '1990-12-20' , '男');
    41. insert into t_student values('04' , '李云' , '1990-12-06' , '男');
    42. insert into t_student values('05' , '周梅' , '1991-12-01' , '女');
    43. insert into t_student values('06' , '吴兰' , '1992-01-01' , '女');
    44. insert into t_student values('07' , '郑竹' , '1989-01-01' , '女');
    45. insert into t_student values('09' , '张三' , '2017-12-20' , '女');
    46. insert into t_student values('10' , '李四' , '2017-12-25' , '女');
    47. insert into t_student values('11' , '李四' , '2012-06-06' , '女');
    48. insert into t_student values('12' , '赵六' , '2013-06-13' , '女');
    49. insert into t_student values('13' , '孙七' , '2014-06-01' , '女');
    50.  
    51. -- 教师表
    52. insert into t_teacher values('01' , '张三');
    53. insert into t_teacher values('02' , '李四');
    54. insert into t_teacher values('03' , '王五');
    55.  
    56. -- 成绩表
    57. insert into t_score values('01' , '01' , 80);
    58. insert into t_score values('01' , '02' , 90);
    59. insert into t_score values('01' , '03' , 99);
    60. insert into t_score values('02' , '01' , 70);
    61. insert into t_score values('02' , '02' , 60);
    62. insert into t_score values('02' , '03' , 80);
    63. insert into t_score values('03' , '01' , 80);
    64. insert into t_score values('03' , '02' , 80);
    65. insert into t_score values('03' , '03' , 80);
    66. insert into t_score values('04' , '01' , 50);
    67. insert into t_score values('04' , '02' , 30);
    68. insert into t_score values('04' , '03' , 20);
    69. insert into t_score values('05' , '01' , 76);
    70. insert into t_score values('05' , '02' , 87);
    71. insert into t_score values('06' , '01' , 31);
    72. insert into t_score values('06' , '03' , 34);
    73. insert into t_score values('07' , '02' , 89);
    74. insert into t_score values('07' , '03' , 98);
    75.  
    76.  
    77.  
    78. -- 课程表
    79. insert into t_course values('01' , '语文' , '02');
    80. insert into t_course values('02' , '数学' , '01');
    81. insert into t_course values('03' , '英语' , '03');
    82.  
    83. select * from t_course
    84.  
    85. select * from t_score 
    86.  
    87. select * from t_teacher
    88.  
    89. select * from t_student

    2、案例（大部分都是使用内连接的）

    01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

    1. SELECT a.*,b.score 01score,c.score 02score
    2. FROM t_student as a
    3. INNER JOIN t_score as b
    4. ON a.sid = b.sid
    5. INNER JOIN t_score as c
    6. ON a.sid = c.sid and b.cid = '01' and c.cid = '02'
    7. where b.score > c.score;

    解题思路：

    1、首先将成绩表分为‘01’和‘02’两部分,'01'数据视为b表，'02'数据视为c表

    通过cid将两个表连接起来，然后再筛选出" 01 "课程比" 02 "课程成绩高的

    2、将学生表t_student看做为a表，分别用sid将a表与b表和c表相关联，并且查询出

    学生的信息及课程分数

    02）查询同时存在" 01 "课程和" 02 "课程的情况

    1. SELECT * FROM 
    2. (SELECT * FROM t_score WHERE cId = '01') AS a
    3. INNER JOIN (SELECT * FROM t_score WHERE cId = '02') AS b
    4. ON a.sId = b.sId;

    解题思路：首先将查询条件结果为cid=‘01’看做a表，再将查询结果为cid=‘02’的看做b表，并且用sid将这连个表连接起来

    03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

    1. SELECT * from 
    2. (SELECT * from t_score where cid ='01') as a
    3. LEFT JOIN t_score AS b
    4. ON a.sId = b.sId AND b.cId = '02';

    解题思路：首先将查询结果为cid='01'的看做是a表，再将成绩表看作是b表，用sid将这两个表连接起来，同时添加一个b表中cid=‘02’的条件，代表着可能的意思

    04）查询不存在" 01 "课程但存在" 02 "课程的情况

    1. select * from t_score where sid not in (
    2. SELECT sid FROM t_score WHERE cId = '01'
    3. )and cid = '02'

    解题思路：

    查询条件1：先查询出cid为'01'的sid，同时赋予条件cid=‘02’

    查询条件2:其次再查询所有不为查询条件1的学生信息

    05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

    1. SELECT a.sid,a.sname,b.c from
    2. t_student as a
    3. INNER JOIN (SELECT sid,AVG(score) AS c
    4.             FROM t_score
    5.             GROUP BY sid
    6.             HAVING AVG(score) >= 60) AS b
    7. ON a.sid = b.sid;

    解题思路：1、首先将学生编号进行分组，再过滤出平均数大于60的学生编号

    同时将平均分取名为c，整个查询条件取名为b表

                           2、将学生表看做是a表，用sid将a，b表连接，在查询学生编号和学生姓名和平均成绩

      06）查询在t_score表存在成绩的学生信息 

    1. SELECT b.* from 
    2. (SELECT sid from t_score GROUP BY sid) a
    3. LEFT JOIN t_student b
    4. on a.sid=b.sid

      解题思路:先将存在分数的学生编号在成绩表里进行分组，然后再将这部分学生编号与学生表里的学生编号进行比较

    07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null )

    1. SELECT a.sid,a.sname,b.zs,b.zcj
    2. FROM t_student AS a
    3. LEFT JOIN (SELECT sid,COUNT(cid) AS zs,SUM(score) AS zcj
    4.            FROM t_score
    5.            GROUP BY sid) AS b
    6. ON a.sid = b.sid;

     解题思路：先将总数与总成绩拿出来，将学生编号进行分组，然后将a表与b表用sid相连接，在查询条件就可以了

    08）查询「李」姓老师的数量
     

    select count(*) from t_teacher where tname like '李%'

    解题思路：用like '李%'进行模糊查询出姓李的老师，然后再用Count(*)统计数量

    09）查询学过「张三」老师授课的同学的信息

    1. select * from t_student where sid in (
    2. select sid from t_score where cid =(
    4. select cid from t_course where tid =(
    6. select tid from t_teacher where  tname ='张三'
    7. )
    8. )
    9. )

    解题思路：1、先查询出老师姓名为张三的老师编号

                      2、再查询出张三老师的课程编号

                       3、再通过该编号拿到对应课程的学生编号

                       4、再通过学生编号拿到所有的学生信息

    10）查询没有学全所有课程的同学的信息
     

    1. SELECT a.*,kc
    2. FROM t_student AS a
    3. INNER JOIN (SELECT sid,COUNT(cid) AS kc
    4.             FROM t_score
    5.             GROUP BY sid
    6.             HAVING kc < (SELECT COUNT(cid) FROM t_course)) AS b
    7. ON a.sid = b.sid;

     解题思路：1、先拿到学生的选修情况取别名为kc然后将学生编号进行分组

                      2、再过滤筛选出没有学全的学生编号看做为b表

                   3、将学生表看做a表用sid与b表相连接

    11）查询没学过"张三"老师讲授的任一门课程的学生姓名

    1. SELECT sname
    2. FROM t_student AS a
    3. WHERE sid NOT IN (SELECT sid
    4.                   FROM t_score AS a
    5.                   LEFT JOIN t_course AS b
    6.                   ON a.cid = b.cid
    7.                   INNER JOIN t_teacher AS c
    8.                   ON b.tid = c.tid
    9.                   WHERE tname = '张三');

    解题思路：

                      1、先查询出老师姓名为张三的老师编号

                      2、再查询出张三老师的课程编号

                       3、再通过该编号拿到对应课程的学生编号

                       4、在加条件not in 就是不存在的意思来拿到不在该条件下的学生编号，通过该编号就能拿到没有选修张三老师课程的学生姓名

    12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

    1. SELECT c.sid,d.sname,pjf
    2. FROM(SELECT a.sid,AVG(score) AS pjf
    3.      FROM t_score AS a
    4.      INNER JOIN(SELECT sid
    5.                 FROM t_score
    6.                 WHERE score < 60
    7.                 GROUP BY sid
    8.                 HAVING COUNT(cid) >= 2) AS b
    9.      ON a.sid = b.sid
    10.      GROUP BY a.sid) AS c
    11. LEFT JOIN t_student AS d
    12. ON c.sid = d.sid;

    解题思路：


    13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息

    1. SELECT b.*,a.score
    2. FROM(SELECT sid,score
    3.      FROM t_score
    4.      WHERE cid = '01' AND score < 60) AS a
    5. LEFT JOIN t_student AS b
    6. ON a.sid = b.sid
    7. ORDER BY a.score desc;


    14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

    1. SELECT a.sid,a.cid,a.score,pjcj
    2. FROM(SELECT a.sid,b.cid,b.score
    3.      FROM t_student AS a
    4.      LEFT JOIN t_score AS b
    5.      ON a.sid = b.sid) AS a
    6. LEFT JOIN (SELECT sid,AVG(score) AS pjcj
    7.            FROM t_score
    8.            GROUP BY sid) AS b
    9. ON a.sid = b.sid
    10. ORDER BY b.pjcj DESC;

    解题思路：


    15）查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

    1. SELECT a.*,b.Cname
    2. FROM(SELECT 
    3.      cid,
    4.      COUNT(*)   AS 选修人数,
    5.      MAX(score) AS 最高分,
    6.      MIN(score) AS 最低分,
    7.      AVG(score) AS 平均分,
    8.      SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*) AS 及格率,
    9.      SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT(*) AS 中等率,
    10.      SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优良率,
    11.      SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*) AS 优秀率
    12.      FROM t_score
    13.      GROUP BY cid
    14.      ORDER BY COUNT(*) DESC,CId ASC) AS a
    15. LEFT JOIN t_course AS b
    16. ON a.cid = b.cid;

    解题思路：

  • 相关阅读:
    互联网摸鱼日报（2022-12-05）
    Java中操作字符串有哪些类？他们之间有什么区别?
    bpa软件视频教程,BPA是什么软件
    手机微信里面的文件打印步骤
    目标检测算法——3D公共数据集汇总 2（附下载链接）
    集简云平台助力无代码开发，实现平安银行与电商平台、CRM系统的快速连接
    Unity UGUI开发规范
    【C++】借助cout进行控制台文本输出的基本原理及复杂格式控制方法
    Python内置函数--iter()&next()
    进程地址空间
  • 原文地址：https://blog.csdn.net/m0_67477525/article/details/125575239