SQL基础

发布时间：2020-05-29 07:57:48 来源：网络阅读：150 作者：何小帅栏目：系统运维

1 SQL语句分类

名称	作用	语法关键字
DDL	数据定义语言	CREATE,DROP,ALTER
DML	数据操纵语言	INSERT,DELETE,UPDATE
DQL	数据查询语言	SELECT
DCL	数据控制语言	GRANT,REVOKE,COMMIT,ROLLBACK

2 数据类型

数值类型
日期/时间类型
字符串（字符）类型

2.1 数值类型

2.1.1 整数型

类型	储存空间（字节）	范围
tinyint(m)	1	-128~127
smallint(m)	2	-32768~32767
mediumint(m)	3	-8388608~8388607
int(m)	4	-2147483648~2147483647
bigint(m)	8	-2^63~2^63-1

上述数据类型，如果加修饰符unsigned后，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的
BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

2.1.2 浮点型（float和double），近似值

float(m,d)单精度浮点型8位精度（4字节）m总个数，d小数位
double(m,d)双精度浮点型16位精度（8字节）m总个数，d小数位
假设一个字段定义为float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.457，但总个数还以实际为准，即6位

2.1.3 定点数

在数据库中存放的是精确值，存为十进制
decimal(m,d)参数m<65 是总个数，d<30且 d<m 是小数位
MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。
例如:
decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节
浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

2.2 日期/时间类型

数据类型	值
DATE	'0000-00-00'
TIME	'00:00:00'
DATETIME	'0000-00-00 00:00:00'
TIMESTAMP	'0000-00-00 00:00:00'
YEAR(2),YEAR(4)	00,0000

2.3 字符串类型

数据类型	值
char(n) 固定长度	最多255个字符
varchar(n) 可变长度	最多65535个字符
tinytext 可变长度	最多255个字符
text 可变长度	最多65535个字符
mediumtext 可变长度	最多2的24次方-1个字符
longtext 可变长度	最多2的32次方-1个字符
BINARY(M) 固定长度	可存二进制或字符，长度为0-M字节
VARBINARY(M) 可变长度	可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举，SET集合
ENUM是一个字符串对象，其值是从允许值的列表中选择的，这些值在表创建时在列规范中明确枚举
SET是可以具有零个或多个值的字符串对象，每个值都必须从创建表时指定的允许值列表中选择。 SET由多个set成员组成的列值用用逗号（,）分隔的成员指定。这样的结果是 SET成员值本身不应包含逗号。

2.3.1 char和varchar比较

1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3.char类型的字符串检索速度要比varchar类型的快

2.3.2 varchar和text比较

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text
数据类型

2.3.3 BLOB和TEXT比较

1.BLOB和test存储方式不同，text以文本方式存储，英文存储区分大小写，而blob以二进制方式存储，不区分大小写
2.BLOB存储的数据只能整体读出
3.text可以指定字符集，blob不用指定字符集

2.4 修饰符

2.4.1 适合所有类型的修饰符：

名称	作用
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
UNIQUE KEY	唯一键
CHARACTER SET name	指定一个字符集

2.4.2 适用数值型的修饰符

名称	作用
AUTO_INCREMENT	自动递增，适用于整数类型，不支持列的负值。
UNSIGNED	无符号，可用于仅允许一列中使用非负数，或者在您需要该列的较大的较高数字范围时使用

3 DDL语句

3.1 创建表

HELP CREATE TALBE;

创建表的方法

3.1.1 直接创建

CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型

范例：

mysql> CREATE TABLE student (id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED);
Query OK, 0 rows affected (0.03 sec)

mysql> DESC student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE employee (id int UNSIGNED NOT NULL,name VARCHAR(20) NOT NULL,age TINYINT UNSIGNED,PRIMARY KEY(id,name));
Query OK, 0 rows affected (0.02 sec)

3.1.2 通过查询现存表创建，新表会被直接插入查询而来的数据

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement

【例】通过查询现存表student的数据来创建新表new_student：

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> create table new_student select * from student;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from new_student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> desc new_student;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(10) unsigned    | NO   |     | 0       |       |
| name  | varchar(20)         | NO   |     | NULL    |       |
| age   | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

注：这种创建表的方法虽然把现存表的查询结果插入到了新表中，但是没有把现存表的表结构的修饰符插入到新表中。

3.1.3 通过复制现存的表的表结构创建，但不复制数据。

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }

【例】通过查询现存表student的数据来创建新表new2_student：

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | xiaoming |   18 |
|  2 | xiaohong |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> create table new2_student like student;
Query OK, 0 rows affected (0.30 sec)

mysql> desc new2_student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from new2_student;
Empty set (0.00 sec)

注：从上面的结果中可以发现，此种创建新表的方法，只是复制了现在表的表结构，而不复制数据。

3.2 表查看

3.2.1 查看支持的engine类型

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

注：默认是innodb存储引擎

3.2.2 查看当前数据库有哪些表

SHOW TABLES [FROM db_name]

3.2.3 查看表结构

DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

3.2.4 查看表创建命令

SHOW CREATE TABLE tbl_name;

3.2.5 查看表状态

SHOW TABLE STATUS LIKE 'tbl_name';

3.2.6 查看库中所有表状态

show table status from db_name;

3.3 修改和删除表

3.3.1 删除表

DROP TABLE [IF EXISTS] 'tbl_name';

3.3.2 修改表

语法：
ALTER TABLE tbl_name

获取帮助：
help alter table

# 在表中添加字段：add
语法：
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

# 删除表中字段：drop
语法：
DROP [COLUMN] col_name

# 修改表中字段：
change（字段名）
语法：
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

modify（字段属性）
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

范例：

3.3.2.1 修改student表名为S1

mysql> alter table student rename s1;
Query OK, 0 rows affected (0.01 sec)

3.3.2.2 在s1表中的name字段后面添加phone字段，并且设置数据类型为可变长的字符串。

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> alter table s1 add phone varchar(11) after name;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| phone | varchar(11)         | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.3 修改s1表中的phone字段的数据类型为int

mysql> alter table s1 modify phone int;
Query OK, 2 rows affected (7.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| phone | int(11)             | YES  |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.4 修改s1表中的phone字段的名称为mobile，并且将其数据类型修改为定长字符串char

mysql> alter table s1 change column phone mobile char(11);
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc s1;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| mobile | char(11)            | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.3.2.5 删除s1表中的mobile字段

mysql> alter table s1 drop column mobile;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc s1;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

修改s1表的字符集类型为utf8

mysql> alter table s1 character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改s1表中的name字段名为new_name，并且为其设置字符集为utf8

mysql> alter table s1 change name new_name varchar(20) character set utf8;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

4 DML语句

INSERT,DELETE,UPDATE

4.1 INSERT语句

功能：一次插入一行或多行数据  
获取帮助：help insert
简化语法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val2,...)

4.2 UPDATE语句

获取帮助：help update
语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注：一定要有(where)限制条件，否则将修改所有行的指定字段

可以利用以下选项避免此错误

mysql -U | --safe-updates| --i-am-a-dummy

范例

4.2.1 没加限制条件直接更新s1表的age为25，将导致所有行的age段都为25

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   22 | NULL  |
|  2 | xiaohong |   20 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

mysql> update s1 set age=25 ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   25 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

4.2.2 加where限制条件，只更新id为1的age值为18，以避免上面所发生的错误

mysql> update s1 set age=18 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   18 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

4.2.3 登录MySQL服务器的时候加上-U选项（也可以是其它两个），然后更新age字段的值为30，如果不加限制条件将直接报错，加上此选项从而避免了更新所有行的指定字段值的错误

[root@CentOS7-01 ~]#mysql -U -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mytest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from s1;
+----+----------+------+-------+
| id | new_name | age  | geder |
+----+----------+------+-------+
|  1 | xiaoming |   18 | NULL  |
|  2 | xiaohong |   25 | NULL  |
+----+----------+------+-------+
2 rows in set (0.00 sec)

mysql> update s1 set age=30;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
mysql> update s1 set age=30 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4.3 DELETE语句

获取帮助：help delete;

语法：
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

注意：一定要有限制条件，否则将清空表中的所有数据

如果只想清空表，但保留表结构，可以使用下面语句

truncate table tbl_name;

5 DQL语句

5.1 单表查询

获取帮助：help 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 [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

说明：

1)字段显示可以使用别名
字段1 AS 字段1别名，字段2 AS 字段2别名

2)WHERE字句：指明过滤条件以实现“选择”的功能。
过滤条件：布尔型表达式
算术操作符：+,-,*,/,%
比较操作符：=,<=>(相等或都为空),<>,!=(非标准SQL),>,>=,<,<=
BETWEEN min_num AND max_num # 在两个值之间
IN(element1,element2,...) # ()中的任意单个值，相当于列表
IS NULL #为空
IS NOT NULL #不为空
DISTINCT #去除重复列，范例：SELECT DISTINCT gender FROM students;
LIKE:
% 任意长度的任意字符
_ 任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
3)GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算，先过滤再分组
常见聚合函数：avg(), max(), min(), count(), sum()
HAVING: 对分组聚合运算后的结果指定过滤条件，先分组再过滤
4)ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
5)LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
6)对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作

5.1.1 练习

使用students表进行练习，表内容如下

+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |    NULL |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |    NULL |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  45 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
+-------+---------------+-----+--------+---------+-----------+

1.查询年龄大于25岁，且为男性的同学的名字和年龄

mysql> select name,age,gender from students where age > 25 and gender='m';
+-----------+-----+--------+
| name      | age | gender |
+-----------+-----+--------+
| Xie Yanke |  53 | M      |
| Ding Dian |  32 | M      |
| Yu Yutong |  26 | M      |
| Shi Qing  |  46 | M      |
+-----------+-----+--------+
4 rows in set (0.00 sec)

2.以ClassID为分组依据，显示每组的平均年龄

mysql> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  36.0000 |
|       1 |  22.0000 |
|       2 |  22.0000 |
|       3 |  35.5000 |
|       4 |  32.0000 |
|       5 |  46.0000 |
|       6 |  20.0000 |
|       7 |  17.0000 |
+---------+----------+
8 rows in set (0.00 sec)

3.显示第2题中平均年龄大于30的分组及平均年龄

mysql> select classid,avg(age) from students where age>30 group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  53.0000 |
|       3 |  45.0000 |
|       4 |  32.0000 |
|       5 |  46.0000 |
+---------+----------+
4 rows in set (0.00 sec)

4.显示以L开头的名字的同学的信息

mysql> select * from students where name like 'l%';
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     8 | Lin Daiyu |  17 | F      |       7 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

5.显示TeacherID非空的同学的相关信息

mysql> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |    NULL |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

6.以年龄排序后，显示年龄最大的前3位同学的信息

mysql> select * from students order by age desc limit 3;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |    NULL |        16 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    10 | Yue Lingshan |  45 | F      |       3 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

7.查询年龄大于等于20岁，小于等于25岁的同学的信息

mysql> select * from students where age between 20 and 25;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

8.以ClassID分组，显示每班的同学的人数

mysql> select classid as 班级id,count(stuid) as 班级人数 from students group by classid;
+----------+--------------+
| 班级id   | 班级人数     |
+----------+--------------+
|     NULL |            2 |
|        1 |            1 |
|        2 |            1 |
|        3 |            2 |
|        4 |            1 |
|        5 |            1 |
|        6 |            1 |
|        7 |            1 |
+----------+--------------+
8 rows in set (0.00 sec)

9.以Gender分组，显示其年龄之和

mysql> select gender 性别,sum(age) 年龄之和 from students group by gender;
+--------+--------------+
| 性别   | 年龄之和     |
+--------+--------------+
| F      |          101 |
| M      |          201 |
+--------+--------------+
2 rows in set (0.00 sec)

10.以ClassID分组，显示其平均年龄大于25的班级

mysql> select classid,avg(age) 平均年龄 from students group by classid having 平均年龄 > 25;
+---------+--------------+
| classid | 平均年龄     |
+---------+--------------+
|    NULL |      36.0000 |
|       3 |      35.5000 |
|       4 |      32.0000 |
|       5 |      46.0000 |
+---------+--------------+
4 rows in set (0.00 sec)

11.以Gender分组，显示各组中年龄大于25的学员的年龄之和

mysql> select gender,sum(age) from students where age > 25 group by gender;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| F      |       45 |
| M      |      157 |
+--------+----------+
2 rows in set (0.00 sec

12.显示其年龄大于平均年龄的同学的名字

mysql> select name,age from students where age > (select avg(age) from students);
+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Yue Lingshan |  45 |
+--------------+-----+
4 rows in set (0.00 sec)

13.对classid正序排序，NULL记录排在最后

mysql> select * from students order by -classid desc;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|    10 | Yue Lingshan |  45 | F      |       3 |      NULL |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|     9 | Ren Yingying |  20 | F      |       6 |      NULL |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|     3 | Xie Yanke    |  53 | M      |    NULL |        16 |
|     7 | Xi Ren       |  19 | F      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)

5.1.2 SQL注入

使用user表进行练习，表内容如下

+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  2 | root  | M      | 88888    |
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+

【例1】

mysql> select * from user where name='admin' and password='' or 1=1;
+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  2 | root  | M      | 88888    |
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+
2 rows in set (0.01 sec)

【例2】

mysql> select * from user where name='admin'; -- and password='abc123';
+----+-------+--------+----------+
| id | name  | gender | password |
+----+-------+--------+----------+
|  3 | admin | M      | 66666    |
+----+-------+--------+----------+
1 row in set (0.00 sec)

向AI问一下细节