mysql 基础操作全覆盖（适用与兼职DBA，不定期更新）
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 dropuser''@'localhost';dropuser""@"localhost.localdomain";或者deletefrommysql.userwherehost="127.0.0.1";flushprivileges; 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 index id_name_dept on student(name,dept); 注：红色为索引名称可以随便起啊，但是为了以后好改还是按标准比较好 创建唯一索引： Create unique index index_ind_name on student(name); 建出来时会是UNI OK,最后我们来说一下索引的具体应用 问题一：既然索引可以加快查询速度，那么为啥不把所有列建立索引啊？ 解答：因为索引不但占用系统空间，更新数据库时还需要维护索引数据，因此索引是一把双刃剑，并不是越多越好，例如比较小的表就不需要建索引了，写频繁读少的业务要少建立索引，因为写一次就需要更新一下索引 因为就像word目录一样，你写入一些东西之后就需要更新目录啊，不然是乱的，这就是为什么写频繁的数据库增加索引会很麻烦的原因，表更新了，就需要更新索引 问题二：在哪些列上创建索引更好呢？ select host,user from mysql.user where 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'and id=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 table student ( 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
