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