创建数据表

CREATE TABLE IF NOT EXISTS tbl_name(
字段名称字段类型 [完整性约束条件],
字段名称字段类型 [完整性约束条件],
…
)ENGINE=存储引擎 CHARSET=编码方式;
#VARCHAR(10)代表10个字符，不管是英文，汉字

--用户表imooc_user
CREATE TABLE IF NOT EXISTS `imooc_user`(
  `id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
  `username` VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
  `password` CHAR(32) NOT NULL COMMENT '密码',
  `email` VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
  `age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  `sex` ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
  `tel` CHAR(11) NOT NULL UNIQUE COMMENT '电话',
  `addr` VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
  `card` CHAR(18) NOT NULL UNIQUE COMMENT '身份证号',
  `married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚,1代表已结婚',
  `salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;

--创建结构相同的表
CREATE GTABLE user2 LIKE user1;

查询数据表

查看当前数据库中的所有表格：SHOW TABLES;

完整命令：SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]

查看指定数据表的详细信息：SHOW CREATE TABLE tbl_name;

显示建表语句

SHOW CREATE TABLE users;

查看数据表结构：以下三条语句的效果是一样的

DESC tbl_name;

DESC users;

DESCRIBE tbl_name;

DESCRIBE users;

SHOW COLUMNS FROM tbl_name;

SHOW COLUMNS FROM users;

4，查看数据表的具体状态（引擎，数据格式等）

SHOW TABLE STATUS LIKE 'test_myisam';

删除数据表

删除指定的数据表：DROP TABLE tbl_name;
如果数据表存在则删除：DROP TABLE [IF EXISTS] tbl_name;
```
DORP TABLE IF EXISTS users;
```

修改数据表

添加，删除字段

ALTER TABLE tbl_name
ADD 字段名称字段属性 [完整性约束条件][FIRST|AFTER 字段名称]

ALTER TABLE tbl_name
DROP 字段名称

--测试添加和删除字段
CREATE TABLE IF NOT EXISTS user1(
  id INT UNSIGNED AUTO_INCREMENT KEY
);

--添加用户名字段 username VARCHAR(20)
ALTER TABLE user1
ADD username VARCHAR(20);

--添加密码字段 password CHAR(32) NOT NULL
ALTER TABlE user1
ADD password CHAR(32) NOT NULL;

--添加邮箱字段 email VARCHAR(50) NOT NULL 加到username之后
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL AFTER username;

--添加测试字段 test TINYINT(1) NOT NULL DEFAULT 0;加到首位
ALTER TABLE user1
ADD test TINYINT(1) NOT NULL DEFAULT 0 FIRST;

--删除测试字段
ALTER TABLE user1
DROP test;

--添加age,addr字段,删除email字段
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',
DROP email;

--以下这样也是可以的
ALTER TABLE user1
ADD (age TINYINT UNSIGNED NOT NULL DEFAULT 18,addr VARCHAR(100) NOT NULL DEFAULT '北京');

添加，删除默认值

ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;

ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT;

--测试添加删除默认值操作
CREATE TABLE user2(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL,
  age TINYINT UNSIGNED NOT NULL DEFAULT 18,
  email VARCHAR(50) NOT NULL
);

--给email字段添加默认值 imooc@qq.com
ALTER TABLE user2
ALTER email SET DEFAULT 'imooc@qq.com';

--删除age字段的默认值
ALTER TABLE user2
ALTER age DROP DEFAULT;

修改字段类型，属性

ALTER TABLE tbl_name
MODIFY 字段名称字段类型 [字段属性][FIRST | AFTER 字段名称]

修改字段名称，类型，属性

ALTER TABLE tbl_name
CHANGE 原字段名称新字段名称字段类型字段属性 [FIRST | AFTER字段名称]

--MODIFY:修改字段类型，字段属性，字段位置
--CHANGE:修改字段名称，字段类型，字段属性，字段位置
CREATE TABLE user3(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(5) NOT NULL UNIQUE,
  password CHAR(32) NOT NULL,
  email VARCHAR(10) NOT NULL
);

--将用户名字段的类型改为20
ALTER TABLE user3
--修改的时候UNIQUE可以不用加，否则会报警告
--但是NOT NULL必须写，否则这个属性会消失
-- MODIFY username VARCHAR(20) NOT NULL UNIQUE;
MODIFY username VARCHAR(20) NOT NULL;

--将密码字段长度改为40
ALTER TABLE user3
MODIFY password CHAR(40) NOT NULL;

--将email字段改为VARCHAR(50) NOT NULL FIRST
ALTER TABLE user3
MODIFY email VARCHAR(50) NOT NULL FIRST;

--用MODIFY添加default属性也是可以的
ALTER TABLE user3
MODIFY username VARCHAR(5) NOT NULL DEFAULT 'SONG';

--将username 名称改为user
ALTER TABLE user3
CHANGE username user VARCHAR(20) NOT NULL;

--将password 名称改pwd
ALTER TABLE user3
CHANGE password pwd CHAR(32) NOT NULL;

--将email改成userEmail 类型改成VARCHAR(100) DEFAULT 'imooc@qq.com'
ALTER TABLE user3
CHANGE email userEmail VARCHAR(100) DEFAULT 'imooc@qq.com';

--以下这样是不对的
-- ALTER TABLE user3
-- CHANGE email VARCHAR(50) DEFAULT 'imooc@qq.com';

添加，删除主键

ALTER TABLE tbl_name
ADD PRIMARY KEY (字段名称)

ALTER TABLE tbl_name
DROP PRIMARY KEY

--测试添加和删除主键
CREATE TABLE user4(
  id INT UNSIGNED,
  username VARCHAR(20) NOT NULL
);

--添加主键
--这个时候会自动添加NOT NULL属性
ALTER TABLE user4
ADD PRIMARY KEY(id);

--删除主键
--这个是时候NOT NULL属性仍然存在
ALTER TABLE user4
DROP PRIMARY KEY;

CREATE TABLE user5(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL
);

--以下这个时候是会报错的，因为有AUTO_INCRMENT属性存在
--AUTO_INCREMENT属性必须和主键属性搭配存在
-- ALTER TABLE user5
-- DROP PRIMARY KEY;

--正确的做法是先把id的AUTO_INCREMENT属性删除，然后在删除主键
ALTER TABLE user5
MODIFY id INT UNSIGNED;

ALTER TABLE user5
DROP PRIMARY KEY;

添加，删除唯一索引

CREATE TABLE user6(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20) NOT NULL UNIQUE,
  password CHAR(32) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE
);

ALTER TABLE tbl_name
ADD UNIQUE KEY(字段名称);
#不设置索引名，mysql默认设置索引名与字段名一致

--添加唯一索引
ALTER TABLE user6
ADD UNIQUE KEY(username);

ALTER TABLE tbl_name
ADD UNIQUE INDEX index_name(字段名称);
#设置索引名，mysql会采用此索引名

ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email);

ALTER TABLE tbl_name
DROP KEY index_name;
#如果没有自定义索引名，用KEY删除

--删除唯一索引
ALTER TABLE user6
DROP KEY username;

ALTER TABLE tbl_name
DROP INDEX index_name;
#如果已经设置索引名，只能用INDEX删除

ALTER TABLE user6
DROP INDEX uni_email;

修改数据表名称

ALTER TABLE tbl_name
RENAME [TO/AS] new_tbl_name;

RENAME TABLE tbl_nameTO new_tbl_name;

--将user6改为user666
ALTER TABLE user6
RENAME TO user666;
--RENAME AS user666;这样也是可以的
--RENAME user666;这样也是可以的

--这样也是可有的，不过这里只能用TO
RENAME TABLE user6 TO user7;

重置INCREMENT

ALTER TABLE tbl_name AUTO_INCREMENT=1;

ALTER TABLE user AUTO_INCREMENT=1;

【后端】mysql

数据表操作