记录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>

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: 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专业备份命令

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

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

posted @ 2017-07-13 01:28 lixin[at]hitwh 阅读(390) 评论(0) 编辑收藏举报

刷新页面返回顶部

青山

运动、读书、平和待人

记录MySQL的一些基础操作

MySQL建表操作

为表的字段添加索引

alter table 修改表结构

1.alter 增加主键索引

2.alter删除主键索引（然后再添加主键索引）

3.alter删除普通索引以及添加普通索引

4.alter 修改字段

5.alter 添加列

6.查看表字段的注释

7.添加注释

8.对字段前n个字符创建索引

9.更该索引

10.为表的多个字段创建联合索引

11.删除索引

12.创唯一索引（非主键）

Finnaly

alter添加字段到指定的位置

INSERT 和 UPDATE

MYSQLDUMP

降序/升序/limit

like 模糊匹配

常用函数

创建新用户并授权

只使用 `grant` 创建用户并授权

使用 `create` 和 `grant` 创建用户并授权

查看某个用户的授权情况

授权局域网内主机远程连接数据库

远端连接的命令：

INVOKE收回某个授权/反向查看ALL PRIVILEGES都有哪些具体权限

mysqldump专业备份命令

SQL命令查看MySQL数据库大小

公告

青山

运动、读书、平和待人

记录MySQL的一些基础操作

MySQL建表操作

为表的字段添加索引

alter table 修改表结构

1.alter 增加主键索引

2.alter删除主键索引（然后再添加主键索引）

3.alter删除普通索引以及添加普通索引

4.alter 修改字段

5.alter 添加列

6.查看表字段的注释

7.添加注释

8.对字段前n个字符创建索引

9.更该索引

10.为表的多个字段创建联合索引

11.删除索引

12.创唯一索引（非主键）

Finnaly

alter添加字段到指定的位置

INSERT 和 UPDATE

MYSQLDUMP

降序/升序/limit

like 模糊匹配

常用函数

创建新用户并授权

只使用 grant 创建用户并授权

使用 create 和 grant 创建用户并授权

查看某个用户的授权情况

授权局域网内主机远程连接数据库

远端连接的命令：

INVOKE收回某个授权/反向查看ALL PRIVILEGES都有哪些具体权限

mysqldump专业备份命令

SQL命令查看MySQL数据库大小

公告

只使用 `grant` 创建用户并授权

使用 `create` 和 `grant` 创建用户并授权