MySql阶段案例
来源:互联网 发布:网络综艺访谈节目 编辑:程序博客网 时间:2024/06/05 09:11
MySql阶段案例
案例一
涉及的知识点:数据库和表的基本操作,添加数据,多表操作
题目
使用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)查询雇员中年龄最大的员工的姓名、年龄以及所在部门。
答案
(1)-建库
CREATE DATABASE TEST;
(2)-建表
USE TEST;CREATE TABLE department (did INT(4) PRIMARY KEY, dname VARCHAR(36) NOT NULL );
CREATE TABLE employee (id INT(4) PRIMARY KEY, NAME VARCHAR(36) NOT NULL, age INT(2) NOT NULL, did INT(4));
(3)--向表中插入数据
向部门表插入数据:
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);
(4)-条件查询
SELECT NAME, ageFROM employeeWHERE did = (SELECT didFROM departmentWHERE dname = '人事部');
(5)-条件查询
SELECT dname, NAME,ageFROM department d,employee eWHERE 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,统计出教材的订购数量和总价。
答案
(1)-建表:
CREATE TABLE book (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price FLOAT NOT NULL);
(2)--向表中插入数据:
INSERT INTO bookVALUE (1,'Java基础入门',44.5),(2,'MySQL数据库入门',39.5),(3,'网页设计与制作',39.5);
(3)--创建存储过程total:
DELIMITER //CREATE PROCEDURE total(OUT COUNT INT)BEGINDECLARE 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;REPEATFETCH cur_id INTO itmp;IF itmp>0THEN SET @totalprice= @totalprice+itmp;END IF;UNTIL 0 END REPEAT;CLOSE cur_id;END //
(4)--调用存储过程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));
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”的员工信息。
mysql> SELECT * FROM employee WHERE name LIKE "%a%";+----+------+----------------------+--------+| id | name | dept | gender |+----+------+----------------------+--------+| 2 | Jack | Business Office | man || 4 | Tina | Business Office | woman || 5 | Jama | Personnel Department | woman |+----+------+----------------------+--------+3 rows in set (0.00 sec)
5、查询employee表中名字以“J”开头并且有5个字母的员工信息。
mysql> SELECT * FROM employee WHERE name like "J____";+----+-------+-----------------+--------+| id | name | dept | gender |+----+-------+-----------------+--------+| 3 | Jeden | Business Office | man |+----+-------+-----------------+--------+1 row in set (0.00 sec)
由查询结果可以看出,用like关键词查询出了符合要求的员工信息。
案例总结
在本案例中,百分号和下划线统称为通配符,它们在通配字符串中有特殊含义。
1、百分号(%)通配符匹配任意长度的字符串,包括空字符串。例如,字符串“%a%”匹配字含有字符a的任意长度的字符串,如“Jack”、“Tina”、“Jama”等。
2、下划线通配符只匹配单个字符,如果要匹配多个字符,需要使用多个下划线通配符。例如,字符串“J____”匹配以字符“J”开始,长度为5的字符串。需要注意的是,如果使用多个下划线匹配多个连续的字符,下划线之间不能有空格,例如,通配字符串“M_ _QL”只能匹配字符串“My SQL”,而不能匹配字符串“MySQL”。
分组统计各班级的分数
3、需求分析
GROUP BY和聚合函数一起使用,可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等等。GROUP BY和HAVING关键字一起使用,用于对分组后的结果进行过滤。
设计思路(实现原理)
1)使用数据库chapter04
2)在数据库chapter04中创建表score
3)用INSERT语句向score表中插入8条记录
4)统计出score表中1班和2班的最高分
5)计算出两个班级的总平均分,然后统计出平均分小于总平均分的班级
二、案例实现
1、使用数据库chapter04
USE chapter04;
2、在数据库chapter04中创建表score,创建score表的SQL语句如下所示:
CREATE TABLE score (id INT(3) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,score FLOAT,class INT);
3、执行SQL语句创建score表,然后使用INSERT语句向score表中插入8条记录,INSERT语句如下所示:
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);
5、统计出score表中1班和2班的最高分
mysql> SELECT class,MAX(score) FROM score GROUP BY class;+-------+------------+| class | MAX(score) |+-------+------------+| 1 | 98 || 2 | 92 |+-------+------------+2 rows in set (0.00 sec)
6、统计出平均分小于两个班级的总平均分的班级
1)计算两个班级的总平均分
mysql> SELECT AVG(score) FROM score;+------------+| AVG(score) |+------------+| 75.125 |+------------+1 row in set (0.00 sec)
2)统计出平均分小于75.125的班级
mysql> SELECT class, AVG (score) FROM score GROUP BY classHAVING AVG (score) <75.125;+-------+-------------+| class | AVG (score) |+-------+-------------+| 2 | 73.75 |1 row in set (0.00 sec)
案例总结
1、实际开发中,经常需要对某些数据进行统计,例如统计某个字段的最大值、最小值、平均值等等,为此,MySQL中提供了一些函数来实现这些功能,具体如下表所示。
函数名称作用
COUNT()返回某列的行数
SUM()返回某列值的和
AVG()返回某列的平均值
MAX()返回某列的最大值
MIN()返回某列的最小值
3、HAVING关键字和WHERE关键字的作用相同,都用于设置条件表达式对查询结果进行过滤,两者的区别在于,HAVING关键字后可以跟聚合函数,而WHERE关键字不能。通常情况下HAVING关键字都和GROUP BY一起使用,用于对分组后的结果进行过滤。
使用字符串函数
需求分析
MySQL中,提供了字符串函数用于对字符串进行各种处理
4、设计思路(实现原理)
1)使用数据库chapter04。
2)在数据库chapter04中创建表content
3)用INSERT语句向content表中插入1条记录
4)用LENGTH(str)函数查询content表中id为1的info字段的字符长度
5)用CONCAT(s1,s2,……)函数将content表中id为1的 info字段中数据与“:”和“Beijing IT WisdomCast Corporation Limited”字符串连接起来
案例实现
1、使用数据库chapter04
USE chapter04;
2、在数据库chapter04中创建表content,创建content表的SQL语句如下所示:
CREATE TABLE content (id INT(3) PRIMARY KEY AUTO_INCREMENT,info VARCHAR(50) NOT NULL);
3、执行SQL语句创建content表,然后使用INSERT语句向content表中插入1条记录,INSERT语句如下所示:
INSERT INTO content (info) VALUES('IT educational institutions');
4、查询content表中id为1的info字段的字符长度
mysql> SELECT LENGTH(info) FROM content WHERE id=1;+--------------+| LENGTH(info) |+--------------+| 27 |+--------------+1 row in set (0.00 sec)
5、将content表中id为1的 info字段中数据与“:”和“Beijing IT Wisdom Cast Corporation Limited”字符串连接起来
mysql> SELECT CONCAT(info,':','Beijing IT Wisdom Cast Corporation Limited')FROM content WHERE id=1;+-------------------------------------------------------------------------+| CONCAT(info,':','Beijing IT Wisdom Cast Corporation Limited') |+-------------------------------------------------------------------------+| IT educational institutions:Beijing IT Wisdom Cast Corporation Limited |+-------------------------------------------------------------------------+1 row in set (0.00 sec)
案例总结
1.除了本案例中使用的LENGTH(str)和CONCAT(s1,s2,……)函数,MySQL还提供了一些函数用于字符串的处理,如下表所示。
函数名称 作用
LENGTH(str) 返回字符串str的长度
CONCAT(s1,s2,……) 返回一个或者多个字符串连接产生的新的字符串
TRIM(str) 删除字符串两侧的空格
REPLACE(str,s1,s2) 使用字符串s2替换字符串str中所有的字符串s1
SUBSTRING(str,n,len) 返回字符串str的子串,起始位置为n,长度为len
REVERSE(str) 返回字符串反转后的结果
LOCATE(s1,str) 返回子串s1在字符串str中的起始位置
- MySql阶段案例
- mysql知识点阶段总结
- MySQL(阶段一)
- MySQL(阶段二)
- MySQL两阶段提交
- MYSQL阶段题目1.1
- MySQL学习阶段小结
- 产品推广阶段之营销参考案例
- MySql-两阶段加锁协议
- 案例研究:“大淘宝战略”规划阶段分析
- Jsp+Servlet+JavaBean学习阶段案例-留言板(MessageBoard)
- 【雪野实训记录】Oracle聊天室-L1阶段项目案例
- php+mysql案例1
- php+mysql案例2
- mysql索引测试案例
- mysql精彩案例
- jsp连接mysql案例
- mysql优化案例分析
- Mysql综合案例
- ADODB.Connection对象的Execute方法
- 国庆去哪人少,大数据来告诉你
- 最近最少使用
- redis 主从复制
- MySql阶段案例
- 暴力穷举法-------查找序列的最大和值
- Linux 学习路程(4)安装g++
- AndroidStuido提示Git命令找不到问题解决
- 用java写出第一个窗口
- Java中BeanUtils插件的安装和基本使用
- c#在dataGridView中增加一列并赋值
- SLIC超像素
- layui学习笔记