Mysql命令收集【重要】

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: 0

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    |   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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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: 0

mysql>

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: 0

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         |           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;

巴特西

Mysql命令收集【重要】

16、MySQL NULL 值处理

最新文章

热门文章