1.建表

结构表:

字段名称 数据类型 备注
id int(11) 员工编号
name varchar(25) 员工名称
deptld int(11) 所在部门编号
salary float 工资
//建表语句
create table tbl_emp(
    id int(11),
    name varchar(25),
    deptId int(11),
    salary float
)

查看表结构

desc tbl_emp;

查看创建表语句

show create table tbl_emp\G;

表操作

从一个已存在的表新建

相当于复制一个已存在的表,包括表数据

create table fisha_tb select * from fisha_tb1

2.改变表结构

修改表名、字段类型、字段名、增删字段、改变表引擎、删除外键

ALTER TABLE <表名> [修改选项]
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

例子:

第一行添加新字段:alter table tbl_emp add column col1 int first;
name字段后添加新字段:alter table tb_emp add column col2 int after name;
改字段类型:alter table tb_emp modify name varchar(30);
删除字段:alter table tb_emp drop col2;
改字段名:alter table tb_emp change col1 col3 char(30);
改表名:alter table tb_emp rename to tb_emp2;

在name字段后添加新字段

添加新字段

改变字段类型

添加新字段

改变字段名

添加新字段

删除字段

添加新字段

改表名

添加新字段

3.删除表

DROP TABLE [IF EXISTS] <表名> [ , <表名1> , <表名2>]
drop table 表名;

添加新字段

4.约束

主键约束

不允许空值

  • 建表时创建主键约束

    1. 在某个字段定义后加上 PRIMARY KEY
      CREATE TABLE tb_emp
      -> (
      -> id `int(11)` primary key,
      -> name `varchar(25)`,
      -> deptId `int(11)`
      -> );
    2. 在全部字段定义完后,加上PRIMARY KEY(字段)定义主键
      CREATE TABLE tb_emp
      -> (
      -> id `int(11)`,
      -> name `varchar(25)`,
      -> deptId `int(11)`,
      -> PRIMARY `key(id) ` // -> `PRIMARY KEY(id,deptId)`
      -> );

      第二种方法可以设置复合主键,指定多个字段作为主键。

  • 建表后创建主键约束
    ALTER TABLE <数据表名> ADD PRIMARY KEY`(<列名>)`;

外键约束

两个表数据间建立链接。外键不是本表的主键但是每一个外键的值必须等于另一个表中主键的某个值。

  • 新建表时创建外键约束
    CREATE TABLE tb_dept1
    -> (
    -> id INT`(11)` PRIMARY KEY,
    -> name VARCHAR`(22)` NOT NULL,
    -> location VARCHAR`(50)`
    -> );
    CREATE TABLE tb_emp1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id) on delete cascade
    -> );

fk_emp_dept1为外键名,使用references链接主表的主键,on delete cascade父表中删数据时也自动删掉字表中对应数据。

  • 在修改表时添加外键约束

    ALTER TABLE <数据表名> ADD CONSTRAINT <索引名>
    FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
  • 删除外键约束
    ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

其他各种约束

如果在建表时添加约束,直接在字段定义后添加即可。

  • 非空约束 not null
    如果需要删除非空约束,则设置为null

    alter table 表名 modify 字段 属性 not null;
  • 自动增长 auto_increment

    alter table 表名 modify 字段 属性 auto_increment;
  • 唯一约束 unique
    UNIQUE,可为空,字段值唯一

    添加唯一约束:ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);
    删除唯一约束:ALTER TABLE <表名> DROP INDEX <唯一约束名>;
  • 检查约束 check
    CHECK(xxx),比如设置某字段值取值范围。

    添加检查约束:ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)
    删除检查约束:ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;
    CHECK(salary>0 AND salary<100),
  • 默认值约束 default
    DEFAULT xxx
    添加默认值约束:ALTER TABLE <数据表名>
    CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
    删除默认值约束:ALTER TABLE <数据表名>
    CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

可以通过查询建表语句查看所有的约束。show create tables 表名\G;