文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL数据库约束,表的设计

2023-09-17 11:52

关注

❤️Author: 老九
☕️个人博客:老九的CSDN博客
🙏 个人名言:不可控之事 乐观面对
😍 系列专栏:MySQL通关系列

文章目录

not null

 create table student (id int not null,name varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> desc student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)

unique

default

create table student(id int,name varchar(20) default '匿名');

primary key 主键

auto_increment

 create table student (id int primary key auto_increment,name varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || name  | varchar(20) | YES  |     | NULL    |                |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> insert into student values(null,'张三');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+--------+| id | name   |+----+--------+|  1 | 张三   |+----+--------+1 row in set (0.00 sec)

foreign key 外键

mysql> create table class (    -> id int primary key,    -> name varchar(20) not null    -> );Query OK, 0 rows affected (0.04 sec)mysql> create table student (    -> id int primary key,    -> name varchar(20) not null,    -> email varchar(20) default 'unknow',    -> QQ varchar(20) unique,    -> classId int , foreign key (classId) references class(id)    -> );Query OK, 0 rows affected (0.03 sec)mysql> desc class;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | NO   |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec)mysql> desc student;+---------+-------------+------+-----+---------+-------+| Field   | Type        | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| id      | int(11)     | NO   | PRI | NULL    |       || name    | varchar(20) | NO   |     | NULL    |       || email   | varchar(20) | YES  |     | unknow  |       || QQ      | varchar(20) | YES  | UNI | NULL    |       || classId | int(11)     | YES  | MUL | NULL    |       |+---------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

check

create table test_user (   id int,   name varchar(20),   sex varchar(1),   check (sex ='男' or sex='女'));

一对一

一对多

多对多

多对多建表实例

-- 学生表mysql> create table test_student (    -> id int primary key,    -> name varchar(10) default 'unknow'    -> );Query OK, 0 rows affected (0.03 sec)-- 选课表mysql> create table test_course (    -> id int primary key,    -> name varchar(20) default 'unknow'    -> );Query OK, 0 rows affected (0.02 sec)-- 成绩表mysql> create table test_score (    -> studentId int,    -> courseId int,    -> score int,    -> foreign key (studentId) references test_student(id),    -> foreign key (courseId) references test_course(id)    -> );Query OK, 0 rows affected (0.02 sec)mysql> desc test_student;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(10) | YES  |     | unknow  |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc test_coures;ERROR 1146 (42S02): Table 'java_5_27.test_coures' doesn't existmysql> desc test_course;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   | PRI | NULL    |       || name  | varchar(20) | YES  |     | unknow  |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> desc test_score;+-----------+---------+------+-----+---------+-------+| Field     | Type    | Null | Key | Default | Extra |+-----------+---------+------+-----+---------+-------+| studentId | int(11) | YES  | MUL | NULL    |       || courseId  | int(11) | YES  | MUL | NULL    |       || score     | int(11) | YES  |     | NULL    |       |+-----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)

插入数据到实例实现多对多

mysql> insert into test_student values (1, 'listen');Query OK, 1 row affected (0.01 sec)mysql> insert into test_course values (1, '数学');Query OK, 1 row affected (0.00 sec)mysql> insert into test_student values (2, 'Faker');Query OK, 1 row affected (0.00 sec)mysql> insert into test_course values (2, '数学');Query OK, 1 row affected (0.00 sec)mysql> insert into test_score values(1, 1, 90);Query OK, 1 row affected (0.00 sec)mysql> insert into test_score values (1, 2, 99);Query OK, 1 row affected (0.00 sec)mysql> insert into test_score values (2, 1, 50);Query OK, 1 row affected (0.00 sec)mysql> insert into test_score values (2, 2, 60);Query OK, 1 row affected (0.00 sec)mysql> select * from test_student;+----+--------+| id | name   |+----+--------+|  1 | listen ||  2 | Faker  |+----+--------+2 rows in set (0.00 sec)mysql> select * from test_course;+----+--------+| id | name   |+----+--------+|  1 | 数学   ||  2 | 语文   |+----+--------+2 rows in set (0.00 sec)mysql> select * from test_score;+-----------+----------+-------+| studentId | courseId | score |+-----------+----------+-------+|         1 |        1 |    90 ||         1 |        2 |    99 ||         2 |        1 |    50 ||         2 |        2 |    60 |+-----------+----------+-------+4 rows in set (0.00 sec)

————————————————————————
♥♥♥码字不易,大家的支持就是我坚持下去的动力♥♥♥
版权声明:本文为CSDN博主「浦上青天」的原创文章

来源地址:https://blog.csdn.net/partworld/article/details/125120375

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯