MySQL4-CSDN博客

本文链接：https://blog.csdn.net/Error_Fe/article/details/137239828
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)

mysql> create database lty;
Query OK, 1 row affected (0.00 sec)

mysql> set names gbk;
Query OK, 0 rows affected (0.01 sec)

mysql> use lty;
Database changed
mysql> SOURCE C:/Users/Administrator/Desktop/文件接收柜/sy7.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 14 rows affected (0.00 sec)
Records: 14  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM xs WHERE 专业名 IS NULL;
+--------+--------+--------+------+------------+--------+------+------+
| 学号      | 姓名       | 专业名     | 性别    | 出生日期           | 总学分      | 照片    | 备注    |
+--------+--------+--------+------+------------+--------+------+------+
| 567655 | 杜和平     | NULL   |    1 | 1994-11-08 |   NULL | NULL | NULL |
| 756566 | 王天保     | NULL   |    1 | 1998-12-03 |   NULL | NULL | NULL |
+--------+--------+--------+------+------------+--------+------+------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM xs WHERE 备注 LIKE '%补考%';
+--------+-------+----------+------+------------+--------+------+------------------------+
| 学号      | 姓名      | 专业名       | 性别    | 出生日期           | 总学分      | 照片    | 备注                      |
+--------+-------+----------+------+------------+--------+------+------------------------+
| 081202 | 王林      | 软件工程         |    1 | 1993-01-29 |     40 | NULL | 有一门课不及格，待补考                    |
| 081203 | 严红%    | 通信工程       |    0 | 1993-08-11 |     48 | NULL | 有一门课不及格，待补考                    |
+--------+-------+----------+------+------------+--------+------+------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM xs WHERE 姓名 LIKE '%林';
+--------+--------+----------+------+------------+--------+------+------------------------+
| 学号      | 姓名       | 专业名       | 性别    | 出生日期           | 总学分      | 照片    | 备注                      |
+--------+--------+----------+------+------------+--------+------+------------------------+
| 081101 | 王林       | 计算机         |    1 | 1994-02-10 |     50 | NULL | NULL                   |
| 081202 | 王林       | 软件工程         |    1 | 1993-01-29 |     40 | NULL | 有一门课不及格，待补考                    |
| 081303 | 孙祥林       | 网络工程       |    1 | 1993-03-09 |     42 | NULL | NULL                   |
+--------+--------+----------+------+------------+--------+------+------------------------+
3 rows in set (0.00 sec)

mysql> SELECT 学号, 姓名 FROM xs WHERE 学号 LIKE '__1%';
+--------+--------+
| 学号      | 姓名       |
+--------+--------+
| 081101 | 王林       |
| 081102 | 程\明      |
| 081103 | 王燕       |
| 081104 | 林平平     |
| 081201 | 王_敏      |
| 081202 | 王林       |
| 081203 | 严红%     |
| 081204 | 马琳琳       |
| 081301 | %李计      |
| 081302 | 李林庆       |
| 081303 | 孙祥林       |
| 081304 | 孙研_      |
+--------+--------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM xs WHERE MONTH(出生日期) IN (8, 10);
+--------+--------+----------+------+------------+--------+------+------------------------+
| 学号      | 姓名       | 专业名       | 性别    | 出生日期           | 总学分      | 照片    | 备注                      |
+--------+--------+----------+------+------------+--------+------+------------------------+
| 081103 | 王燕       | 计算机         |    0 | 1993-10-06 |     50 | NULL | NULL                   |
| 081104 | 林平平     | 计算机         |    1 | 1994-08-26 |     50 | NULL | NULL                   |
| 081203 | 严红%     | 通信工程       |    0 | 1993-08-11 |     48 | NULL | 有一门课不及格，待补考                    |
| 081304 | 孙研_      | 网络工程       |    1 | 1994-10-09 |     42 | NULL | NULL                   |
+--------+--------+----------+------+------------+--------+------+------------------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM xs WHERE YEAR(出生日期) <> 1995;
+--------+--------+----------+------+------------+--------+------+------------------------------+
| 学号      | 姓名       | 专业名       | 性别    | 出生日期           | 总学分      | 照片    | 备注                            |
+--------+--------+----------+------+------------+--------+------+------------------------------+
| 081101 | 王林       | 计算机         |    1 | 1994-02-10 |     50 | NULL | NULL                         |
| 081103 | 王燕       | 计算机         |    0 | 1993-10-06 |     50 | NULL | NULL                         |
| 081104 | 林平平     | 计算机         |    1 | 1994-08-26 |     50 | NULL | NULL                         |
| 081201 | 王_敏      | 通信工程       |    1 | 1993-06-10 |     42 | NULL | NULL                         |
| 081202 | 王林       | 软件工程         |    1 | 1993-01-29 |     40 | NULL | 有一门课不及格，待补考                          |
| 081203 | 严红%     | 通信工程       |    0 | 1993-08-11 |     48 | NULL | 有一门课不及格，待补考                          |
| 081204 | 马琳琳       | 通信工程       |    0 | 1993-01-29 |     42 | NULL | NULL                         |
| 081301 | %李计      | 网络工程       |    1 | 1993-09-20 |     42 | NULL | NULL                         |
| 081302 | 李林庆       | 软件工程         |    1 | 1993-05-01 |     44 | NULL | 已提前修完一门课，并获得学分                        |
| 081303 | 孙祥林       | 网络工程       |    1 | 1993-03-09 |     42 | NULL | NULL                         |
| 081304 | 孙研_      | 网络工程       |    1 | 1994-10-09 |     42 | NULL | NULL                         |
| 567655 | 杜和平     | NULL     |    1 | 1994-11-08 |   NULL | NULL | NULL                         |
| 756566 | 王天保     | NULL     |    1 | 1998-12-03 |   NULL | NULL | NULL                         |
+--------+--------+----------+------+------------+--------+------+------------------------------+
13 rows in set (0.00 sec)

mysql> SELECT * FROM xs_kc WHERE 学号 LIKE '%302%' AND 课程号 LIKE '%302%';
+--------+--------+------+------+
| 学号      | 课程号     | 成绩    | 学分    |
+--------+--------+------+------+
| 081302 | 302    |   89 |    5 |
+--------+--------+------+------+
1 row in set (0.00 sec)