数据库培训4.ppt

1、1、DML数据操作语言 1）insert语句 2）update语句 3）delete语句 4）select单表查询语句,数据库培训4,表结构 Create table student_s( Sid int primary key, Sname varchar(20) default , Scourse varchar(20), Sscore int ),1、insert语句 Insert into table_name(col_name) values(expr | DEFAULT,); Insert into table_name(col_name) select语句 INSERT INTO

2、 student_s VALUES(6, 张三,英语,75); INSERT INTO student_s VALUES(6,DEFAULT,英语,75); INSERT INTO student_s_copy SELECT * FROM student_s;,2、update语句 Update table_name set col_name1=expr1, where 语句 UPDATE student_s SET sscore = 100 WHERE sid = 2 UPDATE student_s SET sscore=(SELECT sscore FROM student_s_copy

3、 WHERE sid = 1) WHERE sid = 2 UPDATE student_s SET sscore=90 WHERE sname = 李四 ORDER BY sid LIMIT 2(mysql的特殊用法),3、delete语句 Delete from tbl_name where语句 DELETE from student_s WHERE sid = 5 DELETE FROM student_s LIMIT 3(mysql，只删除多少行) TRUNCATE TABLE tbl_name TRUNCATE TABLE用于完全清空一个表，不可回滚。 对于被外键引用的表无法使用tr

4、uncate table,4、select单表查询语句 1）查询出表中所有内容 SELECT * FROM student_s; 2）查询出指定列的内容 SELECT sname,scourse,sscore from student_s; 3) 去除重复列 SELECT DISTINCT sname from student_s 4)列别名 SELECT sname as 姓名,scourse as 课程,sscore as 成绩 from student_s;,5、聚合函数 1)求和函数SUM() 2)计数函数COUNT() 3)最大/最小值函数MAX()/MIN() 4)平均值函数AVG

5、() Select sum(sscore) as 总分from student_s; Select count(*) as 行数from student_s; Select count(DISTINCT sname) as 行数 from student_s; Select max(sscore) as 最高分 from student_s; Select avg(sscore) as 平均分 from student_s;,6、where条件 Select * from student_s Where sid = 1 多条件 Select * from student_s Where sid

6、 = 1 or sid = 2; Select * from student_s where sname = 张三 and scourse = 数学; BETWEEN AND Select * from student_s where Sscore between 80 and 100;,IN/NOT IN 条件 Select * from student_s Where sscore in (80,85,90); 6、group by语句 Select sname,SUM(sscore) from student_s GROUP BY sname; 7、 HAVING语句，与group by

7、在一起使用 Select sname,SUM(sscore) as total from student_s GROUP BY sname HAVING total 165;,8、order by 语句 SELECT * FROM student_s ORDER BY sscore; 默认升序排序asc,降序用desc SELECT * FROM student_s ORDER BY sscore desc; 9、mysql中的limit说明 LIMIT子句可以被用于限制被SELECT语句返回的行数。 LIMIT offset, row_count offset指定返回的第一行的偏移量 row_count指定返回的行数的最大值,10、嵌套查询 查询所有课程成绩都在85分以上的学生信息 Select * from student_s where sname not in( Select sname from stud