本文共 5605 字，大约阅读时间需要 18 分钟。

MySQL数据库操作指南

一、基本操作

1. 增加记录

可以使用 INSERT 语句添加新员工数据：

INSERT INTO t_mysql_employees (first_name, last_name, email, phone_number, salary, commission_pct, manager_id, hiredate) VALUES('李', '施', '393322561@qq.com', '16607478549', 6000, 0.25, 100, now());

2. 修改记录

使用 UPDATE 语句更新员工信息：

UPDATE t_mysql_employees SET last_name = '施'WHERE phone_number = '16607478549';

3. 删除记录

使用 DELETE 语句删除指定电话号码的员工记录：

DELETE FROM t_mysql_employees WHERE phone_number = '16607478549';

二、查询操作

1. 基础查询

• 查询单个字段：

SELECT first_name FROM t_mysql_employees;

• 查询多个字段：

SELECT first_name, last_name FROM t_mysql_employees;

• 查询所有字段：

SELECT * FROM t_mysql_employees;

• 查询常量值：

SELECT 100;

• 查询表达式：

SELECT 100%98;

• 查询数据库版本：

SELECT VERSION();

2. 按条件筛选

• 查询工资大于12000的员工信息：

SELECT * FROM t_mysql_employees WHERE salary > 12000;

• 查询部门编号不等于90的员工名和部门编号：

SELECT first_name, department_id FROM t_mysql_employees WHERE department_id != 90;

3. 逻辑筛选

• 查询工资在10000到20000之间的员工信息：

SELECT first_name, salary, commission_pct FROM t_mysql_employees WHERE salary BETWEEN 10000 AND 20000;

• 查询部门编号不在90到110之间或工资高于15000的员工信息：

SELECT * FROM t_mysql_employees WHERE NOT (department_id BETWEEN 90 AND 110) OR salary > 15000;

4. 模糊查询

• 查询员工名中包含字符 a 的员工信息：

SELECT * FROM t_mysql_employees WHERE first_name LIKE '%a%';

• 查询员工名中第三个字符为 e，第五个字符为 a 的员工名和工资：

SELECT * FROM t_mysql_employees WHERE first_name LIKE '%__e_a%';

• 查询员工名中第二个字符为 _ 的员工名：

SELECT * FROM t_mysql_employees WHERE last_name LIKE '_$_% ESCAPE '$';

5. 排序

• 按员工薪资排序：

SELECT * FROM t_mysql_employees ORDER BY salary DESC;

• 查询部门编号大于等于90的员工信息，并按薪资降序：

SELECT * FROM t_mysql_employees WHERE department_id >= 90 ORDER BY salary DESC;

• 查询员工信息，按年薪降序：

SELECT *, salary*12*(1+IFNULL(commission_pct,0)) FROM t_mysql_employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

三、MySQL经典题

1. 创建表并插入数据

CREATE TABLE t_student (    sid VARCHAR(10),    sname VARCHAR(20),    sage DATE,    sax VARCHAR(10));INSERT INTO t_student VALUES('01', '赵雷', '1990-01-01', '男');INSERT INTO t_student VALUES('02', '钱电', '1990-12-21', '男');INSERT INTO t_student VALUES('03', '孙风', '1990-12-20', '男');INSERT INTO t_student VALUES('04', '李云', '1990-12-06', '男');INSERT INTO t_student VALUES('05', '周梅', '1991-12-01', '女');INSERT INTO t_student VALUES('06', '吴兰', '1992-01-01', '女');INSERT INTO t_student VALUES('07', '郑竹', '1989-01-01', '女');INSERT INTO t_student VALUES('09', '张三', '2017-12-20', '女');INSERT INTO t_student VALUES('10', '李四', '2017-12-25', '女');INSERT INTO t_student VALUES('11', '李四', '2012-06-06', '女');INSERT INTO t_student VALUES('12', '赵六', '2013-06-13', '女');INSERT INTO t_student VALUES('13', '孙七', '2014-06-01', '女');

2. 查询相关题目

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

SELECT a.*, b.score as `01`score, c.score as `02`score FROM t_student AS aINNER JOIN t_score AS b ON a.sid = b.sidINNER JOIN t_score AS c ON a.sid = c.sidWHERE b.cid = '01' AND c.cid = '02' AND b.score > c.score;

• 查询同时存在"01"课程和"02"课程的学生信息：

SELECT a.*, b.score as `01`score, c.score as `02`score FROM t_student AS aINNER JOIN t_score AS b ON a.sid = b.sidINNER JOIN t_score AS c ON a.sid = c.sidWHERE b.cid = '01' AND c.cid = '02';

• 查询存在"01"课程但可能不存在"02"课程的学生信息：

SELECT * FROM (    SELECT *     FROM t_score     WHERE cid = '01') AS a1WHERE a1.sid NOT IN (    SELECT sid     FROM t_score     WHERE cid = '02');

• 查询不存在"01"课程但存在"02"课程的学生信息：

SELECT * FROM (    SELECT *     FROM t_score     WHERE cid = '02') AS a1WHERE a1.sid NOT IN (    SELECT sid     FROM t_score     WHERE cid = '01');

• 查询平均成绩大于等于60分的同学信息：

SELECT a.sid, a.sname, e.avg_score FROM t_student AS aINNER JOIN (    SELECT sid, AVG(score) as avg_score     FROM t_score     GROUP BY sid     HAVING AVG(score) >= 60) AS b ON a.sid = b.sid;

• 查询在t_score表存在成绩的学生信息：

SELECT * FROM t_student WHERE sid IN (    SELECT sid     FROM t_score     GROUP BY sid);

• 查询所有同学的学生编号、姓名、选课总数及所有课程总成绩：

SELECT a.sname, a.sid, b.zs, b.zcj FROM t_student AS aINNER JOIN (    SELECT sid, COUNT(cid) as zs, SUM(score) as zcj     FROM t_score     GROUP BY sid) AS b ON a.sid = b.sid;

• 查询「李」姓老师的数量：

SELECT COUNT(*) FROM t_teacher WHERE tname LIKE '李%';

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

SELECT a.* FROM t_student AS aINNER JOIN t_course AS b ON a.sid = b.sidINNER JOIN t_teacher AS c ON b.tid = c.tidWHERE tname = '张三';

• 查询没有学全所有课程的同学信息：

SELECT * FROM t_student AS aINNER JOIN (    SELECT sid     FROM t_score     GROUP BY sid     HAVING COUNT(cid) < (        SELECT COUNT(cid)         FROM t_course    )) AS b ON a.sid = b.sid;

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

SELECT sname FROM t_student WHERE sid NOT IN (    SELECT a.sid     FROM t_student AS a    INNER JOIN t_course AS b ON a.sid = b.sid    INNER JOIN t_teacher AS c ON b.tid = c.tid    WHERE tname = '张三');

• 查询两门及其以上不及格课程的同学信息：

SELECT c.sid, d.sname, pjf FROM (    SELECT a.sid, AVG(score) as pjf     FROM t_score AS a    INNER JOIN (        SELECT sid         FROM t_score         WHERE score < 60        GROUP BY sid        HAVING COUNT(cid) >= 2    ) AS b ON a.sid = b.sid    GROUP BY a.sid) AS cLEFT JOIN t_student AS d ON c.sid = d.sid;

• 查询"01"课程分数小于60的学生信息并按分数降序排列：

SELECT b.*, a.score FROM (    SELECT sid, score     FROM t_score     WHERE cid = '01' AND score < 60) AS aLEFT JOIN t_student AS b ON a.sid = b.sidORDER BY a.score DESC;

• 查询各科成绩最高分、最低分和平均分：

SELECT CId,COUNT(*) AS 选修人数,MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分,(SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(*)) AS 及格率,(SUM(CASE WHEN score >= 70 AND score < 80 THEN 1 ELSE 0 END) / COUNT(*)) AS 中等率,(SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) / COUNT(*)) AS 优良率,(SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) / COUNT(*)) AS 优秀率FROM t_scoreGROUP BY CIdORDER BY 选修人数 DESC, CId ASC;

通过以上操作，可以对MySQL数据库进行增删改查和复杂查询操作，满足实际应用需求。

转载地址：http://uxbfk.baihongyu.com/