目录
一、MySQL进价查询
1、排序
■ 使用ORDERBY语句来实现排序
■ 排序可针对一个或多个字段
■ ASC:升序,默认排序方式 【升序是从小到大】
■ DESC:降序 【降序是从大到小】
■ ORDER BY的语法结构
■ ORDER BY后面跟字段名
SELECT column1, column2,....FROM table_name ORDER BY column1,column2,...ASC|DESC;
例:建一张表
mysql> create table info (id int(4) auto_increment primary key,name varchar(10) not null,score int(4));
Query OK, 0 rows affected (0.02 sec)
插入数据后
mysql> select * from info;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 82 |
| 2 | lisi | 89 |
| 3 | wangwu | 92|
| 4 | zhaoliu | 65 |
| 5 | zhaosi | 75 |
| 6 | zhouliu | 84 |
+----+----------+-------+
6 rows in set (0.00 sec)
1.1、 单字段排序
不加排列顺序,默认是升序排列
mysql> select * from info order by score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 4 | zhaoliu | 65 |
| 5 | zhaosi | 75 |
| 1 | zhangsan | 82 |
| 6 | zhouliu | 84 |
| 2 | lisi | 89 |
| 3 | wangwu | 92 |
+----+----------+-------+
6 rows in set (0.00 sec)
加desc 降序排列
mysql> select * from info order by score desc;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 3 | wangwu | 92 |
| 2 | lisi | 89 |
| 6 | zhouliu | 84 |
| 1 | zhangsan | 82 |
| 5 | zhaosi | 75 |
| 4 | zhaoliu | 65 |
+----+----------+-------+
6 rows in set (0.01 sec)
1.2、 多字段排序
默认状态
mysql> select * from info;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 82 | 2 |
| 2 | lisi | 89 | 3 |
| 3 | wangwu | 92 | 1 |
| 4 | zhaoliu | 65 | 1 |
| 5 | zhaosi | 75 | 2 |
| 6 | zhouliu | 84 | 1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)
mysql> select * from info where 2=2 order by hobby desc,score desc;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 2 | lisi | 89 | 3 |
| 1 | zhangsan | 82 | 2 |
| 5 | zhaosi | 75 | 2 |
| 3 | wangwu | 92 | 1 |
| 6 | zhouliu | 84 | 1 |
| 4 | zhaoliu | 65 | 1 |
+----+----------+-------+-------+
6 rows in set (0.00 sec)
只有第一个字段相同的情况下,第二字段排序才有意义
2 分组
■ 使用GROUP BY语句来实现分组
■ 通常结合聚合函数一起使用
■ 可以按一个或多个字段对结果进行分组
■ GROUP BY的语法结构
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator value GROUP BY column_name;
2.1、GROUP BY分组
按hobby进行分组,统计每组的name个数
mysql> select count(name),hobby from info group by hobby;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
| 3 | 1 |
| 2 | 2 |
| 1 | 3 |
+-------------+-------+
3 rows in set (0.00 sec)
2.2、GROUP BY结合ORDER BY
mysql> select count(name),hobby from info group by hobby order by count(name) desc;
+-------------+-------+
| count(name) | hobby |
+-------------+-------+
| 3 | 1 |
| 2 | 2 |
| 1 | 3 |
+-------------+-------+
3 rows in set (0.00 sec)
3、限制结果条目LIMIT
■ 只返回SELECT查询结果的第一行或前几行
■ 使用LIMIT语句限制条目
■ LIMIT语法结构
SELECT column1,column2,...FROM table_name LIMIT[offset,] number;
number:返回记录行的最大数目
[offset,]:位置偏移量,从0开始
查看前三行
mysql> select * from info limit 3;
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 82 | 2 |
| 2 | lisi | 89 | 3 |
| 3 | wangwu | 92 | 1 |
+----+----------+-------+-------+
3 rows in set (0.00 sec)
查看4-6行
mysql> select * from info limit 3,3;
+----+---------+-------+-------+
| id | name | score | hobby |
+----+---------+-------+-------+
| 4 | zhaoliu | 65 | 1 |
| 5 | zhaosi | 75 | 2 |
| 6 | zhouliu | 84 | 1 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)
4、设置别名
■ 使用AS语句设置别名,关键字AS可省略
■ 设置别名时,保证不能与库中其他表或字段名称冲突
■ 别名的语法结构
SELECT column_name AS alias_name FROM table_name;
SELECT column_name(s)FROM table_name ASalias_name;
■ AS的用法
对于字段设置
给name和score设置别名
mysql> select name as 姓名,score as 成绩 from info;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 82 |
| lisi | 89 |
| wangwu | 92 |
| zhaoliu | 65 |
| zhaosi | 75 |
| zhouliu | 84 |
+----------+--------+
6 rows in set (0.00 sec)
不加as语法也可以设置别名
mysql> select name 姓名,score 成绩 from info;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 82 |
| lisi | 89 |
| wangwu | 92 |
| zhaoliu | 65 |
| zhaosi | 75 |
| zhouliu | 84 |
+----------+--------+
6 rows in set (0.00 sec)
对于表设置,一般在多表查询时使用
给info表设置别名i,然后在name和score前面也要加i.使用,不加as也可以使用
mysql> select i.name as 姓名,i.score as 成绩 from info as i;
+----------+--------+
| 姓名 | 成绩 |
+----------+--------+
| zhangsan | 82 |
| lisi | 89 |
| wangwu | 92 |
| zhaoliu | 65 |
| zhaosi | 75 |
| zhouliu | 84 |
+----------+--------+
6 rows in set (0.00 sec)
作为连接语句
创建good新表,将info表的 score字段>=85的数据放在新表good上
mysql> create table good as select * from info where score >=85;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
可以看到主键、自增约束都没有了,
mysql> desc good;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | 0 | |
| name | varchar(10) | NO | | NULL | |
| score | int(4) | YES | | NULL | |
| hobby | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
数据导过去了
mysql> select * from good;
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 2 | lisi | 89 | 3 |
| 3 | wangwu | 92 | 1 |
+----+--------+-------+-------+
2 rows in set (0.00 sec)
5、通配符
■ 用于替换字符串的部分字符
■ 通常配合LIKE一起使用,并协同WHERE完成查询
■ 常用的通配符
● %表示零个、一个或多个
● _表示单个字符
查询l开头的,%表示零个、一个或多个
mysql> select * from info where name like 'l%';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
| 2 | lisi | 89 | 3 |
+----+------+-------+-------+
1 row in set (0.00 sec)
_下划线代表单个字符
mysql> select * from info where name like '_i_i';
+----+------+-------+-------+
| id | name | score | hobby |
+----+------+-------+-------+
| 2 | lisi | 89 | 3 |
+----+------+-------+-------+
1 row in set (0.00 sec)
6、子查询
■ 也称作内查询或者嵌套查询
■ 先于主查询被执行,其结果将作为外层主查询的条件
■ 在增删改查中都可以使用子查询
■ 支持多层嵌套
■ IN语句是用来判断某个值是否在给定的结果集中
先建一个num表,里面只有id,在id字段在添加一些数据
mysql> select * from num;
+------+
| id |
+------+
| 1 |
| 3 |
| 5 |
| 7 |
+------+
4 rows in set (0.00 sec)
然后根据刚刚的info表和num表进行多表相连,按照num表的1、3、5、7显示出info表的1、3、5、7行数据
从最内的括号开始,括号内的结果是括号外的条件
注意!!!括号内作为结果输出的数据类型要与括号外的一样,不然无法查询。
mysql> select * from info where id in(select id from num);
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 82 | 2 |
| 3 | wangwu | 92 | 1 |
| 5 | zhaosi | 75 | 2 |
+----+----------+-------+-------+
多层嵌套,从内部括号到外面匹配
mysql> select * from info where id in(select id from num where name in(select name from num));
+----+----------+-------+-------+
| id | name | score | hobby |
+----+----------+-------+-------+
| 1 | zhangsan | 82 | 2 |
| 3 | wangwu | 92 | 1