MYSQL总结

Mysql总结

1.检索数据

select distinct id from products 消除相同的id 只取唯一的id

limit 限制结果

2.排序检索

order by asc 升序 默认方式 desc 降序

select id from products order by price desc

select price from products order by price desc limit 1 加上limit作用

3.过滤数据 （where） 子句

范围 = <> != < <= > >= between

空值检查

select name from products where price is null;

组合where子句 数据过滤

select id from products where id = 1003 and price <= 10;

select id from products where id = 1003 or prod_price <= 10;

select name from products where id in (1002, 1003) order by prod_name;

in 优点操作符

1.in操作服务清楚和直观

2.in,计算机更容易管理

3.in操作符比or操作符执行更快

4.in包含select子句

通配符 ‘ ’ 空格也是匹配内容

like操作符 检查未知和不明确的用处 不能匹配整个列值如 ‘1000’

select * from products where name like ‘%anvil%’ 百分号通配符；

Null 不能匹配 %通配符

_下划线通配符

selecct id from products where name like ‘_ ton anvil’;

4.正则表达式 匹配特殊的字符串

匹配对象字符 regexp ‘1000’

select name from products where name regexp ‘1000|2000’
匹配指定字符 select name from products where name regexp ‘[123] Ton’
//匹配开头为1 或2 或3
匹配集合 [1-3] [1-9] [a-z]
匹配特殊字符 [] | . 需要在前面加上\表示查找
select name from products where name regexp \.
//匹配/需要 ///

匹配字符类（预定义的字符类）
[:alnum:] 任意字母和数字 【a-zA-Z0-9】
[:alpha:] 任意字符 【a-zA-Z】
[:blank:] 空格和制表 [\t]
[:cntrl:] ASCII字符
[:digit:] 任意数字
[:print:] 任意可打印字符
[:graph:] 与[:print:] 不包括空格
[:lower:] 任意小写字母
[:upper:] 任意大写字母
[:punct:] 既不在[:alnum]又不在[:cntrl:] 中任意字符
[:space:] 包括空格在内的任意空白字符[\f换页\n换行\r回车\t制表\纵向制表]
select name from products where name regexp ‘[[:digit:]]{4}’

匹配不区分大小写
使用BINARY关键字 where name regexp binary ‘JetPack .000’ .就是_通配符
元字符
* 0以上匹配 zo*
+ 1以上匹配 zo+
? 0-1匹配
{n} 指定数目的匹配 o{2}
{n,} 不能少于n
{n, m} n到m匹配

正则表达式 \w \s \d \b
. 匹配除换行符以外的任意字符
\w 匹配字母或数字或下划线或汉字 等价于 ‘[^A-Za-z0-9_]’。
\s 匹配任意的空白符
\d 匹配数字
\b 匹配单词的开始或结束
^ 匹配字符串的开始
$ 匹配字符串的结束
\w能不能匹配汉字要视你的操作系统和你的应用环境而定

6.创建计算字段

mySQL 使用concat()函数实现连接 拼接字段
select Concat(name, ‘(‘, country, ‘)’) from vendors;
数据： ACME (USA) Furball Inc. (USA)

删除函数 RTrim() LTrim()
select Concat(RTrim(name), ‘(‘, RTrim(country), ‘)’)

使用别名
select Concat(RTrim(name), ‘(‘, RTrim(country), ‘)’) As vend_title
from vendors;

算术
select id, quantity, quantity*price As expanded_price from orderitems

使用数据处理函数 添加代码注释
文本处理函数 upeer 大写转换
select name Upeer(name) As name_upcase from vendors;

left(str, length) 返回串左边的字符
select LEFT(‘foobarbar’, 5) ->’fooba’

locate(substr, str) 返回子串的位置

select LOCATE(‘bar’, ‘foobarbar’) 4

lower 小写转换

length 串长度

subString(str, pos, len)

select SUBSTRING(‘QUADRAtically’, 5, 6);

Soundex() 通过发音查询

select name, cust_contact form customers where soundex(cust_contact) = Soundex(‘Y Lie’);

返回结果 Coyote Inc Y Lee;

日期函数

AddDate() 增加一个日期

AddTime() 增加一个时间

CurDate() 返回当前日期

CurTime() 返回当前时间

Date() 返回日期时间的日期部分

DateDiff() 计算两个日期之差

Date_Add() 高度灵活的日期运算函数

Date_Format() 返回一个格式化的日期或时间串

Day() 返回一个日期的天数部分

DayOfWeek() 对于一个日期，返回对象的星期几

Hour() 返回一个时间的小时部分

Minute() 返回一个时间的分钟部分

Month() 返回一个日期的月份部分

Now() 返回当前日期和时间

Second() 返回一个时间的秒部分

Time() 返回一个日期时间的时间部分

Year() 返回一个日期的年份部分

select id, num from orders where order_date = ‘2005-09-01’; 比值不光日期还有时间 00-00-00

select id from order where Date(order_date) = ‘2005-09-01’;

检索2005年9月下所有订单

select id from order where Date(order_date) Between ‘2005-09-01’ and ‘2005-09-30’

select id from order where Year(order_date) = 2005 and Month(order_date) = 9;

数值处理函数

Abs() 绝对值

Mod() 指数

Sqrt() 平方根

7.汇总数据

聚集函数 5

AVG() 返回某列的平均值 忽略行为Null行

select AVG(prod_price) as avg_price from products

Count() 函数 确定表中行的数目和符合特定条件的行的数目

select COUNT(*) as num_cust from customers

Max 函数 指定列明最大的值

select ma(prod_price) as max_price from products

min 函数 最小值

sum 指定列值的和

DISTINCT不同的价格

select avg(DISTINCT prod_price) as avg_price from products where id = 1003;

聚集函数用来汇总弄数据

7.分组数据

select id, count(*) as num_prods from products group by id;

规定：

1.group by子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致控制。

2.如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。

意思是在建立分组是，指定的所有列都一起计算

3.group by子句中列出的每个列都必须是检索列或有效的表达式（不能是聚集函数）不能使用别名。

4.除去聚集计算语句外，select语句中的每个列都必须在group by 子句中给出。

5.如果分组列中具有NULL值，则Null将作为一个分组返回。如果列中有多行NULL值，特闷将分为一组。

6.Group by子句必须出现在where子句之后， order by 子句之前

ROllup WITH Rollup 对每个分组以及一个分组汇总级别的值

select id, count(*) as num_prods form products group by vend_id with rollup

http://blog.itpub.net/29773961/viewspace-1255419/ 详细的解释说明

过滤分组 having

select cust_id, count() as orders from orsers group by cust_id having count() >= 2;

Having和where的差别

where在数据分组前进行过滤 , Having在数据分组后进行过滤

where 可能会影响having的值

理解题：列出具有2个以上，，杰哥为10以上的产品供应商

select id count() as num_prods from products where price >= 10 group by id having count( ) >= 2;

group by 分组根据后面的id

order by 分组可以改写group by 分组数据 97 mysql必知必会

select 顺序：

select from where group by having order by limit

8.子查询

9.联结表

分解数据为多个表能更有效第存储，更方便地处理，有更大的可伸缩性

内连接inner join 用on 来代替where

10.高级联结

1.使用表别名 缩短联结字符

2.使用不同类型的联结

1）自联结代替子查询

select id, name from products where id =(select id from products where id=’DTNTR’)

select p1.prod, p1.prod_name from products as p1, products as p2

where p1.vend_id = p2.id and p2.id = ‘DTNTR’;

2)自然联结

select c.*, o.id o.date from customers as c, orser as o where c.id = o.id;

自然联结去除标准联结的相同的列多次出现

3）外部联结

左外部联结和右外部联结 区别？ 内连接区别？笛卡尔积？

4）带聚集函数的联结

11.组合查询

使用组合查询的情况：

1.在单个查询中从不同的表返回类似结构的数据。

2.在单个表执行多个查询，按单个查询返回数据

规则：

1.union必须由两条会两条以上的select语句组成，语句之间有union分隔。

2.unoin中每个查询必须包含相同的列，表达式或聚集函数

3.列数据类型必须兼容

union all 代替 union相除相同的行

union组合查询只能使用一个order by 子句

12.全文本搜索

MYISAM支持全文本搜索

INNODB不知全文本搜索

通配符搜索限制：

1.性能–通配符和正则表达式匹配通常要求MYsql尝试匹配表中所有行。

2.明确控制 –通配符和正则表达式匹配,很难明确地控制匹配什么和不匹配什么。

3.智能化的结果–虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但他们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含盖茨的所有行，而不区分包含单个匹配的行和包含多个匹配的行。

限制通过全文本搜索来解决。

启用全文本搜索

create talbe producenotes

{

​ note_id int not null auto_increment,

​ prod_id char(10) not null,

​ note_date datetime not null,

​ note_text text null,

​ primary key(note_id),

​ fulltext(note_text)

} engine = MYISAM

select note_text from productnotes where Match(note_text) against(‘rabbit’);

全文本搜索的优点是有顺序

查询扩展

select note_text from productnotes where Match(note_text) Against(‘anvils’ with query expansion) 返回与anvils一切相关联的东西

布尔文本搜索

select note_text from productnotes where Match(note_text) Against(‘heavy’ in boolean mode)

全文本布尔操作符：

+ 包含，词必须存在

- 排除，次必须不出现

> 包含，而且增加等级值

<包含, 且减少等级值

() 把词组成字表达式（允许这些子表达式作为一个组被包含，排除，排列）

~ 取消一个词的排序值

* 词尾的通配符

“” 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

select note_text from productnotes where match(note_text) against(‘+rabbit +bait’ in boolean mode);

这个搜索匹配包含词rabbit 和bait的行

select note_text from productnotes where Match(note_text) against(‘rabbit bait’ in boolean mode)

这个搜索匹配包含rabbit和bait中的至少一个词的行

select note_text from productnotes where Match(note_text) against(‘“rabbit bait”’ in boolean mode)

这个搜索匹配整个词 rabbit bait

select note_text from productnotes where Match(note_text) Against(‘+safe +(

13.插入数据

两种方式

insert into customers values(null, ‘Pepp E. LaPew’, ‘100 Main Street’, ‘Los angeles’, ‘ca’);

null 为自增的id

insert into customers(cust_name, cust_address, cust_city, cust_state, cust_zip)

values(‘Pep E. laPEw’)

省略列:

1.定义为允许的null值

2.在表定义中给出默认值。

提高整体性能，在数据检索select时，可以通过在insert和into之间添加关键字LOW_PRIORITY

values(), ()来添加多个数据提高insert的性能。

14.更新数据

update 由3部分组成 更新的表 列明和他们的新值 确定的过滤条件

update customers set cust_email = ‘elmer@fudd.com’ where cust_id = 10005;

update customers set cust_email = ‘elmer@fudd.com’, cust_name = ‘The Fudds’ where cust_id = 10005;

IGNORE 忽视错误

update ignore customers

更新和删除原则：

1.必带where子句

2.保证每个表都有主键，尽可能像where子句那样使用它

15.创建表和操纵表

create table customers

(

​ cust_id int not null auto_increment,

​ cust_name char(50) not null,

​ primary key (cust_id)

)

主键两个有什么意义

select last_insert_id() 返回增加的订单

default:

quantity int not null default 1;

更新表

alter table vendors add vend_phone char(20);

定义外键：

1.alter table orderitems add constraint fk_orseritems_orders

foreign key (order_num) references orders (order_num);

2.alter table orderitems add constraint fk_orderitems_products

foreign key (prod_id) references products (prod_id);

3.alter table orders add constraint fk_orders_customers

foreign key (cust_id) references customer(cust_id)

4.alter table products add constraint fk_products_vendors

foreign key(vend_id) reference vendors (vend+id);

删除表

drop table customers2;

重名表

rename table customers to customers;

使用视图

为什么使用：

1.重用sql语句

2.简化复杂的sql操作。

3.使用表的组成而不是整个表

4.保存数据。可以给用户授予表的特定部分的访问权限而不是真个表的访问权限。

5.更改数据格式和表示。

视图的规则和限制：

1.视图必须唯一命名。

2.视图数目没有限制

3.为了创建视图，必须具有足够的访问权限。

4.视图可以嵌套。

5.order by 可以用在视图中

6.视图不能索引，也不能有关联的触发器或默认值。

7，视图可以和表一起使用。

使用视图

1.用create view语句创建

2.使用show create view viewname;来查看创建视图的语句

3.使用drop删除视图 drop view viewname;

4.更新视图： 1）drop view create view 2)create or replace view;

create view productcustomers as

select cust_name, cust_contact, prod_id from customers, orders, orseritems

where customers.cust_id = orsers.cust_id and orseritems.order_num = orders.order_num;

视图的使用就是各种select组成的结果。

使用存储过程

定义：就是为以后的使用而保存的一条或多条mysql语句的集合。

为什么使用：

1.通过吧处理封装在容易使用的单元中，简化负责的操作；

2.由于不要求反复简历一系列处理不走，保证了数据的完整性。

3.提高性能。 简单，安全，高性能

执行存储过程：

call productpricing(@pricelow, @pricehigh, @priceaverage)

执行名为productpricing的存储过程，它计算并返回产品的最低，最高和平均价格。

创建存储过程

一个返回产品平均价格的存储过程：

create procedure productpricing()

begin

​ select avg(prod_price) as priceaverage

​ from products

end;

mysql命令行 delimiter 高速命令行使用程序使用//来代替 ;

删除存储过程

drop procedure productpricing; 没有（）

dorp proceduer if exists 删除是否不存在存储过程

使用参数

create procedure ordertotal(

​ in onumvber int,

​ out ototal decimal(8, 2)

)

begin

​ select sum(item_price * quantity)

​ from orderitems

​ where order_num = onumber

​ into ototal //插入参数

end

mysql支持in（传递给存储过程） out(从存储过程传出)

call odertotal(20005, @total) 使用存储过程

select @total; 读出结果

建立智能存储过程

–Name： ordertotal

–Parameters :onumber = order number

– taxable = 0 if not taxable , 1 if taxable

– ototal = order total varible

create procedure ordertotal(

​ in onumber int,

​ in taxable boolean,

​ out ototal decimal(8, 2)

)

– declare variable fort total

declare total decimal(8, 2)

declare taxrate int default 6;

select sum(item_price*quantity) from orderitems

where order_num = onumber into total;

if taxable then

​ select total+(total/100*taxrate) into total;

end if;

​ select total into ototal;

end;

call ordertotal(20005, 0, @total);

select @total;

检查存储过程

show create procedure ordertotal;

使用游标

是一个语句检索出来的结果集。 智能用于存储过程（函数）

游标用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。

使用游标步骤：

1.在使用游标前，声明它。

2.一旦声明，必须打开游标以供使用。

3.对于填有数据的游标，根据需要取出（检索）各行

4.结束游标使用，关闭游标

创建游标

create proceduer processorders()

begin

​ declare ordernumbers cursor

​ for

​ select order_num from orders;

end;

open ordernumbers; //打开游标

close ordernumbers; //关闭游标

使用游标数据

create proceduer processorders()

begin

​ declare o int;

​ declare ordernumber cursor

​ for

​ select order_num from orders;

​ open ordernumbers;

​ fetch orsernumber into o;

​ close orsernumber;

end;

只检索数据就一行

循环检索数据，从第一行到最后一行

create proceduer processorders()

begin

​ declare done boolean default 0;

​ declare o int;

​ declare ordernumbers cursor

​ for

​ select order_num from orders;

​ declare continue handle for sqlstate ‘02000’ set done = 1;

​ open ordernumbers;

​ repeat

​ fetch ordernumbers into o;

​ untile done end repeat;

​ close ordernumber;

end;

sqlstate状态码； http://blog.csdn.net/cangyingaoyou/article/details/7402243;

总体运用：

create procedure processorders()

begin

​ declare done boolean default 0;

​ declare o int;

​ declare t decimal(8, 2);

​ declare ordernumber cursor;

​ for

​ select order_num from orders;

​ declare continue handle for sqlstate ‘02000’ set done = 1;

​ create table if not exists orsertotals

​ open orsernumbers;

​ repeat

​ fetch ordernumbers into 0;

​ call ordertotal(o, 1, t);

​ insert into ordertotals(orser_num, total) values(o, t);

​ until done end repeat;

​ close ordernumbers;

end;

使用触发器

想要某条语句，某些语句在时间发生时自动执行

触发器是mysql响应以下任意语句而自动执行的一条mysql语句

创建触发器 4条信息：

1.唯一的触发器名；

2.触发器关联的表；

3.触发器应该响应的活动

4.触发器何时执行。

只有表才支持触发器，视图不支持（临时表也不支持）

触发器按每个表每个时间每次地定义，每个表每个时间每次值允许一个触发器。

每个表最多支持6个触发器单一触发器不能与多个时间或多个表关联。所以，

如果你需要一个队insert和update操作执行的除法器，应定义两个触发器

删除触发器

drop trigger newproduct;

使用触发器

1.引用New的虚拟表，放完被插入的行

2.在before insert触发器中, new中的值也可以被更新

3.对于auto_increment列， new在insert执行之前包含0，在insert执行之后包含新的自动生成值

create trigger neworder after insert on orders for each row select New.order_num;

insert into order(orser_date, cust_id) values(Now(), 10001);

返回order_Num 20010

before after ？ before用于数据验证和净化（母的是保证插入表中的数据确实是需要的数据）

delete触发器

create trigger delteorder before delete on orders for each row

begin

​ insert into archive_orders(order_num, order_date, cust_id)

​ values(old.ordernum, old.order_date, ole.cust_id)

end;

update触发器

create trigger update evendor beofer update on vendors

for each row set new.vend_state = Upper(New.vend_state)

26.管理事务处理

目的：维护数据库的完整性。

含义：一种机制，用来管理必须成批执行的mysql操作，以保证数据库不包含不完整的操作结果。

事务的术语：

1.事务(transaction) 一组sql语句

2.回退（rollback） 撤销指定sql语句的过程

3.提交（commit）将为存储的sql语句结果写入数据库表

4.保留点（savepoint） 指事务处理中设置的临时占位符，你可以对它发布回退。

start transaction 标志事务的开始

使用rollback

select * from ordertotals;

start transaction;

delete from ordertotals;

select * fro ordertotals;

rollback; //回退到start transaction之后的所有语句

select * from ordertotals;

rolleback只能在一个事务处理内使用。管理insert, update, delete语句，不能回退select语句

使用commit

一般的mysql语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交，即提交操作是自动进行的。

事务处理块中，提交不会隐含的进行。为进行明确的提交，使用commit语句

start transaction;

delete from orderitems where order_num = 20010;

delete from orders where order_num = 20010;

commit;

隐含事务关闭 当commit或rollback语句执行后，事务会自动关闭。

使用保留点

savepoint deletel;

rollback to deletel; 通过commit / rollback / release savepoint 来释放保留点

更改默认的提交认为

set autocommit = 0; ?

//autocommit标志决定是否自动提交更改，不管有没有commit语句

全球化和本地化

字符集：字母和符号的集合

编码：为某个字符集成员的内部表示；

校对：为规定字符如何比较的指令

show character set; 或 show variables like ‘character%’

显示所有可用的字符集一级每个字符集的描述和默认校对；

show collation; 或 show variables like ‘collaction%’

查看校对的完整列表，一级它们使用的字符集

create table mytable

(

​ columnn1 int,

​ columnn2 varchar(10) character set latin1 collate

) default character set hebrew

collate hebrew_general_ci

select * from customers

order by lastname, firstname collate latinal_general_cs;

20安全管理

创建用户账号

create user ben identified by password ‘P@fdsag’

重命名账号

rename user ben to bforta;

删除账号

drop user bforta;

设置访问权限

show grants for bforta;

grant select on crashcourst.* to bforta crshcourst为数据库

revoke select on crashcourst.* from bforta;

具体权限zai 212

更改口令

set password for bforta = password(‘fdsags’);

29.数据库维护

1.使用命令行使用程序mysqldump转储所有数据库内容到某个外部文件。

2.使用命令行使用程序mysqlhotcopy从一个数据库赋值所有数据（看引擎，有些不支持）

3.使用mysql的backup table 或select into ooutfile转储所有数据到某个外部文件。数据可以用restore table 来复原

数据库维护

analyze talbe 检查表键是否正确

check table对许多问题对表进行检查 MYiSAM

日志

1.错误日志 包含启动和关闭问题，任意关键错误的细节。名为hostname.err 位于date目录 操作log-error;

2.查询日志 记录所有mysql活动 hostname.log 位于date目录 操作 –log

3.二进制日志 更新过数据的所有语句 hostname-bin date目录 –log-bin

4.缓慢查询日志。 执行缓慢的任何查询 hostname-slow.log –log-slow-queries;

flush logs语句来刷新和重新开始所有日志文件

30.改善性能

1.mysql具有特定的硬件建议。

2.mysql预先配置，查看当前配置 show varialbes和show status;

3.mysql一个多用户多线程dbms,同事执行多个任务。

通过show processlist显示所有活动进程，ill命令终结某个特定的进程

4.找出最佳的select语句

5.使用explain语句让mysql解释它将如何执行一条select语句。

6.存储过程执行地比一条一条执行其中的各条mysql语句块。

7.使用正确的数据类型。

8.绝不要检索比需求还要多的数据

9.有的操作（insert）支持一个可选的delayed关键字。 ？

10.在导入数据时，应该关闭自动提交。还想删除索引，

11.必须索引数据库表已改善数据检索的性能。

12.通过union连接多个select语句 来改善 or条件

13.索引改善数据检索的性能，伤害插入，删除和更新的性能

14.like很慢。最好使用fulltext而不是like

15.了解理想的优化和配置，经常的优化它们