理解MySQL字段约束的概念、基本使用。企业新闻管理系统数据库建设

本文链接：https://blog.csdn.net/gujianpenggu/article/details/11981017

(1) 创建一个新生报名表，
表名：baoming字段分别为：

编号（主键，自动增加）
姓名（不能为空，唯一）

性别（不能为空）
手机号（不能为空）

邮箱（可以为空，唯一）

高考分数（不能为空）

获得奖项（不能为空）抽奖奖品

专业（不能为空）

QQ号（不能为空）

家庭地址

5.1.3 任务要求

(1) 整理建表语句并上交

(2) 插入5条数据测试，并理解数据完整性约束。

5条数据姓名分别是：松井、坝上、野田、小犬、叫兽

Enter password: ***
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.13-enterprise-commercial-advanced MySQL Enterprise Server -
Advanced Edition (Commercial)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

显示现有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csdn3g |
| dazuoye |
| gujianpeng |
| mysql |
| performance_schema |
| student |
| test |
| webnews |
| zuoye |
+--------------------+
10 rows in set (0.00 sec)

创建一个lianxi数据库
mysql> create database lianxi;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csdn3g |
| dazuoye |
| gujianpeng |
| lianxi |
| mysql |
| performance_schema |
| student |
| test |
| webnews |
| zuoye |
+--------------------+
11 rows in set (0.00 sec)

用lianli数据库
mysql> use lianxi;
Database changed

(3) 创建一个新生报名表，
表名：baoming字段分别为：
编号（主键，自动增加）
姓名（不能为空，唯一）
性别（不能为空）
手机号（不能为空）
邮箱（可以为空，唯一）
高考分数（不能为空）
获得奖项（不能为空）抽奖奖品
专业（不能为空）
QQ号（不能为空）
家庭地址

mysql> create table baoming(id int primary key auto_increment,
-> name varchar(20) not null unique,
-> sex varchar(20) not null,
-> mobile varchar(20) not null,
-> mail varchar(20) unique,
-> grade int not null,
-> awarde varchar(20) not null,
-> major varchar(20) not null,
-> QQ int(30) not null,
-> family varchar(50)
-> );
Query OK, 0 rows affected (1.42 sec)

查看表名数据
mysql> desc baoming;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| sex | varchar(20) | NO | | NULL | |
| mobile | varchar(20) | NO | | NULL | |
| mail | varchar(20) | YES | UNI | NULL | |
| grade | int(11) | NO | | NULL | |
| awarde | varchar(20) | NO | | NULL | |
| major | varchar(20) | NO | | NULL | |
| QQ | int(30) | NO | | NULL | |
| family | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
10 rows in set (0.07 sec)

插入5条数据测试，
5条数据姓名分别是：松井、坝上、野田、小犬、叫兽.

mysql> insert into baoming values(1,'松井','男','182032823','652323@qq.com','435
','苹果8s','软件开发','652323','高老庄');
Query OK, 1 row affected (0.12 sec)

mysql> insert into baoming values(2,'坝上','男','182032823','6523523@qq.com','43
55','苹果10s','软件','6523523','花果山');
Query OK, 1 row affected (0.09 sec)

mysql> insert into baoming values(3,'野田 ','不男不女','182032823','16523523@qq.
com','4355','苹果120s','软件','16523523','盘丝岭');
Query OK, 1 row affected (0.11 sec)

mysql> insert into baoming values(4,'小犬 ','不男不女','182032823','116523523@qq
.com','4355','苹果120s','软件','116523523','盘丝岭');
Query OK, 1 row affected (0.08 sec)

mysql> insert into baoming values(5,'叫兽 ','不女','182032823','1216523523@qq.co
m','4355','苹果120s','软件','1216523523','盘丝岭');
Query OK, 1 row affected (0.14 sec)

查看biaoming数据
mysql> desc baoming;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| sex | varchar(20) | NO | | NULL | |
| mobile | varchar(20) | NO | | NULL | |
| mail | varchar(20) | YES | UNI | NULL | |
| grade | int(11) | NO | | NULL | |
| awarde | varchar(20) | NO | | NULL | |
| major | varchar(20) | NO | | NULL | |
| QQ | int(30) | NO | | NULL | |
| family | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

显示插入的新数据信息
mysql> select *from baoming;
+----+---------+--------------+-----------+-------------------+-------+---------
---+--------------+------------+-----------+
| id | name | sex | mobile | mail | grade | awarde
| major | QQ | family |
+----+---------+--------------+-----------+-------------------+-------+---------
---+--------------+------------+-----------+
| 1 | 松井 | 男 | 182032823 | 652323@qq.com | 435 | 苹果8s
| 软件开发 | 652323 | 高老庄 |
| 2 | 坝上 | 男 | 182032823 | 6523523@qq.com | 4355 | 苹果10s
| 软件 | 6523523 | 花果山 |
| 3 | 野田 | 不男不女 | 182032823 | 16523523@qq.com | 4355 | 苹果120s
| 软件 | 16523523 | 盘丝岭 |
| 4 | 小犬 | 不男不女 | 182032823 | 116523523@qq.com | 4355 | 苹果120s
| 软件 | 116523523 | 盘丝岭 |
| 5 | 叫兽 | 不女 | 182032823 | 1216523523@qq.com | 4355 | 苹果120s
| 软件 | 1216523523 | 盘丝岭 |
+----+---------+--------------+-----------+-------------------+-------+---------
---+--------------+------------+-----------+
5 rows in set (0.00 sec)

mysql>