SQL SERVER 从某一个数字开始加1

基础函数upper，lower，length，substr，concat，replace，ifnullround，ceil floor，转义\，distinct
UUID
模糊查询
条件查询 null，between and，limit，order by
聚合函数
where having

查询

Select count(*) from tb_door;#查询tb_door表中的总记录数
SELECT * FROM tb_door;#查询tb_door表中的所有记录（插入的内容/数据）

修改

1.修改tb_door表中id为1的记录

update tb_door set tel=555 where id=1;

2.修改字段类型：alter table 表名 modify 字段名称 字段类型()；

增加（插入记录、添加加字段）

insert into tb_door values(null,'永和大王1店',666);
insert into tb_door values(null,' 永和大王2店',888);

2.增加字段：alter table 表名 add column 字段名称 类型()；

删除

删除tb_door表中id为2的数据

Delete from tb_door where id=2;

查询语句

where

SELECT empno,ename,job FROM emp;#从表中查询指定字段
SELECT *FROM emp WHERE empno=100
SELECT *FROM emp WHERE sal>=8000

基础函数

SELECT empno,ename,job FROM emp;#查编号和名字
SELECT empno a,ename b FROM emp;  #1.设置别名

SELECT ename, UPPER(ename) a FROM emp;	#2.数据转大写
SELECT 'ABC',LOWER('ABC') FROM emp;
	#lower转小写
SELECT ename,LOWER(ename) FROM emp;
#lengtn求长度 ，根据u8，一个字母或数字长度为1，一个汉子长度为3
SELECT ename, LENGTH(ename) FROM emp;	#字段长度
SELECT ename,job, LENGTH(job) FROM emp;
#substr截取子串
#substr(a,b)--a是截取谁，b是从哪个字符开始，c是截取的长度
SELECT ename,SUBSTR(ename,2) FROM emp;  
SELECT ename,SUBSTR(ename,2,2)FROM emp;	#3

SELECT ename,CONCAT(ename,123,'asd',6) FROM emp;#4.concat拼接
SELECT ename,REPLACE(ename,'a','666')FROM emp;  #5.replace替换,把ename中的a替换成666
SELECT comm, IFNULL(comm,10) FROM emp;#6.判断,如果comm是null,用10替换

round，ceil floor，转义\，distinct

#round ceil floor  uuid()  distinct
SELECT comm ,ROUND(comm)FROM emp;#四舍五入取整
SELECT comm ,ROUND(comm，1)FROM emp；#四舍五入保留一位小数
SELECT comm,CEIL(comm)FROM emp;#向上取整
SELECT comm,FLOOR(comm) FROM emp;#向下取整

SELECT UUID();#生成一个32为的随机字串
SELECT NOW();
##写错select now(year()),now(month()),now(day());
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW());#获取当前的年月日
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());#获取当前的时分秒

SELECT 'xi\'an';
SELECT "xi'an";

##写错select loc,distinct(loc)from dept;
##select loc distinct from dept
SELECT DISTINCT loc FROM dept;#除去字段loc中重复的数据
SELECT DISTINCT depno FROM emp;

SELECT *FROM dept WHERE dept=1;
SELECT *FROM dept WHERE dept!=1;
SELECT *FROM dept WHERE dept<>1;

SELECT dname FROM dept WHERE dept>1;

SELECT dept FROM dept WHERE dname='research';#查询名称是research的部门编号
SELECT dname FROM dept WHERE loc='二区' AND dept=3;#查询地址在二区编号为3的部门名称
###select dname from dept in dept(1,2,3);
SELECT dname FROM dept WHERE dept IN(1,2,3)#查询dept中含123的编号  in只能查询统一字段中的条件

UUID

UUID 是指Universally Unique Identifier，翻译为中文是通用唯一识别码，UUID 的目的是让分布式系统中的所有元素都能有唯一的识别信息。如此一来，每个人都可以创建不与其它人冲突的 UUID，就不需考虑数据库创建时的名称重复问题。

格式： UUID 的十六个八位字节被表示为 32个十六进制数字，以连字号分隔的五组来显示，形式为 8-4-4-4-12，总共有 36个字符（即三十二个英数字母和四个连字号）。例如：
123e4567-e89b-12d3-a456-426655440000

模糊查询

like

SELECT *FROM emp WHERE ename LIKE '%a%';
SELECT *FROM emp WHERE ename LIKE 'l__';
SELECT *FROM emp WHERE ename LIKE 'a%';
SELECT *FROM emp WHERE ename LIKE '%a';

条件查询

null，between and，limit分页，order by

SELECT dept FROM dept WHERE dept IN(1,2,3)

INSERT INTO dept(dept,dname,loc) VALUES
(4,'assds','三区'),
(5,'yyds','五区');

SELECT dname FROM dept WHERE loc="二区";
SELECT dept FROM dept WHERE loc="二区"  AND dname LIKE '%o%';
SELECT DISTINCT job FROM emp WHERE ename LIKE 'l%';#查询名字一l开头的员工岗位并去重
#查询15年到19年入职的员工姓名和岗位
SELECT ename,job FROM emp WHERE YEAR(hiredate)>=2015 AND YEAR(hiredate)<=2019;
SELECT ename,job FROM emp WHERE hiredate>='2015-1-1' AND hiredate<='2019-12-31';

SELECT sal,comm,(sal+IFNULL(comm,0))*12 FROM emp WHERE job="副总";#查询副总的年薪
#条件查询
#1.null,null的数据用is /is not关键字来过滤
SELECT *FROM emp WHERE comm IS NULL;#查询没有奖金的员工信息
SELECT *FROM emp WHERE comm IS NOT NULL

#2.between and
SELECT *FROM emp WHERE 
SELECT *FROM emp WHERE sal BETWEEN '5000' AND '10000';[5000,10000]#查询工资5000~10000的员工信息
SELECT ename FROM emp WHERE hiredate BETWEEN '2019-1-1' AND '2019-12-31';#查询2019年入职的员工姓名
#select ename  from emp where hiredate year(hiredate)='2019';
SELECT ename  FROM emp WHERE YEAR(hiredate)='2019';

#3.limit分页，复杂语句通常limit放最后
SELECT *FROM emp LIMIT 2;#只取前n条
SELECT *FROM emp LIMIT 0,2;#从0+1条开始，总共展示2条

SELECT *FROM emp WHERE job='员工' LIMIT 0,1;#查询岗位是员工的第一条信息记录

#4.order by排序 默认升序
SELECT *FROM emp ORDER BY hiredate
SELECT *FROM emp ORDER BY ename #
SELECT *FROM emp ORDER BY job	ASC# ASC升序；把汉字转换为编码比较，默认按照字典顺序升序
SELECT *FROM emp ORDER BY sal
SELECT *FROM emp ORDER BY sal DESC #降序
#查询15年到19年入职的员工信息只取前两条并按照工资升序排
SELECT *FROM emp WHERE hiredate BETWEEN '2015-1-1' AND '2019-12-31' ORDER BY sal LIMIT 0,2;##[2015,2019]
#year(hiredate) between 2015 and 2019
SELECT *FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2019 ORDER BY sal LIMIT 4

SELECT ename,12*(sal+IFNULL(comm,0)) '年薪' FROM emp #统计每个员工的年薪
SELECT *FROM emp WHERE YEAR(hiredate)<2019#统计19年以前入职的员工信息

聚合函数

max min sum avg count

#聚合函数max min sum avg count
SELECT MAX(sal) FROM emp;
SELECT COUNT(sal),AVG(sal) '平均薪资',SUM(sal),MIN(sal) FROM emp
#count总记录数，用法count(*),count(1),count(字段名)
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp#低版本相较于count(*)高效
SELECT COUNT(comm) FROM emp;#为null的不记录,count(字段)不推荐用
#select count(comm is null) from emp

SELECT COUNT(*) FROM emp WHERE ename LIKE '%a%';	#统计名字里包含a的总记录数
SELECT MAX(sal) FROM emp WHERE YEAR(hiredate)=2019;	#统计19年入职的员工的最高薪
SELECT AVG(sal) FROM emp WHERE depno=2;			#统计2号部门的平均薪资
SELECT SUM((sal+IFNULL(comm,0))*12) FROM emp WHERE depno=2;#统计2号部门的工资支出总数

group by having

#分组
 #把表里的所有数据按照不同的维度
 #group by
 SELECT depno '部门',AVG(sal) FROM emp GROUP BY depno	#统计每个部门的平均薪资
 SELECT depno,MAX(sal) FROM emp GROUP BY depno
 #口诀1：查询结果中出现了混合列，包括着聚合列和非聚合列，必须分组
 #口诀1：通常按照非聚合列分组
 SELECT depno,MIN(sal) FROM emp GROUP BY depno
 
 SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate)
 SELECT job,AVG(sal) FROM emp GROUP BY job
 
 
 #统计每个岗位的平均薪资，而且只要>=10000
 SELECT job,AVG(sal) '平均工资' FROM emp GROUP BY job HAVING  AVG(sal)>=10000
 
 #where里不能用别名，也不能出现聚合函数
 
 #统计每年入职的人数，而且只要19年的
 #SELECT YEAR(hiredate),COUNT(1) FROM emp GROUP BY YEAR(hiredate) HAVING YEAR(hiredate)=2019#出错, year()函数特殊
 SELECT YEAR(hiredate) a,COUNT(1) FROM emp GROUP BY YEAR(hiredate) HAVING a=2019##用别名没报错
 SELECT YEAR(hiredate) a,COUNT(1) FROM emp WHERE YEAR(hiredate)=2019 GROUP BY YEAR(hiredate)
 #having a=2019分组后的过滤

#where比having高效，因为执行时机要早些
#where先筛选再分组，group by先把所有数据先分组再筛选

start transaction;
insert into dept values(null,'asaxd','asxds');
select *from dept;  #自己可以查到别人查不到
commit;					#提交后都可以查到