数据库基础学习笔记----从0-1

mysql 数据库基础学习

(1、真的是笔记 2、mysql 版本5.7.13 修改用户密码时候注意是password字段被authentication_string替换)

一、基本操作类

1、数据库的使用

进入数据库

mysql -u root -p

输入password :123456

查看本地数据库

show databases;

创建数据库

create database example；

指定编码格式以后都要制定

create database default charset=utf8

删除数据库

drop database example;

展示引擎

show engines;

show engines \G; //带格式化的

查询mysql 支持的引擎

show variables like 'have%';

2、创建表

显示数据库

show databases;

使用数据库

use example;

创建数据库表

create table example0 (id int, name varchar(20), sex boolean );

查看已经创建的表

show tables;

查看创建数据库表的创建语句

show create table example0;

简单删除数据库表

drop table example0;

创建单主键的表

create table example0 (id int primary key, name varchar(20), sex boolean );

创建多主键的表

create table example0 (id int , name varchar(20), sex boolean,primary key(id,name) );

创建有外键的表，外键的表要跟父表有关系，而且必须是父表的主键

create table example3 (id int , myname varchar(20), sex boolean,constraint c_fk foreign key(id,myname) references example0(id,name) );

删除时候先删除子表在删除父表

drop table example3;

drop table example0;

创建非空约束表

create table example3 (id int not null primary key , myname varchar(20), sex boolean );

创建表的唯一性约束unique

create table example4 (id int not null primary key , myname varchar(20) unique, sex boolean);

创建表的属性自动增加

create table example6 (id int not null primary key auto_increment , myname varchar(20) unique, sex boolean);

创建表的默认值

create table example7 (id int not null primary key auto_increment , myname varchar(20) unique, sex boolean,math int default 1);

3、查看表

查看表结构

describe example7;

或者 desc example7;

查看表详细结构语句

show create table example7;

4、修改表

修改表名

alter table example7 rename to example5;

alter table example7 rename example5;

修改字段的数据类型

desc example7;

alter table example7 modify id int(11);

5、增加字段 (增加和修改会涉及到位置问题)

alter table ..add..

增加无完整性约束条件的字段

alter table example7 add classname int(11);

// 先删除主键再删除主键属性再添加主键

alter table zxc4 drop primary key;

alter table zxc4 drop id;

alter table zxc4 add id int primary key;(如果后面带有first 则在第一个属性为id)

//指定位置添加字段（使用after）

alter table zxc4 add age int after sex;

修改字段名用change 修改字段位置用modify

修改字段名 (要带上新属性的类型) 旧名在前新名在后

alter table example7 change myname name int(1);

alter table example7 change myname name varchar(2);

更改到首位用first 记得要带全字段属性

alter table zxc4 modify name varchar(2) first;

更改到指定位置：after

alter table zxc4 modify name varchar(2) after sex;

添加一个外键

（alter table 表 add constraint 外键约束名称 foreign key (列) references 表 (主键列);）

alter table zxc4 add constraint zxcOut foreign key (className) references zxc(id);

6、删除字段

alter table .. drop ..

alter table zxc4 drop age;

删除表的外键约束

先添加

alter table zxc4 add constraint zxcOut foreign key (className) references zxc(id);

再删除

alter table zxc4 drop foreign key zxcOut;

7、修改表的引擎

先查看在修改

show create table zxc4;

alter table zxc4 engine=myisam;

8、删除表

drop table zxc;

删除外键关联的主表会报错

Cannot delete or update a parent row: a foreign key constraint fails

可以先删除关联表再删除也可以先去除关联关系在删除

alter table zxc4 drop foreign key zxcOut;

drop table zxc;

二、索引

快速找出匹配一个WHERE子句的行。

在多个表的查询时，执行连接时加快了与其他表中的行匹配的行的搜索。

对特定的索引列找出MAX()或MIN()值。

在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

分类普通索引唯一性索引全文索引单列索引多列索引空间索引

设计原则选择唯一性索引为经常排序分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数量量少的索引尽量使用前缀来索引删除使用低频的索引

1、创建索引

创建表的时候创建索引

create table 表名（属性名数据类型 [约束条件]，[unique|fulltext|spatial] index|key [别名]（属性名1 （长度））[asc|desc] ）

create table index1(id int ,name varchar(20),sex boolean,index(id));

或者 create table index1(id int ,name varchar(20),sex boolean,index index_id(id));

使用explain 查看索引是否被使用

explain select * from index1 where id=1 \G;

创建全文索引字段 char archer 或者text 而且引擎是myisam.

create table index3(id int ,name varchar(20),sex boolean,fulltext index index3_name(name))engine=myisam;

单列索引

create table index4(id int ,name varchar(20),sex boolean, index index4_name(name (10)));

多列索引

create table index5(id int ,name varchar(20),sex boolean, index index5_name(name,sex));

查看：explain select *from index5 where name='h' \G

创建空间索引

create table index6(id int ,space geometry not null,spatial index index6_name(space));

在已经创建的表上创建索引

create [unique|fulltext|spatical] index 索引名 on 表名（属性名[(长度)] [asc|desc]）

如：create index index1_name on index1 (name) ;

或者用alter语句创建索引

alter table 表名 add [unique|fulltext|spatical] index 索引名（属性名[(长度)] [asc|desc]）

alter table index1 add unique index index1_n (name(2) asc);

2、删除索引

drop index 索引名 on 表名

drop index index_id on index1;

3、使用索引

三、视图

视图就是基于sql语句的结果集的可视化的表

视图是一种虚拟的表，是从数据库中一个活多个表中导出来的表作用是方便对数据库的操作，依赖于原数据库表的操作

使操作简单增加数据安全性提高表的逻辑独立性

在MySQL中，可以通过CREATE VIEW语句创建视图。语法格式如下：

CREATE [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

VIEW 视图名[(属性清单)]

AS SELECT语句

[WITH [CASCADED|LOCAL] CHECK OPTION];

create [algorithm={undefined|merge|temptable}] view 视图名 [(属性列表)] as select 语句 [with [cascaded|local] check option]

ALGORITHM：可选。表示视图选择的算法。

UNDEFINED：表示MySQL将自动选择所要使用的算法。
MERGE：表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。

视图名：表示要创建的视图的名称。
属性清单：可选。指定了视图中各个属性的名词，默认情况下，与SELECT语句中查询的属性相同。
SELECT语句：是一个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。
WITH CHECK OPTION：可选。表示创建视图时要保证在该视图的权限范围之内。
CASCADED：可选。表示创建视图时，需要满足跟该视图有关的所有相关视图和表的条件，该参数为默认值。
LOCAL：表示创建视图时，只要满足该视图本身定义的条件即可。

提示：

使用CREATE VIEW语句创建视图时，最好加上WITH CHECK OPTION参数，同时，最好加上CASCADED参数。这样，从视图上派生出新视图后，更新新视图需要考虑其父视图的约束条件。这种方式比较严格，可以保证数据的安全性

创建视图时，需要有CREATE VIEW的权限。同时，应该具有查询涉及的列的SELECT权限。在MySQL数据库的user表中保存着这些权限信息，可以使用SELECT语句查询。

SELECT语句查询的方式如下：

SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名'

Select_priv：表示用户是否具有SELECT权限。Y表示拥有SELECT权限，N表示没有权限。
Create_view_priv：表示用户是否具有CREATE VIEW权限。Y表示拥有CREATE VIEW权限，N表示没有权限。
mysql.user：表示MySQL数据库下面的user表。
用户名：表示要查询哪个用户是否拥有DROP权限，该参数需要单引号引起来。因为该数据库系统中只有root用户，所以查询出来的结果只有root用户的权限。

查询如下：SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user=‘root’；

1、创建视图

单表上创建视图

create view department_view1 as select from department;

desc department_view1;

create view department_view2(name,function,location) as select d_name,function,address from department;

desc department_view2;

多表上创建视图

create algorithm =merge view worker_view1(name,department,sex,age,address) as select name,department.d_name,sex,2009-birthday,address from worker,department where worker.d_id=department.d_id with local check option;

desc worker_view1;

2、查看视图

describe 视图名；

show table status like ‘视图名’

show table status like 'worker_view1' \G;

结果中comment项为view 说明 worker_view1 是虚拟表

查看视图详细信息

show create view worker_view1;

view 表中查看视图详细信息

select *from information_schema.views;

3、修改视图如果视图存在则进行修改如果不存在则创建

create or replace [algorithm={undefined|merge|temptable}] view 视图名 [(属性列表)] as select 语句 [with [cascaded|local] check option]

也可以用alter

alter view [algorithm={undefined|merge|temptable}] view 视图名 [(属性列表)] as select 语句 [with [cascaded|local] check option]

alter view department_view2(department,name,function,location) as select d_id,d_name,function,address from department;

4、更新视图

是指通过视图来插入更新和删除表中的数据视图是个虚拟表所以通过视图更新的时候都是转化到基础表

在表中插入

insert department (d_id,d_name,function,address) values (1001,'renshibu','guanli','building2flot2');一条数据

创建一个视图

create view department_view3(name,function,address) as select d_name,function,address from department where d_id=1001;

更新视图

update department_view3 set name='1',function='11',address='111';

查看结果

select *from department_view3；

同时原表也进行了更新

select *from department;

注意：如下视图不能更新：

视图中有sum() count() max() min()等函数的时候不能更新

包含union union all 等关键字的时候不能更新

常量视图不能更新

包含select子查询不能更新

由不可更新的视图导出来的视图

创建视图时候 algorithm 为temptable类型的

5、删除视图

删除之前先得有权限

也就是先执行一下

select drop_priv from mysql.user where user='root';

语法：

drop view [if exists] 视图名列表 [restrict|cascade]

drop view if exists department_view1,department_view2;

四、触发器

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

CREATE TRIGGER trigger_name

trigger_time

trigger_event ON tbl_name

FOR EACH ROW

trigger_stmt

触发器创建：

trigger_name：标识触发器名称，用户自行指定；

trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

trigger_event 详解

MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。

LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。

REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。

INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。

INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；

UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；

DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发。

BEGIN … END 详解

在MySQL中，BEGIN … END 语句的语法为：

BEGIN

[statement_list]

END

其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。

而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：

DELIMITER new_delemiter

new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：

DELIMITER $

在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

1、创建触发器

create trigger 触发器名称 before|after 触发事件 on 表名 for each row 执行语句

创建一个insert 触发器

create trigger dept_trig1 before insert on department for each row insert into trigger_time values(NOW());

创建存放触发器内容： create table trigger_time(time varchar(20) );

插入数据：insert into department values(1004,'22','22','22');

查看触发器：select *from trigger_time;

2、创建多个执行语句的触发器

mysql> delimiter &&

mysql> create trigger dept_trig2 after delete

-> on department for each row

-> begin

-> insert into trigger_time values('21:00:50');

-> insert into trigger_time values('21:00:55');

-> end

-> &&

mysql> delimiter ;

先删除数据

delete from department where id=1001;

在查看 select *from trigger_time;

3、查看触发器

show triggers;

triggers 表中查看

select *from information_schema.triggers \G;

或者带有trigger name 比如select *from information_schema.triggers where trigger_name=‘’ ;\G;

4、删除触发器

drop trigger 触发器名称；

drop trigger dept_trig2;

五、查询数据

select 属性列表 from 表名和视图列表 [where 条件表达式] [group by 属性名1 [having 条件表达式2] ] [order by 属性名2 [asc|desc]]

1、普通查询

select

单表所有字段

select * from employee;

单个或者多个字段

select name,age from employee;

where子查询有多种分别是比较指定范围指定集合匹配字符是否空值多个查询条件

使用group by 和where

select name,age from employee where age<26 order by num desc;

指定记录

select * from employee where d_id=1001;

带IN 查询 [NOT IN](x,y,z) :

select * from employee where d_id in (1001,1004);

带 [not ]between and查询注意是包含

select * from employee where d_id between 1001 and 1004;

带like的查询 [NOT] like ‘字符串’

如果是% 不介定多少字符如果是_ 表示下划线个字符

select * from employee where name like '%c'; // 以c 结尾前面多个字符

select * from employee where name like ‘Ar_c’;// 四个字符中间是任意字符

空字符查询 is [not] null

select * from employee where name is not null;

带and /or 多条件查询

select * from employee where name is not null and age >=24;

select * from employee where name is not null or age >=25;

查询结果不重复

select distinct name from employee；

对查询结果排序

select distinct name from employee order by age asc;

2、分组查询通常会有集合出现

group by 属性名 [having 条件表达式] [with rollup]

having 限制分组后的显示 with rollup 是记录记过总和的

group by 单独使用

select * from employee group by sex;

group by 与group_concat();// 分类下全部列举

select * , group_concat(name) from employee group by sex;

select name, group_concat(name) from employee group by sex;

group by 与集合函数/ count()

select sex,count(name) from employee group by sex;

select *,count(name) from employee group by sex;

group by 与having 限制输出的结果

select sex,count(sex) from employee group by sex having count(sex)>=3;

3、多条件分组

select * from employee group by sex,name;

使用 with rollup;

select * from employee group by sex with rollup;

4、limit 限制数量

limit 记录数

select * from employee limit 2;

指定初始位置 limit 初始位置, 记录数

select * from employee limit 0,2;

5、使用集合函数count() num() avg() max() min()等

select count(*) from employee;

select count(sex) from employee;

select avg(age) from employee;

6、连接查询

内连接可以查询两个或者两个以上的表

select * from employee, worker where employee.d_id=worker.d_id;

外连接 left 表示左查询左侧表1中所有记录 on 表2中的条件记录；

right 表示右查询表2中所有记录 on表1中的条件记录

select 属性名列表 from 表名1 left|right join 表名2 on 表名1.属性名=表名2.属性名2；

注意重叠的字段名要带表信息

select employee.name from employee left join worker on worker.d_id=employee.d_id ;

select homeaddr from employee left join worker on worker.d_id=employee.d_id ;

可以携带复合条件

select* from employee left join worker on worker.d_id=employee.d_id where employee.age>24;

7、子查询

就是一个查询语句嵌套在另一个查询语句中可以实现多表查询可以包含

in any all exists 还可以包含 = > 之类的

in 子查询

select * from employee where d_id in (select d_id from worker);

比较运算符的子查询

select * from employee where age >=(select age from worker where age>25);

带exists的子查询

mysql> select * from employee where exists (select age from worker where age>25);

带any的子查询满足any里的任何一个条件

select * from employee where age>any (select age from worker where age>25);

带all的子查询

select * from employee where age>all (select age from worker where age>25);//无数据

select * from employee where age>all (select age from worker where age=24);// 有数据

8、合并查询结果带all 不排重

select 语句1 union | union all select 语句2 union | union all select 语句n...

select d_id from employee union all select d_id from worker;

9、为表和字段取别名

表名别名

select * from employee e where e.d_id=1001;

字段名 [AS] 别名

select name n,homeaddr address from employee;

select name as n,homeaddr as address from employee;

10、使用正则表达式查询

属性名 regexp ‘匹配方式’

以某字符开头

select * from employee where name regexp '^张';

以某字符或者字符串结尾

select * from employee where name regexp '三$';

用.代替任何一个字符

select * from employee where name regexp '^A..c$';

匹配指定字符串中的任意一个

select * from employee where name regexp '[c]';

匹配制定外的字符

select * from employee where name regexp ‘[^a-w0-9]';

匹配指定字符串

select * from employee where name regexp 'ic|uc|ab';

使用* 或者+ *可以有0个 + 至少有一个字符

select * from employee where name regexp 'a*c';

使用{M}或者{M,N} 来指定字符串连续出现的次数

select * from employee where name regexp 'a{3}';

六、插入更新和删除

1、插入

为表的所有字段插入数据

insert into 表名 values();

insert INTO score VALUES (2,902,'计算机',65);

列出所有字段或者表的指定字段插入数据

insert INTO score(id,stu_id,c_name,grade) VALUES (2,902,'计算机',65);

insert INTO score(id,stu_id) VALUES (11,909);

同时插入多条数据

insert INTO score(id,stu_id) VALUES (12,909),(13,909);

将查询结果插入表中

insert into 表名1 （属性列表）select 属性列表2 from 表名2 where 条件表达式

insert INTO score(id,stu_id) select id from student where id=901;

2、更新数据

update 表名 set 属性名1=value1，属性名2=value2.。。where 条件表达式

update score set id=14 where id=901；

update score set grade=60 where grade is null；

3、删除数据

delete from 表名 [where 条件表达式];

delete from worker; 删除表里面全部内容

delete from score where grade = 60;

七、mysql 运算符

1、算术运算符 + - * / div % mod 求余

select grade,grade+5,grade-8,grade*2,grade/3,grade div 3,grade %3,mod(grade,3)from score;

2、比较运算符 is null, is not null , between and , in ,like regexp;

select 'b'='b','c'='c',null=null;

3、逻辑运算符

&& 或者and ! 或者not ||或者or 或 xor

select -1&&1,null&3;

4、位运算符 & | ~ ^ >> <<

select 5&6,5&6&7;

select 5<<4,5>>4;

5、运算符的优先级

优先级运算符

1(最高) !

2 -（负号）,~（按位取反）

3 ^（按位异或）

4 *,/(DIV),%(MOD)

5 +,-

6 >>,<<

7 &

8 |

9 =(比较运算),<=>,<,<=,>,>=,!=,<>,IN,IS NULL,LIKE,REGEXP

10 BETWEEN AND,CASE,WHEN,THEN,ELSE

11 NOT

12 &&,AND

13 XOR

14 ||,OR

15(最低) =(赋值运算),:=

第八、函数

参考大部分 http://www.cnblogs.com/kissdodog/p/4168721.html　　

MySQL数据库提供了很多函数包括：

* 数学函数；* 字符串函数；* 日期和时间函数；* 条件判断函数；* 系统信息函数；* 加密函数；* 格式化函数；

1、数学函数

　　数学函数主要用于处理数字，包括整型、浮点数等。

ABS(x) 返回x的绝对值　　

SELECT ABS(-1) -- 返回1

CEIL(x),CEILING(x) 返回大于或等于x的最小整数　　

SELECT CEIL(1.5) -- 返回2

FLOOR(x) 返回小于或等于x的最大整数　　

SELECT FLOOR(1.5) -- 返回1

RAND() 返回0->1的随机数　　

SELECT RAND() --0.93099315644334

RAND(x) 返回0->1的随机数，x值相同时返回的随机数相同　　

SELECT RAND(2) --1.5865798029924

SIGN(x) 返回x的符号，x是负数、0、正数分别返回-1、0和1　　

SELECT SIGN(-10) -- (-1)

PI() 返回圆周率(3.141593）　　

SELECT PI() --3.141593

TRUNCATE(x,y) 返回数值x保留到小数点后y位的值（与ROUND最大的区别是不会进行四舍五入）　　

SELECT TRUNCATE(1.23456,3) -- 1.234

ROUND(x) 返回离x最近的整数　　

SELECT ROUND(1.23456) --1

ROUND(x,y) 保留x小数点后y位的值，但截断时要进行四舍五入　　

SELECT ROUND(1.23456,3) -- 1.235

POW(x,y).POWER(x,y) 返回x的y次方　　

SELECT POW(2,3) -- 8

SQRT(x) 返回x的平方根　　

SELECT SQRT(25) -- 5

EXP(x) 返回e的x次方　　

SELECT EXP(3) -- 20.085536923188

MOD(x,y) 返回x除以y以后的余数　　

SELECT MOD(5,2) -- 1

LOG(x) 返回自然对数(以e为底的对数)　　

SELECT LOG(20.085536923188) -- 3

LOG10(x) 返回以10为底的对数　　

SELECT LOG10(100) -- 2

三角函数系列：

RADIANS(x) 将角度转换为弧度　　

SELECT RADIANS(180) -- 3.1415926535898

DEGREES(x) 将弧度转换为角度　　

SELECT DEGREES(3.1415926535898) -- 180

SIN(x) 求正弦值(参数是弧度)　　

SELECT SIN(RADIANS(30)) -- 0.5

ASIN(x) 求反正弦值(参数是弧度)

COS(x) 求余弦值(参数是弧度)

ACOS(x) 求反余弦值(参数是弧度)

TAN(x) 求正切值(参数是弧度)

ATAN(x) ATAN2(x) 求反正切值(参数是弧度)

COT(x) 求余切值(参数是弧度)

2、字符串函数

　　字符串函数是MySQL中最常用的一类函数，字符串函数主要用于处理表中的字符串。

CHAR_LENGTH(s) 返回字符串s的字符数

SELECT CHAR_LENGTH('你好123') -- 5

LENGTH(s) 返回字符串s的长度

SELECT LENGTH('你好123') -- 9

CONCAT(s1,s2,...) 将字符串s1,s2等多个字符串合并为一个字符串

SELECT CONCAT('12','34') -- 1234

CONCAT_WS(x,s1,s2,...) 同CONCAT(s1,s2,...)函数，但是每个字符串直接要加上x

SELECT CONCAT_WS('@','12','34') -- 12@34

select concat_ws('ssssssss','a','b','c','d');

INSERT(s1,x,len,s2) 将字符串s2替换s1的x位置开始长度为len的字符串

SELECT INSERT('12345',1,3,'abc') -- abc45

select insert('ssssssss',1,4,'r'); 同时要进行截取

UPPER(s),UCAASE(S) 将字符串s的所有字母变成大写字母

SELECT UPPER('abc') -- ABC

LOWER(s),LCASE(s) 将字符串s的所有字母变成小写字母

SELECT LOWER('ABC') -- abc

LEFT(s,n) 返回字符串s的前n个字符

SELECT LEFT('abcde',2) -- ab

RIGHT(s,n) 返回字符串s的后n个字符

SELECT RIGHT('abcde',2) -- de

LPAD(s1,len,s2) 字符串s2来填充s1的开始处，使字符串长度达到len

SELECT LPAD('abc',5,'xx') -- xxabc

RPAD(s1,len,s2) 字符串s2来填充s1的结尾处，使字符串的长度达到len

SELECT RPAD('abc',5,'xx') -- abcxx

LTRIM(s) 去掉字符串s开始处的空格

RTRIM(s) 去掉字符串s结尾处的空格

TRIM(s) 去掉字符串s开始和结尾处的空格

TRIM(s1 FROM s) 去掉字符串s中开始处和结尾处的字符串s1

SELECT TRIM('@' FROM '@@abc@@') -- abc

REPEAT(s,n) 将字符串s重复n次

SELECT REPEAT('ab',3) -- ababab

SPACE(n) 返回n个空格

REPLACE(s,s1,s2) 将字符串s2替代字符串s中的字符串s1

SELECT REPLACE('abc','a','x') --xbc

STRCMP(s1,s2) 比较字符串s1和s2

SUBSTRING(s,n,len) 获取从字符串s中的第n个位置开始长度为len的字符串

MID(s,n,len) 同SUBSTRING(s,n,len)

select substring('abcdefd',2,3);

LOCATE(s1,s),POSITION(s1 IN s) 从字符串s中获取s1的开始位置

SELECT LOCATE('b', 'abc') -- 2

INSTR(s,s1) 从字符串s中获取s1的开始位置

SELECT INSTR('abc','b') -- 2

REVERSE(s) 将字符串s的顺序反过来

SELECT REVERSE('abc') -- cba

ELT(n,s1,s2,...) 返回第n个字符串

SELECT ELT(2,'a','b','c') -- b

EXPORT_SET(x,s1,s2) 返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用。

SELECT EXPORT_SET(5,'Y','N',',',4) -- Y,N,Y,N

FIELD(s,s1,s2...) 返回第一个与字符串s匹配的字符串位置

SELECT FIELD('c','a','b','c') -- 3

FIND_IN_SET(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置

MAKE_SET(x,s1,s2) 返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。

SELECT MAKE_SET(1|4,'a','b','c'); -- a,c

SUBSTRING_INDEX

返回从字符串str的第count个出现的分隔符delim之后的子串。

如果count是正数，返回第count个字符左边的字符串。

如果count是负数，返回第(count的绝对值(从右边数))个字符右边的字符串。

SELECT SUBSTRING_INDEX('a*b','*',1) -- a

SELECT SUBSTRING_INDEX('a*b','*',-1) -- b

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c

LOAD_FILE(file_name) 读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

3、日期时间函数

　　MySQL的日期和时间函数主要用于处理日期时间。

CURDATE(),CURRENT_DATE() 返回当前日期

select curdate();

CURTIME(),CURRENT_TIME 返回当前时间

select current_time;

NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP() 返回当前日期和时间

select now();

select current_timestmp();

select localtimestamp();

select localtime();

select sysdate();

UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间

select unix_timestamp();

UNIX_TIMESTAMP(d) 将时间d以UNIX时间戳的形式返回

select unix_timestamp(now());

select unix_timestamp(sysdate());

FROM_UNIXTIME(d) 将UNIX时间戳的时间转换为普通格式的时间

select from_unixtime(1481006651);

UTC_DATE() 返回UTC日期

select utc_date();

UTC_TIME() 返回UTC时间

select utc_time();

MONTH(d) 返回日期d中的月份值，1->12

select month(localtime());

MONTHNAME(d) 返回日期当中的月份名称，如Janyary

select monthname(localtime());

DAYNAME(d) 返回日期d是星期几，如Monday,Tuesday

select dayname(localtime());

DAYOFWEEK(d) 日期d今天是星期几，1星期日，2星期一

select dayofweek(localtime());

WEEKDAY(d) 日期d今天是星期几，

select weekday(localtime());

WEEK(d)，WEEKOFYEAR(d) 计算日期d是本年的第几个星期，范围是0->53

select week(localtime());

DAYOFYEAR(d) 计算日期d是本年的第几天

select dayofyear(localtime());

DAYOFMONTH(d) 计算日期d是本月的第几天

select dayofmonth(localtime());

QUARTER(d) 返回日期d是第几季节，返回1->4

select quarter(localtime());

HOUR(t) 返回t中的小时值

select hour(localtime());

MINUTE(t) 返回t中的分钟值

select minute(localtime());

SECOND(t) 返回t中的秒钟值

select second(localtime());

EXTRACT(type FROM d) 从日期d中获取指定的值，type指定返回的值

select EXTRACT(localtime());

type可取值为：MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND

HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR

YEAR_MONTH

select extract(year_month from localtime());

select extract(hour_second from localtime());

TIME_TO_SEC(t) 将时间t转换为秒

select time_to_sec(localtime());

SEC_TO_TIME(s) 将以秒为单位的时间s转换为时分秒的格式

select sec_to_time(1111);

TO_DAYS(d) 计算日期d距离0000年1月1日的天数

SELECT TO_DAYS('2001-01-01 01:01:01');

FROM_DAYS(n) 计算从0000年1月1日开始n天后的日期

SELECT from_DAYS(1111);

DATEDIFF(d1,d2) 计算日期d1->d2之间相隔的天数

SELECT DATEDIFF('2001-01-01',now());

ADDDATE(d,n) 计算其实日期d加上n天的日期

ADDDATE(d，INTERVAL expr type) 计算起始日期d加上一个时间段后的日期

SELECT ADDDATE('2001-01-01',5);

SELECT ADDDATE(NOW(),interval 5 month);

SELECT DATE_add(NOW(),interval 5 month);

SUBDATE(d,n) 日期d减去n天后的日期

SUBDATE(d,INTERVAL expr type) 日期d减去一个时间段后的日期

SELECT DATE_sub(NOW(),5);

SELECT DATE_sub(NOW(),interval 5 month);

ADDTIME(t,n) 时间t加上n秒的时间

SUBTIME(t,n) 时间t减去n秒的时间

SELECT addtime(NOW(),5);

SELECT subtime(NOW(),5);

DATE_FORMAT(d,f) 按表达式f的要求显示日期d

SELECT DATE_FORMAT(now(),'%Y-%m-%d %r');

TIME_FORMAT(t,f) 按表达式f的要求显示时间tSELECT DATE_FORMAT(now(),'%Y-%m-%d %r');

SELECT DATE_FORMAT(curtime(),'%r');

GET_FORMAT(type,s) 获得国家地区时间格式函数

select get_format(date,'usa')

->%m.%d.%Y (注意返回的就是这个奇怪的字符串(format字符串))

4、条件判断函数

IF(expr,v1,v2)函数

如果表达式expr成立，返回结果v1；否则，返回结果v2。

select if(10>3,'right','wrong');

IFNULL(v1,v2)函数

如果v1的值不为NULL，则返回v1，否则返回v2。

select ifnull('right','wrong');

CASE选择

case when exp1 then v1 [when exp2 then v2]..[else vn]end // 条件符合

select case when grade>98 then 'good' when grade>70 then 'nice' else 'onlevel' end from score;

select *, case when grade>98 then 'good' when grade>70 then 'nice' else 'onlevel' end from score;

case exp when e1 then v1 [when e2 then v2]..[else vn]end // 值相等

select *, case grade when 98 then 'good' when 70 then 'nice' else 'onlevel' end from score;

5、系统信息函数

函数作用

VERSION() 返回数据库的版本号

select version();

CONNECTION_ID() 返回服务器的连接数

select connection_id();

DATABASE()、SCHEMA 返回当前数据库名

select database();

select schema();

USER()、SYSTEM_USER()、SESSION_USER()、

CURRENT_USER()、CURRENT_USER 返回当前用户

select user();

select currentuser();

select system_user();

select session_user();

select current_user;

select current_user();

CHARSET(str) 返回字符串str的字符集

select charset('str');

COLLATION(str) 返回字符串str的字符排列方式

select collation('str');

LAST_INSERT_ID()

select last_insert_id('str');

6、加密函数

加密函数password(str):该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。

select password('123456');

MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。

select md5('123456');

ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对

，需要同样的密码才能够解密。

select encode('123’,’z’);

select decode('lN','z');

7、格式化函数

格式化函数 FORMAT(x,n)函数可以将数字x进行格式化，将x保留到小数点后n位。

select format(3.141599877,3);

不同进制的数字进行转换

• ASCII(s) 返回字符串s的第一个字符的ASCII码；

select ascii('e');

• BIN（x）返回x的二进制编码；

• HEX(x) 返回x的十六进制编码；

• OCT(x) 返回x的八进制编码；

• CONV(x,f1,f2) 返回f1进制数变成f2进制数；

select conv(11,16,2);

IP地址与数字相互转换的函数

• INET_ATON(IP)函数可以将IP地址转换为数字表示；IP值需要加上引号；

select inet_aton('192.168.1.3');

• INET_NTOA(n)函数可以将数字n转换成IP形式。

select inet_ntoa(3232235977);

加锁函数和解锁函数

• GET_LOCK(name,time)函数定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功，则返回1；如果尝试超时，则返回0；如果遇到错误，返回NULL。

• RELEASE_LOCK(name)函数解除名称为name的锁。如果解锁成功，则返回1；如果尝试超时，返回0了如果解锁失败，返回NULL；

• IS_FREE_LOCK(name)函数判断是否已使用名为name的锁定。如果使用，返回0，否则，返回1；

select get_lock('mysql',20);

select is_free_lock('mysql',20);

select release_lock('mysql');

重复执行指定操作的函数

BENCHMARK(count.expr)函数将表达式expr重复执行count此，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度

select benchmark(10000000,now());

改变字符集的函数

CONVERT(s USING cs)函数将字符串s的字符集变成cs。

select charset(convert('abc' using gbk));

转换数据类型

* CAST(x AS type)

* CONVERT(x,type)

这两个函数只对BINARY、CHAR、DATE、DATETIME、TIME、SIGNED INTEGER、UNSIGNED INTEGER。

select cast('123' as unsigned integer)+1;

select cast(now() as date);

第九、存储过程和函数

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程来执行Sql

语句，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

区别：

1）一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。存储过程，功能强大，可以执行包括修改表等一系列数据库操作；用户定义函数不能用于执行一组修改全局数据库状态的操作。

2）对于存储过程来说可以返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类~~存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。

3）存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

创建存储过程默认contain sql

DELIMITER && 定义用&& 替代；也可以用其他的比如 delimiter %% select * from worker %%

create procedure sp_name([proc_parameter[…]]) [characteristic..] routine_body

proc_parameter===[in|out|inout] param_name type

DELIMITER &&

create procedure num_for_employee(in emp_id int,out count_num int) reads sql data begin select count(*) into count_num from employee where d_id=emp_id; end&&

创建一个简单过程

create procedure math(in a int,in b int) begin set @var1=1; set @var2=2; select @sum:=(a+b) as sum,@dif:=(a-b) as dif; end&&

调用

call math(3,4);

创建存储函数

create function sp_name ([func_parameter[……]]) return type [characteristic…] routine_body

routine_body 是sql代码可以用begin 和end处理

create function name_from_employee(emp_id int)returns varchar(20) begin return (select name from employee where num=emp_id); end&&

使用函数

select name_from_employee(1);

变量 declare 需要在存储过程，函数或者触发器中使用

@var是用户自定义变量(user defined vars)，@@var是系统变量（system vars），@@var又分成两种：一个是seesion的(local)，一个是server的(global)

变量分类

a、局部变量

declare var_name[……] type [default value];

为变量赋值

set var_name=expr,[var_name=expr,]…

或者 select column_name[..] into var_name[..] from table_name where condition

b、会话变量

设置会话变量有如下三种方式：（全局同理 session替换成global ）

set session var_name = value;

set @@session.var_name = value;

set var_name = value;

查看一个会话变量也有如下三种方式：

select @@var_name;

select @@session.var_name;

show session variables like "%var%";

c、用户变量

用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。

用户变量使用如下(这里我们无须使用declare关键字进行定义，可以直接这样使用):

select @变量名

对用户变量赋值有两种方式，一种是直接用"="号，另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用":="方式，因为在select语句中，"="号被看作是比较操作符。

d、全局变量

全局变量影响服务器整体操作。当服务器启动时，它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量，必须具有SUPER权限。全局变量作用于server的整个生命周期，但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效，需要更改相应的配置文件。

要设置一个全局变量，有如下两种方式：

set global var_name = value; //注意：此处的global不能省略。根据手册，set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION

set @@global.var_name = value; //同上

要想查看一个全局变量，有如下两种方式：

select @@global.var_name;

show global variables like "%var%";

定义条件和处理程序

declare condition_name condition for condition_value

condition_value指的是条件的类型SQLSTATE [VALUE] sqlstate_value | mysql_error_code

declare handler_type handler for condition_value[,...] sp_statement

handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO

光标的使用

--声明光标

DECLARE cur_name CURSOR FOR select_statement

--open光标

OPEN cursor_name

--FETCH 光标

FETCH cursor_name INTO var_name [,var_name....]

--close光标

CLOSE cursor_name

示例如下：

create procedure payment() begin declare id int; declare cur_payment cursor for select num from employee; declare exit handler for not found close cur_payment; set @x1=0; open cur_payment; repeat fetch cur_payment into id; if id=2 then set @x1=@x1+id; end if; until 0 end repeat; close cur_payment; end;

流程控制：

if 语句 if con then do [elseif con then …]end if

case 语句 case when x then dox [when ..then..] end case

case xx when x then dox [when ..then..] end case

loop 语句 [begin_label:] LOOP xxx end loop [end_label]

level 语句 level label

iterate 语句 iterate label 跳出本次循环类似 continue

repeat 语句 [begin_label:] LOOP xxx until xxx1 end repeat [end_label]

while 语句 [begin_label:] WHILE xxx DO xxx1 end repeat [end_label]

调用存储过程用call

call payment();

调用存储函数用select 跟调用本地函数一样

查看存储过程和函数的状态

show {procedure|function } status [like ‘pattern’]

show procedure status like 'payment' \G&&

查看创建的定义

show create {procedure|function } sp_name;

show create procedure payment \G&&

从 information_schema.Routines 表中查看存储过程

select * from Information_schema.Routines where ROUTINE_NAME='payment' \G&&

修改和创建时一样的

alter {procedure|function} sp_name ([func_parameter[……]]) return type [characteristic…] routine_body

删除

drop {procedure|function} sp_name

第十、mysql 用户管理

1、权限表

user表包含：

用户列 Host User Password

权限列 Select_priv Insert_priv

安全列 ssl_type ssl_cipher x509_issure x509_subject

资源控制列 max_questions max_updates max_connections max_user_connections

db表包含(host表)：

用户列 Host Db User 主机名数据库名用户名 host 表是db表的扩展

权限表 db表中会有create_routine_priv 和 alter_routine_priv

mysql> use mysql;

mysql> show tables;

select * from user \G;

select * from db \G;

tables_priv表进行单个表进行权限设置

COLUMNS_priv表进行单个数据列进行权限设置

desc tables_priv \G;

desc COLUMNS_priv \G;

2、用户管理

登录和退出mysql

mysql -h hostname|hostIP -P port -u username -p DatabaseName -e ‘Mysql语句’

mysql -h localhost -P 3306 -u root -p mysql -e 'select * from user'

mysql -u root -p

新建普通用户 create方式必须有create user 权限

CREATE USER user[ IDENTIFIED BY [PASSWORD] ‘password’][…]

create user 'zxc4'@'localhost' identified by '123456';

使用insert into 方式必须有insert权限同时候ssl_cipher,x509_issuer,x509_subject 必须有初始值并且要记得刷新

insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('localhost','zxc1',PASSWORD('123456'),'','','');

plush privileges;

使用grant方式必须有grant权限

grant priv_type on database.table to user [identified by ‘password’][…]..

grant select on *.* to 'zxc2'@'localhost' identified by '123456';

删除普通用户

DROP USER user[..] 或者delete 语句删除

drop user 'zxc2'@'localhost';

delete from mysql.user where host ='localhost' and user='zxcZXC';

Root用户修改自己的密码：

mysqladmin命令修改：

命令行下把mysqladmin添加到快捷方式

alias mysqladmin=/usr/local/mysql/bin/mysqladmin

检验一下

mysqladmin -u root -p ping;

输入密码后有结果 mysqld is alive

mysqladmin -u username -p password ‘’new password;

命令行下输入 mysqladmin -u root -p password 123 完成之后数据库密码就变成了123;

进入mysql 查询用户可以看到已经变化 select * from user \G;

修改数据库下面的：

update mysql.user set authentication_string=password('123456') where User='root' ;

flush privileges;

登录之后修改

set password=password('123');

Root用户修改普通用户的密码

set 语句修改普通用户密码

或者直接修改用户表的数据信息（不同mysql版本的字段不一样 password 或者authentication_string）

或者使用grant 授权方式更改

set PASSWORD for 'zxc'@'localhost'=PASSWORD('12345');

set password for 'zxc'@'localhost'=password('12345');

update mysql.user set password =password('123456') where User='zxc' and Host='localhost';

update mysql.user set authentication_string=password('123456') where User='zxc' and Host='localhost'

grant create,select,drop on *.* to 'zxc'@'localhost' identified by '123456' with grant option;

普通用户 set password=password('123');

3、权限管理

授权：grant priv_type [(column_list)] on database.table to user [identified by [password]’password’] …[with with_option[]]

grant create,select,drop on *.* to 'zxc'@'localhost' identified by '123456' with grant option;

select * from user \G;

收回授权：

revoke priv_type [(column_list)] on database.table from user ,[user]…

revoke all privileges,grant option from 'zxc'@'localhost';

show grants for 'zxc'@'localhost';

第十、数据备份与还原

1、使用mysqldump命令备份

mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

　　mysqldump基本语法：

　　mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql

　　其中：

dbname参数表示数据库的名称；

table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；

BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；

单个单表：mysqldump -u root -p mysql user >/Users/zxc/Documents/zxc/tmp/user.sql;

单个多表：mysqldump -u root -p mysql user time_zone >/Users/zxc/Documents/zxc/tmp/user.sql;

多个数据库 mysqldump -u root -p --databases mysql job >/Users/zxc/Documents/zxc/tmp/job.sql;

全部数据库：mysqldump -u root -p --all-databases>/Users/zxc/Documents/zxc/tmp/user.sql;

2、直接复制整个数据库目录

　　MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。

不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。

　　注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

3、数据库还原

　还原使用mysqldump命令备份的数据库的语法如下：

　　mysql -u root -p [dbname] < backup.sq

mysql -u root -p mysql </Users/zxc/Documents/zxc/tmp/user.sql;

4、还原直接复制目录的备份

　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。

5、表的导出和导入

select [列名] from table [where 语句] into outfile ‘目标文件’ [option];

option:

fields terminated by ‘’ 设置分隔符默认‘\t’

fields enclosed by ‘’ 设置括号等效字符

fields optionally enclosed by ‘’

fields escaped by 设置转义字符

lines starting by’’ 每行的分割字符

lines terminated by ‘’ 设置结束字符

select * from job.employee into outfield ‘/Users/zxc/Documents/zxc/tmp/employee.txt’ fields terminated by ‘\、’ fields optionally enclosed by ‘\’ lines starting by’\>’ lines terminated by ‘\r\n’

使用mysqldump

mysqldump -u root -pPassword -T 目标目录 dbname [tables] [option]

dbname参数表示数据库的名称。

tables参数表示要导出的表的名称。如果不指定，则导出数据库dbname中所有的表。

option为可选参数选项，这些选项需要结合-T选项使用。

option常见的取值如下：

--fields-terminated-by=字符串：设置字符串为各个字段之间的分隔符，可以为单个或多个字符。默认值为制表符“\t”。
--fields-enclosed-by=字符：设置字符来括住字段的值。
--fields-optionally-enclosed-by=字符：设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。
--fields-escaped-by=字符：设置转义字符，只能为单个字符。默认值为“\”。
--lines-terminated-by=字符串：设置每行数据结尾的字符，可以为单个或多个字符。默认值为“\n”。

mysqldump -u root -p -T /Users/zxc/Documents/zxc/ job employee "--fields-terminated-by=" "--fields-optionally-enclosed-by=" "--lines-terminated-by=r\n"

用mysql 语句导出文本文件

mysql -u root -p -e"select * from employee" job>/Users/zxc/Documents/zxc/employee.txt

6、导入文件

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]

fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：

terminated by分隔符：意思是以什么字符作为分隔符

enclosed by字段括起字符

escaped by转义字符

terminated by描述字段的分隔符，默认情况下是tab字符（\t）

enclosed by描述的是字段的括起字符。

escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）

load data infile '' into table student fields terminated by',' optionally enclosed by '''';

或者使用 mysqlimport 参数和mysqldump导入参数相同

mysqlimport -u root -p [options] db_name textfile1 [textfile2 ...]

十一、mysql 日志

1、二进制日志 (binary log)： log-bin 记录数据库的操作但不记录查询语句

启动和设置二进制日志

[mysqlId]

log-bin=DIR\[filename]

查看 mysqlbinlog filename.number

删除 reset master

或者根据编号purge master logs to “filename.number”

或者创建时间删除 purge master logs to “time”

使用二进制还原数据库 mysqlbinlog filename.number | mysql -u root -p 层级还原 number从小到大

暂停二进制功能 set sql_log_bin=0/1; 0 暂停 1 继续

2、错误日志(error log)： log-err 记录服务器的启动关闭和运行错误

启动和设置二进制日志

[mysqlId]

log-error=DIR\[filename]

删除 mysqladmin -u root -p flush-logs

3、通用查询日志(general query log)： log 记录用户登录和记录查询的信息

[mysqlId]

log=DIR\[filename]

删除 mysqladmin -u root -p flush-logs

4、慢查询日志: -log-slow-queries 记录执行时间超过指定时间的操作

[mysqlId]

log-slow-queries=DIR\[filename]

long_query_time=n 单位默认是秒

删除 mysqladmin -u root -p flush-logs

十二、性能优化

1、查看数据库性能

show status like ‘value’;

value包含 connections 链接服务器次数

uptime mysql 上线时间

slow_queries; 慢查询次数

com_select; com_insert;com_delete;com_update; 增删改查的操作次数

show status like 'connections';

2、优化查询

explain select 语句

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

explain列的解释

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和all

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key：实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：mysql认为必须检查的用来返回请求数据的行数

extra：关于mysql如何解析查询的额外信息

3、索引对查询速度的影响

索引可以一定程度上提升查询速度所以使用好索引可以相应提升检索速度

查询使用like 关键字的时候如果第一个字符为% 则索引不被使用如果% 不在第一个位置索引被使用

查询语句中多列索引只有查询使用索引的第一个字段时候才会被使用

使用or 如果有一个不是索引则不背使用

4、优化子查询

将字段多的表分解成多个表

增加中间表

增加冗余字段

5、优化插入记录的速度

禁用索引可以先把索引关闭再重启索引

alter table 表名 disable keys;

alter table 表名 enable keys;

禁用唯一性检查之后重启

set unique_checks=0;

set unique_checks=1;

优化insert语句

同时插入多条数据而不是一次次插入

6、分析表

analyze table 表名、[表名]

analyze table employee;

检查表

check table 表名、[表名]

check table employee;

优化表

optimize table 表名、[表名]

optimize table employee;

十三、javaweb链接数据库

加载JDBC驱动 Class.forName(“com.mysql.jdbc.Driver);//加载JDBC驱动

提供链接数据库URL String URL = "jdbc:mysql://localhost:3306/job"

创建数据库链接 Connection connection = DriverManager.getConnection(URL,"root","123456")

创建执行对象 Statement statement = connection.createStatement();

执行数据库操作 ResultSet result = statement.executeQuery(sql);

处理结果集 while(result.next()) {System.out.println( result.getString(1)) }

关闭资源 result.close() ; statement.close() ; connection.close() ;

posted on 2016-12-20 10:42 成己成物kukum 阅读(375) 评论(0) 编辑收藏举报

刷新页面返回顶部

数据库基础学习笔记----从0-1

导航

公告