技术饭

一些常用的mysql语句收集

copylian 0 评论 10943 浏览 2017.03.14

一些常用的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 avg(oil) from car b where b.brand = a.brand)

select * from (

select id, name from student where sex = 1

) t where t.id > 2;

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

select all 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 age having age is not null;

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

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

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

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

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

select count(*), cid, sex from student group by cid, sex having 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] ,

　　　　.......

　　　　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(永久删除数据库及对象，如视图、索引等)

　　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()

参考网址：

MySQL高级查询——连接查询实例详解

MySQL高级查询之与Group By集合使用介绍：GROUP_CONCAT、WITH ROLLUP

mysql sql语句大全

常用的Mysql数据库操作语句大全

感谢你的支持，我会继续努力！

扫码打赏，感谢您的支持！

常用 mysql 语句

文明上网理性发言！

0 条评论

还没有评论，沙发等你来抢

只袄早~~~

只袄早~~~

CopyLian's Blog

关注互联网、学习IT技术、分享生活、艰苦创业的心情日迹！

技术饭

一些常用的mysql语句收集