Mysql-DQL
mysql之DQL查询AS CONCAT LIKE的使用select 列名1，列名2，... from 表名
过滤掉重复的列值
select distinct 列名1from 表名
-- 重复的列值只列出一次(去掉列值重复)
mysql> select distinct(password) from user;
连接concat
select concat(列名1，列名2) from 表名 concat_ws带分隔符
列起别名 as
select 列名1 as 别名，列名2 from 表名
模糊查询
select 列名 ... from 表名 where 列名 like '%字符串%';
mysql> select user_name from user where user_name like '%ng%';
+-----------+
| user_name |
+-----------+
| liming |
| zhangsan|
+-----------+
Mysql之DQL排序以及聚合函数
order by 字段 asc;
order by 字段 desc;
mysql> select user_name,id from user order by> +-----------+----+
| user_name |> +-----------+----+
| liming |1 |
| zhangsan|2 |
| 李华 |3 |
+-----------+----+
3 rows in set (0.01 sec)
mysql> select user_name,id from user order by> +-----------+----+
| user_name |> +-----------+----+
| 李华 |3 |
| zhangsan|2 |
| liming |1 |
+-----------+----+
3 rows in set (0.00 sec)
表中有多少条记录，某列的总和，平均值，最大值，最小值
mysql> select count(*) from user;
mysql> select sum(age) from user;
mysql> select avg(age) from user;
mysql> select max(age) from user;
mysql> select min(age) from user;
分组查询
user表的数据
+----+-----------+-----+----------+----------------+-------------+-------+------+
|> +----+-----------+-----+----------+----------------+-------------+-------+------+
|1 | liming | 0 | 123435 | liming@163.com | 13666666666 | 12.21 | 28 |
|2 | zhangsan| 1 | 229999 | zs@163.com | 13554442907 | 86.21 | 22 |
|3 | 李华 | 0 | 123435 | lihua@163.com| 1366666666| 99.12 | 18 |
|4 | lisi | 1 | 2×××2 | lisi@163.com | 17376756841 | 32.18 | 35 |
|5 | wangwu | 1 | 888888 | wangwu@163.com | 18511111122 | 38.69 | 26 |
+----+-----------+-----+----------+----------------+-------------+-------+------+
统计sex=1时的人数，sex=0时的人数
mysql> select sex,count(*) from user group by sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
| 0 | 2 |
| 1 | 3 |
+-----+----------+
按照sex进行分组，哪组人数大于2
mysql> select sex from user group by sex having count(*)>2;
+-----+
| sex |
+-----+
| 1 |
+-----+
连接查询
内连接查询
-- where 可换成 on
mysql> select s.id,s.name,m.mark from student as s inner join mark as m where m.stu_id=s.id;
+----+---------+------+
|>
+----+---------+------+
|2 | xiaoliu | 66 |
|4 | xiaoli| 77 |
|5 | xiaopan | 88 |
+----+---------+------+
3 rows in set (0.00 sec)
mysql> select m.id,s.name,m.mark from student as s, mark as m where m.stu_id=s.id;
+----+---------+------+
|>
+----+---------+------+
|1 | xiaoliu | 66 |
|2 | xiaoli| 77 |
|3 | xiaopan | 88 |
+----+---------+------+
3 rows in set (0.00 sec)
外连接查询(左连接查询，右连接查询)
左连接查询
mysql> select s.name,m.mark from student as s left join mark as m on m.stu_id=s.id;
+-----------+------+
| name | mark |
+-----------+------+
| xiaoliu | 66 |
| xiaoli | 77 |
| xiaopan | 88 |
| zhaozhang | NULL |
| xiaowang| NULL |
+-----------+------+
右连接查询
mysql> select s.name,m.mark from student as s right join mark as m on m.stu_id=s.id;
+---------+------+
| name | mark |
+---------+------+
| xiaoliu | 66 |
| xiaoli| 77 |
| xiaopan | 88 |
+---------+------+
联合查询
三种方式实现：查询id=2或id=5的记录
mysql> select * from student where>
mysql> select * from student where>
mysql> select * from student where>
子查询
mysql> select>
+----+
|>
+----+
|2 |
|4 |
|5 |
+----+
limit查询
mysql> select * from student limit 2;
+----+-----------+-----+
|>
+----+-----------+-----+
|1 | zhaozhang |26 |
|2 | xiaoliu |27 |
+----+-----------+-----+
mysql> select * from student limit 3,2;
+----+---------+-----+
|>
+----+---------+-----+
|4 | xiaoli|29 |
|5 | xiaopan |30 |
+----+---------+-----+
页:
[1]