MySQL常用操作命令

MySQL 终端操作常用命令。

1、连接数据库

mysql -uroot -p

2、展示mysql的数据库

mysql> show databases;

创建数据库：

mysql> create database test;

3、选用数据库

> use test;

4、列出数据库的表

mysql> show tables;

5、创建表

mysql> CREATE table msg_per_hr (
       hr datetime not null,
       cnt int unsigned not null,
       primary key(hr)
       );

6、插入表语句

insert into msg_per_hr (hr, cnt) values(‘2017-09-02 14:02:30’, 500);

7、使用LEFT截取字符串输出

注意：LEFT方法计算字段的长度并截取字符串长度

mysql> select LEFT(hr, 14) as sim_hr FROM msg_per_hr

8、使用CONCAT拼接字符串

CONCAT 方法可以拼接字段,如，我们可以将时间字段的分秒先截去，然后使用CONCAT拼接整时间点的字符串。

mysql> select CONCAT(LEFT(hr, 14), '00:00') as sim_hr FROM msg_per_hr

9、interval 函数

MySQL函数大全 及用法示例

mysql> select sum(cnt) from msg_per_hr where hr between concat(left(now(), 14), '00:00') - interval 23 hour and concat(left(now(), 14), '00:00') -interval 1 hour;
+----------+
| sum(cnt) |
+----------+
|     2524 |
+----------+
1 row in set (0.06 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-09-02 16:05:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now() - interval 23 hour;
+--------------------------+
| now() - interval 23 hour |
+--------------------------+
| 2017-09-01 17:06:19      |
+--------------------------+
1 row in set (0.00 sec)

mysql> select now() - 23 hour;
+----------------+
| hour           |
+----------------+
| 20170902162023 |
+----------------+
1 row in set (0.00 sec)

mysql>

从上的测试，我们可以看出 interval 方法在进行加减法操作时,可以有效的计算日期。

10、比较两个字段的大小

SELECT CASE WHEN a < b THEN b ELSE a END AS maxs FROM tb

或者这样：
if(a>b, a, b)

实际应用（比较money和wms_money最小值，并将值相加）：
SELECT year,month,sum(if(money < wms_money, money, wms_money)) as actual_money FROM `shop_express_fy` WHERE year = 2017 AND month = 7 AND invoice_no IN (333300396,333300457);

11.mysql重启

我在Ubuntu14 64位系统中安装mysql后，后来通过mysql -u用户名 -p密码 的命令连接

        Mysql数据库时，报错"can't connect to local mysql serverthrough  socket'/var/run/mysqld/mysqld.sock'"

解决方法1：
在不重新安装mysql的情况下，通过如下命令来解决，具体的就是先停止mysql服务，再重新开启mysql服务

cd /etc/init.d
sudo service mysql stop
sudo service mysql start 

12.两字段相减

SELECT *, send_year - send_month as diff FROM `cross_month_tbl`;

这里举一个简单的例子：

或者差值作为条件：

$sql = "SELECT * from spkcb WHERE goods_id = '13735' AND color_id = '3741' AND size_id = '127' AND (sl - sl1 - sl2) > 0 ";

  $sql = "SELECT ck_id, (sl - sl1 - sl2) as use_count from spkcb WHERE goods_id = '{$goods_id}'
 AND size_id = '{$size_id}' AND color_id = '{$color_id}'  HAVING use_count > 0";

13.GROUP_CONCAT函数

GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成。

示例：
对月份进行分组，并将分组月份的id获取到，如果用这个方法GROUP_CONCAT最好不过了。

SELECT send_month, GROUP_CONCAT(id)as ids FROM `cross_month_tbl` GROUP BY send_month;

结果：

相关文章：MySQL中函数CONCAT及GROUP_CONCAT

14.show processlist SQL杀进程

3点钟刚睡下, 4点多, 同事打电话告诉我用户数据库挂掉了. 我起床看一下进程列表.

mysql>show processlist;

出来哗啦啦好几屏幕的, 没有一千也有几百条, 查询语句把表锁住了, 赶紧找出第一个Locked的thread_id, 在mysql的shell里面执行.

mysql>kill thread_id;

kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下.

#!/bin/bash
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt

for line in `cat locked_log.txt | awk '{print $1}'`
do 
   echo "kill $line;" >> kill_thread_id.sql
done

现在kill_thread_id.sql的内容像这个样子

kill 66402982;
kill 66402983;
kill 66402986;
kill 66402991;
.....
好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.

mysql>source kill_thread_id.sql

当然了, 也可以一行搞定

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
   mysqladmin kill ${id}
done

原文地址：mysql中kill掉所有锁表的进程

15.MYSQL中UNIX时间戳与日期的转换

select FROM_UNIXTIME(1464973385.641,'%Y-%m-%d %H:%i:%s');
select UNIX_TIMESTAMP('2016-06-04 01:03:05');

结果：
FROM_UNIXTIME(1464973385.641,'%Y-%m-%d %H:%i:%s')
2016-06-04 01:03:05

UNIX_TIMESTAMP('2016-06-04 01:03:05')
1464973385

示例：

SELECT sku, sl, hs_sl, FROM_UNIXTIME(create_time,'%Y-%m-%d %H:%i:%s')  as add_time, ckdm FROM `sku_stock_go_wms_log` where  create_time>=UNIX_TIMESTAMP('2018-04-01')  
and sku='X8B2012464135'
limit 100

16.MySQL字符串大小写转换函数

MySQL字符串大小写转换需要用到MySQL字符串大小写转换函数，MySQL 字符串大小写转化函数有两对： lower(), uppper() 和 lcase(), ucase()：

mysql> select lower('DDD');  
+--------------+  
| lower('DDD') |  
+--------------+  
| ddd          |  
+--------------+  
mysql> select upper('ddd');  
+--------------+  
|upper('ddd') |  
+--------------+  
| DDD          |  
+--------------+  
mysql> select lcase('DDD');  
+--------------+  
| lcase('DDD') |  
+--------------+  
| ddd          |  
+--------------+  
mysql> select ucase('ddd');  
+--------------+  
| ucase('ddd') |  
+--------------+  
| DDD          |  
+--------------+ 

通常情况下，我选择 lower(), upper() 来实现MySQL转换字符串大小写，因为这和其他数据库中函数相兼容。

17.NOT EXISTS 用法

SELECT order_sn, sd_id  FROM order_list o WHERE o.lylx = 'jd' AND o.order_status <> 0  AND o.create_time >= '2018-04-25 00:00:00' AND ifnull(invoice_no, '') = ''  
AND o.shipping_name <> 'jd' AND NOT EXISTS (SELECT order_sn FROM jingdong_waybill j WHERE j.order_sn = o.order_sn) limit 100

---