mysql语句以及实例

数据库操作

数据库备份与恢复

备份mydb数据库，使用mydb.sql文件存储
[root@localhost bin]# mysqldump -u root -p mydb > mydb.sql
备份mydb数据库中的stu表，使用mydb.sql文件存储
[root@localhost bin]# mysqldump -u root -p mydb stu > mydb_stu.sql
恢复mydb数据信息（条件是mydb库存在）
[root@localhost bin]# mysql -u root -p mydb < mydb.sql
查看当前MySQL下的所有数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

创建一个mydb2的数据库

mysql> create database mydb2;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

删除数据库mydb2

mysql> drop database mydb2;
Query OK, 0 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
创建一个mydb数据库
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
再次常见mydb数据库会报错
mysql> create database mydb;
ERROR 1007 (HY000): Can''t create database 'mydb'; database exists
尝试创建mydb数据库（若已存在则会报一个警告，不会报Error错误）
mysql> create database if not exists  mydb;
Query OK, 1 row affected, 1 warning (0.00 sec)
查看mydb的建库语句
mysql> show create database mydb;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
查看当前所在数据库位置：NULL表示没有在任何数据库中
mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)
选择进入mydb数据库
mysql> use mydb;
Database changed
查看当前所在数据库的位置
mysql> select database();
+------------+
| database() |
+------------+
| mydb       |
+------------+
1 row in set (0.00 sec)
查看当前数据库中的所有表
mysql> show tables;
Empty set (0.00 sec)
创建一个uu表，内有三个字段id，name和age
mysql> create table uu(id int,name varchar(16),age int);
Query OK, 0 rows affected (0.09 sec)
查看uu表的建表语句
mysql> show create table uu\G
*************************** 1. row ***************************
       Table: uu
Create Table: CREATE TABLE `uu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
删除uu表
mysql> drop table uu;
Query OK, 0 rows affected (0.02 sec)
添加一个数据，给定所有字段，所有的值
mysql> insert into uu(id,name,age) values(1,'zhangsan',20);
Query OK, 1 row affected (0.01 sec)

mysql> insert into uu(id,name,age) values(2,'lisi',22);
Query OK, 1 row affected (0.02 sec)
不指定字段，添加值，值按默认顺序写
mysql> insert into uu values(3,'wangwu',25);
Query OK, 1 row affected (0.01 sec)
批量添加值
mysql> insert into uu values(4,'zhaoliu',21),
    -> (5,'xiaoli',22),
    -> (6,'xiaozhang',19);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
将id值为4的信息age改为30（修改）
mysql> update uu set age=30 where id=4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from uu;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | zhangsan  |   20 |
|    2 | lisi      |   22 |
|    3 | wangwu    |   25 |
|    4 | zhaoliu   |   30 |
|    5 | xiaoli    |   22 |
|    6 | xiaozhang |   19 |
+------+-----------+------+
6 rows in set (0.00 sec)
删除id为5的所有信息
mysql> delete from uu where id=5;
Query OK, 1 row affected (0.01 sec)

修改表结构实例

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc tt;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1. 在tt表末尾添加一个phone字段，类型varchar(11)，无其他约束
mysql> alter table tt add phone varchar(11);
2. 在tt表中age字段后添加一个address字段，类型varchar(100) ，无其他约束
mysql> alter table tt add address varchar(100) after age;
3. 在tt表首位插入一个mm字段，类型int
mysql> alter table tt add mm int first;
mysql> desc tt;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| mm      | int(11)      | YES  |     | NULL    |       |
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(16)  | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(11)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
4. 删除tt表的mm字段
mysql> alter table tt drop mm;
5. 修改字段：tt表age字段类型改为tinyint类型，unsigned not null default 20
mysql> alter table tt modify age tinyint unsigned not null default 20;
6. 修改name字段名为username
mysql> alter table tt change name username varchar(16);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tt;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id       | int(11)             | YES  |     | NULL    |       |
| username | varchar(16)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | NO   |     | 20      |       |
| address  | varchar(100)        | YES  |     | NULL    |       |
| phone    | varchar(11)         | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

数据的增、删、改，查

=============

mysql> desc stu;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name    | varchar(8)          | NO   | UNI | NULL    |                |
| age     | tinyint(3) unsigned | YES  |     | NULL    |                |
| sex     | enum('w','m')       | NO   |     | m       |                |
| classid | char(8)             | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

1. 添加：

格式：insert into 表名[(字段列表)] values(值列表)[,(值列表)...]

标准添加sql，指定所有字段，给定所有的值

mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','python03');

指定部分字段，给定部分值添加

mysql> insert into stu(name,age,classid) values('lisi',22,'python02');
Query OK, 1 row affected (0.02 sec)

不指定字段添加值（注意顺序和个数）

mysql> insert into stu values(null,'wangwu',25,'w','python03');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.00 sec)

批量添加值

mysql> insert into stu values
    -> (null,'xiaoli',28,'m','python02'),
    -> (null,'xiaozhang',21,'w','python01'),
    -> (null,'xiaozhao',27,'m','python03'),
    -> (null,'uu01',18,'m','python03');
Query OK, 4 rows affected, 1 warning (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  8 | uu02     | NULL | m   | NULL     |
|  9 | uu03     | NULL | m   | NULL     |
| 10 | uu04     | NULL | m   | NULL     |
| 11 | uu06     | NULL | m   | NULL     |
| 12 | uu08     | NULL | m   | NULL     |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)

2. 删除数据

格式： delete from 表名 [where 条件 [分组、排序、limit]]

删除id为20的信息
mysql> delete from stu where id=20;
Query OK, 0 rows affected (0.00 sec)
删除id大于100的所有信息
mysql> delete from stu where id>100;
Query OK, 0 rows affected (0.00 sec)
删除id是100~200的所有信息
mysql> delete from stu where id between 100 and 200;
等价于
mysql> delete from stu where id>=100 and id<=200;
Query OK, 0 rows affected (0.00 sec)
删除性别为w，年龄大于25的所有信息
mysql> delete from stu where sex='w' and age>25;
Query OK, 0 rows affected (0.00 sec)

3. 修改数据

格式：update 表名 set 字段名1=值1[,字段名2=值2,...] [where 条件...]

将id为9和11 的年龄age改为23，班级classid改为python03
 mysql> update stu set age=23,classid='python03' where id in(9,11);
Query OK, 2 rows affected (0.08 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  8 | uu02     | NULL | m   | NULL     |
|  9 | uu03     |   23 | m   | python03 |
| 10 | uu04     | NULL | m   | NULL     |
| 11 | uu06     |   23 | m   | python03 |
| 12 | uu08     | NULL | m   | NULL     |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)
将id为8,10和12 的年龄age改为26，班级classid改为python02
mysql> update stu set age=26,classid='python02' where id in(8,10,12);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  8 | uu02     |   26 | m   | python02 |
|  9 | uu03     |   23 | m   | python03 |
| 10 | uu04     |   26 | m   | python02 |
| 11 | uu06     |   23 | m   | python03 |
| 12 | uu08     |   26 | m   | python02 |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)

mysql> update stu set sex='w' where id in(8,11,12);
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  8 | uu02     |   26 | w   | python02 |
|  9 | uu03     |   23 | m   | python03 |
| 10 | uu04     |   26 | m   | python02 |
| 11 | uu06     |   23 | w   | python03 |
| 12 | uu08     |   26 | w   | python02 |
+----+----------+------+-----+----------+
12 rows in set (0.00 sec)

mysql>
mysql> update stu set age=age-2 where id=15;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4. 查询

mysql> select * from stu;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  8 | uu02     |   26 | w   | python02 |
|  9 | uu03     |   23 | m   | python03 |
| 10 | uu04     |   26 | m   | python02 |
| 11 | uu06     |   23 | w   | python03 |
| 12 | uu08     |   26 | w   | python02 |
| 13 | qq06     | NULL | m   | NULL     |
| 14 | qq01     |   31 | m   | python01 |
| 15 | qq03     |   33 | m   | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql> select id,name,age from stu;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   22 |
|  3 | wangwu   |   25 |
|  4 | xiaoli   |   28 |
|  5 | xiaozhan |   21 |
|  6 | xiaozhao |   27 |
|  7 | uu01     |   18 |
|  8 | uu02     |   26 |
|  9 | uu03     |   23 |
| 10 | uu04     |   26 |
| 11 | uu06     |   23 |
| 12 | uu08     |   26 |
| 13 | qq06     | NULL |
| 14 | qq01     |   31 |
| 15 | qq03     |   33 |
+----+----------+------+
15 rows in set (0.00 sec)

mysql> select id,name as username,age from stu;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   22 |
|  3 | wangwu   |   25 |
|  4 | xiaoli   |   28 |
|  5 | xiaozhan |   21 |
|  6 | xiaozhao |   27 |
|  7 | uu01     |   18 |
|  8 | uu02     |   26 |
|  9 | uu03     |   23 |
| 10 | uu04     |   26 |
| 11 | uu06     |   23 |
| 12 | uu08     |   26 |
| 13 | qq06     | NULL |
| 14 | qq01     |   31 |
| 15 | qq03     |   33 |
+----+----------+------+
15 rows in set (0.01 sec)

mysql> select id,name username,age from stu;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | zhangsan |   20 |
|  2 | lisi     |   22 |
|  3 | wangwu   |   25 |
|  4 | xiaoli   |   28 |
|  5 | xiaozhan |   21 |
|  6 | xiaozhao |   27 |
|  7 | uu01     |   18 |
|  8 | uu02     |   26 |
|  9 | uu03     |   23 |
| 10 | uu04     |   26 |
| 11 | uu06     |   23 |
| 12 | uu08     |   26 |
| 13 | qq06     | NULL |
| 14 | qq01     |   31 |
| 15 | qq03     |   33 |
+----+----------+------+
15 rows in set (0.00 sec)

mysql> select *,age+5 age5 from stu;
+----+----------+------+-----+----------+------+
| id | name     | age  | sex | classid  | age5 |
+----+----------+------+-----+----------+------+
|  1 | zhangsan |   20 | m   | python03 |   25 |
|  2 | lisi     |   22 | m   | python02 |   27 |
|  3 | wangwu   |   25 | w   | python03 |   30 |
|  4 | xiaoli   |   28 | m   | python02 |   33 |
|  5 | xiaozhan |   21 | w   | python01 |   26 |
|  6 | xiaozhao |   27 | m   | python03 |   32 |
|  7 | uu01     |   18 | m   | python03 |   23 |
|  8 | uu02     |   26 | w   | python02 |   31 |
|  9 | uu03     |   23 | m   | python03 |   28 |
| 10 | uu04     |   26 | m   | python02 |   31 |
| 11 | uu06     |   23 | w   | python03 |   28 |
| 12 | uu08     |   26 | w   | python02 |   31 |
| 13 | qq06     | NULL | m   | NULL     | NULL |
| 14 | qq01     |   31 | m   | python01 |   36 |
| 15 | qq03     |   33 | m   | python01 |   38 |
+----+----------+------+-----+----------+------+
15 rows in set (0.01 sec)

mysql> select *,'beijing' as city from stu;
+----+----------+------+-----+----------+---------+
| id | name     | age  | sex | classid  | city    |
+----+----------+------+-----+----------+---------+
|  1 | zhangsan |   20 | m   | python03 | beijing |
|  2 | lisi     |   22 | m   | python02 | beijing |
|  3 | wangwu   |   25 | w   | python03 | beijing |
|  4 | xiaoli   |   28 | m   | python02 | beijing |
|  5 | xiaozhan |   21 | w   | python01 | beijing |
|  6 | xiaozhao |   27 | m   | python03 | beijing |
|  7 | uu01     |   18 | m   | python03 | beijing |
|  8 | uu02     |   26 | w   | python02 | beijing |
|  9 | uu03     |   23 | m   | python03 | beijing |
| 10 | uu04     |   26 | m   | python02 | beijing |
| 11 | uu06     |   23 | w   | python03 | beijing |
| 12 | uu08     |   26 | w   | python02 | beijing |
| 13 | qq06     | NULL | m   | NULL     | beijing |
| 14 | qq01     |   31 | m   | python01 | beijing |
| 15 | qq03     |   33 | m   | python01 | beijing |
+----+----------+------+-----+----------+---------+
15 rows in set (0.00 sec)

mysql> select *,'beijing' city from stu;
+----+----------+------+-----+----------+---------+
| id | name     | age  | sex | classid  | city    |
+----+----------+------+-----+----------+---------+
|  1 | zhangsan |   20 | m   | python03 | beijing |
|  2 | lisi     |   22 | m   | python02 | beijing |
|  3 | wangwu   |   25 | w   | python03 | beijing |
|  4 | xiaoli   |   28 | m   | python02 | beijing |
|  5 | xiaozhan |   21 | w   | python01 | beijing |
|  6 | xiaozhao |   27 | m   | python03 | beijing |
|  7 | uu01     |   18 | m   | python03 | beijing |
|  8 | uu02     |   26 | w   | python02 | beijing |
|  9 | uu03     |   23 | m   | python03 | beijing |
| 10 | uu04     |   26 | m   | python02 | beijing |
| 11 | uu06     |   23 | w   | python03 | beijing |
| 12 | uu08     |   26 | w   | python02 | beijing |
| 13 | qq06     | NULL | m   | NULL     | beijing |
| 14 | qq01     |   31 | m   | python01 | beijing |
| 15 | qq03     |   33 | m   | python01 | beijing |
+----+----------+------+-----+----------+---------+
15 rows in set (0.01 sec)

mysql> select concat(classid,":",name) from stu;
+--------------------------+
| concat(classid,":",name) |
+--------------------------+
| python03:zhangsan        |
| python02:lisi            |
| python03:wangwu          |
| python02:xiaoli          |
| python01:xiaozhan        |
| python03:xiaozhao        |
| python03:uu01            |
| python02:uu02            |
| python03:uu03            |
| python02:uu04            |
| python03:uu06            |
| python02:uu08            |
| NULL                     |
| python01:qq01            |
| python01:qq03            |
+--------------------------+
15 rows in set (0.00 sec)

where条件查询

1. 查询班级为python03期的所有学生信息
mysql> select * from stu where classid='python03';
2. 查询班级为python03期，并且性别为m的所有学生信息
mysql> select * from stu where classid='python03' and sex="m";
3. 查询年龄大于20，性别为w的所有信息
mysql> select * from stu where age>20 and sex='w';
4. 查询年龄是20~25的所有信息
mysql> select * from stu where age>=20 and age<=25;
mysql> select * from stu where age between 20 and 25;
5. 查询年龄不在20~25的学生信息
mysql> select * from stu where age<20 or age>25;
mysql> select * from stu where age not between 20 and 25;
6. 查询id号为1,3,5,7,9的学生信息
mysql> select * from stu where id in(1,3,5,7,9);
7. 查询classid不为null所有信息
mysql> select * from stu where classid is not null;
8. 查询班级为python01和python02期所有男生（sex='m'）信息
mysql> select * from stu where (classid='python01' or classid='python02') and sex='m';
mysql> select * from stu where classid in('python01','python02') and sex='m';
9. 查询姓名中含有an子串的所有信息
like 模糊查询，支持俩个特殊符号：'%'和'_' %表示任意数量的任意字符， _表示任意一位字符
mysql> select * from stu where name like '%an%';
mysql> select * from stu where name regexp 'an';
10. 查询姓名是有4位任意小写字符或数字构成的信息
mysql> select * from stu where name like '____';
mysql> select * from stu where name regexp '^[a-z0-9]{4}$';

统计函数（聚合函数）max() min() sum() avg() count()

获取最大年龄，最小年龄，年龄总和，平均年龄，总计条数

mysql> select max(age),min(age),sum(age),avg(age),count(id) from stu;
+----------+----------+----------+----------+-----------+
| max(age) | min(age) | sum(age) | avg(age) | count(id) |
+----------+----------+----------+----------+-----------+
|       33 |       18 |      371 |  24.7333 |        15 |
+----------+----------+----------+----------+-----------+
1 row in set (0.01 sec)

group by 字段名 分组

按性别sex分组，并统计人数
mysql> select sex,count(*) from stu group by sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
| w   |        5 |
| m   |       10 |
+-----+----------+
2 rows in set (0.00 sec)
按班级分组统计每个班级的人数（排除班级信息为null的数据）
mysql> select classid,count(*) from stu where classid is not null  group by classid;
+----------+----------+
| classid  | count(*) |
+----------+----------+
| python01 |        3 |
| python02 |        5 |
| python03 |        6 |
+----------+----------+
3 rows in set (0.00 sec)
按班级分组，并统计每个班级的男生和女生人数（排除班级信息为null的数据）
mysql> select classid,sex,count(*) from stu where classid is not null  group by classid,sex;
+----------+-----+----------+
| classid  | sex | count(*) |
+----------+-----+----------+
| python01 | w   |        1 |
| python01 | m   |        2 |
| python02 | w   |        2 |
| python02 | m   |        3 |
| python03 | w   |        2 |
| python03 | m   |        4 |
+----------+-----+----------+
6 rows in set (0.00 sec)
在上面的查询中，加入过滤条件(人数大于等于3的信息）
mysql> select classid,sex,count(*) num from stu where classid is not null  group by classid,sex having num>=3;
+----------+-----+-----+
| classid  | sex | num |
+----------+-----+-----+
| python02 | m   |   3 |
| python03 | m   |   4 |
+----------+-----+-----+
2 rows in set (0.00 sec)
排序：order by 字段名 asc(默认升序)|desc（降序）
-------------------------------------------------------------
mysql> select * from stu order by age;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  7 | uu01     |   18 | m   | python03 |
|  1 | zhangsan |   20 | m   | python03 |
|  5 | xiaozhan |   21 | w   | python01 |
|  2 | lisi     |   22 | m   | python02 |
| 13 | qq06     |   22 | m   | NULL     |
| 11 | uu06     |   23 | w   | python03 |
|  9 | uu03     |   23 | m   | python03 |
|  3 | wangwu   |   25 | w   | python03 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  6 | xiaozhao |   27 | m   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
| 14 | qq01     |   31 | m   | python01 |
| 15 | qq03     |   33 | m   | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql> select * from stu order by age asc;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  7 | uu01     |   18 | m   | python03 |
|  1 | zhangsan |   20 | m   | python03 |
|  5 | xiaozhan |   21 | w   | python01 |
|  2 | lisi     |   22 | m   | python02 |
| 13 | qq06     |   22 | m   | NULL     |
| 11 | uu06     |   23 | w   | python03 |
|  9 | uu03     |   23 | m   | python03 |
|  3 | wangwu   |   25 | w   | python03 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  6 | xiaozhao |   27 | m   | python03 |
|  4 | xiaoli   |   28 | m   | python02 |
| 14 | qq01     |   31 | m   | python01 |
| 15 | qq03     |   33 | m   | python01 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql> select * from stu order by age desc;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
| 15 | qq03     |   33 | m   | python01 |
| 14 | qq01     |   31 | m   | python01 |
|  4 | xiaoli   |   28 | m   | python02 |
|  6 | xiaozhao |   27 | m   | python03 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
|  9 | uu03     |   23 | m   | python03 |
| 11 | uu06     |   23 | w   | python03 |
| 13 | qq06     |   22 | m   | NULL     |
|  2 | lisi     |   22 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  1 | zhangsan |   20 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql> select * from stu order by classid desc;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  3 | wangwu   |   25 | w   | python03 |
| 11 | uu06     |   23 | w   | python03 |
|  6 | xiaozhao |   27 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
|  9 | uu03     |   23 | m   | python03 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  4 | xiaoli   |   28 | m   | python02 |
|  2 | lisi     |   22 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
| 14 | qq01     |   31 | m   | python01 |
| 15 | qq03     |   33 | m   | python01 |
| 13 | qq06     |   22 | m   | NULL     |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql> select * from stu order by classid asc;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
| 13 | qq06     |   22 | m   | NULL     |
| 15 | qq03     |   33 | m   | python01 |
| 14 | qq01     |   31 | m   | python01 |
|  5 | xiaozhan |   21 | w   | python01 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  4 | xiaoli   |   28 | m   | python02 |
|  2 | lisi     |   22 | m   | python02 |
|  7 | uu01     |   18 | m   | python03 |
|  9 | uu03     |   23 | m   | python03 |
|  6 | xiaozhao |   27 | m   | python03 |
| 11 | uu06     |   23 | w   | python03 |
|  3 | wangwu   |   25 | w   | python03 |
|  1 | zhangsan |   20 | m   | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.01 sec)

mysql> select * from stu order by classid asc,age desc;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
| 13 | qq06     |   22 | m   | NULL     |
| 15 | qq03     |   33 | m   | python01 |
| 14 | qq01     |   31 | m   | python01 |
|  5 | xiaozhan |   21 | w   | python01 |
|  4 | xiaoli   |   28 | m   | python02 |
| 12 | uu08     |   26 | w   | python02 |
| 10 | uu04     |   26 | m   | python02 |
|  8 | uu02     |   26 | w   | python02 |
|  2 | lisi     |   22 | m   | python02 |
|  6 | xiaozhao |   27 | m   | python03 |
|  3 | wangwu   |   25 | w   | python03 |
|  9 | uu03     |   23 | m   | python03 |
| 11 | uu06     |   23 | w   | python03 |
|  1 | zhangsan |   20 | m   | python03 |
|  7 | uu01     |   18 | m   | python03 |
+----+----------+------+-----+----------+
15 rows in set (0.00 sec)

mysql>

获取部分数据：limit

-- 分页公式： limit (页号-1)*页大小,页大小

--------------------------------------------------
mysql> select * from stu limit 0,3;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  1 | zhangsan |   20 | m   | python03 |
|  2 | lisi     |   22 | m   | python02 |
|  3 | wangwu   |   25 | w   | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.00 sec)

mysql> select * from stu limit 3,3;
+----+----------+------+-----+----------+
| id | name     | age  | sex | classid  |
+----+----------+------+-----+----------+
|  4 | xiaoli   |   28 | m   | python02 |
|  5 | xiaozhan |   21 | w   | python01 |
|  6 | xiaozhao |   27 | m   | python03 |
+----+----------+------+-----+----------+
3 rows in set (0.01 sec)

mysql> select * from stu limit 6,3;
+----+------+------+-----+----------+
| id | name | age  | sex | classid  |
+----+------+------+-----+----------+
|  7 | uu01 |   18 | m   | python03 |
|  8 | uu02 |   26 | w   | python02 |
|  9 | uu03 |   23 | m   | python03 |
+----+------+------+-----+----------+
3 rows in set (0.00 sec)

mysql> select * from stu limit 9,3;
+----+------+------+-----+----------+
| id | name | age  | sex | classid  |
+----+------+------+-----+----------+
| 10 | uu04 |   26 | m   | python02 |
| 11 | uu06 |   23 | w   | python03 |
| 12 | uu08 |   26 | w   | python02 |
+----+------+------+-----+----------+
3 rows in set (0.00 sec)

综合查询练习

1. 查询python03期所有学员，按年龄降序排序
mysql> select * from stu where classid='python03' order by age desc;
2. 查询python03期，年龄最大的3位学员信息
mysql> select * from stu where classid='python03' order by age desc limit 3;
3. 统计每个班级人数，并按人数降序排序（排除班级信息为null的数据）
mysql> select classid,count(*) m from stu where classid is not null group by classid order by m desc;
4. 统计每个班级年龄在20~30的学员人数信息，并按人数降序排序（排除班级信息为null的数据）
mysql> select classid,count(*) m from stu where classid is not null and age between 20 and 30
    -> group by classid order by m desc
5. 统计每个班级男女生人数最多3条记录信息。（排除班级信息为null的数据）
mysql> select classid,sex,count(*) m from stu where classid is not null
    -> group by classid,sex order by m desc limit 3;

多表查询：

1. 嵌套查询 （一个查询的结果是另外查询的条件）
2. where关联查询
3. join连接查询：内联inner join，左联 left join，右联right join

1. 嵌套查询：

-- 查询年龄最大的学生信息
mysql> select * from stu where age=(select max(age) from stu);
+----+------+------+-----+----------+
| id | name | age  | sex | classid  |
+----+------+------+-----+----------+
| 15 | qq03 |   33 | m   | python01 |
+----+------+------+-----+----------+
1 row in set (0.00 sec)
查询帖子标题为'工作十年'的发帖人信息
mysql> select * from users where id in(select uid from topic where title='工作十年');
+----+---------------+----------------------------------+--------+-------------+
| id | email         | password                         | name   | phone       |
+----+---------------+----------------------------------+--------+-------------+
|  2 | lisi@sohu.com | e10adc3949ba59abbe56e057f20f883e | 李四   | 13466587533 |
+----+---------------+----------------------------------+--------+-------------+
1 row in set (0.00 sec)

2. where关联查询

查询帖子信息，并关联发帖人姓名信息
mysql> select t.id,t.title,t.addtime,u.name from topic t,users u where t.uid=u.id;
+----+-----------------------------------------------+---------------------+-----------+
| id | title                                         | addtime             | name      |
+----+-----------------------------------------------+---------------------+-----------+
|  1 | 工作十年                                      | 2017-09-10 12:23:45 | 李四      |
|  2 | 毕业一年，大家过着怎样的生活                  | 2017-09-11 18:23:45 | 张三丰    |
|  3 | 英语四级真的很重要吗？                        | 2017-09-12 08:03:45 | 王月      |
|  4 | 为什么工资一直涨不了？速度进来                | 2017-09-13 15:43:23 | 王五      |
|  5 | 现在创业好难啊                                | 2017-09-14 16:23:45 | 李四      |
|  6 | 现在学什么技术好一些？                        | 2017-09-14 18:23:25 | 王五      |
+----+-----------------------------------------------+---------------------+-----------+
6 rows in set (0.00 sec)

使用where管理，统计每个用户的发帖量信息

mysql> select u.id,u.name,count(*)  from users u,topic t where u.id=t.uid group by u.id;
+----+-----------+----------+
| id | name      | count(*) |
+----+-----------+----------+
|  1 | 张三丰    |        1 |
|  2 | 李四      |        2 |
|  3 | 王五      |        2 |
|  5 | 王月      |        1 |
+----+-----------+----------+
4 rows in set (0.00 sec)

mysql> select u.id,u.name,count(*) num  from users u,topic t where u.id=t.uid group by u.id order by num desc limit 10;
+----+-----------+-----+
| id | name      | num |
+----+-----------+-----+
|  2 | 李四      |   2 |
|  3 | 王五      |   2 |
|  5 | 王月      |   1 |
|  1 | 张三丰    |   1 |
+----+-----------+-----+
4 rows in set (0.01 sec)

mysql>

join 连接查询

以左联为主，user信息为主，右侧topic表信息没有的补null
mysql> select u.id,u.name,t.id,t.title from users u left join topic t on u.id=t.uid;
+----+-----------+------+-----------------------------------------------+
| id | name      | id   | title                                         |
+----+-----------+------+-----------------------------------------------+
|  1 | 张三丰    |    2 | 毕业一年，大家过着怎样的生活                  |
|  2 | 李四      |    1 | 工作十年                                      |
|  2 | 李四      |    5 | 现在创业好难啊                                |
|  3 | 王五      |    4 | 为什么工资一直涨不了？速度进来                |
|  3 | 王五      |    6 | 现在学什么技术好一些？                        |
|  4 | 赵六      | NULL | NULL                                          |
|  5 | 王月      |    3 | 英语四级真的很重要吗？                        |
|  6 | 陆玲      | NULL | NULL                                          |
+----+-----------+------+-----------------------------------------------+
8 rows in set (0.00 sec)
右联查询，以topic为主，左侧users没有对应信息的补null
mysql> select u.id,u.name,t.id,t.title from users u right join topic t on u.id=t.uid;
+------+-----------+----+-----------------------------------------------+
| id   | name      | id | title                                         |
+------+-----------+----+-----------------------------------------------+
|    2 | 李四      |  1 | 工作十年                                      |
|    1 | 张三丰    |  2 | 毕业一年，大家过着怎样的生活                  |
|    5 | 王月      |  3 | 英语四级真的很重要吗？                        |
|    3 | 王五      |  4 | 为什么工资一直涨不了？速度进来                |
|    2 | 李四      |  5 | 现在创业好难啊                                |
|    3 | 王五      |  6 | 现在学什么技术好一些？                        |
| NULL | NULL      |  8 | aaaaaaaa                                      |
+------+-----------+----+-----------------------------------------------+
7 rows in set (0.00 sec)
内联（等价于where管理），求的是两侧交集的数据
mysql> select u.id,u.name,t.id,t.title from users u inner join topic t on u.id=t.uid;
+----+-----------+----+-----------------------------------------------+
| id | name      | id | title                                         |
+----+-----------+----+-----------------------------------------------+
|  1 | 张三丰    |  2 | 毕业一年，大家过着怎样的生活                  |
|  2 | 李四      |  1 | 工作十年                                      |
|  2 | 李四      |  5 | 现在创业好难啊                                |
|  3 | 王五      |  4 | 为什么工资一直涨不了？速度进来                |
|  3 | 王五      |  6 | 现在学什么技术好一些？                        |
|  5 | 王月      |  3 | 英语四级真的很重要吗？                        |
+----+-----------+----+-----------------------------------------------+
6 rows in set (0.00 sec)
统计每个用户的发帖量信息，包含没有发帖的用户统计
mysql> select u.id,u.name,count(t.id) num from users u left join  topic t on u.id=t.uid  group by u.id order by num desc limit 10;
+----+-----------+-----+
| id | name      | num |
+----+-----------+-----+
|  3 | 王五      |   2 |
|  2 | 李四      |   2 |
|  5 | 王月      |   1 |
|  1 | 张三丰    |   1 |
|  6 | 陆玲      |   0 |
|  4 | 赵六      |   0 |
+----+-----------+-----+
6 rows in set (0.01 sec)

查询实战

1. 查询帖子id号为1的所有评论信息，要求管理评论人信
显示字段：评论id，帖子id，评论人id，评论人姓名，评论内容
要求使用：where关联，inner jion和left join各执行一次
mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r,users u where r.uid = u.id and r.tid=1;

mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r inner join users u on r.uid = u.id where r.tid=1;

mysql> select r.id,r.tid,r.uid,u.name,r.content from reply r left join users u on r.uid = u.id where r.tid=1;
2. 查询帖子信息，并关联输出发帖人信息
显示字段：帖子id，帖子标题，发帖时间，发帖人id，发帖人姓名
要求使用：where关联，inner jion和left join各执行一次
mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t,users u where t.uid=u.id;

mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t inner join users u on t.uid=u.id;

mysql> select t.id,t.title,t.addtime,t.uid,u.name from topic t left join users u on t.uid=u.id;
3. 查询id号为3的单条评论信息，关联帖子和用户信息
显示字段：评论id，帖子id，帖子标题，评论人id，评论人姓名，评论内容，评论时间
mysql> select r.id,r.tid,t.title,r.uid,u.name,r.content,r.addtime from reply r,topic t,users u where r.tid=t.id and r.uid=u.id and r.id=3;
--左联查
mysql> select r.id,r.tid,t.title,r.uid,u.name,r.content,r.addtime 
    -> from reply r left join topic t on r.tid=t.id 
    -> left join users u on r.uid=u.id where r.id=3\G
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 219,427评论 6赞 508
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 93,551评论 3赞 395
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 165,747评论 0赞 356
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,939评论 1赞 295
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,955评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,737评论 1赞 305
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,448评论 3赞 420
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,352评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,834评论 1赞 317
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,992评论 3赞 338
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 40,133评论 1赞 351
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,815评论 5赞 346
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,477评论 3赞 331
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 32,022评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 33,147评论 1赞 272
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 48,398评论 3赞 373
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 45,077评论 2赞 355

推荐阅读更多精彩内容

  • Java面试
    一. Java基础部分.................................................
    wy_sure阅读 3,811评论 0赞 11
  • MySQL数据库常用命令
    MySQL 数据库常用命令 1、MySQL常用命令 create database name; 创建数据库 use...
    55lover阅读 4,791评论 1赞 57
  • Java初级面试题
    1. Java基础部分 基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语...
    子非鱼_t_阅读 31,644评论 18赞 399
  • Spring Cloud
    Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具（例如配置管理，服务发现，断路器，智...
    卡卡罗2017阅读 134,672评论 18赞 139
  • 你需要的并不是你想要的
    看过了太多太多的鸡汤，永远都在说着：“我在你身上看不到希望，所以我们走不下去了。”其实女生最初并没有这样的想法，而...
    矫情先生阅读 889评论 0赞 0