查询
- 找出选修多门课程 的学生
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 表名;
数据库增删改备份统计类界面


聚合函数

增删改

表备份

扩展

网友评论