2018-10-29数据库增删改&备份&统计类界面

思维导图

数据库增删改&备份&统计类界面.png
增删改
delete from 删除
格式 delete from 表名 where 条件
update 表名 set 设置 字段名=字段值,字段名=字段值 where 条件 改
inset into 表名(字段一,字段2...))values(值一,值二...)
字段和值要对应

表备份 固定格式
只备份结构 create table 临时表名 as select * from 源表名 where 1=2
只备份结构+数据 create table 临时吧表名 as select *from 源表名 where 1=1

INSERT INTO t_user_user(cst_id,user_name,pwd,STATUS)VALUES(1188,'lizy','qwe123',0);

INSERT INTO t_cst_customer(cst_id,cst_name,phone,certNo,province,city,STATUS,sex,email)
VALUES(1314,'李中洋','11132131111','353434343555555777','河南','平顶山',0,0,'344654654@qq.com');

INSERT INTO t_cst_account(accout_id,account_name,cst_id,balance,STATUS,operate_user_id)
VALUES(1314,'lizy',1119,90000,0,0);

UPDATE  表名  SET 字段名  WHERE;
更新   表     设置 字段名=字段值  where条件
UPDATE   t_user_user
SET pwd='qwe'
WHERE  pwd='qwe123'

UPDATE   t_user_user
SET pwd='qweasd123'
WHERE  pwd='lizy'


UPDATE   t_cst_customer
SET cst_name='lizy'
WHERE  cst_name='李中洋'

DELETE FROM 表名 ;删除全表数据
DELETE FROM 表名 WHERE 条件;删除全部数据
DELETE FROM t_user_user  WHERE  user_id='1046'

-- 只备份表结构
   CREATE  TABLE  临时表名  AS  SELECT  *   FROM  源表名 WHERE 1=2
-- 备份表结构+数据
   CREATE  TABLE  临时表名  AS  SELECT  *   FROM  源表名 WHERE 1=1
   CREATE  TABLE t_user_user_lizy  AS SELECT * FROM  t_user_user WHERE 1=2
CREATE  TABLE t_user_user_lizy  AS SELECT * FROM  t_user_user WHERE 1=1


删除表  DROP  TABLE  表名;
DROP TABLE t_user_user_lizy

清空表  DELETE  FROM  表名;





SELECT *
FROM students st JOIN scores sc
ON st.`sno`=sc.`sno`
WHERE degree > (SELECT MIN(degree)
FROM scores
);
-- 
SELECT AVG(degree)
FROM scores;

SELECT st.`sname`,sc.`sno`,sc.`degree`
FROM scores sc JOIN students st
ON  sc.`sno`=st.`sno`
WHERE degree>(SELECT AVG(degree)
FROM scores);
-- 最小成绩的学生的任课老师姓名是
SELECT MIN(degree)
FROM scores;

SELECT te.`tname`
FROM scores sc JOIN teachers te JOIN courses co
ON sc.`cno`=co.`cno` AND co.`tno`=te.`tno`
WHERE degree=(SELECT MIN(degree)
FROM scores);
-- 查询学习课程1门以上学生信息
SELECT sno
FROM scores
GROUP BY sno
HAVING COUNT(sno)>1;

SELECT *
FROM students
WHERE sno IN (SELECT sno
FROM scores
GROUP BY sno
HAVING COUNT(sno)>1);


SELECT sc.`cno`
FROM students st JOIN scores sc 
ON st.`sno`=sc.`sno`
WHERE ssex=1;


SELECT co.`cname`,te.`tname`
FROM students st JOIN scores sc JOIN courses co JOIN teachers te
ON (st.`sno`=sc.`sno`)AND (sc.`cno`=co.`cno`)AND(co.`tno`=te.`tno`)
WHERE ssex=1;
-- 查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT *
FROM scores sc JOIN courses co JOIN students st
ON sc.`cno`=co.`cno`AND sc.`sno`=st.`sno`
WHERE cname='计算机导论'AND ssex=0;
-- 查询和“李军”同性别并同班的同学Sname.
SELECT ssex,class
FROM students
WHERE sname='李军'

SELECT sname
FROM students
WHERE ssex=(SELECT ssex
FROM students
WHERE sname='李军') AND class=(SELECT class
FROM students
WHERE sname='李军');
-- 查询与曾华同一班级的学生信息
SELECT *
FROM students
WHERE class=(SELECT class  FROM students WHERE sname='曾华')
-- 查询与李军同一班的女生有哪些
SELECT sname
FROM students
WHERE class=(SELECT class FROM students WHERE sname='李军')AND ssex=1```