数据库查询练习题大全,与表约束关系建立

-- 查询薪金和佣金之和
SELECT sal,comm,sal+comm FROM emp;
-- 如果该值是空的话就当0处理
SELECT sal,comm,sal+IFNULL(comm,0) as total FROM emp;
-- 排序
-- 按薪金排序(默认升序ASC)  降序desc
SELECT * FROM emp ORDER BY sal DESC;
-- 5.3　查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC,empno DESC;
-- 查询表的记录数
SELECT COUNT(*) FROM emp;
--  查询佣金的记录数
-- count(字段)查询不为空的记录数
SELECT COUNT(comm),COUNT(sal) FROM emp;
-- 查询emp表中月薪大于2500的人数：
SELECT COUNT(sal) FROM emp WHERE sal >=2500;
-- 统计月薪与佣金之和大于2500元的人数：
SELECT COUNT(*) FROM emp WHERE sal+IFNULL(comm,0) > 2500;
-- 查询有佣金的人数，有领导的人数--统计非空行数
SELECT COUNT(comm),COUNT(mgr) FROM emp;
-- 查询薪金总和
SELECT SUM(sal) FROM emp;
-- 查询所有雇员月薪和，以及所有雇员佣金和：
SELECT SUM(sal),SUM(comm) FROM emp;
-- 查询所有雇员月薪+佣金和：
SELECT SUM(sal)+SUM(comm) FROM emp;
-- 统计所有员工平均工资：
SELECT AVG(sal) FROM emp;

SELECT MAX(sal) FROM emp;
SELECT MIN(sal) FROM emp;
-- 查询每个部门的编号和每个部门的工资和
-- 分组查询时查询的字段必须是分组的字段(记录数要对应);
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 查询每个部门的编号以及每个部门的人数
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
-- 查询每个部门的部门编号以及每个部门工资大于1500的人数：
SELECT deptno,COUNT(*) FROM emp WHERE sal >1500 GROUP BY deptno;
-- 查询工资总和大于9000的部门编号以及工资和：
-- having关键字是分组之后使用的的 筛选条件(可以使用函数)

SELECT
    deptno,
    SUM(sal)
FROM
    emp
GROUP BY
    deptno
HAVING
    SUM(sal) > 9000;
-- 分页查询
-- 0 起始页
-- 3 一共显示几条数据
SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 3,3;
SELECT * FROM emp LIMIT (x-1)*y,y;
-- 模糊查询
-- %代表0~n个字符
-- _代表一个字符
SELECT * FROM emp WHERE ename LIKE 'w%';
SELECT * FROM emp WHERE ename LIKE 'w___';
-- 主键

-- 一对一表关系
-- 例如QQ和QQ的详细信息
-- 如何外键的时候,如何明确主从关系?
-- 谁引用了谁被引用的表是主表 外键在从表中建然后关联主表
create table qq(
  qqid int PRIMARY KEY,
  PASSWORD VARCHAR(10)
);
CREATE TABLE qqdetail(
   qqid int PRIMARY KEY,
   qname VARCHAR (10) NOT NULL,
   qgend VARCHAR(10) DEFAULT 'nan' 
);
-- 建立表关系

ALTER TABLE qqdetail ADD CONSTRAINT fk_qq_qqdetail FOREIGN KEY (qqid) REFERENCES qq (qqid); 

/*
student表
sid sname   
1   wl      
2   dp      
3   zb  
这样会出现 大量的重复数据 把表进行 拆分(一张表 一套增删改查)
分数表
sid score cid
1   100    1
1   50     2
1   100    3
2   99     1
3   11     1
3   11     2
科目表
cid  cname
1    java
2     h5
3    u3d
4     ios
*/
CREATE TABLE Student(
   sid int PRIMARY KEY,
   sname VARCHAR(10)
);

CREATE TABLE score(
   sid int,
   score int,
   cid int   
);

CREATE TABLE coures(
   cid int PRIMARY KEY,
   cname VARCHAR(10)
);
ALTER TABLE score ADD CONSTRAINT s_s FOREIGN KEY (sid) REFERENCES Student (sid); 
ALTER TABLE score ADD CONSTRAINT s_c FOREIGN KEY (cid) REFERENCES  coures(cid); 

 CREATE TABLE A(
   name VARCHAR(10),
   score int
);
 CREATE TABLE B(
   name VARCHAR(10),
   score int
);
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO B VALUES('a',10),('b',20),('c',30);

-- 合并查询 返回值结果集字段的类型数量要一致
-- 单独使用union 会把两张表的数据合并到一起 并过滤相同数据
-- 不想过滤使用 UNION ALL
SELECT name,score FROM A
UNION ALL
SELECT  name,score FROM B;
-- 链接查询 
-- 查询两张表的数据
-- 4*5产生20条数据 
-- 这样无用的东西可以通过外键过滤掉
-- 查询所有学生的成绩 考过试才输出 99查询法
SELECT * FROM Student,score where Student.sid=score.sid;
-- 链接查询 内连接查询 外连接查询 左外连接  
-- 内连接 相当于把两张表连接到一起查询
SELECT
    s.sid,s.sname,c.score
FROM
    Student s,
    score c
WHERE
    s.sid=c.sid;
-- 查询学生的成绩要求50分以上
SELECT
    s.sid,s.sname,c.score
FROM
    Student s,
    score c
WHERE
    s.sid=c.sid AND c.score>30;
-- 查询所有学生的成绩
-- 使用 inner join 关键字 条件使用 on (inner可以省略不写)
select * FROM Student s INNER JOIN score c ON s.sid = c.sid;
-- 左外连接查询 left
-- 以左边的表为主 主表中的数据都会查询出来(有可能会产生无用的数据)
-- 右外连接 right
SELECT s.sid,s.sname,c.score FROM Student s  LEFT JOIN  score c ON s.sid=c.sid;
-- 外键是约束数据的插入 查询就是查询 跟约束无关
-- 自然连接
-- 会自动匹配表中相同的字段 没有外键也能自然查询
SELECT * FROM Student NATURAL JOIN score;
-- 多表查询
-- 查询所有学生的分数和考试科目
-- 查询3张表2条件 你张表n-1个条件

SELECT
    s.sid,
    s.sname,
    c.score,
    e.cname
FROM
    Student s
JOIN score c ON s.sid = c.sid
JOIN coures e ON c.cid = e.cid;
-- 子查询
-- 出现多次select关键字的查询语句
CREATE TABLE dept(
    deptno INT,
    dname varchar(14),
    loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
-- 查询与SCOTT同一个部门的员工

SELECT * FROM emp WHERE deptno = (
select deptno from emp WHERE ename = 'SCOTT');
-- 查询工资高于30号部门所有部门的所有信息
SELECT * FROM emp WHERE sal > (
SELECT MAX(sal) FROM emp WHERE deptno=30
);
-- 查询工作和工资与MARTIN 完全相同的员工信息
select * FROM emp WHERE sal=(
select sal FROM emp WHERE ename='MARTIN') AND job=(
select job FROM emp WHERE ename='MARTIN');

select * FROM emp WHERE (job,sal) in(
select job,sal FROM emp WHERE ename='MARTIN');
-- 有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno in(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr) >=2
);
-- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
SELECT e.ename,e.sal,e.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=7788;
SELECT e.ename,e.sal,e.deptno,d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno AND empno=7788;
--  求7369员工编号、姓名、经理编号和经理姓名
-- 自连接全部数据都在一张表中 但是不能一次查出来
SELECT e.deptno,e.ename,e.mgr,d.ename 经理 FROM emp e , emp d WHERE e.mgr=d.empno AND e.empno=7369;
-- 求各个部门薪水最高的员工的所有信息查询过程出现问题 查询时没有把部门的条件带进去
-- 只是把按最高工资处理
-- 把查询结果集当成一张新表使用
SELECT * FROM emp WHERE sal in (SELECT MAX(sal) FROM emp GROUP BY deptno);

SELECT * FROM emp e1,(
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno) e2 
WHERE e1.sal = e2.sal AND e1.deptno=e2.deptno;

数据库
1.对库/表进行操作
create alter drop
查询表字段的desc 表名
desc emp;
pri 主键
mul 可重复的键值(外键)
nui唯一性
2.对表数据的操作语句
插入,更新,删除
3.查询
条件查询 where
分组查询 group by
分页查询 limit
模糊查询 like
排序查询 order by
聚合函数查询 sum avg count max min
子查询 多个select嵌套
链接查询 join left right
自然连接查询 NATURAL JOIN
自连接查询 把自己当做新表使用
把查询结果当做一个表使用
合并查询 UNION ALL

-- 创建一个Student表 id(主键) name
-- 在创建表的时候可以保持数据的完整性
-- 主键特点:1.唯一 2.不能是空的
CREATE TABLE student(
   id int PRIMARY KEY,
    name VARCHAR(50)
);
-- 插入数据
INSERT INTO student VALUES(1,'wanglong'),(2,'wangsong');
SELECT * FROM student;
-- 创建方式2
-- 好处:可以添加联合主键

CREATE TABLE student1(
   id int,
    name VARCHAR(50),
     PRIMARY KEY(id)
);
-- 联合创建主键,主键的内容不能完全一样,不完全一样都可以插入数据库中
CREATE TABLE student2(
   id int,
    stuid int,
    name VARCHAR(50),
     PRIMARY KEY(id,stuid)
);
insert into student2 values(1,2,'qq'),(1,1,'qq'),(2,1,'qq');

CREATE TABLE student3(
   id int,
    name VARCHAR(50);
);
-- 通过修改表结构  添加主键yue
alter TABLE student3 ADD CONSTRAINT PRIMARY KEY(id);
-- 唯一约束 不能重复 (可以有空值)
CREATE TABLE student4(
   id int PRIMARY KEY,
    name VARCHAR(50) UNIQUE
);
-- 自动增长列(auto_increment)
CREATE TABLE student5(
   id int PRIMARY KEY auto_increment,
    name VARCHAR(50)
);

INSERT INTO student5 VALUES(2,'wang');
INSERT INTO student5 VALUES(NULL,方'wang1');
INSERT INTO student5 VALUES(NULL,'wang2');
INSERT INTO student5 VALUES(NULL,'wang3');
-- 域的完整性限制单元格的数据内容
-- 值不能为空 和 默认值
CREATE TABLE student6(
   id int PRIMARY KEY,
    name VARCHAR(50) not null,
     sex varchar(20) DEFAULT '男'
);
INSERT INTO student6 (id,name)VALUES(1,'wang');
-- 引用完整性(外键约束 把表与表之间通过字段建立联系)
create table Student7(
    sid int PRIMARY KEY,
    name VARCHAR(20)  
);
-- 从表
create table score(
    sid int,
    score int,
  -- 创建外键 建立表联系
-- --FOREIGN KEY(当前表的字段)
CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES Student7(sid)
);

-- 建立多对多关系的时候 实际上就是建立外键
-- 1.明确:在那张表建立外键(中间表)
-- 2.哪个字段与哪个字段建立联系,
-- 中间表的tid与老师表的tid 建立外键关系
-- 中间表的tid与学生表的sid 建立外键关系

create table teacher (
  tid int primary key,
  name varchar(20)
);
create table Student(
  sid int primary key,
  sname varchar(10)
);
create table ts(
  tid int,
  sid int,
  think varchar(10),
  CONSTRAINT ts_t FOREIGN KEY(sid) REFERENCES Student(sid),
  CONSTRAINT ts_s FOREIGN KEY(tid) REFERENCES teacher(tid)
);