一、函数(function):
  1 文本函数
  2 数学函数
  3 日期函数
  4 聚合函数
  5 系统函数

1 文本函数(字符串函数)

 1.1 获取字符串长度
语法 select LEN(colname) from tbname [where子句]
 
1.2  转换大 小写
语法 select LOWER(varchar() )/ UPPERvarchar() [where子句]

1.3 去掉左右空格
语法
select LTRIM('      a')
select RTRIM('a      ')

1.4 截取字符串
语法
select left('这是从左边截取',(int)num--截取长度)
select right('这是从右边截取',(int)num--截取长度)
select SUBSTRING(varchar( ),(int) num1--起始位置,(int) num2-- 截取长度)


1.5 字符串替换
语法
select REPLACE(varchar( ),'被更改内容','填入内容')
select STUFF(varchar( ),起始位置,删除长度,'填入内容')

1.6 查找字符串
语法 select CHARINDEX(varchar(需要查找内容),varchar(待查对象))

1.7 合并字符串函数
MySQL 语法 SELECT CONCAT('华信','智原')

sql server 语法 select '3'+'3'

2 数学函数

2.1 取比参数大的最小整数
2.2 取比参数小的最大整数
2.3 四舍五入(可保留精度)
语法
select CEILING(22.01)
select FLOOR(22.01)
select ROUND(x,y)  y---精度值

提示:y值为负数时,保留的小数点左边的相应位数直接保存为0,不进行四舍五入

2.4 获取随机数的函数
语法  SELECT RAND(),RAND(),RAND()

语法  SELECT RAND(8),RAND(8),RAND(8)


RAND()返回一个随机的浮点值v,范围在0到1之间(0<= v <=1.0)      RAND(x)用来产生重复序列


3 日期时间函数 
    
    sql server
3.1 获取当前日期时间
sql server语法 select GETDATE()

3.2 将日期指定部分取出以字符串形式返回
sql server语法 select DATENAME(YY,'2019-06-30')

3.3 将日期指定部分取出以数值形式返回
sql server语法 select DATEPART(YY,'2017-11-23')

3.4 给日期的指定部分加数字(会按照完整的年来加减)
sql server语法  select DATEADD(mm,1,'2015')

3.5 两个日期指定部分的差
sql server语法  select DATEDIFF(YY,'2015-10-10','2017-9-30')

    MySQL
 3.1 获取当前日期的函数和获取当前时间的函数
语法
SELECT CURDATE()
SELECT CURRENT_DATE()
SELECT CURTIME()
SELECT CURRENT_TIME()

 3.2  IF函数条件判断
  语法 : IF(表达式,返回值v1,返回值v2)  如果表达式是true,则返回值是v1,反之返回值是v2,IF()的返回值为数字或者字符串值
语法示例    SELECT IF(20>3,1,0)

语法示例   SELECT IF(STRCMP('aa','bb'),'yes',0)

两个字符串自左向右逐个字符相比(按ASCII值大小相比较),直到出现不同的字符或遇'\0'为止。

3.3 IFNULL(v1,v2)函数
语法  SELECT IFNULL(1,2),IFNULL(NULL,8)


3.4 获取最后一个自动生成的ID值的函数
语法  SELECT LAST_INSERT_ID()
MySQL的LAST_INSERT_ID的注意事项:
第一、查询和插入所使用的Connection对象必须是同一个才可以,否则返回值是不可预料的。
使用这函数向一个给定Connection对象返回的值是该Connection对象产生对影响AUTO_INCREMENT列的最新语句第一个AUTO_INCREMENT值的。这个值不能被其它Connection对象的影响,即它们产生它们自己的AUTO_INCREMENT值。
第二、LAST_INSERT_ID 是与table无关的,如果向表a插入数据后,再向表b插入数据,LAST_INSERT_ID返回表b中的Id值。
第三、 假如你使用一条INSERT语句插入多个行,  LAST_INSERT_ID() 只返回插入的第一行数据时产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单



二、查询
    基本查询:
    select colname,....colname from tbname
    [where]
    [order by]
    [group by]
    [having...]
1 条件查询

   1.1  不带条件查询

1.1.1  如果要查询表中所有的列,采用通配符
语法 select * from   tbname

1.1.2 查询部分列
语法 select colname,colname,... from  tbname

 1.2  带条件查询
    1. 2.1 模糊查询
       条件:[not] between and
            [not] like
            [not]in
            is null
语法 select * from tbname where 条件

2 利用聚合函数进行查询语句

  2.1 数据记录个数总和
语法 select count(emp_no)[自定义名称]from emp

2.2 字段数据求和
语法  select sum(colname) [自定义名称]from tbname [where]

2.3 字段平均值
语法 select AVG(colname)[自定义名称] from tbname [where]

2.4 字段最大值
语法 select MAX(colname)[自定义名称] from tbname [where]

2.5 字段最小值
语法  select MIN(colname) [自定义名称] from tbname [where]





3 排序查询

3.1 排序: select * from tbname order by colanme desc(降序)|asc(升序默认,可以不写),colname desc(降序)|asc(升序,可不写)
,.....
语法示例
select * from tbname order by 字段名 desc  
select * from tbname order by 字段名 asc 
3.2 查询前n条数据
sql server语法   select top n * from tbname order by 需要查询的字段 desc
MySQL语法   SELECT DISTINCT 字段名 FROM 表名 ORDER BY 字段名 DESC LIMIT 查询起始位置0),查询个数n



4 分组查询

 4.1 基础语法  select 查询字段名,所需函数 from 表名 group by 查询字段名
     !!!敲黑板 group by 被分组的列名必须跟随聚合函数
例子:
  每个部门岁以上的有多少人,并按降序排列
select dept_no,COUNT(emp_no) from emp
where e_age>=30
group by dept_no
order by COUNT(emp_no) desc

每个部门岁以上的人且人数不少于2人
select dept_no,COUNT(emp_no) from emp
where e_age>=30
group by dept_no
having COUNT(emp_no)>=2   having 对分组后的组进行筛选
order by COUNT(emp_no) desc

注意:如果在MySQL中赋予了别名 在having后必须跟别名

4.2 多列分组
select 大组字段名,小组字段名...[count() 自定义名称] from 表名   group by 大组字段名,小组字段名

注意:
1 顺序:where    group by  having  order by 
2 order bysql server 不允许使用列的别名
3 分组中查询的字段:在select后查询的字段a 出现在group by之后      b 聚合函数中

ps 去掉查询结果中重复的列 关键字 distinct(去重)
语法示例     select distinct 需要查询的列名 from 表名



5 高级查询

5.1 多表连接查询

     5.1.1 内连接  关键字  inner join  内连接连接两张表中共同的列
语法示例
select * from emp
inner join dept
on emp.dept_no = dept.dept_no

  课堂案例
查询学生的选课信息(学号学生姓名 教师工号教师姓名课程名称)
select s.stu_no,stu_name,t.t_id,t_name,c.c_id,c_name from stu s
join xuanke x
on s.stu_no=x.stu_no
join  t_c tc
on x.tc_id=tc.tc_id
join teacher t
on t.t_id=tc.t_id
join course c
on c.c_id=tc.c_id
 
     5.1.2 外连接
      5.1.2.1 左外连接    关键字  left [outer] join
语法   select 字段名(可带函数) from 表名 别名 left [outer] join 表名 别名 on 关联列的表达式
课堂案例
查询员工及部门信息
select * from emp e
left outer join
dept d
on e.dept_no=d.dept_no
select * from dept d
left outer join
emp e
on e.dept_no=d.dept_no

查询每个部门的人数
select d.dept_no,COUNT(emp_no) from dept d
left outer join
emp e
on e.dept_no=d.dept_no
group by d.dept_no

      5.1.2.2 右外连接    关键字 right [outer] join
语法   select 字段名(可带函数) from 表名 别名 right [outer] join 表名 别名 on 关联列的表达式
课堂案例
select * from emp e
right join dept d
on e.dept_no=d.dept_no

注意:显示左表中所有的记录,如果左表没有与右表相匹配的,右表将会自动补充null;右链接同样遵循。
      5.1.2.3 完全外连接  
           sql server   关键字    full [outer] join
语法   select 字段名(可带函数) from 表名 别名 full  [outer] join 表名 别名 on 关联列的表达式

课堂案例
select d.dept_no,COUNT(emp_no) from emp e
full join
dept d
on e.dept_no=d.dept_no
group by d.dept_no

                MySQL       union all  合并结果集         union  合并结果集 去掉重复
示例
SELECT * FROM emp e
LEFT JOIN
dept d
ON e.dept_no=d.dept_no
UNION
SELECT * FROM emp e
RIGHT JOIN
dept d
ON e.dept_no=d.dept_no

备注:完全连接相当于左右连接同时使用,删除重复的数据

     5.1.3 等值连接  不添加筛选条件时,把两个表的数据挨个匹配
示例
select * from emp e,dept d
where e.dept_no=d.dept_no

select * from emp  e,dept d
where e.dept_no=d.dept_no
and d_name='财务部'

     5.1.4 交叉连接(笛卡尔积) 关键字  cross
                        作用:和等值连接的作用相同
示例
select * from emp e
cross join
dept d
where e.dept_no=d.dept_no



子查询

子查询:
    在select    updata    insert    delete中使用查询语句
    注意:()
    分类:
  • 嵌套子查询
  • 相关子查询
    别名:
  • 起别名
  • 不起别名
简单示例

select emp_no,e_sal,dept_no from(
select emp_no,e_sal,d_name,e_hit,e.dept_no
from emp e
join dept d
on e.dept_no=d.dept_no
)as e
where e_sal>5000
order by e_sal desc

删除财务部的员工信息

delete from emp
where dept_no=(
select dept_no from dept
where d_name='财务部'
)

where
    1.关系运算符    >    <    =
        子查询结果一定是单个值
    2.[not]  in
        子查询结果    单列    多个值
    3.[not] exists(true/false)
        子查询结果任意,可以是单个值或多个值,只判断子查询是否查到结果,不关注结果是什么
    4.any    some

关系运算符示例
查询财务部的员工信息

select * from emp
where dept_no=(
select dept_no from dept
where d_name='财务部'
)

工资高于平均工资的员工信息

select * from emp
where e_sal>(
select AVG(e_sal) from emp
)

[not]  in
注意:子查询中的空值问题
查询有员工的部门信息

select * from dept
where dept_no in(
select distinct dept_no from emp
)

查询没有员工的部门信息

select * from dept
where dept_no not in(
select distinct dept_no from emp
where dept_no is not null
)

exists
查询有员工的部门

select * from dept d
where
     exists(
          select 1 from emp e    --select 1 只需要判断是否存在,不需要查询所有记录
          where e.dept_no=d.dept_no
     )

exists效率比in 高

查询每个部门中工资高于部门平均工资的员工信息

select * from emp e1
where e.sal>
(
     select avg(e_sal) from emp e2
     where e1.dept_no=e2.dept_no
)


分页
    1.记录总数
    2.每页显示的记录数
    3.当前页码
sql server

select top 每页显示记录数 * from emp
where emp_no not in(
     select top ((当前页码 - 1) * 每页显示记录数) emp_no from emp order by emp_no desc
)
order by emp_no desc

MySQL 中limit 后面不能跟表达式,需要先定义变量int a = (当前页码 - 1) * 每页显示记录数;

复制表结构select  into ... from tbname
into后的表不能事先存在,该语句会自动创建表
从emp表复制emp_no,e_sal字段到emp2表,同时复制表结构和数据

select emp_no,e_sal into emp2 from emp

从emp表复制emp_no,e_sal字段到emp2表,只复制表结构,不复制数据,where后面可以加限制条件

select emp_no,e_sal into emp2 from emp
where 1!=1

insert into tbname select from --只复制数据,不复制表结构,tbname需要事先创建
create table emp2(
)

insert into emp2 select emp_no,e_sal from emp where e_sal > 6000