MySQL登录

-V, --version  # 查看版本信息
-u  #用户名
-p  #密码
-h  #服务器地址
-P  #端口号
--prompt '>>>' #修改提示符为>>>

mysql -uroot -p -h127.0.0.1 -P3306  #h,P均为默认设定，可以不写
exit/quit/\q  #退出

prompt >>>  #修改提示符为>>>
#特殊提示符有：\D,完整日期；\d,当前数据库；\h,服务器名称；\u，当前用户

SELECT VERSION();  #版本信息
SELECT NOW();  #当前时间 --> 2017-11-12 15:43:10
SELECT USER();  #当前用户 --> root@localhost

其他操作

show error/warnings;  #查看错误信息

操作数据库

show databses;  # 显示所有数据库
create database if t2 character set=utf8/gbk; # 创建数据库
show create database test;  #查看创建数据库的信息，如查看编码方式为gbk/utf8
alter database test character set utf8;  #修改数据库t3编码方式为utf8
drop database test; 删除数据库
select database();  # 显示当前数据库
use test;  # 选择数据库

数据类型

整数

数据类型

字节

TINYINT

1

SMALLINT

2

MEDIUMINT

3

INT

4

BIGINT

8

字节与储存范围的换算：1个字节，8位，有符号位−27−27−1，无符号位0−28−1
浮点型
FLOAT[(M,D)]，储存范围：-3.40E38-1.76E-38,0,1.76E-38到3.40E38
M总位数，D小数点后位数，精度最大7为小数.
DOUBLE[(M,D)]，储存范围大约是FLOAT的10倍.
字符型
CHAR，0-255，定长(补足位会用0补齐)；VARCHAR，0-65535，变长.
TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT.
ENUM(‘var1’, ‘var2’,…)，SET(‘var1’, ‘var2’)，SET不用与ENUM，可以选多个，即取子集.

表操作

CREATE TABLE tb1(
    id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,   # AUTO_INCREMENT必须和PRIMARY KEY主键一起使用
    username VARCHAR(20) NOT NULL UNIQUE KEY，  # NOT NULL非空，UNIQUE KEY唯一约束
    sex ENUM('0', '1', '2') DEFAULT '2',  # DEFUALT默认‘2’
    salary FLOAT(8,2) UNSIGNED
);

SHOW CREATE TABLES tb_name   # 查看创建表的命令
desc tb_name  #等价于SHOW COLUMNS FROM tb_name，查看表结构

SELECT * FROM tb1;  # 查看tb1中所有记录.*表示选中所有字段.

# 添加单列
ALTER TABLE tb_name ADD col_name col_definition [FIRST|AFTER col_name]
# 添加多列，不能指定插入位置，只能在最后添加
ALTER TABLE tb_name ADD (col_name column_definition,...)
# 删除列
ALTER TABLE tb_name DROP col_name;
# 多操作
ALTER TABLE tb_name DROP col_name ,ADD col_name col_definition,...

# 修改列定义
alter table tb_name change old_col_name new_col_name col_definition;
alter table tb1 change id ID tinyint unsigned first;

# 数据表更名
alter table tb_name rename new_tb_name
rename table tb_name to new_tb_name [, tb_name to new_to_name,...]  # 为多张数据据表更名

NOT NULL：非空
AUTO_INCREMENT：必须与主键组合使用，默认起始值1，每次增量1
PRIMARY KEY：每张表只有一个主键，字段唯一，自动设置为NOT NULL
UNIQUE KEY：每张表可以有多个， 字段唯一，可以为NULL
DEFAULT：默认值

约束

列级约束和表级约束（针对2个以上字段）
NOT NULL/PRIMARY KEY/UNIQUE KEY/DEFAULT/FOREIGN KEY

外键约束
  1. 父表和子表相同的储存引擎InnoDB，禁止使用临时表.
  2. 外键列和参照列，数字必须相同（类型，长度，有无符号），字符类型长度可以不同.
  3. 外键列和参照列必须创建索引，外键列不存在索引，MySQL自动创建.
  4. FOREIGN KEY物理外键约束，CASECADE为父表中删除和更新记录，子表中同样删除更新.
CREATE TABLE provinces(  # 父表
        id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  # 参照列，有索引
        pname VARCHAR(20) NOT NULL);
CREATE TABLE users(  # 子表
        id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT，
        username VARCHAR(20) NOT NULL,
        pid SMALLINT UNSIGNED,  # 外键列，数据类型必须相似;没有索引但会自动创建
        FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE;  # 设置外键约束
        );  

SHOW CREATE TABLE provinces;  # 可查看表的储存引擎
SHOW INDEXES FROM provinces\G;  # 查看索引

# 添加主键约束
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)

# 添加唯一约束
ALTER TABLE tb_name ADD UNIQUE [INDEX|KEY] (index_col_name,...)
alter table tb5 add unique (username);

# 添加外键约束
ALTER TABLE tb_name ADD FOREIGN KEY (index_col_name）reference_deinitin
# 添加/删除默认约束
ALTER TABLE tb_name ALTER col_name {SET DEFAULT iteral|DROP DEFAULT}

# 删除主键约束
alter table tb_name drop primary key
# 删除唯一约束/索引
alter table tb_name drop {index|key} index_name

# 删除外键约束
# alter table tb_name drop foreign key fk_symbol
show create table tb5;  # 查看fk_symbol
alter table tb5 drop foreign key tb5_ibfk_1;  # 删除外键约束
alter table tb5 drop index pid;  # 索引还在，可以删除

记录操作

# 插入记录
insert tb1 values('Bing', 25, 3783.4);  #不指定列，所有列都要插入
insert tb1 (username, salary) values('Wo', 4502.44);  #插入指定列，部分插入
# 列可以为null或设置默认值的，insert时可以省略列. 如以上tb1有id字段（设为auto_increment），可以省略列

insert users values(default, 'Wu', '123', 2*10+1, 0);  # 也可以是表达式

# 一次插入多条，比多次插入一条好快.
insert tb values('Bing', 25, 23.4), ('Wu', 34, 234.3);  # 逗号(,)分隔，同时插入多条记录

# 插入索引出的数据
insert tb1(id, username) select ID, uname from tb2;

# insert很耗时，会降低等待处理select性能，low_priority可以跟在insert/update/delete之后，降低其性能，提高select性能.
insert low_priority tb values('Bing', 25, 232.4);

# 更新，在更新多行时，如果某行发生错误，则整个update操作被取消. 
update users set age=age+1, sex=1 where id>=4;  #逗号(,)分隔，更新多个字段，where添加条件

# 删除
delete from users where id=8;  # 若不加where，则删除全部记录
truncate table tb4;  # 清空表，速度快于delete from tb4;