1.
创建表格
Enter password:**********
Welcome to theMySQL monitor. Commands end with ; or\g.
Your MySQLconnection id is 23
Server version:5.6.13-enterprise-commercial-advanced MySQLEnterprise Server -
AdvancedEdition (Commercial)
Copyright (c)2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is aregistered 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> usecsdn;
Databasechanged
mysql>create table biaoming(stu_id int primary key auto_increment,name varchar
10) not nullunique,sex int not null,tel varchar(11) not null,email varchar(25)
unique,scoresint not null,awarda varchar(10) not null
-> ,pro varchar(10) not null,jiguanvarchar(50));
Query OK, 0rows affected (0.94 sec)
mysql> descbiaoming;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| stu_id |int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | UNI | NULL | |
| sex | int(11) | NO | | NULL | |
| tel | varchar(11) | NO | | NULL | |
| email | varchar(25) | YES | UNI | NULL | |
| scores |int(11) | NO | | NULL | |
| awarda |varchar(10) | NO | | NULL | |
| pro | varchar(10) | NO | | NULL | |
| jiguan |varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
9 rows in set(0.08 sec)
插入name
mysql>insert into biaoming values(null,'松井',0,12345678909,'13256642682@qq.com
',234,'一等奖','3G','保定',);
ERROR 1064(42000): You have an error in your SQL syntax; check the manual that
corresponds toyour MySQL server version for the right syntax to use near ')' at
line 1
mysql>insert into biaoming values(null,'坝上',0,12345678909,'1344442682@qq.com'
,234,'二等奖','3G','保定');
Query OK, 1 rowaffected (0.09 sec)
mysql>insert into biaoming values(null,'野田',0,12345678909,'1354442682@qq.com'
,234,'三等奖','3G','保定');
Query OK, 1 rowaffected (0.11 sec)
mysql>insert into biaoming values(null,'小犬',0,12345678909,'3354442682@qq.com'
,234,'四等奖','3G','保定');
Query OK, 1 rowaffected (0.06 sec)
mysql>insert into biaoming values(null,'叫兽',0,12345678909,'4354442682@qq.com'
,234,'五等奖','3G','保定');
Query OK, 1 rowaffected (0.11 sec)
mysql>select *from biaoming;
+--------+--------+-----+-------------+--------------------+--------+-----------
---+-----+--------+
| stu_id |name | sex | tel | email | scores | awarda
| pro | jiguan |
+--------+--------+-----+-------------+--------------------+--------+-----------
---+-----+--------+
| 1 | 松井 | 0| 12345678909 | 13256642682@qq.com | 234 | 一等奖
| 3G | 保定 |
| 2 | 坝上 | 0| 12345678909 | 1344442682@qq.com | 234 | 二等奖
| 3G | 保定 |
| 3 | 野田 | 0| 12345678909 | 1354442682@qq.com | 234 | 三等奖
| 3G | 保定 |
| 4 | 小犬 | 0| 12345678909 | 3354442682@qq.com | 234 | 四等奖
| 3G | 保定 |
| 5 | 叫兽 | 0| 12345678909 | 4354442682@qq.com | 234 | 五等奖
| 3G | 保定 |
+--------+--------+-----+-------------+--------------------+--------+-----------
---+-----+--------+
5 rows in set(0.00 sec)
2.管理员表
mysql> create table users(id int(4) notnull auto_increment primary key,type int
(4) not null,username char(30) not null ,password char(30) not null,limits char
(30) not null);
Query OK, 0rows affected (0.70 sec)
mysql> descusers;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| type | int(4) | NO | | NULL | |
| username |char(30) | NO | | NULL | |
| password |char(30) | NO | | NULL | |
| limits | char(30) | NO | | NULL | |
+----------+----------+------+-----+---------+----------------+
5 rows in set(0.03 sec)
3
(1) 会员表
mysql>create database webnews;
Query OK, 1 rowaffected (0.09 sec)
mysql>create table member(memberID int(4) not null primary key,
-> loginName varchar(50) not null,
-> loginPwd varchar(50) not null,
-> realName varchar(50) not null,
-> email varchar(100) not null);
Query OK, 0rows affected (0.42 sec)
mysql> descmember;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| memberID | 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.08 sec)
(2) 新闻评论表
mysql>create table comments(commentID int(4) not null primary key,reviewer int(
4),titlevarchar(100) not null,content text(16) not null,commentDate datetime no
tnull default now(),newsID int(4),foreign key(reviewer)references member(member
ID),foreignkey(newsID) references news(newsID));
QueryOK, 0 rows affected (0.25 sec)
mysql>desc comments;
+-------------+--------------+------+-----+-------------------+-------+
|Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------+
|commentID | int(4) | NO | PRI | NULL | |
|reviewer | int(4) | YES | MUL | NULL | |
|title | varchar(100) | NO | | NULL | |
|content | tinytext | NO | | NULL | |
|commentDate | datetime | NO | | CURRENT_TIMESTAMP | |
|newsID | int(4) | YES | MUL | NULL | |
+-------------+--------------+------+-----+-------------------+-------+
6rows in set (0.00 sec)
(3) 新闻版块表
mysql>create table comments(commentID int(4) not null primary key,reviewer int(
4),titlevarchar(100) not null,content text(16) not null,commentDate datetime no
tnull default now(),newsID int(4),foreign key(reviewer)references member(member
ID),foreignkey(newsID) references news(newsID));
QueryOK, 0 rows affected (0.25 sec)
mysql>desc comments;
+-------------+--------------+------+-----+-------------------+-------+
|Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+-------+
|commentID | int(4) | NO | PRI | NULL | |
|reviewer | int(4) | YES | MUL | NULL | |
|title | varchar(100) | NO | | NULL | |
|content | tinytext | NO | | NULL | |
|commentDate | datetime | NO | | CURRENT_TIMESTAMP | |
|newsID | int(4) | YES | MUL | NULL | |
+-------------+--------------+------+-----+-------------------+-------+
6rows in set (0.00 sec)
(4).新闻表
mysql>create table news(newsID int not null primary key,submdate datetime not n
ull,authervarchar(50) not null,title varchar(100) not null,content text not nUl
l,sectionIDint,keywords varchar(100),clickedtimes int(4) not null,picture varch
ar(100),foreignkey (sectionID) references section (sectionID));
Query OK, 0rows affected (0.61 sec)
mysql> descnews;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| newsID | int(11) | NO | PRI | NULL | |
| submdate | datetime | NO | | NULL | |
| auther | varchar(50) | NO | | NULL | |
| title | varchar(100) | NO | | NULL | |
| content | text | NO | | NULL | |
|sectionID | int(11) | YES | MUL | NULL | |
| keywords | varchar(100) | YES | |NULL | |
| clickedtimes| int(4) | NO | | NULL | |
| picture | varchar(100) | YES | |NULL | |
+--------------+--------------+------+-----+---------+-------+
9 rows in set(0.00 sec)