首页 > 代码库 > mysql用法精华集锦
mysql用法精华集锦
1. 环境：windows，MySQL Server 5.5，Navicat forMySQL
2. Mysql常用sql语句
SQL分类：
DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）
DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）
首先介绍基础语句：
2.1创建数据库
CREATE DATABASE database-name
2.2删除数据库
drop database dbname
2.3备份sql server
---创建备份数据的device
USE master EXEC sp_addumpdevice ‘disk’,’testBack’,’c:mssql7backupMyNwind_1.dat’
---开始备份
BACKUP DATABASE pubs TO testBack
2.4创建新表
create table tabname(col1 type1[notnull][primary key],col2 type2[not null])
根据已有的表建立新表
2.4A:create table tab_new liketab_old（使用旧表创建新表）
2.4B:create table tab_new as selectcol1,col2…from tab_old definition only
2.5删除表
Drop table tabname
2.6为表添加一列
Alter table tabname add column coltype 注：列增加后将不能删除
2.7添加主键/删除主键
Alter table tabname add primarykey(col)
Alter table tabname drop primarykey(col)
2.8添加索引/删除索引
Create [unique] index idxname ontabname(col…)
drop index idxname
2.9 创建视图/删除视图
create view viewname as select statement
drop view viewname
2.10常用的基本sql语句
查找：select * fromtable1 where 范围
插入：insert intotable1(field1,field2) values(value1,value2)
删除：delete fromtable1 where 范围
更新：update table1set field1=value1 where 范围
模糊查询：select *from table1 where field1 like ‘%value1%’
排序：select * fromtable1 order by field1,field2[desc]
总数：selectcount(*) as totalcount from table1
求和：selectsun(field1) as sunvalue from table1
平均：selectavg(field1) as avgvalue from table1
最大：selectmax(field1) as maxvalue from table1
最小：selectmin(field1) as minvalue from table1
2.11几个常用高级查询运算符
2.11A：UNION运算符
UNION运算符通过组合其他两个结果表（例如table1和table2）并消去表中任何重复行而派生出一个结果表。当ALL与UNION一起使用时（即UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自table1就是table2。
2.11B：EXCEPT运算符
EXCEPT运算符通过包括所有在table1中但不再table2中的行并消除所有重复行而派生出的一个结果表。当ALL随EXCEPT一起使用时（EXCEPT ALL），不消除重复行。
2.11C：INTERSECT运算符
INTERSECT运算符通过至包括table1和table2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时（INTERSECT ALL），不消除重复行。
2.12使用外连接
2.12A：left outer join
左外连接：结果集包括连接表的匹配行，也包括做连接表的所有行
SQL：select a.a,a.b,a.c,b.c,b.d,d.f from a LEFT OUT JOIN b ON a.a=b.c
2.12B：right outer join
右外连接：结果集既包括连接表的匹配行，也包括右连接表的所有行
1.12C：full outer join
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表的所有行
3. mysql子查询
3.1mysql子查询语法与用法实例
子查询是将一个 SELECT 语句的查询结果作为中间结果，供另一个SQL 语句调用。MySQL支持SQL标准要求的所有子查询格式和操作，也扩展了特有的几种特性。
示例：select* from article where uid in(select uid from user where status=1)
3.2mysql标量子查询
标量子查询是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。
示例：select* from article where uid=(select uid from user where status=1 order by uid desclimit 1)
3.3mysql列子查询
列子查询是指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。
示例：select *from article where uid in(select uid from user where status=1)
3.4mysql行子查询
行子查询是指子查询返回的结果集是一行N列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。
示例：select* from table1 where (1,2)=(select col1,col2 from table)
3.5mysql表子查询
表子查询是指子查询返回的结果集是N行N 列的一个表数据。
示例：select *from article where (title,content,uid) in (select title, content, uid fromblog)
3.6mysql from子查询
MySQL FROM 子查询是指 FROM 的子句作为子查询语句，主查询再到子查询结果中获取需要的数据
语法：select …from(subquery) as name…
3.7mysql exists和notexists子查询
语法：select …from table where exists(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
示例：select *from article where exists(select * from user where article.uid=user.uid)
3.8mysql关联子查询
关联子查询是指一个包含对表的引用的子查询，该表也显示在外部查询中。通俗一点来讲，就是子查询引用到了主查询的数据数据
示例：selectarticle.* from article inner join user on article.uid=user.uid
4. mysql循环
4.1while…end while循环
Create procedure p1()
begin
declare v int;
set v=0;
while v<5 do
insertinto t values(v)
setv=v+1;
end while;
end;
4.2repeat…end repeat循环
create procedure p2()
begin
declare v int;
set v=0;
repeat
insert into t values(v);
set v=v+1;
until v>=5;
end repeat;
end;
注：until后面可以没有分号
4.3loop…end loop循环
create procedure p3()
begin
declare v int;
set v=0;
loop_label:loop
insert into tvalues(v);
set v=v+1;
if v>=5 then
leaveloop_label;
end if;
end loop;
end;
5. mysql视图查询
5.1视图定义
视图是由查询结果形成的一张虚拟表。
5.2视图的使用
如果某个查询结果出现的非常频繁，也就是要经常拿这个查询结果来做子查询
5.3语法
create view 视图名 as select 语句
5.4视图优点
l 简化查询语句
示例：有一张商品表，我们要经常查每个栏目下的商品的平均价格
Select cat_id, avg(shop_price) from goods group by cat_id;
创建一个视图
create view avgPrice as select cat_id, avg(shop_price) fromgoods group by cat_id;
当我们再次查询每个栏目平均价格时，只需要这么写
select * from avgPrice;
l 进行权限控制
把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据列，例如我们的goods商品表，我们不想让别人看到我们的销售价格，这个时候我们可以把商品表权限封闭，创建一张视图
create view showGoods as select goods_id,goods_name fromgoods;
5.5视图修改
Alter view 视图名 as select 语句;
5.6视图与表的关系
视图是表的查询结果，自然表的数据变了，会影响视图的结果
6. mysql关联查询
6.1连接查询简介
连接查询中用来连接两个表的条件称为连接条件或连接谓词。其形式为：
[<表1>].<列名><连接运算符>[<表2>].<列2>
常见连接运算符包括
l 比较运算符：=、>、<、>=、<=、!=、between和and
l 逻辑运算符：not、and、or
6.2连接按照结果集分类
l 内连接：表中的行互相连接，结果集的行数等于每个表满足条件的行数乘积，参与连接的表示平等的。
l 外连接：参与连接的表有主次之分，主表的每一行数据去匹配从表的数据列，符合连接条件的数据直接返回到结果集中，不符合连接条件的的数据列将以null填充后返回到结果集中，其中外连接又分为左外连接，右外连接和全连接。
6.3内连接查询
内连接语法结构:
Select <属性或表达式列表> from <表名> [inner]join <表名> on <连接条件> [where <限定条件>]
Inner可以省略，当只见到join时就是省略掉了inner。内连接就是传统的连接操作，这里用on子句指定连接条件，用where指定其他限定条件,
示例：select p.name,c.countryname from country as c inner join person p onp.countryid=c.countryid
6.4左外连接查询
左外连接语法结构：
Select <属性或表达式列表> from <表名> leftouter join <表名> on <连接条件> [where <限定条件>]
左外连接在结果表中包含第一个表中满足条件的所有记录，如果连接条件匹配，第二张表返回相应的值，否则返回null。也就是说不管第二个表有没有记录，第一张表的字段都会返回。
示例：select p.name,c.countryname from country as c right join person p onp.countryid=c.countryid
6.5右外连接查询
右外连接查询语法结构：
Select <属性或表达式列表> from <表名> rightouter join <表名> on <连接条件> [where <限定条件>]
右外连接的结果表中包含第二张表中满足条件的所有记录，如果连接条件匹配，第一张表返回相应的值，否则返回null。
示例：select p.name, c.countryname from country as c right join person pon p.countryid=c.countryid
6.6全外连接查询
全外连接查询语法结构：
Select <属性或表达式列表> from <表名> fullouter join <表名> on <连接条件> [where <限定条件>]
全外连接查询的结果集表中包含两个表满足记录所有记录。如果在连接上条件上匹配的元组，则另一个表返回相应的值，否则返回null
mysql用法精华集锦