MySql库和表的增删改查

Scroll Down

第一章 MySql库的增删改查

库定义的 开发规范 
开发  ----》  测试   ----》 预发布  ----》生产
1. 库名不能数字开头
2. 库名要和业务有关
3. 库名不要有大写字符
   原因:为了多平台兼容。
4. 建库需要显示指定字符集。建议是utf8mb4。	
5. 生产中禁用普通用户的drop database权限。

一、建库

# 语法
create database  charset 字符集;
# 示例
create database oldboy charset utf8mb4;

8.0之前,默认字符集 latin1 。
8.0之后,默认字符集utf8mb4 。
我们建议使用utf8mb4 。

  • 在客户端字符集设置:
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
# 在mysqld 标签下面添加下面两句
character_set_server=utf8mb4
init_connect = "set names 'utf8mb4'"

二、查库

  1. 查看所有库列表
show databases;
  1. 查看建库语句
# 语法
show create database 库名;
# 示例
show create database oldboy;

三、改库

# 语法
alter database 库名 charset 字符集;
# 示例
alter database oldboy charset utf8mb4;

四、删库

# 语法
show create database 库名;
# 示例
show create database oldboy;

第二章 表的增删改查

# 表定义规范
1. 建表  
	a. 表名: 
		不能数字开头
		业务有关
		不要大写字母
		不要超过18字符
		不能是关键字
	b. 存储引擎使用InnoDB
	c. 5.7版本以后,字符集使用utf8mb4
	d. 列名要和业务有关,不要超过18个字符
	e. 选择合适、足够、简短数据类型
	f. 建议每个列设置not null
	g. 每个列要有注释
	h. 每个表要有主键
	i. 针对not null 列,可以设定默认值。
	j. 表注释	
2. 修改表 
	a. 添加列,使用追加式添加列 
	b. 修改列属性,尽量使用modify语句
	c. 修改表定义,建议在业务不繁忙期间进行。尽量采用pt-osc或者gh-ost工具减少业务影响。

一、建表

  1. 语法
Create table 库名.表名
(
列名 列属性,
列名 列属性,
)
ENGINE=存储引擎 charset=字符集 collate=校对规则; 
  1. 示例
Create table `oldboy`.`student`
( 
`id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(64) NOT NULL COMMENT '学生姓名',
 `age` tinyint UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
 `gender` CHAR(1) NOT NULL DEFAULT 'n' COMMENT '学生性别',
 primary key (`id`) 
) ENGINE=InnoDB charset=utf8mb4 collate=utf8mb4_bin; 

补充:`` 反引号, 还原字符串原有的含义。

  1. 克隆一张结构一致的表
create table 表名 like 被克隆表名;
create table stu like student;

二、查表

  1. 语法
# 1.使用该库
use 库名;
# 2.查询该库下的所有表
show tables;
# 查询表定义语句
show create table student;
# 查询表结构
desc student;

三、改表

  1. 在表最后添加一列
-- 修改表定义  
--- 添加列: 在student表中添加telnum列 *****
use oldboy;
desc student;

--- 语法
alter table 库名.表名 add column 要添加的列名 列属性;
--- 示例
alter table oldboy.student 
	add column telnum CHAR(11) not null unique key default '0' comment '手机号' ;

desc student;
  1. 在表中指定某一列后面添加一列 (不推荐使用)
--- 不建议的方式
alter table oldboy.student 
add column a CHAR(11) not null unique key default '0' comment '手机号' after gender ;
desc student;
  1. 修改列属性
--- 修改列属性
desc st;

--- 修改列语法
alter table 表名 change 列名 属性;
--- 修改列名
alter table student change shenfen cardnum CHAR(18) not null default '0' comment '身份证';

--- 修改默认值
alter table student change cardnum cardnum CHAR(18) not null default '1' comment '身份证';

--- 修改数据类型
alter table student modify cardnum CHAR(20) not null default '1' comment '身份证';
  1. 修改表属性
--- 修改表名 --- 语法
alter table 源表名 rename to 新表名;
--- 修改表名 
alter table student rename to st;

--- 修改引擎
create table t1(id int)engine=myisam;
show create table t1;
alter table t1 engine=innodb;

--- 修改字符集
create table t2(id int)charset=utf8;
show create table t2;
alter table t2 charset=utf8mb4;

四、删除

  1. 删除列
--- 删除列 (不代表生产操作,危险!!!!)
alter table student drop  a;
alter table student drop  telnum;
  1. 删表
#  删表(危险操作!! 普通用户禁用 drop 操作)
drop table stu;