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值视为真
十余年的三都网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。网络营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整三都建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“三都网站设计”,“三都网站推广”以来，每个客户项目都认真落实执行。
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且 dMySQL5.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)
名称栏目：SQL基础
网页链接：
http://cdkjz.cn/article/pjhshp.html
