一、T-sql语句(增删查改) 1、查询数据表中所有数据:select * from 表名 2、创建数据库: create database [MyDatabaseOne] on primary ( name='MyDatabaseOne', filename='f:\MyDatabaseOne.mdf', size=5MB, maxsize=150MB, filegrowth=20% ) log on ( name='MyDatabaseOne_log', filename='f:\MyDatabaseOne_log.ldf', size=5mb, filegrowth=5mb ) 3、数据库中创建表 use MyDatabaseOne create table Departments ( AutoID int identity(1,1) primary key, DepartmentName nvarchar(50) not null ) 4、删除数据表:drop 数据表名 5、*注意点: INSERT INTO TblClass (tClassName) VALUES (N'3班')--字符串中有中文建议都加N 6、数据插入语句:Insert into 表名 [(列1,列2...) ] values(值1,值2...) 7、数据更新语句:Update 表名 set 列=新值,列2=新值,... [where 条件] 8、数据删除语句:delete from 表名 [where...] 9、删除表中的全部数据(drop table用来删除整个表) 1.delete form Biao 自动编号继续编 2.truncate table biao 推荐这个 1,无法和条件删除,只能全部删除 2,自动编号恢复到初始值 3,使用truncate删除表中所有数据效率高的多 4,不触发delete触发器 二、数据库的约束 主键约束(PK) 非空约束 唯一约束(UQ):邮箱和手机号码唯一性 检查约束(CK):年龄范围 默认约束(DF):性别默认男 外键约束(FK):级联删除:主键表中某一元素删除,外键表关联的数据全部删除 级联更新 三、T-sql语句(约束) --手动删除一列 ALTER TABLE dbo.Employees DROP COLUMN EmpAddress --增加一列 ALTER TABLE dbo.Employees ADD EmpAddr NVARCHAR(1000) --修改列数据类型 ALTER TABLE dbo.Employees ALTER COLUMN EmpEmail VARCHAR(200) --增加一个主键约束 ALTER TABLE dbo.Employees ADD CONSTRAINT PK_Rmployees_EmpId PRIMARY KEY(EmpId) --增加非空约束 ALTER TABLE dbo.Employees ALTER COLUMN EmpName VARCHAR(50) NOT NULL --增加唯一约束(允许有一个空值) ALTER TABLE dbo.Employees ADD CONSTRAINT UQ_Employees_EmpName UNIQUE (EmpName) --增加默认约束 ALTER TABLE dbo.Employees ADD CONSTRAINT DF_Employees_EmpGender DEFAULT('男') FOR EmpGender --添加检查约束 ALTER TABLE dbo.Employees ADD CONSTRAINT CK_Employees_EmpGender CHECK(EmpGender='男' OR EmpGender='女') ALTER TABLE dbo.Employees ADD CONSTRAINT CK_Employees_EmpAge CHECK(EmpGender>=18 AND EmpGender<=120) --增加一个外键 ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Departments FOREIGN KEY(DeptID) REFERENCES dbo.Departments(AutoID) --删除约束 ALTER TABLE dbo.Employees DROP CONSTRAINT FK_Employees_Departments,CK_Employees_EmpAge --通过一句话增加多个约束 ALTER TABLE dbo.Employees ADD CONSTRAINT DF_Employees_EmpGender DEFAULT('男') FOR EmpGender, CONSTRAINT CK_Employees_EmpGender CHECK(EmpGender='男' OR EmpGender='女'), CONSTRAINT CK_Employees_EmpAge CHECK(EmpGender>=18 AND EmpGender<=120) --创建表的时候同时加约束 create table Departmentstest ( AutoID int identity(1,1) primary key, DepartmentName nvarchar(50) not NULL UNIQUE ) create table Employeestest ( EmpID int identity(1,1) primary key, EmpIDCard varchar(18) not NULL , EmpName nvarchar(50) NOT NULL UNIQUE CHECK(LEN(EmpName)>2), EmpGender CHAR(2) DEFAULT('男'), EmpDate datetime, EmpAge INT CHECK(EmpAge>0 AND EmpAge<80), EmpAddress nvarchar(300) NOT NULL, EmpPhone varchar(100), DeptID int not NULL FOREIGN KEY REFERENCES dbo.Departmentstest(AutoID) ON DELETE CASCADE, EmpEmail varchar(50) UNIQUE ) 四、T-sql语句(数据查询) --*表示显示所有列 --查询语句没有加where SELECT * FROM dbo.TblStudent --查询部分列 SELECT tsID,tsname,tsgender FROM dbo.TblStudent --根据条件,只查询部分行(使用where条件筛选部分行显示) SELECT * FROM dbo.TblStudent WHERE tsID=5 --给查询结果其中的列起别名 SELECT tsid AS '(学生ID)', tsname AS 学生姓名 , 学生性别=tsgender FROM dbo.TblStudent SELECT tsid 学生ID , tsname 学生姓名 , tsgender 学生性别, 婚否='否' --添加一个新列 FROM dbo.TblStudent --并不是select一定要配合from来使用,可以单独使用select SELECT 当前系统时间=GETDATE() SELECT 班长='丁许', 班花='班花', 班主任='班主任' 五、Top、Distinct --distinct关键字,针对已经查询出的结果然后去除重复 SELECT * FROM dbo.TblStudent SELECT DISTINCT * FROM dbo.TblStudent SELECT tsname,tsgender,tsaddress FROM dbo.TblStudent SELECT DISTINCT tsname,tsgender,tsaddress FROM dbo.TblStudent ----排序 order by列名 SELECT * FROM dbo.TblStudent ORDER BY tsAge DESC --降序排序 SELECT * FROM dbo.TblStudent ORDER BY tsAge ASC SELECT * FROM dbo.TblStudent ORDER BY tsAge --默认为升序排序 --top关键字,一般与orderby配合使用 SELECT TOP 2 * FROM dbo.TblStudent ORDER BY tsAge DESC SELECT TOP (1*2) * FROM dbo.TblStudent ORDER BY tsAge DESC --如果是表达式,应该用()把表达式括起来 SELECT TOP 10 PERCENT * FROM dbo.TblStudent ORDER BY tsAge DESC --百分比,3.1->4向上取整 六、聚合函数(先分组在统计) sum()求和 count()求数目 max()最大值 min()最小值 avg()平均值 一些问题: 1、聚合函数不统计空值(sum()认为null为零 avg()也不统计空值) 2、如果使用聚合函数的时候,没有手动group by分组,那聚合函数默认把整个表中的数据当作“一组”来进行统计 七、带条件的查询 1、精确查询 --查询没有及格的学生的学号 SELECT tsid FROM dbo.TblScore WHERE tEnglish<60 OR tMath<60 --查询年龄在18-19岁的男同学 两种等价 SELECT * FROM dbo.TblStudent WHERE tsAge>=18 AND tsAge<=19 AND tsGender='男' SELECT * FROM dbo.TblStudent WHERE tsAge BETWEEN 18 AND 19 AND tsGender='男' --查询math成绩在80-90之间的学生 SELECT * FROM dbo.TblScore WHERE tMath BETWEEN 80 AND 90 --查询出所有班级id等于1或2的学生 SELECT * FROM dbo.TblStudent WHERE tsClass=1 OR tsclass=2 SELECT * FROM dbo.TblStudent WHERE tsClass IN(1,2) --对于in或者or查询,如果查询中的条件是连续的几个数字,最好使用>= <=或者between ..and 来提高效率 SELECT * FROM dbo.TblStudent WHERE tsClass >=1 AND tsClass<=2 2、模糊查询(针对字符串操作的) 通配符:_ % [] ^ _表示任意的单个字符 --姓张2个字 SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张_' --姓张3个字 SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张__' %匹配任意多个任意字符 --姓名只要是姓张的都查询出来 SELECT* FROM dbo.TblStudent WHERE tsName LIKE'张%' SELECT* FROM dbo.TblStudent WHERE tsName LIKE'张%' AND LEN(tsName)=2 []表示筛选,范围 UPDATE dbo.TblStudent SET tsName=REPLACE(tsName,'(女)','') SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张[0,9]三' SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张_三' SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张[a,z]三' SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张[0,9 a-z]三' SELECT * FROM dbo.TblStudent WHERE tsName LIKE '张[^0,9]三' SELECT * FROM dbo.TblStudent WHERE tsName NOT LIKE '张[0,9]三' 查询出姓名中包含百分号(使用[]进行转义或者自己定义转义符) SELECT * FROM dbo.TblStudent WHERE tsName LIKE '%[%]%' SELECT * FROM dbo.TblStudent WHERE tsName LIKE '%/%%' ESCAPE '/'--直接申明转义符 3、空值null的问题 null值无法使用=或者<>来进行比较 判断null值必须使用is null或is not null --查询所有年龄是null的同学信息 SELECT * FROM dbo.TblStudent WHERE tsAge IS null --查询所有年龄不是null的同学信息 SELECT * FROM dbo.TblStudent WHERE tsAge IS NOT NULL --任何值和null计算完毕后都是null SELECT 2000+NULL 七、数据排序 --1.降序order by 列名 desc --2.升序order by 列名 或者 order by 列名 asc --3.order by 语句必须一定要放在整个sql语句的最后 SELECT * FROM biao INNER JOIN.. WHERE... gruop BY... HAVING ... ORDER BY.. --4.根据多列进行排序 --先根据英语成绩排序,再根据数学成绩排序(当英语成绩相同的时候再根据数学成绩排序) SELECT * FROM dbo.TblScore ORDER BY tEnglish DESC,tMath DESC --5.可以按照表达式进行排序 SELECT *, 平均分=(tEnglish+tMath)*1.0/2 FROM dbo.TblScore ORDER BY 平均分 DESC SELECT * FROM dbo.TblScore ORDER BY (tEnglish+tMath)*1.0/2 DESC SELECT * --3 FROM dbo.TblScore --1 WHERE tMath>=60 --2 ORDER BY tMath --4 --6.order by 返回的数据是有序的,返回数据集合叫‘游标’(order by 排序以后的结果不再是集合,不能再被别的查询使用) 八、分组(gruop by):对数据进行汇总统计 1.分组一班都和聚合函数连用 --从学生表中查询出每个班级的班级ID和班级人数 SELECT tsclass AS 班级ID, 班级人数=COUNT(*) FROM dbo.TblStudent GROUP BY tsClass --统计处所有学生中男同学与女的人数 SELECT tsgender AS 性别, 人数=COUNT(*) FROM dbo.TblStudent GROUP BY tsgender --从学生表中查询出每个班级的班级ID和班级男同学的人数 SELECT 班级id=tsclass,男同学人数=COUNT(*) FROM dbo.TblStudent WHERE tsgender='男' GROUP BY tsClass SELECT SUM(tsage), 性别=tsgender, 人数=COUNT(*) FROM tblstudent GROUP BY tsgender --当使用了分组语句group by或者是聚合函数的时候,再select的查询列表中不能再包含其他的列名,除非该列同时出现在了group by 子句中,或者该列也包含在某个聚合函数中 九、having语句:对分组以后的数据组进行筛选而where是对分组前的每一行数据进行筛选 SELECT tsclass AS 班级ID, 班级人数=COUNT(*) FROM dbo.TblStudent GROUP BY tsClass HAVING COUNT(*)>1 十、SQL语句执行顺序 5>select 5-1>选择列,5-2>distinct,5-3>top(应用TOP项最后计算) 1>From表 2>WHERE条件 3>gruop by列 4>having筛选条件 6>order by列 select 商品名称, sum(销售数量) as 销售数量 from Myorders gruop by 商品名称 order by 销售数量 desc select 商品名称, 销售总价=sum(销售数量*销售价格) from Myorders group by 商品名称 having sum(销售数量*销售价格)>3000 order by 销售总价 desc select 购买人 购买可口可乐数量=sum(销售数量) form myorders where 商品名称='可口可乐' group by 购买人 order by 购买数量 desc
MS SQL Server学习笔记(一)
最新推荐文章于 2022-05-18 15:53:48 发布