一、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
①原子性
所有语句作为一个单元全部成功执行或者全部取消
②一致性
事务开始时处于一致性状态,则执行该事务期间也将保留一致状态
③隔离性
事务之间不相互影响
④持久性
事务所有的更改都会记录在数据库中,所做的更改不会丢失