MySQL常用操作

一. 表操作

1.1 新建表

新建一张表:

• 1.指定非业务主键自增;
• 2.设置唯一键约束(使用多字段唯一约束; 索引类型为unique; 索引方法为HASH(还有一种BTEE));
• 3.数据库引擎设置为InnoDB

CREATE TABLE uv_stat (
pid bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘非业务主键’,
type varchar(50) NOT NULL COMMENT ‘类型’,
date_cy date NOT NULL COMMENT ‘今年日期’,
uv_cy int(20) DEFAULT NULL COMMENT ‘今年UV’,
date_ly date NOT NULL COMMENT ‘去年日期’,
uv_ly int(50) DEFAULT NULL COMMENT ‘去年UV’,
PRIMARY KEY (pid),
UNIQUE KEY idx_uniq_date_and_uv (type,date_cy,date_ly) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT=‘UV统计’

1.2 基于已有的表建表

①. like 方式建表

语法:

create table tbl_2 like tbl_1;

解释:

原表所有的scheme属性, 将全部带回.
包括:

• 主键
• 外键
• 主键auto_increment属性
• 非空约束
• 表级注释
• 字段级注释
• 字段字符集编码
• index信息(unique)

②. select中建表

语法:

create table tbl_3 as select * from tbl_1 where 0=1;

解释:

原表中的部分sheme属性, 会被带回.
包括:

• 非空约束
• 表级注释
• 字段级注释
• 字段字符集编码

不包括:

• 主键
• 外键
• 主键auto_increment属性
• 表级注释
• auto_increment属性
• index信息(unique)

1.3 原表中的数据复制插入到新表中

从源表复制数据,并插入到目标表中，目标表中任何已存在的行都不会受影响。

语法：

①. 当表A和表B的表结构一致时，直接插入即可

insert into B select * from A;

②. 当表结构不一致时（字段大小、类型都相同）,mysql中,需要第一列如pid为主键, 需要设置为自增主键类型(AUTO_INCREMENT).

insert into B(col1, col2) select col11, col22 from A;

1.4 alter table [add | modify] column

语法：
ALTER TABLE 表名 [add | modify] COLUMN 字段名 字段类型 [DEFAULT NULL | NOT NULL] COMMENT '新加字段' AFTER id;

分开写：

ALTER TABLE user ADD email_address varchar(50);
ALTER TABLE user MODIFY address varchar(30);
ALTER TABLE user ADD COLUMN last_name varchar(30);

合并写：

-- 创建测试表
create table test_user (id int);

-- 只写add,没有 column 关键字, 增加的多个字段内容在大括号中
alter table test_user add (user_name varchar(50), address varchar(30));

-- 带 column 关键字, 增加的多个字段内容在大括号中
alter table test_user add column (user_name varchar(50), address varchar(30));

-- 分开写多个add，没有 column 关键字, 没有大括号
alter table test_user add user_name varchar(50), add address varchar(30);
 
-- 分开写多个add，带有 column 关键字, 没有大括号
alter table test_user add column user_name varchar(50), add column address varchar(30);
 
-- 同时做增加字段 和 修改字段操作
alter table test_user add user_name varchar(50), modify address varchar(100);

二. 权限操作

2.1 创建用户，只读权限

-- 创建用户qa_user，并设置密码（密码同时包含大、小写字母，数字，特殊字符）
CREATE USER 'qa_user'@'%' IDENTIFIED BY 'xx1@XX';   

-- 赋予只读权限，允许远程访问
GRANT SELECT ON *.* TO 'qa_user'@'%';

-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;