创建表
create table table1(name varchar(10),age int,sex char(10));
1.增
insert into table(name,age,sex) value(tom,18,boy);
2.删
DELETE 语句的基本格式如下所示:
DELETE FROM tbl_name WHERE which rows to delete;
其中,WHERE子句是可选的,它用于指定需要删除掉哪些行。如果没有WHERE 子句,那么将删除表里面的所有行。这意味着,越简单的DELETE语句越危险,例如下面的语句:
DELETE FROM tbl_name
它会彻底删除表里的内容,因此务必要小心使用。
如果不清楚某条DELETE语句到底会删除哪些行,那么最好把这条语句的WHERE子句放到一条SELECT 语句里,看看这条SELECT 语句能查出哪些行。这有助于确认:它们的确是你想要删除的那些行,而且只有这些,不能多也不能少。
如下是一个使用示例:
mysql> select * from student; +--------+-----+------------+ | name | sex | student_id | +--------+-----+------------+ | Jim | M | 1 | | Tom | M | 2 | | Lily | F | 3 | | Lucy | F | 4 | | Travis | M | 5 | | Steve | M | 6 | +--------+-----+------------+ 6 rows in set (0.00 sec) mysql> delete from student where sex='F'; Query OK, 2 rows affected (0.01 sec) mysql> select * from student; +--------+-----+------------+ | name | sex | student_id | +--------+-----+------------+ | Jim | M | 1 | | Tom | M | 2 | | Travis | M | 5 | | Steve | M | 6 | +--------+-----+------------+ 4 rows in set (0.00 sec)
3. 改
如果想要修改已有记录行,则需要使用UPDATE语句,其基本格式如下:
UPDATE tbl_name SET which columns to change WHERE which rows to update;
这里的WHERE子句的情况与DELETE语句里的相似,它是可选的,因此在没有指定它时,会更新表里的每一行。例如,下面的语句会把每一位学生的 name 更改为 George:
mysql> UPDATE student SET name='George';
很显然,必须谨慎对待这类查询,所以通常情况下都需要加上一个WHERE子句,用它来更具体地指出需要更行那些行。
假设在 student 表里添加了一行,但忘记指定 sex 列的内容,默认地,该列会被设置为 'F':
mysql> INSERT INTO student (name) -> VALUES('Julian'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM student; +--------+-----+------------+ | name | sex | student_id | +--------+-----+------------+ | Jim | M | 1 | | Tom | M | 2 | | Travis | M | 5 | | Steve | M | 6 | | Hellen | M | 7 | | Julian | F | 8 | +--------+-----+------------+ 6 rows in set (0.00 sec)
此时,可以用一条UPDATE语句来进行修复,其中包含一条WHERE子句,用以标识需要更新的那一行:
4、查
select * from table;查表中所有字段
select name from table;查表中的name字段;
select name,age from table;查表中的name和age字段;
select * from table where name='tom';查表中name为tom的行;
select * from table limit 1; 查找表中第一行
select * from table limit 0,3; 查找表中第一行到第三行
SELECT * FROM 表名 ORDER BY 表_ID DESC LIMIT 1 查找表中最后一行,order by表示排序查询,--降序DESC,升序ASC,
select grade from Achievement where course='english' order by grade
desc limit 1; 查找表的某一类中排名最高的,比如在总成绩的表中,查找英语成绩最高的
select Grade from Achievement where Grade BETWEEN 70 AND 80;查找分数在70分到80分之间的,BETWEEN ... AND指到范围查找,
select avg(grade) as "平均成绩",max(grade) as "最高成绩",min(grade) as "最低成绩" from table where
cno='c02'; 查看table表中c02课程的平均成绩,最高成绩和高低成绩
select student.Sname,avg(sc.grade),count(sc.cno) from student,sc where student.sno = sc.sno group by student.Sname having count(sc.cno) >2; 查询选课门数超过2门的学生的平均成绩和选课门数。having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
having和where的区别:
作用的对象不同。WHERE 子句作用于表和视图,HAVING 子句作用于组。
select sno,sum(grade) from sc group by sno having sum(grade) > 200;列出总成绩超过200分的学生,要求列出学号、总成绩。
select sname,sdept from student,sc where student.sno=sc.sno and cno='c02'; 查询选修了c02号课程的学生的姓名和所在系。
select sname,cno,grade from student,sc where student.sno=sc.sno and sc.grade > 80 order by grade desc;查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。
select sname,ssex,sdept,grade from student,sc where student.sno=sc.sno and student.ssex='男' and student.sdept='计算机系' and sc.cno='c04';查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。
select sname,cname,grade from student,course,sc where sc.grade!='' and student.sno=sc.sno and course.cno=sc.cno;查询有考试成绩的所有学生的姓名、修课名称及考试成绩
select sname,ssex,cname,grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and student.sdept='计算机系';分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,
子查询
子查询允许把一个查询嵌套在另一个查询当中。
子查询,又叫内部查询,相对于内部查询,包含内部查询的就称为外部查询。
子查询可以包含普通select可以包括的任何子句,比如:distinct、 group by、order by、limit、join和union等;但是对应的外部查询必须是以下语句之一:select、insert、update、delete、set或 者do。
用法:
WHERE column IN (value1,value2,...)
WHERE column NOT IN (value1,value2,...)
1、in 后面是记录集,如:
select * from table where uname in(select uname from user);
2、in 后面是字符串,如:
select * from table where uname in('aaa',bbb','ccc','ddd','eee',ffff'');
注意:这里一定要将字符串用单引号'' 标注起来;
示例:
select cname as "课程",count(*) as "选修人数",max(grade) from sc,course where sc.cno=course.cno group by cname;统计每门课程(cname)的修课人数(使用count统计)和考试最高分(max(grade))。两张表下字段匹配sc.cno=course.cno,group by语法可以根据给定数据列(cname)的每个成员对查询结果进行分组统计,最终得到一个分组汇总表。
mysql> select t1.sname as "年龄相同的人",t1.sage as "年龄" from student t1 inner join student t2 on t1.sage in(select sage from student where t1.sage=t2.sage and t1.sname!=t2.sname) group by t1.sname,t1.sage order by t1.sage
;查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。
select cno,cname from course where cno NOT in(select cno from sc);查询哪些课程没有人选,要求列出课程号和课程名。
用子查询实现如下查询: --
(1) 查询选修了C01号课程的学生的姓名和所在系。
select distinct student.sname,student.sdept from student,sc where student.sno in(select sc.sno from sc where sc.cno='c01');
(2) 查询数学系成绩80分以上的学生的学号、姓名
select sno,sname from student where sno in(select sno from sc where sc.grade > 80) and sno in(select sno from student where sdept='数学系');
(3) 查询计算机系学生所选的课程名.
select cname from course where cno in(select cno from sc where sc.sno in(select sno from student where student.sdept='计算机系'));
某个员工信息表结构和数据如下:
id name dept salary edlevel hiredate
1 张三 开发部 2000 3 2009-10-11
2 李四 开发部 2500 3 2009-10-01
3 王五 设计部 2600 5 2010-10-02
4 王六 设计部 2300 4 2010-10-03
5 马七 设计部 2100 4 2010-10-06
6 赵八 销售部 3000 5 2010-10-05
7 钱九 销售部 3100 7 2010-10-07
8 孙十 销售部 3500 7 2010-10-06
例如,我想列出每个部门最高薪水的结果,sql语句如下:
SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT
查询结果如下:
DEPT MAXIMUM
开发部 2500
设计部 2600
销售部 3500
解释一下这个结果:
1、满足“SELECT子句中的列名必须为分组列或列函数”,因为SELECT有GROUP BY DEPT中包含的列DEPT。
2、“列函数对于GROUP BY子句定义的每个组各返回一个结果”,根据部门分组,对每个部门返回一个结果,就是每个部门的最高薪水。
注意:计算的是每个部门(由 GROUP BY 子句定义的组)而不是整个公司的 MAX(SALARY)。
例如,查询每个部门的总的薪水数
SELECT DEPT, sum( SALARY ) AS total FROM STAFF GROUP BY DEPT
查询结果如下:
DEPT total
开发部 4500
设计部 7000
销售部 9600
二、将 WHERE 子句与 GROUP BY 子句一起使用.
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准 WHERE 子句。必须在GROUP BY 子句之前指定 WHERE 子句。
例如,查询公司2010年入职的各个部门每个级别里的最高薪水
SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUM FROM staff WHERE HIREDATE > '2010-01-01' GROUP BY DEPT, EDLEVEL ORDER BY DEPT, EDLEVEL
查询结果如下:
DEPT EDLEVEL MAXIMUM
设计部 4 2300
设计部 5 2600
销售部 5 3000
销售部 7 3500
注意:在SELECT语句中指定的每个列名也在GROUP BY子句中提到。未在这两个地方提到的列名将产生错误。
GROUP BY子句对DEPT和EDLEVEL的每个唯一组合各返回一行。
三、在GROUP BY子句之后使用HAVING子句
可应用限定条件进行分组,以便系统仅对满足条件的组返回结果。为此,在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。每个谓词将组特性(如AVG(SALARY))与下列之一进行比较:
例如:寻找雇员数超过2个的部门的最高和最低薪水:
SELECT DEPT, MAX( SALARY ) AS MAXIMUM, MIN( SALARY ) AS MINIMUM FROM staff GROUP BY DEPT HAVING COUNT( * ) >2 ORDER BY DEPT
查询结果如下:
DEPT MAXIMUM MINIMUM