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

2018-10-29 本文已影响0人雁_9587

查询

找出选修多门课程的学生
select sno
from scores
group by sno
having count(sno)>1
判断不等于最大的分数
SELECT
FROM scores
WHERE degree !=
找出最大分数的
SELECT MAX(degree)
FROM scores
举例:
-- 找出选修多门课程的学生
select sno
from scores
group by sno
having count(sno)>1
-- 判断不等于最大的分数
SELECT
FROM scores
WHERE degree !=
-- 找出最大分数的
SELECT MAX(degree)
FROM scores
举例

select  *

from scores
where degree !=(SELECT  MAX(degree) 
FROM scores) and sno in (SELECT sno
FROM scores
GROUP BY sno
HAVING COUNT(sno)>1)
FROM scores) and sno in (SELECT sno
FROM scores
GROUP BY sno
HAVING COUNT(sno)>1)

查询学习课程两门以上的学生信息
1.查询学习课程两门
SELECT
FROM scores
GROUP BY sno
HAVING COUNT(sno)>1;
2.学生信息
SELECT *
FROM students
WHERE sno IN(101,107,108,109)
查询各个男学生的学习成绩
1.查询各个男学生
SELECT
FROM students
WHERE ssex=0;
2.的学习成绩
SELECT degree
FROM scores
WHERE sno IN (SELECT sno
FROM students
WHERE ssex=0)
查询各个女学生的任课老师姓名,课程名是
1.查询各个女学生的
SELECT sno
FROM students
WHERE ssex=1;

表名(字段名)

1.INSERT INTO 表名(字段名1,字段名2)VALUES (字段值1,字段值2)
2.插入那里去表名(1-n个字段,逗号分隔)VALUES(1-n个数据的值)

   INSERT  INTO t_user_user(cst_id,user_name,pwd,STATUS)
VALUES(1145,'guhy666','qweasd',0);

INSERT INTO t_cst_customer(cst_id,cst_name,phone,certNo,province,city,STATUS,sex,email)
VALUES(1175,'顾虹雁','18815123698','695865412154','上海','闵行',0,0,'160769204@qq.com');

INSERT INTO 表名(字段名1,字段名2)VALUES (字段值1,字段值2);
INSERT INTO t_cst_account(account_name,cst_id,balance,STATUS)
VALUES ('guhy','1066','6688',0);

备份(删除,清空)

DELETE FROM 表名;删除全表数据
DELETE FROM 表名 WHERE 条件;删除指定数据
DELETE FROM t_user_user WHERE user_id='8889';

只备份表结构
         CREATE TABLE 临时表名 AS SELECT * FROM  源表名 WHERE 1=2;
只备份表结构+数据
         CREATE TABLE 临时表名 AS SELECT * FROM   源表名 where1-1;
 备份数据
 CREATE TABLE t_user_user_guhy AS SELECT *FROM t_user_user WHERE 1=2;
 CREATE TABLE t_user_user_back1 AS SELECT *FROM t_user_user WHERE 1=1;   
 
 删除表 DROP TABLE 表名;
 DELETE FROM  t_user_user_back1;
 DROP TABLE t_user_user_guhy;
 
 清空表  DELETE FROM 表名;