mysql博客园day_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 (

,

...

);

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 nullAUTO_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`)