mysql基本操作
创建一个库
mysql> create database mydata;
Query OK, 1 row affected (0.00 sec)
mysql> show databases like 'mydata';
+-------------------+
| Database (mydata) |
+-------------------+
| mydata |
+-------------------+
1 row in set (0.00 sec)
mysql> show create database mydata\G
*************************** 1. row ***************************
Database: mydata
Create Database: CREATE DATABASE `mydata` /*!40100 DEFAULT CHARACTER
SET utf8 */
1 row in set (0.00 sec)
建立表
create table <表名>（
<字段名1><类型1>,
...
<字段名n><类型n>
）;
mysql> use mydata;
Database changed
mysql> 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);
Query OK, 0 rows affected (0.01 sec)
mysql> select database();
+------------+
| database() |
+------------+
| mydata |
+------------+
1 row in set (0.00 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` ( 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，默认为空
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #引擎和字符集，引擎默认为InnoDB，字符集，继承库的
1 row in set (0.00 sec)
# 查看表结构 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.00 sec)
为表的字段创建索引
主键索引
查询速度最快，每个表只能有一个主键列，整个表的每一条记录的主键值在表内都是唯
一的，用来唯一标识一条记录。可以对单列创建索引，也可以对多列创建索引。
mysql> create table student(
-> 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) 普通索引
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| 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)
普通的索引
KEY index_name(name) 普通索引
mysql> show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
删除索引
mysql> alter table student drop index index_name; 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(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)
添加索引 方法1
mysql> alter table student add index index_age(age); 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(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
添加索引 方法2
mysql> create index index_dept on student(dept(4)); 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(4) | 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.00 sec) # 这边alter命令也可以进行设置，效果一样 mysql> alter table student add index index_dept(dept(4));
为表的多个字段创建联合索引
mysql> create index index_name_dept on student(name,dept); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
为表的多个字段前n个字符列，创建联合索引
mysql> create index index_name_dept_2 on student(name(8),dept(10)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
创建唯一索引
mysql> create unique index uni_name on student(name); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | UNI | NULL | | | age | tinyint(2) | NO | | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
统计表记录的唯一值的命令
mysql> select user,host from mysql.user; +------+-------------------------+ | user | host | +------+-------------------------+ | root | 127.0.0.1 | | root | ::1 | | | izbp1gay9nb7kpofaiaylqz | | root | izbp1gay9nb7kpofaiaylqz | | | localhost | | root | localhost | +------+-------------------------+ 6 rows in set (0.00 sec)
查看唯一值
mysql> select count(distinct user) from mysql.user; +----------------------+ | count(distinct user) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.00 sec) # 尽量在唯一值较多的列上，建立索引
联合索引的前缀特性
index（a,b,c）仅有a,ab,abc三个查询条件列可以走索引，b,bc,ac,c无法使用索引
尽量把最常用作为查询条件的列，放在第一的位置
主键也可以联合索引，例如mysql.user中的主键
PRIMARY KEY (`Host`,`User`)
浙公网安备 33010602011771号