MySQL查询篇
更多查询请参照我的有道笔记及友人博客

复合条件查询链接

多表查询链接

参考链接

语句执行顺序问题
一、sql执行顺序 、

(1)from
(2) on
(3) join
(4) where
(5)group by
(6) avg,sum….
(7)having
(8) select
(9) distinct
(10) order by
也就是说,我们每次执行的SQL语句,都是从FROM开始的。



查询所有表
show tables;

查询表结构
desc 表名称
查询表中所有字段信息:
mysql> show full columns from a_account_change;



查询两个a_account_change表中两个字段memberId,changeTime
mysql> select memberId,changeTime from a_account_change;
查询表中两个字段作为where条件及like 条件
mysql> select memberId,changeTime from a_account_change where memberId=104 and changeTime like '2017-10-02%';


查询表中充值金额大于1的充值记录与用户ID


mysql> select memberId,addFunds from a_member_account where addFunds>1;


查询字段名 updateDate 为空的记录
mysql> select * from a_member_account where updateDate is null;


查询字段名 updateDate 不为空的记录
mysql> select * from a_member_account where updateDate is not null;
查询表中,账户余额大于1,充值记录大于1
mysql> select memberId,accountBalance,addFunds from a_member_account where accountBalance>1 and addFunds>1;
查询表中账户金额大于1,充值记录1到1000的用户
mysql> select memberId,accountBalance,addFunds from a_member_account where accountBalance>1 and addFunds between 1 and 1000;
查询f_name以b字母开头,y字母结尾的所有记录
SELECT * FROM fruits WHERE f_name LIKE 'b%y';
查询memberId,去除重复的ID
mysql> select distinct memberId from a_member_account;
统计数据表中包含的记录行的总数
mysql> SELECT COUNT(*) FROM a_member_account;
+----------+
| COUNT(*) |
+----------+
| 1381 |
+----------+
1 row in set (0.00 sec)

mysql>
SUM()是一个求总和的函数,返回指定列值的总和
mysql> select sum(addFunds) from a_member_account;
+----------------+
| sum(addFunds) |
+----------------+
| 4336541.292100 |
+----------------+
1 row in set (0.00 sec)
查询表中最大值
mysql> select max(addFunds) from a_member_account;
+---------------+
| max(addFunds) |
+---------------+
| 491872.000000 |
+---------------+
1 row in set (0.01 sec)
查询表中最小值
mysql> select min(addFunds) from a_member_account;
+---------------+
| min(addFunds) |
+---------------+
| 0.000000 |
+---------------+
1 row in set (0.00 sec)
查询mysql用户
mysql> desc mysql.user;
授权usrabc查询、写入、删除、更新权限 所有库表,任何地方登陆
GRANT SELECT, INSERT, DELETE,UPDATE ON *.* TO 'usrabc'@'%';
查询拥有查询、写入、删除、更新权限的用户
mysql> SELECT user, host,select_priv, insert_priv, update_priv,delete_priv from mysql.user;
结果


刷新数据库
mysql> flush privileges;
显示授权用户
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E83154BBA09E3A798817DACCAD9C97E0C4FFA453' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看MYSQL数据库中所有用户
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+--------------------------------------------------------------------+
| query |
+--------------------------------------------------------------------+
| User: 'backup'@'%'; |
| User: 'root'@'119.9.105.153'; |
| User: 'root'@'127.0.0.1'; |
| User: 'root2'@'13.114.194.241'; |
| User: 'root'@'::1'; |
| User: 'amoeba'@'ip-172-31-18-208.ap-northeast-1.compute.internal'; |
| User: 'root'@'ip-172-31-18-208.ap-northeast-1.compute.internal'; |
| User: 'amoeba'@'ip-172-31-18-4.ap-northeast-1.compute.internal'; |
| User: 'root'@'ip-172-31-18-4.ap-northeast-1.compute.internal'; |
| User: 'root'@'ip-172-31-19-67.ap-northeast-1.compute.internal'; |
| User: ''@'ip-172-31-29-241.ap-northeast-1.compute.internal'; |
| User: 'root'@'ip-172-31-29-241.ap-northeast-1.compute.internal'; |
| User: ''@'localhost'; |
| User: 'root'@'localhost'; |
+--------------------------------------------------------------------+
14 rows in set (0.00 sec)
查看数据库中具体backup用户的权限
mysql> show grants for 'backup'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for backup@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'backup'@'%' IDENTIFIED BY PASSWORD '*667B16FFC365FCBC27BF9F38981A055BDC037E2A' |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

利用函数DATE_SUB(CURDATE() ,比对changeTime字段的时间取出15天前的数据条目数
mysql>  select memberId,changeTime,changeId,count(*) from a_account_change where DATE_SUB(CURDATE(), INTERVAL 15 DAY) >= date(changeTime);

删除指定主键ID数据
[root@ip-172-31-30-218 centos]# mysql -u root -pPASSWORD  -h IP -P 3306 -e "delete from amoeba101.a_account_change where changeId='99998'   LIMIT 5000;"

打赏作者

3 comments to “MySQL常用查询操作详解(查询篇)”

You can leave a reply or Trackback this post.
  1. 沙丘 - 2017年10月17日 回复

    感谢博主分享,查询语句工作中非常实用,谢谢。

  2. Aijoabnehc - 2017年10月17日 回复

    读大佬文章,品味精彩 IT 生活

  3. 远志 - 2017年12月5日 回复

    这个可以啊老哥

Leave a Reply

Your email address will not be published.