❤️Author: 老九 ☕️个人博客:老九的CSDN博客 🙏 个人名言:不可控之事 乐观面对 😍 系列专栏:Mysql通关系列 文章目录 数据库约束no
❤️Author: 老九
☕️个人博客:老九的CSDN博客
🙏 个人名言:不可控之事 乐观面对
😍 系列专栏:Mysql通关系列
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)
create table student(id int,name varchar(20) default '匿名');
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)
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)
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
--结束END--
本文标题: MySQL数据库约束,表的设计
本文链接: https://www.lsjlt.com/news/411040.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
2024-05-02
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0