DQL常用操作及常见问题

Leave a reply

DQL: Database Query Language

1、查询某字段去重复

  • select distinct xxxx from xxxx

2、合并字段查询函数concat( )、concat_ws( )

  • select concat(user_name, password) from user;
  • select concat(user_name, password) as user_name_email from user;
  • select concat_ws(‘->’, user_name, password) from user; // 查询结果用’->’分隔符隔开

3、合并字段去重复

  • select distinct concat(user_name, password) from user; // 合并后的结果去重复

4、as的使用

  • select user_name as xxxx from user;// 字段名将以xxx显示

5、常用的几个函数

  • count( )，计数
    • select count(*) from user; // 计算数据总数
  • sum( )，求和
    • select sum(age) from user; // 对age列求和
  • avg( )，求平均值
    • select avg(age) from user; // 求age平均值
  • max( )、min( )，最大值最小值
    • select max(age) from user;

6、group by 报错的问题

mysql> select * from user group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sian.user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

话说这个问题是mysql的某个模式限制了group by的使用，使用select @@global.sql_mode可以查看；

mysql> select @@global.sql_mode;
+---------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------+
|  ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过set @@global.sql_mode的方式将第一个ONLY_FULL_GROUP_BY去掉

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                      |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

注意：这个时候可能还没有生效，退出mysql，需要重新登录才行！！

7、内连查询

  • 两个表中有某个字段具体有相关联性，通过两个表中该字段的关系，查询两表数据；
  • user表中有用户的相关信息，mark表中有成绩的相关信息，mark表中的user_id与user表中的id相关联 
    mysql> select u.id, u.user_name, u.sex, m.mark from user as u inner join mark as m where u.id = m.user_id;
+----+-----------+-----+------+
| id | user_name | sex | mark |
+----+-----------+-----+------+
|  1 | sian      | 男  |   90 |
|  2 | 余西安     | 男  |   88 |
|  3 | Jack      | 男  |   89 |
|  4 | Rose      | 女  |   92 |
|  5 | Sim       | 女  |  100 |
|  6 | Tony      | 男  |   77 |
+----+-----------+-----+------+
6 rows in set (0.00 sec)
  • 简化语句 
    mysql> select user.id, user_name, sex, mark from user, mark where user.id = mark.user_id;
+----+-----------+-----+------+
| id | user_name | sex | mark |
+----+-----------+-----+------+
|  1 | sian      | 男  |   90 |
|  2 | 余西安     | 男  |   88 |
|  3 | Jack      | 男  |   89 |
|  4 | Rose      | 女  |   92 |
|  5 | Sim       | 女  |  100 |
|  6 | Tony      | 男  |   77 |
+----+-----------+-----+------+
6 rows in set (0.00 sec)

8、左连接、右连接查询

  • 左连接以左表为基准，右连接以右表数据为基准 
    mysql> select user.id, user_name, sex, mark from user left join mark on user.id = mark.user_id;
+----+-----------+-----+------+
| id | user_name | sex | mark |
+----+-----------+-----+------+
|  1 | sian      | 男  |   90 |
|  2 | 余西安     | 男  |   88 |
|  4 | Rose      | 女  |   92 |
|  5 | Sim       | 女  |  100 |
|  6 | Tony      | 男  |   77 |
|  3 | Jack      | 男  | NULL |
+----+-----------+-----+------+
6 rows in set (0.00 sec)

mysql> select user.id, user_name, sex, mark from user right join mark on user.id = mark.user_id;
+------+-----------+------+------+
| id   | user_name | sex  | mark |
+------+-----------+------+------+
|    1 | sian      | 男   |   90 |
|    2 | 余西安     | 男   |   88 |
|    4 | Rose      | 女   |   92 |
|    5 | Sim       | 女   |  100 |
|    6 | Tony      | 男   |   77 |
| NULL | NULL      | NULL |   99 |
+------+-----------+------+------+
5 rows in set (0.00 sec)

9、联合查询

  • union关键字 
    mysql> select user_name from user union select mark from mark;
+-----------+
| user_name |
+-----------+
| sian      |
| 余西安     |
| Jack      |
| Rose      |
| Sim       |
| Tony      |
| 90        |
| 88        |
| 92        |
| 100       |
| 77        |
| 99        |
+-----------+
12 rows in set (0.00 sec)
  • union来代替where条件中的or 
    mysql> select id, user_name, email from user where id = 2 or id = 4;
+----+-----------+-----------------+
| id | user_name | email           |
+----+-----------+-----------------+
|  2 | 余西安     | yusian@163.com  |
|  4 | Rose      | rose@yusian.com |
+----+-----------+-----------------+
2 rows in set (0.00 sec)

mysql> select id, user_name, email from user where id = 2 union select id, user_name, email from user where id = 4;
+----+-----------+-----------------+
| id | user_name | email           |
+----+-----------+-----------------+
|  2 | 余西安     | yusian@163.com  |
|  4 | Rose      | rose@yusian.com |
+----+-----------+-----------------+
2 rows in set (0.00 sec)
  • union连接的前后查询中，查询字段数目要保持一致

10、子查询

  • 将一个查询结果做为另外一个查询的条件；
  • where xxx in (select …..)，mark中没有id为3的user成绩，因此查询出来的结果就是这样的 
    mysql> select * from user where id in (select user_id from mark);
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
| id | user_name | sex | password                         | email           | address | fee     | age | create_at           |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
|  1 | sian      | 男  | e10adc3949ba59abbe56e057f20f883e | yusian@163.com  | NULL    | 1200.31 |  32 | 2019-04-21 20:31:17 |
|  2 | 余西安     | 男  | c4ca4238a0b923820dcc509a6f75849b | yusian@163.com  | NULL    |    1.00 |  33 | 2019-04-21 17:12:55 |
|  4 | Rose      | 女  | eccbc87e4b5ce2fe28308fd9f2a7baf3 | rose@yusian.com | NULL    |    0.00 |  18 | 2019-04-22 10:46:41 |
|  5 | Sim       | 女  | a87ff679a2f3e71d9181a67b7542122c | sim@yusian.com  | NULL    |    0.00 |  28 | 2019-04-22 10:46:41 |
|  6 | Tony      | 男  | c4ca4238a0b923820dcc509a6f75849b | tony@yusian.com | NULL    |    0.00 |  22 | 2019-04-21 20:41:26 |
+----+-----------+-----+----------------------------------+-----------------+---------+---------+-----+---------------------+
5 rows in set (0.00 sec)

11、查看当前库中的表

  • show table status
  • show table status\G // 按列显示

Leave a Reply