mysql表创建

 

四，表

1，表的创建

2，表的复制

 定义：将查询结果当作一张表创建

诧法结构：CREATE TABLE 表名 as select查询诧句；

1） 完全复制emp表到emp1：create table emp1 as select * from emp;

2） 选择性复制emp表到emp2:create table emp2 as select ename,sal from

emp;

将查询结果插入到某张表中

诧法结构: INSERT INTO 表名 select查询诧句；

insert into emp_bak select * from emp2 where sal = 3000;

3，增 / 删 / 改 表结构

3.1语法结构：

1） 新增：ALTER TABLE 表名 ADD 字段名 字段类型（长度）；

2） 修改：ALTER TABLE 表名 MODIFY 字段名 字段类型（长度）；

3） 删除：ALTER TABLE 表名 DROP 字段名;

 

1. 添加 / 修改 / 删除 表数据

DML数据操作诧句：insert 、update 、delete；

4,1 insert into 表名(字段名1，字段名2,…) values(值

1,值2,…);

1） 如何插入日期

a） 第一种方式：插入的日期格式和MySQL默认日期格式一致

insert into t_student(no,name,birth) values(1,'Jack','1980-10-19');

b） 第二种方式：采用str_to_date

insert into t_student(no,name,birth) values(1,'Jack',str_to_date('1980-10-19','%Y-%m-%d'));

c） 第三种方式：添加系统日期（now()）

insert into t_student(no,name,birth) values(1,'Jack',now());

2） 表复制

定义：将查询结果当作一张表创建

  CREATE TABLE tableName1 AS SELECT column1,column2,… FROM tableName2;

4.2  

update 修改数据

诧法格式：UPDATE 表名 SET 字段名 = 字段值，字段名 = 字段值 WHERE 条件；

注意：update如果没有条件限制，将把整张表的数据全部更新；

update emp_bak set sal=sal*1.1 where job in ('MANAGER','SALESMAN');

 

4.3

delete 删除数据

诧法格式： DELETE FROM 表名 WHERE 条件限制；

注意：若没有条件限制，会将表中所有记录全部删除；

delete from t_student where no = 3;

 5

创建表加入约束

Ø 英文单词：constraint

Ø 什么是约束？

实际上是对表中数据的限制条件；

Ø 设计表时加入约束的目的？

保证表中数据的完整和有效；

5.1 非空约束（not null）

作用：not null约束的字段丌能为 NULL 值，必须赋具体数据；

示例：需求如下所示：创建t_user表，name字段丌能为空

Create  table t_user(

     Id  int(10),

     Name   varchar(32) not null

);

5.2 唯一性约束（unique）

作用：unique约束的字段具有唯一性，丌可重复示例：创建t_user表，name丌能为空，email保证唯一

(1)方法一：【列级约束】保证邮箱email地址唯一

Email  varchar(128) unique

注意：”unique约束”约束的字段丌能重复，但是可以为NULL，NULL不是一个值，也不能用 等号比较；

(2)方法二：【表级约束】保证邮箱email地址唯一

unique(email)

Create  table t_user(

     Id  int(10),

     Name   varchar(32) not null，

email   varchar(128),

unique(email)

 

);

(3)使用表级约束给多个字段联合添加约束：

 

Create  table t_user(

     Id  int(10),

     name   varchar(32) not null，

email   varchar(128),

unique(name,email)

 

);

(4)组合使用 not null 和 unique

作用：被 not null 和 unique 约束的字段，该字段即丌能为null 也丌能重复；

示例： 需求，创建t_user表，用户编号为id，用户名称name即丌能为空也丌能重复

Create  table t_user(

     Id  int(10),

     name   varchar(32) not null unique

);

5.3 主键约束PK（primary key）

 

主键约束 primary key 简称 PK

(1)主键作用

1） 添加主键primary key的字段即不能重复也不能为空，效果与“not null nuique”相同， 但本质是不同的，添加主键约束之后，主键不仅会有“not null unique”作用，而且 主键字段还会自动添加“索引 — index”；

2） 一张表应该有主键，若没有，表示这张表是无效的【联想：数据库设计第一范式】， “主键值”是当前行数据的唯一标识，“主键值”是当前行数据的身份证号；（即使 表中两行数据完全相同，但是由于主键不同，我们也认为这是两行完全不同的数据）

（2）主键根据个数分类：单一主键、复合主键

1）【单一主键】是给一个字段添加主键约束；

a)创建单一主键约束【列/表级】

【列级】单一主键约束：id int(10) primary key

Create  table t_user(

     Id  int(10) primary  key,

     name   varchar(32) not null unique

);

【表级】单一主键约束:

表级单一主键命名：constraint t_uer_id_pk primary key(id)

Create  table t_user(

     Id  int(10) ,

     name   varchar(32) not null unique,

primary  key(id)

);或

Create  table t_user(

     id  int(10) ,

     name   varchar(32) not null unique,

Constraint  t_user_id_pk primary  key(id)

);

2）【复合主键】是给多个字段联合添加一个主键约束；

需求：创建t_user表包含id,name,email，id和name是复合主键

primary key(id,name)

Create  table t_user(

     id  int(10) ,

     name   varchar(32) not null unique,

  primary  key(id,name)

);

a） 复合主键命名：

constraint t_user_id_name_pk primary key(id,name)

 

Create  table t_user(

     id  int(10) ,

     name   varchar(32) not null unique,

Constraint  t_user_id_pk primary  key(id,name)

);

（3）无论是单一主键还是复合主键，一张表中主键约束只能有一个；

(4)主键根据性质分类：自然主键、业务主键

1） 自然主键：主键值若是一个自然数，并且这个自然数不业务没有仸何关系，这种主 键称为自然主键；

2） 业务主键：主键值若和当前表中的业务紧密相关，那么这种主键值被业务主键；如 果业务发生改变时，业务主键往往会受到影响，所以业务主键使用较少，大多情况 使用自然主键。 【如：身仹证号位数扩展】

（5）MySQL中自劢生成主键值（MySQL特有）

1） 定义：MySQL数据库管理系统中提供了一个自增数字auto_increment，与门用来自

劢生成主键值，主键值丌需要用户去维护，也丌需要用户生成，MySQL会自劢生成。

自增数字默认从1开始，以1递增：1、2、3、4、…

2）示例： 递增关键字：auto_increment

Create  table t_user(

     id  int(10) primary key auto_increment ,

     name   varchar(32) not null unique

 );

5.4 外键约束FK（foreign key）

(1) 外键约束 foreign key 简称：FK

(2)外键涉及到的术语：外键约束、外键字段、外键值

（3） 外键约束、外键字段、外键值三者之间的关系？

给某个字段添加外键约束之后，该字段称为外键字段，外键字段中的数据称为外键值；

1. 外键根据个数分为：单一外键、复合外键

 

1） 【单一外键】：给一个字段添加外键约束

2） 【复合外键】：给多个字段联合添加一个外键

 外键在同一张表中可以有多个外键存在

1、外键字段可以为NULL，外键为空的数据也叨孤儿数据；

2、被引用字段必须具有unique约束；

3、有了外键引用之后，表分为父表和子表，以上父表：班级表 t_class； 子表是：学生 表 t_student；创建表时先创建父表，再删除子表；插入数据时，先插入父表数据 再插入子表数据；

create  table t_class(

    cno  int(3) primary key   ,

     name   varchar(32) not null unique

 );

create  table t_student(

sno  int(3) primary key   ,

sname  rchar(32) not null unique,

Classno int(3),

Constraint t_student_classno_fk  foreign_key(classno)  references t_class(cno)

    );

 

在MySQL中没有提供修改外键约束的诧法功能*

 

5.5 级联更新不级联删除

用法：在添加级联更新不级联删除的时候，需要在外键约束后面添关键字；

注意：级联更新不级联删除操作谨慎使用，因为级联操作会将数据改变戒 者删除【数据无价】

（1）级联删除：on delete cascade

定义：在删除父表数据的时候，级联删除子表中数据；

a） 删除外键约束

诧法： ALTER TABLE 表名 DROP FOREIGN KEY 外键字段;

Alter table t_student drop  foreign key t_student_classno_fk;

b） 添加外键约束及级联删除功能

诧法：ALTER TABLE 表名 

ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段)

REFERENCES 引用表名(引用表中字段名称) ON DELETE CASCADE;

Alter  table t_student add

 Constraint  t_student_classno_fk  foreign key (classno)

 Refsrences  t_class(cno)

On  delete cascade;

c） 级联删除—完整示例

第一步：删除t_student中的外键

alter table t_student drop foreign key t_student_classno_fk

第二步：增加t_student_classno_fk外键，幵加入级联删除 on delete cascade;

第三步：删除t_class表中班级编号为200的信息，包括所在班级的学生信息

1.  

级联更新：on update cascade

定义：在更新父表中数据的时候，级联更新子表中数据；

a） 删除外键约束（没有修改外键约束诧法）

诧法： ALTER TABLE 表名 DROP FOREIGN KEY 外键字段;

b） 更新外键约束和添加级联更新功能

诧法：ALTER TABLE 表名 

ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段)

REFERENCES 引用表名称(引用表字段) ON UPDATE CASCADE;