MySQL

SQL：Structured Query Language DBMS：DataBase Management System，包括基于文件共享系统、客户机-服务器模式 MySQL is one of the DBMS 本地主机名：localhost，默认端口：3306

SQL语句

# 登录
$ mysql -h localhost -u root -p

# 创建
$ CREATE DATABASE django;
$ SHOW VARIABLES LIKE "CHAR%";
$ SHOW DATABASES;
$ SHOW CREATE DATABASE name;
$ USE django;
$ SHOW TABLES;
$ SHOW CREATE TABLE name;
$ DESCRIBE app_author; = SHOW COLUMNS FROM app_author;
$ SHOW STATUS
$ SHOW GRANTS
$ SHOW ERRORS SHOW WARNINGS
$ HELP SHOW # 要善于用help

# 表操作
$ CREATE TABLE customers(cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_address char(50) NULL, PRIMARY KEY (cust_id))ENGINE=InnoDB;
$ ALTER TABLE customers ADD COLUMN cust_phone char(20);
$ ALTER TABLE customers DROP COLUMN cust_phone;
$ ALTER TABLE customers modify COLUMN cust_phone varchar(1024) NOT NULL;
$ DROP TABLE customers;
$ RENAME TABLE customers TO cus;
$ ALTER TABLE customers DROP PRIMARY KEY, ADD PRIMARY KEY(id, create_time)

# 索引
$ ALTER TABLE CORE_USER ADD INDEX index_name (column_name) # index_name 可省略
$ ALTER TABLE CORE_USER ADD INDEX index_name (column_1, column_2, column_3(4)) # 联合索引
$ ALTER TABLE CORE_USER ADD UNIQUE/PRIMARY KEY (column_name) # 创建唯一，主键索引
$ ALTER TABLE CORE_USER ADD UNIQUE KEY (column_1, column_2) # 联合唯一索引
$ ALTER TABLE CORE_USER DROP INDEX index_name # 删除

# 外键
$ ALTER TABLE CORE_TOKEN DROP FOREIGN KEY foreign_key_name
$ ALTER TABLE CORE_TOKEN ADD CONSTRAINT foreign_key_name FOREIGN KEY (user_id) REFERENCES CORE_USER (id)

# 插入，更新，删除
$ INSERT INTO customers(cust_name, cust_email) VALUES('wcg', '[email protected]'), ('', '');
$ UPDATE customers SET cust_name = 'wcg', cust_email = '' WHERE cust_id =100;
$ DELETE FROM customers WHERE cust_id = 100;

# 查询
$ SELECT email, name FROM app_author;
$ SELECT * FROM app_author;
$ SELECT DISTINCT name FROM app_author; #去重
$ SELECT name FROM app_author LIMIT 4 OFFSET 3; # 从行3开始取4行(从id=4开始) offset = (page-1) * limit
$ SELECT name, email FROM app_author ORDER BY name, name1 DESC; #倒序
$ SELECT name, email FROM app_author WHERE name='wcg'; #范围 BETWEEN 5 AND 10
$ SELECT * FROM app_author WHERE name IS NULL;

# 组合操作负 AND OR IN NOT
$ SELECT * FROM app_author WHERE id IN (1, 2);
$ SELECT name, email FORM app_author WHERE name LIKE ''; # wcg% 以wcg开始，%wcg% 任意位置，_cg 只匹配单个字符
$ SELECT name, email FORM app_author WHERE name REGEXP '';
$ SELECT name, email FORM app_author WHERE Data(timestamp) = '2018-4-17';

# exists
$ SELECT * FORM app_author A WHERE exists(SELECT * FROM customers C WHERE A.id=C.user_id)

# 字段(field) 和column的意思相同，但是计算来的
$ SELECT CONCAT(name, '-by-wcg') FROM app_author; # 拼接字符串
$ SELECT CONCAT(RTrim(name), '-by-wcg') FROM app_author; # 去掉右边所有的空格
$ AS A 别名 SELECT NOW(); # 当前时间
$ LEFT() LENGTH() SOUNDEX() UPPER() LOWER() SUBSTRING() # 函数
$ CURDATE() CURTIME() DATA() DATA_FORMAT() NOW() YEAR() # 日期
$ SELECT * FROM app_author WHERE DATA(create_time) BETWEEN '2018-01-01' AND '2019-01-01';

# 聚合(汇总)函数，COUNT(*), AVG(), MAX(), MIN(), SUM()
$ SELECT COUNT(*) AS NUM FROM app_author;
$ SELECT COUTN(name) FROM app_author; # 只统计name不为null

# 分组 having是分组后过滤
$ SELECT name, COUNT(*) AS NUM FROM app_author GROUP BY name HAVING name <> 'wcg';
$ SELECT name, count(*) as count from app_author group by name having count(*) > 1; # 找出name重复的

# 子查询
$ SELECT ... WHERE .. IN (SELECT ... WHERE ..); # 能用子查询的尽量都用联结

# 联结
$ SELECT vend_name,  prod_name FROM vendors, products WHERE vendors.vend_id = products.vend_id; # sql没有对表进行联结的东西，必须自己做，where子句就是将两个表进行配对
# 笛卡尔积 (两个表没有联结关系)
$ SELECT name, products from app_author, products;
# 内部联结(join 或 inner join， 两张不同的表)
$ SELECT vend_name ,prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
$ SELECT vend_name, prod_name, quantity FROM orders, products, vendors where products.vend_id = vendors.vend_id and orders.prod_id = products.prod_id; # 多张表联结
# 自联结(两张相同的表)
$ SELECT p1.prod_id , p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
# 外部联结(left join rigin join, 包括内联结没有包括的行)
$ SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

# 组合查询 UNION

# 存储过程
$ CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(8,2))BEGIN ... END;
$ CALL ordertotal(10, @total);
$ SHOW PROCEDURE STATUS; # 查看数据库存储的过程
$ SHOW CREATE PROCEDURE  存储过程名
$ DROP PROCEDURE 存储过程名

$ DELIMITER // # 给变分隔符，默认；否则存储过程中有；监视器会无法分辨
$ CREATE PROCEDURE sp_search_customer() (IN p_name VARCHAR(20)) # out p_result INT
$ BEGIN
$ DECLARE tem CHAR(4); # 申明变量
$ SET tem='wcg'; # 给变量赋值
$ IF p_name=NULL OR p_name='' THEN
$ select * from customers;
$ ElSE
$ select * from customers where nam like p_nam;
$ END IF;
$ END//

$ CALL sp_search_customer(""); # 调用
$ CALL sp_search_customer('wcg', @res) # out
$ SELECT @res

# 触发器
$ CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

# 事务处理
$ START TRANSACTION;
$ ...
$ COMMIT;

# 定时任务
$ SHOW VARIABLES LIKE 'event_scheduler'; # 确定event是否开启
$ SET GLOBAL event_scheduler = 1;
$ SHOW EVENTS;
$ SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
$ SHOW CREATE EVENT 任务名
$ create event second_event
$ on sechdule every 1 second
$ on completion preserve disable # 创建后并不开始生效
$ do call sp_search_customer();
$ alter event second_event on completion preserve enable; # 开启定时任务
$ alter event second_event on sechdule every 1 day starts current_timestamp;
$ alter event second_event rename to third_event; # 重命名

# 分区 按时间
$ ALTER TABLE customers DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `create_time`);
$ ALTER TABLE customers PARTITION BY RANGE (YEARWEEK(`create_time`)) (
  PARTITION p201811 VALUES LESS THAN (201811) ENGINE = InnoDB,
  PARTITION p201812 VALUES LESS THAN (201812) ENGINE = InnoDB,
  PARTITION p201813 VALUES LESS THAN (201813) ENGINE = InnoDB,
); # 分区必须包含所有数据范围 yearweek 一年的第几周
$ ALTER TABLE customers ADD PARTITION (PARTITION p201820 VALUES LESS THAN (201820) ENGINE = InnoDB); # 手动添加分区
$ ALTER TABLE customers DROP PARTITION p201820; # 删除
$ SELECT * FROM infomation_schema.PARTITIONS where table_schema='django' and table_name='customers'; # 查看分区大小
$ SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'django' AND TABLE_NAME = 'customers';

$ SHOW CREATE TABLE CORE_USER; # 查看建表语言
$ ALTER TABLE users AUTO_INCREMENT=1001; # id从1001开始
$ ALTER TABLE CORE_USER DROP FOREIGN KEY <外键名> # 删除外键，外键名在上一命令中
$ ALTER TABLE CORE_USER CHANGE COLUMN user_id user VARCHAR(255) NOT NULL; # 重命名
$ SELECT * FORM CORE_USER\G 格式化

## 设置
# 设置 utf8mb4
$ ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # 数据库
$ ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  # 表
$ ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;　＃ 字段

# 远程访问
$ use mysql;
$ update user set host='%' where user = 'root';
$ select host, user from user;
$ netstat -an | grep 3306 # 查看端口状态
$ vim /etc/mysql/mysql.conf.d/mysqld.conf
$ bind-address = 127.0.0.1 # 注释掉
$ systemctl restart mysql.service # 然后在云服务商放行3306端口

# 用户管理
$ USE mysql;
$ SELECT user FROM user;
$ CREATE USER wcg IDENTIFIED BY 'password';
$ DROP USER wcg;
$ SHOW GRANTS FOR wcg;
$ GRANT/REVOKE SELECT ON customers.* TO wcg;
$ SET PASSWORD FOR wcg = Password('');

# 复制表
$ CREATE TABLE customers_bk select * from customers;

# 备份
$ mysqldump -h localhost -P 3306 -u root -p wcg --set-gtid-purged=OFF -database django >django.sql # database 不包含 GTID 信息
$ mysqldump -h localhost -P 3306 -u root -p wcg django user > user.sql # table
$ mysqldump -h localhost -P 3306 -u root -p wcg django user --where="name='wcg'" > user.sql # 筛选条件 “true limit 100” 前100个
# 导入
$ mysql -h localhost -u root -p wcg django < user.sql
$ source user.sql
# 导出excel
$ mysql -h 127.0.0.1 -u root -pwcg --default-character-set=utf8 -e "select * from user" django > user.xls

高级

$ select @@tx_isolation # 查看mysql隔离级别
$ set transaction isolation level # read uncommitted read committed repeatable read serialzable 读未提交 读已提交 可重复读 可序列化