专题图：
编号：ylbtech MySQL100010010，揭示Google，百度，facebook，互动百科为什么选择MySQL做数据库
| 内容简介 MySQL目录 |
--===================================
--ylb：MySQL
--1，MySQL提纲
--16:41 2012-1-6
--我的人生除了技术，我还能拥有什么？
--那我就把此生献给她吧！希望她给能给更多的人带来心灵的温暖
--===================================
1,什么是MySQL？
2，数据类型
3，基本操作命令
4，数据库的操作
5，建表、约束、
6，表结构的修改与约束（添加、删除）
7，数据库的备份
8，日常维护与管理
9，高级查询（子查询，函数运用）
| 技术与环境 |
操作系统: |
windows |
语言类别: |
SQL |
| thankyou: | sunshine, 谢谢你的默默付出 | 数据库: |
MySQL |
学习软件: |
mysql-essential-5.1.55-win32 |
||
课程总策划: |
yuanbo |
English name: |
sunshine |
个人主页: |
http://www.cnblogs.com/ylbtech/ |
||
科研团队: |
ylbtech |
教研团队: |
ylbtech |
| MySQL: 1,什么是MySQL？ |
--============================================
--ylb: MySQL
--1,什么是MySQL？
--============================================
MySQL是一个真正的多用户、多线程SQL数据库服务器。SQL（结构化查询语言）是世界上最流行的和标准化的数据库语言。MySQL是以一个客户机/服务器结构的实现，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。
SQL是一种标准化的语言，它使得存储、更新和存取信息更容易。例如，你能用SQL语言为一个网站检索产品信息及存储顾客信息，同时MySQL也足够快和灵活以允许你存储记录文件和图像。
MySQL 主要目标是快速、健壮和易用。最初是因为我们需要这样一个SQL服务器，它能处理与任何可不昂贵硬件平台上提供数据库的厂家在一个数量级上的大型数据库，但速度更快，MySQL就开发出来。自1996年以来，我们一直都在使用MySQL，其环境有超过 40 个数据库，包含 10,000个表，其中500多个表超过7百万行，这大约有100 个吉字节(GB)的关键应用数据。
MySQL建立的基础是业已用在高要求的生产环境多年的一套实用例程。尽管MySQL仍在开发中，但它已经提供一个丰富和极其有用的功能集。
MySQL的官方发音是“My Ess Que Ell”(不是 MY-SEQUEL )。
--ylb: MySQL
--1,什么是MySQL？
--============================================
MySQL是一个真正的多用户、多线程SQL数据库服务器。SQL（结构化查询语言）是世界上最流行的和标准化的数据库语言。MySQL是以一个客户机/服务器结构的实现，它由一个服务器守护程序mysqld和很多不同的客户程序和库组成。
SQL是一种标准化的语言，它使得存储、更新和存取信息更容易。例如，你能用SQL语言为一个网站检索产品信息及存储顾客信息，同时MySQL也足够快和灵活以允许你存储记录文件和图像。
MySQL 主要目标是快速、健壮和易用。最初是因为我们需要这样一个SQL服务器，它能处理与任何可不昂贵硬件平台上提供数据库的厂家在一个数量级上的大型数据库，但速度更快，MySQL就开发出来。自1996年以来，我们一直都在使用MySQL，其环境有超过 40 个数据库，包含 10,000个表，其中500多个表超过7百万行，这大约有100 个吉字节(GB)的关键应用数据。
MySQL建立的基础是业已用在高要求的生产环境多年的一套实用例程。尽管MySQL仍在开发中，但它已经提供一个丰富和极其有用的功能集。
MySQL的官方发音是“My Ess Que Ell”(不是 MY-SEQUEL )。
| MySQL：2，数据类型 |
--================================
--ylb:MySQL
--1,数据类型
--17:04 2012-1-6
--================================
| MySQL：3，基本操作命令 |
--================================
--ylb:MySQL
--1,数据类型
--17:04 2012-1-6
--================================
1、显示数据库列表。
show databases;
刚开始时才两个数据库：mysql和test。mysql库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。
2、显示库中的数据表：
use mysql； //打开库，学过FOXBASE的一定不会陌生吧
show tables;
3、显示数据表的结构：
describe 表名;
4、建库：
create database 库名;
5、建表：
use 库名；
create table 表名 (字段设定列表)；
6、删库和删表:
drop database 库名;
drop table 表名；
7、将表中记录清空：
delete from 表名;
8、显示表中的记录：
select * from 表名;
9、显示use的数据库名：
SELECT DATABASE();
10、显示当前的user：
SELECT USER();
| MySQL：4，数据库的操作 |
--================================
--ylb:MySQL
--1，数据库的操作
--2，建库、删库、建表、操作（增删改查）、删表
--17:04 2012-1-6
--================================
Enter
password
: ****
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
1
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>
--1,
mysql>
drop
database
if exists yb;
Query OK, 5
rows
affected (0.19 sec)
mysql>
--2,
mysql>
create
database
yb;
Query OK, 1 row affected (0.00 sec)
mysql>
--3,
mysql> use yb;
Database
changed
mysql>
--4,create table
mysql>
create
table
users
-> (
-> userID
int
not
null
,
-> username
varchar
(100)
-> );
Query OK, 0
rows
affected (0.11 sec)
mysql>
--5,desc
mysql>
desc
users;
+
----------+--------------+------+-----+---------+-------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
----------+--------------+------+-----+---------+-------+
| userID |
int
(11) |
NO
| | | |
| username |
varchar
(100) | YES | |
NULL
| |
+
----------+--------------+------+-----+---------+-------+
2
rows
in
set
(0.00 sec)
mysql>
--6,insert
mysql>
insert
into
users(userID,username)
values
(101,
'pengYun'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
users(userID,username)
values
(102,
'wenFei'
);
Query OK, 1 row affected (0.00 sec)
mysql>
--7,select
mysql>
select
*
from
users;
+
--------+----------+
| userID | username |
+
--------+----------+
| 101 | pengYun |
| 102 | wenFei |
+
--------+----------+
2
rows
in
set
(0.00 sec)
mysql>
--8,update
mysql>
update
users
set
username=
'huWenFei'
where
userID=102;
Query OK, 1 row affected (0.00 sec)
Rows
matched: 1 Changed: 1 Warnings: 0
mysql>
select
*
from
users;
+
--------+----------+
| userID | username |
+
--------+----------+
| 101 | pengYun |
| 102 | huWenFei |
+
--------+----------+
2
rows
in
set
(0.00 sec)
mysql>
--9,delete
mysql>
delete
from
users
where
userid=101;
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
users;
+
--------+----------+
| userID | username |
+
--------+----------+
| 102 | huWenFei |
+
--------+----------+
1 row
in
set
(0.00 sec)
mysql>
--10,truncate table
mysql>
truncate
table
users;
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
*
from
users;
Empty
set
(0.00 sec)
mysql>
--11,commit
mysql>
commit
;
Query OK, 0
rows
affected (0.00 sec)
mysql>
--12,drop table
mysql>
drop
table
users;
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
*
from
users;
ERROR 1146 (42S02):
Table
'yb.users'
doesn
't exist
mysql> --13,
mysql> drop database yb;
Query OK, 0 rows affected (0.00 sec)
mysql> use yb;
ERROR 1049 (42000): Unknown database '
yb'
mysql>
--14,
mysql> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| phpmyadmin |
| test |
| ylb |
+
--------------------+
5
rows
in
set
(0.06 sec)
mysql>
|
| MySQL：4_2,表的基本约束 |
--================================
--ylb:MySQL
--1,表的约束添加
--2，主键、外键、唯一、检查、非空、默认
--17:04 2012-1-6
--================================
--====================================
--P1:默认约束
--====================================
Enter
password
: ****
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
2
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>
--1,
mysql>
drop
database
if exists yb;
Query OK, 0
rows
affected, 1 warning (0.00 sec)
mysql>
--2,
mysql>
create
database
yb;
Query OK, 1 row affected (0.00 sec)
mysql>
--3,
mysql> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| phpmyadmin |
| test |
| yb |
| ylb |
+
--------------------+
6
rows
in
set
(0.00 sec)
mysql>
--4,
mysql> use yb;
Database
changed
mysql>
--5,create table
mysql>
create
table
users
-> (
-> userID
int
not
null
primary
key
,
-> username
varchar
(100)
not
null
,
-> nicheng
varchar
(100)
not
null
unique
,
-> sex
char
(2)
check
(sex
in
(
'男'
,
'女'
)),
-> regdate
date
-> );
Query OK, 0
rows
affected (0.06 sec)
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 | |
NULL
| |
+
----------+--------------+------+-----+---------+-------+
5
rows
in
set
(0.00 sec)
mysql>
--6,
mysql>
create
table
orders
-> (
-> orderid
int
not
null
primary
key
,
-> total number(6,2)
check
(total>0),
-> userid
int
not
null
references
users(userid)
-> );
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
'numbe
r(6,2) check(total>0),
userid int not null references users(userid)
)'
at
line 4
mysql>
mysql>
create
table
orders
-> (
-> orderid
int
not
null
primary
key
,
-> total
numeric
(6,2)
check
(total>0),
-> userid
int
not
null
references
users(userid)
-> );
Query OK, 0
rows
affected (0.08 sec)
mysql>
desc
orders;
+
---------+--------------+------+-----+---------+-------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
---------+--------------+------+-----+---------+-------+
| orderid |
int
(11) |
NO
| PRI | | |
| total |
decimal
(6,2) | YES | |
NULL
| |
| userid |
int
(11) |
NO
| | | |
+
---------+--------------+------+-----+---------+-------+
3
rows
in
set
(0.00 sec)
mysql>
--7,
mysql> show tables;
+
--------------+
| Tables_in_yb |
+
--------------+
| orders |
| users |
+
--------------+
2
rows
in
set
(0.00 sec)
mysql>
--8,auto_increment
mysql>
create
table
autoUsers
-> (
-> userid
int
auto_increment
primary
key
,
-> username
varchar
(100)
-> );
Query OK, 0
rows
affected (0.09 sec)
mysql>
desc
autoUsers;
+
----------+--------------+------+-----+---------+----------------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
----------+--------------+------+-----+---------+----------------+
| userid |
int
(11) |
NO
| PRI |
NULL
| auto_increment |
| username |
varchar
(100) | YES | |
NULL
| |
+
----------+--------------+------+-----+---------+----------------+
2
rows
in
set
(0.00 sec)
mysql>
insert
into
autoUsers(username)
values
(
'pengYun'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
autoUsers(username)
values
(
'weiFei'
);
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
autoUsers;
+
--------+----------+
| userid | username |
+
--------+----------+
| 1 | pengYun |
| 2 | weiFei |
+
--------+----------+
2
rows
in
set
(0.00 sec)
mysql>
|
| MySQL：4_3,后加约束与修改列 |
--================================
--ylb:MySQL
--1,后加约束与修改列
--2，主键、外键、唯一、检查、非空、默认
--17:04 2012-1-6
--================================
--============================================
-- P1:删除约束
--============================================
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>
--1,
mysql>
drop
database
if exists yb;
Query OK, 1 row affected (0.00 sec)
mysql>
--2,
mysql>
create
database
yb;
Query OK, 1 row affected (0.00 sec)
mysql>
--3,
mysql> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| phpmyadmin |
| test |
| yb |
| ylb |
+
--------------------+
6
rows
in
set
(0.00 sec)
mysql>
--4,
mysql> use yb;
Database
changed
mysql>
--5,
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>
--5,
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>
--7,add pk
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>
--8,add unique
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>
--9,add check
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>
--10,modify not null|null
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>
--11,modify default
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>
--12,
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>
--13,
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>
--14,add fk
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>
--15,drop constraint
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>
--16,修改表结构
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>
--17,add column
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>
--18,drop column
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>
|
| MySQL：4_4,视图(View) |
--================================
--ylb:MySQL
--1,视图（View）创建于管理
--17:04 2012-1-6
--================================
Enter
password
: ****
Welcome
to
the MySQL monitor. Commands
end
with
;
or
\g.
Your MySQL
connection
id
is
3
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>
--1,
mysql>
drop
database
if exists yb;
Query OK, 3
rows
affected (0.06 sec)
mysql>
--2,
mysql>
create
database
yb;
Query OK, 1 row affected (0.00 sec)
mysql>
--3,
mysql> show databases;
+
--------------------+
|
Database
|
+
--------------------+
| information_schema |
| mysql |
| phpmyadmin |
| test |
| yb |
| ylb |
+
--------------------+
6
rows
in
set
(0.00 sec)
mysql>
--4,
mysql> use yb;
Database
changed
mysql>
--5,create table
mysql>
create
table
users
-> (
-> userID
int
auto_increment
primary
key
,
-> username
varchar
(100)
-> );
Query OK, 0
rows
affected (0.11 sec)
mysql>
insert
into
users(username)
values
(
'weiFei'
);
Query OK, 1 row affected (0.00 sec)
mysql>
insert
into
users(username)
values
(
'xiaoLiu'
);
Query OK, 1 row affected (0.00 sec)
mysql>
select
*
from
users;
+
--------+----------+
| userID | username |
+
--------+----------+
| 1 | weiFei |
| 2 | xiaoLiu |
+
--------+----------+
2
rows
in
set
(0.00 sec)
mysql>
--6,create view
mysql>
create
view
v_users
->
as
->
select
*
from
users;
Query OK, 0
rows
affected (0.05 sec)
mysql>
--7,
mysql> show tables;
+
--------------+
| Tables_in_yb |
+
--------------+
| users |
| v_users |
+
--------------+
2
rows
in
set
(0.01 sec)
mysql>
--8,desc view
mysql>
desc
v_users;
+
----------+--------------+------+-----+---------+-------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
----------+--------------+------+-----+---------+-------+
| userID |
int
(11) |
NO
| | 0 | |
| username |
varchar
(100) | YES | |
NULL
| |
+
----------+--------------+------+-----+---------+-------+
2
rows
in
set
(0.00 sec)
mysql>
desc
users;
+
----------+--------------+------+-----+---------+----------------+
| Field | Type |
Null
|
Key
|
Default
| Extra |
+
----------+--------------+------+-----+---------+----------------+
| userID |
int
(11) |
NO
| PRI |
NULL
| auto_increment |
| username |
varchar
(100) | YES | |
NULL
| |
+
----------+--------------+------+-----+---------+----------------+
2
rows
in
set
(0.00 sec)
mysql>
--9,select view
mysql>
select
*
from
v_users;
+
--------+----------+
| userID | username |
+
--------+----------+
| 1 | weiFei |
| 2 | xiaoLiu |
+
--------+----------+
2
rows
in
set
(0.00 sec)
mysql>
--10,drop view
mysql>
drop
view
v_users;
Query OK, 0
rows
affected (0.00 sec)
mysql>
select
*
from
v_users;
ERROR 1146 (42S02):
Table
'yb.v_users'
doesn't exist
mysql>
|
| MySQL: 4_5,修改表约束 |
--===========================================
--ylb:MySQL
--1，修改表约束
--9:18 2012-1-7
--===========================================
用
alter
table
语句：
完整用法：
ALTER
[
IGNORE
]
TABLE
tbl_name
alter_specification [, alter_specification] ...
alter_specification:
table_option ...
|
ADD
[
COLUMN
] column_definition [
FIRST
|
AFTER
col_name ]
|
ADD
[
COLUMN
] (column_definition,...)
|
ADD
{
INDEX
|
KEY
} [index_name] [index_type] (index_col_name,...)
|
ADD
[
CONSTRAINT
[symbol]]
PRIMARY
KEY
[index_type] (index_col_name,...)
|
ADD
[
CONSTRAINT
[symbol]]
UNIQUE
[
INDEX
|
KEY
] [index_name] [index_type] (index_col_name,...)
|
ADD
[FULLTEXT|SPATIAL] [
INDEX
|
KEY
] [index_name] (index_col_name,...)
|
ADD
[
CONSTRAINT
[symbol]]
FOREIGN
KEY
[index_name] (index_col_name,...)
[reference_definition]
|
ALTER
[
COLUMN
] col_name {
SET
DEFAULT
literal |
DROP
DEFAULT
}
| CHANGE [
COLUMN
] old_col_name column_definition
[
FIRST
|
AFTER
col_name]
|
MODIFY
[
COLUMN
] column_definition [
FIRST
|
AFTER
col_name]
|
DROP
[
COLUMN
] col_name
|
DROP
PRIMARY
KEY
|
DROP
{
INDEX
|
KEY
} index_name
|
DROP
FOREIGN
KEY
fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [
TO
] new_tbl_name
|
ORDER
BY
col_name [, col_name] ...
|
CONVERT
TO
CHARACTER
SET
charset_name [
COLLATE
collation_name]
| [
DEFAULT
]
CHARACTER
SET
charset_name [
COLLATE
collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
index_col_name:
col_name [(length)] [
ASC
|
DESC
]
index_type:
USING {BTREE | HASH}
删除 主键 和外键约束:
ALTER
TABLE
mytablename
DROP
PRIMARY
KEY
DROP
FOREIGN
KEY
fk_symbol;
还有就是怎么样查询 约束;
你可以通过查看该表的 创建语句来 查看 约束：
SHOW
CREATE
TABLE
mytablename;
|
本文转自ylbtech博客园博客，原文链接：http://www.cnblogs.com/ylbtech/archive/2012/08/19/2645152.html，如需转载请自行联系原作者
