数据库基础培训SQL语言培训1教案.ppt
n数据库基础培训SQL语言培训1n Still waters run deep.流静水深流静水深,人静心深人静心深 nWhere there is life,there is hope。有生命必有希望。有生命必有希望北明软件员工培训讲义2章节n查询数据的SQL语句n修改数据的SQL语句n管理数据定义的SQL语句3查询数据的SQL语句nSELECT 语句4查询数据的SQL语句-SELECTn本章讲述如何使用SELECT语句的五个子句。n在SELECT语句中，你必须按以下顺序使用这些子句：nSELECT子句nFROM子句nWHERE子句nORDER BY子句SELECT 字段1,字段2字段n FROM 表1,表 2表n5SELECT语句基础n可以用多种方法来查询数据库中的一个表。你可以裁剪SELECT语句来达到以下目的：n检索所有的或指定的列select id,name from student （查询编号，姓名)select*from student(查询所有字段)n对检索所得的数据执行计算或其它函数select id,name,sum(mark)from student group by id,name(按照编号、姓名为分组，对“成绩”进行汇总)(简单的说，就是查询每个学生的总成绩)6Where子句nWhere 子句用于限制数据nselect id,name from student where id=222233444上句查找出编号为222233444的学生的学生编号和姓名nselect id,name from student where name like 张%上句查找出姓名为“张”开头的学生的学生编号和姓名其它约束方法：nWhere column1 like _b%(部分匹配,%为通配符,表示=0个任意字符,_表示一个任意字符)nWhere id between 10000 and 20000 (范围匹配)nWhere id=10000 and id=20000nWhere id in(100001,100005,133434)(集合匹配)nWhere name is NULL(空值匹配)nWhere name is not NULL (非空值匹配)nWhere column1=column2 (字段间匹配)(=,=,.)7Where子句(2)n多条件限制1)select id,name from student where id 23456 and name like 张%2)select id,name from student where id 23456 or name like 张%3)select id,name from student where(id 23456 or name like 张%)and mark60 不要考虑条件匹配的先天优先级，在你想优先匹配的地方加上括号，永远是对的8Order by 子句(用于排序)n单列排序nSelect id,name from student order by id asc (升序)nSelect id,name from student order by id desc (降序)n多列排序select id,name,mark from student order by mark desc,id asc(先按照成绩降序，如果成绩相同，则按照编号升序)asc可以省略，因为默认是按照升序排列但是desc不可以如:select id,name from student order by id (升序)9Group by(用于汇总计算)nGROUP 中文意思：分组nselect id,name,sum(mark)from student group by id,name（上句按照id,name 为分组，计算mark的汇总值)nSelect id,name,avg(mark),sum(mark)from student group by id,name(上句按照id,name 为分组，计算mark的平均值)nSelect id,name,avg(mark)from student group by id(上一句语法是错误的，因为有一个字段name在分组之外，数据库不知道怎么归类计算)nSelect id,name,mark1+mark2 from student group by id,name(上一句也是错误的，mark1+mark2是两个字段间的计算，没有汇总计算，分组没有意义)10UNION 组合查询结果1)Union 组合查询结果,合并重复记录Select id,name from studentUnionSelect id,name from teacher2)Union All 组合查询结果，不合并重复记录Select id,name from studentUnion AllSelect id,name from teacher11多表关联(1)n内连接(inner join)n写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id=b.idn写法二:select a.id,a.name,b.id,b.name from student a inner join techer b on a.techer_id=b.id（写法二是标准语法)12多表关联(2)n外连接(outer join)n左外连接(left outer join)写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id*=b.id写法二:select a.id,a.name,b.id,b.name from student a left outer join techer b on a.techer_id=b.id （写法二是标准语法)n右外连接(right outer join)写法一:select a.id,a.name,b.id,b.name from student a,teacher b where a.techer_id=+b.id写法二:select a.id,a.name,b.id,b.name from student a right outer join techer b on a.techer_id=b.id（写法二是标准语法)13子查询n子查询的结果相当于一张tableSelect A.id,A.name,sum(A.mark)from(select id,name,mark from student where mark 60)A Group by A.id,A.namen子查询的结果相当于一个集合Select id,name from student where id in(select id from student where mark 60)很多数据库支持：Select id,name from student where(id,name)in(select id,name from student where mark 60)14Tea Break!15修改数据的SQL语句n修改数据的SQL语句nInsert (插入)nDelete (删除)nUpdate (修改)16INSERT语句n基本语法:INSERT INTO table(column1,column2,columnN)Values (value1,value2,valueN)例子:Insert into student(id,name,subject,mark)Values(25,李四,语文,60)17INSERT语句(2)n根据查询结果插入数据Insert into table1(select column1,column2,columnN from table2)Insert into student(select id,name,subject,mark from student2)注意:student表一定要刚好有 id,name,subject,mark四个字段18DELETE 语句-删除数据n情况一:全表删除 Delete from table1 (删除table1的全部数据)建议用 truncate 语句进行删除truncate table studentn情况二:组合Where子句 Delete from student where id=223434319UPDATE语句修改数据n语法:UPDATE table set column1=xxx,column2=xxx,columnN=xxxx where conditionSample:Update student set id=333 where name=张三20Tea Break!21管理数据定义的SQL语句nCREATE (创建数据库对象)nDROP (删除数据库对象)nALTER (修改数据库对象)
