mysql语句以及实例
2017-12-06 本文已影响3143人
tkpy
数据库操作
数据库备份与恢复
备份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
