数据库总结2

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

FROM tbl_student

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.删除那张新表

DROP TABLE tbl_new

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