Sql Server 语句学习

Sql Server 语句学习

用 SQL 语句建库

新建数据库

单机新建查询，输入下方代码，点击执行，闭上眼睛默说五遍“我想要一个数据库”，然后睁开眼睛，你便拥有了一个数据库~~~~

CREATE DATABASE [2859902680] --数据库名字（名字含数字数字要用[]，下同）
on primary
(
	name = [2859902680_data],
	filename = 'E:\goodgoodstudy\project\sql\1\2859902680_data.mdf', --数据文件路径
	size=10, --初始大小/MB
	maxsize=unlimited, --是否限制增长
	filegrowth=1 --增量
)
LOG ON (
	name = [2859902680_log],
	filename = 'E:\goodgoodstudy\project\sql\1\2859902680_log.ldf', --日志文件路径
	size = 5,
	maxsize=unlimited,
	filegrowth=1
)

建表

右键点击新建好的数据库，选择新建查询，在新的 sql 文件中输入下方代码，点击执行并呼出一口仙气

CREATE TABLE SC
(
--  列名 约束条件（数据类型，可否为空）
    S# CHAR(4),
    C# CHAR(4),
    SCORE SMALLINT,
    PRIMARY KEY(S#,C#),--设置主键
    --FOREIGN KEY(S#)REFERENCES S(S#),--设置外键
);

你就会发现一个现新建的数据表！！！

插入数据

还是右键数据库，新建查询，输入以下代码后点击执行

INSERT INTO SC
VALUES ('S1','C1',70),
('S1','C2',80),
('S1','C4',90),
('S2','C5',90),
('S4','C1',90),
('S5','C2',60),
('S5','C3',NULL),
('S5','C4',60),
('S5','C5',78),
('S6','C1',76)

记得 char 类型的数据需要加引号，int 类型不需要

然后右键这个数据表

点击 编辑前200行

即可看到刚刚输入的数据乖乖躺在表里~~~~

SQL 查询语句

还是右键数据库，新建查询，然后按照下方模板输入代码，体会查询数据带来的成就感

SELECT DISTINCT /*返回唯一不同的值*/ column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column /*结合函数对结果集进行分组*/
    HAVING constraint_expression
    ORDER BY column ASC/*升序排序*/ / DESC/*降序排序*/
    LIMIT count/*输出数据量*/ OFFSET COUNT/*从第几个数据开始输出*/;

数据操作

– ① 检索年龄小于17岁的女学生的学号和姓名。

SELECT DISTINCT S#, SNAME
FROM S
WHERE AGE<17 AND SEX='F'

– ② 检索男学生所学课程的课程名与课程号。

SELECT DISTINCT CNAME, SC.C#
FROM SC, S, C
WHERE  SC.C# = C.C#
    AND S.S#=SC.S#
    AND S.SEX='M'

– ③ 检索男学生所学课程的任课教师的工号和姓名。

SELECT DISTINCT T#, TNAME
FROM T WHERE T# IN( 
	SELECT T# FROM C WHERE C# IN(
        SELECT C# FROM SC WHERE S# IN(
            SELECT S# FROM S WHERE SEX = 'M'
        )
    )
)

– ④ 检索至少选修两门课程的学生学号。

SELECT DISTINCT S#, COUNT(C#) AS a
FROM SC
GROUP BY S#
HAVING COUNT(C#) > 2

– ⑤ 检索至少有学号S2和S4学生选修的课程的课程号。

SELECT C#
FROM C
WHERE C# IN (
    SELECT x.C# FROM SC as x, SC as y
		WHERE x.S# = 'S2' and y.S# ='S4' and x.C#=y.C#
)

– ⑥ 检索WANG同学不学的课程的课程号。

SELECT DISTINCT C#
	FROM SC
	WHERE C# NOT IN(
    	SELECT C# FROM SC WHERE S# IN(
    		SELECT S# FROM S WHERE SNAME = 'WANG'
	)
)
GROUP BY C#

– ⑦ 检索全部学生都选修的课程的课程号与课程名。

SELECT C#, CNAME
FROM C
WHERE C# IN(
    SELECT C# FROM SC
	GROUP BY C#
	HAVING COUNT (*) = (
    	SELECT COUNT(*) FROM S
		GROUP BY S#
	)
)

– ⑧ 检索选修课程包含LIU老师所授课程的学生学号。

SELECT DISTINCT SC.S#
FROM SC, C, T
WHERE SC.C# IN (
    SELECT C.C# WHERE C.T# IN(
        SELECT T# FROM T WHERE TNAME = 'LIU' 
    )
)

组合查询：

– ① 统计有学生选修的课程门数。

SELECT COUNT(DISTINCT C#) AS co
FROM SC

– ② 求选修C4课程的女学生的平均年龄。

SELECT AVG(S.AGE)AS RESULT
FROM S
WHERE S.S# IN(
    SELECT SC.S# FROM SC WHERE SC.C#='C4'
)
AND S.SEX='F'

– ③ 求LIU老师所授课程的每门课程的平均成绩。

SELECT C#,AVG(SC.SCORE) AS AVG_SCO
FROM SC
WHERE SC.C# IN(
    SELECT C# FROM C WHERE T# IN(
        SELECT T# FROM T WHERE TNAME='LIU'
    )
)
GROUP BY C#

– ④ 统计每门课程的学生选修人数（超过10人的课程才统计）。要求显示课程号和人数。

SELECT SC.C#, COUNT(*) AS NUM
FROM SC
GROUP BY SC.C#
HAVING COUNT(*)>10

– ⑤ 检索学号比WANG同学大，而年龄比他小的学生姓名。

SELECT SNAME FROM S
WHERE S#>(
	SELECT S# FROM S WHERE SNAME = 'WANG'
)
AND AGE<(
	SELECT AGE FROM S WHERE SNAME = 'WANG'
)

– ⑥ 在表SC中检索成绩为空值的学生学号和课程号。

SELECT S#, C#
FROM SC
WHERE SCORE IS NULL

– ⑦ 检索姓名以L打头的所有学生的姓名和年龄。

SELECT SNAME,AGE FROM S
WHERE SNAME LIKE 'L%'

– ⑧ 求年龄大于女同学平均年龄的男学生姓名和年龄。

```sql
SELECT SNAME, AGE
FROM S
WHERE SEX = 'M'
    AND AGE >(
    SELECT AVG(AGE) FROM S WHERE SEX='F'
)

– ① 用户定义完整性：学生年龄<30。

ALTER TABLE S
ADD CONSTRAINT NL CHECK(AGE<30)

– 用实验验证当操作违反了完整性约束时，系统如何处理？

INSERT INTO S
VALUES('S11', 'HAORAN', 19, 'F')

– ② 修改SC中的约束条件，成绩在0-100之间。
– 如果约束存在则先删除

if exists(
	select * from sysobjects where name= 'CJ'
	)
alter table SC drop constraint CJ; 

– 添加约束

ALTER TABLE SC
ADD CONSTRAINT CJ CHECK(SCORE<=100 AND SCORE >=0)

–用实验验证当操作违反了完整性约束时，系统如何处理？

INSERT INTO SC
VALUES('S1', 'C5', 150)

– ①求某门课的平均成绩，并把结果存入数据库；

CREATE TABLE L
(
    C# CHAR(4) NOT NULL,
    AVGGRADE SMALLINT,
    PRIMARY KEY (C#),
    FOREIGN KEY (C#) REFERENCES C(C#)
);

INSERT INTO L
SELECT C#, AVG(SCORE)
FROM SC
GROUP BY C#

– ②将选LIU老师所授课的全体学生的成绩置零。

UPDATE SC SET SCORE = 0
WHERE C# IN (
    SELECT C# FROM C WHERE T# IN(
        SELECT T# FROM T WHERE TNAME = 'LIU'
    )
)