MySQL数据库操作----增删改查

分类:数据库    发布时间:2017-11-15 09:21:23

创建表

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   


标签: mysql

阅读(188)┆ 评论(0) ┆ (0) ┆ 返回博客首页


发表我的评论

欢迎您: | 退出登录


文章评论