Mysql命令收集【重要】-白红宇

Mysql命令收集【重要】

阅读量：5153 次

发布时间：2019-06-13

本文共 24417 字，大约阅读时间需要 81 分钟。

1、在linux上获取Mysql服务器状态，及版本：

[root@host]

# mysqladmin --version

结果信息：

mysqladmin Ver 8.42 Distrib 5.7.21, for Linux on x86_64

检查MySQL服务器是否启动：ps

-ef |

grep

mysqld

结果信息：

mysql 14179 1 0 14:10 ? 00:00:06 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

root 15477 15378 0 20:19 pts/1 00:00:00 grep --color=auto mysqld

2、连接到mysql数据库服务器：

[root@host]

# mysql -u root -p

Enter password:*******

3、关闭和启动Mysql服务器：

service mysqld start/stop

以下列出了使用Mysql数据库过程中常用的命令：

USE 数据库名 :选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。

SHOW TABLES: #显示指定数据库的所有表，使用该命令前需要使用 use命令来选择要操作的数据库。

SHOW COLUMNS FROM 数据表: #显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息。

create database testdb charset "utf8"; #创建一个叫testdb的数据库，且让其支持中文

drop database testdb; #删除数据库

SHOW INDEX FROM 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

4、增加用户名：

root@host# mysql -u root -p

Enter

password

:*******

mysql> use mysql;

Database

changed

mysql>

INSERT

INTO

user

(host,

user

,

password

,

select_priv, insert_priv, update_priv)

VALUES

(

'localhost'

,

'guest'

,

PASSWORD

(

'guest123'

),

'Y'

,

'Y'

,

'Y'

);

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH

PRIVILEGES

;

Query OK, 1 row affected (0.01 sec)

mysql>

SELECT

host,

user

,

password

FROM

user

WHERE

user

=

'guest'

;

+

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

| host |

user

|

password

|

+

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

| localhost | guest | 6f8c114b58f2ce9e |

+

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

1 row

in

set

(0.00 sec)

第二种方法：

默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

有时候，只是为了自己测试，不想密码设置得那么复杂，譬如说，我只想设置root的密码为123456。

必须修改两个全局参数：

首先，修改validate_password_policy参数的值

mysql> set global validate_password_policy=0;

root@host# mysql -u root -p

password

;

Enter

password

:*******

mysql> use mysql;

Database

changed

mysql>

GRANT

SELECT

,

INSERT

,

UPDATE

,

DELETE

,

CREATE

,

DROP

->

ON

TUTORIALS.*

->

TO

'zte'

@

'localhost'

-> IDENTIFIED

BY

'zte'

;

5、增加表：

查看表属性：

show create table study_record; 可查看外键

desc study_record ；

+--------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| day    | int(11)     | NO   |     | NULL    |                |
| status | varchar(32) | NO   |     | NULL    |                |
| stu_id | int(11)     | YES | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

mysql> create table bbuinfo(

-> bbuid INT NOT NULL AUTO_INCREMENT,

-> bbuname CHAR(32) NOT NULL,

-> age INT NOT NULL,

-> PRIMARY KEY ( bbuid )

-> );

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

删除表：DROP TABLE IF EXISTS tbl_name；

6、MySQL 插入数据

mysql> insert into bbuinfo

-> (bbuname,age)

-> values ( '8200','1993');

Query OK, 1 row affected (0.00 sec)

7、查询数据

SELECT

column_name,column_name

FROM

table_name

[

WHERE

Clause]

[OFFSET M ][LIMIT N]

查询两个表：

desc bbuinfo; 描述一个表，

| Field   | Type     | Null | Key | Default | Extra          |+---------+----------+------+-----+---------+----------------+| bbuid   | int(11)  | NO   | PRI | NULL    | auto_increment || bbuname | char(32) | NO   |     | NULL    |                || age     | int(11)  | NO   |     | NULL    |                |+---------+----------+------+-----+---------+----------------+

select bbuname,rruname from bbuinfo,rruinfo;

mysql> select * from bbuinfo,rruinfo;+-------+---------+------+----+---------+------+------+------------+| bbuid | bbuname | age  | id | rruname | age  | len  | updatetime |+-------+---------+------+----+---------+------+------+------------+|     1 | 8200    | 1993 |  1 | 8852    | 1993 |  800 | 1234       ||     2 | 9200    | 1994 |  1 | 8852    | 1993 |  800 | 1234       ||     3 | 9300    | 1954 |  1 | 8852    | 1993 |  800 | 1234       ||     1 | 8200    | 1993 |  2 | 8862    | 1994 |  900 | 123564     ||     2 | 9200    | 1994 |  2 | 8862    | 1994 |  900 | 123564     ||     3 | 9300    | 1954 |  2 | 8862    | 1994 |  900 | 123564     ||     1 | 8200    | 1993 |  3 | 8872    | 1993 | 1800 | 123564     ||     2 | 9200    | 1994 |  3 | 8872    | 1993 | 1800 | 123564     ||     3 | 9300    | 1954 |  3 | 8872    | 1993 | 1800 | 123564     |+-------+---------+------+----+---------+------+------+------------+9 rows in set (0.00 sec)mysql>

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

SELECT 命令可以读取一条或者多条记录。

你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

你可以使用 WHERE 语句来包含任何条件。

你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

你可以使用 LIMIT 属性来设定返回的记录数。

加偏移量的查询：

mysql> select * from rruinfo;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  5 | 8892    | 1994 | 1800 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+7 rows in set (0.00 sec)mysql> select * from rruinfo limit 3 offset 4;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  5 | 8892    | 1994 | 1800 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+3 rows in set (0.00 sec)

mysql> select * from rruinfo  limit 2,3;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  5 | 8892    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+3 rows in set (0.00 sec)

8、使用where命令来查询

操作符	描述	实例
=	等号	(A = B) 返回false。
<>, !=	不等于	(A != B) 返回 true。
>	大于号	(A > B) 返回false。
<	小于号	(A < B) 返回 true。
>=	大于等于号	(A >= B) 返回false。
<=	小于等于号	(A <= B) 返回 true。

mysql> select rruname from rruinfo where rruname = 8872;+---------+| rruname |+---------+| 8872    |+---------+1 row in set (0.00 sec)mysql> select * from rruinfo where age = 8872;Empty set (0.02 sec)mysql> select * from rruinfo where rruname  = 8872;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  3 | 8872    | 1993 | 1800 | 123564     |+----+---------+------+------+------------+1 row in set (0.01 sec)mysql> select * from rruinfo where rruname  = 8872 or rruname = 8862;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     |+----+---------+------+------+------------+2 rows in set (0.00 sec)mysql> select age ,len  from rruinfo where rruname  = 8872 or rruname = 8862;+------+------+| age  | len  |+------+------+| 1994 |  900 || 1993 | 1800 |+------+------+2 rows in set (0.00 sec)

9、MySQL UPDATE 数据：

mysql> update rruinfo  set age=22 ,len="2100" where rruname=8892;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from rruinfo;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  5 | 8892    |   22 | 2100 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+7 rows in set (0.00 sec)

10、MySQL DELETE 数据

mysql> delete from rruinfo where len=2100;Query OK, 1 row affected (0.04 sec)mysql> select * from rruinfo;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+6 rows in set (0.00 sec)

11、MySQL LIKE 子句

mysql> select * from rruinfo  where  rruname like "%2";+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+4 rows in set (0.00 sec)mysql> select * from rruinfo    -> ;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+6 rows in set (0.00 sec)

12、MySQL 排序

使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。

mysql> select * from rruinfo where rruname like '%2' order by rruname desc;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  4 | 8882    | 1994 | 1800 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  2 | 8862    | 1994 |  900 | 123564     ||  1 | 8852    | 1993 |  800 | 1234       |+----+---------+------+------+------------+4 rows in set (0.00 sec)mysql> select * from rruinfo where rruname like '%2' order by rruname asc;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     |+----+---------+------+------+------------+4 rows in set (0.00 sec)

13、MySQL GROUP BY 分类语句

统计出现的次数

mysql> select rruname, count(*) from rruinfo group by rruname;+---------+----------+| rruname | count(*) |+---------+----------+| 8852    |        4 || 8854    |        1 || 8862    |        1 || 8863    |        1 || 8872    |        1 || 8882    |        1 || 8892    |        1 |+---------+----------+7 rows in set (0.00 sec)mysql> select * from rruinfo;+----+---------+------+------+------------+| id | rruname | age  | len  | updatetime |+----+---------+------+------+------------+|  1 | 8852    | 1993 |  800 | 1234       ||  2 | 8862    | 1994 |  900 | 123564     ||  3 | 8872    | 1993 | 1800 | 123564     ||  4 | 8882    | 1994 | 1800 | 123564     ||  6 | 8854    | 1994 | 1800 | 123564     ||  7 | 8863    | 1994 | 1800 | 123564     ||  8 | 8892    | 1993 | 1800 | 12345      ||  9 | 8852    | 1994 | 1800 | 12345      || 10 | 8852    | 1994 |  900 | 12345      || 11 | 8852    | 1994 |  900 | 12345      |+----+---------+------+------+------------+10 rows in set (0.00 sec)

使用 WITH ROLLUP

其中记录

NULL

表示总体累加值。

可以使用

coalesce

来设置一个可以取代

NUll

的名称，

coalesce

语法：

mysql> select rruname, sum(len) as lens from rruinfo group by rruname with rollup;+---------+-------+| rruname | lens  |+---------+-------+| 8852    |  4400 || 8854    |  1800 || 8862    |   900 || 8863    |  1800 || 8872    |  1800 || 8882    |  1800 || 8892    |  1800 || NULL    | 14300 |+---------+-------+8 rows in set (0.00 sec)mysql> select coalesce(rruname,'total'), sum(len) as lens from rruinfo group by rruname with rollup;+---------------------------+-------+| coalesce(rruname,'total') | lens  |+---------------------------+-------+| 8852                      |  4400 || 8854                      |  1800 || 8862                      |   900 || 8863                      |  1800 || 8872                      |  1800 || 8882                      |  1800 || 8892                      |  1800 || total                     | 14300 |+---------------------------+-------+8 rows in set (0.00 sec)

14、MySQL ALTER命令修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

增加表格字段，删除表格字段

mysql> select * from bbuinfo;+-------+---------+------+| bbuid | bbuname | age  |+-------+---------+------+|     1 | 8200    | 1993 ||     2 | 9200    | 1994 ||     3 | 9300    | 1954 |+-------+---------+------+3 rows in set (0.00 sec)mysql> alter table bbuinfo add len int not null;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from bbuinfo;+-------+---------+------+-----+| bbuid | bbuname | age  | len |+-------+---------+------+-----+|     1 | 8200    | 1993 |   0 ||     2 | 9200    | 1994 |   0 ||     3 | 9300    | 1954 |   0 |+-------+---------+------+-----+3 rows in set (0.00 sec)mysql> alter table bbuinfo drop len ;Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from bbuinfo;+-------+---------+------+| bbuid | bbuname | age  |+-------+---------+------+|     1 | 8200    | 1993 ||     2 | 9200    | 1994 ||     3 | 9300    | 1954 |+-------+---------+------+3 rows in set (0.00 sec)

修改字段类型及名称

mysql> desc bbuinfo;+---------+----------+------+-----+---------+----------------+| Field   | Type     | Null | Key | Default | Extra          |+---------+----------+------+-----+---------+----------------+| bbuid   | int(11)  | NO   | PRI | NULL    | auto_increment || bbuname | char(32) | NO   |     | NULL    |                || age     | int(11)  | NO   |     | NULL    |                |+---------+----------+------+-----+---------+----------------+3 rows in set (0.00 sec)mysql> alter table bbuinfo  modify bbuname int;Query OK, 3 rows affected (0.02 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> desc bbuinfo;+---------+---------+------+-----+---------+----------------+| Field   | Type    | Null | Key | Default | Extra          |+---------+---------+------+-----+---------+----------------+| bbuid   | int(11) | NO   | PRI | NULL    | auto_increment || bbuname | int(11) | YES  |     | NULL    |                || age     | int(11) | NO   |     | NULL    |                |+---------+---------+------+-----+---------+----------------+

mysql> alter table bbuinfo  modify bbuname int   NOT NULL;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc bbuinfo    -> ;+---------+---------+------+-----+---------+----------------+| Field   | Type    | Null | Key | Default | Extra          |+---------+---------+------+-----+---------+----------------+| bbuid   | int(11) | NO   | PRI | NULL    | auto_increment || bbuname | int(11) | NO   |     | NULL    |                || age     | int(11) | NO   |     | NULL    |                |+---------+---------+------+-----+---------+----------------+3 rows in set (0.00 sec)

mysql> alter table bbuinfo  modify bbuname int  NULL;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc bbuinfo     -> ;+---------+---------+------+-----+---------+----------------+| Field   | Type    | Null | Key | Default | Extra          |+---------+---------+------+-----+---------+----------------+| bbuid   | int(11) | NO   | PRI | NULL    | auto_increment || bbuname | int(11) | YES  |     | NULL    |                || age     | int(11) | NO   |     | NULL    |                |+---------+---------+------+-----+---------+----------------+

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。

mysql> alter table bbuinfo  change  bbuname  bbuname1 bigint;Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from bbuinfo;+-------+----------+------+| bbuid | bbuname1 | age  |+-------+----------+------+|     1 |     8200 | 1993 ||     2 |     9200 | 1994 ||     3 |     9300 | 1954 |+-------+----------+------+3 rows in set (0.00 sec)mysql> desc bbuinfo ;+----------+------------+------+-----+---------+----------------+| Field    | Type       | Null | Key | Default | Extra          |+----------+------------+------+-----+---------+----------------+| bbuid    | int(11)    | NO   | PRI | NULL    | auto_increment || bbuname1 | bigint(20) | YES  |     | NULL    |                || age      | int(11)    | NO   |     | NULL    |                |+----------+------------+------+-----+---------+----------------+3 rows in set (0.00 sec)

ALTER TABLE 对 Null 值和默认值的影响：

mysql> alter table bbuinfo  modify len1  int  not NULL default 200;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> mysql> mysql> insert into bbuinfo (bbuname1,age,len) value(8300,1994,100);Query OK, 1 row affected (0.00 sec)mysql> select * from bbuinfo;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 |+-------+----------+------+-----+------+4 rows in set (0.00 sec)

修改表名：

mysql> alter table bbuinfo rename to bbuinfo1;Query OK, 0 rows affected (0.00 sec)mysql> show tables    -> ;+--------------+| Tables_in_my |+--------------+| bbuinfo1     || rruinfo      || student      || user         |+--------------+4 rows in set (0.00 sec)

15、关于外键

一个特殊的索引，用于关联两个表

mysql> create table class(    -> id  int not null primary key,    -> name char(16));Query OK, 0 rows affected (0.02 sec)  CREATE TABLE `student2` (  `id` int(11) NOT NULL,  `name` char(16) NOT NULL,  `class_id` int(11) NOT NULL,  PRIMARY KEY (`id`),  KEY `fk_class_key` (`class_id`),  CONSTRAINT `fk_class_key` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))

此时如果class 表中不存在id 1,student表也插入不了，这就叫外键约束

mysql> insert into student2(id,name,class_id) values(1,'zq', 1);Query OK, 1 row affected (0.01 sec)mysql> select * from student2 ;+----+------+----------+| id | name | class_id |+----+------+----------+|  1 | zq |        1 |+----+------+----------+1 row in set (0.00 sec)mysql> select * from class;+----+-------+| id | name  |+----+-------+|  1 | linux |+----+-------+1 row in set (0.00 sec)

如果有student表中跟这个class表有关联的数据，你是不能删除class表中与其关联的纪录的

16、MySQL NULL 值处理　　

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，

该命令可能就无法正常工作。为了处理这种情况，MySQL提供了三大运算符: IS NULL: 当列的值是NULL,此运算符返回true。

IS NOT NULL: 当列的值不为NULL, 运算符返回true。 <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。在MySQL中，

NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。 MySQL中处理NULL使用IS NULL和IS NOT NULL运算符

17、Mysql 连接（left join, right join, inner join ,full join）求集合

Inner join 求交集

select

*

from

a

INNER

JOIN

b

on

a.a = b.b;

select

a.*,b.*

from

a,b

where

a.a = b.b;

Left join

select * from a LEFT JOIN b on a.a = b.b;

Right join

select * from a RIGHT JOIN b on a.a = b.b;

Full join

select * from a FULL JOIN b on a.a = b.b;

18、事务演示：

rollback会在中途回滚，commit提交之后就不可回滚。没commit之前是存在内存里面的。

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 |+-------+----------+------+-----+------+4 rows in set (0.01 sec)mysql> insert into bbuinfo1 (bbuname1,age,len,len1) values(8100,1995,100,45);Query OK, 1 row affected (0.01 sec)mysql> insert into bbuinfo1 (bbuname1,age,len,len1) values(8500,1995,100,45);Query OK, 1 row affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 ||     5 |     8100 | 1995 | 100 |   45 ||     6 |     8500 | 1995 | 100 |   45 |+-------+----------+------+-----+------+6 rows in set (0.00 sec)mysql> rollback    -> ;Query OK, 0 rows affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 |+-------+----------+------+-----+------+4 rows in set (0.00 sec)

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 |+-------+----------+------+-----+------+4 rows in set (0.00 sec)mysql> insert into bbuinfo1 (bbuname1,age,len,len1) values(8500,1995,100,45);Query OK, 1 row affected (0.00 sec)mysql> insert into bbuinfo1 (bbuname1,age,len,len1) values(8500,1995,100,45);Query OK, 1 row affected (0.00 sec)mysql> insert into bbuinfo1 (bbuname1,age,len,len1) values(8500,1995,100,45);Query OK, 1 row affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 ||     7 |     8500 | 1995 | 100 |   45 ||     8 |     8500 | 1995 | 100 |   45 ||     9 |     8500 | 1995 | 100 |   45 |+-------+----------+------+-----+------+7 rows in set (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from bbuinfo1;+-------+----------+------+-----+------+| bbuid | bbuname1 | age  | len | len1 |+-------+----------+------+-----+------+|     1 |     8200 | 1993 | 100 |   22 ||     2 |     9200 | 1994 | 100 |   22 ||     3 |     9300 | 1954 | 100 |   22 ||     4 |     8300 | 1994 | 100 |  200 ||     7 |     8500 | 1995 | 100 |   45 ||     8 |     8500 | 1995 | 100 |   45 ||     9 |     8500 | 1995 | 100 |   45 |+-------+----------+------+-----+------+7 rows in set (0.00 sec)

19、索引

主键就是一个索引

查看索引：

mysql> show index from rruinfo;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rruinfo |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

添加索引：

mysql> create index index_bane on rruinfo(rruname(32));Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from rruinfo;+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rruinfo |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               || rruinfo |          1 | index_bane |            1 | rruname     | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)mysql>

删除索引：

mysql> drop index index_bane on rruinfo;