当前位置：Gxlcms > 数据库问题 > 记录MySQL的一些基础操作

记录MySQL的一些基础操作

时间：2021-07-01 10:21:17 帮助过：6人阅读

为表的字段添加索引

索引类似于书的目录，如果在字段上建立索引，那么以索引列为查询条件就可以加快查询数据的速度

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

root@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);

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

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

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

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

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

root@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 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的一些基础操作

标签：参考   uniq   结构   int   server   谷歌   一个   blog   people   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！