linux mysql运维_linux运维、架构之路-MySQL(二)

一、SQL语句实战

1、DDL语句——库管理

①查看数据库

show databases;

show databases like'word%';#模糊查询数据库

②创建数据库

create database oldboy;#库的字符集和编译时指定的相同

③查看数据库的创建语句

show create database oldboy;

④查看数据库支持的字符集

show character set;

⑤创建数据库时指定字符集

create database oldboy character set utf8 collate utf8_general_ci;

⑥更改库的字符集

alter database oldboy character set gbk collate gbk_chinese_ci;

⑦查看当前所在的库以及当前的用户

selectdatabase();select user();

2、DDL语句——表管理

①在库里建表

use oldboy; #进入到oldboy库

CREATE TABLE student (

idint(4) NOT NULL,

namechar(20) NOT NULL,

age tinyint(2) NOT NULL DEFAULT 0,

dept varchar(16) DEFAULT NULL

);

desc student; #查看表结构

show create table student\G #查看创建表的语句

②更改表名

rename table student to test;

alter table test rename to student;

③往表里插入数据

#创建test表

CREATE TABLE test (

idint(4) NOT NULL AUTO_INCREMENT,

namechar(20) NOT NULL,

PRIMARY KEY (id)

);

#插入数据

insert into test(id,name) values(1,'oldboy');

insert into test(name) values('oldgirl');

insert into test values(3,'inca');

insert into test values(4,'zuma'),(5,'kaka');

#查看表内容

mysql> select * fromtest;+----+---------+

| id | name |

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

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

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

5 rows in set (0.00 sec)

#一条语句插入多条数据

INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');

3、DCL语句——用户管理

1.查看当前用户

2.创建用户

3.查看用户对应的权限

4.删除用户

5.给用户授权

6.收回权限

7.工作博客授权

4、DML语句——表内容管理

①修改表数据

update test set id=6 where name='kaka';

②防止不加条件误删除

#登录的时候加-U参数

mysql> update test set name='kaka';

ERROR1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

至于防止误操作导致上述数据库故障案例的方法之一：http://oldboy.blog.51cto.com/2561410/1321061

③删除表

delete fromtest;#逻辑删除，一行一行删

truncate table test;#物理删除，pages(block)，效率高

④伪删除案例

alter table test add state tinyint(2) not null default 1;

update testset state=1;

#正常显示：select * from test where id=1;

update testset state=0 where name='oldboy';

mysql> select * from test where state=1;+----+---------+-------+

| id | name | state |

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

| 2 | oldgirl | 1 |

| 3 | inca | 1 |

| 4 | zuma | 1 |

| 5 | kaka | 1 |mysql> select * fromtest;+----+---------+-------+

| id | name | state |

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

| 1 | oldboy | 0 |

| 2 | oldgirl | 1 |

| 3 | inca | 1 |

| 4 | zuma | 1 |

| 5 | kaka | 1 |

二、SQL语句

1、select查询语句

mysql> select user,host,password frommysql.user;+------------+------------+-------------------------------------------+

| user | host | password |

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

| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root | 127.0.0.1 | |

| rep | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| wordpress | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| wordpress | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| www | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| bbs | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| bbs | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| www | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| jira | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| jira | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| confluence | 172.16.1.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| confluence | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

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

用法：

select * from oldboy.test;

select id,name from oldboy.test;

select id,name from test where id=2;

select id,name from test where name='oldgirl';

select id,name from test where id>2;

select id,name from test where id>2 and id<4;

select id,name from test where id>2 or id<4;

select id,name from test;

select id,name from test order by id asc;

select id,name from test order by id desc;

select id,name from test limit 1,3;

2、字符集

MySQL数据库的字符集：

字符集(CHARACTER)

校对规则(COLLATION)

MySQL中常见的字符集：

UTF8

LATIN1

GBK

常见校对规则：

ci：大小写不敏感

cs或bin：大小写敏感

可以使用以下命令查看：

show charset;

show collation；

①linux系统字符集

[root@db02 ~]# echo $LANG

en_US.UTF-8

②客户端字符集

③MySQL字符集

#编译时指定

cmake .-DDEFAULT_CHARSET=utf8 \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \

#配置文件里指定

[mysqld]

character-set-server=utf8

二进制安装的MySQL默认字符集为latin1,配置文件中修改

character-set-server = utf8

3、生产环境更改数据库(含数据)字符集的方法

alter database oldboy CHARACTER SET utf8 collate utf8_general_ci;#更改库的字符集

alter table t1 CHARACTER SET latin1;#更改表的字符集

三、MySQL中的show语句

SOHW databases：#列出所有数据库

SHOW TABLES：#列出默认数据库中的表

SHOW TABLES FROM ：#列出指定数据库中的表

SHOW COLUMNS FROM ：#显示表的列结构

SHOW INDEX FROM ：#显示表中有关索引和索引列的信息

SHOW CHARACTER SET：#显示可用的字符集及其默认整理

SHOW COLLATION：#显示每个字符集的整理

SHOW STATUS：#列出当前数据库状态

SHOW VARIABLES：#列出数据库中的参数定义值

1、工作中利用Information_schema获取元数据的方式

①备份

mysqldump -uroot -p123456 -B world >/tmp/world_bak.sql#备份指定单个库

mysqldump-uroot -p123456 -A >/tmp/full.sql#备份所有库

②分库分表备份

SELECT CONCAT("mysqldump -uroot -p123"," ",table_schema," ",table_name,"> /tmp/",table_schema,"_",table_name,"_bak.sql")

FROM information_schema.tables

WHERE table_schema='world'INTO OUTFILE'/tmp/bak.sh

注：升级、迁移备份数据库时，只备份生产库，不需要备份系统库，比如mysql、information_schema等

四、创建索引

索引好比一本书的目录，会让你更快的找到需要的内容

1、主键索引，在表的对应列上创建

①建表的时候后面加索引

CREATE TABLE `test` (

`id`int(4) NOT NULL AUTO_INCREMENT,

`name`char(20) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

②建表后添加主键索引

create index index_name on test(name);

alter table test add index index_name(name);

③删除索引

alter table test drop index index_name;

④查询

desc test;

show indexfrom test;

⑤指定字段前n个字符建立索引语句

create index index_name on test(name(8));

2、唯一索引

①创建唯一索引

create unique index name_uindex on test(name);#与业务有关

②删除唯一索引

alter table test drop index name_uindex;

3、普通索引

①添加普通索引

create index index_name on test(name);

alter table test add index name_idx(name);

②删除索引

alter table test drop index index_name;

4、前缀索引

create index index_name on test(name(8));

5、复合索引

create index index_name on test(name(8),sex(2));

五、建立索引的流程

1、找到慢SQL

show processlist;

2、用expain查看SQL的执行计划

explain select id,name from test where name='oldboy'\G

3、查看表的唯一值数量

select count(distinct user,host) from mysql.user;

4、流量低谷时建立索引

alter table test add index name_idx(name);

六、MySQL存储引擎

1、存储引擎的基本管理

①查看当前会话的默认存储引擎

SELECT @@default_storage_engine;

②使用show确认每个表的存储引擎

SHOW CREATE TABLE test\G

2、设置存储引擎

①设置服务器级别默认存储引擎

[mysqld]default-storage-engine=

②使用SET命令为当前用户会话设置

SET @@storage_engine=;

③创建表语句的时指定

CREATE TABLE test (iINT) ENGINE = ;

3、InnoDB体系结构

①共享表空间设置

[mysqld]

innodb_data_file_path=datafile_spec1[;datafile_spec2]……

②配置表空间例子

创建一个表空间，其中包含一个名为ibdata1且大小为50MB(固定)的数据文件和一个名为ibdata2且为50MB(自动扩展)的数据文件

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

4、存储引擎事务ACID

①原子性

所有语句作为一个单元全部成功执行或者全部取消

②一致性

事务开始时处于一致性状态，则执行该事务期间也将保留一致状态

③隔离性

事务之间不相互影响

④持久性

事务所有的更改都会记录在数据库中，所做的更改不会丢失