MySQL · SQL | Rao's Blog

连接

1	mysql -h <host> -P <port> -u <user> -p <password>

库

# 查看库
show databases;
show create database 'dbname'

# 创建数据库
create database 'dbname' /*!40100 DEFAULT CHARACTER SET utf8 */

# 删除数据库
drop database 'dbname'

# 查看当前时间、用户名、数据库版本
select now(), user(), version();

表

# 创建表
CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项] 每个字段必须有数据类型，最后一个字段后不能有逗号，TEMPORARY 临时表，会话结束时表自动消失

字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']

-- 字符集
CHARSET = charset_name，如果表没有设定，则使用数据库字符集

-- 存储引擎
ENGINE = engine_name
表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
不同的引擎在保存表的结构和数据时采用不同的方式
MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
InnoDB表文件含义：.frm表定义，表空间数据和日志文件
SHOW ENGINES -- 显示存储引擎的状态信息
SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息

-- 自增起始数
AUTO_INCREMENT = 行数

-- 数据文件目录
DATA DIRECTORY = '目录'

-- 索引文件目录
INDEX DIRECTORY = '目录'

-- 表注释
COMMENT = 'string'

-- 分区选项
PARTITION BY ... (详细见手册)

# 修改表
-- 修改表本身的选项
ALTER TABLE 表名 表的选项
eg: ALTER TABLE 表名 ENGINE=MYISAM;

-- 对表进行重命名
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）

-- 修改表的字段机构（13.1.2. ALTER TABLE语法）
ALTER TABLE 表名 操作名

-- 操作名
ADD[ COLUMN] 字段定义       -- 增加字段
AFTER 字段名          -- 表示增加在该字段名后面
FIRST               -- 表示增加在第一个
ADD PRIMARY KEY(字段名)   -- 创建主键
ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
ADD INDEX [索引名] (字段名) -- 创建普通索引
DROP[ COLUMN] 字段名      -- 删除字段
MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
DROP INDEX 索引名 -- 删除索引
DROP FOREIGN KEY 外键    -- 删除外键

eg. 
CREATE TABLE `testdemo` (
	`id` bigint unsigned NOT NULL AUTO_INCREMENT,
	`name` char(20) NOT NULL DEFAULT '',
  `address` varchar(50),
	`age` tinyint unsigned NOT NULL DEFAULT 0,
	`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
);

# 查看表结构
desc test;
show create table test\G
show table status from 'db_name';

# 复制表
CREATE TABLE 'newname' LIKE 'oldname';
INSERT INTO 'newname' SELECT * FROM 'oldname';

# 修改表存储引擎
ALTER TABLE 'table_name' engine=myisam;

# 删除表
DROP TABLE[ IF EXISTS] 表名

# 清空表数据，truncate 是删除表再创建，delete 是逐条删除
TRUNCATE [TABLE] 表名

# 复制表结构
CREATE TABLE 表名 LIKE 要复制的表名

# 复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表

# 复制旧表的数据到新表(假设两个表结构不一样)
INSERT INTO 新表(字段1,字段2,……) SELECT 字段1,字段2,…… FROM 旧表

# 复制表结构和数据
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

# CRUD
-- 增
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]

-- 查
SELECT 字段列表 FROM 表名[ 其他子句]

-- 删
DELETE FROM 表名[ 删除条件子句]

-- 改
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]

索引

# 删除索引
DROP index idx_name on 'table_name'

# 添加自增主键
ALTER TABLE 'table_name' ADD id bigint(10) PRIMARY KEY AUTO_INCREMENT;

# 查看表索引
SHOW INDEX FROM 'table_name'；

# 添加主键
ALTER TABLE 'table_name' ADD PRIMARY KEY (column)

# 创建唯一索引
ALTER TABLE 'table_name' ADD UNIQUE (column)

# 创建普通索引
ALTER TABLE 'table_name' ADD 'index_name'(column)

# 创建聚合索引
ALTER TABLE 'table_name' ADD INDEX 'index_name' (column1, column2, column3)

视图

# 查询视图
SELECT * FROM information_schema.views;

# 创建视图
create view 'viewname' as 'sql'

# 删除视图
drop view 'viewname'

复杂查询

DISTINCT

去除重复记录，默认为 ALL 选项

LIKE

1	select * from 'table' where field like '%value%'

UNION & UNION ALL

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

注意：默认地，UNION 操作符选取不同的值，如果允许重复的值，请使用 UNION ALL。

子查询

1	select a,b,c from a where d IN (select d from b )

BETWEEN AND

1	select * from table1 where time between time1 and time2

IN

1	select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

ORDER BY

语法：

1 2	SELECT field1, field2,...fieldN FROM table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列，默认情况下，按升序排列。
你可以添加 WHERE…LIKE 子句来设置条件。

1	select * from 'tabname' order by field1,field2 [desc]

统计信息

# 查询数据库总量
select concat(round(sum((data_length + index_length)/1024/1024/1024),2),'GB') as data from information_schema.tables;

# 查询所有库数据量并排序
SELECT table_schema, CONCAT(
TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(
TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;
+--------------------+------------+------------+
| table_schema       | data_size  | index_size |
+--------------------+------------+------------+
| csomo-barcode      | 51872.64MB | 49203.56MB |
| cosmodb            | 30257.57MB | 13855.68MB |
| cosmo_im_1001      | 19769.54MB | 6251.67MB  |
| cosmoimbase        | 17594.54MB | 18239.81MB |
| cosmo_im_1004      | 3141.99MB  | 4099.13MB  |
| cosmo_im_1012      | 1524.87MB  | 1592.03MB  |
| cosmo_im_1010      | 1464.20MB  | 2176.00MB  |
| cosmo_im_1016      | 1092.29MB  | 1543.65MB  |
| cosmo_im_1009      | 793.96MB   | 1612.11MB  |
| cosmo_im_1003      | 662.42MB   | 1417.97MB  |
| cosmo_im_1006      | 200.44MB   | 288.24MB   |
| cosmo_im_1047      | 184.14MB   | 90.46MB    |
| cosmo_im_1038      | 32.15MB    | 46.03MB    |
| mysql              | 22.60MB    | 0.14MB     |
| test               | 7.06MB     | 0.03MB     |
| information_schema | 0.00MB     | 0.00MB     |
| performance_schema | 0.00MB     | 0.00MB     |
+--------------------+------------+------------+

# 查询单个表数据量
select table_name,table_rows,CONCAT(
TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(
TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size from information_schema.tables where TABLE_SCHEMA = 'mqm' and table_name='t_batch';

# 查询单个库中表数据排序
select table_name,table_rows from information_schema.tables where TABLE_SCHEMA= 'employees' order by table_rows desc;
+----------------------+------------+
| table_name           | table_rows |
+----------------------+------------+
| salaries             |    2838426 |
| titles               |     441772 |
| dept_emp             |     331570 |
| employees            |     299600 |
| dept_manager         |         24 |
| departments          |          9 |
| current_dept_emp     |       NULL |
| dept_emp_latest_date |       NULL |
+----------------------+------------+

# 查询单个库中表排序
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', table_rows AS 'Rows', CONCAT(ROUND(data_length/(1024*1024),2),'M') AS 'Data Size',CONCAT(ROUND(index_length/(1024*1024),2),'M') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'M') AS'Total'FROM information_schema.TABLES WHERE TABLE_SCHEMA= 'employees' ORDER BY --total DESC;
+--------------------------------+---------+-----------+------------+--------+
| Table Name                     | Rows    | Data Size | Index Size | Total  |
+--------------------------------+---------+-----------+------------+--------+
| employees.salaries             | 2838426 | 95.63M    | 0.00M      | 95.63M |
| employees.titles               |  441772 | 19.56M    | 0.00M      | 19.56M |
| employees.dept_emp             |  331570 | 11.52M    | 5.52M      | 17.03M |
| employees.employees            |  299600 | 14.52M    | 0.00M      | 14.52M |
| employees.dept_manager         |      24 | 0.02M     | 0.02M      | 0.03M  |
| employees.departments          |       9 | 0.02M     | 0.02M      | 0.03M  |
| employees.current_dept_emp     |    NULL | NULL      | NULL       | NULL   |
| employees.dept_emp_latest_date |    NULL | NULL      | NULL       | NULL   |
+--------------------------------+---------+-----------+------------+--------+

复制

# 取消复制
stop slave;
reset slave all;

# 建立复制
CHANGE MASTER TO 
MASTER_HOST='10.133.7.18',
MASTER_USER='repl',
MASTER_PASSWORD='Changeme_123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-binlog.000297', 
MASTER_LOG_POS=900834433,
MASTER_CONNECT_RETRY=10;