Mysql数据库
MySQL，是当前最流行的关系型数据库管理系统（Relational Database Management System）应用软件之一，应用广泛。
特点：开源，且支持 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等多种语言。支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
常用操作
操作分类：用户管理操作，数据库操作，表操作，数据操作，数据库性能优化操作。
一些常用操作的关键词：
# 增删改查
union, select from
insert into values
update set, alter rename/modify/change
delete from, drop
show
select into outfile/dumpfile fields terminated by 'exp', lines terminated by '\n'
load data local infile 'dump.txt' into table mytbl;
select load_file('/etc/profile')
select case when 2>1 then 'aaa' else 'bbb' end
# 条件筛选
where having
as
with rollup
and/&&, or/||, xor, not, like, regexp, is
in, between and
order by, group by
# 逗号替换
limit 1,2 等价于 limit 2 offset 1
left join on, right join on, inner join on
desc, asc
# 函数
user(), version() 或 @@version, database()
cast("2017-08-29" as DATE)
charset
convert('ABC' USING gbk)
count, sum, avg
bin, binary, conv, mid, ascii, filed, locate
left/right, position, substr/substring
ifnull, if, isnull, nullif
sleep
coalesce
function
concat, group_concat, concat_ws
updatexml, extractvalue, name_const
# 数学函数
floor, rand,exp,log, round
geometrycollection, multipoint, polygon, multipolygon
linestring, multilinestring,
# 运算符
+，-，*，/，%，mod，div，~
>，<，=，:=，!=
!， &， |， ^ ，>>， <<
连接数据库：
mysql -h 192.168.1.23 -P 3306 -u root -p
Enter password: ***
......
......
mysql>
依次指定mysql服务器的ip、端口，用户名root，最后一个参数 -p 指定用密码登录。
需要说明的是，mysql执行语句不限制关键词的大小写，select 、SELECT、SelEct 完全等价。
1、用户管理操作
创建用户test，密码123456，其可在任何ip地址发起登录操作
CREATE USER ‘test’@’%’ IDENTIFIED BY ‘123456’;
设置与更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('newpassword1');
查看用户权限
查看当前用户权限
show grants;
查看dba用户的权限
show grants for dba@localhost;
为数据库TUTORIALS添加用户(用户名zara，密码zara123)，限制客户端登录地址为localhost。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON TUTORIALS.* TO 'zara'@'localhost' IDENTIFIED BY 'zara123';
重新载入授权表，使权限修改操作生效
FLUSH PRIVILEGES;
撤销已经赋予给 dba 用户的所有权限
revoke all on *.* from dba@localhost;
删除用户test
DROP USER 'test'@'localhost';
对 存储过程、函数的操作权限设置
grant execute on procedure testdb.pr_add to 'dba'@'localhost';
grant execute on function testdb.fn_add to 'dba'@'localhost';
2、数据库操作
查看数据库
SHOW DATABASES;
数据库切换（选择）
use mysql;
创建数据库dbname
CREATE DATABASE dbname;
删除数据库
DROP DATABASE database_name;
查看 创建数据库 test 的语句
show create database test;
3、表操作
查看数据表
SHOW TABLES;
查看表 t2 结构
desc
或者
show columns from t2;
查看 创建表 t2 的语句
show create table t2;
显示数据表的字段信息
SHOW COLUMNS FROM table_name;
创建表 EMPLOYEE
CREATE TABLE EMPLOYEE (FIRST_NAME CHAR(20) NOT NULL,LAST_NAME CHAR(20),AGE INT,SEX CHAR(1),INCOME FLOAT);
删除表
DROP TABLE table_name;
DROP TABLE IF EXISTS EMPLOYEE;
添加表的字段i（包含位置在c之后）及其数据类型
ALTER TABLE test_tbl ADD i INT;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
删除表的字段i
ALTER TABLE test_tbl DROP i;
修改表的字段c数据类型
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
修改表的字段c名称和数据类型
ALTER TABLE testalter_tbl CHANGE c d INT;
删除表的索引index_name
ALTER TABLE table_name DROP INDEX index_name;
删除外键约束
alter table tableName drop foreign key keyName;
修改表名
ALTER TABLE table_name rename to tbl2;
删除表内的数据，但并不删除表本身（保留表结构）
TRUNCATE TABLE table_name;
修改存储引擎
alter table tableName engine=myisam;
4、数据操作
（1）增、删、改、查
查询数据
select * from sea_admin where income >10000 order by id desc limit 0,1;
select * from tbl1 as a left join tbl2 as b on a.id=b.id where a.id > 10;
SELECT DEPT, MAX(SALARY) AS ma, MIN(SALARY) AS mi
FROM staff GROUP BY DEPT
HAVING AVG(SALARY) > 3000
ORDER BY DEPT
删除数据
DELETE FROM EMPLOYEE WHERE AGE > 30;
修改数据
UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M';
插入数据
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME, AGE, SEX, INCOME) VALUES ('Mac', 'Mohan', 20, 'M', 2000);
（2）数据导入、导出（备份）
交互模式：
导出表的数据，也可以认为是将数据写入文件中。
select * from tb_test into outfile "E:/TEST/test.txt"；
select * from tb_test into dumpfile "E:/TEST/test.txt"；
SELECT a,b,c INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
导入备份数据abc.sql
source /home/abc/abc.sql;
命令行模式：
备份某个数据库test的指定表users的数据
mysqldump -u root -p test users > dump.txt
password ******
备份某个数据库的数据
mysqldump -h 1.2.3.4 -P 3306 -u root -p database_name > database_dump.txt
password ******
导入备份的数据库时，确保已在mysql中建立同名数据库
mysql -u root -p database_name < dump.txt
password *****
备份所有数据库的数据
mysqldump -h 1.2.3.4 -P 3306 -u root -p --all-databases > database_dump.txt
password ******
（3）视图操作
数据库视图的本质，是创建 虚拟表，通过虚拟表实现对数据的重构，但是该虚拟表只能进行查询 和整体的删除操作，无法进行修改等写操作，当元素数据表结构（字段名）发生变化时，视图失效。
视图 v_123 的创建
create view v_123 as select name,email from tbl123;
对应的虚拟表 可用看作是通过 as 后的 select语句 得到的。
视图的删除
drop view v_123;
5、索引操作
为users表创建索引index1
CREATE INDEX index1 ON users(username(10));
ALTER TABLE users ADD INDEX c(username);
创建唯一索引
CREATE UNIQUE INDEX index1 ON users(username(10));
ALTER TABLE users ADD UNIQUE c (username);
创建复合索引
alter table t add index idx12(c1,c2);
删除索引
DROP INDEX index1 ON users;
ALTER TABLE users DROP INDEX index1;
显示数据表的详细索引信息
SHOW INDEX FROM table_name;
添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
删除主键
ALTER TABLE users DROP PRIMARY KEY;
启用 查询语句时间检测，关闭时将其值设置为0
set profiling=1;
查看数据库 语句的执行时间
show profiles;
6、事务
事务：数据库中，一系列操作的集合，最小操作单元。
特性：原子性（A），一致性（C），隔离性（I），持久性（D）。
事务开始后，数据库语句会 关闭（当前用户的）自动提交（commit），同时其他用户处于 队列等待状态。
事务开启：
begin;
或者
start transaction;
提交执行：
commit;
事务回滚：
rollback;
7、文件操作
读取文件
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
select load_file('/etc/passwd');
select load_file('C:\\WWW\\phpinfo.php');
select load_file('C:/WWW/phpinfo.php');
select load_file(0x433A2F5757572F706870696E666F2E706870);
select load_file(char(67,58,47,87,87,87,47,112,104,112,105,110,102,111,46,112,104,112));
查询日记是否开启
show variables like "%general_log%"；
开启日志
SET GLOBAL general_log = 'On';
设置日志文件
SET GLOBAL general_log_file = '/var/www/html/mylog.php';
在数据库 information_schema 可用的前提下，可用使用一句话查看所有数据库、表和字段，该功能的实现 巧妙的借助了 变量。
select (@) from (select (@:=0),(select (@) from (information_schema.columns) where (table_schema>=@) and (@) in (@:=concat(@,0x0a,'[',table_schema,']>',table_name,'>',column_name))))x;
或者查询当前数据库中所有的表名 和字段名：
select * from (select (concat(@:=0,(select count(*) from `information_schema`.columns where table_schema=database() and @:=concat(@,0xa,table_schema,0x3a3a,table_name,0x3a3a,column_name)),@)))x;
扫一扫
494
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
