mysql 列与其他列的20%_MySQL 表与列约束

MySQL 表

数据库是表的容器

表必须属于某一个数据库

可以通过.语法指明数据表所属的数据库;

进行表操作时, 都会指明当前默认数据库

use db_name 只会设定默认数据库, 不会影响操作其他数据库

创建表

create table table_name(列结构) [表选项]

先分析需要保存的实体数据, 拥有哪些属性, 这些属性应该如何保存.

例如班级信息

班号, 开班日期

标识符命名规则同数据库名

列定义:

列名 列的数据类型 (列属性(约束))

注意: 最有一个列属性没有逗号, 表明列定义结束

简单字符串类型 varchar()

日期类型 date

create database php_one

create table php_one.php_class (

class_no varchar(20),

date_start date

);

如果设定了默认数据库, 可以直接在该数据库下建表, 而不用指定数据库名

表的 crud(增删改查)

表名前缀

为了区分相同逻辑表名的不同应用, 给逻辑表名增加前缀, 形成真实表名.

学生管理

create table info_student (

name varchar(20),

stu_no varchar(20)

)

在线考试

create table exam_student (

name varchar(20),

tu_no varchar(20),

score int

);

show tables [like 'pattern']

如: show tables like 'exam_%'

%: 通配符 任意字符任意个数的组合匹配

显示创建表的信息

show create table exam_student;

可以使用 \G 作为语句结束符

查看表结构(描述表结构)

describe table_name;

缩写为 desc table_name;

一般来说, 表在MySQL的 data目录下的存储为 table_name.frm文件 (frame). (数据库对应目录, 表对应文件)

删除表

drop table table_name;

表不存在, 不能删除, 会报错

drop table if exists table_name;

修改表

修改表名

修改列定义

修改表选项

rename table old_table_name to new_table_name;

支持同时修改多个表

rename table oldname1 to newname1, oldname2 to newname2[, ...];

支持跨数据库重命名

rename table db1.table_name to db2.table_name

可以通过这种方式为数据库重命名, 创建一个新数据库, 旧数据库的表都重命名到新数据库中, 删除旧数据库

修改列

增加一个列

add

删除一个列

drop

修改列的定义

modify

重命名一个列

change

修改表结构, 上面的是子命令, 上级是

alter table table_name add|drop|change|modify

alter table exam_student add height int;

alter table exam_student drop height;

alter table exam_student modify stu_no varchar(40);

alter table exam_student change score point int;

修改表选项

alter table table_name character set utf8

DML

crud create retrieve update delete

创建数据(插入) 获取数据(查询) 删除数据 修改数据

insert into table_name(字段列表) values(值列表)

desc exam_student;

insert into exam_student(name stu_no) values("kitty", "6666");

如果插入时, 为所有的字段设置值, 可省略字段列表. 要求值的顺序应该与表中的字段的顺序一致.

获取数据

select 字段列表 from 表名 [查询条件].

字段列表可以用*表示, 表示所有字段, 查询条件可以省略, 表示获取所有数据相当于 where 1

删除数据

delete from table_name 条件;

要求删除在逻辑花式虐狗严格给条件, 否则容易导致数据丢失;

没条件显示加where 1

修改数据

update table_name set 字段=新值 ... 条件;

列约束

约束, 保证表的结构和数据的正确性和稳定性

总体来说有5种, 唯一性与主键约束, 外键约束, 检查约束, 空值约束, 默认值约束;

列属性, 列约束:

是否允许为空(null|not null)

字段默认值(default)

主键(primary key|unique key)

自动增长(auto increment)

外键(foreign key)

列注释(comment)

关联操作(references)

是否为 null

规定一个字段的值是否是 null; null|not null, 默认可以为空;

create table php1 (

a int not null,

b int

);

insert into php1 (a) values(10);

// a不能为空, 且没有默认值,插入报错

insert into php1 (b) values(11);

null 表示什么都没有, 但是还是占一个字节的存储空间, 存储 null的标识;

默认值

默认值属性: default value 来声明

默认值: 在没有为该字段设置值时使用, 而且默认值的设置需要时固定值,常见的用法是一个字段不能为空, 而且存在默认值.

create table php2 (

a int not null default value 12,

b int default value 13

);

主键

主键PK: primary key

可以唯一标识某条记录的字段或者 字段的集合,就是主键

主键可以是真实实体的属性.

但是, 常用的好的解决方案是利用一个与实体不相关的属性作为唯一标识

主键和业务逻辑不发生关系, 只用来标识记录.

设置主键的语法:

字段上设置 primary key

在定义完字段之后定义, 可以定义组合主键;

一个表,主键只有一个, 主键唯一且不为 null

create table teacher (

t_id int primary key,

t_name varchar(10),

class_name varchar(10),

span tinyint unsigned

);

create table teacher2 (

t_id int,

t_name varchar(10),

class_name varchar(10),

span tinyint unsigned,

primary key(t_id)

);

使数据库能插入中文, set db_name gbk;

自动增长

为每条记录提供一个唯一标识, 每次插入记录时, 将某个字段自动加1; 使用auto_increment 标识;

需要整型, 还需要索引.

插入数据时, 可以选择插入null, 或者不插入; 也可以手动设置, 如果不是主键的话, 可以重复; 可以修改;

create table teacher3 (

t_id int primary key auto_increment,

t_name varchar(10),

class_name varchar(10),

span tinyint unsigned

);

insert into teacher3 values(null, "snow", "0224", 34);

insert into teacher3 (t_name, class_name, span) values("snow", "0224", 34);

自动增长的初始值默认是1, 可以设置, 通过表选项设置;

alter table teacher3 auto_increment 10;

如果设置的值小于当前存在的最大值, 则从当前最大值 + 1开始增长; 可以理解为内部放弃了这个操作, 虽然没有报错;

外键约束与注释

概念: 如果一个实体A的某个字段指向另一个实体B的主键, 则该字段称为实体B的外键.

被指向的实体, 被称为主实体(主表), 也叫父实体(父表); 负责指向的实体, 称为从实体(从表), 也叫子实体(子表).

外键作用

用于约束处于关系内的实体

增加子表记录时, 是否有与之对应的主表记录

在删除或者更新主表记录时, 从表如何处理相关记录.

定义外键

使用关键字, foreign key

在从表上添加一个外键字段, 指向主表

注释

注释使用关键字 comment

create table tbl_class (

class_id int primary key auto_increment,

class_name varchar(10) not null default "coper" comment "班级名称"

) character set utf8;

create table tbl_student (

stu_id int primary key auto_increment,

stu_name varchar(10) not null default "",

class_id int,

foreign key(class_id) references tbl_teacher(class_id)

) character set utf8;

SET NAMES ‘charset_name’ 设置客户端与服务器的通信编码.

SET NAMES显示客户端发送的SQL语句中使用什么字符集。

因此，SET NAMES 'gbk'语句告诉服务器将来从这个客户端传来的信息采用字符集 gbk”。

它还为服务器发送回客户端的结果指定了字符集。

例如，如果你使用一个 SELECT 语句，它表示列值使用了什么字符 集。)

设置级联操作

在主表数据发生改变时, 与之关联的从表数据应该如何处理;

主表更新和主表删除, 二者可以同时出现

on update: 只有主表的主键发生变化, 才有对从表发生影响

on delete

允许(主表)级联操作:

cascade 如果主表被更新或者删除, 那么从表也会执行相应操作.

set null 设置null, 表示从表不指向任何主表记录, 前提是该字段可以为null;

restrict 拒绝主表的相关操作

修改外键: 先删除, 在新建, 通过就修改表完成

删除外键通过指定`外键名称`达到目的, 使用创建外键时指定名称或者使用MySQL默认生成的名称; 删除的是规则, 而不是数据

alter table tbl_student drop foreight key 外键名;

alter alter table tbl_student add foreight key(class_id) references tbl_class (class_id) on delete set null;

一般不使用外键约束