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

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