mysql sqlbuckcopy_一些常用的mysql语句收集

一些常用的mysql语句收集，我们使用SQL查询不能只使用很简单、最基础的SELECT语句查询。如果想从多个表查询比较复杂的信息，就会使用高级查询实现。常见的高级查询包括多表连接查询、内连接查询、外连接查询与组合查询等。

1、rand()方法随机查询

select * from emp order by rand() limit 2;

2、子查询(无关子查询)

select * from info where nation =(select Code from nation where name='汉族') //两个查询 一个查询的结果当做另一个查询的条件 查一个 =

select * from info where nation in (select Code from Nation where Name='汉族' or Name='苗族')，in(在里面)not in (在不里面)任意一个都可以 作为两个查询结果的链接 查两个in

3、子查询(相关子查询)：子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。

select * from car a where a.oil

select * from (

select id, name from student where sex = 1

) t where t.id > 2;

4、all：查询所有 ，distinct ：过滤重复

selectall sex from student;

select distinct sex from student;

select count(distinct sex) from student;

5、top 取前N条记录

select top 3 * from student;

6、having 分组过滤条件，从group by筛选出来的结果中再进行条件筛选

按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息

select count(*), age from student group by agehaving age is not null;

按照年龄和cid组合分组，过滤条件是cid大于1的记录

select count(*), cid, sex from student group by cid, sexhaving cid > 1;

按照年龄分组，过滤条件是分组后的记录条数大于等于2

select count(*), age from student group by agehaving count(age) >= 2;

按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2

select count(*), cid, sex from student group by cid, sexhaving cid > 1 and max(cid) > 2;

7、some、any、all子句查询示例

查询班级的学生年龄大于班级的学生的年龄的信息

select * from student where cid = 5 and age > all(

select age from student where cid = 3

);

select * from student where cid = 5 and age > any(

select age from student where cid = 3

);

select * from student where cid = 5 and age > some(

select age from student where cid = 3

)

8、创建数据库

CREATE DATABASE IF NOT EXISTS `test123` DEFAULT CHARACTER SET utf8;//创建数据库

USE `test123`; //切换数据库

DROP TABLE IF EXISTS `trest`; //删除数据库

CREATE TABLE `trest`(

`username` varchar(100) DEFAULT NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8; //创建表

9、用户管理

1、新建用户：

>CREATE USER copylian IDENTIFIED BY 'ssapdrow';

2、更改密码：

>SET PASSWORD FOR copylian=PASSWORD('copylian123');

3、权限管理

>SHOW GRANTS FOR name;    //查看name用户权限

>GRANT SELECT ON db_name.* TO name;　　　　//给name用户db_name数据库的所有权限

>REVOKE SELECT ON db_name.* TO name;　　　　//GRANT的反操作，去除权限；

10、插入检索出来的数据

INSERT INTO tb_name(name,score) SELECT name,score FROM tb_name2;

11、MySQL的正则表达式

SELECT * FROM tb_name WHERE name REGEXP '^[A-D]'   //找出以A-D 为开头的name

12、全文检索——MATCH和AGAINST

1、SELECT MATCH(note_text),AGAINST('PICASO') FROM tb_name;

2、InnoDB引擎不支持全文检索，MyISAM可以；

13、视图

1、创建视图

>CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;

2、视图的特殊作用：

a、简化表之间的联结(把联结写在select中)；

b、重新格式化输出检索的数据(TRIM，CONCAT等函数)；

c、过滤不想要的数据(select部分)

d、使用视图计算字段值，如汇总这样的值。

14、使用存储过程：

个人理解，存储过程就是一个自定义函数，有局部变量参数，可传入参数，可以返回值，不过这语法够呆滞的~~~

1、创建存储过程：

>CREATE PROCEDURE pro(

>IN num INT,OUT total INT)

>BEGIN

>SELECT SUM(score) INTO total FROM tb_name WHERE id=num;

>END;

***这里的  IN (传递一个值给存储过程)，OUT(从存储过程传出一个值)，INOUT(对存储过程传入、传出)，INTO(保存变量)

2、调用存储过程：

>CALL pro(13,@total)　　　　　　//这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错

>SELECT @total　　　　　　　　　//这里就可以看到结果了；

3、存储过程的其他操作：

>SHOW PROCEDURE STATUS;　　　　　　//显示当期的存储过程

>DROP PROCEDURE pro;　　　　　　　　　//删除指定存储过程

15、使用游标：

对这个理解不是很懂，朋友多多指点哦~~~

1、游标的操作

>CREATE PROCEDURE pro()

>BEGIN

>DECLARE ordername CURSOR FOR

>SELECT order_num FROM orders;

>END;

>OPEN ordername;　　　　//打开游标

>CLOSE ordername;　　　　//关闭游标

16、触发器：

触发器是指在进行某项指定操作时，触发触发器内指定的操作；

1、支持触发器的语句有DELETE、INSERT、UPDATE,其他均不支持

2、创建触发器：

>CREATE TRIGGER trig AFTER INSERT ON ORDERS FOR EACH ROW SELECT NEW.orser_name;

>INSERT语句，触发语句，返回一个值

3、删除触发器

>DROP TRIGGER trig;

17、语法整理：

1、ALTER TABLE(修改表)

ALTER TABLE table_name

(　　ADD　　　　column　　datatype  　　[ NULL | NOT NULL ]　　[ CONSTRAINTS ]

CHANGE　　column 　　datatype 　　COLUMNS　　[ NULL | NOT NULL ]　　 [ CONSTRAINTS ]

DROP　　　 column，

。。。。

)

2、COMMIT(处理事务)

>COMMIT;

3、CREATE INDEX(在一个或多个列上创建索引)

CREATE INDEX index_name ON tb_name (column [ ASC | DESC ] , .......);

4、CREATE PROCEDURE (创建存储过程)

CREATE PROCEDURE pro([ parameters ])

BEGIN

........

END

5、CREATE TABLE(创建表)

CREATE TABLE tb_name(

column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,

column_name　　datetype　　[ NULL | NOT NULL ] 　　[ condtraints]   ,

.......

PRIMARY KEY( column_name )

)ENGINE=[  InnoDB | MyiSAM ]DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

6、CREATE USER(创建用户)

CREATE USER user_name [ @hostname ] [ IDENTIFIED BY [ PASSWORD ] 'pass_word' ];

7、CREATE VIEW (在一个或多个表上创建视图)

CREATE [ OR REPLACE ] VIEW view_name AS SELECT。。。。。。

8、DELETE (从表中删除一行或多行)

DELETE FROM table_name [WHERE ......]

9、DROP(永久删除数据库及对象，如视图、索引等)

DROP DATEBASE | INDEX | PROCEDURE | TABLE | TRIGGER | USER | VIEW  name

10、INSERT (给表添加行)

INSERT INTO tb_name [ ( columns,...... ) ]  VALUES(value1,............);

使用SELECT值插入：

INSERT INTO tb_name [ ( columns,...... ) ]

SELECT columns , .......   FROM tb_name [ WHERE ...... ] ;

11、ROLLBACK(撤销一个事务处理块)

ROLLBACK [  TO  savapointname  ];

12、SAVEPOINT(为ROLLBACK设置保留点)

SAVEPOINT sp1;

13、SELECT (检索数据，显示信息)

SELECT column_name,.....FROM tb_name  [ WHERE ]   [ UNION ]    [ RROUP BY ]   [ HAVING ]   [ ORDER BY ]

14、START TRANSACTION (一个新的事务处理块的开始)

START TRANSACTION

15、UPDATE(更新一个表中的一行或多行)

UPDATE tb_name SET column=value,......[ where ]

18、备份sql server

--- 创建 备份数据的 device

USE master

EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'

--- 开始 备份

BACKUP DATABASE pubs TO testBack

19、创建新表

根据已有的表创建新表：

A：create table tab_new like tab_old (使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

20、添加主键：

Alter table tabname add primary key(col)

删除主键： Alter table tabname drop primary key(col)

21、创建索引：

create [unique] index idxname on tabname(col….)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

22、几个高级查询运算词

A： UNION 运算符

UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL)，不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符

INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

23、复制表、拷贝表

select top 0 * into b from a

insert into b(a, b, c) select d,e,f from b;

24、选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

25、包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

26、说明：随机取出10条数据

select top 10 * from tablename order by rand()

参考网址：