-- 查询薪金和佣金之和SELECT sal,comm,sal+comm FROM emp;-- 如果该值是空的话就当0处理SELECT sal,comm,sal+IFNULL(comm,0) as total FROM emp;-- 排序-- 按薪金排序(默认升序ASC) 降序descSELECT * FROM emp ORDERBY sal DESC;-- 5.3 查询所有雇员,按月薪降序排序,如果月薪相同时,按编号升序排序SELECT * FROM emp ORDERBY sal DESC,empno DESC;-- 查询表的记录数SELECTCOUNT(*) FROM emp;-- 查询佣金的记录数-- count(字段)查询不为空的记录数SELECTCOUNT(comm),COUNT(sal) FROM emp;-- 查询emp表中月薪大于2500的人数:SELECTCOUNT(sal) FROM emp WHERE sal >=2500;-- 统计月薪与佣金之和大于2500元的人数:SELECTCOUNT(*) FROM emp WHERE sal+IFNULL(comm,0) > 2500;-- 查询有佣金的人数,有领导的人数--统计非空行数SELECTCOUNT(comm),COUNT(mgr) FROM emp;-- 查询薪金总和SELECTSUM(sal) FROM emp;-- 查询所有雇员月薪和,以及所有雇员佣金和:SELECTSUM(sal),SUM(comm) FROM emp;-- 查询所有雇员月薪+佣金和:SELECTSUM(sal)+SUM(comm) FROM emp;-- 统计所有员工平均工资:SELECTAVG(sal) FROM emp;SELECTMAX(sal) FROM emp;SELECTMIN(sal) FROM emp;-- 查询每个部门的编号和每个部门的工资和-- 分组查询时查询的字段必须是分组的字段(记录数要对应);SELECT deptno,SUM(sal) FROM emp GROUPBY deptno;-- 查询每个部门的编号以及每个部门的人数SELECT deptno,COUNT(*) FROM emp GROUPBY deptno;-- 查询每个部门的部门编号以及每个部门工资大于1500的人数:SELECT deptno,COUNT(*) FROM emp WHERE sal >1500GROUPBY deptno;-- 查询工资总和大于9000的部门编号以及工资和:-- having关键字是分组之后使用的的 筛选条件(可以使用函数)SELECT
deptno,
SUM(sal)
FROM
emp
GROUPBY
deptno
HAVINGSUM(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的详细信息-- 如何外键的时候,如何明确主从关系?-- 谁引用了谁被引用的表是主表 外键在从表中建然后关联主表createtable qq(
qqid intPRIMARYKEY,
PASSWORD VARCHAR(10)
);CREATETABLE qqdetail(
qqid intPRIMARYKEY,
qname VARCHAR (10) NOTNULL,
qgend VARCHAR(10) DEFAULT'nan'
);-- 建立表关系ALTERTABLE qqdetail ADDCONSTRAINT fk_qq_qqdetail FOREIGNKEY (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
*/CREATETABLE Student(
sid intPRIMARYKEY,
sname VARCHAR(10)
);CREATETABLE score(
sid int,
score int,
cid int
);CREATETABLE coures(
cid intPRIMARYKEY,
cname VARCHAR(10)
);ALTERTABLE score ADDCONSTRAINT s_s FOREIGNKEY (sid) REFERENCES Student (sid);ALTERTABLE score ADDCONSTRAINT s_c FOREIGNKEY (cid) REFERENCES coures(cid);CREATETABLE A(
name VARCHAR(10),
score int
);CREATETABLE B(
name VARCHAR(10),
score int
);INSERTINTO A VALUES('a',10),('b',20),('c',30);INSERTINTO B VALUES('a',10),('b',20),('c',30);-- 合并查询 返回值结果集字段的类型数量要一致-- 单独使用union 会把两张表的数据合并到一起 并过滤相同数据-- 不想过滤使用 UNION ALLSELECT name,score FROM A
UNIONALLSELECT 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 INNERJOIN score c ON s.sid = c.sid;-- 左外连接查询 left-- 以左边的表为主 主表中的数据都会查询出来(有可能会产生无用的数据)-- 右外连接 rightSELECT s.sid,s.sname,c.score FROM Student s LEFTJOIN score c ON s.sid=c.sid;-- 外键是约束数据的插入 查询就是查询 跟约束无关-- 自然连接-- 会自动匹配表中相同的字段 没有外键也能自然查询SELECT * FROM Student NATURALJOIN 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关键字的查询语句CREATETABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);INSERTINTO dept values(10, 'ACCOUNTING', 'NEW YORK');INSERTINTO dept values(20, 'RESEARCH', 'DALLAS');INSERTINTO dept values(30, 'SALES', 'CHICAGO');INSERTINTO dept values(40, 'OPERATIONS', 'BOSTON');-- 查询与SCOTT同一个部门的员工SELECT * FROM emp WHERE deptno = (
select deptno from emp WHERE ename = 'SCOTT');-- 查询工资高于30号部门所有部门的所有信息SELECT * FROM emp WHERE sal > (
SELECTMAX(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 GROUPBY mgr HAVINGCOUNT(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 (SELECTMAX(sal) FROM emp GROUPBY deptno);SELECT * FROM emp e1,(
SELECT deptno,MAX(sal) FROM emp GROUPBY deptno) e2
WHERE e1.sal = e2.sal AND e1.deptno=e2.deptno;
数据库
1.对库/表进行操作
createalterdrop
查询表字段的desc 表名
desc emp;
pri 主键
mul 可重复的键值(外键)
nui唯一性
2.对表数据的操作语句
插入,更新,删除
3.查询
条件查询 where
分组查询 group by
分页查询 limit
模糊查询 like
排序查询 order by
聚合函数查询 sum avg count max min
子查询 多个select嵌套
链接查询 joinleftright
自然连接查询 NATURALJOIN
自连接查询 把自己当做新表使用
把查询结果当做一个表使用
合并查询 UNIONALL
-- 创建一个Student表 id(主键) name-- 在创建表的时候可以保持数据的完整性-- 主键特点:1.唯一 2.不能是空的CREATETABLE student(
id intPRIMARYKEY,
name VARCHAR(50)
);-- 插入数据INSERTINTO student VALUES(1,'wanglong'),(2,'wangsong');SELECT * FROM student;-- 创建方式2-- 好处:可以添加联合主键CREATETABLE student1(
id int,
name VARCHAR(50),
PRIMARYKEY(id)
);-- 联合创建主键,主键的内容不能完全一样,不完全一样都可以插入数据库中CREATETABLE student2(
id int,
stuid int,
name VARCHAR(50),
PRIMARYKEY(id,stuid)
);insertinto student2 values(1,2,'qq'),(1,1,'qq'),(2,1,'qq');CREATETABLE student3(
id int,
name VARCHAR(50);
);
-- 通过修改表结构 添加主键yuealterTABLE student3 ADDCONSTRAINTPRIMARYKEY(id);-- 唯一约束 不能重复 (可以有空值)CREATETABLE student4(
id intPRIMARYKEY,
name VARCHAR(50) UNIQUE
);-- 自动增长列(auto_increment)CREATETABLE student5(
id intPRIMARYKEY auto_increment,
name VARCHAR(50)
);INSERTINTO student5 VALUES(2,'wang');INSERTINTO student5 VALUES(NULL,方'wang1');INSERTINTO student5 VALUES(NULL,'wang2');INSERTINTO student5 VALUES(NULL,'wang3');-- 域的完整性限制单元格的数据内容-- 值不能为空 和 默认值CREATETABLE student6(
id intPRIMARYKEY,
name VARCHAR(50) notnull,
sex varchar(20) DEFAULT'男'
);INSERTINTO student6 (id,name)VALUES(1,'wang');-- 引用完整性(外键约束 把表与表之间通过字段建立联系)createtable Student7(
sid intPRIMARYKEY,
name VARCHAR(20)
);-- 从表createtable score(
sid int,
score int,
-- 创建外键 建立表联系
-- --FOREIGNKEY(当前表的字段)
CONSTRAINT fk_score_sid FOREIGNKEY(sid) REFERENCES Student7(sid)
);-- 建立多对多关系的时候 实际上就是建立外键-- 1.明确:在那张表建立外键(中间表)-- 2.哪个字段与哪个字段建立联系,-- 中间表的tid与老师表的tid 建立外键关系-- 中间表的tid与学生表的sid 建立外键关系createtable teacher (
tid intprimarykey,
name varchar(20)
);createtable Student(
sid intprimarykey,
sname varchar(10)
);createtable ts(
tid int,
sid int,
think varchar(10),
CONSTRAINT ts_t FOREIGNKEY(sid) REFERENCES Student(sid),
CONSTRAINT ts_s FOREIGNKEY(tid) REFERENCES teacher(tid)
);