1,查看数据库
show databases;
2,创建数据库
create database lianwei4;
3,使用某个数据库
use lianwei4;
4,显示某个数据库中的所有表
show tables;
5,在当前使用的数据库中创建一张表
学生表：
create table student(
id varchar(12),
name varchar(20),
sex char(4)
);
课程表：
create table course(
id varchar(12),
name varchar(20),
teacher varchar(20)
);
6,查看表的结构
desc student;
7,插入数据到表中
insert into student values('2017c001','张三','男');
8,查看表中的数据
select * from student;
2018年3月13日15:59:32 2018年3月13日 星期二
1，复习前面常用的命令
2，修改配置文件，使数据库可以保持中文数据。
找到MySQL的安装目录，例如：
D:\Program Files\MySQL\MySQL Server 5.5
在此目录下有一个文件：my.ini
如果你是win10系统，此文件在没有权限修改，可以将此文件剪切到桌面，然后用文本打开，修改里面client端的字符编码集设置：
default-character-set=utf8 改成 default-character-set=gbk
保存后，再剪切回 D:\Program Files\MySQL\MySQL Server 5.5 目录
3，重新启动MySQL服务，
4，重新打开命令行窗口，登陆数据库，
5，使用lianwei4这个数据库
6，在Student表中插入一条中文数据
insert into student values('2017c001','黎小龙','男');
7，查看一下里面的数据是否成功？
+----------+------+------+
| id | name | sex |
+----------+------+------+
| 2017c001 | 张三 | 男 |
+----------+------+------+
2018年3月14日14:56:15（1,2节）
ppt1(40)
2018年3月15日14:04:46 2018年3月15日 星期四
1，前次课程内容回顾
2，完成ppt1讲解,ppt2(24)介绍，安装概览
3，布置将第一个ppt的E-R模型手绘一遍，并预习第三个ppt。
2018年3月20日15:55:10 2018年3月20日 星期二
1，创建一个数据库：test1
create database test1 default character set=utf8;
2，查看数据库使用的默认字符集
show create database test1;
2.1，修改此数据库的字符集为：Latin1
alter database test1 character set=gbk;
3，在此数据库中创建一张表：user
use test1;
create table user(name varchar(20));
4，查看此表的创建脚本及默认字符集
show create table user;
CREATE TABLE user (
name varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
5，修改数据库的字符集
6，删除表
drop table user;
7，删除数据库
drop database test1;
8，在数据库：lianwei4中建表：book(isbn,name,publishdate,price,number)
use lianwei4;
create table book(
isbn char(20) primary key,
name varchar(40) not null,
publishdate date not null,
price float(5,2) not null,
number int(3) not null
)engine=InnoDB default charset=utf8;
9，初始化表，插入3条数据。
insert into book values('isbn01','《水浒传》','2001-09-10',25.50,57);
insert into book values('isbn02','《西游记》','1991-11-01',27.50,34);
insert into book values('isbn03','《红楼梦》','2008-08-08',11.80,90);
10,ppt3(22)
作业：课本前4章认真读完，并完成每章后面的练习题。
2018年3月21日08:08:54 2018年3月21日 星期三
1，给表增加一列，并给其默认值
alter table book add column 浏览次数 varchar(20) default '11次';
mysql> select * from book;
+--------+------------+-------------+-------+--------+----------+
| isbn | name | publishdate | price | number | 浏览次数 |
+--------+------------+-------------+-------+--------+----------+
| isbn01 | 《水浒传》 | 2001-09-10 | 25.50 | 57 | 11次 |
| isbn02 | 《西游记》 | 1991-11-01 | 27.50 | 34 | 11次 |
| isbn03 | 《红楼梦》 | 2008-08-08 | 11.80 | 90 | 11次 |
+--------+------------+-------------+-------+--------+----------+
2，其他相似的命令
【例3.5】 假设已经在数据库Bookstore中创建了表book，表中存在“书名”列。在表book中增加“浏览次数”列并将表中的“书名”列删除。
ALTER TABLE book ADD 浏览次数 tinyint default 3 ,
DROP COLUMN 书名 ;
【例3.6】 假设数据库Bookstore中已经存在table1表，将table1表重命名为student。
ALTER TABLE table1 RENAME TO student;
修改表名除了上面的ALTER TABLE命令，还可以直接用RENAME TABLE语句来更改表的名字。
语法格式： RENAME TABLE 旧表名1 TO 新表名1
[ , 旧表名2 TO 新表名2] ...
【例3.7】 假设数据库BookStore中已经存在table2表和table3表，将table2表重命名为orders，table3表重命名为orderlist。
RENAME TABLE table2 TO orders, Table3 TO orderlist;
3，复制表的结构（含约束）或结构及数据（不包含约束）
语法格式：
CREATE TABLE [IF NOT EXISTS] 新表名
[ LIKE 参照表名 ]
| [AS (select语句)]
使用LIKE关键字创建一个与old_table_name表相同结构的新表，列名、数据类型、空指定和索引也将复制，但是表的内容不会复制，因此创建的新表是一个空表。
使用AS关键字可以复制表的内容，但索引和完整性约束是不会复制的。
【例3.8】 假设数据库BookStore中有一个表Book，创建Book表的一个名为book_copy1的拷贝。
CREATE TABLE book_copy1 LIKE Book;
【例 3.9】 创建表Book的一个名为book_copy2的拷贝，并且复制其内容。 CREATE TABLE book_copy2
AS
(SELECT * FROM Book);
4，删除表
mysql> drop table abcd;
ERROR 1051 (42S02): Unknown table 'abcd'
mysql> drop table if exists abcd;
Query OK, 0 rows affected, 1 warning (0.00 sec)
5，查看表中某一列的结构信息
mysql> desc book price;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| price | float(5,2) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
6，学生表
use lianwei4;
create table xs(
id char(20),
name varchar(20),
major varchar(20),
sex char(4),
birthday date,
total_score int(4),
note text
);
7，课程表
create table kc(
id char(12),
name varchar(20),
begin_date date,
hours int(4),
credit int(2)
);
8，成绩表
create table xs_kc(
xs_id char(20),
kc_id char(12),
score float(5,2)
);
9, 给学生表加主键
alter table xs add constraint primary key pk (id);
10, 给课程表加主键
11，给成绩表加主键
12，给成绩表加上外键
13，ppt3(36),还有最后一页ppt没有操作完成
2018年3月22日14:02:56 2018年3月22日 星期四
9, 给学生表加主键
alter table xs add constraint primary key pk (id);
10, 给课程表加主键
alter table kc add constraint primary key zhujian(id);
11，给成绩表加主键
alter table xs_kc add constraint primary key pk(xs_id,kc_id);
12，给成绩表加上外键
alter table xs_kc add constraint foreign key fk1(xs_id) references xs(id);
alter table xs_kc add constraint foreign key fk2(kc_id) references kc(id);
13,在CSDN上比较详细的约束的创建与删除文档：
http://blog.csdn.net/a909301740/article/details/62887992
14，创建表Book中列包括
图书编号，图书类别，书名，作者，出版社，出版时间，单价，数量，折扣，封面图片
create table book(
isbn char(20) primary key,
type char(20) not null,
name varchar(20) not null,
author varchar(20) not null,
publisher varchar(20),
publishdate date,
price float(5,2),
number int(4),
discount float(2,2),
coverpicture blob
)Engine=InnoDB charset=utf8;
15,插入如下的一行数据：
（'ISBN 7-5006-6625-X/T', '计算机', 'Dreamwearer 8网站制作‘, '鲍嘉', '高等教育出版社', '2010-08-16', 33.25, 50, 0.8, NULL ）
第一条数据：
insert into book(isbn, type, name, author, publisher,
publishdate, price, number, discount, coverpicture)
values('ISBN 7-5006-6625-X/T', '计算机', 'Dreamwearer 8网站制作', '鲍嘉', '高等教育出版社', '2010-08-16', 33.25, 50, 0.8, NULL);
第二条数据：
insert into book(isbn, type, name, author, publisher,
publishdate, price, number, discount)
values('ISBN 7-5006-6626-X/T', '计算机', 'Dreamwearer 8网站制作', '鲍嘉', '高等教育出版社', '2010-08-16', 33.25, 50, 0.8);
16，将图书类型添加默认值‘计算机’
17,ppt4(6)
2018年3月26日15:57:37 2018年3月26日 星期一
1，替换一条已经存在的数据，可以全部替换，也可以部分替换。
replace book(isbn, type, name, author, publisher,
publishdate, price, number, discount)
values('ISBN 7-5006-6626-X/T', '文学书籍', '《平凡的世界》', '路遥', '陕西文艺出版社', '1982-08-16', 53.25, 70, 0.95);
2，部分列插入数据（含图片路径），
1)插入blob图片数据的路径：
insert into book(isbn, type, name, author, coverpicture)
values('isbn_101', '武侠小说', '《射雕英雄传》', '金庸', 'http://img1.imgtn.bdimg.com/it/u=1603129501,1399616862&fm=27&gp=0.jpg');
2)插入blob图片数据：
insert into book(isbn, type, name, author, coverpicture)
values('isbn_102', '武侠小说', '《射雕英雄传》', '金庸', load_file('c:/01.jpg'));
***说明：
在MySQL中Blob是一个二进制的对象，它是一个可以存储大量数据的容器(如图片，音乐等等)，且能容纳不同大小的数据，在MySQL中有四种Blob类型，他们的区别就是可以容纳的信息量不容分别是以下四种:
①TinyBlob类型 最大能容纳255B的数据
②Blob类型 最大能容纳65KB的
③MediumBlob类型 最大能容纳16MB的数据
④LongBlob类型 最大能容纳4GB的数据
3，一次性插入多条数据
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.03 sec)
mysql> insert into temp(name) values('n1'),('n2'),('n3'),('n4');
4，修改数据：update
update book
set publisher='江西出版社', publishdate='2008-10-27'
where isbn='isbn_101';
5,修改book数据，让所有的number都增加10
+----------------------+-----------------------+--------+
| isbn | name | number |
+----------------------+-----------------------+--------+
| ISBN 7-5006-6625-X/T | Dreamwearer 8网站制作 | 50 |
| ISBN 7-5006-6626-X/T | 《平凡的世界》 | 70 |
| isbn_101 | 《射雕英雄传》 | 1 |
+----------------------+-----------------------+--------+
mysql> update book set number=number+10;
+----------------------+-----------------------+--------+
| isbn | name | number |
+----------------------+-----------------------+--------+
| ISBN 7-5006-6625-X/T | Dreamwearer 8网站制作 | 60 |
| ISBN 7-5006-6626-X/T | 《平凡的世界》 | 80 |
| isbn_101 | 《射雕英雄传》 | 11 |
+----------------------+-----------------------+--------+
6，在student表后面加一列：classid
alter table student add column classid varchar(20) ;
7，建一张班级表：class(classid, name,)
create table class(
classid varchar(20) primary key,
name varchar(20) not null
);
insert into class values('jsj_04', '最萌四班');
insert into class values('jsj_09', '丐帮班');
alter table student add constraint fk foreign key (classid) references class(classid);
8,在学生表插入一条数据，
insert into student(id, name, sex, classid)
values('2017s002','洪七公','男','jsj_09');
9，修改张三的所属班级
update student set classid='jsj_04' where id='2017c001';
10,同时修改2张表的数据
update student t1, class t2
set t1.name='李四', t2.name='么么哒四班'
where t1.classid = t2.classid and name='张三';
11,ppt4(13)
2018年3月27日16:00:56 2018年3月27日 星期二
1，创建部门表：dept
CREATE TABLE dept (
deptno int(11) NOT NULL,
dname varchar(50),
loc varchar(50),
PRIMARY KEY (deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2,创建雇员表：emp
CREATE TABLE emp (
empno int(11) NOT NULL,
ename varchar(50),
job varchar(50),
mgr int(11),
hiredate date,
sal decimal(7,2),
COMM decimal(7,2),
deptno int(11),
PRIMARY KEY (empno),
KEY fk_emp (mgr),
CONSTRAINT fk_emp FOREIGN KEY (mgr) REFERENCES emp (empno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3,创建工作等级表：salgrade
CREATE TABLE salgrade (
grade int(11) ,
losal int(11) ,
hisal int(11) ,
PRIMARY KEY (grade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4,表数据的初始化：
-- Records of dept
INSERT INTO dept VALUES ('10', '教研部', '北京');
INSERT INTO dept VALUES ('20', '学工部', '上海');
INSERT INTO dept VALUES ('30', '销售部', '广州');
INSERT INTO dept VALUES ('40', '财务部', '武汉');
-- Records of emp
INSERT INTO emp VALUES ('1001', '甘宁', '文员', '1013', '2000-12-17', '8000.00', null, '20');
INSERT INTO emp VALUES ('1002', '黛绮丝', '销售员', '1006', '2001-02-20', '16000.00', '3000.00', '30');
INSERT INTO emp VALUES ('1003', '殷天正', '销售员', '1006', '2001-02-22', '12500.00', '5000.00', '30');
INSERT INTO emp VALUES ('1004', '刘备', '经理', '1009', '2001-04-02', '29750.00', null, '20');
INSERT INTO emp VALUES ('1005', '谢逊', '销售员', '1006', '2001-09-28', '12500.00', '14000.00', '30');
INSERT INTO emp VALUES ('1006', '关羽', '经理', '1009', '2001-05-01', '28500.00', null, '30');
INSERT INTO emp VALUES ('1007', '张飞', '经理', '1009', '2001-09-01', '24500.00', null, '10');
INSERT INTO emp VALUES ('1008', '诸葛亮', '分析师', '1004', '2007-04-19', '30000.00', null, '20');
INSERT INTO emp VALUES ('1009', '曾阿牛', '董事长', null, '2001-11-17', '50000.00', null, '10');
INSERT INTO emp VALUES ('1010', '韦一笑', '销售员', '1006', '2001-09-08', '15000.00', '0.00', '30');
INSERT INTO emp VALUES ('1011', '周泰', '文员', '1008', '2007-05-23', '11000.00', null, '20');
INSERT INTO emp VALUES ('1012', '程普', '文员', '1006', '2001-12-03', '9500.00', null, '30');
INSERT INTO emp VALUES ('1013', '庞统', '分析师', '1004', '2001-12-03', '30000.00', null, '20');
INSERT INTO emp VALUES ('1014', '黄盖', '文员', '1007', '2002-01-23', '13000.00', null, '10');
-- Records of salgrade
INSERT INTO salgrade VALUES ('1', '7000', '12000');
INSERT INTO salgrade VALUES ('2', '12010', '14000');
INSERT INTO salgrade VALUES ('3', '14010', '20000');
INSERT INTO salgrade VALUES ('4', '20010', '30000');
INSERT INTO salgrade VALUES ('5', '30010', '99990');
5，更新练习：
1）甘宁的工资收入相应的增加1元
update emp
set sal=sal+1
where ename='甘宁';
2）如果有几个人都叫“甘宁”，哪怎么办？
修改工作是文员，并且姓名是甘宁的工资10元
update emp
set sal=sal+1,comm=10000;
2018年3月29日14:07:51 2018年3月29日 星期四
******数据库中的约束******
有5种约束：
1，主键约束（primary key）：非空，唯一
用来唯一的标识这一条（一行）数据。
2，外键约束(foreign key)：可以为空值（null），但是如果不为空，则这个值一定是在另一张表中（被引用的表）存在的值。
用来维护数据引用的完整性。
3，非空约束(not null):如此列定义为非空，则必须有值。
4，唯一约束：可以为null,如果不为null值，则不能存在重复值。
5，检查约束(MySQL不支持):规定此列的值必须满足这个条件才能保存。
测试：
create table s1(
id int(11) primary key auto_increment,
name varchar(30),
age int(4) check (age>20)
);
6，建表应该掌握的知识
1）creat table....语法
2）数据类型
3）约束规则
7,更新语法
update 表名称1,表名称2,...
set 列名称1=值1,列名称2=值2，列名称3=值3, .....
where 条件;
测试：
update s1
set name='金小胖'
where id=1;
update s1
set name='小雪'
where id=4 or id=5 or id=6;
update s1
set name='雪花', age=27
where id in(4,5,6);
8,删除数据的语法
delete
from 表名称
where 条件;
测试：删除 部门编号是10员工信息
delete
from salgrade
where grade>3;
作业：在命令行环境下5分钟之内，建好三张表：dept,emp,salgrade
注意：创建表的顺序，主键，外键
我会在课堂检测。不能完成的要留堂继续练习，一直到成功！
2018年4月2日15:53:47 2018年4月2日 星期一
1，查询句法：
select ... 要查询的列，多个列用逗号隔开
from ... 要查询的数据来自哪些表
where ... 查询的条件
group by ... 查询分组条件
having ... 分组过滤条件
order by ... 查询结果排序方式
limit ... 分页显示
我们在使用过程当中，会使用其中的全部，或一部分（更常见），但是顺序不能错！
2，需求：查询lianwei4数据库的Members表中各会员的会员姓名、联系电话和注册时间。
select 会员姓名, 联系电话, 注册时间
from members;
----------------------------
select 会员姓名 姓名, 联系电话 电话, 注册时间 时间
from members;
----------------------------
select 会员姓名 '姓 名', 联系电话 "电 话", 注册时间 "时 间"
from members;
3，使用列别名的时候，注意：如果别名中有空格，则一定要用引号括起来。
4，替换查询结果中的数据
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式
END
需求：
查询Book表中图书编号、书名和数量，对其库存数量按以下规则进行替换：若数量为空值，替换为“尚未进货”；若数量小于5，替换为“需进货”；若数量在5-50之间，替换为“库存正常”；若总学分大于50，替换为“库存积压”。列标题更改为“库存”。
select 图书编号, 书名,
case
when 数量 is null then '尚未进货'
when 数量 <=5 then '要进货了'
when 数量 >05 and 数量<=50 then '库存正常'
when 数量>50 and 数量<100 then '库存积压'
else '活捉此货N多！'
end 库存
from book;
5，测试：
drop table if exists stud1;
create table stud1 (
id int(10) primary key auto_increment,
name varchar(20),
score float(4,2)
);
insert into stud1(name ,score) values('赵小乐',97.75),('钱小强', 86.67),('孙小雷',77.90),('李小刚',65.31),('周小太', 58.9);
insert into stud1(name ,score) values('若兰',null);
select id 学生编号,name 姓名, score 得分,
case
when score >=90 then '优秀'
when score >=80 and score<90 then '良好'
when score >=70 and score<80 then '中等'
when score >=60 and score<70 then '及格'
when score <60 then '挂科'
else '缺考'
end 成绩
from stud1;
2018年4月3日15:48:30 2018年4月3日 星期二
6，计算列值
示例：
对Sell表已发货的记录计算订购金额（订购金额=订购册数*订购单价），并显示图书编号和订购金额
select 图书编号,订购册数*订购单价 订购金额, 是否发货
from sell;
---------------------------
select 图书编号,订购册数*订购单价 订购金额, 是否发货
from sell
where 是否发货='已发货';
select t1.图书编号,书名,订购册数*订购单价 订购金额, 是否发货
from sell t1, book t2
where t1.图书编号=t2.图书编号
and 是否发货='已发货';
7，消除结果集中的重复行:distinct
select select 列1,列2,...
from 表名称;
注明：此处重复行指的是，查询出来的所有列值都一模一样的行。
8,课堂练习：
1）查询每个雇员的的地址和电话，显示的列标题要求显示“地址”、“电话”。
select name 姓名, address 地址, phoneNumber 电话
from employees;
2）查询employees表部门号和性别，要求消除重复行
select departmentid, sex
from employees;
-------------------------
select distinct departmentid, sex
from employees;
3）计算每个雇员的实际收入
select EmployeeID, income, outcome , income-outcome 实际收入
from salary;
------------------------------
select EmployeeID, income, outcome , round(income-outcome,2) 实际收入
from salary;
4）查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”。
select name 姓名,
case
when sex='1' then '男'
else '女'
end 性别
from employees;
5）查询Salary表中员工的员工编号和收入水平，要求2000元以下显示为“低收入”，2000-3000显示为“中等收入”，3000元以上时显示为“高收入”。
select employeeid 员工编号, income 收入水平,
case
when income<2000 then '低收入'
when income>=2000 and income<=3000 then '中等收入'
else '高收入'
end 收入等级
from salary;
------------------------同时显示姓名----------
select t1.employeeid 员工编号, name 姓名, income 收入水平,
case
when income<2000 then '低收入'
when income>=2000 and income<=3000 then '中等收入'
else '高收入'
end 收入等级
from salary t1, employees t2
where t1.employeeid=t2.employeeid;
完成ppt5(10)
2018年4月8日14:03:54 2018年4月8日 星期日
1,作业:一个小目标
从2018/4/8起,4周内看完课本的前6章.包含后面的练习题.
2,回顾之前的内容.
select...
from...
where...
group by...
having...
order by ...
limit...
1)查询每个雇员的的地址和电话，显示的列标题要求显示“地址”、“电话”。
select address 地址, phonenumber 电话
from employees;
2)查询employees表部门号和性别，要求消除重复行
select distinct departmentID, sex
from employees;
3)计算每个雇员的实际收入
select employeeID, income, outcome, round((income-outcome),2) 实际收入
from salary;
4)查询employees表中员工的姓名和性别，要求sex值为1时显示为“男”，为0时显示为“女”。
select name,sex,
case
when sex='1' then '男'
else '女'
end as 性别
from employees;
5)查询Salary表中员工的员工编号和收入水平，要求2000元以下显示为“低收入”，2000-3000显示为“中等收入”，3000元以上时显示为“高收入”。
select employeeid, income,
case
when income<2000 then '低收入'
when income>=2000 and income<=3000 then '中等收入'
else '高收入'
end as 收入水平
from salary;
3,聚合函数:常常用于对一组值进行计算，然后返回单个值。
max:求此列的最大值
min:求此列的最小值
avg:求此列的平均值
count:计算此列有多少行(不含null行)
sum:求此列值的和
4,示例:
select max(sal) 最高工资,min(sal) 最低工资,avg(sal) 平均工资,count(sal) 领工资的人数,sum(sal) 工资总和
from emp;
+----------+----------+--------------+------------+-----------
| max(sal) | min(sal) | avg(sal) | count(sal) | sum(sal)
+----------+----------+--------------+------------+-----------
| 50000.00 | 8000.00 | 20732.142857 | 14 | 290250.00
+----------+----------+--------------+------------+-----------
最高工资 | 最低工资 | 平均工资 | 领工资的人数 | 工资总 |
---------+----------+--------------+--------------+-----------
50000.00 | 8000.00 | 20732.142857 | 14 | 290250.00
5,用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0
示例: select count(empno),count(comm),count(job),count(sal)
from emp;
6,统计订购册数在5以上的订单数。
SELECT COUNT(订购册数) 订购册数在5以上的订单数
FROM Sell
WHERE 订购册数>5;
7, SELECT SUM(订购册数) 订购总册数
FROM Sell
WHERE 图书编号 = 'ISBN 8-5006-6625-X';
8,课堂小爽一下:
1)计算员工总数
select count(*) 员工总数
from employees;
2)计算salary表中员工月收入的平均数
select avg(income) 员工月收入的平均数
from salary;
-----------------------------------
select sum(income)/count(employeeID)
from salary;
3)计算所有员工的总支出
select sum(outcome) 总支出
from salary;
4)显示最高收入和最低收入的员工的员工号
select max(income), min(income)
from salary;
2018/4/9 2018年4月9日15:58:55 2018年4月9日 星期一
*****课后作业:从2018/4/9(今天起),4周内把课本前6章看完,并且完成后面的练习, 计入平时分.
~~2018/4/9----2018/4/15 完成第1,2章
~~2018/4/6----2018/4/22 完成第3,4章
~~2018/4/23---2018/5/5 完成第5,6章
1,wher子句.
select ...
from ...
where 查询条件;
逻辑运算符: !=,<>, > , < ,>=, <=, =, <=>,between ... and ..., like
还有个判断是否为空的:is null , is not null,
select *
from salary
where income < 3000;
---------------------------
select *
from salary
where income > 3000;
------------------------
select *
from salary
where income = 3000;
--------------------------
select *
from salary
where income != 3000;
------------------
select *
from salary
where income <> 3000;
------------------
select *
from salary
where income between 2000 and 3000;
----------------------
select *
from salary
where income > 2000 and income <3000;
----------------------
select *
from emp
where comm is null;
----------------------
select *
from emp
where comm is not null;
注明:MySQL有一个特殊的等于运算符“<=>”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。没有UNKNOWN的情况。
select *
from emp
where comm <=> null;
2,【例5.12】 查询lianwei4数据库Book表中书名为“网页程序设计”的记录。
SELECT 书名,单价
FROM Book
WHERE 书名='网页程序设计';
3,【例5.13】 查询Book表中单价大于30的图书情况。
SELECT *
FROM Book
WHERE 单价>30;
MySQL有一个特殊的等于运算符“<=>”，当两个表达式彼此相等或都等于空值时，它的值为TRUE，其中有一个空值或都是非空值但不相等，这个条件就是FALSE。没有UNKNOWN的情况。
4,【例5.14】 查询Sell表中还未收货的订单情况。
SELECT 订单号,是否收货
FROM Sell
WHERE 是否收货<=>NULL;
5,【例5.15】 查询Sell表中已收货且已结清的订单情况。
SELECT 订单号,是否收货,是否结清
FROM Sell
WHERE 是否收货='已收货'
AND 是否结清='已结清';
6,【例5.16】 查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。
SELECT 书名,出版社,单价
FROM Book
WHERE (出版社='清华大学出版社' OR 出版社='北京大学出版社' )
AND 单价>=25;
或：
SELECT书名,出版社,单价
FROM Book
WHERE (出版社='清华大学出版社' AND 单价>=25)
OR (出版社='北京大学出版社' AND 单价>=25);
7,【思考题】以下语句能否得到正确结果？为什么？
SELECT 书名,出版社,单价 FROM Book
WHERE 出版社='清华大学出版社' OR 出版社='北京大学出版社'
AND 单价>=25;
例如:条件的组和,注意区别
select (2+3)10;
select 210+310;
--------------------
select 2+(310);
*****对lianwei4数据库完成一下查询：
8、显示月收入高于2000元的员工号码
select employeeId , income
from salary
where income>2000;
9、查询1970年以后出生的员工的姓名和地址
select name,address,birthday
from employees
where birthday>'1970';
10、显示女雇员的地址和电话，列标题要求用中文“地址”、“电话”表示
select address 地址, phonenumber 电话
from employees;
11,范围比较 between ... and ... 包含两端, 是个闭区间.
用于范围比较的关键字有两个：BETWEEN和IN。
当要查询的条件是某个值的范围时，可以使用BETWEEN关键
BETWEEN关键字指出查询范围，格式为：
expression [ NOT ] BETWEEN expression1 AND expression2
当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。
注意：expression1的值不能大于expression2的值。
12, 【例5.20】 查询Book表中2010年出版的图书的情况。
SELECT *
FROM Book
WHERE 出版时间 BETWEEN '2010-1-1' AND '2010-12-3
13, in: 使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。
使用IN关键字指定值表的格式为：
expression IN ( expression [,…n])
select *
from emp
where sal in (8000,16000, 2);
select *
from emp
where sal=8000 or sal=16000 or sal=2;
14, like:其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。
1) %:表示0个或任意多个字符
2) _:表示任意一个字符
示例:
select * from emp where ename like '%';
------------------------------------
select * from emp;
============================
select * from emp where ename like '%亮';
select * from emp where job like '%师';
select * from emp where job like '销%员';
-----------------------------------------------
select * from emp where job like '销_';
select * from emp where job like '销__';
select * from emp where job like '___';
select * from emp where job like '__';
select * from emp where job like '__员';
ppt5(28)没有完成
2018年4月10日15:53:37 2018年4月10日 星期二
15,课堂暗爽! 在数据库lianwei4中实现下列查询业务.
唯有套路的人心!
select
from
where
group by
having
order by
limit
//下面的查询用到的表: employees, salary, department
1)显示月收入高于2000元的员工号码
select employeeID,income
from salary
where income > 2000;
2)查询1970年以后出生的员工的姓名和地址
select name, address, birthday
from employees
where birthday>'1970';
3)查询出支持在:50-150之间的员工信息
分析过程:
*)先确定要的信息来自employees,但不能直接获得满足条件的信息;
*)从salary中找满足outcome 在50到150之间的员工的编号
select employeeID
from salary
where outcome between 50 and 150;
*)根据上一步得到的employeeID数据,到employee表中查询满足条件的员工信息.
select *
from employees
where employeeID
in(select employeeID
from salary
where outcome between 50 and 150);
(1)实现方式一:
select *
from employees
where employeeID in
(select employeeID
from salary
where outcome between 50 and 150);
------------
(2)实现方式二:
select t1.employeeID, t1.name,t2.outcome
from employees t1, salary t2
where t1.employeeID=t2.employeeID and
outcome between 50 and 150;
4)查询财务部、研发部、市场部的员工信息
(1)实现方式一:
select *
from employees
where departmentID in(select departmentID
from departments
where DepartmentName
in('财务部','研发部','市场部')
);
(2)实现方式二:
select t2.departmentID,t2.departmentName, t1.*
from employees t1, departments t2
where t1.departmentID=t2.departmentID
and t2.departmentName in('财务部','研发部','市场部')
order by t1.departmentid desc;
5)显示工作年限三年以上（含3年）、学历在本科以上（含本科）的男性员工的信息.
select *
from employees
where workyear>3
and education in('本科','硕士');
6)查找员工号码中倒数第二个数字为0的姓名、地址和学历
select employeeID name, address, education
from employees
where employeeID like '%0_';
7)查找地址中包含’中山路’ 的员工的信息
select *
from employees
where address like '%中山路%';
PPT5(28)完成!
2018年4月12日14:18:15 2018年4月12日 星期四
一: ***** 葵花宝典总则: *****
多表查询要点心得:
1)分析要查询的目标数据来源
2)在条件字句中,如果有n张表参与查询,则有(n-1)个条件用来去除笛卡尔积.
3)然后在条件字句中加上业务查询条件即可.
二: 多表查询
1,示例
select a.sal, a.job, a.empno, a.ename from lianwei4.emp as a;
全连接: 会产生笛卡尔积, 我们在查询的时候要注意这种情况.
2,需求:查询员工编号,部门号,姓名,及其所在部门的名称.
select t1.empno, t2.deptno, t1.ename, t2.dname
from emp t1, dept t2
where t1.deptno=t2.deptno;
3,【例5.24】 查找客户订购的图书书名，订购册数和订购时间。
SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book, Sell
WHERE Book.图书编号=Sell.图书编号;
查找客户订购的图书书名,订购册数大于四本,订购时间信息
SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book, Sell
WHERE Book.图书编号=Sell.图书编号
and 订购册数>4;
-
JOIN连接
第二种方式是使用JOIN关键字的连接，语法格式如下：table_reference INNER JOIN table_reference
ON conditional_expr| USING (column_list)
示例: 查找客户订购的图书书名，订购册数和订购时间。
select 书名,订购册数,订购时间
from book t1 inner join sell t2
on t1.图书编号=t2.图书编号;
-----------------------
查找客户订购的图书书名,订购册数大于四本,订购时间信息
select 书名,订购册数,订购时间
from book t1 inner join sell t2
on t1.图书编号=t2.图书编号
and 订购册数>4;
ppt5(34)
2018年4月17日15:41:00 2018年4月17日 星期二
一:稍微回顾一下:
select
from t1, t2, t3, ...
where ... and ...and...;
select
from t1 inner join t2
on ...and...and ...
inner join t3
on ... and...;
二:今天内容
1,系统默认是inner(可以省略)
【例 5.26】 用JOIN关键字表达下列查询：查找购买了“网页程序设计”且订购数量大于5本的图书信息。
SELECT 书名,订购册数
FROM Book JOIN Sell
ON Book.图书编号 = Sell. 图书编号
WHERE 书名 = '网页程序设计' AND 订购册数>0;
2,内连接还可以用于多个表的连接。
【例5.27】 用JOIN关键字表达下列查询：
涉及的表: book, members, sell
查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。
SELECT Book.图书编号, 会员姓名, 书名, 订购册数
FROM Sell JOIN Book ON Book. 图书编号= Sell.图书编号
JOIN Members ON Sell.身份证号 = Members.身份证号
WHERE 书名 = '网络数据库' AND 订购册数>5 ;
--------等价写法-----------
SELECT b.图书编号, 会员姓名, 书名, 订购册数
FROM Sell s, book b, members m
where b. 图书编号= s.图书编号
and s.身份证号 = m.身份证号
and 书名 = '网络数据库' AND 订购册数>5 ;
---------------------------------------------------------------
本学期有的的数据库及表:
人力资源系统1: emp, dept, salgrade
人力资源系统2: employees, departments, salry
图书商城系统: book, members, sell
校园选课系统: xs, kc, xs_kc
2,****表的自连接: 可以将一个表与它自身进行连接，称为自连接。
示例需求: 显示emp表中所有员工姓名及其上级的姓名
员工姓名|上级姓名
-------------
甘宁 | 庞统
黛绮丝| 关羽
... ...
select w.ename 员工姓名, b.ename 上级姓名
from emp w, emp b
where w.mgr=b.empno;
-----------------
select w.ename 员工姓名, b.ename 上级姓名
from emp w inner join emp b
on w.mgr=b.empno;
-------------------------
select w.empno, w.ename, w.mgr, b.ename, b.empno
from emp w, emp b
where w.mgr=b.empno;
+-------+--------+------+--------+-------+
| empno | ename | mgr | ename | empno |
+-------+--------+------+--------+-------+
| 1001 | 甘宁 | 1013 | 庞统 | 1013 |
| 1002 | 黛绮丝 | 1006 | 关羽 | 1006 |
| 1003 | 殷天正 | 1006 | 关羽 | 1006 |
| 1004 | 刘备 | 1009 | 曾阿牛 | 1009 |
| 1005 | 谢逊 | 1006 | 关羽 | 1006 |
| 1006 | 关羽 | 1009 | 曾阿牛 | 1009 |
| 1007 | 张飞 | 1009 | 曾阿牛 | 1009 |
| 1008 | 诸葛亮 | 1004 | 刘备 | 1004 |
| 1010 | 韦一笑 | 1006 | 关羽 | 1006 |
| 1011 | 周泰 | 1008 | 诸葛亮 | 1008 |
| 1012 | 程普 | 1006 | 关羽 | 1006 |
| 1013 | 庞统 | 1004 | 刘备 | 1004 |
| 1014 | 黄盖 | 1007 | 张飞 | 1007 |
+-------+--------+------+--------+-------+
------------------------
select w.ename 员工姓名, b.ename 上级姓名
from emp w left outer join emp b
on w.mgr=b.empno;
注意点:
使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。
3.指定了OUTER关键字的连接为外连接。
外连接包括：
● 左外连接（LEFT OUTER JOIN）：
结果表中除了匹配行外，还包括左表有的但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。
● 右外连接（RIGHT OUTER JOIN）：
结果表中除了匹配行外，还包括右表有的但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。
测试:
select w.ename 员工姓名, b.ename 上级姓名
from emp w left outer join emp b
on w.mgr=b.empno;
---------------------------
select w.ename 员工姓名, b.ename 上级姓名
from emp w right outer join emp b
on w.mgr=b.empno;
ppt(5) 40 没有讲
2018年4月19日14:09:00 2018年4月19日 星期四
====================================================
一:回顾一下连接查询
1),内联
系统为默认
语法: ...from t1 inner join t2 on ...
其中 inner 可以省略
只显示满足条件的数据
2),外联:
左外联: ...from t1 left outer join t2 on ...
右外联: ...from t1 right outer join t2 on ...
其中: 左右连接可以转换
显示对于左右表中所有的数据,不满足条件的用null匹配之
示例,见操作....
内联示例:
select worker.empno, worker.ename 下级, worker.mgr, boss.ename 上级, boss.empno
from emp worker join emp boss
on worker.mgr = boss.empno;
----------------------------
select worker.empno, worker.ename 下级, worker.mgr, boss.ename 上级, boss.empno
from emp worker, emp boss
where worker.mgr = boss.empno;
外联示例:
select worker.empno, worker.ename 下级, worker.mgr, boss.ename 上级, boss.empno
from emp worker left outer join emp boss
on worker.mgr = boss.empno;
select worker.empno, worker.ename 下级, worker.mgr, boss.ename 上级, boss.empno
from emp boss right outer join emp worker
on worker.mgr = boss.empno;
下面查询涉及的表: book, members, sell
【例5.30】 查找所有图书的图书编号、数量及订购了图书的会员身份证号，若从未订购过，也要包括其情况。
SELECT Book.图书编号, Book.数量, 身份证号
FROM Book LEFT outer JOIN Sell
ON Book.图书编号= Sell.图书编号;
【例5.31】 查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。
SELECT 订单号, 图书编号, 订购册数, Members.会员姓名
FROM Sell RIGHT JOIN Members
ON Members.身份证号= Sell.身份证号;
二,今天的主要内容:
1,子查询:在查询条件中，可以使用另一个查询的结果作为条件的一部分
1)in: 显示在"教研部"和"学工部"中的员工信息.
select *
from emp
where deptno in(select deptno
from dept
where dname in("教研部","学工部"));
2)in: 显示不在"教研部"和"学工部"中的员工信息.
select *
from emp
where deptno not in(select deptno
from dept
where dname in("教研部","学工部"));
2,【例5.32】 查找在lianwei4数据库中张三的订单信息。
涉及到的表：sell, members
SELECT *
FROM Sell
WHERE 身份证号 in
(SELECT 身份证号
FROM Members
WHERE 会员姓名 = '张三');
说明：在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。
3, 复杂一点的子查询嵌套
【例5.33】 查找购买了除“网页程序设计”以外图书的会员信息。
分析: 要查找会员信息，先要知道会员的身份证号，而要知道购买了除“网页程序设计”以外图书的会员，可以按图书编号在Sell表中查到，但是“网页程序设计”的图书编号要通过查找Book才可以获得。
所涉及的表: members, sell, book
SELECT *
FROM Members
WHERE 身份证号 IN
(SELECT 身份证号
FROM Sell
WHERE 图书编号 IN
( SELECT 图书编号
FROM Book
WHERE 书名!='网页程序设计'));
--------等价于:---------------------
select t1.*
from members t1, sell t2, book t3
where t1.身份证号=t2.身份证号
and t2.图书编号=t3.图书编号
and 书名!='网页程序设计';
ppt5(44)课堂练习没讲
2018年4月23日15:52:39 2018年4月23日 星期一
一:内容回顾
1, 查询的总的套路
select ...
from
where
group by
having
order by
limit
2,多表查询: 葵花宝典
1)笛卡尔积的产生: 多表全连接查询会产生笛卡尔积.
2)消除方式:n张表参与查询,至少要(n-1)个条件用来消除笛卡尔积.
一般用表之间的联系进行消除,比如:主,外键关联
3)多表查询的思路:
(1),分析数据(列)的来源, 来自哪些表?
(2),如果这些表存在, 就直接写出, 如不存在则需要构造.
(3),通过表之间的关联消除笛卡尔积
(4),添加上额外的业务条件,并列出查询项(列)即可.
3,子查询:
1)概念:在查询中又包含一个(或多个)查询,被包含的查询就是子查询
2)出现的位置: from, where , select,...
3)我们一般可以用内联来替换子查询.
4)自查询的重要作用之一: 用来构造数据(临时表)
4,内连接
select ....
from T1 inner join T2
on ...
inner join T3
on ...
where ... and ...;
**系统默认的查询行为, 只会显示满足条件的数据.
**可以跟子查询进行替换.
5, 外联接
1)左外连接
select ...
from T1 left outer join T2
on ...
where ... and ... ;
**左表T1里面的数据会全部显示, 右表中有匹配的数据则显示,若没有则用null来匹配
2)右外连接
select ...
from T1 right outer join T2
on ...
where ... and ... ;
**右表T2里面的数据会全部显示, 左表T1中有匹配的数据则显示,若没有则用null来匹配
3)左,右外联接查询中, 左和右是相对的, 可以相互转化.
4)示例:显示部门号,部门名称及此部门中的员工姓名
select t1.deptno, dname, ename
from dept t1, emp t2
where t1.deptno=t2.deptno;
5)示例:显示所有的部门号,部门名称及此部门中的员工姓名
select t1.deptno, dname, ename
from dept t1 left outer join emp t2
on t1.deptno=t2.deptno;
------ 用右外联实现等价效果: ------------
select t2.* , ename
from emp t1 right outer join dept t2
on t1.deptno=t2.deptno;
二:兄dei,要多练习!
下列查询可能涉及到的表有: employees, departments, salary
1、查询每个雇员的基本情况和薪水情况
select t1.employeeID, name, address, income
from employees t1, salary t2
where t1.employeeID=t2.employeeID;
2、查询“王林”的基本情况和所工作的部门名称
select t1.*, departmentName
from employees t1, departments t2
where t1.DepartmentID=t2.DepartmentID
and t1.name='王林';
3、查询月收入在2000~3000元的员工姓名和支出
select t1.employeeID, name, income, outcome
from employees t1, salary t2
where t1.employeeID=t2.employeeID
and income between 2000 and 3000 ;
4、查询研发部在1970年以前出生的员工姓名和薪水情况
select DepartmentName,t1.name,income,outcome
from employees t1,salary t2, departments t3
where t1.employeeID=t2.employeeID
and t1.departmentID=t3.departmentID
and birthday<'1970'
and DepartmentName='研发部';
5、使用子查询查找工资收入大于2000元的员工的基本信息
select t1.*, income
from employees t1, salary t2
where t1.employeeID=t2.employeeID
and income>2000;
-----------------------------
select t1.*, income
from employees t1 join salary t2
on t1.employeeID=t2.employeeID
where income>2000;
------------------------------
select t1.*
from employees t1
where employeeID in (select employeeID
from salary
where income>2000);
6、查找在财务部工作的员工的基本信息
select *
from employees
where departmentID in(select departmentId
from departments
where departmentName='财务部');
-----------
select *
from employees t1 inner join departments t2
where t1.departmentID=t2.departmentID
and departmentName='财务部';
------------------
select t1.*
from employees t1, departments t2
where t1.departmentID=t2.departmentID
and departmentName='财务部';
7、查找住在“中山路”的员工的工作部门名称
方式一:子查询实现
select departmentName
from departments
where departmentID in(select departmentID
from employees
where address like '%中山路%');
--------------------------
方式二:内联查询实现(SQL1999标准)
select departmentName
from employees t1, departments t2
where t1.departmentID=t2.departmentID
and t1.address like '%中山路%';
-------------------------------
方式三:内联查询实现(SQL2003标准)
select departmentName
from employees t1 inner join departments t2
on t1.departmentID=t2.departmentID
where t1.address like '%中山路%';
2018年4月26日14:04:59 2018年4月26日 星期四
1,分类汇总与排序 : group by
1)GROUP BY子句主要用于根据字段对查询结果行分组显示
2)GROUP BY子句后通常包含列名或表达式
3)可以在列的后面指定ASC（升序,系统默认）或DESC（降序）
4)GROUP BY可以根据一个或多个列进行分组，也可以根据表达式进 行分组
5)经常和聚合函数一起使用
6)在查询列(select字句)中 , 只能出现分组列, 或聚合(统计)函数
7)在分组后进行过滤,则只能用having,不能用where
**用需求来理解
2,显示emp表中,每个部门的人数.
select deptno, count()
from emp
group by deptno;
3,显示每个部门的平均工资...
select deptno 部门号, avg(sal) 平均工资, max(sal) 最高工资,count(*) 部门人数, sum(sal) 工资总和,min(sal) 最低工资
from emp
group by deptno;
+--------+--------------+----------+----------+-----------+----------+
| 部门号 | 平均工资 | 最高工资 | 部门人数 | 工资总和 | 最低工资 |
+--------+--------------+----------+----------+-----------+----------+
| 10 | 29166.666667 | 50000.00 | 3 | 87500.00 | 13000.00 |
| 20 | 21750.000000 | 30000.00 | 5 | 108750.00 | 8000.00 |
| 30 | 15666.666667 | 28500.00 | 6 | 94000.00 | 9500.00 |
+--------+--------------+----------+----------+-----------+----------+
4,统计各个工作职位的人数
select job, count()
from emp
group by job;
+--------+----------+
| job | count() |
+--------+----------+
| 分析师 | 2 |
| 文员 | 4 |
| 经理 | 3 |
| 董事长 | 1 |
| 销售员 | 4 |
+--------+----------+
5,显示人数多于两人的岗位及其人数.
select job, count()
from emp
group by job
having count()>2;
+--------+----------+
| job | count(*) |
+--------+----------+
| 文员 | 4 |
| 经理 | 3 |
| 销售员 | 4 |
+--------+----------+
6,多次分组:可以使用...with rollup 进行汇总
****按部门号及岗位显示员工人数
(先按部门号分组, 然后在按岗位分组)
select deptno, job, count(*)
from emp
group by deptno, job with rollup;
7,【例5.38】 输出Book表中图书类别名。
SELECT 图书类别
FROM Book
GROUP BY 图书类别;
8,【例5.39】 按图书类别统计Book表中各类图书的库存数。
SELECT 图书类别,COUNT(*) AS '库存数'
FROM Book
GROUP BY 图书类别;
9,【例5.40】 按图书编号分类统计其订单数和订单的平均订购册数。
SELECT 图书编号, AVG(订购册数) AS '订购册数' ,
COUNT(订单号) AS '订单数'
FROM Sell
GROUP BY 图书编号;
10,【例5.42】 查找Sell表中每个会员平均订购册数在10本以上的会员的身份证号和平均订购册数。
SELECT 身份证号, AVG(订购册数) AS '平均订购册数'
FROM Sell
GROUP BY 身份证号
HAVING AVG(订购册数) >10;
11,【例5.43】 查找Sell表中会员订单数在2笔以上且每笔订购册数都在5本以上的会员。
SELECT 身份证号,count(身份证号)
FROM Sell
WHERE 订购册数 >3
GROUP BY 身份证号
HAVING COUNT(*) > 2
order by 身份证号;
+--------------------+-----------------+
| 身份证号 | count(身份证号) |
+--------------------+-----------------+
| 430103198608201963 | 3 | 此人订购过3次以上, 每次
+--------------------+-----------------+ 超过5本书.
10,排序:order by
1)如果不使用ORDER BY子句，结果中行的顺序是不可预料的。
2)使用ORDER BY子句后可以保证结果中的行按一定顺序排列。
3)关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC
4)可以根据单列,或多列进行排序; 也可以通过查询列的编号排序
11, 按工资从高到低显示员工信息.
select deptno, empno, ename ,sal
from emp
order by sal desc;
12, 按部门号显示员工信息.
select deptno, empno, ename ,sal
from emp
order by deptno;
13, 按部门号从高到低显示员工信息.
select deptno, empno, ename ,sal
from emp
order by deptno desc;
14, 按部门号从高到低,员工号从低到高,显示员工信息.
select deptno, empno, ename ,sal
from emp
order by deptno desc, empno asc;
+--------+-------+--------+----------+
| deptno | empno | ename | sal |
+--------+-------+--------+----------+
| 30 | 1002 | 黛绮丝 | 16000.00 |
| 30 | 1003 | 殷天正 | 12500.00 |
| 30 | 1005 | 谢逊 | 12500.00 |
| 30 | 1006 | 关羽 | 28500.00 |
| 30 | 1010 | 韦一笑 | 15000.00 | <--- 部门降序号
| 30 | 1012 | 程普 | 9500.00 |
| 20 | 1001 | 甘宁 | 8000.00 | <--- 员工号升序
| 20 | 1004 | 刘备 | 29750.00 |
| 20 | 1008 | 诸葛亮 | 30000.00 |
| 20 | 1011 | 周泰 | 11000.00 |
| 20 | 1013 | 庞统 | 30000.00 |
| 10 | 1007 | 张飞 | 24500.00 |
| 10 | 1009 | 曾阿牛 | 50000.00 |
| 10 | 1014 | 黄盖 | 13000.00 |
+--------+-------+--------+----------+
15,【例5.44】 将Book表中记录按出版时间先后排序。
SELECT *
FROM Book
ORDER BY 出版时间;
16,【例5.45】 将Sell表中记录按订购册数从高到低排列。
SELECT *
FROM Sell
ORDER BY 订购册数 DESC;
17:LIMIT子句(分页查询)
1)LIMIT子句是SELECT语句的最后一个子句，
2)主要用于限制被SELECT语句返回的行数。
3)语法:...limit m, n; 或者 ...limt n offset m;
m:表示从第(m+1)显示,
n:表示显示多少行
格式中的offset和row_count都必须是非负的整数常数,
其中:offset指定返回的第一行的偏移量,从0开始.
18,显示工资最高的3个员工的信息
select empno, ename, sal
from emp
order by sal desc
limit 0,3;
-------- 等价 -----------------
select empno, ename, sal
from emp
order by sal desc
limit 3;
-------- 等价 ---------------
select empno, ename, sal
from emp
order by sal desc
limit 3 offset 0;
19,【例5.46】 查找Members表中注册时间最靠前的5位会员的信息。
SELECT *
FROM Members
ORDER BY 注册时间
LIMIT 5;
20,【例5.47】 查找Book表中从第4条记录开始的5条记录。
SELECT *
FROM Book
ORDER BY 学号
LIMIT 3, 5;
ppt5(53)完成
2018年5月2日08:10:40 2018年5月2日 星期三
一: 课堂练习,走起!!
下列练习所涉及到的表有: employees, dapartments, salary
1、按部门列出该部门工作的员工人数
select departmentID, count(*)
from employees
group by departmentID;
2、按员工学历分组统计各种学历人数
select education, count(*)
from employees
group by education;
3、分别统计男性员工和女性员工人数
SELECT SEX,COUNT(*)
FROM EMPLOYEES
GROUP BY SEX;
4、查找雇员数超过2人的部门名称和员工数量
SELect t1.*,count(t2.employeeID)
from departments t1, employees t2
where t1.departmentID=t2.departmentID
group by departmentID
having count(t2.employeeID)>2;
-----第1步:------------
select departmentID did, count(*) cnt
from employees
group by departmentID;
-----第2步:------------
select t2.did, t1.departmentname, t2.cnt
from departments t1, (select departmentID did, count(*) cnt
from employees
group by departmentID
having cnt>2) t2
where t1.departmentID=t2.did;
5、将员工信息按年龄从大到小排序
select *
from employees
order by birthday asc;
6、将员工薪水按收入多少从小到大排序
select income
from salary
order by income asc;
----------------------
将员工姓名按薪水收入多少从小到大排序
7、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序
select workyear, count(*)
from employees
group by workyear
order by count(*);
二:课堂练习2. (ppt5(p55))
1,显示每个雇员的姓名及实际收入（实际收入=收入-支出)。
select t1.employeeid, t2.name, round((income-outcome),0) 实际收入
from salary t1, employees t2
where t1.employeeID=t2.employeeID;
2、查找员工号码中倒数第二个数字为0的姓名、地址和学历。
selet name, address, education
from employees
where employeeid like '%0_';
3、按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序。
select woreyear, count(*)
from employees
group by wokeyear
order by count(*) asc;
4、查询研发部在1970年以前出生的员工姓名和薪水情况。
select DepartmentName,birthday,t1.name,income,outcome
from employees t1,salary t2, departments t3
where t1.employeeID=t2.employeeID
and t1.departmentID=t3.departmentID
and birthday<'1970'
and DepartmentName='研发部';
2018年5月3日14:04:33 2018年5月3日 星期四
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1, 视图的概念
视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。
视图是从一个或多个表（或视图）导出的表。视图一经定义以后，就可以像表一样被查询(插入、删除和更新)。
2,视图的主要作用
使用视图有下列优点：
（1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。
（2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。
（3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。
（4）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。
（5）可以重新组织数据以便输出到其他应用程序中。
3,视图的语法
使用CREATE VIEW语句创建视图
语法格式：
CREATE [OR REPLACE] VIEW 视图名 [(列名列表)]
AS select语句
[WITH [CASCADED | LOCAL] CHECK OPTION]
注意点:
1)列名列表：要想为视图的列定义明确的名称，可使用可选的列名列表子句，列出由逗号隔开的列名。
2)列名列表中的名称数目必须等于SELECT语句检索的列数。
3)若使用与源表或视图中相同的列名时可以省略列名列表。
4,使用视图
create or replace view my_view1
as
select empno, ename, job, salary
from emp
where salary>12000
order by salary desc;
------测试:
select * from my_view1;
5,视图示例:
查找雇员数超过2人的部门名称和员工数量
SELect t1.departmentname, count(t2.employeeID)
from departments t1, employees t2
where t1.departmentID=t2.departmentID
group by t2.departmentID
having count(t2.employeeID)>2;
-----------------
使用视图优化:
create or replace view v1(部门名称, 人数)
as
SELect t1.departmentname, count(t2.employeeID)
from departments t1, employees t2
where t1.departmentID=t2.departmentID
group by t2.departmentID
having count(t2.employeeID)>2;
------测试:
select * from v1;
6,课堂练习
1)创建视图Emp_view1,包含所有男员工的员工号码、姓名、工作年限和学历，字段名用中文表示
create or replace view emp_view1
as
select employeeID 员工号码, name 姓名, workyear 工作年限, education 学历
from employees
where sex=1;
2)从Emp_view2查询工作年限在两年以上的员工信息。
create or replace view emp_view2(员工号码,姓名,工作年限,学历)
as
select employeeID , name , workyear , education
from employees
where workyear>2;
3)创建创建视图Emp_view3、包含员工号码、姓名、所在部门名称和收入
create or replace view emp_view3(员工号码,姓名,所在部门名称,收入)
as
select t1.employeeID, name, departmentname, income
from employees t1, departments t2, salary t3
where t1.departmentID=t2.departmentID
and t1.employeeID=t3.employeeID;
4)从Emp_view2查询研发部的员工号码、姓名和收入。
create or replace view emp_view3(员工号码,姓名,所在部门名称,收入)
as
select t1.employeeID, name, departmentname, income
from employees t1, departments t2, salary t3
where t1.departmentID=t2.departmentID
and t1.employeeID=t3.employeeID
and departmentName='研发部';
6,使用SQL语句删除视图
语法格式：
DROP VIEW [IF EXISTS]
视图名1 [,视图名2] ...
7,****视图主要是用来简化查询的.
2018年5月7日16:00:48 2018年5月7日 星期一
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
说明: 下列查询涉及的表有: emp, dept, salgrade
1. 列出薪金比关羽高的所有员工。*/
select *
from emp
where sal>(select sal from emp where ename='关羽');
--------------------------
select t1.*
from emp t1,(select sal from emp where ename='关羽') t2
where t1.sal>t2.sal;
--------------------------
select t1.*
from emp t1, emp t2
where t1.sal>t2.sal
and t2.ename="关羽";
2. 列出所有员工的姓名及其直接上级的姓名。*/
select e.empno, e.ename, e.mgr, b.ename , b.empno
from emp e, emp b
where e.mgr=b.empno;
-------------------------
select e.empno, e.ename, e.mgr, b.ename , b.empno
from emp e left outer join emp b
on e.mgr=b.empno;
----------------------------
select e.empno, e.ename, e.mgr, b.ename , b.empno
from emp b right outer join emp e
on e.mgr=b.empno;
3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
select t1.empno, t1.ename, t1.hiredate, t2.ename, t2.hiredate, dname
from emp t1, emp t2, dept t3
where t1.mgr=t2.empno
and t1.deptno=t3.deptno
and t1.hiredate<t2.hiredate;
4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
部门号 部门名称 员工姓名 雇佣日期
-------------------------------------------
10 研发部 张三 2001-01-12
20 运营部 李四 2009-09-10
20 运营部 王五 2010-01-07
....
90 监察部 null null
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select d.deptno, dname, ename, hiredate
from dept d left outer join emp e
on d.deptno=e.deptno;
---------------------------------
select d.deptno, dname, ename, hiredate
from emp e right outer join dept d
on d.deptno=e.deptno;
-----------------------------------
select d.deptno, dname, ename, hiredate
from dept d, emp e
where d.deptno=e.deptno;
5. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
select dname, e.*
from dept d, emp e
where d.deptno=e.deptno
and dname='销售部';
-----------------------
select ename
from emp, (select deptno from dept where dname='销售部') t
where emp.deptno = (t.deptno);
6. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
select t1.empno, t1.ename, t1.sal, dname, t2.ename, t4.grade, t5.avg_
from emp t1, emp t2, dept t3, salgrade t4, (select avg(sal) avg_
from emp) t5
where t1.mgr=t2.empno
and t1.deptno=t3.deptno
and t1.sal between losal and hisal
and t1.sal>t5.avg_;
--------------------------------
select t1.empno, t1.ename, t1.sal, dname, t2.ename, t4.grade, t5.avg_
from emp t1 left outer join emp t2
on t1.mgr=t2.empno, dept t3, salgrade t4, (select avg(sal) avg_
from emp) t5
where t1.deptno=t3.deptno
and t1.sal between losal and hisal
and t1.sal>t5.avg_;
7.列出与庞统从事相同工作的所有员工及部门名称。*/
select dname, e.*
from emp e, dept d
where e.deptno=d.deptno
and job in (select job from emp where ename='庞统');
------------------
select dname, e.*
from emp e, dept d, (select job from emp where ename='庞统') t
where e.deptno=d.deptno
and e.job = t.job ;
8.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
9.查询2000年入职的员工
10.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
11.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
12.列出所有文员的姓名及其部门名称，部门的人数。
13.列出最低薪金大于15000的各种工作及从事此工作的员工人数。
14.列出在每个部门工作的员工数量、平均工资。
15.找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
16.显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列
学号 姓名 完成的题号
------------------------------------
0000 黎小龙 1,2,3,4,5,16
网友评论