1、
创建表格baoming 并录入信息。
mysql> create table baoming(stu_id int primary key auto_increment,name varchar(2
00) not null unique,sex int not null,phone int not null,e_mall varchar(200) not
null unique,scr int(11) not null,award varchar(200) not null default 'phone',maj
or varchar(200) not null,qq int(30) not null,address varchar(500));
Query OK, 0 rows affected (0.88 sec)
mysql> desc biaoming;
ERROR 1146 (42S02): Table 'zhu.biaoming' doesn't exist
mysql> desc baoming;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(200) | NO | UNI | NULL | |
| sex | int(11) | NO | | NULL | |
| phone | int(11) | NO | | NULL | |
| e_mall | varchar(200) | NO | UNI | NULL | |
| scr | int(11) | NO | | NULL | |
| award | varchar(200) | NO | | phone | |
| major | varchar(200) | NO | | NULL | |
| qq | int(30) | NO | | NULL | |
| address | varchar(500) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
10 rows in set (0.15 sec)
mysql> select * from baoming;
Empty set (0.05 sec)
插入5条数据测试,并理解数据完整性约束。
5条数据姓名分别是:松井、坝上、野田、小犬、叫兽
mysql> insert into baoming values(1,'松井',0,1111,22,333,'手机','软件',123,'保定
');
Query OK, 1 row affected (0.13 sec)
mysql> insert into baoming values(2,'坝上',0,1111,32,333,'手机','软件',123,'保定
');
Query OK, 1 row affected (0.01 sec)
mysql> insert into baoming values(3,'野田',0,1111,42,333,'手机','软件',123,'保定
');
Query OK, 1 row affected (0.00 sec)
mysql> insert into baoming values(4,'小泉',0,1111,43,333,'手机','软件',123,'保定
');
Query OK, 1 row affected (0.04 sec)
mysql> insert into baoming values(5,'叫兽',0,1111,44,333,'手机','软件',123,'保定
');
Query OK, 1 row affected (0.00 sec)
查询表格
2、
创建一个新闻管理系统数据库名称:webnews
mysql> create database webnews;
Query OK, 1 row affected (0.08 sec)
mysql> use webnews;
Database changed
1、创建管理员表 并录入字段
mysql> create table Users(id int(4)not null primary key auto_increment,type int(
4)not null,username char(30)not null,password char(30)not null,limits char(30)no
t null);
Query OK, 0 rows affected (0.15 sec)
mysql>
2、会员表(Member)
'
字段名 | 类型 | 长度 | 是否为空 | 注释 | 默认值 |
memberID | int | 4 | Not null | 主键,会员ID | 无 |
loginName | varchar | 50 | Not null | 登录名 | 无 |
loginPwd | varchar | 50 | Not null | 登录密码 | 无 |
realName | varchar | 50 | Not null | 真实姓名 | 无 |
| varchar | 100 | Not null | 会员email | 无 |
mysql> create table member(member int(4)not null primary key,loginname varchar(
50)not null,loginpwd varchar(50)not null,realname varchar(50)not null,email varc
har(100)not null);
Query OK, 0 rows affected (0.07 sec)
mysql> desc member;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| member | int(4) | NO | PRI | NULL | |
| loginname | varchar(50) | NO | | NULL | |
| loginpwd | varchar(50) | NO | | NULL | |
| realname | varchar(50) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.05 sec)
3、新闻评论表(Comments)
字段名 | 类 型 | 长 度 | 是否为空 | 注 释 | 默认值 |
CommentID | int | 4 | 否 | 主键 评论标识 | (无) |
reviewer | int | 4 | 是 | 外键 评论者姓名(会员ID) | (无) |
Title | varchar | 100 | 否 | 评论标题 | (无) |
Content | Text | 16 | 否 | 评论内容 | (无) |
CommentDate | Datetime | 8 | 否 | 评论时间 | Now() |
newsID | int | 4 | 是 | 外键 所属新闻ID | (无) |
mysql> create table comments(id int(4)not null primary key,
-> reviewer int(4),
-> title varchar(100) not null,
-> content text(16) not null,
-> commentdate datetime(6)not null,
-> newid int(4),
-> foreign key (reviewer) references member (member),
-> foreign key (newid) references section(id));
Query OK, 0 rows affected (0.20 sec)
mysql> desc comments;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| reviewer | int(4) | YES | MUL | NULL | |
| title | varchar(100) | NO | | NULL | |
| content | tinytext | NO | | NULL | |
| commentdate | datetime(6) | NO | | NULL | |
| newid | int(4) | YES | MUL | NULL | |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
4、新闻版块表(section)
字段名 | 类 型 | 长 度 | 是否为空 | 注 释 | 默认值 |
sectionID | int | 4 | 否 | 主键 版块标识 | (无) |
sectionName | Varchar | 30 | 否 | 模板名 | (无) |
description | Varchar | 100 | 是 | 版块描述 | (无) |
mysql> create table section(id int(4)not null primary key,sectionname varchar(30
)not null,description varchar(100));
Query OK, 0 rows affected (0.23 sec)
mysql> desc section;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| sectionname | varchar(30) | NO | | NULL | |
| description | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.06 sec)
1、 新闻表(news)
字段名 | 类 型 | 长 度 | 是否为空 | 注 释 | 默认值 |
NewsID | int | 4 | 否 | 主键 新闻标识 | (无) |
submtDate | datetime | 8 | 否 | 新闻提交时间 | now() |
Author | varchar | 50 | 否 | 记者姓名 | (无) |
Title | varchar | 100 | 否 | 新闻标题 | (无) |
Content | text | 16 | 否 | 新闻内容 | (无) |
sectionID | int | 4 | 是 | 外键 所属版块 | (无) |
keyWords | Varchar | 100 | 是 | 关键字 | (无) |
clickedTimes | Int | 4 | 否 | 点击次数 | 0 |
picture | varchar(100) | varchar(100) | 是 | 图片,存放图片路径 | default NULL |
mysql> create table news(id int(4)not null primary key,
-> submtdate datetime not null default now(),
-> author varchar(50) not null,
-> title varchar(100)not null,
-> content text(16)not null,
-> section int(4),
-> clickedtimes int(4)not null default 0,
-> foreign key(section) references section (id));
Query OK, 0 rows affected (0.21 sec)