一、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(4) NOT NULL, name char(20) NOT NULL, age tinyint(2) NOT NULL DEFAULT 0, dept varchar(16) DEFAULT NULL ); desc student; #查看表结构 show create table studentG #查看创建表的语句
②更改表名
rename table student to test;
alter table test rename to student;
③往表里插入数据
#创建test表 CREATE TABLE test ( id int(4) NOT NULL AUTO_INCREMENT, name char(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 * from test; +----+---------+ | 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'; ERROR 1175 (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(2) not null default 1; update test set state=1; #正常显示： select * from test where id=1; update test set 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 * from test; +----+---------+-------+ | 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 from mysql.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 <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(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 index from 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 testG
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
①原子性
所有语句作为一个单元全部成功执行或者全部取消
②一致性
事务开始时处于一致性状态，则执行该事务期间也将保留一致状态
③隔离性
事务之间不相互影响
④持久性
事务所有的更改都会记录在数据库中，所做的更改不会丢失