mysql基础操作全覆盖（适用与兼职DBA，不定期更新）

创新互联公司是一家以重庆网站建设公司、网页设计、品牌设计、软件运维、seo优化、小程序App开发等移动开发为一体互联网公司。已累计为成都VR全景等众行业中小客户提供优质的互联网建站和软件开发服务。

1.MySQL版本应该怎么选择

由于5.5-5.6对mysql性能做了较大优化，所以一般选择mysql 5.5 或者 5.6  

mysql 5.5.xx -5.6.xx 产品的特殊性，所以编译方式也和早期的产品安装方式不同，采用cmake或者gmake方式进行编译安装。即 ./cmake ; make ; make install ,生产场景的具体命令及参数为详见mysql 一键安装脚本 

一键安装脚本下载链接 http://down.51cto.com/data/2228998 

2.设置mysql的初始密码

mysql 安装完毕后是没有初始用户名密码的，所以我们要为mysql设置一个初始密码

mysqladmin -uroot password 1qaz@WSX？ --（备注，是mysqladmin，而不是mysql）

3.查看数据库 

show databases ;

4.使用某个库

use 库名 ；

5.查看当前数据库

select database ();

6.删除一个库

drop database test（库名称为test） ;

7.使用某个库

use 库名 ；

8.进入库后查看表

show tables ；

9.查看表结构

desc table名 ；

或者select * from table 名 

10.查看所有用户

select host,user from mysql.user ;

11.删除用户

一般删除用户就用drop ，但是如果名字中有大写或者特殊字符，drop可能会有问题，会用delete

drop user ''@'localhost';

drop user ""@"localhost.localdomain";
或者
delete from mysql.user  where host="127.0.0.1";

flush privileges;

12.新建用户并为用户赋权/新建管理员

grant all on *.* to 'dailiang'@'%' identified by '1qaz@WSX?';

flush privileges;

这样这个用户与管理员的差别就是没有grant权限 

grant all on *.* to 'dailiang'@'%' identified by '1qaz@WSX?' with grant option;

13.只设置查询权限

grant select on *.* to 'dailiang'@'%' identified by '1qaz@WSX?' ;

flush privileges；

grant select on *.* to 'dailiang'@'10.0.0.%' identified by '1qaz@WSX?' ;

表示对10.0.0.x 网段授权

13.查看用户权限

show grants for 'dailiang'@'%';

或者

select * from mysql.user where user='dailiang'\G;

14. 收回权限之revoke

help revoke；

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

15.交互式执行sql，不用登陆数据库

mysql -uroot -p1qaz@WSX? -e"show grants for 'dailiang'@'%';" 

16.mysql用户一共18个权限

Select

Insert

Update

Delete

Create

Drop         

Grant

References 

Index

Alter

17.修改用户密码

UPDATE mysql.user SET password=PASSWORD('新密码') WHERE user='用户名

update mysql.user set password=PASSWORD('111') where user='dailiang' and host='%';

flush privileges;

注意：

1.如果不加WHERE 条件，则会把所有用户的密码都修改为’新密码’

密码修改完成后，需要进行权限刷新操作才能生效，FLUSH PRIVILEGES;

ROOT用户可以修改自己的密码，也可以修改其他用户的密码

其他用户只能修改自己的密码

2.PASSWORD函数

mysql> SELECT PASSWORD('111');

用于把密码明文进行加密,所得到的密码为原密码的哈希值。

mysql> SELECT PASSWORD('111');

+-------------------------------------------+

| PASSWORD('111')                           |

+-------------------------------------------+

| *832EB84CB764129D05D498ED9CA7E5CE9B8F83EB |

+-------------------------------------------+

1 row in set (0.00 sec)

18.建表语句

建表格式为：

create table 表名（

字段名1 类型1，

字段名2 类型2，

地段名3 类型3

）;

备注：注意逗号的位置

建表例子：

CREATE table student(

id int(4) NOT NULL,

name CHAR(20) NOT NULL,

age TINYINT(2) NOT NULL DEFAULT '0',

dept VARCHAR(16) DEFAULT NULL

);

int 整数类型  （4）是指长度   not null 是指不允许是空的

查看表结构如下：

mysql> desc student;

查看当初建表语句：

show create table student\G;

mysql> show create table student\G;

*************************** 1. row ***************************

       Table: student

Create Table: CREATE TABLE `student` (

  `id` int(4) NOT NULL,

  `name` char(20) NOT NULL,

  `age` tinyint(2) NOT NULL DEFAULT '0',

  `dept` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

解释：mysql 5.5以后使用的是innoDB引擎，mysql5.1及以前默认引擎为MyISAM

MYSQL表的字段类型：

int     整数类型

char    定长字符串类型，当存储时，总是用空格填满右边到指定的长度

varchar 变长字符串类型

19.索引（主键）相关知识 

索引就像书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询速度，这是mysql的优化的重要内容之一

主键索引：

查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列，主键列要求所有内容必须唯一，而索引列不要求内容必须唯一

主键就类似我们在学校的学号或者×××号一样，是唯一的，索引类是不唯一的 

可以这样说： 主键是一个特殊的索引

建立索引：

首先无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引

CREATE table student02(

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)

);

解析：

AUTO_INCREMENT                → 自增

PRIMARY KEY(id）              → PRI主键，以id列为主键

KEY INDEX_NAME(NAME)          → 普通索引

mysql> desc  student02;

PRI是主键索引

MUL是普通索引

建表后可以通过alter命令增加主键索引，但是一般不建议这样干

 alter table student change id id int primary key auto_increment;

增加主键前：

增加主键后：

查看某个表的索引和主键：

Show index from表名\G;

建表后删除普通索引：

alter table student02 drop index index_name  ;
index_name是索引的名字

建表后增加普通索引：

alter table student02 add index index_name(name);
index_name 是指索引的名字

name 是这行的名字，就是对name 这行添加

创建联合索引：

Create indexid_name_dept on student(name,dept);

注：红色为索引名称可以随便起啊，但是为了以后好改还是按标准比较好

创建唯一索引：

Create unique index index_ind_name on student(name);

建出来时会是UNI

OK,最后我们来说一下索引的具体应用

问题一：既然索引可以加快查询速度，那么为啥不把所有列建立索引啊？

解答：因为索引不但占用系统空间，更新数据库时还需要维护索引数据，因此索引是一把双刃剑，并不是越多越好，例如比较小的表就不需要建索引了，写频繁读少的业务要少建立索引，因为写一次就需要更新一下索引

因为就像word目录一样，你写入一些东西之后就需要更新目录啊，不然是乱的，这就是为什么写频繁的数据库增加索引会很麻烦的原因，表更新了，就需要更新索引

问题二：在哪些列上创建索引更好呢？

select host,user from mysql.userwhere user='xxx';
索引一般都会建在where后的条件列上

20.mysql插入之insert

先建一个简单的测试表

create table test (
id  int(4)     NOT NULL    AUTO_INCREMENT ,
name varchar(20) NOT NULL ,
PRIMARY KEY (id)
);

ok，准备工作做好了，开始插入第一条

插入单条数据：

insert into test(id,name) value (1,'dailiang');

插入多条数据：

insert into test(id,name) value (2,'dailiang02'), (3,'dailiang03');

注意： 数字 2 之类的不需要加单引，但是字符串系列如 dailiang 就需要加单引了

插入数据最好批量插入，不要一条一条的插入

21.mysql之select语句

limit用法：

select id，namefrom test limit 2  只查两行

select id，namefrom test limit 0，2； 只查0到2行

条件查询：

mysql> selectid,name from test where id=1;

mysql> selectid,name from test where name='oldgirl';

Attention: 字符串要接引号

后面加where条件语句：and  or

mysql> selectid,name from test where name='oldgirl'andid=3;

mysql> selectid,name from test where name='oldgirl'or  id=1;

范围查询：

Select id,name from test where id >2 and id<1

Select id,name from test where id >2 or id<1

排序: 默认是正序

Select id,name from test order by id asc; 正序

Select id,name from test order by id desc; 倒序

22.连表查询：

首先先建三张表做实验：

create tablestudent ( Sno int(10) NOT NULL COMMENT '学号',Sname varchar(16) NOT NULLCOMMENT '性名',Ssex char(2) NOT NULL COMMENT '性别',Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',Sdept varchar(16) default NULL COMMENT '学生所在系别',primary key (Sno),key index_Sname (Sname) );

create table course(
Cno int(10) NOT NULL COMMENT '课程号',
Cname varchar(64) NOT NULL COMMENT '课程名',
Ccredit tinyint(2) NOT NULL COMMENT '学分',
PRIMARY KEY (Cno)
)ENGINE=InnoDB AUTO_INCREMENT=1 ;

create table SC (
SCid int(12) not null auto_increment comment '主键',
Cno int(10) not null comment '课程号',
Sno int(10) not null comment '学号',
Grade tinyint(2) not null comment '学生成绩',
primary key (SCid)
)ENGINE=innodb ;

插入内容：

insert into student values(0001,'代亮','男',18,'清华计算机');

insert into student values(0002,'张峰来','女',68,'野鸡管理');

insert into student values(0003,'管建宇','女',28,'野鸡管理');

insert into student values(0004,'周新宇','女',19,'野鸡管理');

insert into student values(0005,'林绅武','女',31,'野鸡管理');

insert into student values(0006,'胡麒','女',38,'野鸡管理');

insert into student values(0007,'李楠','女',40,'野鸡管理');

insert into course values(1001,'LINUX高级',1);

insert into course values(1002,'小学数学',1);

insert into course values(1003,'小学语文',1);

insert into course values(1004,'小学英语',1);

insert into course values(1005,'大保健培训',6);

insert into SC(Sno,Cno,Grade) values (0001,1001,1);
insert into SC(Sno,Cno,Grade) values (0002,1002,1);
insert into SC(Sno,Cno,Grade) values (0002,1003,1);
insert into SC(Sno,Cno,Grade) values (0002,1004,1);
insert into SC(Sno,Cno,Grade) values (0002,1005,6);

insert into SC(Sno,Cno,Grade) values (0003,1002,1);
insert into SC(Sno,Cno,Grade) values (0003,1003,1);
insert into SC(Sno,Cno,Grade) values (0003,1004,1);
insert into SC(Sno,Cno,Grade) values (0003,1005,6);

insert into SC(Sno,Cno,Grade) values (0004,1002,0);
insert into SC(Sno,Cno,Grade) values (0004,1003,0);
insert into SC(Sno,Cno,Grade) values (0004,1004,0);
insert into SC(Sno,Cno,Grade) values (0004,1005,6);


insert into SC(Sno,Cno,Grade) values (0005,1002,0);
insert into SC(Sno,Cno,Grade) values (0005,1003,0);
insert into SC(Sno,Cno,Grade) values (0005,1004,0);
insert into SC(Sno,Cno,Grade) values (0005,1005,6);

经典连表查询：

select student.Sname,student.Ssex,student.Sage,student.sdept ,course.Cname,SC.Grade from student,course,SC where student.Sage >1 and student.Sno<100;

23.explain命令

用explain命令来查看某个select命令是不是走了索引了啊，如果没有走索引，我们可以见一个啊

但是索引不是乱建的，建索引原则，请看上文

mysql> explain select Sage from student where Sage=18\G;

可以看出where后面的Sage不是索引。。

为这一列添加普通索引：
mysql> Alter table student add index index_Sage(Sage);

这下就使用索引了额。。

24.修改表中的数据

更改表数据：

update student set Sname='张来' where Sno=2

强烈备注：千万不要忘记ｗｈｅｒｅ，如果忘记ｗｈｅｒｅ，那么整个表Ｓｎａｍｅ就全部变了，容易造成生产事故

25.删除整个表

drop tablename

26.删除表内容，但保持表结构

delete table名

或者

truncat table名

两种都是清空表内数据，但是保留表的内容

**删除了指定表中的所有行，但表的结构及其列，约束，索引等保持不变**

27.mysql停从库操作

show processlist;

show slave status\G;

slave stop;

service mysqld stop