MYSQL入门学习之八：数据库及表的基本操作_MySQL

MYSQL入门学习之八：数据库及表的基本操作

相关链接：

MYSQL入门学习之一：基本操作

http:///database/201212/173868.html

MYSQL入门学习之二：使用正则表达式搜索

http:///database/201212/173869.html

MYSQL入门学习之三：全文本搜索

http:///database/201212/173873.html

MYSQL入门学习之四：MYSQL的数据类型

http:///database/201212/175536.html

MYSQL入门学习之五：MYSQL的字符集

http:///database/201212/175541.html

MYSQL入门学习之六：MYSQL的运算符

http:///database/201212/175862.html

MYSQL入门学习之七：MYSQL常用函数

http:///database/201212/175864.html

一、操作数据库

1、查看数据库

show databases [ like ''];

示例：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| luomian |

| mydb |

| mysql |

| net80576314 |

| phpcms_uat |

| phpcmsv9 |

| phpcmsv9_new |

| rutiao |

| szwalkers |

| test |

| v9test1 |

+--------------------+

mysql> show databases like 'php%';

+-----------------+

| Database (php%) |

+-----------------+

| phpcms_uat |

| phpcmsv9 |

| phpcmsv9_new |

+-----------------+

2、创建数据库

create database [if not exists] dbname;

示例：

mysql> create database if not exists mydb;

3、选择需要的数据库

use dbname

示例：

mysql> use mydb;

Database changed

4、删除数据库

drop database [if exists] dbname;

示例：

mysql> drop database if exists mydb;

二、操作表

1、显示表

show tables;

示例：

mysql> show tables;

+-----------------------+

| Tables_in_test |

+-----------------------+

| newname |

| productnotes |

| test_char |

| test_inn |

| test_inn2 |

| test_priority |

| test_trans |

| test_view |

+-----------------------+

2、创建表

示例：

mysql> create table user(

-> id int(10) not null auto_increment primary key,

-> name varchar(50) default 'N/A' not null,

-> sex char(1) null

-> )engine=InnDB;

3、复制表

示例：

mysql> create table student select * from user;

mysql> create table teacher like user;

4、重命名表

mysql> rename table teacher to senior_teacher;

mysql> alter table student rename to senior_student;

5、删除表

mysql> drop table if exists senior_teacher;

6、查看创建表语句

mysql> show create table student;

+---------+-------------------------------------

| Table | Create Table

+---------+-------------------------------------

| student | CREATE TABLE `student` (

`id` int(10) NOT NULL DEFAULT '0',

`name` varchar(50) NOT NULL DEFAULT 'N/A',

`sex` char(1) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+---------+-------------------------------------

7、查看表结构

mysql> desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(10) | NO | | 0 | |

| name | varchar(50) | NO | | N/A | |

| sex | char(1) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

8、修改表结构

mysql> alter table student add bithday date null;

mysql> alter table student modify bithday datetime;

mysql> alter table student change bithday birt datetime;

mysql> alter table student drop column bithday;

9、操作表中的数据

mysql> select * from student;

+----+------+------+---------------------+

| id | name | sex | birt |

+----+------+------+---------------------+

| 0 | jack | 1 | 2012-12-13 00:00:00 |

+----+------+------+---------------------+

mysql> insert into senior_student select * from student;

mysql> insert into student(name,sex,birt) values('jack','1',current_date());

mysql> update student set sex = 0 where name = 'jack';

mysql> delete from student where name = 'jack';

10、创建及查看索引

mysql> create index idx_student_name on student(name);

mysql> show index from student;

+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| student | 1 | idx_student_name | 1 | name | A | NULL | NULL | NULL | | BTREE | |

+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+