频道澳门葡京手机版网址
登录注册
澳门葡京手机版网址 > 数据库 > MySQL > 正文
mysql数据库常用sql的分类整理
2017-04-19 09:15:09         来源:fetch_and_update的博客  
收藏   我要投稿
*******************************************************************************************************************************
*MySQL      	
					1.中型的关系型数据库管理系统(RDBMS,Relational Database Management System)
					2.使用的 SQL (结构化查询语言,Structured Query Language)语言是用于访问数据库的最常用标准化语言

*SQL功能概括分组:
					1.DML(Data Manipulation Language,数据操作语言)--------用于检索或修改数据
					2.DDL(Data Definition Language,数据定义语言)---------用于定义数据的结构,创建、修改或者删除数据库对象
					3.DCL(Data Control Language,数据控制语言)---------用于定义数据库用户的权限

*使用CMD操作MySQL
					1.mysql -h Mysql主机地址 -u 用户名 -p                               =======   (进入MySQL环境)

					2.show databases;                                                            =======   (显示数据库)

					3.use 数据库名称;                                                            =======   (进入数据库)

					4.create database 数据库名称 default character set 'utf8';      =======   (创建数据库(SQL))
										
					5.show tables;                                                                  =======   (查看数据库中的所有表)
										
					6.show create database 数据库名称;                                   =======   (查看数据库创建语句)
										
					7.desc 表名称;  (或者describe 表名);                                   =======   (查看表结构)

					8.show create table 表名称;                                               =======   (查看表创建语句)
*******************************************************************************************************************************
*常用的SQL语句
					1.创建表

							create table 表名称(
								字段名1  数据类型   [primary key  auto_increment],
								字段名2  数据类型   [not null], 
								...
								字段名n  数据类型   [约束] 
							);

					注意:
							数值型数据类型: 
								integer (int) 
								float(有效位数,小数位数)
								double(有效位数,小数位数)
								decimal(有效位数,小数位数)

							字符串数据类型:	
								varchar(n)   
								char(n)

					2.删除表
							drop table 表名称;

					3.插入数据行(记录)
							insert into 表名称(字段名1,字段名2,...)values(值1,值2,...);
							insert into 表名称 values(值1,值2,...);

							一条SQL插入多条记录:

							insert into 表名称(字段名1,字段名2,...)values(值1,值2,...),(值1,值2,...)...;
							insert into 表名称 values(值1,值2,...),(值1,值2,...)...;

					4.修改数据行(记录)
							update 表名称 set 字段名1=值1 [where 条件] [order by 字段名 [desc]] [limit 修改行数];

					5.删除数据行(记录)
							delete from 表名称 [where 条件] [order by 字段名 [desc]] [limit 删除行数];

					6.如果要删除表中所有数据,除了使用delete from 表名称,还可使用
							truncate table 表名称;   ======》更高效,因为它不返回删除的影响行数。

					7.查询数据
							select 字段名1,字段名2... from 表名称 [where 条件];

							给查询的结果字段起一个别名:
							select 字段名1 as 别名1,字段名2 as 别名2... from 表名称 [where 条件];
*******************************************************************************************************************************
*修改数据表的SQL

					1.给数据表添加一列(字段)
							alter table 表名称 add column 列名称 数据类型 [约束];

					2.修改数据表中的一列(字段)的数据类型或约束
							alter table 表名称 modify column 列名称 数据类型 [约束];

					3.修改数据表中的列名(字段名)
							alter table 表名称 change column 旧列名称 新列名称 数据类型 [约束];

					4.删除数据表中的列(字段)
							alter table 表名称 drop column 列名称;
*******************************************************************************************************************************
*******************************************************************************************************************************
*******************************************************************************************************************************
*
简单查询


1.not、or、and如果and与or共同出现在where条件中,则and的优先级高。eg:查询remark不为null的记录select * from student where remark is not null;2.模糊查询(like)通配符:% 任意个数的任意字符eg:将姓名中含有'东'字的学生信息查出来select * from student where name like '%东%';_ 代表一个字符eg: 将姓名中第二个字为'京'的人查询出来select * from student where name like '_京%';3.对查询结果排序order by 字段名1[desc],字段名2[desc]...;4.限制数量的查询limit 查询数量;limit 查询记录的初始偏移量(从0开始计算偏移量),查询数量;eg:查询student表中的前三条记录select * from student limit 3;查询第2到第4条记录select * from student limit 1,3;查询前三名的学生信息;select * from student order by score desc limit 3;**********************************************************************************************************************************分组查询1.聚合函数 max(字段) 返回某字段的最大值 eg: select max(price) from product; min(字段) 返回某字段的最小值 eg: select min(price) from product; sum(字段) 返回某字段的和 avg(字段) 返回某字段的平均值 count(*) 返回记录数 count(字段名) 返回不为null的某个字段的记录数2.分组查询 group by 字段名1,字段名2 [having 条件] 注意:在分组查询时,select后面的字段名必须与group by后面的分组字段一致,当然,select后也可跟聚合函数(用来计算分组后每组的信息)。如果分组字段与查询字段不一致,则查询结果无意义。 eg:按照种类分组,并计算每组的平均价格:select kind,avg(price) from product group by kind;只查询水果组的平均价格。select kind,avg(price) from product group by kind having kind='水果';**********************************************************************************************************************************子查询(内层查询)子查询中的关键字1.any(some)any是在关系运算符后出现的,表示只要符合子查询结果中的任何一个数据。2.allall是在关系运算符后出现的,表示符合子查询结果中的所有数据。3.exists如果子查询的结果中至少存在一行记录,则返回true.not exists 如果子查询的结果中不存在记录,则返回true.4.in如果外层查询的字段包含于子查询的结果列表中,则返回该外层查询的记录。not in **********************************************************************************************************************************连接查询1.等值连接select 列1,列2... from 表1,表2 where 连接条件;2.外连接左外连接(left join): 左表中的记录全部查询出,右表只查询出符合连接条件的记录。select 列1,列2... from 左表 left join 右表 on 连接条件;eg:select student.name,school.school_name from student left join school on student.school_id=school.id;右外连接(right join):右表中的记录全部查询出,左表只查询出符合连接条件的记录。select 列1,列2... from 左表 right join 右表 on 连接条件;3.内连接select 列1,列2... from 表1 inner join 表2 on 连接条件; **********************************************************************************************************************************外键(Foreign Key)约束主要用来设置“一对多”的两张表的关联关系。外键在子表中设置,用来与父表关联。create table emp( id int primary key auto_increment, emp_name varchar(10) not null, dept_id int not null, constraint fk_emp foreign key(dept_id) references dept(id));

					alter table emp drop foreign key fk_emp;   // 删除外键
    *********************************************************************************************************************************
    /*
     eg:
     create table school(
		sid int primary key auto_increment,
		sname varchar(20) not null
     );
     insert into school(sname) values('辰战'),('辰南');

     create table teacher(
		tid int primary key auto_increment,
		tname varchar(20) not null,
		school_id int not null,
		constraint fk_teacher foreign key(school_id) references school(sid)
     );

     insert into teacher(tname,school_id) values('痞子龙',1);
     insert into teacher(tname,school_id) values('大德大威',2);
     create table subject(
		sid int primary  key auto_increment,
		sname varchar(20) not null,
		teacher_id int not null,
		constraint fk_subject foreign key(teacher_id) references teacher(tid)
     );
     insert into subject(sname,teacher_id) values('梦舞',2);   
     insert into subject(sname,teacher_id) values('独孤',1);
*/
*********************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
*事务(transaction)
				事务规定了一组操作,这一组操作作为一个整体,要么全部成功,要么全部失败。

				事务的特性(ACID):
				1.原子性(Atomic)
						事务作为一个整体而存在,事务内的操作不可再分割,
						要么全部成功,要么全部失败。

				2.一致性(Consistent)
						事务操作成功之前与之后,数据状态要保持一致。

				3.隔离性(Isolation)
						一个事务只能查看其他事务操作之前的状态或之后的状态,
						不能查看其正在操作的状态。

				4.持久性(Duration)
						一旦事务操作成功,则对数据系统的影响是持久性的。
*********************************************************************************************************************************
*事务的隔离级别
				几个前提概念:

				*脏读(dirty read):一个事务读取到了另一个事务未提交的数据的现象。
				*不可重复读:一个事务两次读取的数据不一致的现象。
				*幻读:一个事务两次读取的数据记录数(数据条目的数量)不一致的现象。

				隔离级别:
				1.读未提交级别(read uncommitted):
						一个事务读取到了其他事务还未提交状态的数据。
						可能发生脏读、不可重复读、幻读。

				2.读提交级别(read committed):
						一个事务只能读取其他事务已经提交之后状态的数据。
						可能会发生不可重复读,幻读。

				3.可重复读级别(repeatable read,MySQL默认的隔离级别)
						在当前事务中可以重复读取到相同的数据。
						可能会发生幻读(注意:Innodb数据库存储引擎已经解决了幻读问题)。

				4.串行化隔离级别(serializable)
						事务的操作(主要是指写操作)是串行化的。

				*在CMD中操作事务:
						start transaction;    // 开启事务
						select @@tx_isolation;  // 查看当前事务隔离级别
						// 设置当前会话的事务隔离级别
						set session transaction isolation level read uncommitted || read committed || repeatable read || serializable;
						rollback;  // 回滚事务(相当于撤销操作)
						savepoint 保存点名称;  // 设置保存点
						rollback to 保存点名称;  // 回滚到保存点的状态
						commit;  // 提交事务
*********************************************************************************************************************************
*JDBC批处理、处理事务操作
				JDBC批处理
				方式一:使用PreparedStatement来进行批处理操作
						void addBatch()   // 将一组参数添加到此PreparedStatement对象的批处理命令中
						int[] executeBatch()  // 实行批处理命令

				方式二:使用Statement来进行批处理操作
						如何获取Statement对象?
						Statement stmt=connection.createStatement();
						int count=stmt.executeUpdate(String sql);  // 实行静态的增删改SQL语句
						ResultSet rs=stmt.executeQuery(String sql);  // 实行静态的查询SQL

						Statement对象用来处理批处理的方法:
						addBatch(String sql)  // 将给定的SQL命令添加到此Statement对象的当前命令列表中
						int[] executeBatch()  // 实行批处理命令
*********************************************************************************************************************************
*JDBC处理事务:
				Connection对象的方法:
				1. setAutoCommit(boolean autoCommit) // 设置自动提交状态
					conn.setAutoCommit(false);  // 取消自动提交模式

				2. void commit()  // 提交事务
				3. void rollback()  // 回滚整个事务
				4. Savepoint setSavepoint()  // 设置保存点
				5. void rollback(Savepoint savepoint)  // 回滚到保存点
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
*合并查询结果
				select 字段名1,字段名2... from 表名称 [where 条件]
				union [all]
				select 字段名1,字段名2... from 表名称 [where 条件];
				
				注意一:union all是合并所有查询结果,如果查询结果有重复记录,则不会去重。
					 union如果查询结果有重复记录,会自动去重。

				注意二:合并查询的多个SQL中的查询字段数量必须一致,各个SQL的字段需要对应
					  起来(各字段对应的意义必须一样,否则查询结果无意义)。查询的结果集中
					  的字段名以第一个SQL为准。
*********************************************************************************************************************************
*SQL注入
				SQL注入即SQL的拼接,将前台传入的参数,经后台拼接成SQL后实行一些非法操作。
				如何防止SQL注入?
				前台验证、后台验证输入的内容是否有敏感SQL关键字、对输入密码进行加密。
*********************************************************************************************************************************
*MySQL的备份与还原
				1.备份数据库
						mysqldump --no-defaults -u 用户名 -p --databases 要备份的数据库 > 备份路径

				2.还原数据库
						mysql -u 用户名 -p < 当时的备份文件路径

				3.备份数据表
						mysqldump --no-defaults -u 用户名 -p 数据库 要备份的表 > 备份路径

				4.还原数据表
						mysql -u 用户名 -p 还原表所属的数据库 < 备份路径
*********************************************************************************************************************************
*用户与权限(DCL)
				1.创建用户
						create user '用户名'@'主机IP' identified by '密码';
						注意:如果要让该用户在每台机器上都能登录,则主机IP为'%'。
			   
				2.授予用户权限
						grant update|insert|alter... on 数据库名.数据表名 to '用户名'@'主机IP';

						eg:授予某用户可以使用所有权限操作所有数据库中的表。
							grant all on *.* to  '用户名'@'主机IP';

				3.收回用户权限
						revoke 权限 on 数据库名.数据表名 from '用户名'@'主机IP'; 

				4.删除用户
						delete from user where user='用户名';
*********************************************************************************************************************************
*索引(index)
				分析查询语句:explain select * from 表 [where ...];
				索引主要用来提高检索(select)效率。

				创建普通索引的语法:
						create index 索引名称 on 表名称(字段名列表);
						注意:创建索引字段时,应该选择那些查询条件经常会使用到的字段。
				
				查看索引:
						show index from 表名称 \G;

				删除索引:
						drop index 索引名称 on 表名称;

				以下情况不会使用到索引:
						1.模糊查询时,%出现在第一个匹配位置时索引不会被用到。
						2.当使用多个字段共同创建索引时,只有第一个字段被使用时会用到索引,其它
							字段不会使用到索引。

						补充:唯一索引(也叫唯一约束)unique
						create table stu(
							id int primary key auto_increment,
							name varchar(10) unique,
							score double unique
						);

						主键约束与唯一约束的共同点与不同点:
							共同点:值不能重复。
							不同点:主键值不能为null,且每个表最多只能有一个主键。
						   唯一约束的列值可以为null,且每个表可以有多个唯一索引(约束)。
*********************************************************************************************************************************
*视图(view)
				视图是一个查询结果集。方便多次使用该结果集。可以将视图作为一张逻辑表。
				create view 视图名称
				as
				(select查询语句);

				 
				举例:
				create view emp_view 
				as
				(
				  select  emp.e_name, emp.e_job,dept.d_name  from employee as emp 
				  inner join dept on emp.dept_no=dept.d_no
				);
*********************************************************************************************************************************
*存储过程(Storage Procedure)

				delimiter //
				create procedure 存储过程名(参数类型 参数名称 参数数据类型)
				begin
				   存储过程体
				end //


				参数类型: 
					in(输入参数类型,默认参数类型)  
					out(输出参数类型) 
					inout(输入输出参数类型)

				 eg: in(输入参数类型,默认参数类型) 

					 delimiter //
					 create procedure proc_select(stuid int)
					 begin
						select id as 学号,name as 姓名,age as 年龄,score as 成绩,remark as 备注
						from student where id=stuid;
					 end  // 

				eg: out(输出参数类型) 

					delimiter //
					create procedure proc_out(out count int)
					begin
					  select count(*) into count from student;
					end  // 

					call proc_out(@x) //
					select @x //

				eg:inout(输入输出参数类型)

				   delimiter //
					 create procedure proc_inout(inout info varchar(20))
					 begin
						select remark into info from student where name=info;
					 end  //

					set @y='熊大' //
					call proc_inout(@y) //
					select @y //

			   eg: if...then...end if结构

				delimiter //
				create procedure proc_update(in a int)
				begin
				   if (a is not null) then
					 update student set remark='你的武功已经很高了!' where score>=95;
				   end if;
				end  // 

			   
				delimiter //
				create procedure proc_case(in stuid int)
				begin
					  declare vscore double;
					  declare vremark varchar(20);
					  declare info varchar(20);
					  select score,remark into vscore,vremark from student where id=stuid;
					  case vscore
						  when 60 then
							 set info='刚好及格';
						  when 95 then
							 set info='我考了95分!';
						  else
							 set info='不知道,没查出来';   
					  end case;
			   select info;
			   end  // 
*********************************************************************************************************************************
*MySQL系统函数
				时间数据类型: date (2017-02-23)
							   time (16:40:35)
							   datetime (2017-02-23 16:40:35)
							   timestamp 

				日期时间函数:
				 1.返回当前日期时间 now()或者sysdate()

				 2.增加时间函数:
						adddate('date类型或datetime类型',interval 要增加的时间 year|month|day|hour|minute|second);
						eg:六天以后的时间
							select adddate(now(),interval 6 day);

				 3.减去时间函数:
						subdate('date类型或datetime类型',interval 要减去的时间 year|month|day|hour|minute|second);
						eg:六天以前的时间
							select subdate(now(),interval 6 day);

				 4.时间间隔函数:
						datediff('第一个时间date或datetime类型','第二个时间date或datetime类型');
							注意:使用第一个时间减去第二个时间的日期部分。

*******************************************************************************************************************************     
				其他函数:
				  select md5('要进行MD5加密的字符串');
				  select database(); 查看当前所在的数据库
				  select session_user(); 查看当前用户
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
*MySQL中的触发器(trigger)
				delimiter //

				create trigger 触发器名称 before|after  触发事件(insert|delete|update)
				on 触发器关联的表名称 for each row
				begin
				   触发器实行的逻辑代码
				end  

				delimiter ;

				触发器案例:
				delimiter //

				create trigger stu_trigger after insert
				on student for each row
				begin
					 insert into log(logname,logtime)values(new.name,now());
				end //

				delimiter ;
*********************************************************************************************************************************
*MySQL的优化
				查询优化的方法:

				1.优化查询SQL语句。如果连接查询和子查询都可以查询到结果,则优先选择连接查询。
				         因为子查询时会创建临时表,临时表的内容是存储子查询结果的,查询完毕后,还要撤销临时表。

				2.在频繁使用的列上创建索引(index)。

				3.拆表。如果一个表中的字段很多,应该将不经常使用的字段存储于另一张表中,原表
						只保留常用查询字段。

				4.增加冗余字段。将需要经常使用到的信息以冗余字段的方式添加到表中,省去了连接查询的耗时。eg:如果经常要查询员工姓名与其对应的部门名称,则
						可将部门名称作为员工表emp的一个冗余字段,省去了emp表与dept表
						的连接查询。
				5.增加中间表。建立一个中间表,将多张表的查询字段存放于此中间表中。
***************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
点击复制链接 与好友分享!回澳门葡京手机版网址澳门葡京手机版网址
上一篇:MySQL的基本操作与创建
下一篇:mac安装mysql+sequelpro
相关文章
图文推荐
点击排行

关于大家 | 联系大家 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 澳门葡京手机版网址_澳门新莆京娱乐_www.88807.com - 点此进入--致力于做实用的IT技术学习网站

XML 地图 | Sitemap 地图