sql语句_自连接_外连接_左连接_子查询_排序_分组

数据库的sql语句分为DDL,DCL,DLL

一 DDL,
data define language: 新建一个数据库
ceate创建 alter修改 drop删
这三条语句,在下面几个组件中,都会用得到

数据库分为下面几个组件

1 简单操作数据库,database

$ drop DATABASE if exists 数据库名; #如果之前已经存在,删除它
$ create database 数据库名;#创建一个数据库
$ use 数据库名;#进入数据库

#增加字段
alter table present_log add `user_uid` int(11) NOT NULL COMMENT '赠送者user_uid';

#修改字段名 + 其它信息
ALTER TABLE `present_log`
CHANGE COLUMN `user_uid` `owner_uid`  int(11) NOT NULL COMMENT '赠送者user_uid' AFTER `created_at` ;


#根据字段名修改其它信息
alter table present_log MODIFY `owner_uid` int(11) NOT NULL COMMENT '赠送者owner_uid' AFTER `owner_id` ;

2 简单操作表table

设置变量,和约束

变量类型	说明
int	数字
tinyint	数字
varchar	字符串类型 必须是一个介于 1 和 8,000 之间的数值
char	指定字符的长度 char(8)，你输入的字符小于8时,它会再后面补空值。当你输入 的字符大于指定的数时,它会截取超出的字符
datetime	日期加时间,设置长度之后,写入的时间会有毫秒值
date	日期
TEXT	text存储可变长度的非Unicode数据
longtext
dicemal
float
double

六大约束

类型	说明
主键约束:primamy key	一个表只有一个主键,且不能空值
唯一约束:unique	表示,一行数据中不能有相同的列值,一个表可以定义多个unique的约束
检查约束:check	输入一列或多列的值的范围，通过逻辑表达式来判断数据的有效性
外键约束:foreign key (ID)	references 表名(ID)连接主表与从表,主表中的一个列,与另一个表中的数据相连接,一般不使用
非空约束:not null	是否允许为空
默认约束:default	设置默认值

create table 表名(
 .... 设置字段变量与约束	
						)DEFAULT CHARSET=utf8 #字符编码的更改

数据库与表的字符编码设置要比较注意

3,视图 view

是一个是虚表,只能是本用户自身可以使用,基于sql的查询建立
建立view:

$ create view lishi as 
					select sname from student,(select ...from) sd WHERE sname like "lishi";

4,存储过程,(procedure)

一般用于供外部程序调用,相当于一个把数据封装的概念,能够起到对数据的保护安全的作用,能提交处理效率
自定义:
没有返回值,out代表结果输出,out可以有多个也就是说在参数
in代表

create procedure pro ([参数[in|out] 数据类型])
	create procedure sp_search(in p_name varchar(10)
	begin
	set @varibale1 = concat('%',p_name,'%');
	if p_name is null  or p_name='' then
		select * from t_user;------------------------查找所有
		else
	select *from t_user where user_name like @varibale1;-----按条件查找
		end if;
	end;	

调用方法:

$ call sp_search('李'); 

接收返回结果:
1.可以在procedure中使用select 语句
2.在参数中定义out类型

5 触发器

一般不使用,不做了解 

6,索引 index

使用索引的前提是数据量庞大的表
创建索引的优点是,可以提高查询效率,
当更新基本表时,自己自动修改索引
缺点是:当基本表,不断地在更新,删除,增加数据时,会提高维护索引的时间

创建新的索引:

$ create index 索引名 on 表名()

使用索引注意点:
添加索引就是为了查询使用,提高查询效率,当我们的业务比较复杂时,sql查询优化是我们必须要去做的!!!

https://blog.csdn.net/zoeou/article/details/65481400

7 函数

主要是用于对数据字段的处理,要有返回值,这是跟存储过程的区别

举例几个简单函数:

concat(a,b)字符串的连接 sage+10 +10也是一个简单函数
avg 平均值
sum 求和
max 最大
min 最小
调用方法 select func(xxx,xxx),如下:

select sid,CONCAT(sname,'xxx') sname,sage+10 from student where sname like 'lishi';

自定义函数:

CREATE function funadd(age int,stand int)
						returns VARCHAR(20)
						BEGIN
						if age >= stand then
						#--------------------这里写各种T-SQL语句
						return 'old';
					end;

二,DML,
data manage language: 对数据本身的操作语句

增insert 删delete 改updata

模板	sql
insert into 表名(列名) values(‘要加入的值’);	insert into classes (cname) values(‘数学’);
delete from 表名 条件 id为7的;	delete from zsy where id=7;
update 表名 set 要改的列名里面的外语 where 表里面名字为 英语,	update classes set cname=‘外语’ where cname=‘英语’;
删除可以是没有条件的,不用where,可以直接删除整个表,如果你真的这么做的,你就准备卷铺盖走人吧_

查select

select [distinct] 字段1 as 别名, 函数(),字段1+字段2 ...
from 表 (基本表,子查询,视图)  表别名 
 ( left |inner join ) 表2 on 表1.字段 = 表2.字段2 ...
where  字段 ( 关系 (any | all) | between ..and | like | in | )  
and | or  条件2 
order by 列1,列2 (asc | desc) #排序
group by 列1,列2  #分组,
having count(1) > 0 #分组以后条件

单表查询

模板	说明
select * from 表名;	查表里面所有数据
select name,id from student where id=12;	查表里面所有id为12的数据,结果要显示名字和id;
select * from student where sname like ‘%lishi%’;	模糊查询,student里面sname带有lishi的全都显示到结果
select sum(sage) from student;	求总数
select avg(sage) from student;	平均值
select *from student order by sdate asc;	按sdate查询出整个表的结果,然后进行升序 默认结果为升序排列
select *from student order by sdate desc;	按sdate查询出整个表的结果,然后进行降序
select scid,avg(sage) avg from student group by scid;	先按scid分组,再进行取其年龄平均数,把scid和sage显示到结果里面
select sid,sname from student where (sname like ‘李四%’ and sid=2) or sid=9 order by sdate desc;	从student 查询 id和name 条件是名字里面有包含 李四的和id为2的,或者id为9的,以date的形式降序排列

多表查询-外连接

模板	说明
select cname,sname from student,classes;	查询两个表,两个表之间用逗号隔开
select scid,sname,cname from student,classes where scid=cid;	查询两个表,条件是第一个表的id=第二个表的id;还有取别名,如果取了别名的话,一定要使用别名
多表查询-左连接
left join用来查询左侧表的全部记录及右测表的连接字段完全相同于左侧表的记录
模板	说明
–	–
select cname,count(scid) sum from classes left join student on student.scid=classes.cid ;	查询calsses 和student里面的名字和所有id的,如果是左id=右id的话,把左边名字都列出来,右边如果条件吻合的话也列出来,排序cname分几个小组

**多表查询-自连接 **
树形结构,存到二维表中 自己先复制一份,
select sname,cname from category c1,category c2 where c1.pid=c2.cid;

多表查询-子查询-
把查询出来的结果,做为一张表来用,所以有时候select查询语句会写的很长

any跟all的区别
any: 大于的话,就是比最小值要大,大于的话,就是比最小值要小
all: 取出最大值 跟前者比较

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”,它的作用是通过一定的规则将一个数据集划分成若干个小的区域

Transcat-Sql语句
变量的声明 控制语句 分支 循环
case when可以直接在select语句
%百分号,放两边%100% 结果表示模糊包含
放左边%100 结果表示以100开头
放右边100% 结果表示以100结束
_下划线,有几个下划线,表示结果后面可以跟几个字符

修改sql_mode
In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column ‘db.table.is_delete’; this is incompatible with sql_mode=only_full_group_by

$ show variables like '%sql_mode%'

$ select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

$ set @@global.sql_mode=(select replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY,',''))

$ select @@global.sql_mode;
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

改完之后,服务重新连接数据库即可