MySQL学习代码记录-5(DQL语法大部)_unknown column '’李勇’' in 'field list-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44350366/article/details/105789003
今天主要弄的是MySQL语句的查询……

mysql> #重新建个库与表吧。
mysql> DROP DATABASE jwgl;
Query OK, 3 rows affected (0.06 sec)

mysql> #重建
mysql> CREATE DATABASE IF NOT EXISTS jwgl;       -- 创建数据库 jwgl（教务管理）
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> USE jwgl;                                 -- 转到jwgl
Database changed
mysql> 
mysql> CREATE TABLE IF NOT EXISTS student(           -- 创建学生信息表student
    -> sno CHAR(9) ,                 -- sno：学号 
    -> sname VARCHAR(10) ,           -- sname：学生姓名
    -> sgender CHAR,                 -- sgender：学生性别
    -> sage TINYINT UNSIGNED,           -- sage：学生年龄
    -> sdept CHAR(2) COMMENT 'IS信息系、CS计算机系、MA数学系'        -- sdept：学生所在系，IS信息系、CS计算机系、MA数学系
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> # 插入数据到学生表中
mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815121', '李勇', '男', 23, 'CS');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815122', '刘晨', '女', 20, 'IS');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815123', '王敏', '女', 19, 'MA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815124', '王大海', '男', 21, 'MA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815125', '张立', '男', 20, 'IS');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815126', '于大宝', '男', 21, 'IS');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  student (sno, sname, sgender, sage, sdept) VALUES ('201815127', '李斌', '男', 19, 'CS');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> CREATE TABLE IF NOT EXISTS course(        -- 创建课程信息表course
    -> cno CHAR(2) ,                     -- cno：课程编号
    -> cname VARCHAR(20) ,               -- cname：课程名称
    -> cpno CHAR(2)  COMMENT '先修课程号',      -- cpno：先修课程号
    -> ccredit TINYINT UNSIGNED                 -- ccredit:学分
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> # 插入数据到课程信息表中
mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('01', '数据库',NULL, 4);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('02', '数学',NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('03', '信息系统',NULL, 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('04', '操作系统',NULL, 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('05', '数据结构',NULL, 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('06', '数据处理',NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('07', 'PASCAL语言',NULL, 4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('08', 'DB_Design',NULL, 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('09', 'DBSDesign',NULL, 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  course (cno, cname, cpno, ccredit) VALUES ('10', '线性代数',NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> # 修改课程信息表中部分数据
mysql> UPDATE course SET Cpno='05' WHERE Cno='01';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE course SET Cpno='01' WHERE Cno='03';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE course SET Cpno='06' WHERE Cno='04';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE course SET Cpno='07' WHERE Cno='05';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE course SET Cpno='06' WHERE Cno='07';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE course SET Cpno='01' WHERE Cno='08';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> CREATE TABLE IF NOT EXISTS sc(               -- 创建学生选课信息表sc
    -> sno CHAR(9) ,                        -- sno：学号
    -> cno CHAR(2) ,                        -- cno：课程编号
    -> score TINYINT UNSIGNED               -- score：成绩 
    ->         );
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815121', '01', 92);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815121', '02', 85);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815121', '03', 88);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815122', '02', 90);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815122', '03', 59);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815124', '07',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO  sc (sno, cno, score) VALUES ('201815126', '08',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> #现在应该重建完成，检查一下。
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jwgl               |
| mydb1              |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_gbk           |
+--------------------+
8 rows in set (0.00 sec)

mysql> SELECT DATABASE();#因为之前的创建，现在应该已经在使用jwgl数据库了。
+------------+
| DATABASE() |
+------------+
| jwgl       |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;#查看该数据库下所有的表
+----------------+
| Tables_in_jwgl |
+----------------+
| course         |
| sc             |
| student        |
+----------------+
3 rows in set (0.00 sec)

mysql> #由上表可知一共有三个数据表，依次完全打开，查询数据。
mysql> SELECT * FROM course;#SELECT是查询语句的关键字，各类查询语句
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM sc；
    -> ;#刚刚的；似乎用了中文应该要报错了……
ERROR 1146 (42S02): Table 'jwgl.sc；' doesn't exist
mysql> SELECT * FROM sc;
+-----------+------+-------+
| sno       | cno  | score |
+-----------+------+-------+
| 201815121 | 01   |    92 |
| 201815121 | 02   |    85 |
| 201815121 | 03   |    88 |
| 201815122 | 02   |    90 |
| 201815122 | 03   |    59 |
| 201815124 | 07   |  NULL |
| 201815126 | 08   |  NULL |
+-----------+------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM student;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> #显示完毕
mysql> #按照幕布顺序给予各类表查询语句的操作实例，也是DQL操作
mysql> ＃SELSCT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组列表 HAVING 分组后的条件        ORDER BY   排序 LIMIT 分页限定
    -> ＃
    -> ；
    -> 
    -> ；；；
    -> ｘｓｘｓａｘｓａ扎克；；；；；；；;;;;;#
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '＃SELSCT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组列' at line 1
ERROR: 
No query specified

ERROR: 
No query specified

ERROR: 
No query specified

ERROR: 
No query specified

mysql> #看来微软自带的输入法出问题了，可是搜狗输入法在命令提示符窗口显示不了候选字，这有点狗…………
mysql> #SELSCT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组列表 HAVING 分组后的条件 ORDER BY 排序 LIMIT 分页限定
mysql> #这些是DQL查询语句语法简览
mysql> #若要查询某表全部语句，就跟之前本人查询SC表之类一样，使用*号，表示无条件
mysql> #举例
mysql> SELECT * FROM student;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> #现在我们不需要那么多列，只想要特定的列，可以将*换成想要的列名，若需要显示多个列则，中间用，割开。
mysql> SELECT sname,sage;
ERROR 1054 (42S22): Unknown column 'sname' in 'field list'
mysql> SELECT sname,sage FROM student;
+-----------+------+
| sname     | sage |
+-----------+------+
| 李勇      |   23 |
| 刘晨      |   20 |
| 王敏      |   19 |
| 王大海    |   21 |
| 张立      |   20 |
| 于大宝    |   21 |
| 李斌      |   19 |
+-----------+------+
7 rows in set (0.00 sec)

mysql> #这样能使获取信息更加清晰有效
mysql> #在原查询命令中加入WHERE子句，实现数据筛选
mysql> #逻辑比较的操作符是最好理解和记忆的，跟数学与其他程序语言的逻辑符号涵义无差别
mysql> #查询STUDENT表中系别为IS的学生
mysql> SELECT * FROM  student WHERE sdept ='IS';
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
+-----------+-----------+---------+------+-------+
3 rows in set (0.00 sec)

mysql> #输出正确
mysql> #查询20岁以下的学生信息，并只表示姓名及年龄字段
mysql> SELECT sname,sage FROM student WHERE sage<=20;
+--------+------+
| sname  | sage |
+--------+------+
| 刘晨   |   20 |
| 王敏   |   19 |
| 张立   |   20 |
| 李斌   |   19 |
+--------+------+
4 rows in set (0.00 sec)

mysql> ##做不配备查询，使用<>,!=这两个表示不等于
mysql> #查询年龄不等于20岁的，用两种方式
mysql> SELECT * FROM student WHERE sage!=20;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
5 rows in set (0.00 sec)

mysql> SELECT sname FROM student WHERE sage<>20;#进行一定的补充
+-----------+
| sname     |
+-----------+
| 李勇      |
| 王敏      |
| 王大海    |
| 于大宝    |
| 李斌      |
+-----------+
5 rows in set (0.00 sec)

mysql> #范围值查询
mysql> #查询20-21岁的学生
mysql> SELECT * FROM student WHERE sage BETWEEN 20 AND 21;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
+-----------+-----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> #相反
mysql> SELECT * FROM student WHERE sage NOT BETWEEN 20 AND 21;#增加not关键字
+-----------+--------+---------+------+-------+
| sno       | sname  | sgender | sage | sdept |
+-----------+--------+---------+------+-------+
| 201815121 | 李勇   | 男      |   23 | CS    |
| 201815123 | 王敏   | 女      |   19 | MA    |
| 201815127 | 李斌   | 男      |   19 | CS    |
+-----------+--------+---------+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM student WHERE sage>20 AND sage<21;#尝试另一种表达
Empty set (0.00 sec)

mysql> SELECT * FROM student WHERE sage>=20 AND sage<=21;#尝试另一种表达
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
+-----------+-----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> ##之前没有等于号，显示空集，正确
mysql> #查询空信息,sc表中有
mysql> SELECT * FROM sc WHERE score IS NULL;
+-----------+------+-------+
| sno       | cno  | score |
+-----------+------+-------+
| 201815124 | 07   |  NULL |
| 201815126 | 08   |  NULL |
+-----------+------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM sc WHERE score IS NOT NUL#相反加not
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NUL' at line 1
mysql> SELECT * FROM sc WHERE score IS NOT NULL;
+-----------+------+-------+
| sno       | cno  | score |
+-----------+------+-------+
| 201815121 | 01   |    92 |
| 201815121 | 02   |    85 |
| 201815121 | 03   |    88 |
| 201815122 | 02   |    90 |
| 201815122 | 03   |    59 |
+-----------+------+-------+
5 rows in set (0.00 sec)

mysql> #尝试整合到现在为止的所有语法
mysql> SELECT cname,cpno FROM course WHERE ccredit>
    -> ;重新来一次
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
    -> SELECT cname,cpno 
    -> FROM course 
    -> WHERE ccredit>2
    ->  AND
    ->  cpno!=05
    -> 	OR cpno IS NULL
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '重新来一次
SELECT cname,cpno 
FROM course 
WHERE ccredit>2
 AND
 cpno!=05
	' at line 1
mysql> # 05看来是字符形式
mysql> SELECT cname,cpno FROM course WHERE ccredit>=2 OR cpno IS NULL AND cno=01;
+--------------+------+
| cname        | cpno |
+--------------+------+
| 数据库       | 05   |
| 数学         | NULL |
| 信息系统     | 01   |
| 操作系统     | 06   |
| 数据结构     | 07   |
| 数据处理     | NULL |
| PASCAL语言   | 06   |
| DB_Design    | 01   |
| DBSDesign    | NULL |
| 线性代数     | NULL |
+--------------+------+
10 rows in set (0.01 sec)

mysql> # 后面的cno=01似乎没有用，反向再试一次
mysql> SELECT cname,cpno FROM course WHERE ccredit>=2 AND cpno IS NOT NULL OR cno=01;
+--------------+------+
| cname        | cpno |
+--------------+------+
| 数据库       | 05   |
| 信息系统     | 01   |
| 操作系统     | 06   |
| 数据结构     | 07   |
| PASCAL语言   | 06   |
| DB_Design    | 01   |
+--------------+------+
6 rows in set (0.00 sec)

mysql> ##现在查询时去除重复值：使用DISTINCT 关键字
mysql> SELECT DISTINCT sdept FROM student;
+-------+
| sdept |
+-------+
| CS    |
| IS    |
| MA    |
+-------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT (sdept,sgender) FROM student;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT DISTINCT sdept,sgender FROM student;
+-------+---------+
| sdept | sgender |
+-------+---------+
| CS    | 男      |
| IS    | 女      |
| MA    | 女      |
| MA    | 男      |
| IS    | 男      |
+-------+---------+
5 rows in set (0.00 sec)

mysql> #去除这些列中的重复句，并响应显示
mysql> #限制结果：LIMIT
mysql> SELECT * FROM student LIMIT 5;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
+-----------+-----------+---------+------+-------+
5 rows in set (0.00 sec)

mysql> #显示前5条信息
mysql> SELECT * FROM student LIMIT 5 OFFSET -1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
mysql> SELECT * FROM student LIMIT 5 OFFSET 0;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
+-----------+-----------+---------+------+-------+
5 rows in set (0.00 sec)

mysql> #添加OFFSET关键字，后跟需要开始的索引，利用此功能可以完成网页十条显示之类的功能。
mysql> SELECT * FROM student LIMIT 5 OFFSET 3;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> #一共7条数据想要在第4条开始，显示后面的5条--3=4-1，4=7-4+1<5
mysql> SELECT * FROM student LIMIT 3,5;
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
4 rows in set (0.00 sec)

mysql> #实现同样的功能，但功能顺序有所转换
mysql> #开始的索引 = （当前的页码 - 1） * 每页显示的条数
mysql> SELECT student.sno , student.sname FROM student;
+-----------+-----------+
| sno       | sname     |
+-----------+-----------+
| 201815121 | 李勇      |
| 201815122 | 刘晨      |
| 201815123 | 王敏      |
| 201815124 | 王大海    |
| 201815125 | 张立      |
| 201815126 | 于大宝    |
| 201815127 | 李斌      |
+-----------+-----------+
7 rows in set (0.00 sec)

mysql> #上一语句的完全限定有点搞不懂有什么实际用处
mysql> #或者说跟在字段前不加表名称的有什么不同
mysql> #查询结果排序：ORDER BY
mysql> #注意：ORDER BY 子句必须是SELECT语句中的最后一个子句
mysql> #排序方向：ASC ：升序,(默认，可不写出) ；DESC：降序，DESCENDING，必须指出;
mysql> SELECT  * FROM student ORDER BY sage; #按年龄升序排序
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815127 | 李斌      | 男      |   19 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815121 | 李勇      | 男      |   23 | CS    |
+-----------+-----------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> SELECT  * FROM student ORDER BY sage ASC; #按年龄升序排序
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815127 | 李斌      | 男      |   19 | CS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815121 | 李勇      | 男      |   23 | CS    |
+-----------+-----------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> SELECT  * FROM student ORDER BY sage DESC; #按年龄降序排序
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815121 | 李勇      | 男      |   23 | CS    |
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
| 201815122 | 刘晨      | 女      |   20 | IS    |
| 201815125 | 张立      | 男      |   20 | IS    |
| 201815123 | 王敏      | 女      |   19 | MA    |
| 201815127 | 李斌      | 男      |   19 | CS    |
+-----------+-----------+---------+------+-------+
7 rows in set (0.00 sec)

mysql> #使用多条件时，AND的优先级高于OR，之前的输出不等同于设想就是这个原因了。
mysql> #模糊查询，LIKE 操作符 结合通配符 % （代表任何字符出现任何次数）和 _ （单个任意字符）
mysql> #要是想表达明确的_符号，要在前面加上通配符\
mysql> #举例
mysql> SELECT * FROM course;#course有测试的可能
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '数%';#查询课程表中课程名以"数"字开头的课程信息
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
+------+--------------+------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '%数据库%';
+------+-----------+------+---------+
| cno  | cname     | cpno | ccredit |
+------+-----------+------+---------+
| 01   | 数据库    | 05   |       4 |
+------+-----------+------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '数据_';
+------+-----------+------+---------+
| cno  | cname     | cpno | ccredit |
+------+-----------+------+---------+
| 01   | 数据库    | 05   |       4 |
+------+-----------+------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '%%';
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '_';
Empty set (0.00 sec)

mysql> #没有只有一个字符的课程
mysql> SELECT * FROM course WHERE cname LIKE '%_';
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '%\_';#注意奇迹出现……
Empty set (0.00 sec)

mysql> SELECT * FROM course WHERE cname LIKE '%\_%';##现在才是
+------+-----------+------+---------+
| cno  | cname     | cpno | ccredit |
+------+-----------+------+---------+
| 08   | DB_Design | 01   |       3 |
+------+-----------+------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM student WHERE sname REGEXP '大';
+-----------+-----------+---------+------+-------+
| sno       | sname     | sgender | sage | sdept |
+-----------+-----------+---------+------+-------+
| 201815124 | 王大海    | 男      |   21 | MA    |
| 201815126 | 于大宝    | 男      |   21 | IS    |
+-----------+-----------+---------+------+-------+
2 rows in set (0.01 sec)

mysql> #正则表达式(regular expression)进行搜索：REGEXP 关键字
mysql> SELECT * FROM course  WHERE  cname REGEXP '[a-zA-Z]{7}';#查询课程名称包含7个以上连续英文字母的课程
+------+-----------+------+---------+
| cno  | cname     | cpno | ccredit |
+------+-----------+------+---------+
| 09   | DBSDesign | NULL |       3 |
+------+-----------+------+---------+
1 row in set (0.00 sec)

mysql> SELECT * FROM course  WHERE  cname expression '[a-zA-Z]{7}';#查询课程名称包含7个以上连续英文字母的课程
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'expression '[a-zA-Z]{7}'' at line 1
mysql> SELECT * FROM course  WHERE  cname expression '[a-zA-Z]{7}';#查询课程名称包含7个以上连续英文字母的课程
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'expression '[a-zA-Z]{7}'' at line 1
mysql> #正则表达式，通常被用来检索、替换那些符合某个模式(规则)的文本
mysql> #整合
mysql> SELECT * FROM course;#以此表为基础
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT  course.cpno,ccredit,DISTINCT cname FROM course; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT cname FROM course' at line 1
mysql> SELECT DISTINCT  course.cpno,ccredit, FROM course; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM course' at line 1
mysql> SELECT DISTINCT cpno,ccredit, FROM course; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM course' at line 1
mysql> SELECT DISTINCT cpno,ccredit FROM course; 
+------+---------+
| cpno | ccredit |
+------+---------+
| 05   |       4 |
| NULL |       2 |
| 01   |       4 |
| 06   |       3 |
| 07   |       4 |
| 06   |       4 |
| 01   |       3 |
| NULL |       3 |
+------+---------+
8 rows in set (0.00 sec)

mysql> SELECT DISTINCT cpno,ccredit FROM course; 
+------+---------+
| cpno | ccredit |
+------+---------+
| 05   |       4 |
| NULL |       2 |
| 01   |       4 |
| 06   |       3 |
| 07   |       4 |
| 06   |       4 |
| 01   |       3 |
| NULL |       3 |
+------+---------+
8 rows in set (0.00 sec)

mysql> #DISTINCT只能放在前面，并对之后所有字符串成立
mysql> SELECT * FROM course;#再来一次并叠加语句。
+------+--------------+------+---------+
| cno  | cname        | cpno | ccredit |
+------+--------------+------+---------+
| 01   | 数据库       | 05   |       4 |
| 02   | 数学         | NULL |       2 |
| 03   | 信息系统     | 01   |       4 |
| 04   | 操作系统     | 06   |       3 |
| 05   | 数据结构     | 07   |       4 |
| 06   | 数据处理     | NULL |       2 |
| 07   | PASCAL语言   | 06   |       4 |
| 08   | DB_Design    | 01   |       3 |
| 09   | DBSDesign    | NULL |       3 |
| 10   | 线性代数     | NULL |       2 |
+------+--------------+------+---------+
10 rows in set (0.00 sec)

mysql> SELECT DISTINCT cname,coures.cpno,ccredit
    -> FROM course
    -> ;
ERROR 1054 (42S22): Unknown column 'coures.cpno' in 'field list'
mysql> #应该是没有空格了……
mysql> SELECT DISTINCT cname,coures.cpno,ccredit
    -> 	FROM course
    -> 	WHERE ccredit>2 AND cno<>'01' 
    -> 	ORDER BY ccredit DESC
    -> 	LIMIT 10 OFFSET 0;
ERROR 1054 (42S22): Unknown column 'coures.cpno' in 'field list'
mysql> SELECT DISTINCT cname,cpno,ccredit
    -> 	FROM course
    ->     ->  WHERE ccredit>2 AND cno<>'01'
    ->     ->  ORDER BY ccredit DESC
    ->     ->  LIMIT 10 OFFSET 0;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->  WHERE ccredit>2 AND cno<>'01'
    ->  ORDER BY ccredit DESC
    ->  LIMIT 10' at line 3
mysql> SELECT DISTINCT cname,cpno,ccredit
    -> 	FROM course
    -> 	WHERE ccredit>2 AND cno<>'01' 
    -> 	ORDER BY ccredit DESC
    -> 	LIMIT 10 OFFSET 0;
+--------------+------+---------+
| cname        | cpno | ccredit |
+--------------+------+---------+
| PASCAL语言   | 06   |       4 |
| 信息系统     | 01   |       4 |
| 数据结构     | 07   |       4 |
| DBSDesign    | NULL |       3 |
| DB_Design    | 01   |       3 |
| 操作系统     | 06   |       3 |
+--------------+------+---------+
6 rows in set (0.00 sec)

mysql> SELECT DISTINCT cname,cpno,ccredit
    -> 	FROM course
    -> 	WHERE ccredit>2 AND cno<>'01' OR cpno IS NULL
    -> 	ORDER BY ccredit DESC
    -> 	LIMIT 10 OFFSET 0;
+--------------+------+---------+
| cname        | cpno | ccredit |
+--------------+------+---------+
| PASCAL语言   | 06   |       4 |
| 信息系统     | 01   |       4 |
| 数据结构     | 07   |       4 |
| DBSDesign    | NULL |       3 |
| DB_Design    | 01   |       3 |
| 操作系统     | 06   |       3 |
| 数学         | NULL |       2 |
| 数据处理     | NULL |       2 |
| 线性代数     | NULL |       2 |
+--------------+------+---------+
9 rows in set (0.00 sec)

mysql> #就是这样最后一段才是精华啊
mysql> \t