SQL基础
发表于：2025-12-03 作者：千家信息网编辑
千家信息网最后更新 2025年12月03日，1 SQL语句分类名称作用语法关键字DDL数据定义语言CREATE,DROP,ALTERDML数据操纵语言INSERT,DELETE,UPDATEDQL数据查询语言SELECTDCL数据控制语言GRA
千家信息网最后更新 2025年12月03日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值视为真
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
例如:
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 type1PRIMARY KEY(col1,...)INDEX(col1, ...)UNIQUE KEY(col1, ...)#表选项：ENGINE [=] engine_nameROW_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: 0mysql> 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 | (LIKEold_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_nameSHOW COLUMNS FROM [db_name.]tb_name3.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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 04 DML语句
INSERT,DELETE,UPDATE4.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: 0mysql> 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: 0mysql> 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 -p123456mysql: [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 16Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use mytestReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 04.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，XOR3)GROUP：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算，先过滤再分组常见聚合函数：avg(), max(), min(), count(), sum()HAVING: 对分组聚合运算后的结果指定过滤条件，先分组再过滤4)ORDER BY: 根据指定的字段对查询结果进行排序升序：ASC降序：DESC5)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 sec12.显示其年龄大于平均年龄的同学的名字
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)
字符
数据
类型
字段
年龄
字节
存储
查询
长度
语法
字符串
分组
条件
结果
小数
语句
同学
可变
个数
实际
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
导航数据库多久更新一次
呈贡区创新软件开发咨询报价
无线网络技术感想
通讯软件开发速成方法
除了知网以外的其他数据库
阿里云服务器需要做防护吗
sql数据库管理程序中文正式版
怀柔区什么是网络技术承诺守信
网络技术开发管理制度
天力套接字服务器怎样查找卸载
更新数据库 连接失败
plc不能把数据传到网络服务器
软件开发公司双方解除合同
nba球员每赛季数据库
软件开发公司岗位说明书
数据库表中增加一个字段麻烦吗
二手服务器赶集网
socket并发服务器
nginx流媒体服务器缓存
服务器虚拟化趋势
软件开发行业的风险
方舟生存进化服务器开不了控制台
珠海餐饮软件开发服务费
注册网易账号网络安全隐患
弓箭手大作战服务器崩了
无网络安全会议
软件开发人员的任职资格
4核服务器租
检察院网络安全平台
万方数据库的数据如何下载
