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;

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

select database();

select user();

2、DDL语句——表管理

①在库里建表

use oldboy; #进入到oldboy库

CREATE TABLE student (

  id int() NOT NULL,

  name char() NOT NULL,

  age tinyint() NOT NULL DEFAULT ,

  dept varchar() DEFAULT NULL

);

desc student; #查看表结构

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

②更改表名

rename table student to test;

alter table test rename to student;

③往表里插入数据

#创建test表

CREATE TABLE test (

  id int() NOT NULL AUTO_INCREMENT,

  name char() NOT NULL,

  PRIMARY KEY (id)

);

#插入数据

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

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

insert into test values(,'inca');

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

#查看表内容

mysql> select * from test;

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

| id | name    |

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

|   | oldboy  |

|   | oldgirl |

|   | inca    |

|   | zuma    |

|   | kaka    |

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

 rows in set (0.00 sec)

#一条语句插入多条数据

INSERT INTO `test` VALUES (,'oldboy'),(,'oldgirl'),(,'inca'),(,'zuma'),(,'kaka');

3、DCL语句——用户管理

1.查看当前用户

2.创建用户

3.查看用户对应的权限

4.删除用户

5.给用户授权

6.收回权限

7.工作博客授权

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

①修改表数据

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

②防止不加条件误删除

#登录的时候加-U参数

mysql> update test set name='kaka';

ERROR  (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 from test;#逻辑删除，一行一行删

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

④伪删除案例

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

update test set state=;

#正常显示：

select * from test where id=;

update test set state= where name='oldboy';

mysql> select * from test where state=;

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

| id | name    | state |

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

|   | oldgirl |      |

|   | inca    |      |

|   | zuma    |      |

|   | kaka    |      |

mysql> select * from test;

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

| id | name    | state |

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

|   | oldboy  |      |

|   | oldgirl |      |

|   | inca    |      |

|   | zuma    |      |

|   | kaka    |      |

二、SQL语句

1、select查询语句

mysql> select user,host,password from mysql.user;

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

| user       | host       | password                                  |

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

| root       | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| root       | 127.0.0.1  |                                           |

| rep        | 172.16..% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| wordpress  | 172.16..% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| wordpress  | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| www        | 172.16..% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| bbs        | 172.16..% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| bbs        | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| www        | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| jira       | 172.16..% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| jira       | localhost  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| confluence | 172.16..% | *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-

②客户端字符集

③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 <database_name>：#列出指定数据库中的表

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

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

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() NOT NULL AUTO_INCREMENT,

`name` char() 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 index from test;

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

create index index_name on test(name());

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());

5、复合索引

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

五、建立索引的流程

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=<Storage Engine>

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

SET @@storage_engine=<Storage Engine>;

③创建表语句的时指定

CREATE TABLE test (iINT) ENGINE = <Storage 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

①原子性

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

②一致性

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

③隔离性

事务之间不相互影响

④持久性

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

巴特西

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

最新文章

热门文章