1、创建数据库 www.2cto.com
mysql> create database user;
Query OK, 1 row affected (0.00 sec)
2、使用此数据库
mysql> use user;
Database changed
3、在此数据库上创建表
mysql> create table person(
-> id int unsigned not null auto_increment primary key,
-> name varchar(30)
-> );
Query OK, 0 rows affected (0.00 sec)
4、查看此person表的表结构 www.2cto.com
mysql> desc person;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
5、创建person_bak,并是此表的表结构与person一样，即复制person的表结构
mysql> create table person_bak like person;
Query OK, 0 rows affected (0.01 sec)
6、向person表中插入数据
mysql> insert into person (name) values ("user1");
Query OK, 1 row affected (0.00 sec)
7、将person表中的数据复制到person_bak表中
mysql> insert into person_bak select * from person;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
8、向person表中创建name列的索引
方法一：
mysql> create index in_name on person (name);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
方法二：
mysql> alter table person add index in_name (name);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
9、查看索引 www.2cto.com
mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | |
| person | 1 | in_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)
10、在person表中创建唯一索引
mysql> alter table person add unique index un_name (name);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
11、修改列的属性
mysql> alter table person modify name varchar(20);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
12、统计表中的数据数据
mysql> select count(*) from person;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
13、创建一个视图
mysql> create view v_person as select * from person;
Query OK, 0 rows affected (0.01 sec)
14、查看视图(和查看表的命令一样)
当删除表中的某条记录时，相应的此表对应的视图中的满足条件的记录也将会被删除掉
mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| person |
| person_bak |
| v_person |
+----------------+
3 rows in set (0.00 sec)
15、删除视图
mysql> drop view v_person;
Query OK, 0 ro