USE StudentManager
SELECT * FROM tbl_student; SELECT * FROM tbl_class AS c; 【1】单条插入数据 INSERT INTO tbl_student ( stu_number, stu_name, stu_age, birthday, class_id ) VALUES ( ‘1000000001’, ‘张三’, ‘aa’, ‘1996-06-06’, 2 )
INSERT INTO tbl_class ( class_name, create_time ) VALUES ( ‘机202班’, default )
【2】–批量插入数据 INSERT INTO tbl_class(class_name) SELECT ‘光101班’ union SELECT ‘光102班’ union SELECT ‘光103班’ union SELECT ‘光104班’ –2008下语法 insert into tbl_class(class_name) values(‘光105班’),(‘光106班’),(‘光107班’)
SELECT * FROM tbl_student 【3】–利用tbl_student表复制一张新表，复制部分列 SELECT stu_number,stu_name,stu_age INTO tbl_stu2 FROM tbl_student 【4】–过滤重复的行 SELECT distinct stu_number FROM tbl_stu1
INSERT INTO tbl_stu1 SELECT stu_number,stu_name,stu_age
CREATE TABLE tbl_test ( id INT, NAME VARCHAR(20) ) SELECT * FROM tbl_test 【5】–删除去除表中重复的行
–1.先查询不重复的数据,将数据保存到一张新表 SELECT distinct * INTO tbl_new FROM tbl_test –2.删除tbl_test数据 DELETE FROM tbl_test –3.将新表中的数据迁移回tbl_test INSERT INTO tbl_test(id,[NAME]) SELECT * FROM tbl_new –4.删除那张新表
SELECT * FROM tbl_student DELETE FROM tbl_test
DELETE FROM tbl_student WHERE id=3
DELETE FROM tbl_student WHERE stu_name=’张三’
SELECT * FROM tbl_class
DELETE FROM tbl_class WHERE class_id<>2
INSERT INTO tbl_class ( – class_id – this column value is auto-generated class_name ) VALUES ( ‘计201班’ )
CREATE TABLE tbl_course ( id INT IDENTITY(1,1) PRIMARY KEY, [name] VARCHAR(20) ) SELECT * FROM tbl_course AS c insert INTO tbl_course VALUEs(‘Java’) insert INTO tbl_course VALUEs(‘Net’) DELETE FROM tbl_course TRUNCATE TABLE tbl_course
SELECT * FROM tbl_student AS s 【6】—修改 update 表名 set 列=值，列等值 where 条件 UPDATE tbl_student SET stu_name=’詹姆斯’,stu_age=23 WHERE id=5
CREATE TABLE tbl_student ( id INT … …. STATE INT –0 正常 -1删除 )
UPDATE tbl_student SET STATE =-1 WHERE id=5; SELECT * FROM tbl_student WHERE STATE=0 【7】简单查询 SELECT stu_number AS ‘学号’,stu_name as ‘姓名’ FROM tbl_student
SELECT stu_number ‘学号’,stu_name ‘姓名’ FROM tbl_student
SELECT ‘学号’=stu_number,’姓名’=stu_name FROM tbl_student
SELECT * FROM tbl_student WHERE stu_age>=23 AND stu_age<=29 SELECT * FROM tbl_student WHERE stu_age BETWEEN 23 AND 29 SELECT * FROM tbl_student WHERE birthday BETWEEN ‘1996-01-01’ AND ‘1999-12-31’
【8】排序 USE chapter4
SELECT TOP 3 * FROM tbl_student ORDER BY id DESC
–联合主键 DROP TABLE tbl_stu CREATE TABLE tbl_stu ( sid INT, num INT, stuNo CHAR(10) UNIQUE ) 【9】 –查找子字符串在父的位置 SELECT CHARINDEX(‘ACCP’,’My Accp Course’,1) –字符长度 SELECT LEN(‘SQL Server课程’) –字节长度 SELECT DATALENGTH (‘SQL Server课程’) –转小写 SELECT LOWER(‘HELLO’) –转大写 SELECT UPPER(‘hello’) –去空格 SELECT LTRIM(’ 周 瑜 ‘) SELECT RTRIM(’ 周 瑜 ‘) SELECT LTRIM(RTRIM(’ 周 瑜 ‘)) –截取 SELECT RIGHT(‘买卖提.吐尔松’,3) SELECT left(‘买卖提.吐尔松’,3)
SELECT SUBSTRING(‘hello everyone’,3,2) –替换 SELECT REPLACE(‘莫乐可切.杨可’,’可’,’兰’) SELECT REPLACE(’ 周 瑜 ‘,’ ‘,”)–去除空格
SELECT STUFF(‘ABCDEFG’, 2, 3, ‘我的音乐我的世界’)
–日期 SELECT GETDATE() –日期加法 SELECT DATEADD(mm,3,DATEADD(yy,4,’99-01-01’)) –日期差 SELECT DATEDIFF(mm,’1999-01-05’,’1998-08-06’)
–转换函数 SELECT CONVERT(VARCHAR(20),200)
SELECT CAST(200 AS VARCHAR(20))
PRINT ‘这是一个数:’+CONVERT(VARCHAR(20),200)
USE chapter4 【10】–模糊查询 –is null SELECT * FROM stuInfo WHERE stuAddress IS NOT NULL –between …and —>[18,28] SELECT * FROM stuInfo WHERE stuAge BETWEEN 18 AND 28 –in用法 SELECT * FROM stuInfo WHERE stuSex IN (‘男’,’女’)
SELECT * FROM stuInfo WHERE stuSex=’男’ or stuSex=’女’
【11】–聚合函数 SELECT * FROM stuMarks
INSERT INTO stuMarks ( ExamNo, StuNo, WrittenExam, LabExam ) VALUES ( ‘s271819’, ‘s25328’, 90, 58 )
SELECT SUM(WrittenExam) AS ‘总分’ FROM stuMarks
SELECT SUM(WrittenExam) 总分 FROM stuMarks
SELECT ‘sumScore’=SUM(WrittenExam) FROM stuMarks
SELECT SUM(WrittenExam)+SUM(LabExam) FROM stuMarks
SELECT SUM(WrittenExam+LabExam) AS ‘大总分’ FROM stuMarks –avg SELECT AVG(WrittenExam+LabExam)/2 AS ‘平均分’ FROM stuMarks AS sm
SELECT AVG(distinct WrittenExam) AS ‘平均分’ FROM stuMarks AS sm WHERE StuNo IN (‘s25303’,’s25302’,’s25301’)
SELECT AVG(distinct WrittenExam+LabExam) AS ‘平均分’ FROM stuMarks AS sm
SELECT AVG(WrittenExam+LabExam) FROM stuMarks —count SELECT COUNT(stuNo) FROM stuInfo AS si
SELECT COUNT(distinct stuName) FROM stuInfo AS si
SELECT COUNT(*) FROM stuInfo AS si 【12】–分组查询 SELECT * FROM score –分组依据 SELECT courseId,AVG(score) AS avgScore FROM score GROUP BY courseId HAVING AVG(score)>80 –where having区别 –having必须跟在group by的后面，在分组后求条件 –where 单独使用，也不使用where,在分组前求条件 SELECT * FROM stuInfo
CREATE TABLE tbl_test ( NAME VARCHAR(20), sex VARCHAR(4), score FLOAT, className VARCHAR(20) )
INSERT INTo tbl_test VALUES(‘张三’,’男’,80,’1班’) INSERT INTo tbl_test VALUES(‘李四’,’男’,60,’1班’) INSERT INTo tbl_test VALUES(‘王五’,’女’,70,’1班’) INSERT INTo tbl_test VALUES(‘赵六’,’女’,50,’2班’) INSERT INTo tbl_test VALUES(‘田七’,’女’,60,’2班’) INSERT INTo tbl_test VALUES(‘王八’,’男’,80,’2班’) INSERT INTo tbl_test VALUES(‘lss’,’男’,90,’3班’) INSERT INTo tbl_test VALUES(‘oop’,’女’,70,’4班’) INSERT INTo tbl_test VALUES(‘test’,’男’,80,’3班’) –同时使用where 和having SELECT className,AVG(score) AS avgScore FROM tbl_test WHERE sex=’男’ GROUP BY className having AVG(score)>=80
SELECT * FROM tbl_test AS t
【13】—表连接 SELECT * FROM student SELECT * FROM score SELECT * FROM Course
SELECT student.stuName,Course.cname,score.score FROM student INNER JOIN score ON student.stuNo=score.studentId INNER JOIN Course ON score.courseId=Course.cid
SELECT student.stuName,Course.cname,score.score FROM student left JOIN score ON student.stuNo=score.studentId LEFT JOIN Course ON score.courseId=Course.cid
SELECT * FROM student inner JOIN score ON student.stuNo=score.studentId 【14】–变形的写法 SELECT stuName,cname,score FROM student,score,Course WHERE stuNo=studentId AND courseId=cid
–子查询 –分步解决问题 先找李文才的年龄 再判断比李文才的大的
SELECT * FROM stuInfo WHERE stuAge in ( SELECT stuAge FROM stuInfo WHERE stuName=’李文才’ )
UPDATE stuInfo SET stuSeat = 10 WHERE stuAge> ( SELECT stuAge FROM stuInfo WHERE stuName=’李文才’ )
SELECT * FROM stuMarks
