mysql查看表的约束条件_数据库----mysql表的约束和查询

一,mysql中表的约束

为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。

约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

NOT NULL :非空约束,指定某列不能为空;

是否可空,null表示空,非字符串

not null - 不可空

null - 可空

mysql> create table t12 (id int not null);

Query OK, 0 rows affected (0.02 sec)

mysql> select * from t12;

Empty set (0.00 sec)

mysql> desc t12;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

#不能向id列插入空元素。

mysql> insert into t12 values (null);

ERROR 1048 (23000): Column 'id' cannot be null

mysql> insert into t12 values (1);

Query OK, 1 row affected (0.01 sec)

DEFAULT: 我们约束某一列不为空,如果这一列中经常有重复的内容,就需要我们频繁的插入,这样会给我们的操作带来新的负担,于是就出现了默认值的概念。默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

mysql> create table t13 (id1 int not null,id2 int not null default 222);

Query OK, 0 rows affected (0.01 sec)

mysql> desc t13;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id1 | int(11) | NO | | NULL | |

| id2 | int(11) | NO | | 222 | |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.01 sec)

# 只向id1字段添加值,会发现id2字段会使用默认值填充

mysql> insert into t13 (id1) values (111);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;

+-----+-----+

| id1 | id2 |

+-----+-----+

| 111 | 222 |

+-----+-----+

1 row in set (0.00 sec)

# id1字段不能为空,所以不能单独向id2字段填充值;

mysql> insert into t13 (id2) values (223);

ERROR 1364 (HY000): Field 'id1' doesn't have a default value

# 向id1,id2中分别填充数据,id2的填充数据会覆盖默认值

mysql> insert into t13 (id1,id2) values (112,223);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;

+-----+-----+

| id1 | id2 |

+-----+-----+

| 111 | 222 |

| 112 | 223 |

+-----+-----+

2 rows in set (0.00 sec)

设置严格模式:

不支持对not null字段插入null值

不支持对自增长字段插入”值

不支持text字段有默认值

直接在mysql中生效(重启失效):

mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效):

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE : 唯一约束,指定某列或者几列组合不能重复

unique示例

方法一:

create table department1(

id int,

name varchar(20) unique,

comment varchar(100)

);

方法二:

create table department2(

id int,

name varchar(20),

comment varchar(100),

unique(name)

);

mysql> insert into department1 values(1,'IT','技术');

Query OK, 1 row affected (0.00 sec)

mysql> insert into department1 values(1,'IT','技术');

ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

not null 和 unique的结合

mysql> create table t1(id int not null unique);

Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.00 sec)

联合唯一

create table service(

id int primary key auto_increment,

name varchar(20),

host varchar(15) not null,

port int not null,

unique(host,port) #联合唯一

);

mysql> insert into service values

-> (1,'nginx','192.168.0.10',80),

-> (2,'haproxy','192.168.0.20',80),

-> (3,'mysql','192.168.0.30',3306)

-> ;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);

ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

PRIMARY KEY :

主键,指定该列的值可以唯一地标识该列记录

主键可以包含一个字段或多个字段。当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键。

主键可以在建置新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。

主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合。

单字段主键

============单列做主键===============

#方法一:not null+unique

create table department1(

id int not null unique, #主键

name varchar(20) not null unique,

comment varchar(100)

);

mysql> desc department1;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | NO | UNI | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

rows in set (0.01 sec)

#方法二:在某一个字段后用primary key

create table department2(

id int primary key, #主键

name varchar(20),

comment varchar(100)

);

mysql> desc department2;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

rows in set (0.00 sec)

#方法三:在所有字段后单独定义primary key

create table department3(

id int,

name varchar(20),

comment varchar(100),

primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

rows in set (0.01 sec)

# 方法四:给已经建成的表添加主键约束

mysql> create table department4(

-> id int,

-> name varchar(20),

-> comment varchar(100));

Query OK, 0 rows affected (0.01 sec)

mysql> desc department4;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> alter table department4 modify id int primary key;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc department4;

+---------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | YES | | NULL | |

| comment | varchar(100) | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

多字段主键

==================多列做主键================

create table service(

ip varchar(15),

port char(5),

service_name varchar(10) not null,

primary key(ip,port)

);

mysql> desc service;

+--------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-------------+------+-----+---------+-------+

| ip | varchar(15) | NO | PRI | NULL | |

| port | char(5) | NO | PRI | NULL | |

| service_name | varchar(10) | NO | | NULL | |

+--------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into service values

-> ('172.16.45.10','3306','mysqld'),

-> ('172.16.45.11','3306','mariadb')

-> ;

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');

ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

FOREIGN KEY :外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性

创建外键的条件

mysql> create table departments (dep_id int(4),dep_name varchar(11));

Query OK, 0 rows affected (0.02 sec)

mysql> desc departments;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| dep_id | int(4) | YES | | NULL | |

| dep_name | varchar(11) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 创建外键不成功

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));

ERROR 1215 (HY000): Cannot add foreign key

# 设置dep_id非空,仍然不能成功创建外键

mysql> alter table departments modify dep_id int(4) not null;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc departments;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| dep_id | int(4) | NO | | NULL | |

| dep_name | varchar(11) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));

ERROR 1215 (HY000): Cannot add foreign key constraint

# 当设置字段为unique唯一字段时,设置该字段为外键成功

mysql> alter table departments modify dep_id int(4) unique;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc departments; +----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| dep_id | int(4) | YES | UNI | NULL | |

| dep_name | varchar(11) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));

Query OK, 0 rows affected (0.02 sec)

外键操作示例

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一

create table department(

id int primary key,

name varchar(20) not null

)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除

create table employee(

id int primary key,

name varchar(20) not null,

dpt_id int,

foreign key(dpt_id)

references department(id)

on delete cascade # 级连删除

on update cascade # 级连更新

)engine=innodb;

#先往父表department中插入记录

insert into department values

(1,'教质部'),

(2,'技术部'),

(3,'人力资源部');

#再往子表employee中插入记录

insert into employee values

(1,'yuan',1),

(2,'nezha',2),

(3,'egon',2),

(4,'alex',2),

(5,'wusir',3),

(6,'李沁洋',3),

(7,'皮卡丘',3),

(8,'程咬金',3),

(9,'程咬银',3)

;

#删父表department,子表employee中对应的记录跟着删

mysql> delete from department where id=2;

Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;

+----+-----------+--------+

| id | name | dpt_id |

+----+-----------+--------+

| 1 | yuan | 1 |

| 5 | wusir | 3 |

| 6 | 李沁洋 | 3 |

| 7 | 皮卡丘 | 3 |

| 8 | 程咬金 | 3 |

| 9 | 程咬银 | 3 |

+----+-----------+--------+

rows in set (0.00 sec)

#更新父表department,子表employee中对应的记录跟着改

mysql> update department set id=2 where id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from employee;

+----+-----------+--------+

| id | name | dpt_id |

+----+-----------+--------+

| 1 | yuan | 1 |

| 5 | wusir | 2 |

| 6 | 李沁洋 | 2 |

| 7 | 皮卡丘 | 2 |

| 8 | 程咬金 | 2 |

| 9 | 程咬银 | 2 |

+----+-----------+--------+

rows in set (0.00 sec)

外键操作示例

AUTO_INCREMENT : 约束字段为自动增长,被约束的字段必须同时被key约束

#不指定id,则自动增长

create table student(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') default 'male'

);

mysql> desc student;

+-------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

| sex | enum('male','female') | YES | | male | |

+-------+-----------------------+------+-----+---------+----------------+

mysql> insert into student(name) values

-> ('egon'),

-> ('alex')

-> ;

mysql> select * from student;

+----+------+------+

| id | name | sex |

+----+------+------+

| 1 | egon | male |

| 2 | alex | male |

+----+------+------+

#也可以指定id

mysql> insert into student values(4,'asb','female');

Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+----+------+--------+

| id | name | sex |

+----+------+--------+

| 1 | egon | male |

| 2 | alex | male |

| 4 | asb | female |

| 7 | wsb | female |

+----+------+--------+

#对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长

mysql> delete from student;

Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;

Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');

mysql> select * from student;

+----+------+------+

| id | name | sex |

+----+------+------+

| 8 | ysb | male |

+----+------+------+

#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它

mysql> truncate student;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');

Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+----+------+------+

| id | name | sex |

+----+------+------+

| 1 | egon | male |

+----+------+------+

row in set (0.00 sec)

二,表结构的查询

单表查询

单表查询语法

SELECT DISTINCT 字段1,字段2... FROM 表名

WHERE 条件

GROUP BY field

HAVING 筛选

ORDER BY field

LIMIT 限制条数

关键字执行的优先级

关键字

作用

from

找到表

where

拿着where指定的约束条件,去文件/表中取出一条条记录

group by

将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组

select distinct

执行select(去重)

having

将分组的结果进行having过滤

order by

将结果按条件排序:order by

limit

限制结果的显示条数

简单查询

SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id

FROM employee;

SELECT * FROM employee;

SELECT emp_name,salary FROM employee;

#避免重复DISTINCT

SELECT DISTINCT post FROM employee;

#通过四则运算查询

SELECT emp_name, salary*12 FROM employee;

SELECT emp_name, salary*12 AS Annual_salary FROM employee;

SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式

CONCAT() 函数用于连接字符串

SELECT CONCAT('姓名: ',emp_name,' 年薪: ', salary*12) AS Annual_salary

FROM employee;

CONCAT_WS() 第一个参数为分隔符

SELECT CONCAT_WS(':',emp_name,salary*12) AS Annual_salary

FROM employee;

结合CASE语句:

SELECT

(

CASE

WHEN emp_name = 'jingliyang' THEN

emp_name

WHEN emp_name = 'alex' THEN

CONCAT(emp_name,'_BIGSB')

ELSE

concat(emp_name, 'SB')

END

) as new_name

FROM

employee;

where约束

select 字段名 from 表名 where 条件

类型

用法

比较运算符

> < >= <= <> !=

between 80 and 100

值在80到100之间(包含80,100)

in(80,90,100)

值是80或90或100

like 'e%'

通配符可以是%或_,%表示任意多字符, _表示一个字符

regexp

正则匹配

is / is not

is null / is not null

逻辑运算符

and or not

#1:单条件查询

SELECT emp_name FROM employee

WHERE post='sale';

#2:多条件查询

SELECT emp_name,salary FROM employee

WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND

SELECT emp_name,salary FROM employee

WHERE salary BETWEEN 10000 AND 20000;

SELECT emp_name,salary FROM employee

WHERE salary NOT BETWEEN 10000 AND 20000;

#4:关键字IS NULL(判断某个字段是否为NULL不能用等号,需要用IS)

SELECT emp_name,post_comment FROM employee

WHERE post_comment IS NULL;

SELECT emp_name,post_comment FROM employee

WHERE post_comment IS NOT NULL;

SELECT emp_name,post_comment FROM employee

WHERE post_comment=''; 注意''是空字符串,不是null

ps:

执行

update employee set post_comment='' where id=2;

再用上条查看,就会有结果了

#5:关键字IN集合查询

SELECT emp_name,salary FROM employee

WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;

SELECT emp_name,salary FROM employee

WHERE salary IN (3000,3500,4000,9000) ;

SELECT emp_name,salary FROM employee

WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询

通配符’%’

SELECT * FROM employee

WHERE emp_name LIKE 'eg%';

通配符’_’

SELECT * FROM employee

WHERE emp_name LIKE 'al__';

三,表结构

删除表

drop table 表名;

多表结构的创建和分析

分析步骤:

# 1,先站在左表的角度去找

是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)

# 2,再站在右表的角度去找

是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)

# 3,总结:

# 多对一:

如果只有步骤1成立,则是左表多对一右表

如果只有步骤2成立,则是右表多对一左表

# 多对多

如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

# 一对一:

如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然.这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

# 一对多或多对一 示例

mysql> create table press(

-> id int primary key auto_increment,

-> name varchar(20)

-> );

mysql> create table book(

-> id int primary key auto_increment,

-> name varchar(20),

-> press_id int not null,

-> foreign key(press_id) references press(id)

-> on delete cascade

-> on update cascade

-> );

mysql> insert into press(name) values

-> ('北京工业地雷出版社'),

-> ('人民音乐不好听出版社'),

-> ('知识产权没有用出版社');

mysql> insert into book(name,press_id) values

-> ('九阳神功',1),

-> ('九阴真经',2),

-> ('九阴白骨爪',2),

-> ('独孤九剑',3),

-> ('降龙十巴掌',2),

-> ('葵花宝典',3);

mysql> select * from book;

+----+-----------------+----------+

| id | name | press_id |

+----+-----------------+----------+

| 1 | 九阳神功 | 1 |

| 2 | 九阴真经 | 2 |

| 3 | 九阴白骨爪 | 2 |

| 4 | 独孤九剑 | 3 |

| 5 | 降龙十巴掌 | 2 |

| 6 | 葵花宝典 | 3 |

+----+-----------------+----------+

# 多对多

# 三张表:作者,书,对应关系

# 多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多

# 关联方式:foreign key+一张新的表

# 作者表

mysql> create table author(

-> id int primary key auto_increment,

-> name varchar(20)

-> );

# 这张表就存放作者表与书表的关系,即查询二者的关系查这表就可以了

mysql> create table author_book(

-> id int primary key auto_increment,

-> author_id int not null,

-> book_id int not null,

-> constraint fk_author foreign key(author_id) references author(id)

-> on delete cascade on update cascade,

-> constraint fk_book foreign key(book_id) references book(id)

-> on delete cascade on update cascade,

-> unique(author_id,book_id)

-> );

mysql> insert into author(name) values

-> ('孙悟空'),('猪八戒'),('唐僧'),('沙和尚');

mysql> insert into author_book(author_id,book_id) values

-> (1,4),(1,2),(1,3),(2,1),(2,6),(3,5),(4,2),(4,6);

mysql> select * from book;

+----+-----------------+----------+

| id | name | press_id |

+----+-----------------+----------+

| 1 | 九阳神功 | 1 |

| 2 | 九阴真经 | 2 |

| 3 | 九阴白骨爪 | 2 |

| 4 | 独孤九剑 | 3 |

| 5 | 降龙十巴掌 | 2 |

| 6 | 葵花宝典 | 3 |

+----+-----------------+----------+

mysql> select * from author;

+----+-----------+

| id | name |

+----+-----------+

| 1 | 孙悟空 |

| 2 | 猪八戒 |

| 3 | 唐僧 |

| 4 | 沙和尚 |

+----+-----------+

mysql> select * from author_book;

+----+-----------+---------+

| id | author_id | book_id |

+----+-----------+---------+

| 2 | 1 | 2 |

| 3 | 1 | 3 |

| 1 | 1 | 4 |

| 4 | 2 | 1 |

| 5 | 2 | 6 |

| 6 | 3 | 5 |

| 7 | 4 | 2 |

| 8 | 4 | 6 |

+----+-----------+---------+

# 一对一

# 两张表:用户表和博客表

# 一对一:一个用户只有一个博客

# 关联方式:foreign key+unique

# 用户表

mysql> create table user(

-> id int primary key auto_increment,

-> name varchar(12));

mysql> insert into user(name) values

-> ('孙悟空'),('猪八戒'),('沙悟净');

# 博客表

mysql> create table blog(

-> id int primary key auto_increment,

-> url varchar(40) not null unique,

-> name_id int unique,

-> foreign key(name_id) references user(id)

-> on delete cascade on update cascade);

mysql> insert into blog(url,name_id) values

-> ('www.baidu.com',1),('www.sohu.com',3),('www.taobao.com',2);

mysql> select * from user;

+----+-----------+

| id | name |

+----+-----------+

| 1 | 孙悟空 |

| 2 | 猪八戒 |

| 3 | 沙悟净 |

+----+-----------+

mysql> select * from blog;

+----+----------------+---------+

| id | url | name_id |

+----+----------------+---------+

| 1 | www.baidu.com | 1 |

| 2 | www.sohu.com | 3 |

| 3 | www.taobao.com | 2 |

+----+----------------+---------+

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页