MySQL语句使用。
程序员文章站
2022-05-18 21:14:10
MySQL的DDL、DML、DQL语句和单表增、删、改、查 [toc] 实验准备： 1. 初始化设置过的centos虚拟机 2. 安装MySQL数据库 实验开始： DDL语句 1. 创建和删除数据库 2. 查询数据库字符集 MariaDB [(none)] show create database ......
mysql的ddl、dml、dql语句和单表增、删、改、查
实验准备：
- 初始化设置过的centos虚拟机
- 安装mysql数据库
实验开始：
ddl语句
表：二维关系 设计表：遵循规范 定义：字段，索引 字段：字段名，字段数据类型，修饰符 约束，索引：应该创建在经常用作查询条件的字段上
- 创建和删除数据库
mariadb [(none)]> create database testdb1; (分号为结束符) query ok, 1 row affected (0.00 sec) mariadb [(none)]> show databases; （列出所有数据库） +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | testdb1 | +--------------------+ 4 rows in set (0.00 sec) [root@centos7 ~]#tree /data/mysql/testdb1/ （其实就是在mysql下面建立了一个目录） /data/mysql/testdb1/ └── db.opt [root@centos7 ~]#cat /data/mysql/testdb1/db.opt （也可以查看里面的内容，字符集也可以看到） default-character-set=latin1 default-collation=latin1_swedish_ci 0 directories, 1 file mariadb [testdb1]> drop database testdb1; （删除数据库，同时mysql下面的testdb1目录也会删除） query ok, 1 row affected (0.00 sec) mariadb [(none)]> show databases; +--------------------+ | database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
- 查询数据库字符集
mariadb [(none)]> show create database testdb1; (查看testdb1的数据库字符集)
+----------+--------------------------------------------------------------------+
| database | create database |
+----------+--------------------------------------------------------------------+
| testdb1 | create database `testdb1` /*!40100 default character set latin1 */ | （这里最后的为字符集既latin1这个字符集不支持汉字的。）
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
(这样也可以查看比较好辨认)
mariadb [(none)]> show create database testdb1\g;
*************************** 1. row ***************************
database: testdb1
create database: create database `testdb1` /*!40100 default character set latin1 */
1 row in set (0.00 sec)
mariadb [(none)]> show create database mysql; （查看mysql的数据库字符集）
+----------+------------------------------------------------------------------+
| database | create database |
+----------+------------------------------------------------------------------+
| mysql | create database `mysql` /*!40100 default character set latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
- 修改字符集
mariadb [(none)]> alter database testdb1 character set utf8mb4; (这个字符集支持全球文字还支持表情包) query ok, 1 row affected (0.00 sec) mariadb [(none)]> show create database testdb1; +----------+---------------------------------------------------------------------+ | database | create database | +----------+---------------------------------------------------------------------+ | testdb1 | create database `testdb1` /*!40100 default character set utf8mb4 */ | +----------+---------------------------------------------------------------------+ 1 row in set (0.00 sec) [root@centos7 ~]#cat /data/mysql/testdb1/db.opt (文件也会修改) default-character-set=utf8mb4 default-collation=utf8mb4_general_ci
- 创建一个表做实验
mariadb [(none)]> use testdb1;
database changed
mariadb [testdb1]> create table test ( id int unsigned auto_increment primary key,name varchar(10) not null,mobile char(11) not null );
query ok, 0 rows affected (0.01 sec)
mariadb [testdb1]> desc test; （查看表结构）
+--------+------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | no | pri | null | auto_increment |
| name | varchar(10) | no | | null | |
| mobile | char(11) | no | | null | |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mariadb [hellodb]> show tables; （列出当前库里的所有表）
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
(查看表内容，先要进入存在这个表的数据库) 这个表是我从外部导入进来的没有手工创建，需要的话下次我贴出来。
mariadb [(none)]> use hellodb
database changed
mariadb [hellodb]> select * from 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 | 2 | 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 | 3 | null |
| 8 | lin daiyu | 17 | f | 7 | null |
| 9 | ren yingying | 20 | f | 6 | null |
| 10 | yue lingshan | 19 | f | 3 | null |
| 11 | yuan chengzhi | 23 | m | 6 | null |
| 12 | wen qingqing | 19 | f | 1 | null |
| 13 | tian boguang | 33 | m | 2 | null |
| 14 | lu wushuang | 17 | f | 3 | null |
| 15 | duan yu | 19 | m | 4 | null |
| 16 | xu zhu | 21 | m | 1 | null |
| 17 | lin chong | 25 | m | 4 | null |
| 18 | hua rong | 23 | m | 7 | null |
| 19 | xue baochai | 18 | f | 6 | null |
| 20 | diao chan | 19 | f | 7 | null |
| 21 | huang yueying | 22 | f | 6 | null |
| 22 | xiao qiao | 20 | f | 1 | null |
| 23 | ma chao | 23 | m | 4 | null |
| 24 | xu xian | 27 | m | null | null |
| 25 | sun dasheng | 100 | m | null | null |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
（创建表获取帮助命令）
mariadb [hellodb]> help create table;
name: 'create table'
description:
syntax:
create [temporary] table [if not exists] tbl_name
(create_definition,...)
[table_options]
[partition_options]
or:
create [temporary] table [if not exists] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
or:
create [temporary] table [if not exists] tbl_name
{ like old_tbl_name | (like old_tbl_name) }
- 克隆表
mariadb [hellodb]> create table newstudents select * from students; （通过查询现存表创建；新表会被直接插入查询而来的数据）
query ok, 25 rows affected (0.01 sec)
records: 25 duplicates: 0 warnings: 0
mariadb [hellodb]> show tables;
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| scores |
| students |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
mariadb [hellodb]> select * from newstudents;
+-------+---------------+-----+--------+---------+-----------+
| 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 | 2 | 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 | 3 | null |
| 8 | lin daiyu | 17 | f | 7 | null |
| 9 | ren yingying | 20 | f | 6 | null |
| 10 | yue lingshan | 19 | f | 3 | null |
| 11 | yuan chengzhi | 23 | m | 6 | null |
| 12 | wen qingqing | 19 | f | 1 | null |
| 13 | tian boguang | 33 | m | 2 | null |
| 14 | lu wushuang | 17 | f | 3 | null |
| 15 | duan yu | 19 | m | 4 | null |
| 16 | xu zhu | 21 | m | 1 | null |
| 17 | lin chong | 25 | m | 4 | null |
| 18 | hua rong | 23 | m | 7 | null |
| 19 | xue baochai | 18 | f | 6 | null |
| 20 | diao chan | 19 | f | 7 | null |
| 21 | huang yueying | 22 | f | 6 | null |
| 22 | xiao qiao | 20 | f | 1 | null |
| 23 | ma chao | 23 | m | 4 | null |
| 24 | xu xian | 27 | m | null | null |
| 25 | sun dasheng | 100 | m | null | null |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
（注意：这样不会把主键之类的复制过来，只是复制数据，表结构不一样）如下：
mariadb [hellodb]> desc newstudents;
+-----------+---------------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+-------+
| stuid | int(10) unsigned | no | | 0 | |
| name | varchar(50) | no | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mariadb [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+----------------+
| stuid | int(10) unsigned | no | pri | null | auto_increment |
| name | varchar(50) | no | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
通过复制现存的表的表结构创建，但不复制数据
mariadb [hellodb]> create table newstudents2 like students;
query ok, 0 rows affected (0.01 sec)
mariadb [hellodb]> select * from newstudents2; （这里查看数据是没有的）
empty set (0.00 sec)
mariadb [hellodb]> desc newstudents2; （但是有表结构）
+-----------+---------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+----------------+
| stuid | int(10) unsigned | no | pri | null | auto_increment |
| name | varchar(50) | no | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
- 表查看
查看支持的engine类型：
mariadb [hellodb]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| engine | support | comment | transactions | xa | savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| csv | yes | stores tables as csv files | no | no | no |
| mrg_myisam | yes | collection of identical myisam tables | no | no | no |
| myisam | yes | non-transactional engine with good performance and small data footprint | no | no | no |
| sequence | yes | generated tables filled with sequential values | yes | no | yes |
| performance_schema | yes | performance schema | no | no | no |
| memory | yes | hash based, stored in memory, useful for temporary tables | no | no | no |
| aria | yes | crash-safe tables with myisam heritage | no | no | no |
| innodb | default | supports transactions, row-level locking, foreign keys and encryption for tables | yes | yes | yes |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
查看表：
mariadb [hellodb]> show tables ; （列出当前数据库中的所有表）
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| newstudents2 |
| scores |
| students |
| teachers |
| toc |
+-------------------+
9 rows in set (0.00 sec)
mariadb [hellodb]> show tables from mysql; （指明数据库查看这个数据库的表）
+---------------------------+
| tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
查看表结构：
mariadb [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+----------------+
| stuid | int(10) unsigned | no | pri | null | auto_increment |
| name | varchar(50) | no | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+----------------+
表结构字段的意思。
1. field:字段表示的是别名
2. type:字段表示的是列的数据类型
3. null :字段表示这个列是否能取空值
4. key :在mysql中key 和index 是一样的意思，这个key列可能会看到有如下的值：pri(主键)、mul(普通的b-tree索引)、uni(唯一索引)
5. default: 列的默认值
6. extra :其它信息
查看指定表的创建命令： （既创建过程）
mariadb [hellodb]> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table | create table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | create table `students` (
`stuid` int(10) unsigned not null auto_increment,
`name` varchar(50) not null,
`age` tinyint(3) unsigned not null,
`gender` enum('f','m') not null,
`classid` tinyint(3) unsigned default null,
`teacherid` int(10) unsigned default null,
primary key (`stuid`)
) engine=innodb auto_increment=26 default charset=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看看表的状态：（查看指定某个表状态要加单引号）
mariadb [hellodb]> show table status like 'students'\g;
*************************** 1. row ***************************
name: students
engine: innodb
version: 10
row_format: dynamic
rows: 25
avg_row_length: 655
data_length: 16384
max_data_length: 0
index_length: 0
data_free: 0
auto_increment: 26
create_time: 2019-11-20 21:19:51
update_time: null
check_time: null
collation: utf8_general_ci
checksum: null
create_options:
comment:
1 row in set (0.00 sec)
查看库中所有表的状态：
mariadb [hellodb]> show table status from hellodb\g;
*************************** 1. row ***************************
name: classes
engine: innodb
version: 10
row_format: dynamic
rows: 8
avg_row_length: 2048
data_length: 16384
max_data_length: 0
index_length: 0
data_free: 0
auto_increment: 9
create_time: 2019-11-20 21:19:51
update_time: null
check_time: null
collation: utf8_general_ci
checksum: null
create_options:
comment:
*************************** 2. row ***************************
name: coc
engine: innodb
version: 10
row_format: dynamic
rows: 14
avg_row_length: 1170
data_length: 16384
max_data_length: 0
index_length: 0
data_free: 0
auto_increment: 15
create_time: 2019-11-20 21:19:51
update_time: null
check_time: null
collation: utf8_general_ci
checksum: null
create_options:
comment:
.
.
.(省略证明我不是来混行数的)
.
.
*************************** 8. row ***************************
name: teachers
engine: innodb （这里是使用什么存储引擎的意思）
version: 10
row_format: dynamic
rows: 4
avg_row_length: 4096
data_length: 16384
max_data_length: 0
index_length: 0
data_free: 0
auto_increment: 5
create_time: 2019-11-20 21:19:51
update_time: null
check_time: null
collation: utf8_general_ci
checksum: null
create_options:
comment:
*************************** 9. row ***************************
name: toc
engine: innodb
version: 10
row_format: dynamic
rows: 0
avg_row_length: 0
data_length: 16384
max_data_length: 0
index_length: 0
data_free: 0
auto_increment: 1
create_time: 2019-11-20 21:19:51
update_time: null
check_time: null
collation: utf8_general_ci
checksum: null
create_options:
comment:
9 rows in set (0.00 sec)
- 修改删除表
删除表：
mariadb [hellodb]> show tables;
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| newstudents2 |
| scores |
| students |
| students2 |
| teachers |
| toc |
+-------------------+
10 rows in set (0.00 sec)
mariadb [hellodb]> drop table newstudents2;
query ok, 0 rows affected (0.00 sec)
mariadb [hellodb]> show tables;
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| scores |
| students |
| students2 |
| teachers |
| toc |
+-------------------+
9 rows in set (0.00 sec)
查看修改表帮助：
mariadb [hellodb]> help alter table
name: 'alter table'
description:
syntax:
alter [online | offline] [ignore] table tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
修改表：
mariadb [hellodb]> show tables;
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
mariadb [hellodb]> alter table students rename s1; （修改表名为s1）
query ok, 0 rows affected (0.00 sec)
mariadb [hellodb]> select * from students;
error 1146 (42s02): table 'hellodb.students' doesn't exist
mariadb [hellodb]> show tables;
+-------------------+
| tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| s1 |
| scores |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
mariadb [hellodb]> alter table s1 add phone varchar(11) after name; （在name字段后面添加phone字段） add：添加字段 varchar可变的 字符长度为11为
query ok, 0 rows affected (0.01 sec)
records: 0 duplicates: 0 warnings: 0
mariadb [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 3 |
| 2 | shi potian | null | 22 | m | 1 | 7 |
| 3 | xie yanke | null | 53 | m | 2 | 16 |
| 4 | ding dian | null | 32 | m | 4 | 4 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | null |
| 7 | xi ren | null | 19 | f | 3 | null |
| 8 | lin daiyu | null | 17 | f | 7 | null |
| 9 | ren yingying | null | 20 | f | 6 | null |
| 10 | yue lingshan | null | 19 | f | 3 | null |
| 11 | yuan chengzhi | null | 23 | m | 6 | null |
| 12 | wen qingqing | null | 19 | f | 1 | null |
| 13 | tian boguang | null | 33 | m | 2 | null |
| 14 | lu wushuang | null | 17 | f | 3 | null |
| 15 | duan yu | null | 19 | m | 4 | null |
| 16 | xu zhu | null | 21 | m | 1 | null |
| 17 | lin chong | null | 25 | m | 4 | null |
| 18 | hua rong | null | 23 | m | 7 | null |
| 19 | xue baochai | null | 18 | f | 6 | null |
| 20 | diao chan | null | 19 | f | 7 | null |
| 21 | huang yueying | null | 22 | f | 6 | null |
| 22 | xiao qiao | null | 20 | f | 1 | null |
| 23 | ma chao | null | 23 | m | 4 | null |
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
+-------+---------------+-------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
mariadb [hellodb]> alter table s1 modify phone int; （把phone的数据类型改为int）
query ok, 25 rows affected (0.01 sec)
records: 25 duplicates: 0 warnings: 0
mariadb [hellodb]> desc s1
-> ;
+-----------+---------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+----------------+
| stuid | int(10) unsigned | no | pri | null | auto_increment |
| name | varchar(50) | no | | null | |
| phone | int(11) | yes | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
（这些就不一一解释了有兴趣的可以自己试一试）
alter table s1 change column phone mobile char(11);
alter table s1 drop column mobile;
alter table s1 character set utf8;
alter table s1 change name name varchar(20) character set utf8;
alter table students add gender enum('m','f');
aletr table students change id sid int unsigned not null primary key;
alter table students drop primary key ;
alter table students drop primary key ;
desc students;
alter table students drop age;
dml语句
dml: insert, delete, update insert 语句 功能：一次插入一行或多行数据 语法 update 语句 语句用于修改表中的数据。 注意：一定要有限制条件，否则将修改所有行的指定字段 delete语句 语句用于删除表中的行。 注意：一定要有限制条件，否则将清空表中的所有数据 可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的： delete from table_name 或者 delete * from table_name
- insert语句实验: 三种语句
mariadb [hellodb]> help insert;
name: 'insert'
description:
syntax:
insert [low_priority | delayed | high_priority] [ignore]
[into] tbl_name [(col_name,...)]
{values | value} ({expr | default},...),(...),...
[ on duplicate key update
col_name=expr
[, col_name=expr] ... ]
or:
insert [low_priority | delayed | high_priority] [ignore]
[into] tbl_name
set col_name={expr | default}, ...
[ on duplicate key update
col_name=expr
[, col_name=expr] ... ]
or:
insert [low_priority | high_priority] [ignore]
[into] tbl_name [(col_name,...)]
select ...
[ on duplicate key update
col_name=expr
[, col_name=expr] ... ]
- 第一种语法
mariadb [hellodb]> insert into s1 values(26,'xietingfeng',null,23,'m',2,1); (添加一行表记录)
query ok, 1 row affected (0.00 sec)
mariadb [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 3 |
| 2 | shi potian | null | 22 | m | 1 | 7 |
| 3 | xie yanke | null | 53 | m | 2 | 16 |
| 4 | ding dian | null | 32 | m | 4 | 4 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | null |
| 7 | xi ren | null | 19 | f | 3 | null |
| 8 | lin daiyu | null | 17 | f | 7 | null |
| 9 | ren yingying | null | 20 | f | 6 | null |
| 10 | yue lingshan | null | 19 | f | 3 | null |
| 11 | yuan chengzhi | null | 23 | m | 6 | null |
| 12 | wen qingqing | null | 19 | f | 1 | null |
| 13 | tian boguang | null | 33 | m | 2 | null |
| 14 | lu wushuang | null | 17 | f | 3 | null |
| 15 | duan yu | null | 19 | m | 4 | null |
| 16 | xu zhu | null | 21 | m | 1 | null |
| 17 | lin chong | null | 25 | m | 4 | null |
| 18 | hua rong | null | 23 | m | 7 | null |
| 19 | xue baochai | null | 18 | f | 6 | null |
| 20 | diao chan | null | 19 | f | 7 | null |
| 21 | huang yueying | null | 22 | f | 6 | null |
| 22 | xiao qiao | null | 20 | f | 1 | null |
| 23 | ma chao | null | 23 | m | 4 | null |
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
（如果出现这个提示可能是你少写了一个字段或者写错了）
mariadb [hellodb]> insert into s1 values(26,'xietingfeng',23,'m',2,1);
error 1136 (21s01): column count doesn't match value count at row 1
注意：如果想省略字段名的话，只能所有字段都赋值的话可以省略，单个赋值不可以省略。
如下：挑出三个字段来赋值，必须对应的写上。
mariadb [hellodb]> insert into s1(name,age,classid) values ('liudehua',18,1);
query ok, 1 row affected (0.00 sec)
mariadb [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 3 |
| 2 | shi potian | null | 22 | m | 1 | 7 |
| 3 | xie yanke | null | 53 | m | 2 | 16 |
| 4 | ding dian | null | 32 | m | 4 | 4 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | null |
| 7 | xi ren | null | 19 | f | 3 | null |
| 8 | lin daiyu | null | 17 | f | 7 | null |
| 9 | ren yingying | null | 20 | f | 6 | null |
| 10 | yue lingshan | null | 19 | f | 3 | null |
| 11 | yuan chengzhi | null | 23 | m | 6 | null |
| 12 | wen qingqing | null | 19 | f | 1 | null |
| 13 | tian boguang | null | 33 | m | 2 | null |
| 14 | lu wushuang | null | 17 | f | 3 | null |
| 15 | duan yu | null | 19 | m | 4 | null |
| 16 | xu zhu | null | 21 | m | 1 | null |
| 17 | lin chong | null | 25 | m | 4 | null |
| 18 | hua rong | null | 23 | m | 7 | null |
| 19 | xue baochai | null | 18 | f | 6 | null |
| 20 | diao chan | null | 19 | f | 7 | null |
| 21 | huang yueying | null | 22 | f | 6 | null |
| 22 | xiao qiao | null | 20 | f | 1 | null |
| 23 | ma chao | null | 23 | m | 4 | null |
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | null |
+-------+---------------+-------+-----+--------+---------+-----------+
27 rows in set (0.00 sec) （↑ 这里是因为表结构设置默认不允许为空随机分配的值）
一次行两条记录赋值：
mariadb [hellodb]> insert into s1(name,age,gender,classid) values('mahuateng',20,'m',3),('wuyanzu',19,'m',4);
query ok, 2 rows affected (0.00 sec)
records: 2 duplicates: 0 warnings: 0
mariadb [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 3 |
| 2 | shi potian | null | 22 | m | 1 | 7 |
| 3 | xie yanke | null | 53 | m | 2 | 16 |
| 4 | ding dian | null | 32 | m | 4 | 4 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | null |
| 7 | xi ren | null | 19 | f | 3 | null |
| 8 | lin daiyu | null | 17 | f | 7 | null |
| 9 | ren yingying | null | 20 | f | 6 | null |
| 10 | yue lingshan | null | 19 | f | 3 | null |
| 11 | yuan chengzhi | null | 23 | m | 6 | null |
| 12 | wen qingqing | null | 19 | f | 1 | null |
| 13 | tian boguang | null | 33 | m | 2 | null |
| 14 | lu wushuang | null | 17 | f | 3 | null |
| 15 | duan yu | null | 19 | m | 4 | null |
| 16 | xu zhu | null | 21 | m | 1 | null |
| 17 | lin chong | null | 25 | m | 4 | null |
| 18 | hua rong | null | 23 | m | 7 | null |
| 19 | xue baochai | null | 18 | f | 6 | null |
| 20 | diao chan | null | 19 | f | 7 | null |
| 21 | huang yueying | null | 22 | f | 6 | null |
| 22 | xiao qiao | null | 20 | f | 1 | null |
| 23 | ma chao | null | 23 | m | 4 | null |
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | null |
| 28 | mahuateng | null | 20 | m | 3 | null |
| 29 | wuyanzu | null | 19 | m | 4 | null |
+-------+---------------+-------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)
- 第二种语法
mariadb [hellodb]> insert s1 set name='wuzetian',age=21,gender='f'; query ok, 1 row affected (0.00 sec) mariadb [hellodb]> select * from s1; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 6 | shi qing | null | 46 | m | 5 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 24 | xu xian | null | 27 | m | null | null | | 25 | sun dasheng | null | 100 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 27 | liudehua | null | 18 | f | 1 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 30 rows in set (0.00 sec) (使用的不多一般使用第一种了解一下)
- 第三种语法
mariadb [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | tid | name | age | gender | +-----+---------------+-----+--------+ | 1 | song jiang | 45 | m | | 2 | zhang sanfeng | 94 | m | | 3 | miejue shitai | 77 | f | | 4 | lin chaoying | 93 | f | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) mariadb [hellodb]> insert s1 (name,classid,gender,age) select name,age,gender,18 from teachers; query ok, 4 rows affected (0.00 sec) records: 4 duplicates: 0 warnings: 0 mariadb [hellodb]> select * from s1; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 6 | shi qing | null | 46 | m | 5 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 24 | xu xian | null | 27 | m | null | null | | 25 | sun dasheng | null | 100 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 27 | liudehua | null | 18 | f | 1 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 30 | wuzetian | null | 21 | f | null | null | | 31 | song jiang | null | 18 | m | 45 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 34 rows in set (0.00 sec) （批量导入，a表参考b表的结构导入到b表中，主键必须是唯一的不可导入a表的主键）
- update语句
mariadb [hellodb]> help update; (语句帮助)
name: 'update'
description:
syntax:
single-table syntax:
update [low_priority] [ignore] table_reference
set col_name1={expr1|default} [, col_name2={expr2|default}] ...
[where where_condition]
[order by ...]
[limit row_count]
mariadb [hellodb]> select * from n1; （不加指定修改会把整个列都修改）
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 3 |
| 2 | shi potian | null | 22 | m | 1 | 7 |
| 3 | xie yanke | null | 53 | m | 2 | 16 |
| 4 | ding dian | null | 32 | m | 4 | 4 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | null |
| 7 | xi ren | null | 19 | f | 3 | null |
| 8 | lin daiyu | null | 17 | f | 7 | null |
| 9 | ren yingying | null | 20 | f | 6 | null |
| 10 | yue lingshan | null | 19 | f | 3 | null |
| 11 | yuan chengzhi | null | 23 | m | 6 | null |
| 12 | wen qingqing | null | 19 | f | 1 | null |
| 13 | tian boguang | null | 33 | m | 2 | null |
| 14 | lu wushuang | null | 17 | f | 3 | null |
| 15 | duan yu | null | 19 | m | 4 | null |
| 16 | xu zhu | null | 21 | m | 1 | null |
| 17 | lin chong | null | 25 | m | 4 | null |
| 18 | hua rong | null | 23 | m | 7 | null |
| 19 | xue baochai | null | 18 | f | 6 | null |
| 20 | diao chan | null | 19 | f | 7 | null |
| 21 | huang yueying | null | 22 | f | 6 | null |
| 22 | xiao qiao | null | 20 | f | 1 | null |
| 23 | ma chao | null | 23 | m | 4 | null |
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | null |
| 28 | mahuateng | null | 20 | m | 3 | null |
| 29 | wuyanzu | null | 19 | m | 4 | null |
| 30 | wuzetian | null | 21 | f | null | null |
| 31 | song jiang | null | 18 | m | 45 | null |
| 32 | zhang sanfeng | null | 18 | m | 94 | null |
| 33 | miejue shitai | null | 18 | f | 77 | null |
| 34 | lin chaoying | null | 18 | f | 93 | null |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
mariadb [hellodb]> update n1 set teacherid=1;
query ok, 32 rows affected (0.00 sec)
rows matched: 34 changed: 32 warnings: 0
mariadb [hellodb]> select * from n1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 1 |
| 2 | shi potian | null | 22 | m | 1 | 1 |
| 3 | xie yanke | null | 53 | m | 2 | 1 |
| 4 | ding dian | null | 32 | m | 4 | 1 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | 1 |
| 7 | xi ren | null | 19 | f | 3 | 1 |
| 8 | lin daiyu | null | 17 | f | 7 | 1 |
| 9 | ren yingying | null | 20 | f | 6 | 1 |
| 10 | yue lingshan | null | 19 | f | 3 | 1 |
| 11 | yuan chengzhi | null | 23 | m | 6 | 1 |
| 12 | wen qingqing | null | 19 | f | 1 | 1 |
| 13 | tian boguang | null | 33 | m | 2 | 1 |
| 14 | lu wushuang | null | 17 | f | 3 | 1 |
| 15 | duan yu | null | 19 | m | 4 | 1 |
| 16 | xu zhu | null | 21 | m | 1 | 1 |
| 17 | lin chong | null | 25 | m | 4 | 1 |
| 18 | hua rong | null | 23 | m | 7 | 1 |
| 19 | xue baochai | null | 18 | f | 6 | 1 |
| 20 | diao chan | null | 19 | f | 7 | 1 |
| 21 | huang yueying | null | 22 | f | 6 | 1 |
| 22 | xiao qiao | null | 20 | f | 1 | 1 |
| 23 | ma chao | null | 23 | m | 4 | 1 |
| 24 | xu xian | null | 27 | m | null | 1 |
| 25 | sun dasheng | null | 100 | m | null | 1 |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | 1 |
| 28 | mahuateng | null | 20 | m | 3 | 1 |
| 29 | wuyanzu | null | 19 | m | 4 | 1 |
| 30 | wuzetian | null | 21 | f | null | 1 |
| 31 | song jiang | null | 18 | m | 45 | 1 |
| 32 | zhang sanfeng | null | 18 | m | 94 | 1 |
| 33 | miejue shitai | null | 18 | f | 77 | 1 |
| 34 | lin chaoying | null | 18 | f | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
避免的方法：登陆的时候加-u进入安全模式，改模式下不加where指定不能更改。
[root@centos7 ~]#mysql -u -uroot -p123456
welcome to the mariadb monitor. commands end with ; or \g.
your mariadb connection id is 11
server version: 10.2.29-mariadb-log mariadb server
copyright (c) 2000, 2018, oracle, mariadb corporation ab and others.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
mariadb [(none)]> update n1 set teacherid=2;
error 1046 (3d000): no database selected
mariadb [(none)]>
或者修改配文件： 在mysql下面加上这一行
[root@centos7 ~]#vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-updates
mariadb [hellodb]> update n1 set teacherid=2 where stuid=25; （用where指定主键的某一范围修改）
query ok, 1 row affected (0.00 sec)
rows matched: 1 changed: 1 warnings: 0
mariadb [hellodb]> select * from n1
-> ;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | shi zhongyu | null | 22 | m | 2 | 1 |
| 2 | shi potian | null | 22 | m | 1 | 1 |
| 3 | xie yanke | null | 53 | m | 2 | 1 |
| 4 | ding dian | null | 32 | m | 4 | 1 |
| 5 | yu yutong | null | 26 | m | 3 | 1 |
| 6 | shi qing | null | 46 | m | 5 | 1 |
| 7 | xi ren | null | 19 | f | 3 | 1 |
| 8 | lin daiyu | null | 17 | f | 7 | 1 |
| 9 | ren yingying | null | 20 | f | 6 | 1 |
| 10 | yue lingshan | null | 19 | f | 3 | 1 |
| 11 | yuan chengzhi | null | 23 | m | 6 | 1 |
| 12 | wen qingqing | null | 19 | f | 1 | 1 |
| 13 | tian boguang | null | 33 | m | 2 | 1 |
| 14 | lu wushuang | null | 17 | f | 3 | 1 |
| 15 | duan yu | null | 19 | m | 4 | 1 |
| 16 | xu zhu | null | 21 | m | 1 | 1 |
| 17 | lin chong | null | 25 | m | 4 | 1 |
| 18 | hua rong | null | 23 | m | 7 | 1 |
| 19 | xue baochai | null | 18 | f | 6 | 1 |
| 20 | diao chan | null | 19 | f | 7 | 1 |
| 21 | huang yueying | null | 22 | f | 6 | 1 |
| 22 | xiao qiao | null | 20 | f | 1 | 1 |
| 23 | ma chao | null | 23 | m | 4 | 1 |
| 24 | xu xian | null | 27 | m | null | 1 |
| 25 | sun dasheng | null | 100 | m | null | 2 |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | 1 |
| 28 | mahuateng | null | 20 | m | 3 | 1 |
| 29 | wuyanzu | null | 19 | m | 4 | 1 |
| 30 | wuzetian | null | 21 | f | null | 1 |
| 31 | song jiang | null | 18 | m | 45 | 1 |
| 32 | zhang sanfeng | null | 18 | m | 94 | 1 |
| 33 | miejue shitai | null | 18 | f | 77 | 1 |
| 34 | lin chaoying | null | 18 | f | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
注意：安全模式下，这样修改表必须有主键才可以修改。
添加主键方法：
mariadb [hellodb]> alter table n1 change stuid stuid int primary key;
- delete语句：（也要加where） （-u 和配置文件修改都支持）
mariadb [hellodb]> delete from n1 where stuid=28; （删除这一行）
query ok, 1 row affected (0.00 sec)
mariadb [hellodb]> select * from n1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+---------------+-------+-----+--------+---------+-----------+
| 24 | xu xian | null | 27 | m | null | 1 |
| 25 | sun dasheng | null | 100 | m | null | 2 |
| 26 | xietingfeng | null | 23 | m | 2 | 1 |
| 27 | liudehua | null | 18 | f | 1 | 1 |
| 29 | wuyanzu | null | 19 | m | 4 | 1 |
| 30 | wuzetian | null | 21 | f | null | 1 |
| 31 | song jiang | null | 18 | m | 45 | 1 |
| 32 | zhang sanfeng | null | 18 | m | 94 | 1 |
| 33 | miejue shitai | null | 18 | f | 77 | 1 |
| 34 | lin chaoying | null | 18 | f | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
33 rows in set (0.00 sec)
(第二种清空数据保留表结构的方式)
mariadb [hellodb]> delete from n1;
query ok, 33 rows affected (0.00 sec)
mariadb [hellodb]> select * from n1;
empty set (0.00 sec)
mariadb [hellodb]> desc n1; （表结构还在）
+-----------+---------------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-----------+---------------------+------+-----+---------+-------+
| stuid | int(11) | no | pri | null | |
| name | varchar(50) | no | | null | |
| phone | int(11) | yes | | null | |
| age | tinyint(3) unsigned | no | | null | |
| gender | enum('f','m') | no | | null | |
| classid | tinyint(3) unsigned | yes | | null | |
| teacherid | int(10) unsigned | yes | | null | |
+-----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
dql语句
查询语句：select （单表和多表）
单表操作
语法：
select
[all | distinct | distinctrow ]
[sql_cache | sql_no_cache]
select_expr [, select_expr ...]
[from table_references
[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}]
[for update | lock in share mode]
说明：
- 字段显示可以使用别名： col1 as alias1, col2 as alias2, ...
- where子句：指明过滤条件以实现“选择”的功能： 过滤条件：布尔型表达式 算术操作符：+, -, *,
/, % 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准sql), >, >=, <, <= between min_num
and max_num in (element1, element2, ...) is null is not null select distinct 去除重复
列，范例：select distinct gender from students; like: % 任意长度的任意字符 _ 任意单个字
符 rlike：正则表达式，索引失效，不建议使用 regexp：匹配字符串可用正则表达式书写模式，
同上 逻辑操作符：not，and，or，xor - group：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 常见聚合函数：avg(), max(),
min(), count(), sum() having: 对分组聚合运算后的结果指定过滤条件 - order by: 根据指定的字段对查询结果进行排序 升序：asc 降序：desc
- limit [[offset,]row_count]：对查询的结果进行输出行数数量限制
- 对查询结果中的数据请求施加“锁” for update: 写锁，独占或排它锁，只有一个读和写操作
lock in share mode: 读锁，共享锁，同时多个读操作
范例：
truncate table tbl_name;
select
[all | distinct | distinctrow ]
[sql_cache | sql_no_cache]
select_expr [, select_expr ...]
[from table_references
[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}]
[for update | lock in share mode]
desc students;
insert into students values(1,'tom'，'m'),(2,'alice','f');
insert into students(id,name) values(3,'jack'),(4,'allen');
select * from students where id < 3;
select * from students where gender='m';
select * from students where gender is null;
select * from students where gender is not null;
select * from students order by name desc limit 2;
select * from students order by name desc limit 1,2;
select * from students where id >=2 and id <=4
select * from students where between 2 and 4
select * from students where name like ‘t%’
select * from students where name rlike '.*[lo].*';
select id stuid,name as stuname from students
- 单表实验：
- 展示表里的所有内容*代表所有字段，也可以挑指定字段来显示。
mariadb [hellodb]> select * from s1; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 6 | shi qing | null | 46 | m | 5 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | .... 省略掉 +-------+---------------+-------+-----+--------+---------+-----------+ 34 rows in set (0.00 sec)
- 甚至可以做数字运算
mariadb [hellodb]> select 2*3; +-----+ | 2*3 | +-----+ | 6 | +-----+ 1 row in set (0.01 sec) mariadb [hellodb]> select 2*3 from teachers; (指定一个表，表里有几行做几次数字运算和awk很相似) +-----+ | 2*3 | +-----+ | 6 | | 6 | | 6 | | 6 | +-----+ 4 rows in set (0.00 sec) mariadb [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | tid | name | age | gender | +-----+---------------+-----+--------+ | 1 | song jiang | 45 | m | | 2 | zhang sanfeng | 94 | m | | 3 | miejue shitai | 77 | f | | 4 | lin chaoying | 93 | f | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec)
- 指定字段名更改 加*的话不行
mariadb [hellodb]> select '韦小宝',name from teachers; +-----------+---------------+ | 韦小宝 | name | +-----------+---------------+ | 韦小宝 | song jiang | | 韦小宝 | zhang sanfeng | | 韦小宝 | miejue shitai | | 韦小宝 | lin chaoying | +-----------+---------------+ 4 rows in set (0.00 sec) mariadb [hellodb]> select '韦小宝',name,age,gender from teachers; +-----------+---------------+-----+--------+ | 韦小宝 | name | age | gender | +-----------+---------------+-----+--------+ | 韦小宝 | song jiang | 45 | m | | 韦小宝 | zhang sanfeng | 94 | m | | 韦小宝 | miejue shitai | 77 | f | | 韦小宝 | lin chaoying | 93 | f | +-----------+---------------+-----+--------+ 4 rows in set (0.00 sec) (只是显示效果更改) mariadb [hellodb]> select * from teachers; +-----+---------------+-----+--------+ | tid | name | age | gender | +-----+---------------+-----+--------+ | 1 | song jiang | 45 | m | | 2 | zhang sanfeng | 94 | m | | 3 | miejue shitai | 77 | f | | 4 | lin chaoying | 93 | f | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec)
- 对表头起别名显示
mariadb [hellodb]> select '韦小宝',name as "姓名",age "年龄",gender from teachers; +-----------+---------------+--------+--------+ | 韦小宝 | 姓名 | 年龄 | gender | +-----------+---------------+--------+--------+ | 韦小宝 | song jiang | 45 | m | | 韦小宝 | zhang sanfeng | 94 | m | | 韦小宝 | miejue shitai | 77 | f | | 韦小宝 | lin chaoying | 93 | f | +-----------+---------------+--------+--------+ 4 rows in set (0.01 sec)
- 行过滤：挑出性别不是m的 !=是非标准的sql语法 标准的为<>
mariadb [hellodb]> select * from s1 where gender != 'm'; (只能在mysql数据库使用) +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 30 | wuzetian | null | 21 | f | null | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 14 rows in set (0.00 sec) mariadb [hellodb]> select * from s1 where gender <> 'm'; （所有sql数据库都可以使用） +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 30 | wuzetian | null | 21 | f | null | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 14 rows in set (0.00 sec)
- 挑出大于30的
mariadb [hellodb]> select * from s1 where age >=30; +-------+--------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+--------------+-------+-----+--------+---------+-----------+ | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 6 | shi qing | null | 46 | m | 5 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 25 | sun dasheng | null | 100 | m | null | null | +-------+--------------+-------+-----+--------+---------+-----------+ 5 rows in set (0.00 sec)
- 挑出20到30之间的
mariadb [hellodb]> select * from s1 where age >=20 and age <=30; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 9 | ren yingying | null | 20 | f | 6 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 24 | xu xian | null | 27 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 28 | mahuateng | null | 20 | m | 3 | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec) mariadb [hellodb]> select * from s1 where age between 20 and 30; (结果等价于上面的) +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 9 | ren yingying | null | 20 | f | 6 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 24 | xu xian | null | 27 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 28 | mahuateng | null | 20 | m | 3 | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec)
- 挑出大于30小于20的
mariadb [hellodb]> select * from s1 where age >=30 or age <=20; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 6 | shi qing | null | 46 | m | 5 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 25 | sun dasheng | null | 100 | m | null | null | | 27 | liudehua | null | 18 | f | 1 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 22 rows in set (0.00 sec)
- 挑出1班，3班，6班的人
mariadb [hellodb]> select * from s1 where classid in (1,3,6); +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 2 | shi potian | null | 22 | m | 1 | 7 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 7 | xi ren | null | 19 | f | 3 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 28 | mahuateng | null | 20 | m | 3 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 14 rows in set (0.00 sec)
- 查看班级id为空的
mariadb [hellodb]> select * from s1 where classid is null; +-------+-------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+-------------+-------+-----+--------+---------+-----------+ | 24 | xu xian | null | 27 | m | null | null | | 25 | sun dasheng | null | 100 | m | null | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+-------------+-------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec)
- 查看班级id不为空的
mariadb [hellodb]> select * from s1 where classid is not null; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 6 | shi qing | null | 46 | m | 5 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 27 | liudehua | null | 18 | f | 1 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 31 rows in set (0.00 sec)
- 添加空值必须指定为null
mariadb [hellodb]> insert students (name,age,gender,classid)values('abc',20,'m',null);
query ok, 1 row affected (0.00 sec)
mariadb [hellodb]> select * from s1 where classid is null;
+-------+-------------+-------+-----+--------+---------+-----------+
| stuid | name | phone | age | gender | classid | teacherid |
+-------+-------------+-------+-----+--------+---------+-----------+
| 24 | xu xian | null | 27 | m | null | null |
| 25 | sun dasheng | null | 100 | m | null | null |
| 30 | wuzetian | null | 21 | f | null | null |
| 38 | abc | null | 20 | m | null | null |
+-------+-------------+-------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
- 去除calssid重复的列
mariadb [hellodb]> select distinct classid from s1; +---------+ | classid | +---------+ | 2 | | 1 | | 4 | | 3 | | 5 | | 7 | | 6 | | null | | 45 | | 94 | | 77 | | 93 | +---------+ 12 rows in set (0.00 sec)
- where语句模糊搜索匹配w开头的行 （like）
mariadb [hellodb]> select * from s1 where name like 'w%'; +-------+--------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+--------------+-------+-----+--------+---------+-----------+ | 12 | wen qingqing | null | 19 | f | 1 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+--------------+-------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec) mariadb [hellodb]> select * from s1 where name like '%w%'; (这个显示为包含w的行) +-------+--------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+--------------+-------+-----+--------+---------+-----------+ | 12 | wen qingqing | null | 19 | f | 1 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 30 | wuzetian | null | 21 | f | null | null | +-------+--------------+-------+-----+--------+---------+-----------+ 4 rows in set (0.00 sec)
- 查看字符串数量为三的：用三个下划线来表示
mariadb [hellodb]> select * from s1 where name like '___'; +-------+------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+------+-------+-----+--------+---------+-----------+ | 38 | abc | null | 20 | m | null | null | +-------+------+-------+-----+--------+---------+-----------+ 1 row in set (0.00 sec)
- rlike '^#'等价于like '#%'
mariadb [hellodb]> select * from s1 where name rlike '^x'; +-------+-------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+-------------+-------+-----+--------+---------+-----------+ | 3 | xie yanke | null | 53 | m | 2 | 16 | | 7 | xi ren | null | 19 | f | 3 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 24 | xu xian | null | 27 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | +-------+-------------+-------+-----+--------+---------+-----------+ 7 rows in set (0.00 sec) mariadb [hellodb]> select * from s1 where name like 'x%'; +-------+-------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+-------------+-------+-----+--------+---------+-----------+ | 3 | xie yanke | null | 53 | m | 2 | 16 | | 7 | xi ren | null | 19 | f | 3 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 24 | xu xian | null | 27 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | +-------+-------------+-------+-----+--------+---------+-----------+ 7 rows in set (0.00 sec)
- 建立一个表存储用户和密码
mariadb [hellodb]> create table user (id int primary key,name varchar(10) not null,password varchar(30) not null); query ok, 0 rows affected (0.00 sec) mariadb [hellodb]> insert user value(1,'alice','centos'); query ok, 1 row affected (0.00 sec) mariadb [hellodb]> insert user value(2,'bob','linux'); query ok, 1 row affected (0.00 sec) mariadb [hellodb]> select * from user; +----+-------+----------+ | id | name | password | +----+-------+----------+ | 1 | alice | centos | | 2 | bob | linux | +----+-------+----------+ 2 rows in set (0.00 sec)
- 使用sql注入（sql注入漏洞）
mariadb [hellodb]> select * from user where name='alice' and password='tuituibang'; (密码不对应返回空值) empty set (0.00 sec) mariadb [hellodb]> select * from user where name='alice'; -- ' and password='tuituibang'; （使用特殊符号密码不对应也可以查看） +----+-------+----------+ | id | name | password | +----+-------+----------+ | 1 | alice | centos | +----+-------+----------+ 1 row in set (0.00 sec) 构建一个复杂密码用户结果也一样 mariadb [hellodb]> insert user values(3,'admin','p@ssw)rd!'); query ok, 1 row affected (0.00 sec) mariadb [hellodb]> select * from user; +----+-------+-----------+ | id | name | password | +----+-------+-----------+ | 1 | alice | centos | | 2 | bob | linux | | 3 | admin | p@ssw)rd! | +----+-------+-----------+ 3 rows in set (0.00 sec) mariadb [hellodb]> select * from user where name='admin'; -- ' and password='tuituibang'; （这里的引号数量是单数） +----+-------+-----------+ | id | name | password | +----+-------+-----------+ | 3 | admin | p@ssw)rd! | +----+-------+-----------+ 1 row in set (0.00 sec) 在登陆的时候构建一个奇怪的密码也可以登录 （注意这里的引号数量必须是成对的） mariadb [hellodb]> select * from user where name='admin' and password='' or '1=1'; +----+-------+-----------+ | id | name | password | +----+-------+-----------+ | 1 | alice | centos | | 2 | bob | linux | | 3 | admin | p@ssw)rd! | +----+-------+-----------+ 3 rows in set, 5 warnings (0.00 sec) mariadb [hellodb]> select * from user where name='admin' and password='' or '1'='1'; +----+-------+-----------+ | id | name | password | +----+-------+-----------+ | 1 | alice | centos | | 2 | bob | linux | | 3 | admin | p@ssw)rd! | +----+-------+-----------+ 3 rows in set (0.00 sec)
单表操作的分组统计
- sql的聚合函数
avg(column) 返回某列的平均值 binary_checksum checksum checksum_agg count(column) 返回某列的行数（不包括null值） count(*) 返回被选行数（函数返回在给定的选择中被选的行数） count(distinct column) 返回相异结果的数目 first(column) 返回在指定的域中第一个记录的值（sqlserver2000 不支持） last(column) 返回在指定的域中最后一个记录的值（sqlserver2000 不支持） max(column) 返回某列的最高值 min(column) 返回某列的最低值 stdev(column) stdevp(column) sum(column) 返回某列的总和 var(column) varp(column)
- count 统计列的行数
mariadb [hellodb]> select count(stuid) from s1; +--------------+ | count(stuid) | +--------------+ | 35 | +--------------+ 1 row in set (0.00 sec) mariadb [hellodb]> select count(classid) from s1; +----------------+ | count(classid) | +----------------+ | 31 | （有空值不统计） +----------------+ 1 row in set (0.00 sec) mariadb [hellodb]> select count(1) from s1; （跟count（主键）一样，只扫描主键） +----------+ | count(1) | +----------+ | 35 | +----------+ 1 row in set (0.00 sec) mariadb [hellodb]> select count(*) from s1; (和count（主键）使用方式一样，但是在性能上有略微的区别，mysql对前者做了优化。) +----------+ | count(*) | +----------+ | 35 | +----------+ 1 row in set (0.00 sec)
- 使用group by （根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 常见聚合函数）统计男生女生分别多少个
mariadb [hellodb]> select gender,count(*) from s1 group by gender ; +--------+----------+ | gender | count(*) | +--------+----------+ | f | 14 | | m | 21 | +--------+----------+ 2 rows in set (0.00 sec) (起个别名) mariadb [hellodb]> select gender 性别,count(*) 人数 from s1 group by gender ; +--------+--------+ | 性别 | 人数 | +--------+--------+ | f | 14 | | m | 21 | +--------+--------+ 2 rows in set (0.00 sec)
- avg 统计平均年龄
mariadb [hellodb]> select gender,avg(age) from s1 group by gender; +--------+----------+ | gender | avg(age) | +--------+----------+ | f | 18.9286 | | m | 29.1905 | +--------+----------+ 2 rows in set (0.00 sec)
- max 统计男女生最大的年龄
mariadb [hellodb]> select gender,avg(age),max(age) from s1 group by gender; +--------+----------+----------+ | gender | avg(age) | max(age) | +--------+----------+----------+ | f | 18.9286 | 22 | | m | 29.1905 | 100 | +--------+----------+----------+ 2 rows in set (0.00 sec)
- 分组之后过滤行的话不能加where 要加having (在group by后面使用)
mariadb [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender; +---------+--------+----------+ (过滤掉空的行) | classid | gender | avg(age) | +---------+--------+----------+ | null | f | 21.0000 | | null | m | 49.0000 | | 1 | f | 19.0000 | | 1 | m | 21.5000 | | 2 | m | 32.7500 | | 3 | f | 18.3333 | | 3 | m | 23.0000 | | 4 | m | 23.6000 | | 5 | m | 46.0000 | | 6 | f | 20.0000 | | 6 | m | 23.0000 | | 7 | f | 18.0000 | | 7 | m | 23.0000 | | 45 | m | 18.0000 | | 77 | f | 18.0000 | | 93 | f | 18.0000 | | 94 | m | 18.0000 | +---------+--------+----------+ 17 rows in set (0.00 sec) mariadb [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender where classid is not null; error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mariadb server version for the right syntax to use near 'where classid is not null' at line 1 mariadb [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender having classid is not null; （having: 对分组聚合运算后的结果指定过滤条件） +---------+--------+----------+ | classid | gender | avg(age) | +---------+--------+----------+ | 1 | f | 19.0000 | | 1 | m | 21.5000 | | 2 | m | 32.7500 | | 3 | f | 18.3333 | | 3 | m | 23.0000 | | 4 | m | 23.6000 | | 5 | m | 46.0000 | | 6 | f | 20.0000 | | 6 | m | 23.0000 | | 7 | f | 18.0000 | | 7 | m | 23.0000 | | 45 | m | 18.0000 | | 77 | f | 18.0000 | | 93 | f | 18.0000 | | 94 | m | 18.0000 | +---------+--------+----------+ 15 rows in set (0.00 sec)
- 分组前可以使用where过滤 （在group by之前添加）
mariadb [hellodb]> select classid,gender,avg(age) from s1 where classid is not null group by classid,gender; +---------+--------+----------+ | classid | gender | avg(age) | +---------+--------+----------+ | 1 | f | 19.0000 | | 1 | m | 21.5000 | | 2 | m | 32.7500 | | 3 | f | 18.3333 | | 3 | m | 23.0000 | | 4 | m | 23.6000 | | 5 | m | 46.0000 | | 6 | f | 20.0000 | | 6 | m | 23.0000 | | 7 | f | 18.0000 | | 7 | m | 23.0000 | | 45 | m | 18.0000 | | 77 | f | 18.0000 | | 93 | f | 18.0000 | | 94 | m | 18.0000 | +---------+--------+----------+ 15 rows in set (0.00 sec)
- order by 排序 单字段排序：默认按照字母正序排序
mariadb [hellodb]> select * from s1 order by name; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 38 | abc | null | 20 | m | null | null | | 20 | diao chan | null | 19 | f | 7 | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 15 | duan yu | null | 19 | m | 4 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 2 | shi potian | null | 22 | m | 1 | 7 | | 6 | shi qing | null | 46 | m | 5 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 31 | song jiang | null | 18 | m | 45 | null | | 25 | sun dasheng | null | 100 | m | null | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 30 | wuzetian | null | 21 | f | null | null | | 7 | xi ren | null | 19 | f | 3 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 24 | xu xian | null | 27 | m | null | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 倒序排序
mariadb [hellodb]> select * from s1 order by name desc; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 19 | xue baochai | null | 18 | f | 6 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 24 | xu xian | null | 27 | m | null | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 22 | xiao qiao | null | 20 | f | 1 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 30 | wuzetian | null | 21 | f | null | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 13 | tian boguang | null | 33 | m | 2 | null | | 25 | sun dasheng | null | 100 | m | null | null | | 31 | song jiang | null | 18 | m | 45 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 6 | shi qing | null | 46 | m | 5 | null | | 2 | shi potian | null | 22 | m | 1 | 7 | | 9 | ren yingying | null | 20 | f | 6 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 20 | diao chan | null | 19 | f | 7 | null | | 38 | abc | null | 20 | m | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 指定asc排序 （asc：以数字大小从小到大排序，空值默认为0排在最前面）
mariadb [hellodb]> select * from s1 order by classid asc; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 38 | abc | null | 20 | m | null | null | | 24 | xu xian | null | 27 | m | null | null | | 25 | sun dasheng | null | 100 | m | null | null | | 30 | wuzetian | null | 21 | f | null | null | | 27 | liudehua | null | 18 | f | 1 | null | | 2 | shi potian | null | 22 | m | 1 | 7 | | 22 | xiao qiao | null | 20 | f | 1 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 13 | tian boguang | null | 33 | m | 2 | null | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 7 | xi ren | null | 19 | f | 3 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 28 | mahuateng | null | 20 | m | 3 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 29 | wuyanzu | null | 19 | m | 4 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 6 | shi qing | null | 46 | m | 5 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 还是按照数字从小到大排序但是空值排在最后面
mariadb [hellodb]> select * from s1 order by -classid desc; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 2 | shi potian | null | 22 | m | 1 | 7 | | 22 | xiao qiao | null | 20 | f | 1 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 13 | tian boguang | null | 33 | m | 2 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 28 | mahuateng | null | 20 | m | 3 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 14 | lu wushuang | null | 17 | f | 3 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 6 | shi qing | null | 46 | m | 5 | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 25 | sun dasheng | null | 100 | m | null | null | | 30 | wuzetian | null | 21 | f | null | null | | 24 | xu xian | null | 27 | m | null | null | | 38 | abc | null | 20 | m | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 多字段排序 （先对性别排序在对年龄排序）
mariadb [hellodb]> select * from s1 order by gender , age; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 8 | lin daiyu | null | 17 | f | 7 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 30 | wuzetian | null | 21 | f | null | null | | 21 | huang yueying | null | 22 | f | 6 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 38 | abc | null | 20 | m | null | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 18 | hua rong | null | 23 | m | 7 | null | | 23 | ma chao | null | 23 | m | 4 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 24 | xu xian | null | 27 | m | null | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 13 | tian boguang | null | 33 | m | 2 | null | | 6 | shi qing | null | 46 | m | 5 | null | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 25 | sun dasheng | null | 100 | m | null | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 先对性别降序排序在对年龄正序排序。
mariadb [hellodb]> select * from s1 order by gender desc,age asc; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 38 | abc | null | 20 | m | null | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 18 | hua rong | null | 23 | m | 7 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 23 | ma chao | null | 23 | m | 4 | null | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 24 | xu xian | null | 27 | m | null | null | | 4 | ding dian | null | 32 | m | 4 | 4 | | 13 | tian boguang | null | 33 | m | 2 | null | | 6 | shi qing | null | 46 | m | 5 | null | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 25 | sun dasheng | null | 100 | m | null | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 14 | lu wushuang | null | 17 | f | 3 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 30 | wuzetian | null | 21 | f | null | null | | 21 | huang yueying | null | 22 | f | 6 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 35 rows in set (0.00 sec)
- 多字段排序之后无法在做过滤 (但是排序前可以)
mariadb [hellodb]> select * from s1 order by gender desc,age asc where classid is not null; error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mariadb server version for the right syntax to use near 'where classid is not null' at line 1 mariadb [hellodb]> select * from s1 order by gender desc,age asc having classid is not null; error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mariadb server version for the right syntax to use near 'having classid is not null' at line 1 mariadb [hellodb]> select * from s1 order by gender desc,age asc on classid is not null; error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mariadb server version for the right syntax to use near 'on classid is not null' at line 1 (先过滤在排序) mariadb [hellodb]> select * from s1 where classid is not null order by gender desc,age asc; （和group by 一样还是在前面加） +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 28 | mahuateng | null | 20 | m | 3 | null | | 16 | xu zhu | null | 21 | m | 1 | null | | 1 | shi zhongyu | null | 22 | m | 2 | 3 | | 2 | shi potian | null | 22 | m | 1 | 7 | | 23 | ma chao | null | 23 | m | 4 | null | | 26 | xietingfeng | null | 23 | m | 2 | 1 | | 11 | yuan chengzhi | null | 23 | m | 6 | null | | 18 | hua rong | null | 23 | m | 7 | null | | 17 | lin chong | null | 25 | m | 4 | null | | 5 | yu yutong | null | 26 | m | 3 | 1 | | 4 | ding dian | null | 32 | m | 4 | 4 | | 13 | tian boguang | null | 33 | m | 2 | null | | 6 | shi qing | null | 46 | m | 5 | null | | 3 | xie yanke | null | 53 | m | 2 | 16 | | 14 | lu wushuang | null | 17 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 10 | yue lingshan | null | 19 | f | 3 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 22 | xiao qiao | null | 20 | f | 1 | null | | 9 | ren yingying | null | 20 | f | 6 | null | | 21 | huang yueying | null | 22 | f | 6 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 31 rows in set (0.00 sec)
- 后面的分组字段对调位置的话显示效果不一样但是数据还是一样的。(排序逻辑不一样)
mariadb [hellodb]> select gender,classid,avg(age) from s1 where classid is not null group by gender,classid; +--------+---------+----------+ | gender | classid | avg(age) | +--------+---------+----------+ | f | 1 | 19.0000 | | f | 3 | 18.3333 | | f | 6 | 20.0000 | | f | 7 | 18.0000 | | f | 77 | 18.0000 | | f | 93 | 18.0000 | | m | 1 | 21.5000 | | m | 2 | 32.7500 | | m | 3 | 23.0000 | | m | 4 | 23.6000 | | m | 5 | 46.0000 | | m | 6 | 23.0000 | | m | 7 | 23.0000 | | m | 45 | 18.0000 | | m | 94 | 18.0000 | +--------+---------+----------+ 15 rows in set (0.00 sec) mariadb [hellodb]> select gender,classid,avg(age) from s1 where classid is not null group by classid,gender; +--------+---------+----------+ | gender | classid | avg(age) | +--------+---------+----------+ | f | 1 | 19.0000 | | m | 1 | 21.5000 | | m | 2 | 32.7500 | | f | 3 | 18.3333 | | m | 3 | 23.0000 | | m | 4 | 23.6000 | | m | 5 | 46.0000 | | f | 6 | 20.0000 | | m | 6 | 23.0000 | | f | 7 | 18.0000 | | m | 7 | 23.0000 | | m | 45 | 18.0000 | | f | 77 | 18.0000 | | f | 93 | 18.0000 | | m | 94 | 18.0000 | +--------+---------+----------+ 15 rows in set (0.00 sec)
- limit [[offset,]row_count]：对查询的结果进行输出行数数量限制
正序排序取岁数最小的前10名 limit 作为限制条件
mariadb [hellodb]> select * from s1 order by age limit 10; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 14 | lu wushuang | null | 17 | f | 3 | null | | 8 | lin daiyu | null | 17 | f | 7 | null | | 32 | zhang sanfeng | null | 18 | m | 94 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 33 | miejue shitai | null | 18 | f | 77 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 20 | diao chan | null | 19 | f | 7 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 10 rows in set (0.00 sec)
- 跳过前三行取岁数最小的前10名
mariadb [hellodb]> select * from s1 order by age limit 3,10; +-------+---------------+-------+-----+--------+---------+-----------+ | stuid | name | phone | age | gender | classid | teacherid | +-------+---------------+-------+-----+--------+---------+-----------+ | 33 | miejue shitai | null | 18 | f | 77 | null | | 19 | xue baochai | null | 18 | f | 6 | null | | 31 | song jiang | null | 18 | m | 45 | null | | 27 | liudehua | null | 18 | f | 1 | null | | 34 | lin chaoying | null | 18 | f | 93 | null | | 20 | diao chan | null | 19 | f | 7 | null | | 12 | wen qingqing | null | 19 | f | 1 | null | | 15 | duan yu | null | 19 | m | 4 | null | | 7 | xi ren | null | 19 | f | 3 | null | | 29 | wuyanzu | null | 19 | m | 4 | null | +-------+---------------+-------+-----+--------+---------+-----------+ 10 rows in set (0.00 sec)
- 去重之后在做过滤 （这样取出来的数值比较准确）
mariadb [hellodb]> select distinct age from s1 order by age limit 3; (去重取前三个) +-----+ | age | +-----+ | 17 | | 18 | | 19 | +-----+ 3 rows in set (0.00 sec) mariadb [hellodb]> select distinct age from s1 order by age limit 3,6; （去重跳过前3行取6行） +-----+ | age | +-----+ | 20 | | 21 | | 22 | | 23 | | 25 | | 26 | +-----+ 6 rows in set (0.00 sec)
上一篇： python学习一：数据类型
下一篇： 加域