数据库

连接

mysql -h 10.0.0.xxx -P 3306 -u root -p

创建

create database test;
show databases;

删除

drop database test;

选择

use test;

数据表

创建

create table if not exists `students` (
  `id` int unsigned auto_increment,
  `name` varchar(50) not null,
  `birth_date` datetime not null,
  `sex` varchar(10) not null,
  `age` int not null,
  primary key(`id`)
) engine=InnoDB default charset=utf8;

show tables;

删除

drop table students

插入数据

insert into students (name, birth_date, sex, age) values ("jk", "1993-05-28", "男", 29);

查询数据

default

select * from students

columns

show columns from students

where

select * from students where name="captain"

like

select * from students where name like "j%"

union

# 去掉重复的值
select name from students union select name from person;
# 包含重复的值
select name from students union all select name from person;

order by

select * from students order by age desc

group by

select name, count(*) from students group by name

join

# 内连接
select * from students a inner join person b on a.name=b.name
# 左连接。以左表为参照,显示所有数据，右表中没有则以null显示
select * from students a left join person b on a.name=b.name
# 右连接。以右表为参照显示数据，左表中没有则以null显示
select * from students a right join person b on a.name=b.name

修改数据

update

update students set name="jk_best" where id=1

delete

delete from students where id=3

事务

use test;
create table transaction_test(id int(5)) engine=InnoDB;
select * from transaction_test;
begin; # 开始事务
insert into transaction_test value(2);
insert into transaction_test value(3);
commit; # 确认事务
select * from transaction_test;
begin;
insert into transaction_test value(4);
rollback; # 回滚
select * from transaction_test;

ALTER 修改表

use test;
show columns from transaction_test;
alter table transaction_test add column name varchar(50) # 添加字段
alter table transaction_test add column name varchar(50) after xxx # 添加字段在某个字段后面
alter table transaction_test drop column xxx; # 删除字段
alter table transaction_test modify column name varchar(30); # 修改字段类型
alter table transaction_test change column name name2 varchar(60); # 修改字段类型并重命名
alter table transaction_test alter id set default 0;  # 修改字段默认值 - 若本身存在默认值，需要先删除
alter table transaction_test alter id drop default; # 删除字段默认值
alter table transaction_test rename to transaction_test_2; # 重命名

索引

索引查询快，更新慢

CREATE INDEX name_index ON transaction_test_2(name2(10)); # 创建普通索引
ALTER TABLE transaction_test_2 ADD INDEX indexName(columnName); # 添加普通索引
DROP INDEX indexName ON transaction_test_2; # 删除普通索引
SHOW INDEX FROM transaction_test_2 \G; # 显示索引

ALTER TABLE transaction_test_2 ADD PRIMARY KEY (id); # 添加主键
ALTER TABLE transaction_test_2 DROP PRIMARY KEY; # 删除主键

复制表

方法1

# step1 - 获取数据表的完整结构
SHOW CREATE TABLE transaction_test_2 \G; 
# step2 - 修改表名，执行 CREATE TABLE
# step3 - 插入数据
insert into transaction_test_2_new (x, y, z) select x, y, z from transaction_test_2

方法2

# step1
CREATE TABLE transaction_test_3 LIKE transaction_test_2;
# step2
INSERT INTO transaction_test_3 SELECT * FROM transaction_test_2;
# step3，只复制某些字段
CREATE TABLE transaction_test_4 AS (
  SELECT id, sex FROM transaction_test_3
);

临时表

# TODO 

导出数据

# TODO 

导入数据

# TODO 

处理重复数据

# TODO 

SQL 模板

注意大小写 与 charset

CREATE TABLE IF NOT EXISTS `packages`(
   `id` INT UNSIGNED AUTO_INCREMENT COMMENT '包的Id',
   `app_id` INT NOT NULL DEFAULT 0 COMMENT '所属应用ID',
   `service_id` INT NOT NULL DEFAULT 0 COMMENT '所属服务ID',
   `version`   VARCHAR(128)  NOT NULL COMMENT '版本',
   `down_url`  VARCHAR(1024)  NOT NULL COMMENT '下载地址',
   `comments`  VARCHAR(1024)  NOT NULL COMMENT  '备注',
   `update_time` INT   COMMENT '最新更新时间',
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

参考

zhuanlan.zhihu.com/p/42688335

c.biancheng.net/mysql/