数据库基础培训SQL语言培训1ppt课件

1、数据库根底: SQL语法(1)北明软件员工培训讲义2章节n查询数据的SQL语句n修正数据的SQL语句n管理数据定义的SQL语句3查询数据的SQL语句nSELECT 语句4查询数据的SQL语句-SELECTn本章讲述如何运用SELECT语句的五个子句。n在SELECT语句中，他必需按以下顺序运用这些子句 ：nSELECT子句nFROM子句nWHERE子句nORDER BY子句nSELECT 字段1,字段2字段n FROM 表1,表 2表n5SELECT语句根底n可以用多种方法来查询数据库中的一个表。他可以裁剪SELECT语句来到达以下目的：n检索一切的或指定的列nselect id, name

2、from student 查询编号，姓名)nselect * from student(查询一切字段)n对检索所得的数据执行计算或其它函数nselect id, name, sum(mark) from student group by id, namen(按照编号、姓名为分组，对“成果进展汇总)n(简单的说，就是查询每个学生的总成果)6Where子句nWhere 子句用于限制数据nselect id, name from student where id=222233444n上句查找出编号为222233444的学生的学生编号和姓名nselect id,name from student wh

3、ere name like 张%n上句查找出姓名为“张开头的学生的学生编号和姓名n其它约束方法：nWhere column1 like _b% n(部分匹配, %为通配符,表示=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 (

4、字段间匹配) (=, =, , , .)n7Where子句(2)n多条件限制n1) select id, name from student where id 23456 and name like 张%n2) select id, name from student where id 23456 or name like 张%n3) select id, name from student where (id 23456 or name like 张%) and mark60nn不要思索条件匹配的先天优先级，在他想优先匹配的地方加上括号，永远是对的8Order by 子句 (用于排序)n单列

5、排序nSelect id, name from student order by id asc (升序)nSelect id, name from student order by id desc (降序)n多列排序nselect id, name ,mark from student order by mark desc , id ascn(先按照成果降序，假设成果一样，那么按照编号升序)nasc可以省略，由于默许是按照升序陈列但是desc不可以n如: select id, name from student order by id (升序)9Group by (用于汇总计算)nGROUP

6、中文意思： 分组nselect id, name,sum(mark) from student group by id, namen上句按照id,name 为分组，计算mark的汇总值)nSelect id, name, avg(mark),sum(mark) from student group by id, namen(上句按照id,name 为分组， 计算mark的平均值)nSelect id, name, avg(mark) from student group by idn(上一句语法是错误的， 由于有一个字段name在分组之外，数据库不知道怎样归类计算)nSelect id, na

7、me, mark1+mark2 from student group by id,namen(上一句也是错误的，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

8、join)n写法一: select a.id, , b.id, from student a, teacher b where a.techer_id=b.idn写法二: select a.id, , b.id, from student a inner join techer b on a.techer_id=b.id n写法二是规范语法)12多表关联(2)n外衔接(outer join)n左外衔接(left outer join)n写法一: select a.id, , b.id, from student a,

9、 teacher b where a.techer_id *=b.idn写法二: select a.id, , b.id, from student a left outer join techer b on a.techer_id=b.id 写法二是规范语法)n右外衔接(right outer join)n写法一: select a.id, , b.id, from student a, teacher b where a.techer_id =+ b.idn写法二: select a.id, , b.id, fr

10、om student a right outer join techer b on a.techer_id=b.idn写法二是规范语法)13子查询n子查询的结果相当于一张tablenSelect A.id, A.name, sum(A.mark) from ( nselect id, name, mark from student where mark 60n) A Group by A.id, A.namen子查询的结果相当于一个集合nSelect id, name from student where id in (nselect id from student where mark 60

11、n)n很多数据库支持： 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根本语法:nINSERT INTO table (column1, column2, columnN)nValues ( value1, value2, valueN)n例子:nInsert into student (i

12、d, name, subject, mark)nValues ( 25, 李四, 语文, 60)17INSERT语句 (2)n根据查询结果插入数据nInsert into table1 ( nselect column1, column2, , columnN from table2n)nnInsert into student (nselect id, name, subject, mark from student2n)n留意: student表一定要刚好有 id, name, subject, mark四个字段18DELETE 语句 - 删除数据n情况一:全表删除 nDelete from table1 (删除table1的全部数据)n建议用 truncate 语句进展删除ntruncate table studentn情况二: 组合Where子句n Delete from student where id = 223434319UPDATE语句修正数据n语法:nU