首页 / MYSQL / 记录MySQL的一些基础操作
记录MySQL的一些基础操作
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了记录MySQL的一些基础操作，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含46457字，纯文字阅读大概需要67分钟。
内容图文
为表的字段添加索引
索引类似于书的目录，如果在字段上建立索引，那么以索引列为查询条件就可以加快查询数据的速度
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
本文系统来源：http://www.cnblogs.com/standby/p/7158470.html
内容总结
以上是互联网集市为您收集整理的记录MySQL的一些基础操作全部内容，希望文章能够帮你解决记录MySQL的一些基础操作所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】