Mysql库的管理

创建库

• 创建库 create database [if not exists] 库名;
• 删除库 drop database [if exists] 库名;
• 建库通用的写法
  • drop database if exists 旧库名;
  • create database 新库名;

-- 查看当前库
SHOW DATABASES LIKE foo;
-- 删除库 如果存在时
DROP DATABASE IF EXISTS foo;
-- 创建库
CREATE DATABASE foo;

---

表管理

create table 表名(
  字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
  字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];
注意：
1. 在同一张表中，字段名不能相同
2. 宽度和约束条件为可选参数，字段名和类型是必须的
3. 最后一个字段后不能加逗号
4. 类型是用来限制 字段 必须以何种数据类型来存储记录
5. 类型其实也是对字段的约束(约束字段下的记录必须为XX类型)
6. 类型后写的 约束条件 是在类型之外的 额外添加的约束

• not null：标识该字段不能为空

create table test1(
  a int not null comment '字段a、int型、不是null'
);
-- 插入测试数据
insert into test1 values(null);

• default value：为该字段设置默认值，默认值为value

create table test2(
  a int not null comment '字段a、int型、不是null',
  b int not null default 0 comment 'int型、默认值0、不是null'
);
-- 插入测试数据
insert into test2(a) values(1);

• primary key：标识该字段为该表的主键，可以唯⼀的标识记录，插⼊重复的会报错

-- 方法一
-- 如果表test3存在就删除
drop table if exists test3;
-- 创建test3表
create table test3(
  a int not null comment 'int型、不是null' primary key
);
-- 插入测试数据
insert into test3(a) values(1);
insert into test3(a) values(1);

-- 方法二
drop table if exists test4;
create table test4(
  a int not null comment 'int型、不是null',
  b int not null default 0 comment 'int型、默认值0、不是null',
  primary key(a)
);
insert into test4(a,b) values(1,1);
insert into test4(a,b) values(1,2);

-- 多字段作为主键
-- primary key(字段1,字段2,字段n)
create table test7(
  a int not null comment '字段a',
  b int not null comment '字段b',
  primary key(a,b)
);
-- 测试数据
insert into test7(a,b) VALUES (1,1);
insert into test7(a,b) VALUES (1,1);

• foreign key：为表中的字段设置外键
  • 语法：foreign key(当前表的列名) references 引用的外键表(外键表中字段名称)

drop table if exists test5;
-- 创建test5表
create table test5(
  a int not null comment '字段a' primary key
);
drop table test6 if exists test6;
-- 创建test6表并设置外键
create table test6(
  b int not null comment '字段b',
  ts5_a int not null,
  foreign key(ts5_a) references test5(a)
);
-- 插入测试数据
insert into test5 (a) values (1);
insert into test6 (b,test6.ts5_a) values (1,1);
-- 报错 表名test6中ts5_a字段的值来源于表test5中的字段a
insert into test6 (b,test6.ts5_a) values (2,2);
-- 注意
-- * 两张表中需要建立外键关系的字段类型需要一致
-- * 要设置外键的字段不能为主键
-- * 被引用的字段需要为主键
-- * 被插入的值在外键表必须存在，如上面向test6中插入ts5_a为2的时候报错了，原因：2的值在test5表中不存在

• unqiue key(uq)：标识该字段的值是唯一的
  • 支持一个到多个字段，插入重复的值会报违反唯一约束，会插入失败

-- 方法一
drop table if exists test8;
-- 创建test8设置字段唯一性
create table test8(
  a int not null comment '字段a' unique key
);
-- 插入测试数据
insert into test8(a) values(1);
insert into test8(a) values(1);

-- 方法二
create table test9(
  a int not null coment '字段a',
  unique key(a)
);

-- 支持多字段逗号隔开
-- 语法：primary key(字段1,字段2,字段n)
create table test10(
  a int not null comment '字段a',
  b int not null comment '字段b',
  unique key(a,b)
);
-- 测试数据
insert into test10(a,b) values(1,1);
insert into test10(a,b) values(1,1);

• auto_increment：标识该字段的值自动增长（整数类型，而且为主键）

drop table if exists test11;
-- 创建表test11设置主键自增
create table test11(
  a int not null auto_increment primary key comment 'a',
  b int not null comment 'b'
);
-- 添加测试数据
insert into test11(b) VALUES (10);
insert into test11(b) VALUES (20);
-- * 字段a为自动增长，默认值从1开始，每次+1
-- * 关于自动增长字段的初始值、步长可以在mysql中进行设置，比如设置初始值为1万，每次增长10
-- * 自增长列当前值存储在内存中，数据库每次重启之后，会查询当前表中⾃增列的最大值作为当前值，如果表数据被清空之后，数据库重启了，自增列的值将从初始值开始

---

删除表

• drop table [if exists] 表名;

修改表名

• alter table 表名 rename [to] 新表名;

表设置备注

• alter table 表名 comment '备注信息';

复制表

• 只复制表结构 create table 表名 like 被复制的表名;

create table test12 like test11;
select * from test12;
show create table test12;
/*
mysql> show create table test12;
+--------+-------+
| Table | Create Table
+--------+-------+
| test12 | CREATE TABLE `test12` (
`a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
`b` int(11) NOT NULL COMMENT '字段b',
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------+
1 row in set (0.00 sec)
*/

• 复制表结构+数据
• create table 表名 [as] select 字段,... from 被复制的表 [where 条件];

-- 从表test11复制结构和数据到test13表
create table test13 as select * from test11;
select * from test13;

---

表中列的管理

• 修改列
  • alter table 表名 modify column 列名 新类型 [约束];
  • alter table 表名 change column 列名 新列名 新类型 [约束];
  • modify不能修改列名，change可以修改列名

-- 查看test14表结构
show create table test14;
/*
mysql> show create table test14;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
`a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
`b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
`c` int(11) NOT NULL DEFAULT '0' COMMENT '字段c',
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)
*/
-- 修改
alter table test14 change column c d varchar(10) not null;
/*
mysql> show create table test14;
;;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
`a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
`b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
`d` varchar(10) NOT NULL DEFAULT '' COMMENT '字段d',
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)
*/

• 删除列
  • alter table 表名 drop column 列名;

-- 删除表test14中的字段d
alter table test14 drop column d;
/*
mysql> show create table test14;
+--------+--------+
| Table | Create Table |
+--------+--------+
| test14 | CREATE TABLE `test14` (
`a` int(11) NOT NULL AUTO_INCREMENT COMMENT '字段a',
`b` int(11) NOT NULL DEFAULT '0' COMMENT '字段b',
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+--------+
1 row in set (0.00 sec)
*/