SQL 建库 建表
--1.创建一个数据库 create database School; --删除数据库 drop database School; --创建数据库的时候指定一些选项。 create database School on primary ( name='School', filename='C:\Program Files\SQL\MSSQL12.MSSQL\MSSQL\DATA\School.mdf', size=5MB, --filegrowth=10MB, filegrowth=10%,--按照文件的百分比来增长 maxsize=100MB ) log on ( name='School_log', filename='C:\Program Files\SQL\MSSQL12.MSSQL\MSSQL\DATA\School.ldf', size=3MB, filegrowth=3%, maxsize=20MB ) --切换数据库 use School;
TOP
select top 10 * from student 查询前十条 select top 10 percent * from student 查询前10%;
Distinct
select distinct * from student 查询不重复
Avg Count Sum Min MAx
select AVG(ID) from student 平均数 select COUNT(ID) from student 总数 select Sum(ID) from student 求和 select Max(ID) from student 求最大数 select Min(ID) from student 求最小
Where And Or
select * from student where ID>90 and ID<100 and ID%2=0 select * from student where ID>100 or ID<10 and ID%2!=0
模糊查询 Like Not Like % _
select * from student where Name like '%三%' 查询带有三的 select * from student where Name not like '%三%' 查询不带有三的 select * from student where Name like '三%' 查询三开头的 select * from student where Name like '%三' 查询三结尾的 select * from student where Name like '_ 三 %' 查询第二个为三的
Order By where desc asc 排序
select * from NT_User order by UserAge 按年龄大小排序 select * from NT_User order by UserAge asc 降序 select * from NT_User order by UserAge desc 升序
Group By Having 分组 (要和聚合函数一起使用)
select UserGender from NT_User group by UserGender 按性别分成三组 select UserGender, Count(*) from NT_User group by UserGender 查询三组性别的每个的个数 select UserGender,COUNT(*) from NT_User group by UserGender having COUNT(*) >1000 查询三组性别的每个的个数大于1000的
Union Union All
select Name from Student union all select Name from Teacher 两表联查 所有的 select Name from Student union select Name from Teacher 两表联查 去除重复的
Select Into Insert Into
select * into teacher from Student 创建Teacher表 并把Student表里面的数据复制到Teacher表中 insert into teacher select * from Student 把Student表里面的数据复制到Teacher表中
Inner Join on 两表联查
select A.Content,B.UserName from CLN_Resource as A inner join NT_User as B on A.UserID =B.UserID select * from CLN_Resource inner join NT_User on CLN_Resource.UserID=NT_User.UserID