首页 > 代码库 > 记录MySQL的一些基础操作
记录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: stuCreate 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=latin11 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: studentCreate 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=latin11 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: stuCreate 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=latin11 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: stuCreate 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: stuCreate 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: stuCreate 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);
Finnaly
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: stuCreate 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 1root@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.sqldrwx------ 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 version 5.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专业备份命令
记录MySQL的一些基础操作
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。