MySQL表操作

1.表的操作

mysql> show create database scott\G;
*************************** 1. row ***************************
       Database: scott
Create Database: CREATE DATABASE `scott` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

建表
create table <表名> (
    <字段名1><类型1>,
    <字段名n><类型n>
    );

    mysql> create table student(
    -> id int(4) not null,      学号列， 数字类型 长度为4 不为空值
    -> name char(20) not null,  名字列   定长字符类型， 长度20， 不为空值
    -> age tinyint(2) not null default '0', 年龄列，很小的数字类型， 长度为2 不为空值 默认为0
    -> dept varchar(16) default null  系别列，变长字符类型， 长度16
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


1. int（m）型 正常大小整数类型
2. char（m）型 定长字符串类型，当存储时，总是用空格填满右边到指定的长度
3. varchar 型 变长字符串类型

查看表达字段
mysql> desc student;
  字段    类型          是否允许为空  缺省是什么
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)有没有主键或索引

2. 为表的字段创建索引

  索引就像书一样，如果在字段上建立了索引，那么以索引为列查询条件时，可以
加快查询速度，这是mysql优化的重要内容之一，
  查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通
索引列，主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一

CREATE TABLE stud (
  id int(4) NOT NULL AUTO_INCREMENT,
  name char(20) NOT NULL,
  age tinyint(2) NOT NULL DEFAULT '0',
  dept varchar(16) DEFAULT NULL,
  primary key(id),
  KEY index_name(name)
);
AUTO_INCREMENT  自动生成id功能
primary key(id)  主键
KEY index_name(name)  name 字段普通索引

　　

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc stud;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


添加主键

mysql> alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


删除索引

mysql> alter table stud drop index index_name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


添加索引

mysql> alter table stud add index index_name(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stud;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


创建索引

mysql> create index index_dept on student(dept(2));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

创建联合索引
mysql> create index index_dept_name  on student(name,dept);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

删除索引
mysql> drop index index_dept_name on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> drop index index_dept on student;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

name前8个字符，dept前10个字符做索引
mysql> create index index_dept_name  on student(name(8),dept(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

创建唯一索引（非主键索引） 为了约束表的
mysql> create unique index index_age on student(age);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   | UNI | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

　　

索引的创建及生效条件
  索引占用系统空间，所以不是索引越多越好 更新频繁，读取比较少的表要少建立索引
  在那些列上查询索引
  select user,host from mysql.user where host=
索引一定要创建在where后的条件上，而不是select后选择数据的列
另外尽量选择在唯一值多的大表上建立索引

索引小结
创建主键索引
mysql> alter table student change id id int primary key auto_increment;  # 创建主键
mysql> create index index_dept on student(dept(8));  创建索引
mysql> alter table student add index index_name(name);  创建索引

删除主键索引
mysql> alter table student drop primary key;

创建普通索引
mysql> alter table student add index_dept(dept(8))

根据列的前n个字符创建索引
mysql> create index index_name  on student(name(8));
根据多个列创建联合索引
mysql> create index index_dept_name  on student(name(8),dept(10));

删除普通索引
mysql> alter table student drop index index_dept;

mysql> drop index index_dept on student;

创建唯一索引
mysql> create unique index uni_ind_name on student(name)