mysql学习总结2

/*
案例一
　　涉及的知识点：数据库和表的基本操作，添加数据，多表操作
题目
使用sql语句请按照要求完成如下操作：
　　（1）创建一个名称为test的数据库。

　　（2）在test数据库中创建两张表，Department部门表和Employee雇员表，表名和字段信息如下表所示。
　　　　　
      　Department部门表
　　字段类型　　是否空　　是否主键
　　  did　　　 int(4)　　  NOPRI
　　 dname　　varchar(36)　　YES
 　　　　　
    　   Employee雇员表
　　字段类型　　是否空　　  是否主键
　　id　　　　  int(4)　　   NOPRI
　　name　　　　varchar(36)   YES
　　age　　　　 int(2)　　    YES
　　did　　　　 int(4)　　    NO

　　（3）在部门表和雇员表中插入三条数据，要求雇员信息分别为：人事部张三（20岁），院校产品部李四（35岁）以及咨询部王五（19岁）。

　　（4）查出人事部员工的姓名和年龄。

　　（5）查询雇员中年龄最大的员工的姓名、年龄以及所在部门。
*/

--创建数据库
CREATE DATABASE TEST;

--使用test数据库
USE TEST;

--建表
CREATE TABLE department( 
did INT(4) PRIMARY KEY,
dname VARCHAR(36) NOT NULL )
default charset=utf8 ;

CREATE TABLE employee
(id INT(4) PRIMARY KEY,
NAME VARCHAR(36) NOT NULL,
age INT(2) NOT NULL,
did INT(4)
)default charset=utf8 ;

--向部门表中插入数据
INSERT INTO department(did,dname) VALUES(1,'人事部');
INSERT INTO department(did,dname) VALUES(2,'院校产品部');
INSERT INTO department(did,dname) VALUES(3,'咨询部');

--向员工表中插入数据
INSERT INTO employee(id,NAME,age,did) VALUES(1,'张三',20,1);
INSERT INTO employee(id,NAME,age,did) VALUES(2,'李四',35,2);
INSERT INTO employee(id,NAME,age,did) VALUES(3,'王五',19,3);

--查出人事部员工的姓名和年龄
select NAME,age from employee where did = (select did from department where dname='人事部');

--查询雇员中年龄最大的员工的姓名、年龄以及所在部门
select dname,NAME,age from department d , employee e where age = (select max(age) from employee) and d.did=e.did;


/*
案例二
　　涉及的知识点：数据库和表的基本操作，添加数据，存储过程
题目
　　已知张三在传智播客网上书城订购了的几本教材。请按照以下要求建表、定义存储过程并利用存储过程统计出教材的订购数量和总价。
要求如下：

　　（1）创建book表，字段明细如下,name代表书名，price代表书的价格：

　　字段名　　数据类型　　主键　　外键　　非空　　唯一　　自增
　　id　　　　INT(10)　  　是　  　否　  　是　  　是　　  否
　　name　 　VARCHAR(50)　 否　　  否　  　是　  　否　  　否
　　price　 　FLOAT　　    否　　  否　  　是　  　否　  　否

　　（2）向book表中添加数据明细如下：

　　id　　name　　　　　　 price
　　1　　Java基础入门　　  44.5
　　2　　MySQL数据库入门　 39.5
　　3　　网页设计与制作　　 39.5

　　（3）创建一个存储过程total，能够获取表book中的记录数和price的总和。

　　（4）调用存储过程total，统计出教材的订购数量和总价。
*/

--建表
CREATE TABLE book
(id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price FLOAT NOT NULL
)default charset=utf8;

--插入数据
INSERT INTO book
VALUE (1,'Java基础入门',44.5),
(2,'MySQL数据库入门',39.5),
(3,'网页设计与制作',39.5);

--创建存储过程total
DELIMITER //
CREATE PROCEDURE total(OUT COUNT INT)
BEGIN
DECLARE itmp FLOAT;
DECLARE cur_id CURSOR FOR SELECT price FROM book; 
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT COUNT(*) INTO COUNT FROM book;
SET @totalprice=0;
OPEN cur_id;
REPEAT 
FETCH cur_id INTO itmp;
IF itmp>0
THEN SET @totalprice= @totalprice+itmp;
END IF;
UNTIL 0 END REPEAT;
CLOSE cur_id;
END //
;

--调用存储过程total：
CALL total(@count) ;
SELECT @count,@totalprice ;



/*
案例三
带LIKE关键字的查询

需求分析

　　有时候我们需要对字符串进行模糊查询，MySQL中提供了LIKE关键字，LIKE关键字可以判断两个字符串是否相匹配。

设计思路（实现原理）

　　1）创建数据库chapter04

　　2）在数据库chapter04中创建表employee

　　3）向employee表中插入6条记录

　　4）查询employee表中名字中含有字母“a”的员工信息

　　5) 查询employee表中名字以“J”开头并且有5个字母的员工信息
*/

--1、创建数据库chapter04并使用该数据库
CREATE DATABASE chapter04;
USE chapter04;

--2、在数据库chapter04中创建表employee，创建employee表的SQL语句如下所示：
CREATE TABLE employee(
    id INT(3) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    dept VARCHAR(20),
    gender CHAR(10)
)default charset=utf8;

--3、执行SQL语句创建employee表，然后使用INSERT语句向employee表中插入6条记录，INSERT语句如下所示：
INSERT INTO employee(name,dept,gender)VALUES
('Tom','Business Office','man'),
('Jack','Business Office','man'),
('Jeden','Business Office','man'),
('Tina','Business Office','woman'),
('Jama','Personnel Department','woman'),
('Juli','Personnel Department','woman');

--4、查询employee表中名字中含有字母“a”的员工信息。
select * from employee where name like "%a%";

--5、查询employee表中名字以“J”开头并且有5个字母的员工信息。
select * from employee where name like "J____";

/*
    在本案例中，百分号和下划线统称为通配符，它们在通配字符串中有特殊含义。

　　1、百分号(%)通配符匹配任意长度的字符串，包括空字符串。
    例如，字符串“%a%”匹配字含有字符a的任意长度的字符串，如“Jack”、“Tina”、“Jama”等。

　　2、下划线通配符只匹配单个字符，如果要匹配多个字符，需要使用多个下划线通配符。
    例如，字符串“J____”匹配以字符“J”开始，长度为5的字符串。
    需要注意的是，如果使用多个下划线匹配多个连续的字符，下划线之间不能有空格，
    例如，通配字符串“M_ _QL”只能匹配字符串“My SQL”，而不能匹配字符串“MySQL”。
*/



/*
案例四  分组统计各班级的分数

需求分析
　　GROUP BY和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等等。
    GROUP BY和HAVING关键字一起使用，用于对分组后的结果进行过滤。
　　设计思路（实现原理）

　　　　1）使用数据库chapter04

　　　　2）在数据库chapter04中创建表score

　　　　3）用INSERT语句向score表中插入8条记录

　　　　4）统计出score表中1班和2班的最高分

　　　　5）计算出两个班级的总平均分，然后统计出平均分小于总平均分的班级
*/

--创建表
CREATE TABLE score (
id INT(3) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
score FLOAT,
class INT
)default charset=utf8;

--插入数据
INSERT INTO score (name,score,class)
VALUES('Tom',98,1),
('Jack',34,1),
('Jeden',77,1),
('Tina',97,1),
('Jama',92,2),
('Lucy',67,2),
('Lily',59,2),
('Juli',77,2);

--统计出score表中1班和2班的最高分
select class,max(score) from score group by class;

--统计出平均分小于两个班级的总平均分的班级
-- 1)计算两个班级的总平均分
select avg(score) from score;

--2) 统计出平均分小于75.125的班级
select class,avg(score) as avs from score group by class having avs<=(select avg(score) from score);

/*案例总结
  HAVING关键字和WHERE关键字的作用相同，都用于设置条件表达式对查询结果进行过滤，两者的区别在于，
  HAVING关键字后可以跟聚合函数，而WHERE关键字不能。通常情况下HAVING关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。
  where 后面跟的条件可以是select语句中没有出现的字段，having 后面跟的条件必须是select语句出现的字段名称
*/


  
你是个男人，赚钱是你唯一的出路，
即使没有清晨熬好的粥，也没有灯火通明的家，
你也应该对自己的人生负责。
生如蝼蚁，当有鸿鹄之志，
命虽薄纸，应有不屈之心，
欲要成功，必有其梦，
欲戴王冠，必承其重。