MySQL数据操作语句

本文只介绍基础、常用的 MySQL 语法语句，更详细的语法语句请移步这里。
另本文所使用的数据库及对应的表请参考MySQL数据定义语句。

1 INSERT 语法

语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

1.1 向 `Student` 表中插入数据

INSERT INTO Student
    (Sno, Sname, Ssex, Sage, Sdept)
    VALUES
    (201215121, '李勇','男', 20,'CS'),
    (201215122, '刘晨', '女', 19, 'CS'),
    (201215123, '王敏', '女', 18, 'MA'),
    (201215125, '张立', '男', 19, 'IS');

查看一下表中都有哪些数据，可以使用 select * from Student

1.2 向 `Course` 表中插入数据

 INSERT INTO Course
    (Cno, Cname, Cpno, Ccredit)
    VALUES
    (1, '数据库', 5, 4),
    (2, '数学', NULL, 2),
    (3, '信息系统', 1, 4),
    (4, '操作系统', 6, 3),
    (5, '数据结构', 7, 4),
    (6, '数据处理', NULL, 2),
    (7, 'PASCAl语言', 6, 4);

查看一下 Course 表中的数据

数据都已经插入到表中了。

1.3 向 `SC` 表中插入数据

 INSERT INTO SC
    (Sno, Cno, Grade)
    VALUES
    (201215121, 1, 92),
    (201215121, 2, 85),
    (201215121, 3, 88),
    (201215122, 2, 90),
    (201215122, 3, 80);

查看一下表中的数据

2 UPDATE 语法

语法
Single-table语法:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

2.1 更新 `Student` 表中学号为 `201215121` 的年龄为 `21`

 UPDATE Student
    set Sage = 21
    where Sno = 201215121;

查看一下更新后的数据

数据已更新。

2.2 多表联合更新，将 `李勇` 的所有成绩加上 `5` 分

 UPDATE Student, SC
    SET Grade = Grade + 5
    WHERE Student.Sname = '李勇'
    AND Student.Sno = SC.Sno;

查看一下 SC 表

李勇的成绩都提高了5分。

3 SELECT 语法

语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

3.1 Writing Basic SQL SELECT Statements

3.1.1 Basic SELECT Statement

SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;

3.1.2 Selecting All Columns

SELECT * FROM Student;

3.1.3 Selecting Specific Columns

 SELECT Sno, Sname
    FROM Student;

3.1.4 Using Arithmetic Operators

SELECT Sno, Cno, Grade - 60
    FROM SC;

3.1.5 Using Column Aliases

SELECT Ssex AS sex, Sage "Student Age"
    FROM Student;

3.1.6 Duplicate Rows

 SELECT Sno From SC;

3.1.7 Eliminating Duplicate Rows

SELECT DISTINCT Sno From SC;

3.2 Restricting and Sorting Data

SELECT *|{ [DISTINCT] column|expression [alias], ...}
FROM table
[WHERE condition(s)];

3.2.1 Using the WHERE Clause

SELECT Sno, Sname, Ssex, Sdept
    FROM Student
    WHERE Sno = 201215121;

3.2.2 Character Strings and Datas

 SELECT Sno, Sname, Ssex, Sdept
    FROM Student
    WHERE Sname = '李勇';

3.2.3 Using Comparison Conditions

 SELECT Sno, Cno, Grade
    FROM SC
    where Grade <= 90;

3.2.4 Using the BETWEEN Condition

 SELECT Sno, Cno, Grade
    FROM SC
    WHERE Grade BETWEEN 85 AND 93;

3.2.5 Using the IN Conditon

 SELECT Sname, Sage, Sdept
    FROM Student
    WHERE Sdept IN ('CS', 'MA');

3.2.6 Using the Like Condition

 SELECT Sname, Sdept
    FROM Student
    WHERE Sdept LIKE 'C%';

 SELECT Sname, Sdept
    FROM Student
    WHERE Sdept LIKE '_s';

3.2.7 Using the NULL Condition

 SELECT Cno, Cname, Cpno
    FROM Course
    WHERE Cpno IS NULL;

3.2.8 Using the AND Operator

SELECT Sno, Cno
    FROM SC
    WHERE Sno = 201215121
    AND Cno = 2;

3.2.9 Using the OR Operator

SELECT Sno, Cno
    FROM SC
    WHERE Sno = 201215121
    OR Cno = 2;

3.2.10 Using the NOT Operator

SELECT Sno, Sname, Sdept
    FROM Student
    WHERE Sdept NOT IN ('MA', 'IS');

3.2.11 ORDER BY Clause

SELECT Sno, Cno, Grade
    FROM SC
    ORDER BY Grade ASC;

SELECT Sno, Cno, Grade
    FROM SC
    ORDER BY Grade DESC;

3.2.12 Sorting by Column Alias

 SELECT Sno, Cno, Grade-60 over_60
    FROM SC
    ORDER BY over_60 DESC;

3.2.13 Sorting by Multiple Columns

SELECT Sno, Cno, Grade
    FROM SC
    ORDER BY Sno ASC, Grade DESC;

3.3 Displaying Data from Multiple Tables

3.3.1 Retrieving Records with Equijoins

SELECT Sname, Student.Sno, Cno, Sdept
    FROM Student, SC
    WHERE Student.Sno = SC.sno
    ORDER BY Sno;

3.3.2 Retrieving Records with Non-Equijoins

SELECT Sname, Student.Sno, Cno, Grade
    FROM Student, SC
    WHERE Grade BETWEEN 85 AND 93;

3.3.3 Retrieving Records with Left-joins

SELECT Sname, Student.Sno, Cno, Grade
    FROM Student LEFT JOIN SC
    ON Student.Sno = SC.Sno;

SELECT Sname, Student.Sno, Cno, Grade
    FROM SC LEFT JOIN Student
    ON Student.Sno = SC.Sno;

左连接可以理解为左表驱动右表。

3.4 Aggregating Data Using Group Functiong

语法

SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];

3.4.1 Using the AVG、MAX、MIN and MAX Functions

 SELECT AVG(Grade), MAX(Grade), MIN(Grade), SUM(Grade)
    FROM SC
    WHERE Sno=201215121;

3.4.2 Using the COUNT Function

SELECT COUNT(Sno)
    FROM SC;

SELECT COUNT(DISTINCT Sno)
    FROM SC;

3.4.3 Using the GROUP BY Clause

 SELECT Student.Sno, Sname, AVG(Grade)
    FROM Student, SC
    WHERE Student.Sno = Sc.Sno
    GROUP BY Student.Sno;

3.4.4 Illegal Queries Using Group Functions

SELECT 语句中任何不是聚集函数的列或者表达式都必须包含在 GROUP BY 语句中

SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno;

不能使用 SELECT 语句去限制分组结果

SELECT Sno, AVG(Grade)
    FROM SC
    GROUP BY Sno
    HAVING AVG(Grade) > 90;

3.5 Subqueries

语法

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

3.5.1 查询 `李勇` 所在系的学生名单

SELECT *
    FROM Student
    WHERE Sdept IN(
    SELECT Sdept
    FROM Student
    WHERE Sname = '李勇');

3.5.2 查询所有至少选了`刘晨` 所选课的学生姓名、学号、课程号

SELECT DISTINCT Sname, st1.Sno, Cno
FROM Student st1, SC sc1
WHERE NOT EXISTS (
    SELECT * 
    FROM SC sc2, Student st2
    WHERE sc2.Sno = st2.Sno
    AND st2.Sname = '刘晨'
    AND NOT EXISTS (
        SELECT * 
        FROM SC sc3
        WHERE st1.Sno = sc3.Sno
        AND sc2.Cno = sc3.Cno
    )
) AND st1.Sno = sc1.Sno
ORDER BY st1.Sno;

参考

MySQL 5.1中文文档

posted @ 2022-07-20 12:16 岁月飞扬阅读(115) 评论(0) 收藏举报

刷新页面返回顶部

岁月飞扬