MySQL数据库期末

1.数据库设计：

根据需求画出实体属性图、实体联系图，写出关系模式，并指出主码

2.数据库的CRUD操作

查看数据库：show databases（like ‘my%’）
创建数据库：create database 数据库名；
删除数据库：drop database 数据库名；
选中数据库：use 数据库名；
查看MySQL支持的存储引擎：show engines；
设置默认的存储引擎：set default_storage_engine=InnoDB;

创建一个访问者留言表

create database mytest;
use mytest;
create table guestbook(
visitor varchar(40),
comments text,
entrydate datetime);

查看表

show tables;
show tables from mysql;
describe test.choose;
desc test.choose;
alter table student add weight int;
rename table student to student1;
alter table student1 rename student2;
drop table class;
insert into student values(20200006,'刘八',13800000011,2,50);
insert into student(student_no,student_name)values(1111,'xxx')

select 语法

select 字段列表
from 数据源
where 条件表达式
group by 分组字段
having 条件表达式
order by 排序字段【asc|desc】

查询Student表中全体学生的学号Sno与姓名Sname

select Sno,Sname
from student;

查询全部列：

select *
from student;

使用列别名改变查询结果的列标题

select Sname as sn,2016-Sage 出生年份
from student;

使用insert….select插入结果

create table
teacher2(id int auto_increment primary key,
teacher_no int,
reacher_name varcahr(20)
default charset=gbk);

insert into teacher2(teacher_no,teacher_name)
select teacher_no,teacher_name
from teacher1;

使用replace插入新记录

replace into teacher1 values(5,50000,"zhangsan");

表记录的修改

update teacher2
set teacher_name="shangsan"
where teacher_name="张三";

使用delete删除表记录

delete from teacehr1
where teacher_name="zhangsan";
delete from teacher1;

所有记录都将被删除，但表结构依旧存在

使用truncate清空表记录

truncate teacher1;

3.（重点）课本+PPT

#消除取值重复的行 DISTINCT
select distinct Sno
from SC;
#DISTINCT作用于整个查询的属性域，而不仅仅是临近的属性

#使用谓词限制记录的行数
select * from student limit 0,3;
select * from student limit 3;
#使用单一的条件过滤结果集
select student_no,student_contact,student_name,department_name
from student,classes
where student.class_no=class_no;
#结果集时出现乱码问题
set character_set_results=gbk;

select *
from course
where
course_name="java 语言程序设计";

#使用逻辑运算符
select *
from course
where !(up_limit=60);
或者up_limit!=60

检索“MySQL数据库”课程不及格的学生名单（不包括缺考学生）
select student.student_name,student_name
from course join  choose on 
course.course_no=choose.course_no
join student on
choose.student_no=student.student_no
where course.course_name="mysql数据库"
and score<60
[例] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的
selectm Sname,Sdept,Sage
from student
where Sage between 20 and 23;
[例]  查询年龄不在20~23岁之间的学生姓名、系别和年龄
select Sname,Sdept,Sage
from student
where Sage not between 20 and 23;
【例】检索所有姓“张”、姓“田”的学生信息。
select *
from student 
where substring(student_name,1,1)="张" or  substring(student_name,1,1)="田";
【例】检索所有姓“张”、姓“田”的学生信息
select *
from student
where substring(student_name,1,1) 
in("张" "田")；
[例]  查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
select Sname,Ssex
from student
where Sdept in('IS','MA','CS');
[例]  查询所有姓刘学生的姓名、学号和性别
select Sname,Sno,Ssex
from student
where Sname like "刘%";
[例]  查询姓"欧阳"且全名为三个汉字的学生的姓名
select Sname
from student
where Sname like "欧阳_";
[例]  查询名字中第2个字为"阳"字的学生的姓名和学号
select Sname,Sno
from student
where Sname like "_阳%";
[例]  查询所有不姓刘的学生姓名
select Sname,Sno,Ssex
from student
where Sname not like '刘%';
【例】检索new_student表中学生姓名（ student_name ）中所有带“_”的学生信息
select * 
from new_student
where student_name like '%\_%';
可以使用escape关键字自定义一个转义字符，例如下面的SQL语句使用字符“!”作为转义字符
select *
from new_student
where student_name like '%!_%' escape '!';
[例]  查询DB_Design课程的课程号和学分
select Cno,Ccredit
from Course
where Cname like 'DB\_Design' escape '\';

[例]  查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况
select *
from Course
where Cname like  'DB\_%i__' escape '\';

[例]  查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列
select Sno,Grade
from Sc
where Cno='3'
order by Grade desc;

［例］  查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select *
from student
order by Sdept,Sage desc;

聚集函数

计数

COUNT（*）: 统计元组个数

COUNT（[DISTINCT|ALL] <列名>）：统计一列中值的个数

计算总和

SUM（[DISTINCT|ALL] <列名>）：计算一列的总和

计算平均值

AVG（[DISTINCT|ALL] <列名>）：计算一列的平均值

最大最小值

 MAX（[DISTINCT|ALL] <列名>）：求一列值中的最大值

 MIN（[DISTINCT|ALL] <列名>）：求一列值中的最小值

[例]  查询学生总人数
select count(*)
from student;
[例]  查询选修了课程的学生人数
select count(distinct Sno)
from SC;
[例]  计算1号课程的学生平均成绩
select avg(Grade)
from SC
where Cno="1";
[例]  查询选修2号课程的学生最高分数
select max(Grade)
from SC
where Cno='2';

使用group by子句对记录分组统计
【例】按班级 将学生信息进行分组
select * 
from student
group by class_no;
[例]  查询平均成绩大于等于90分的学生学号和成绩
SELECT
	sno,
	AVG(grade)
FROM
	sc
GROUP BY
	sno
HAVING
	AVG(grade) >= 90;
查询平均成绩大于等于80分的学生学号和成绩	
select Sno,avg(Grade)
from SC
Group by Sno
having avg(Grade)>=80;

【例】统计每一个班的学生人数
select class_name,count(student_no)
from class left join student
on student.class_no=class.class_no
group by class.calss_no;
【例】统计每个学生已经选修多少门课程，该生的最高分、最低分、总分及平均成绩
select student.student_no,student_name,count(course_no),max(score),min(score),
sum(score),avg(score)
from student left join choose on
student.student_no=choose.student_no
group by student.student_no;

系统变量，用户会话变量，局部变量的表示方式；
系统变量：@@ 用户会话变量：@ 局部变量：不以@开头
- 如何给用户会话变量，局部变量赋值
  - set或者select
    - set @user_variable1=expression1
    - select @user_variable1:=expression1;
  - declare s1 int;
- 哪些赋值语句产生结果集，哪些不产生结果集
  - 产生结果集：
    - select @user_variable1:=exception1
  - 不产生结果集：
    - select expression1 into @user_variable1
    - set ...
- ENUM和SET类型的适用范围
  - ENUM类似于单元按钮的功能
  - SET类似于复选框的功能
MySQL函数
- 如何自定义函数
  create function 函数名(参数1，参数2，...)
  returns 返回值类型
  [函数选项]
  begin
  函数体;
  return 语句;
  end;
  - 如何给表添加行号
    delimiter $$
    create function row_no_fn() returns int
    no sql
    begin
    set @row_no=@row_no+1;
    return @row_no;
    end;
    $$
    delimiter;
    set @row_no=0;
    select row_no_fn() 行号,student_no,student_name from student;
  - 1到n的累加
    delimiter $$
    create function get_sum_fn(n int)returns int
    no sql
    begin
    declare sum char(20) default 0;
    declare start int default 0;
    add_sum:while true do
    set start=start+1;
    set sum=sum+start;
    if(start=n) then
    leave add_sum;
    end if;
    end while add_sum;
    return sum;
    end;
    $$
    select get_sum_fn(n);
  - 根据学号获得姓名
    delimiter $$
    create function get_name(@stu_no varchar(30))returns varchar(100)
    as
    begin
    declare @result varchar(100)
    select @result=name
    from grade
    where sno=@stu_no
    return @result;
    end;
    $$
- 计算字符串的长度
  select char_length(str);
- 获取子串
  select right("football",4);
- 将字符串逆序
  select reverse('abcd');
- 使用encode，decode加密解密
  select encode('mysql','mr');
  select decode(encode('mysql','mr'),'mr');
- 计算两个日期相差的天数
  SELECT TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
课后第六题
create table enjoy(
stuNo char(20) not null primary key,
stuSex char(2) not null default '男' check(stuSex='男' or stuSex='女'),
stuID int(20) unique,
stuAge int(3) not null,
stuSeat smallint unique auto_increment,
stuAddress char(50) default '地址不详'
);
什么是索引？使用索引有什么意义？
概念：索引是一个表中所包含值的列表，其中注明了表中包含各个值的行所在的存储位置
意义：索引可以提高系统的性能，大大加快数据检索的速度
视图的作用？
-视图能简化用户操作
-增强数据安全性
-避免数据冗余
-提供数据的逻辑独立性
如何创建视图（会写代码，书上的例题）
use choose;
create view view1(student_no,student_name,class_no)
as
select student_no,student_name,class_no
from student;
什么是存储过程？
存储过程是一种数据库对象，它是由一组预先编辑好的SQL语句以一个存储单元的形式存储在数据库服务器上，由用户直接调用执行。
创建并调用存储过程（书上例题）
use choose
delimiter $$
create procedure student_count_proc()
reads sql data
begin
select count(*) from student;
end
$$
call student_count_proc();

posted @ 2021-11-04 22:10 青歌与阅读(258) 评论(0) 收藏举报

刷新页面返回顶部

青歌与

少说脏话，多说你好。你好，你好，真就你好！

MySQL数据库期末

创建一个访问者留言表

查看表

select 语法

查询Student表中全体学生的学号Sno与姓名Sname

查询全部列：

使用列别名改变查询结果的列标题

使用insert….select插入结果

使用replace插入新记录

表记录的修改

使用delete删除表记录

所有记录都将被删除，但表结构依旧存在

使用truncate清空表记录

公告