SQL基础

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://myzitong.com/article/pjhshp.html