排序查找
Select * from table_name [where 筛选条件] [order by<排序列名>[asc desc]]
多次排序:order by 列名1,列名2[desc,asc]
取别名(as):select sname as 姓名 ,addr as 地址 from table_name...
限定行数:top num
如:select top 5 sname ,addr fromtable_name
Mysql 中用limit 5
使用distinct 排除重复 (不改变数据库数据和结构,只从结果中选择不重复的数据)
如 select distinct 列A from 表名
约束关键字:
Between and
Not between and
In()
Not in()
Like ‘’
_ 匹配单个字符
% 匹配多个字符
[]中指定范围内的一个字符 如 like ‘[1-9]’
[^]不在括号中的字符 如 like ‘[a-d][^0-9]’
查空行方法:
Select * from table_name where name is null
聚合函数与分组
Count:返回结果集中行的数目
Sum: 返回所有值的总和
Avg: 返回当前列的平均值
Max :返回最大值
Min: 返回最小值
Group by 列名 ---分组查询
Having: 如:select avg(score) from score group by 列名 having count(score) >1
组合查询:如
Select * from student where num = (
Select num from score whereg grade= (
Select max(grade) from score where id = 1 and times = 1
)
)
Id =[>][<] (唯一结果值)
Id in (可以多个结果值)
Id between 1 and 10
Update insert delete 与 select 都用where做筛选条件
Union 去重复行合并
Union all 不去掉重复行合并
内联接(inner join)取交集
外联接(left join right join full join)
Select st.name,st.stid,c,score from student as st,(inner join ) score as sc where (on) st.scid = sc.num
TSQL
局部变量:必须以@作为前缀如 @name,先声明在使用
全局变量:以两个@为前缀 如 @@version
由系统定义我们不可以改,只可读
声明变量:declare @变量名 数据类型
如 declare @name char(10)
变量赋值 set @变量名 = 值
Set @name = sname from stu where st=200
Print “”打印输出语句
If-else结构
If(条件)
Begin
语句123....
End
Else
Begin
End
while 语句
While()
Begin
语句1,23...
Break
end
case 语句
Case
When 条件1 then 结果
When 条件1 then 结果
Else
End
创建存储过程
Create procedure pro_name[with recompile |encryption]
As sql_statement[...n]
如 create procedure pro_name with encryption as select name from student
删除存储过程
Drop procedure pro_name
带参数的存储过程
Create procedure pro_name @naem varchar(10)
As update user set showcount = showcount+1where name=@name
Select..............where name=@name
调用 exec pro_name ‘name’
666导航网 可以自由收藏管理个人常用网址的便捷上网工具