Mysql

本文详细介绍了MySQL数据库的相关知识,包括数据库设计的三大范式、常用端口和超级用户、存储引擎对比、查询限制、ER图设计、需求分析和设计步骤。还讨论了E-R图的作用、存储过程、触发器、索引、视图、事务特性、数据类型、约束、模糊查询、外键约束、子查询以及SQL优化策略。此外,还涉及了数据库事务的四大特性、创建和优化数据库及视图的方法,以及一些具体的SQL查询示例。

MySql

1. 简述数据库设计三个范式的含义?

第一范式确保每列的原子性
第二范式要求每个表只描述一件事情
第三范式要求表中各列必须和主键直接相关,不能间接相关

2. MySQL默认的端口是多少?默认的超级用户是什么?翱翔机房超级用户密码是什么?如何启动mysql?

3306 root root net start mysql

3. MySQL常用的存储引擎有哪些?有何区别?

MyISAM、InnoDB

名称InnoDBMyISAM
事务处理支持不支持
数据行锁定支持不支持
外键约束支持不支持
全文索引不支持支持
表空间大小较大约2倍较小

适用场合
使用MyISAM: 不需事务,空间小,以查询访问为主
使用InnoDB: 多删除、更新操作,安全性高,事务处理及并发控制

4. 请说明在MySQL中如何限定查询结果显示的条数。

MySQL查询语句中使用LIMIT子句限制结果集条数

5. 想要开发一个投票管理系统,请根据下面的需求,按照数据库设计的步骤,绘制出ER图。
投票类型有单选和多选
多选投票可以显示选择的数量
每个投票有多个选项组成
需要记录每个人的投票记录

参考的表设计:
投票类型:编号、类型名称
投票:编号、投票主题、投票类型、选择数量
选项:编号、选项名称、投票编号
用户表:编号、用户名、密码等等
投票记录:编号、用户编号、投票编号、选项编号、投票日期

6. 简述在需求分析阶段、概要设计阶段和详细设计阶段设计数据库的步骤。

需求分析阶段:分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图,确认需求信息的正确和完整
详细设计阶段:应用三大范式审核数据库结构

7. 什么是E-R图?在数据库设计中的作用是什么?

E-R图:数据库设计的图形化表达方式。以此为模板设计数据库。

8. 请写出设计数据库的三大范式。

第一范式确保每列的原子性
第二范式要求每个表只描述一件事情
第三范式要求表中各列必须和主键直接相关,不能间接相关

9. 举例说明in与exists关键字在子查询中的应用场合。

in 确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。
exists 指定一个子查询,检测行的存在。遍历循环外表,然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

10. 什么是存储过程?用什么来调用?

存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。

使用call命令调用

11. 什么是触发器?触发器的作用?它的触发事件和触发时机都有哪些?

触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由个事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

触发事件 : insert,delete, update

触发时机 : after, before

12. 索引的作用?和它的优点缺点是什么?

创建索引可以大大提高系统的性能:
1.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
5.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

13. 什么叫视图? 创建视图的好处有哪些?

视图是一种查看数据库中一个或多个表中数据的方法。视图是一种虚拟表,通常是作为来自一个或多个表的行或列的子集创建的。

作用: 1. 数据库视图隐藏了数据的复杂性。 2. 数据库视图有利于控制用户对表中某些列的访问。 3. 数据库视图使用户查询变得简单。

14. 什么是事务?它的特性有哪些?

事务时作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性,即原子性、一致性、隔离性及持久性。

15. drop,delete与truncate的区别

1、delete 和 truncate 仅仅删除表数据,drop 连表数据和表结构一起删除,truncate 会初始化自增列的初始值
2、delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚
3、执行的速度上,drop>truncate>delete

16. 写出3种以上mysql数据库常用的数据类型

int,float,double,decimal,char,varchar,date,datetime

17. 写出3种以上的mysql约束,并介绍

普通索引
基本索引类型
允许在定义索引的列中插入重复值和空值
唯一索引
索引列数据不重复
允许有空值
主键索引
主键列中的每个值是非空、唯一的
一个主键将自动创建主键索引
复合索引
将多个列组合作为索引
全文索引
支持值的全文查找
允许重复值和空值
空间索引
对空间数据类型的列建立的索引

18. Mysql的自增和sqlserver的自增有什么区别

mysql:auto_increment
sqlserver:identity(1,1)
mysql的自增列允许手动更改
sqlserver的自增列不允许手动更改

19. 写出常用的模糊查询和常用的匹配符

like in between

%替代 0 个或多个字符
_替代一个字符
[charlist]字符列中的任何单一字符
[^charlist] 或 [!charlist]不在字符列中的任何单一字

20. 简单介绍外键约束

MySQL 外键约束(FOREIGN KEY)用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。

21. 简单介绍子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询,在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一个表或者多个表。

22. 哪个关键字可以按指定行数返回查询结果集,每页显示3条数据,显示学生表第2页数据怎么写。

limit
select * from student limit 3,3

23. 查询多表数据,可以使用那些方式来查询

内连接 拼接查询结果 内部有联系,使用内连接 格式: [inner] join
外连接 以某一张表为基表 进行相关查询 left/right [outer] join
交叉连接 连接查询 cross join
自然连接 natural join 自然连接

24. 写出创建视图的语法

CREATE
    VIEW house.view_name 
    AS
(SELECT * FROM ...);

25. 写出事务的4个特性

  1. 原子性
    事务必须是原子工作单元,事务中的操作要么全部执行,要么全都不执行,不能只完成部分操作。原子性在数据库系统中,由恢复机制来实现。
  2. 一致性
    事务开始之前,数据库处于一致性的状态;事务结束后,数据库必须仍处于一致性状态。数据库一致性的定义是由用户负责的。例如,在银行转账中,用户可以定义转账前后两个账户金额之和保持不变。
  3. 隔离性
    系统必须保证事务不受其他并发执行事务的影响,即当多个事务同时运行时,各事务之间相互隔离,不可互相干扰。事务查看数据时所处的状态,要么是另一个并发事务修改它之前的状态,要么是另一个并发事务修改它之后的状态,事务不会查看中间状态的数据。隔离性通过系统的并发控制机制实现。
  4. 持久性
    一个已完成的事务对数据所做的任何变动在系统中是永久有效的,即使该事务产生的修改不正确,错误也将一直保持。持久性通过恢复机制实现,发生故障时,可以通过日志等手段恢复数据库信息。

26. 创建数据库schoolDB。如果数据库schoolDB已存在,那么,先删除再创建。

drop datebase if exists schoolDB;
create datebase schoolDB;

27. 根据表结构,创建4张表。如果该表已存在,则先删除再创建该表。

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(4) NOT NULL AUTO_INCREMENT,
  `cName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `tid` int(4) DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(4) NOT NULL AUTO_INCREMENT,
  `cid` int(4) DEFAULT NULL,
  `score` int(4) DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(4) NOT NULL AUTO_INCREMENT,
  `stuName` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(4) NOT NULL AUTO_INCREMENT,
  `teaName` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

28. 添加student表、course表、score表的主外键约束以及course表和teacher表的主外键关联。

alter table course add CONSTRAINT `course_teacher` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`);

alter table score add CONSTRAINT `score_student` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`);

alter table score add  CONSTRAINT `score_course` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`);

29. 查询学过“方芳”老师所教课程的学生的学号、姓名。(假定学生所学的课程均参加了考试)。

SELECT
	student.* 
FROM
	student
	JOIN score ON student.sid = score.sid
	JOIN course ON course.cid = score.cid
	JOIN teacher ON teacher.tid = course.tid 
WHERE
	teacher.teaName = '方芳'

30. 查询所有课程成绩均小于60分的学生学号和姓名。

-- 最高分低于60的学生,成绩均不及格
SELECT
	student.sid, stuName 
FROM
	student 
WHERE
	sid IN (
SELECT
	student.sid 
FROM
	student
	JOIN score ON student.sid = score.sid 
GROUP BY
	student.sid 
HAVING
	max( score ) < 60 
	)
-- 先找大于60分的,取反

SELECT DISTINCT
	student.sid, stuName  
FROM
	student
	JOIN score ON student.sid = score.sid 
WHERE
	student.sid NOT IN ( SELECT sid FROM score WHERE score > 60 )
	

31. 查询“方芳”老师所教的“数据库”课程成绩排名在第3~6名的学生学号、姓名,并将此记录插入新表tempScore中。(要求使用limit子句)

SELECT
	student.sid,stuName
FROM
	student
	JOIN score ON student.sid = score.sid
	JOIN course ON course.cid = score.cid
	JOIN teacher ON teacher.tid = course.tid 
WHERE
	teacher.teaName = '方芳' 
	AND course.cName = '数据库' 
ORDER BY
	score DESC 
	LIMIT 2,4

32. 查询有两门以上课程不及格的学生学号及其平均成绩。

SELECT
	student.sid,
	avg(score)
FROM
	student
	join score on student.sid = score.sid
	where student.sid in (
		SELECT sid AS avgScore FROM score WHERE score < 60 GROUP BY cid HAVING count( * ) > 2
	)
	GROUP BY student.sid

33. 创建视图student_view,用于查询所有学生的学号、姓名、参加考试课程数、各科总成绩。

CREATE VIEW student_view AS (
SELECT
	student.sid,
	student.stuName,
	score.courseNum,
	score.sumScore 
FROM
	student
	JOIN ( SELECT sid, count( * ) AS courseNum, sum( score ) AS sumScore FROM score GROUP BY sid ) AS score ON score.sid = student.sid 
	)

34. 查询平均成绩是倒数三名内的男生学号,姓名,平均成绩

SELECT
	student.*,
	avg( score ) 
FROM
	student
	JOIN score ON student.sid = score.sid 
WHERE
	student.sex = 1 
GROUP BY
	student.sid 
ORDER BY
	avg( score ) 
	LIMIT 3

35. 创建函数,查询某学生成绩属于哪一个分数段,90分及以上为A,8090分为B,7080分为C,60~70分为D,60分以下为E。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fun_a`( `name` VARCHAR ( 20 ), courseName VARCHAR ( 20 ) ) RETURNS char(2) CHARSET utf8
BEGIN
DECLARE s INT DEFAULT 0;
	SELECT
		score INTO s 
	FROM
		student
		JOIN score ON student.sid = score.sid
		JOIN course ON course.cid = score.cid 
	WHERE
		student.stuName = `name` 
		AND course.cName = courseName;
	IF
		s >= 90 THEN
			RETURN 'A';
		
		ELSEIF s >= 80 THEN
		RETURN 'B';
		
		ELSEIF s >= 70 THEN
		RETURN 'C';
		
		ELSEIF s >= 60 THEN
		RETURN 'D';
		ELSE RETURN 'E';
		
	END IF;
	END $$
DELIMITER ;

36. 如何优化数据库SQL语句,至少列出4条。

​ (1)对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
​ (2)查询时应尽量不使用模糊查询
​ (3)应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
​ (4)查询总条数时不要使用COUNT(*)

37. 数据库事务有哪些特性,并用一句解释这些特性。

​ (1)原子性:事务包含的所有操作要么全部成功,要么全部失败回滚;成功必须要完全应用到数据库,失败则不能对数据库产生影响;

​ (2)一致性:事务执行前和执行后必须处于一致性状态;

​ (3)隔离性:当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不被其他事务的操作所干扰,多个并发事务之间要相互隔离;

​ (4)持久性:一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便在数据库系统遇到故障的情况下也不会丢失事物的操作

38. 现有数据库表Student,列分别有NAME(姓名),SUBJECT(科目),SCORE(成绩),请用一条语句查询出每门课程均大于80分的学生姓名

答:

-- 子查询
select 
	name 
from 
	stu
group by name
having name not in ( select name from stu where score < 80 )

-- 最低分小于80分
select name from test.stu group by name having min(score) >= 80

39. 在teacher表上创建名为teacher_info1 的存储过程,要求:teacher_info1 有3个参数。输入参数为teacher id 和type,输出参数为info.存储过程的作用是根据编号teacher_id 来查询teacher 表中的记录。如果type的值为1时,将姓名name传给输出参数info:如果type的值为2时,将年龄传给输出参数info:如果type的值为其他值,则返回符串"Error ”

Teacher表的定义如下所示

字段名字段描述数据类型主键外键非空唯一自增
Id编号Int(4)
Num教工号Int(10)
Name姓名Varchar(20)
Sex性别Varchar(4)
Birthday出生日期Datetime
Address家庭住址Varchar(50)
DELIMITER &&
CREATE PROCEDURE 
		teacher_info(IN teacher_id int,IN type int, OUT info VARCHAR(20))
		READS SQL DATA
		BEGIN 
			CASE type
			WHEN 1 THEN
				SELECT name INTO info FROM teacher WHERE id = teacher_id;
			WHEN 2 THEN
				SELECT YEAR(NOW()) - YEAR(Birthday) INTO info
				FROM teacher WHERE id = teacher_id;
			ELSE
				SELECT 'ERROR' INTO info;
			END CASE;
		END &&
DELIMITER ;
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值