常用sql

内容

1 DDL
2 DML
3 binlog
4 其他

DDL

1 建表
CREATE TABLE IF NOT EXISTS `table1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL DEFAULT "",
   `author` VARCHAR(40) NOT NULL DEFAULT "",
   `date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 添加字段
ALTER TABLE table_name ADD COLUMN name VARCHAR(100) DEFAULT NULL COMMENT '姓名' AFTER age;

3 删除字段
ALTER TABLE `table_name` DROP COLUMN age;

4 更改字段
ALTER TABLE `table_name` CHANGE old_name new_name VARCHAR(255) NOT NULL DEFAULT "";

5 建立range分区
CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (2005)
    );

6 建立list分区
CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

7 建立hash分区
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

8 建立key分区
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY KEY(id)
PARTITIONS 12;

DML

1 插入
insert into table (`id`, `name`) values(1,'name');

2 更新
update table_name set name='lisi' where id=1;

3 删除
delete from table_name where id =1;

4 替换
// 1 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据（主键增长）。
// 2. 否则没有此行数据的话，直接插入新数据。
replace into tbl_name(col_name, ...) values(...)

binlog

1 查看当前写入的binlog文件
show master status

2 查看binlog列表
show binary logs

3 查看binlog纤细内容
show binlog events in 'binlog.000003';

4 查看binlog 二进制内容
mysqlbinlog - binlog.000036

5 查看binlog 二进制文件中具体的dml信息
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000036 

6 查看binlog文件存储位置
show variables like "%log_bin%"
my.inf配置文件中查看

其他

权限
查看所有用户权限
select * from mysql.user\G;     

查看当前用户的权限
show grants;    

给gene账号添加权限所有库和表的这些权限；
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD ON *.* TO 'gene'@'%' 
详情：https://juejin.im/post/5e743f966fb9a07ce31f0a72

查看版本
select version()    

查看d1数据库中各个表的大小
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),'MB') as data_size, concat(truncate(index_length/1024/1024,2),'MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'd1' group by TABLE_NAME order by data_length desc;   

查看各个db的大小
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 data_length desc;