科林网络_2 MySQL数据库

账户:mysql

密码:123456

root用户必须是本地回环地址

一、SQL语言基础

1.1 数据库介绍

1.2 创建表

创建数据库,注意数据库名称不能只有数字

成功

设置为默认库

设置默认的库
use kelin;
创建表
create table 表名(列名 数据类型 建表约束,列名 数据类型 建表约束,...)

字符串:char()、nchar()、nvarchar()、varchar()
char(45):字符串长45,不足补0,一列最大能存储8k个字节,“helo0000000000”
nchar(45),n表示宽字节,一个字符占两个字节,最大只能4k个字节
varchar(45),字符串最长是45,占用空间以实际存储内容为准,"hello"

建表约束:
主键(primary key):每个表只能有一列是主键列,主键内容不能重复,不能为空
唯一(unique):可以有多列,内容不能重复,可以为空
不为空(not null):
默认值(default):
自增(auto_increment):
外键约束:与其他表有关系

注意事项:
1.注释使用#
2.数据库不区分大小写,自动转成小写
3.sql语句以 ; 为结束标志

create table studentinfo(
number int primary key auto_increment,
name varchar(10) not null,
sex enum('男','女') not null,
age int default 18
);

保存

选中要保存的代码

           

保存选中的代码(没有选中则保存全部直到遇到报错)、保存光标行代码 

注意,表只能创建一次 

1.3 删除表

删除表:drop table 表名;

drop table studentinfo;

1.4 修改表

修改表:alter table 表名 ...;

1.4.1 增加列

增加列:alter table 表名 add column 列名 数据类型 建表约束;

alter table studentinfo add column school int;

1.4.2 修改列属性

修改列属性:alter table 表名 modify 列名 数据类型 建表约束;

alter table studentinfo modify school varchar(15);

1.4.3 删除列

删除列:alter table 表名 drop column 列名;

alter table studentinfo drop column school;

1.5 查看表

1.6 插入数据

1.6.1 全部列插入

全部列插入:数据的顺序要和表中列的顺序一致
insert into 表名 values (数据1,数据2,...);

insert into studentinfo values(1,'李永昕','男',20);
insert into studentinfo values(2,'李若兮','女',7);

1.6.2 部分列插入

部分列插入:列名顺序和数据顺序一一对应
insert into 表名(列名1,列名2,...)values(数据1,数据2,...);

insert into studentinfo (name,sex) values('伍兹以','男');
insert into studentinfo (sex,age,name) values('女',19,'黄经远');

1.7 修改(更新)数据 

1.7.1 修改整列

update 表名 set 列名 = 新的值;

update studentinfo set sex='女';

需要关闭安全模式

1.7.2 修改某列

修改某列:update 表名 set 列名 = 新的值 where 条件

update studentinfo set sex='男' where number = 1;
update studentinfo set sex='男' where name = '伍兹以';
update studentinfo set sex='男' where number = 4;

1.8 删除数据 

1.8.1 删除所有数据

删除所有数据:delete from 表名

delete	from studentinfo;

   

注意:sql的自动递增是不会重复的

1.8.2  删除部分数据

删除部分数据:delete from 表名 where 条件;

delete from studentinfo where name = '黄经远';

二、

2.2 数据库范式

 

解决表与表之间的关系 

2.1.1 第一范式(1NF)

前者满足第一范式,后者联系方式可分

2.1.2 第二范式(2NF)

所有的非主属性都由全部的关键字(主属性)共同决定

2.1.3 第三范式(3NF)

2.1.4 鲍依斯-科德范式(BCNF)

三、

 1.9 查询语句

1.9.1 查询全部数据

查询全部数据:select * from 表名;

select * from studentinfo;

1.9.2 条件查询

1.9.2.1 查询某几列

查询某几列:select 列名1, 列名2, ... from 表名;

select name, age from studentinfo;
1.9.2.2 给查询到的列起别名

给查询到的列起别名:select 列名1 别名1, 列名2, ... from 表名;

select name 姓名, age 年龄,sex from studentinfo;
1.9.2.3 查询某数据

查询某数据:select * from 表名 where 条件;

select * from studentinfo where age < 20;
1.9.2.4 条件表达式

相等: 列名 = 值;
不等:列名 != 值;(只有MYSQL支持的)列名 <> 值;
大于、大于等于、小于、小于等于:列名 > 值;列名 >= 值;列名 < 值;列名 <= 值;
或者:条件1 or 条件2;
并且:条件1 and 条件2;
在两者之间:between 条件1 and 条件2;
在范围内:in (范围);
不在范围内:not in (范围);

1.9.3 模糊查询

 匹配字符串:select * from 表名 where 列名 like 模糊表达式;

select * from studentinfo where name like '郭%';
select * from studentinfo where name like "郭_";
#匹配所有非空字符
select * from studentinfo where name like '%_';	

模糊表达式

%:匹配任意0~多个字符。ex:"张%"->“张”、"张某某",用于目标字符串长度不固定
_:匹配任意1个字符,ex:“_张”->"某张",用于目标字符串长度确定

1.9.4 分页查找

分页查找:limit a,b;(从第a行开始显示,每页显示b行)

a=(p-1)*b     p从1开始,a从0开始

select * from studentinfo limit 2,4;

1.10 聚合函数

create table Student(S varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');
insert into Student values('04' , N'李云' , '1990-08-06' , N'男');
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');
create table Course(C varchar(10),Cname nvarchar(10),T varchar(10));
insert into Course values('01' , N'语文' , '02');
insert into Course values('02' , N'数学' , '01');
insert into Course values('03' , N'英语' , '03');
create table Teacher(T varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , N'张三');
insert into Teacher values('02' , N'李四');
insert into Teacher values('03' , N'王五');
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
insert into SC values('09' , '03' , 98);

1.10.1 查行数

查行数:count()

select (count(*))from student;
select (count(S))from student;

1.10.2 求和

求和:sum()

select sum(score) from sc;

#ex:查询01课程的总分
select sum(score) from sc where c = '01';

1.10.3 最大值

最大值:max()

#ex:查询02同学的最高分
select max(score) from sc where s = '02';

1.10.4 最小值

最小值:min()

#ex:查询02同学的最低分
select min(score) from sc where s = '02';

1.10.5 平均值

平均值:avg()

#ex:查询03课程的平均分
select avg(score) from sc where c = '03';

1.11 分组

根据一个或多个列对结果集进行分组:group by 条件

#ex:查询每门课程的平均分
select C,avg(score) from sc group by C;
#ex:查询每个同学的总成绩
select S,sum(score) from sc group by S;

#ex:查询总成绩超过200的学生编号
select S from sc group by S having sum(score) > 200;

 having 和 where 都是用来加限定条件的

1.过滤条件的列是原来表中就有的,使用where(先执行过滤条件再显示)

2.过滤条件的列原表中没有,需要计算得到,使用having

Mysql中having和where的区别_having关键字和where关键字的区别-CSDN博客

1.12 排序

 排序:order by 列名 排序规则asc|desc;

#ex:按照学生总成绩降序
select S,sum(score) 总分 from sc group by S order by sum(score) desc;

1.13 多表联查

#多表联查
#内联:select * from 表1 inner join 表2 on 连接条件 inner join 表3 on 连接条件;
#不包含空的结果,取两个表的交集

#左联:select * from 表1 left join 表2 on 连接条件 left join 表3 on 连接条件;
#以左侧的表为基准,匹配到行,如果没有对应的属性就是空

#右联:select * from 表1 right join 表2 on 连接条件 right join 表3 on 连接条件;
#以右侧的表为基准,匹配到行,如果没有对应的属性就是空

#笛卡尔积:select * from 表1,表2 where 连接条件;
#取两表交集。速度慢,先排列组合所有的可能,再筛选满足条件的行

#练习:
#1、查询成绩高于60分的学生的信息
#1联表
select * from student inner join sc on sc.S=student.S;
#2加条件
select * from student inner join sc on sc.S=student.S where score>60;
#3筛选显示结果
select distinct student.* from student inner join sc on sc.S=student.S where score>60;

#2、查询每个学生的总成绩及学生信息
select student.*,sum(score) from student inner join sc on student.S = sc.S group by student.S;

#3、查询总成绩大于200的学生信息
select student.* from student inner join sc on student.S =sc.S group by student.S having sum(score)>200;

#4、查询总成绩最低的学生的信息
select student.* from student inner join sc on student.S =sc.S group by student.S order by sum(score) limit 0,1;

#5、查询学过张三老师课程的同学信息
select student.* from student 
inner join sc on student.S =sc.S
inner join course on sc.C=course.C
inner join teacher on course.T=teacher.T where Tname="张三";

#6、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
select student.S,student.Sname,avg(score) 平均成绩 from student 
inner join sc on student.S=sc.S group by student.S having 平均成绩>60;

#7、查询每门课程被选修的学生数-- 带课程名字 。
select course.Cname,count(S) from course inner join sc on course.C=sc.C group by course.C;

#8、查询每门课程被选修的学生数,排序按照选修的学生人数降序排序 , 如果人数相等按照课程号升序排列。
select course.Cname,count(S) 学生数 from course inner join sc on course.C=sc.C group by course.C order by 学生数 desc,course.C asc;

#9、查询同时存在"01"课程和"02"课程的学生的学生信息。
#select student.* from student inner join (select * from sc where C='01')s1 on student.S=s1.S inner join (select * from sc where C='02')s2 on student.S=s2.S;
select student.* from student inner join sc on sc.S=student.S and (C='01' or C='02')
group by sc.S having count(sc.S)=2;

1.14 视图
#视图:是为了简化复杂的sql语句提出的概念,是一个表或者多个表导出的虚拟表,不是真实存在的,不需要遵守范式的要求。
#和临时表一样,只能查询不能修改值

#创建视图:create view 视图名 as (sql语句);
create view myview as(
select student.*,C,score from student inner join sc on sc.S = student.S
);

#使用视图:把视图名作为表名试用
select * from myview;

#删除视图:drop view 视图名;
drop view myview;

#优点:sql语句在网络中传输,使用视图可以减少流量,更安全
#缺点:执行效率不会提高,还是执行原来的语句

1.15 函数

#函数:function

#创建函数:
#delimiter 结束标志(不能出现在函数体中)                #声明新的sql语句结束标志
#create function 函数名(变量1 数据类型,变量2 数据类型,...)
#returns 返回值类型
#begin
#函数体;
#函数体;
#end
#结束标志
#delimiter ;        #重新声明;为结束标志

#调用函数:select 函数名(参数列表);
select myadd(2,7);

#删除函数:drop function 函数名;
drop function if exists myadd;

#ex:加法函数
drop function if exists myadd;
delimiter //
create function myadd(a int,b int)
returns int
begin
    declare c int default 0;
    set c=a+b+10;
    return c;
end //
delimiter ;

1.16 变量

#变量
#局部变量
#declare 变量名 类型;
#set 变量名 = 值;

#会话变量(用户变量):
set @a=10;
select @a;

#系统变量(全局变量):需要root权限
show global variables;
select @@basedir;

1.17 选择结构
#选择
#if
#if 表达式1 then 执行语句1;执行语句2;
#elseif 表达式2 then 执行语句1;执行语句2;
#else 执行语句1;执行语句2;
#end if

#ex:判断输入参数是整数、负数、0
drop function if exists myfun;
delimiter //
create function myfun(n int)
returns varchar(10)
begin 
    declare res varchar(10) default '';
    if (n>0) then set res='正数';
    elseif n=0 then set res='0';
    else set res='负数';
    end if;
    return res;
end //
delimiter ;
select myfun(10);


#case语法:
#1、
#case 变量 when 值1 then 执行语句;
#          when 值2 then 执行语句;
#          when 值2 then 执行语句;
#end case;

#2、
#case when 表达式1 then 执行语句;
#     when 表达式2 then 执行语句;
#     when 表达式2 then 执行语句;
#end case;

drop function if exists myfun;
delimiter //
create function myfun(n int)
returns varchar(10)
begin 
    declare res varchar(10) default '';
    case when (n>0) then set res='正数';
         when n=0 then set res='0';
         when n<0 then set res='负数';
    end case;
    return res;
end //
delimiter ;
select myfun(-10);

1.18 循环结构

#循环
#while 循环语法:
#while 条件
#do
#执行语句;
#end while;

#ex:实现累加函数1+2+3+...+n
drop function if exists mysum;
delimiter //
create function mysum(n int)
returns int
begin
    declare res int default 0;
    declare i int default 0;
    while i<=n
    do
        set res=res+i;
        set i=i+1;
    end while;
    return res;
end //
delimiter ;
select mysum(10);

#其他循环:mysql没有for循环,repeat和loop不重点

#练习:实现青蛙爬井函数,白天向上爬5m,晚上向下爬4m,井深是参数,返回值是需要多少天。使用循环完成。
drop function if exists myqwpj;
delimiter //
create function myqwpj(n int)
returns int
begin
    declare h int default 0;
    declare day int default 0;
    while h<n
    do
        set day=day+1;
        set h=h+5;
        if h>=n then return day;
        else set h=h-4;
        end if;
    end while;
    return day;
end //
delimiter ;
select myqwpj(10);

1.19 存储过程

#存储过程:procedure
#一组为了完成指定功能的sql语句的集合,一次编译永有效

#优点:
#1、减少网络流量,减少与客户机的交互
#2、增强代码的复用性和共享性
#3、提高系统运行速度
#4、使用灵活

#创建存储过程
#delimiter //
#create procedure 存储过程名(参数1 数据类型1,参数2 数据类型2,...)
#begin
#sql语句;
#执行语句;
#end //
#delimiter ;

#注意:存储过程没有返回值。如果需要返回数据,可以把参数定义为in|inout|out类型,不生命默认是in类型
#执行存储过程:call 存储过程名(参数列表);
#删除执行过程:drop procedure 过程名;

#ex:分页的存储过程。输入参数当前是第几页(page int),每页显示多少行(count int),查询student表
#参数校验:page<1显示第一页;page超过最大页数,显示最后一页
drop procedure if exists limitpro;
delimiter //
create procedure limitpro(page int,count int)
begin
    declare offset int default 0;
    declare maxPage int  default 0;
    declare sumCount int default 0;
    #查询总行数
    select count(*) into sumCount from student;
    #计算最大页数
    #注意:sql语句取整规则为四舍五入
    #ceil()向上取整 floor()向下取整
    set maxPage=ceil(sumCount / count);
    #判断page是否在有效范围内
    if page<1 then set page=1;
    elseif page>maxPage then set page=maxPage;
    end if;
    #计算起始位置
    set offset = (page-1)*count;
    #分页查询
    select * from student limit offset,count;        #select里不能有表达式计算
end //
delimiter ;

call limitpro(2,4);
call limitpro(3,3);
call limitpro(0,5);
call limitpro(5,5);

#存储过程和函数的区别:
#1、函数有返回值,存储过程没有返回值。可以把参数生命为out|inout类型来返回数值
#2、函数内不能有sql语句,存储过程可以有sql语句

1.20 触发器


#触发器triger:一种特殊的存储过程,当指定事件发生的时候,系统自动调用
#创建触发器
#delimiter //
#create triger 触发器名字
#after|before 操作名        #在某操作后|前
#on 表名                    #对某表操作时触发
#foe each row                #影响某一行
#begin
#    执行操作;
#end //
#delimite ;

#删除触发器:drop trigger if exists 触发器名;

#ex:删除的触发器,当删除student表的时候,同时删除sc表中这个同学的选课信息
delimiter //
create trigger mydel
after delete
on student
for each row
begin
    #s删除sc表中这个同学的选课信息
    delete from sc where S=old.S;
    #old表:删除的数据,修改之前的数据
    #new表:修改之后的数据,新增加的数据
end //
delimiter ;

delete from student where S='01';


#ex:增加的触发器,新增一个同学,自动选择01课程
delimiter //
create trigger myinsert
after insert
on student
for each row
begin
    #自动选择01课程
    insert into sc(S,C) values(new.S,'01');
end //
delimiter ;

insert into student (S,Sname) values(15,'李峰');

#ex:修改的触发器:当修改student表修改到S列时,同时修改sc表中的S列alter
delimiter //
create trigger myupdate
after update
on student
for each row
begin
    #同时修改sc表中的S列
    update sc set S=new.S where S=old.S;
end //
delimiter ;

update student set S=20 where S=15;

1.21 事务

#事务

create table bank(
name varchar(10) primary key,
money int not null
check(money>=0)        #约束,mysql中check不生效
);

insert into bank values('李永昕',100);
insert into bank values('王晨阳',0);
select * from bank;

#让一系列sql语句要么都执行成功,要么都不执行,需要使用事务。
#事务:transaction 是作为单个逻辑工作单元执行的一系列的sql操作,操作要么都执行,要么都不执行

#特性(ACID)
#A:原子性 事务是最小的工作单元,不可再分,要么都执行、要么都不执行
#C:一次性 数据库的完整性约束不能被破坏
#I:隔离性 并行执行的事务之间是隔离的,多个事务之间相互不影响
#D:持久性 事务提交以后,数据永久保存

#使用
#开启事务:start transaction;
#一系列sql语句(数据改变是临时的,其他用户看不到)
#提交:commit;    数据确认无误,上传
#回滚:rollback;    恢复到数据原始状态——开启事务之前的值

start transaction;
update bank set money = money-50 where name ='李永昕';
update bank set money = money+50 where name ='王晨阳';

select * from bank;

rollback;    #全部不执行

commit;        #全部执行
 

  • 17
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值