mysql 常见操作

转移：https://gitee.com/chenlushun12/ytx-api/tree/master/blog/mysql

日常运维

一些通用操作

-- 查看mysql版本
select version();

-- 查看数据文件保存目录
show variables like "%datadir%";

-- mysql修改数据库时间
set global time_zone = '+8:00';
set time_zone = '+8:00';
flush privileges;

-- 查看存储引擎
show table status from ytx_combo where name='combo';

mysql中的常用工具

# 查找my.cnf的文件地址
mysql --help|grep my.cnf

# 查看mysql的my.conf文件所在位置
mysql -p123456 --help |grep my.cnf

# 登录mysql
mysql -h 127.0.0.1 -P3306 -u root -p

mybatis

# mybatis批量修改
&allowMultiQueries=true

查询建表语句

show create table film
show create table store;
alter table tt add (age INT); --添加一列
alter table tt modify age VARCHAR(30); --修改列数据类型
alter table tt drop age; --删除列
alter table tt change column  tel  age int ; --修改表列名称
alter table test rename test1; --修改表名  

创建索引

SQL优化

1、查看各种SQL执行频率
show status;
两种方式定位执行效率比较低的SQL语句

A、慢查询日志：

--查询默认慢查询
show variables like ”%long%”
--设置慢查询时间
set global long_query_time=2
--启动满查询
set global slow_query_log=’ON’; 
--查看满查询相关，是否开启、日志存储位置慢查询日志分析工具
show variables like "%slow%"; 
--slow记录最多的10个语句
mysqldumpslow -s r -t 10  /slowquery.log
--按照时间排序前10中含有"left join"的
mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log

B、当前mysql在进行的线程，包括线程的状态、是否锁表

show processlist

2、通过explain分析效率低SQL的执行计划

SHOW STATUS LIKE "%Handler_read%"; //查看索引使用情况

3、两个简单实用的优化方法：
A、定期分析表和检查表
分析表

analyze table store;

检查表

check table store;

B、定期优化表

optimize table store;

4、常用的SQL优化
优化数据库对象：

1. SELECT * from combo_item PROCEDURE ANALYSE() //优化表的数据类型，最适合大表优化
2. 使用中间表提高统计查询速度
3. 逆规范化
4. 通过拆分提高表的访问效率

锁问题
MyISAM
show status like ‘table_locks_%’;
Table_locks_waited 的值比较高的情况下，说明存在着较严重的表级锁争用情况

获取Innodb 行锁争用情况
show status like “innodb_row_lock_%%”;

权限与安全

内嵌mysql

https://github.com/wix/wix-embedded-mysql#customizing-mysqld-settings

MysqldConfig config = aMysqldConfig(v5_7_latest)
                .withCharset(UTF8)
                .withPort(3406)
                .withUser("ok", "123456")
                .build();
        EmbeddedMysql mysqld = anEmbeddedMysql(config)
                .addSchema("ytx_trade", classPathScript("/001_init.sql"))
                .start();

开发篇
7、表类型（存储引擎）的选择
查看表支持的存储引擎

show variables like 'table_type';
show engines \G;
show variables like 'have%';

7.2.2
innoDB
innoDB具有提交、回滚、奔溃恢复能力的事物安全，比myisam的写效率差。占用更多的时间保留数据和索引
自动增长列
create table antoincre_demo(i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;
i即可以手动插入、也可以自动增长（当i的值为0活着null）
alter table antoincre_demo auto_increment=4; --设置默认开始值，数据库重启失效
select last_insert_id(); --查询最后一个id
外键约束
存储方式

8、选择合适的数据类型

8.1 char 和varchar
char 固定长度
varchar 可变长度

8.2 text和blob

8.3 浮点数和定点数
float
8.4 日期类型的选择

9 字符集

show character set --查看支持的字符
show variables like 'character_set_server'; --查看服务器当前的字符

10 索引的设计和使用

10.1 索引
10.2 索引的设计原则

12 存储过程和函数

13 触发器
13.1创建触发器

create triggers ins_film alter insert on ytx_api for each row begin insert into

13.2、删除触发器

13.3、触发器的查看

show triggers \G
select * from information_schema.trigger

14、事物控制和锁定语句
14.1 lock table 和 unlock table

15 SQL中的安全问题

sql注入： or   ／*    #

16 SQL Model 及其相关问题

select @@sql_mode;

三大范式
原子性
二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
非主键列必须直接依赖于主键，不能存在传递依赖

mybatis批量修改

&allowMultiQueries=true

mysql的操作

mysql --help|grep my.cnf

mysql实现rownum功能

 SELECT @rownum:=@rownum+1 as rownum,a.unit_price,sum(a.number) as sales,a.item_sku_id,a.item_name 
FROM  (SELECT @rownum:=0) r, ytx_trade.order_item a left join purchase_order  b on a.purchase_order_id=b.id
where  a.seller_account_id=1830 and b.paid_at>date_sub(now(), interval 30 day)
group by a.item_sku_id 
order by sales desc limit 20;

按多个条件排序：

order by sales desc,price desc

日期函数：

select now()
select curdate();
date_sub(curdate(), interval 30 day) #日期间隔

sql

• 创建表的时候自动更新时间

DROP TABLE IF EXISTS gift_message_log;
create table  gift_message_log (
  id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  created_at TIMESTAMP COMMENT '创建时间',
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE    CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COMMENT = 'test';

• 给MYSQL时间加上指定的秒

 select starttime + interval 10 second

• Modify a field in a table using another table field.

update cart_line cl ,seller s SET cl.seller_type=s.type \n
WHERE cl.seller_account_id=s.account_id;

• 在执行插入的时候更新字段
  INSERT … SELECT Syntax

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
# 或者
INSERT INTO table(field1, field2, fieldn) SELECT 'field1', 'field2', 'fieldn' FROM DUAL 
WHERE NOT EXISTS(SELECT field FROM table WHERE field = ?)

The following conditions hold for INSERT … SELECT statements:

• Specify IGNORE to ignore rows that would cause duplicate-key violations.

• The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. However, you cannot insert into a table and select from the same table in a subquery.

• When selecting from and inserting into the same table, MySQL creates an internal temporary table to hold the rows from the SELECT and then inserts those rows into the target table. However, you cannot use INSERT INTO t … SELECT … FROM t when t is a TEMPORARY table, because TEMPORARY tables cannot be referred to twice in the same statement. See Section 8.4.4, “Internal Temporary Table Use in MySQL”, and Section B.5.6.2, “TEMPORARY Table Problems”.

• AUTO_INCREMENT columns work as usual.

• To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts for INSERT … SELECT statements (see Section 8.11.3, “Concurrent Inserts”).

• To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.

最靠近where的最先被使用，也就是where条件是按照顺序依次被使用的。

執行策略

1. 在 FROM 子句中產生資料表的聯結。若使用明確的 JOIN 語法，則 JOIN 的結果就很明顯。如果 FROM 字句中含有多個以逗號分隔的資料表名稱，這就是資料表的隱含式跨產品聯結。
2. 若有 WHERE 子句，便對步驟 1 所得結果的資料列執行搜尋條件，只保留符合條件的資料列。
3. 如果 SELECT 子句中沒有任何彙總運算，而且如果沒有 GROUP BY 子句，則跳到步驟 7。
4. 如果有 GROUP BY 子句，則會將步驟 2 運算所得的資料列分為多個群組，因此所有群組資料行在每一個群組的所有資料列中具有相同的值。如果沒有 GROUP BY 子句，則將所有資料列放入一個群組中。
5. 若有指定 HAVING 子句，則針對步驟 4 所產生的每一個群組套用 HAVING 子句，只有符合 HAVING 子句的群組才會被保留。
6. 對於步驟 5 所產生的每一個群組，根據該群組評估來自 SELECT 子句中的選取清單，以便只產生一個結果資料列。
7. 若 SELECT 子句包含 DISTINCT 關鍵字，則在步驟 6 所得的結果中刪除重複的資料列。
8. 如果有 ORDER BY 子句，則根據其順序運算式所指定的方式排列步驟 7 的結果。

SQL优化

外键约束

create  table user (
  id int,
  name varchar(30),
  primary key (id)
) engine=innodb;

create table book(
  id int,
  book_name varchar(30),
  user_id int ,
  primary key (id),
  constraint fk_userid foreign key (user_id) references user(id)
)engine =innodb;

insert into boot values (2,'ok',1);