正文
记录MySQL的一些基础操作
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
MySQL建表操作root@localhost 08:05:22> create table stu(
-> 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)root@localhost 08:07:20>
root@localhost 08:07:21> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)root@localhost 08:07:25> desc stu;
+-------+-------------+------+-----+---------+-------+
| 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)root@localhost 08:07:28>
root@localhost 08:07:28> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 08:08:47>
root@localhost 08:05:22> create table stu(
-> 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)root@localhost 08:07:20>
root@localhost 08:07:21> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)root@localhost 08:07:25> desc stu;
+-------+-------------+------+-----+---------+-------+
| 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)root@localhost 08:07:28>
root@localhost 08:07:28> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 08:08:47>
mysql5.1及以前的默认引擎是MyISAM，MySQL5.5及以后的默认引擎的InnoDB
int 整数类型
char 定长字符串类型，存储时总是用空格填满右边到指定的长度
varchar 变长字符串类型
其他详细信息可以参考 MySQL手册
为表的字段添加索引索引类似于书的目录，如果在字段上建立索引，那么以索引列为查询条件就可以加快查询数据的速度
root@localhost 08:38:25> 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.00 sec)root@localhost 08:38:31>
root@localhost 08:40:02> 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)root@localhost 08:40:16> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 08:40:18>
alter table 修改表结构1.alter 增加主键索引
root@localhost 08:50:04> create table stu(
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.01 sec)root@localhost 08:50:05> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
| student |
+----------------+
2 rows in set (0.00 sec)root@localhost 08:50:12> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:50:15> alter table stu change id id int primary key auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:51:16> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 08:51:19>
2.alter删除主键索引（然后再添加主键索引）
root@localhost 08:53:30> create table stu1(
-> id int(4) not null,
-> 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)root@localhost 08:53:54> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:54:20> alter table stu1 drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:54:27> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)root@localhost 08:54:30>root@localhost 08:57:08> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:57:10> alter table stu1 change id id int primary key auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:57:32> desc stu1;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 08:57:34>
3.alter删除普通索引以及添加普通索引
root@localhost 09:06:31> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 09:07:07> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:07:15> alter table stu drop index index_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:07:29> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:07:31>
root@localhost 09:07:59> alter table stu add index index_dept(dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:08:21> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:08:23>
4.alter 修改字段
root@localhost 09:08:23> alter table stu change dept dept varchar(200) not null default "TP";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:10:25> desc stu;
+-------+--------------+------+-----+---------+----------------+
| 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(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:10:28>
root@localhost 09:10:28> alter table stu add index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:11:38> desc stu;
+-------+--------------+------+-----+---------+----------------+
| 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(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:11:39> alter table stu change name name char(50) not null default "Simon";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:12:23> desc stu;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:12:24>
root@localhost 10:13:26> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:13:30>
root@localhost 10:20:21> alter table stu change dept department varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '谷歌部门信息';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:20:24>
root@localhost 10:20:33> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:20:42>
5.alter 添加列
root@localhost 09:19:41> alter table stu add column description longtext;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:21:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)root@localhost 09:22:00> alter table stu add column date datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:22:54> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)root@localhost 09:24:54> alter table stu change date date datetime not null comment '插入时间';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:24:58>
root@localhost 09:24:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)root@localhost 09:27:22>
6.查看表字段的注释
root@localhost 09:35:21> select column_name,column_comment from information_schema.columns where table_name='stu' and table_schema='test';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id | |
| name | |
| age | |
| dept | |
| description | |
| date | 插入时间 |
+-------------+----------------+
6 rows in set (0.00 sec)root@localhost 09:35:26> show full columns from stu\G 也可以
7.添加注释
- 给表添加注释
ALTER TABLE table_name COMMENT='这是表的注释';
- 给字段添加注释
alter table stu change column name name char(50) not null default 'Simon Paul' comment '学生姓名字段注释';
8.对字段前n个字符创建索引
root@localhost 09:50:53> create index index_desc on stu(description(66));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:52:17> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 09:52:24> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_name` (`name`),
KEY `index_desc` (`description`(66))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:52:28>
9.更该索引
MySQL并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作
show index from stu\G
root@localhost 09:52:24> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_name` (`name`),
KEY `index_desc` (`description`(66))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:52:28> alter table stu drop index index_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:56:30> create index index_name on stu(name(12));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:56:56> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_desc` (`description`(66)),
KEY `index_name` (`name`(12))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:57:04>
10.为表的多个字段创建联合索引
create index index_name_dept on stu(name(8),dept);
11.删除索引
drop index index_name on stu;
12.创唯一索引（非主键）
create unique index uniq_index_name on stu(name);
Finnalyroot@localhost 10:25:54> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:25:58> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`department` varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '谷歌部门信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_index_name` (`name`),
KEY `index_dept` (`department`),
KEY `index_desc` (`description`(66)),
KEY `index_name_dept` (`name`(8),`department`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 10:26:09>
alter添加字段到指定的位置
root@localhost 10:28:00> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:28:08> alter table stu add column sex tinyint(1) not null after description;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:29:19> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:29:21>
root@localhost 10:29:57> alter table stu change column sex sex tinyint(1) not null default 1 after description;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:30:47> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | 1 | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:30:53>
root@localhost 10:30:53> alter table stu drop column sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:33:39> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:33:45>
root@localhost 10:33:45> alter table stu add column sex tinyint(1) not null default 1 before date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before date' at line 1
root@localhost 10:34:24> alter table stu add column sex tinyint(1) not null default 1 first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:36:49> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| sex | tinyint(1) | NO | | 1 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:36:51>
索引类似于书的目录，如果在字段上建立索引，那么以索引列为查询条件就可以加快查询数据的速度
root@localhost 08:38:25> 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.00 sec)root@localhost 08:38:31>
root@localhost 08:40:02> 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)root@localhost 08:40:16> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 08:40:18>
1.alter 增加主键索引
root@localhost 08:50:04> create table stu(
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.01 sec)root@localhost 08:50:05> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stu |
| student |
+----------------+
2 rows in set (0.00 sec)root@localhost 08:50:12> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:50:15> alter table stu change id id int primary key auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:51:16> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 08:51:19>
2.alter删除主键索引（然后再添加主键索引）
root@localhost 08:53:30> create table stu1(
-> id int(4) not null,
-> 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)root@localhost 08:53:54> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:54:20> alter table stu1 drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:54:27> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)root@localhost 08:54:30>root@localhost 08:57:08> desc stu1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)root@localhost 08:57:10> alter table stu1 change id id int primary key auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 08:57:32> desc stu1;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 08:57:34>
3.alter删除普通索引以及添加普通索引
root@localhost 09:06:31> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) 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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)root@localhost 09:07:07> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:07:15> alter table stu drop index index_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:07:29> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:07:31>
root@localhost 09:07:59> alter table stu add index index_dept(dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:08:21> desc stu;
+-------+-------------+------+-----+---------+----------------+
| 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)root@localhost 09:08:23>
4.alter 修改字段
root@localhost 09:08:23> alter table stu change dept dept varchar(200) not null default "TP";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:10:25> desc stu;
+-------+--------------+------+-----+---------+----------------+
| 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(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:10:28>
root@localhost 09:10:28> alter table stu add index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:11:38> desc stu;
+-------+--------------+------+-----+---------+----------------+
| 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(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:11:39> alter table stu change name name char(50) not null default "Simon";
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:12:23> desc stu;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)root@localhost 09:12:24>
root@localhost 10:13:26> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:13:30>
root@localhost 10:20:21> alter table stu change dept department varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '谷歌部门信息';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:20:24>
root@localhost 10:20:33> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:20:42>
5.alter 添加列
root@localhost 09:19:41> alter table stu add column description longtext;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:21:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)root@localhost 09:22:00> alter table stu add column date datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:22:54> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)root@localhost 09:24:54> alter table stu change date date datetime not null comment '插入时间';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:24:58>
root@localhost 09:24:58> desc stu;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | YES | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)root@localhost 09:27:22>
6.查看表字段的注释
root@localhost 09:35:21> select column_name,column_comment from information_schema.columns where table_name='stu' and table_schema='test';
+-------------+----------------+
| column_name | column_comment |
+-------------+----------------+
| id | |
| name | |
| age | |
| dept | |
| description | |
| date | 插入时间 |
+-------------+----------------+
6 rows in set (0.00 sec)root@localhost 09:35:26> show full columns from stu\G 也可以
7.添加注释
- 给表添加注释
ALTER TABLE table_name COMMENT='这是表的注释';
- 给字段添加注释
alter table stu change column name name char(50) not null default 'Simon Paul' comment '学生姓名字段注释';
8.对字段前n个字符创建索引
root@localhost 09:50:53> create index index_desc on stu(description(66));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:52:17> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | MUL | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| dept | varchar(200) | NO | MUL | TP | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 09:52:24> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_name` (`name`),
KEY `index_desc` (`description`(66))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:52:28>
9.更该索引
MySQL并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作
show index from stu\G
root@localhost 09:52:24> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_name` (`name`),
KEY `index_desc` (`description`(66))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:52:28> alter table stu drop index index_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:56:30> create index index_name on stu(name(12));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 09:56:56> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`dept` varchar(200) NOT NULL DEFAULT 'TP' COMMENT '单位信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
KEY `index_dept` (`dept`),
KEY `index_desc` (`description`(66)),
KEY `index_name` (`name`(12))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 09:57:04>
10.为表的多个字段创建联合索引
create index index_name_dept on stu(name(8),dept);
11.删除索引
drop index index_name on stu;
12.创唯一索引（非主键）
create unique index uniq_index_name on stu(name);
Finnalyroot@localhost 10:25:54> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:25:58> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`department` varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '谷歌部门信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_index_name` (`name`),
KEY `index_dept` (`department`),
KEY `index_desc` (`description`(66)),
KEY `index_name_dept` (`name`(8),`department`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 10:26:09>
alter添加字段到指定的位置
root@localhost 10:28:00> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:28:08> alter table stu add column sex tinyint(1) not null after description;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:29:19> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:29:21>
root@localhost 10:29:57> alter table stu change column sex sex tinyint(1) not null default 1 after description;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:30:47> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | 1 | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:30:53>
root@localhost 10:30:53> alter table stu drop column sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:33:39> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:33:45>
root@localhost 10:33:45> alter table stu add column sex tinyint(1) not null default 1 before date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before date' at line 1
root@localhost 10:34:24> alter table stu add column sex tinyint(1) not null default 1 first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:36:49> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| sex | tinyint(1) | NO | | 1 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:36:51>
root@localhost 10:25:54> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:25:58> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT '学生姓名字段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`department` varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '谷歌部门信息',
`description` longtext NOT NULL COMMENT '这个是学生描述信息字段的注释',
`date` datetime NOT NULL COMMENT '插入时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_index_name` (`name`),
KEY `index_dept` (`department`),
KEY `index_desc` (`description`(66)),
KEY `index_name_dept` (`name`(8),`department`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释'
1 row in set (0.00 sec)root@localhost 10:26:09>
root@localhost 10:28:00> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:28:08> alter table stu add column sex tinyint(1) not null after description;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:29:19> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:29:21>
root@localhost 10:29:57> alter table stu change column sex sex tinyint(1) not null default 1 after description;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:30:47> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| sex | tinyint(1) | NO | | 1 | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:30:53>
root@localhost 10:30:53> alter table stu drop column sex;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:33:39> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)root@localhost 10:33:45>
root@localhost 10:33:45> alter table stu add column sex tinyint(1) not null default 1 before date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before date' at line 1
root@localhost 10:34:24> alter table stu add column sex tinyint(1) not null default 1 first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0root@localhost 10:36:49> desc stu;
+-------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+------------+----------------+
| sex | tinyint(1) | NO | | 1 | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | Simon Paul | |
| age | tinyint(2) | NO | | 23 | |
| department | varchar(200) | NO | MUL | TP_cloud | |
| description | longtext | NO | MUL | NULL | |
| date | datetime | NO | | NULL | |
+-------------+--------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)root@localhost 10:36:51>
# 多查看帮助文档
help alter table
INSERT 和 UPDATE
root@localhost 10:44:46> insert into stu(name,age,department,description,date) values('Tim',39,'Tencent','Connecting people for a greater future.',now());
Query OK, 1 row affected (0.00 sec)root@localhost 10:46:37>
root@localhost 10:46:40> update stu set description='Just stick to what you love and belive in.' where name='Mark';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0root@localhost 11:02:41> select * from stu;
+----+------------------+-----+-------------+--------------------------------------------------+---------------------+
| id | name | age | department | description | date |
+----+------------------+-----+-------------+--------------------------------------------------+---------------------+
| 1 | standby | 23 | live_cloud | Use ATS to archive the multiple level's cache. | 0000-00-00 00:00:00 |
| 2 | Jefrriy | 25 | live_cloud | Use Squid to archive the multiple level's cache. | 0000-00-00 00:00:00 |
| 3 | Mark | 47 | Mindjet | Just stick to what you love and belive in. | 2017-03-12 10:43:56 |
| 4 | Frankyang | 35 | Google | DNS administrator | 2017-03-12 10:44:46 |
| 5 | Tim | 39 | Tencent | Connecting people for a greater future. | 2017-03-12 10:46:37 |
| 6 | Michael Widenius | 43 | MariaDB inc | The Fastest Growing Open Source Database. | 2017-03-12 10:56:31 |
+----+------------------+-----+-------------+--------------------------------------------------+---------------------+
6 rows in set (0.00 sec)root@localhost 11:02:47>
MYSQLDUMP
[root@standby ~]# mysqldump -uroot -p123456 -B test >/tmp/test_bak_20170312.sql
[root@standby ~]# ll /tmp/
total 8
-rw-r--r-- 1 root root 3236 Mar 12 11:01 test_bak_20170312.sql
drwx------ 2 root root 4096 Sep 17 22:51 tmux-0
[root@standby ~]#
[root@standby ~]# cat /tmp/test_bak_20170312.sql
-- MySQL dump 10.13 Distrib 5.1.72, for unknown-linux-gnu (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version5.1.72/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Current Database: `test`
--CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;USE `test`;--
-- Table structure for table `stu`
--DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL DEFAULT 'Simon Paul' COMMENT 'å¦ç”Ÿå§“åå—段注释',
`age` tinyint(2) NOT NULL DEFAULT '23' COMMENT '年龄',
`department` varchar(200) NOT NULL DEFAULT 'TP_cloud' COMMENT '爱奇艺部门信æ¯',
`description` longtext NOT NULL COMMENT '这个是å¦ç”Ÿæè¿°ä¿¡æ¯å—段的注释',
`date` datetime NOT NULL COMMENT 'æ’入时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_index_name` (`name`),
KEY `index_dept` (`department`),
KEY `index_desc` (`description`(66)),
KEY `index_name_dept` (`name`(8),`department`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COMMENT='这是stu表的注释';
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `stu`
--LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,'standby',23,'live_cloud','Use ATS to archive the multiple level\'s cache.','0000-00-00 00:00:00'),(2,'Jefrriy',25,'live_cloud','Use Squid to archive the multiple level\'s cache.','0000-00-00 00:00:00'),(3,'Mark',47,'Mindjet','Just stick to what you love and belive in.','2017-03-12 10:43:56'),(4,'Frankyang',35,'Google','DNS administrator','2017-03-12 10:44:46'),(5,'Tim',39,'Tencent','Connecting people for a greater future.','2017-03-12 10:46:37'),(6,'Michael Widenius',43,'MariaDB inc','The Fastest Growing Open Source Database.','2017-03-12 10:56:31');
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2017-03-12 11:01:59
[root@standby ~]#
降序/升序/limit
mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)mysql> select user,host,password from mysql.user order by user asc;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)mysql> select user,host,password from mysql.user order by user desc;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
2 rows in set (0.00 sec)mysql> select user,host,password from mysql.user order by user desc limit 1;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)mysql>
like 模糊匹配
root@localhost 10:30:14> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| No_wiki |
| iQiYi |
| mysql |
| wiki |
| wiki001 |
| wiki002 |
| wiki003 |
+--------------------+
8 rows in set (0.00 sec)root@localhost 10:30:20> show databases like 'wiki%';
+------------------+
| Database (wiki%) |
+------------------+
| wiki |
| wiki001 |
| wiki002 |
| wiki003 |
+------------------+
4 rows in set (0.00 sec)root@localhost 10:30:25>
常用函数
root@localhost 10:34:46> select database();
+------------+
| database() |
+------------+
| wiki |
+------------+
1 row in set (0.00 sec)root@localhost 10:34:52> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)root@localhost 10:34:56> select now();
+---------------------+
| now() |
+---------------------+
| 2017-03-11 22:35:00 |
+---------------------+
1 row in set (0.01 sec)root@localhost 10:35:00> select version();
+-----------+
| version() |
+-----------+
| 5.1.72 |
+-----------+
1 row in set (0.00 sec)root@localhost 10:35:05>
创建新用户并授权
只使用 grant 创建用户并授权
root@localhost 10:43:48> grant all privileges on iQiYi.* to 'standby'@'localhost' identified by 'liulixin';
Query OK, 0 rows affected (0.01 sec)root@localhost 10:44:26> select user,host,password from user;
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| standby | localhost | *68400CF212112D85B99EB397ADCB27748218BFAE |
+---------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)root@localhost 10:44:31> root@localhost 10:44:58> select user,host,password from user where user like '%and%';
+---------+-----------+-------------------------------------------+
| user | host | password |
+---------+-----------+-------------------------------------------+
| standby | localhost | *68400CF212112D85B99EB397ADCB27748218BFAE |
+---------+-----------+-------------------------------------------+
1 row in set (0.00 sec)root@localhost 10:45:12>grant all privileges on iQiYi.* to 'standby'@'localhost' identified by 'liulixin';
使用 create 和 grant 创建用户并授权
root@localhost 10:56:11> CREATE USER 'standby001'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)root@localhost 10:56:38> GRANT ALL ON No_wiki.* TO 'standby001'@'localhost';
Query OK, 0 rows affected (0.00 sec)root@localhost 10:57:22>
root@localhost 10:57:26> select user,host,password from mysql.user;
+------------+-----------+-------------------------------------------+
| user | host | password |
+------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| standby | localhost | *68400CF212112D85B99EB397ADCB27748218BFAE |
| standby001 | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)root@localhost 10:57:41> show grants for 'standby001'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for standby001@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'standby001'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `No_wiki`.* TO 'standby001'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root@localhost 10:58:03> show grants for 'standby001'@'localhost'\G
*************************** 1. row ***************************
Grants for standby001@localhost: GRANT USAGE ON *.* TO 'standby001'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'
*************************** 2. row ***************************
Grants for standby001@localhost: GRANT ALL PRIVILEGES ON `No_wiki`.* TO 'standby001'@'localhost'
2 rows in set (0.00 sec)root@localhost 10:58:06>
查看某个用户的授权情况
root@localhost 10:48:54> show grants for 'standby'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for standby@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'standby'@'localhost' IDENTIFIED BY PASSWORD '*68400CF212112D85B99EB397ADCB27748218BFAE' |
| GRANT ALL PRIVILEGES ON `iQiYi`.* TO 'standby'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root@localhost 10:49:10> show grants for 'standby'@'localhost'\G
*************************** 1. row ***************************
Grants for standby@localhost: GRANT USAGE ON *.* TO 'standby'@'localhost' IDENTIFIED BY PASSWORD '*68400CF212112D85B99EB397ADCB27748218BFAE'
*************************** 2. row ***************************
Grants for standby@localhost: GRANT ALL PRIVILEGES ON `iQiYi`.* TO 'standby'@'localhost'
2 rows in set (0.00 sec)root@localhost 10:49:14>
授权局域网内主机远程连接数据库
root@localhost 11:07:07> grant all privileges on wiki.* to 'remote_test'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)root@localhost 11:08:40> flush privileges;
Query OK, 0 rows affected (0.00 sec)root@localhost 11:08:46> select user,host,password from mysql.user;
+-------------+-----------+-------------------------------------------+
| user | host | password |
+-------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| standby | localhost | *68400CF212112D85B99EB397ADCB27748218BFAE |
| standby001 | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| remote_test | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)root@localhost 11:08:48> root@localhost 11:08:48> grant all privileges on wiki.* to 'remote_test001'@'10.0.0.0/24' identified by '123456';
Query OK, 0 rows affected (0.00 sec)root@localhost 11:10:39> grant all privileges on wiki.* to 'remote_test002'@'10.0.0.0/255.255.255.0' identified by '123456';
Query OK, 0 rows affected (0.00 sec)root@localhost 11:11:19> flush privileges;
Query OK, 0 rows affected (0.01 sec)root@localhost 11:11:20> select user,host,password from mysql.user;
+----------------+------------------------+-------------------------------------------+
| user | host | password |
+----------------+------------------------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wiki | localhost | *A5DB2D927D6DF94DA5E1CE4B293AEAAB4D8304EA |
| standby | localhost | *68400CF212112D85B99EB397ADCB27748218BFAE |
| standby001 | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| remote_test | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| remote_test001 | 10.0.0.0/24 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| remote_test002 | 10.0.0.0/255.255.255.0 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+----------------+------------------------+-------------------------------------------+
7 rows in set (0.00 sec)root@localhost 11:11:49>
grant all privileges on wiki.* to 'remote_test'@'10.0.0.%' identified by '123456';可用grant all privileges on wiki.* to 'remote_test002'@'10.0.0.0/255.255.255.0' identified by '123456';可用grant all privileges on wiki.* to 'remote_test001'@'10.0.0.0/24' identified by '123456';不可用
远端连接的命令：
mysql -u remote_test -h 10.0.0.9 -p123456
-P 指定端口
INVOKE收回某个授权/反向查看ALL PRIVILEGES都有哪些具体权限
root@localhost 11:24:12> show grants for 'standby'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for standby@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'standby'@'localhost' IDENTIFIED BY PASSWORD '*68400CF212112D85B99EB397ADCB27748218BFAE' |
| GRANT ALL PRIVILEGES ON `iQiYi`.* TO 'standby'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root@localhost 11:24:16> REVOKE INSERT ON `iQiYi`.* FROM 'standby'@'localhost';
Query OK, 0 rows affected (0.00 sec)root@localhost 11:28:27> show grants for 'standby'@'localhost';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for standby@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'standby'@'localhost' IDENTIFIED BY PASSWORD '*68400CF212112D85B99EB397ADCB27748218BFAE' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `iQiYi`.* TO 'standby'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root@localhost 11:28:29> root@localhost 11:31:50> GRANT INSERT ON `iQiYi`.* to 'standby'@'localhost';
Query OK, 0 rows affected (0.00 sec)root@localhost 11:32:08> show grants for 'standby'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for standby@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'standby'@'localhost' IDENTIFIED BY PASSWORD '*68400CF212112D85B99EB397ADCB27748218BFAE' |
| GRANT ALL PRIVILEGES ON `iQiYi`.* TO 'standby'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)root@localhost 11:32:10>
mysqldump专业备份命令
2018-06-01 补充
mysql> desc app01_identity;
+-----------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-------+
| app_id | varchar(20) | NO | PRI | NULL | |
| active | int(11) | NO | | NULL | |
| last_time | timestamp | YES | | CURRENT_TIMESTAMP | |
| capbility | int(11) | YES | | 0 | |
+-----------+-------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)mysql> # 使用 ALTER 修改时间字段自动更新为当前时间，即记录这条数据最后更新的时间
mysql> ALTER TABLE app01_identity change last_time last_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql>
mysql> desc app01_identity;
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| app_id | varchar(20) | NO | PRI | NULL | |
| active | int(11) | NO | | NULL | |
| last_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| capbility | int(11) | YES | | 0 | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)mysql> select * from app01_identity;
+------------------+--------+---------------------+-----------+
| app_id | active | last_time | capbility |
+------------------+--------+---------------------+-----------+
| F9E86B425CC0CD4D | 1 | 2018-05-22 12:18:21 | 1 |
+------------------+--------+---------------------+-----------+
1 row in set (0.00 sec)# 执行更新操作： queryset_obj.update(active=2, capbility=0)
# 注意：update操作只有QuerySet对象才能使用，单个的obj对象是不能用的
# 即：先filter，查询出QuerySet，然后再执行QuerySet.update(...)mysql>
mysql> select * from app01_identity;
+------------------+--------+---------------------+-----------+
| app_id | active | last_time | capbility |
+------------------+--------+---------------------+-----------+
| F9E86B425CC0CD4D | 2 | 2018-06-01 14:33:02 | 0 |
+------------------+--------+---------------------+-----------+
1 row in set (0.00 sec)mysql>
查看注释和建表语句
mysql> show full columns from app01_identity;
+-----------+-------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-----------+-------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
| app_id | varchar(20) | utf8_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| active | int(11) | NULL | NO | | NULL | | select,insert,update,references | |
| last_time | timestamp | NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | 最后更新时间 |
| capbility | int(11) | NULL | YES | | 0 | | select,insert,update,references | |
+-----------+-------------+-----------------+------+-----+-------------------+-----------------------------+---------------------------------+--------------------+
4 rows in set (0.01 sec)mysql> show create table app01_identity;
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| app01_identity | CREATE TABLE `app01_identity` (
`app_id` varchar(20) NOT NULL,
`active` int(11) NOT NULL,
`last_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
`capbility` int(11) DEFAULT '0',
PRIMARY KEY (`app_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
MySQL里 in 的使用
select * from table where uname in(select uname from user);select * from table where uname in('aaa','bbb','ccc','ddd','eee','ffff');
MySQL结合子查询进行批量删除
mysql> delete from identity
-> where app_id in(
-> select tb.app_id from
-> ( select app_id from identity where app_id like '%\_%' and charge=1 and active=1)tb
-> );
Query OK, 17 rows affected (0.00 sec)mysql>
mysql> delete from identity where app_id in( select tb.app_id from ( select app_id from identity where app_id like '%\_%' and charge=1 and active=1)tb );
Query OK, 322 rows affected (0.01 sec)mysql>
SQL命令查看MySQL数据库大小# 存放了其他的数据库的信息
use information_schema;# 查看所有数据的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;# 查看指定数据库(school)的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='school';# 查看指定数据库(school)指定表(student)的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='school' and table_name='student';# 获取指定表的行数
SELECT COUNT(*) FROM student;
# 存放了其他的数据库的信息
use information_schema;# 查看所有数据的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;# 查看指定数据库(school)的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='school';# 查看指定数据库(school)指定表(student)的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='school' and table_name='student';# 获取指定表的行数
SELECT COUNT(*) FROM student;
