Enter
password
: ****
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
4
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the buffer.
mysql>
mysql>
drop
database
if exists yb;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
create
database
yb;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show databases;
+
|
Database
|
+
| information_schema |
| mysql |
| phpmyadmin |
| test |
| yb |
| ylb |
+
6
rows
in
set
(0.00 sec)
mysql>
mysql> use yb;
Database
changed
mysql>
mysql>
create
table
users
-> (
-> userID
int
not
null
,
-> username
varchar
(100),
-> nicheng
varchar
(100)
not
null
,
-> sex
char
(2),
-> regdate
date
-> );
Query OK, 0
rows
affected (0.11 sec)
mysql>
mysql>
desc
users;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| userID |
int
(11) |
NO
| | | |
| username |
varchar
(100) | YES | |
NULL
| |
| nicheng |
varchar
(100) |
NO
| | | |
| sex |
char
(2) | YES | |
NULL
| |
| regdate |
date
| YES | |
NULL
| |
+
5
rows
in
set
(0.00 sec)
mysql>
mysql>
alter
table
users
->
add
constraint
pk_users_userid
primary
key
(userid);
Query OK, 0
rows
affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
alter
table
users
->
add
constraint
un_users_nicheng
unique
(nicheng);
Query OK, 0
rows
affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
alter
table
users
->
add
constraint
ch_users_sex
check
(sex
in
(
'男'
,
'女'
));
Query OK, 0
rows
affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
alter
table
users
->
modify
username
varchar
(100)
not
null
;
Query OK, 0
rows
affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
alter
table
users
->
modify
regdate
date
default
'2012-1-1'
;
Query OK, 0
rows
affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql>
desc
users;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| userID |
int
(11) |
NO
| PRI | | |
| username |
varchar
(100) |
NO
| | | |
| nicheng |
varchar
(100) |
NO
| UNI | | |
| sex |
char
(2) | YES | |
NULL
| |
| regdate |
date
| YES | | 2012-01-01 | |
+
5
rows
in
set
(0.00 sec)
mysql>
mysql>
create
table
orders
-> (
-> orderID
int
auto_increment
primary
key
,
-> total
numeric
(6,2)
check
(total>0),
-> userid
int
not
null
-> );
Query OK, 0
rows
affected (0.09 sec)
mysql>
mysql>
alter
table
orders
->
add
constraint
fk_orders_users_userid
foreign
key
(userid)
references
-> users(userid);
Query OK, 0
rows
affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
desc
orders;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| orderID |
int
(11) |
NO
| PRI |
NULL
| auto_increment |
| total |
decimal
(6,2) | YES | |
NULL
| |
| userid |
int
(11) |
NO
| MUL | | |
+
3
rows
in
set
(0.00 sec)
mysql>
mysql>
alter
table
users
->
drop
constraint
ch_users_sex;
ERROR 1064 (42000): You have an error
in
your SQL syntax;
check
the manual that
corresponds
to
your MySQL server version
for
the
right
syntax
to
use near
'const
raint ch_users_sex'
at
line 2
mysql>
mysql>
create
table
emp
-> (
-> empno
int
,
-> ename
varchar
(100)
-> );
Query OK, 0
rows
affected (0.09 sec)
mysql>
desc
emp;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| empno |
int
(11) | YES | |
NULL
| |
| ename |
varchar
(100) | YES | |
NULL
| |
+
2
rows
in
set
(0.05 sec)
mysql>
mysql>
alter
table
emp
->
add
sex
char
(2);
Query OK, 0
rows
affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
desc
emp;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| empno |
int
(11) | YES | |
NULL
| |
| ename |
varchar
(100) | YES | |
NULL
| |
| sex |
char
(2) | YES | |
NULL
| |
+
3
rows
in
set
(0.01 sec)
mysql>
alter
table
emo
-> ;
ERROR 1146 (42S02):
Table
'yb.emo'
doesn't exist
mysql>
alter
table
emp
->
add
nicheng
varchar
(100)
not
null
unique
;
Query OK, 0
rows
affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
desc
emp;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| empno |
int
(11) | YES | |
NULL
| |
| ename |
varchar
(100) | YES | |
NULL
| |
| sex |
char
(2) | YES | |
NULL
| |
| nicheng |
varchar
(100) |
NO
| PRI | | |
+
4
rows
in
set
(0.00 sec)
mysql>
mysql>
alter
table
emp
->
drop
column
nicheng;
Query OK, 0
rows
affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
desc
emp;
+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
| empno |
int
(11) | YES | |
NULL
| |
| ename |
varchar
(100) | YES | |
NULL
| |
| sex |
char
(2) | YES | |
NULL
| |
+
3
rows
in
set
(0.00 sec)
mysql>