SQL数据库学习重难点

--插入数据

insert into Students1 values('张三','湖北',90.4,'zhangsan@') insert into Student values(6,'王五','90','数学')

--执行语句

select * from Students1

select * from Student

--插入数据

--插入一行

insert into Studentxingxi values('张三','男',89.7)

insert into Studentxingxi values('李四','男',98.7)

insert into Studentxingxi values('王五','男',89.8)

insert into Studentxingxi values('覃成成','男',89.3)

insert into Studentxingxi values('沈选峰','男',97.3)

insert into Studentxingxi values('陈伦','男',87.4)

insert into Studentxingxi values('孙悟空','男',35.4)

insert into Studentxingxi values('小猪','男',78.3)

insert into Studentxingxi values('小红','女',45.5)

--插入多行

insert into Studentxingxi(sname,ssex,sgrade)

select '小明','男',98.8 union

select '二明','男',67.3 union

select '三明','男',78.3

--插入多行注意区别

insert into Studentxingxi(sname,ssex,sgrade)

select '小明','男',98.8 union all

select '二明','男',67.3 union all

select '三明','男',78.3

-- 使用insert into .....

-- select ...... union 若有相同的项则会重复插入

-- 使用insert into .....

-- select ...... union all 若有相同的项则不会重复插入select * from Studentxingxi

--删除数据

delete from Studentxingxi where scode=10 or scode=11 or scode=12

--更新数据

--更新表Students1中的SAddress列全部改为湖北

update Students1 set SAddress='湖北'

--如果学生成绩小于就加分

update Students1

set SGrade=SGrade+5

where SGrade<90

--将Studentxingxi表中的性别为女的改为男

update Studentxingxi set ssex='男'

where ssex='女'

--从学生信息表里将sname ssex sgrade三列插入到新建表同学录中去

--方法一此方法需要先建立一个tongxunlu表

insert into tongxunlu (姓名,性别,成绩)

select sname,ssex,sgrade

from Studentxingxi

--方法二此方法不需要建立一个chengji表执行时自动建立

use student

select sname,ssex,sgrade

into chengji1

from Studentxingxi

select * from chengji

select * from tongxunlu

--将StudentCourse表中的CourseID,CourseName改为课程编号，课程名称并将StudentCourse表中的数据插入到新建的kechengbiao中

select CourseID as '课程编号' ,CourseName as '课程名称'

into kecheng

from StudentCourse

select * from kecheng

--第四章--

use student

select * from Studentxingxi

--回顾--

insert into Studentxingxi(sname,ssex,sgrade)

values('朱文龙','男',98)

insert into Studentxingxi(sname,ssex,sgrade)

select '小明','男',87 union

select '二明','男',56 union

select '三明','男',34

--第四章重点--

--1取出成绩大于的学生信息--

select * from Studentxingxi

where sgrade>90

--2信息表中的列scode,sname,ssex,sgrade改为学号，姓名，性别，成绩--

select scode as '学号',sname as '姓名',ssex as '性别',sgrade as '成绩'

from Studentxingxi

order by 成绩desc

--或者用order by sgrade desc

--注意该改变仅仅是在结果集中改变,在原表中并不改变--

--3将表按学生成绩从大到小排序--

select * from Studentxingxi

order by sgrade desc

--注意在此中不可用order by 成绩desc 注意区别上一语句

select * from Studentxingxi

--4将两列合并--

select sname+'-'+ssex as '姓名-性别'

from Studentxingxi

--注意合并时as两边的类型应该一致如：select scode+'-'+sname as '学号-性别'其中scode 为int 而要改为字符型顾会出错