MySql常用命令

数据库的操作

1、新建数据库并导入脚本

[root@iZbp14o9sv2a0d95lkfnabZ ~]# mysql -u root -password  // 登录

// 创建数据库并设置字符集
mysql> CREATE DATABASE dbName CHARACTER SET utf8 COLLATE utf8_general_ci;

// 指定使用哪个数据库
mysql> use dbName;

 // 导入脚本
mysql> source /data/dbName.sql 

// 将库赋予给某个用户
mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE ON dbName.* to 'dbUser'@'%';

// 刷新权限
mysql> flush privileges;

2、创建新用户并指定数据库

-- 创建新用户并设置密码
create user 'user' identified by 'psw';
-- 为新用户分配权限并指定数据库
grant all privileges on dbName.* to 'dbUser'@'%';

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE ON dbName.* to 'dbUser'@'%';

-- 刷新权限
mysql> flush privileges;

-- 1142错误解决方法：没有权限

use mysql;
select * from user where user='w2user';
update user set Select_pri='Y' where user='w2user';
flush privileges;

3、修改root密码

update user set password=PASSWORD("你的密码") where User = 'root';

4、字段类型修改

ALTER TABLE tableName modify column money DECIMAL(11,6) ;

5、字段添加

-- 数据库表字段添加
-- AFTER：新字段位置在xx字段之后
ALTER TABLE 表名 ADD COLUMN 字段名 INT(1) DEFAULT 0 COMMENT '字段描述' AFTER IMPORTED

6、数据表说明

SELECT
    t.TABLE_NAME AS 表名,
    t.COLUMN_NAME AS 字段名,
    t.COLUMN_TYPE AS 数据类型,
    CASE IFNULL(t.COLUMN_DEFAULT,'Null') 
        WHEN '' THEN '空字符串' 
        WHEN 'Null' THEN 'NULL' 
        ELSE t.COLUMN_DEFAULT END  AS 默认值,
    CASE t.IS_NULLABLE WHEN 'YES' THEN '是' ELSE '否' END AS 是否允许为空,
    t.COLUMN_COMMENT AS 字段说明
FROM information_schema.COLUMNS t 
WHERE t.TABLE_SCHEMA='dbName' AND t.TABLE_NAME='tableName';

常用函数

1、DATE_FORMAT

-- %Y-%m-%d %H:%i
SELECT 
    DATE_FORMAT(cg.date,'%Y') AS 'year',
FROM 
  T_CG cg
WHERE 
    DATE_FORMAT(cg.date, '%Y') = 2020

2、判断某字符串是否包含某个字符串

-- 方法1：使用通配符%。
select * from user where hobby like "%yanggb%";

-- 字符串函数find_in_set() 用于返回str2中str1所在的位置索引
-- SELECT * FROM users WHERE find_in_set('字符', 字段名);
select * from user where find_in_set(hobby, 'yanggb1,yanggb2,yanggb3');

-- 使用MySQL提供的字符串函数locate()函数。
-- MySQL还提供一个字符串函数locate(substr,str)函数，用于返回str中substr所在的位置索引，如果找到了，则返回一个大于0的数，否则返回0。
select * from user where locate('yanggb', hobby ) > 0;

3、CONCAT函数

-- CONCAT()函数需要一个或多个字符串参数，并将它们连接成一个字符串。CONCAT()函数需要至少一个参数，否则会引起错误。
-- CONCAT()函数在连接之前将所有参数转换为字符串类型。如果任何参数为NULL，则CONCAT()函数返回NULL值。

mysql> SELECT CONCAT('MySQL','CONCAT');
+--------------------------+
| CONCAT('MySQL','CONCAT') |
+--------------------------+
| MySQLCONCAT              |
+--------------------------+

mysql> SELECT CONCAT('MySQL',NULL,'CONCAT');
+-------------------------------+
| CONCAT('MySQL',NULL,'CONCAT') |
+-------------------------------+
| NULL                          |
+-------------------------------+

4、CONCAT_WS函数：使用分隔符连接字符串

-- CONCAT_WS()函数将两个或多个字符串值与预定义的分隔符相连接。
-- 语法 CONCAT_WS(seperator,string1,string2, ... );
mysql> SELECT CONCAT_WS(',','Max','Su');
+---------------------------+
| CONCAT_WS(',','Max','Su') |
+---------------------------+
| Max,Su                    |
+---------------------------+

-- 当且仅当作为分隔符的第一个参数为NULL时，CONCAT_WS函数才返回NULL
-- 与CONCAT函数不同，CONCAT_WS函数在分隔符参数之后跳过NULL值。 换句话说，它忽略NULL值

mysql> SELECT CONCAT_WS(NULL ,'Jonathan', 'Minsu');
+--------------------------------------+
| CONCAT_WS(NULL ,'Jonathan', 'Minsu') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

5、生成序号

-- 结果生成序号
set @rank = 0;
SELECT @rank := @rank + 1 AS 'index',

6、相同组字段拼接

select 
    vin,typeName,GROUP_CONCAT(img SEPARATOR ';') 
    imgs,update_date 
from 
    custom_check_in_martin 
group by 
    vin,typeName

7、几个删除区别

1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.
 以后要新增数据是不可能的,除非新增一个表。
drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)
索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
    
2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。
与drop不同的是,只是清空表数据而已。
注意:truncate 不能删除行数据,要删就要把表清空。
   
3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，
并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
 truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）
    
 4、truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。
如果要删除表定义及其数据，请使用 drop table 语句。  
    
5、对于由foreign key约束引用的表，不能使用truncate table ，
而应使用不带where子句的delete语句。由于truncate table 记录在日志中，
所以它不能激活触发器。
    
6、执行速度，一般来说: drop> truncate > delete。
    
7、delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，
事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop 是数据库定义语言(ddl)，
操作立即生效原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

备份还原语句

-- 导出
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
-- 1. 导出一张表
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
-- 2. 导出多张表
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
-- 3. 导出所有表
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
-- 4. 导出一个库
　　mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
-- 可以-w携带WHERE条件
-- 导入
-- 1. 在登录mysql的情况下：
　　source  备份文件
-- 2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

分页优化

1、示例

-- 优化limit分页
-- 反例：
select id，name，age from employee limit i，n

-- i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
-- n：为查询结果返回的数量

-- 正例：
-- 方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.

-- 方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

-- 方案三：在业务允许的情况下限制页数：

-- 理由：
-- 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，
-- 而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的。
-- 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，
-- 效率提升不少。
-- 方案二使用order by+索引，也是可以提高查询效率的。
-- 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。
-- 因为绝大多数用户都不会往后翻太多页。

针对limit 优化有很多种方式

• 前端加缓存、搜索，减少落到库的查询操作。比如海量商品可以放到搜索里面，使用瀑布流的方式展现数据，很多电商网站采用了这种方式。
• 优化SQL 访问数据的方式，直接快速定位到要访问的数据行。
• 使用书签方式 ，记录上次查询最新/大的id值，向后追溯 M行记录。 对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

2、延迟关联

-- 优化前
root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| id  | select_type | table           | type  | possible_keys | key            | key\_len | ref  | rows   | Extra   |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
| 1  | SIMPLE      | relation    | range | ind\_endtime   | ind\_endtime | 9       | NULL | 349622 | Using                                                                                                                       where; Using filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----+
1 row in set (0.00 sec)

-- 优化后
root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end\_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
3 rows in set (0.36 sec)

3、使用书签的方式

-- 首先要获取复合条件的记录的最大 id和最小id(默认id是主键)
select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

--其次 根据id 大于最小值或者小于最大值 进行遍历
select xx,xx from t 
where kid=2333 and type=1 and id >=min_id order by id asc limit 100; 

select xx,xx from t 
where kid=2333 and type=1 and id <=max_id order by id desc limit 100;