文章目录
- 一、MySQL
- 二、约束
- 三、 索引
- 四、 数据的插入:(insert into)
- 五、查询(select)
- 六、函数(可以嵌套使用)
- 七、连接查询,联合查询
- 八、分组聚合查询(group by)
- 九、子查询
- 十、视图(view)的操作
- 十一、触发器(trigger)
- 十二、事件(event)
- 十三、存储过程与存储函数
- 十四、变量
- 十五、流程控制
- 十六、游标(光标--cursor)
一、MySQL
1、简介
1、介绍数据库,数据库有什么用。
2、MySQL软件的安装与卸载
3、MySQL软件的基本操作
2、数据库的概念
(1)数据库系统的发展史:
数据库管理发展至今已经历了三个阶段:人工管理阶段、文件系统阶段、数据库系统阶段。
(2)数据库的概念:
数据库(DB: DataBase)是长期存储在一起的互相有联系的数据集合。
数据库中的数据是集成的、可共享的、最小冗余的、能为多种应用服务的。
(3)数据库管理系统:
数据库管理系统(DBMS: DataBase Management System ),是一种系统软件,负责数据库中的数据组织、数据操作、数据维护、控制以及保护和数据服务等,为提供数据服务,提供相应的数据语言
(4)常用的数据库管理系统:MySQL 、小海豚
(5)数据库系统(DBS:Database System):
数据库系统由如下几部分组成:数据库(数据)、数据库管理系统(软件)、数据库管理员(人员)、系统平台之一(硬件平台)、系统平台之二(软件平台)。这五个部分构成一个以数据库为核心的完整的运行实体,称之为数据库系统。
(6)数据库管理员(DBA: Database Administrator):
由于数据库的共享性,因此对数据库的规划、设计、维护、监视等需要有专人管理,称他们为数据库管理员。
(7)为什么要使用MySQL数据库?
1.开源
2.性能强劲
3.便于安装
----------------------------------------------------------------------------------------------
3、 DOS指令:
开启MySQL的服务,net start mysql
DOS窗口链接MySQL,如果连接不上,则安装时没有选择自动配置环境变量
指令1:DOS链接数据库 mysql -uroot -p密码
-u:user(用户) -p:password(密码)
win+r+cmd
指令2:输入cd desktop进入桌面
指令3:退出mysql:quit
指令4: MYSQL改密:mysqladmin -u用户名 -p原密码 password 新密码
打开注册表:命令行 输入: regedit
卸载 mysql
1打开控制面板
2删除c盘下:program Files里面的mysql文件
删除c盘下:program DATE里面的MySQL文件 前提查看隐藏文件夹
3删除注册表里面的mysql win+r 输入regedit
找到HKEY-LOCAL-MACHINE\SYSTEM\conrolset001\Services\Eventlog\Application\MySql 目录删除
\currentControlSet\Services\Evebtlog\Application\mysql 目录删除
也有002
mysql基本卸载完成
----------------------------------------------------------------------------------------------
数据库的基本操作与知识
4、数据库的基本操作
4.1、查看数据库:
Show dabases;
4.2、删除数据库:
drop database 库名
4.3、选择数据库:
use 库名
4.4、创建数据库
->create database (库名)
->default character set gb2312
>default collate gb2312_chinese_ci;(红色可省略)
4.5、查看数据库的字符集
Show create database 库名;
4.6、查看数据库的字符编码
Show create database 库名 \G;
4.7、系统数据库
(mysql text performance_schema information_schema)----不要随意的更改和删除,后果不堪设想
4.8、数据库对象
包含表、视图、存储、过程、函数、触发器、事件(create database 库名)
9、创建数据库之前要查看数据库有没有该数据库,假如有了则创建不成功。
5、数据库命名规则(标识符)
A、组成部分:字母、数字、下划线和$符号
B、不能是纯数字组成,数字命名尽量不要使用,因为数字不具有可读性。
C、不能是MySQL的保留字(关键字)和创建数据库不能有空格,以及一些特殊符号。
D、名字长度不可以超过128个字节。
E、多个单词组成的要用下划线隔开。
6、中文也可以创建,但是中文数据库不常用。
前提是你选中的数据库要在系统软件中存在。
7、无论在执行什么操作之前都要查看数据库是否存在。
----------------------------------------------------------------------------------------------
6、存储引擎:决定了表的性能
MyISAM:提高了高速存储、检索,还具有全文搜索功能
inoodb:MySQL默认,提供了具有提交、回滚和崩溃恢复能力的事务安全,提供外键。
7、数据类型:数据型,时间与日期,字符型
7.1、数据类型:(整型(int),浮点型(float),定点型)
7.2、时间与日期:
【1】date (yyyy-mm-dd)
【2】time (hh:mm:ss)
【3】year (yyyy)
【4】datetime (yyyy-mm-dd hh:mm:ss)
7.3、字符类型(char和varchar)
– char(定长字符串)例如: 123 ;会输出7个字符空格也会算
(默认一个字节大小)
– varchar(变长字符串)不会记两边的空格,但是会计算中间的空格
(必须指明大小)
--------------------------------------------------------------------------------------------------
表的基本操作与知识
8、表的基本操作
8.1、创建表(必须带字段)
cerate table 表名 (id int,sname char(20),age int,sex char(20));
8.2、查看表
show tables;
8.3、数据类型
1.整型数据类型(int/bigint/smalint)
2.近视数值类型(float/double/real)保留的位数依次增多。(float只保留1—2位)
3.日期数据类型(date)
8.4、字符类数据类型(char/varchar(变长)/binary/varbinary)
8.5、查看数据库表的结构(定义)
Desc/descried 数据表名
8.6、数据库存储引擎
1.engine:能量/引擎
a、MYISAM表 存储引擎管理非事务表,提供高速存储和检索,以及全文搜索能力。
b、innodb MySQL默认,提供了具有提交、回滚和崩溃恢复能力的事务安全,提供外键。
8.7、查看表的数据结构
Show create table 表名 \G;
8.8、查看表的数量
Show tables;
8.9、删除表数据结构
Drop table 表名
8.10、修改表名
alter table 表名1 rename to(可省) 表名2;
8.11、增加字段
【1】alter table 表名 add 字段(列名) 数据类型 first;→→【加字段在表头
【2】alter table 表名 add 字段1(列名) 数据类型 after 字段2→→→→→→
→→→→→→→→→→→→→→→→→→→→→→→【在字段2的后面加一个字段1)
【3】alter table 表名 add 字段(列名) 数据类型→→→→→→【默认加在末尾
8.12、删除字段:
alter table 表名 drop 列名(字段);
8.13、修改字段中的数据类型
alter table 表名 modify 列表(字段)新的数据类型;
8.14、修改字段以及数据类型
alter table 表名 change 原表名 新表名 新的数据类型;
8.15、修改字段的顺序
alter table 表名 modify 字段名 数据类型 first 或 after 列名(字段)
--------------------------------------------------------------------------------------------------------------------
二、约束
1、常用的约束条件:
(1)主键约束
(2)外键约束
(3)唯一性约束[候选键约束]
(4)默认值约束
(5)检查约束
(6)非空约束
2、数据完整性约束
(1)定义完整性
(2)定义参照完整性:
2.1、主键(非空性,唯一性)
1、主键是表中某一列或某一些列所构成的一个组合
2、主键列必须遵循的规则
【1、每一给表只可以定义一个主键
【2、唯一性原则:表中两条不同记录在主键上不能具有相同的值
3、主键的定义语句:
【1、创表时增加主键:(1)在表级创建主键,在创好所有的字段的后面加上 primary key(字段名)。
(2)在列级创建主键,在你要加主键的字段的后面加上 primary key。
【2、增加主键(前提已建好表)
alter table 表名 add primary key(字段1);
【3、删除主键:
alter table 表名 drop primary key;
2.2、复合主键:
1、不能包含不必要的多余列,即当从一个复合主键中删除一列后,
如剩下的列依然满足唯一性原则,则此复合主键是不正确的。
2、一个列名在复合主键的列表中只能出现一次。
3、定义符合主键的语句:
【1、创表时增加主键:(1)在表级创建主键,在创好所有的字段的后面加上 primary key(字段名1,字段名2)。
【2、增加主键(前提已建好表)
alter table 表名 add primary key(字段1,字段2,。。。。。。);
2.3、候选键:
2.3–1、候选键的概念
【1、一张表可以有多个候选键(且候选键可以为空)
【2、候选键的值必须都是唯一的(数据唯一)
【3、定义了单个候选键的时候:表的详细信息为uni
多个候选键的时候:表的详细信息为mul
23–2、候选键的语句:
【1、在创表时增加候选键:
(1)在表级创建候选键,在创好所有的字段的后面加上 unique(字段名)。
(2)在列级创建候选键,在你要加主键的字段的后面加上 unique key。
【2、增加主键(前提已建好表)
alter table 表名 add unique (字段1,字段2,。。。。。。);
或者 alter table 表名 add constraint 约束名 unique (字段1)
【3、删除候选键:
alter table 表名 drop index 约束名unique (字段1)
2.4、非空约束(not null)
2.4–1、设置非空约束
(1)、在创表时在字段后加入not null
(2)、在加入字段时一起加入
alter table 表名 add 新字段 新数据类型 not null;
(3)、创完表时加入not null
alter table 表名 modify 字段 数据类型 not null;
2.4–2、删除非空约束
alter table 表名 modify 字段 数据类型 null;
2.5、默认值约束(default)
【1、设置默认约束
(1)、在创表时加入
在要加默认值的后面加入default
(2)、创完表时加入default
alter table 表名 alter 字段 数据类型 default'wokao';
(3)、删除默认值
alter table 表名 alter drop 字段 数据类型 default;
-------------------------------------------------------------------------------------------
2.6、外键约束(可以为空)
一、外键的概念:外键是指引用另外一个表中的一列或多列数据,被引用的列应该具有主键约束或者唯一性约束,
外键用来建立和加强两个表数据之间的连接。
二、参照表和被参照表的概念:(参照表的外键就是被参照表的主键)
【1、参照表:参照对象---------(a中的字段→b中获取)----a为参照表,b为被参照表
【2、被参照表:被参照对象 从表 主 表
【3、主表和从表:从表的外键关联必须是主表的主键
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
三、定义参照完整性约束定义外键与主键之间的引用规则,即外键的取值或为空,或者等于被参照关系中的某个主键的值,需要遵守以下规则:
【1、被参照表已经使用create table语句创建,或者必须是当前正在创建的表,
后者称为自参照表,即参照表与被参考表时同一个表(只能用列级方法);
【2、当被参照表的主键为参照表的外键时,被参照表的主键可以为空
【3、主表的主键和从表的外键的数据类型必须一样的;例如:两者必须是int或char类型
如果不满足是会在创建从表是报1055的错误
【4、必须为被参照表定义主键或候选键;
【5、必须在被参照表的表名后面指定列或列名的组合,这个列或列组合必须是被参照表的主键和候选键;
【6、外键对应列的数目必须和被参照表的主键对应列的数目相同;
【7、外键只可以用在使用存储引擎innodb创建的表中,其他引擎不支持外键。
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
三、外键的作用
【1、用于表与表之间的联系
【2、用来保持数据库的参照完整性
【3、保持数据一致性,完整性,主要的目的是控制储存在外键表中的数据,
使两张表形成关联,外键只能引用外表中的列的值
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
四、参照完整性定义法
【1、增加外键—在创表是添加
【2、用alter 加入外键----------alter table 从表名 add constraint 约束名 foreign key(从表的外键字段) references 主表(主表的主键)
【3、删除外键--------alter table 从表名 drop foreign key 约束名
【4、定义外键你自己不命名是系统会自己给你一个约束名
三、 索引
一、索引的分类:普通索引,唯一索引,主键索引,全文索引
3.1、普通索引(index/key):
普通索引没有唯一性,可以取空值和重复值,可以创建在任何数据的字段上
1、创建普通索引:create table 表名( 字段名 数据类型。。。。。 index/key(字段名 asc/desc))【创表的时候用】
a、index或key—参数用来指定字段为索引的关键字
b、“索引名”参数用来指定所创建索引的名称
c、asc 升序
d、desc 降序
e、 索引名,asc desc都是可选项
【1、使用create在已经创建好的表中添加索引
create index 索引名 on 表名(字段名);
【2、使用alter table 创建索引
alter table 表名 add index/key 索引名(字段名 desc/asc)
【假如你没有给索引名系统会自动指定字段名为索引名】
【3、查看索引名
show index from 表名
3.2、唯一索引:
1、创建唯一索引:create table 表名( 字段名 数据类型 。。。。。 unique index/key(字段名 asc/desc))
a、index或key—参数用来指定字段为索引的关键字
b、“索引名”参数用来指定所创建索引的名称
c、asc 升序
d、desc 降序
e、 索引名,asc desc都是可选项
【1、使用create在已经创建好的表中添加索引
create unique index 索引名 on 表名(字段名);
【2、使用alter table 创建索引
alter table 表名 add unique index/key 索引名(字段名 desc/asc)
【假如你没有给索引名系统会自动指定字段名为索引名】
【3、查看索引名
show index from 表名
【3】、主键索引 :主键索引的创建和修改、删除,直接参考上一次课程中的约束中主键约束,主键的索引均是在创建主键、添加主键时,自动创建的索引。
3.3、多列索引:
【1、概念:在创建表的时候用表级完整性约束法定义多列索引
【2、create table 表名(字段名 数据类型,字段名 数据类型,..........字段名 数据类型,
index | key 【索引名】 (字段名 【asc|desc】,...,字段 名n【asc|desc】);
【3、在已经存在的表上使用create创建多列索引,语法格式如下:
reate index 索引名 on 表名(属性名1【asc│desc】,...,属性名nasc│desc】);
【4、通过alter table创建多列索引,其语法格式如下:
alter table table_nameadd index│key [索引名](属性名1【asc│desc】,...,属性名n【(长度)】【asc│desc】);
3.4、全文索引:
【1】、全文索引主要是关联在数据类型为char、varchar、和text的字段上,以便能够更加快速地查询数据量较大的字符串类型的字段。
【2】、目前只有存储引擎为MyISAM的数据表才能创建全文索引。
【3】、 创建全文索引:create table 表名(数据类型 字段名。。。。。fulltext index/key(字段名 asc/desc))
a、index或key—参数用来指定字段为索引的关键字
b、“索引名”参数用来指定所创建索引的名称
c、asc 升序
d、desc 降序
e、 索引名,asc desc都是可选项
【4】、使用create在已经创建好的表中添加全文索引
create fulltext index 索引名 on 表名(字段名);
【5】、使用alter table 创建全文索引
alter table 表名 add fulltext index/key 索引名(字段名 desc/asc)
【假如你没有给索引名系统会自动指定字段名为索引名】
【6】、查看索引名
show index from 表名
【7】删除索引:
drop index 索引名 on 表名;
或
drop table 表名 drop index 索引;
【8】很多情况下不推荐使用索引,索引会增加系统的运行机制和内存,在很多情况下
要提高检索数据的速度,一般采用优化查询语句。
数据的基本操作
关键词(insert into(插入数据),select (查询数据),update(更新数据),delele(删除数据))
四、 数据的插入:(insert into)
mysql->insert into 表名(字段名1,字段名2,。。。) values(数据1,数据2,。。。。。数据n)
4.2、插入部分数据
MySQL->insert into表名(字段1,字段2,字段3)values(数据1,数据2,数据3)
【注意】:若不给字段名的话,系统自动默认全部字段都要插入数据
4.3、复制表中的数据:
【1、insert into 表名1 (字段1,字段2。。。。字段n) select 字段a,字段b。。。字段n from 表名;
【2、insert into 表名1 select*from 表名;
4.4、更新数据
update 表名 set 字段名='新数据' where 字段名=已有的数据值(独一无二的数据值);
4.5、删除数据
delete from 表名 where 字段名=已有的数据值(独一无二的数据值);
4.6、删除所有数据
delete from 表名;
五、查询(select)
5.1、数据的查询
select*from 表名;
5.2、避免重复数据查询(distinct)
select distinct 字段from 表名;
5.3、数学四则运算数据查询(* / + -)
select 字段名*(+,-,/)数值 from 表名;
5.4、设置显示格式数据查询(concat)
Concat():MySQL连接函数
select concat(参数1,。。。。,参数n) as 新字段名 from 表名;
5.5、单条件查询
select 字段名 from 表名 where 条件字段>数值
5.6、多条件查询
(and)&&:与 ||:或 !:非
select 字段名1,字段名2 from 表名 where 条件1>800 && 条件2<5000;
5.7、范围关键词查询(between and)
(a到b之间的查询)
select 字段名1,字段名2 from 表名 where 条件 between 数值 and 数值;
(a到b之外的查询)
select 字段名1,字段名2 from 表名 where 条件 not between 数值 and 数值;
5.8、条件查询
1、关键字用where
select 字段名 from tb_name where conditions;
2、多条件查询语句中:
3、与(&& 或 and)
4、或(|| 或 or)
5、非(! 或 not)
6、between A and B:A到B 之间的范围查询
7、not between A and B:不在 A到B 之间的范围查询
5.9、is null:空值查询
select * from tb_student where nation is null;
5.10、is not null:非空值查询
select * from tb_student where nation is not null;
5.11、in(A,B,C…)
1、in表示的集合查询,不是区间查询
5.12、模糊查询
1、带like关键字的模糊查询:
2、%通配符: “A%” :%剩下的所有,以A开头的
select 字段名1,字段名2… from 表名 where 字段名1 like 条件;
3、"%A%":查询含有A的所有数据
4、"__" 通配符:_代表一个字符
5、“_江西%”:查询的是第二个、第三个字符分别是江西
5.13、查询结果排序
1、ASC:升序(从低到高)
2、DESC:降序(从高到底)
select 字段名 from tb_name order by 字段名 asc/desc
5.14、查询语句
1、slect 目标字段名 from tb_name where 过滤条件
5.15、limit分页查询
offset X 是跳过X个数据
limit Y 是选取Y个数据
limit X,Y 中X表示跳过X个数据,读取Y个数据
#limit的角标是从 0 开始的
#查询一个表中的前三条数据
select * from tb_student limit 0,3; #(limit 行数,数据条数)
#limit的查询算法
int now_page = 1 #当前页
int pagesize = 3 #一页有多少数据
select * from tb_Student limit (now_page-1)*pagesize,pagesize;
六、函数(可以嵌套使用)
6.1、format (A,B) 函数 :
1、设置结果显示的格式,A是表达式或者字段名,B是保留的小数点位数,参与了四舍五入运算。
2、select format (3.1415926,3); -------3.142
6.2、truecate(A,B)函数:
1、设置结果显示的格式,A是表达式或者字段名,B是保留的小数点位数。不会四舍五入。
2、select truecate (3.1415926,3); -------3.141
6.3、函数的分类:
1、聚合函数,数学函数,字符串函数,时间和日期函数
6.3–1、聚合函数
1、count (字段)函数:
用于查询指定记录的记录数,函数会自动忽略字段为null的值。
2、sum ()函数:
可以求出表中指定数据的和(且只能为int类型)。
3、avg ()函数:
可以求出平均值(且只能为int类型)。
4、max ()函数:
可以求最大值(且只能为int类型)。
5、min ()函数:
可以求最小值(且只能为int类型)。
6.3–2、时间和日期函数
select curdate();
1、curdate ()
==current_date()----获取当前的年月日;
2、curtime()
== current_time()—获取时分秒;
3、now ()
----获取 年月日 时分秒;
6.3–3、 数学函数
1、ABS():
可以求出表中某中字段取值的绝对值。
2、floor():
向下取整-----floor(3.9999999)===3;
3、ceil():
向上取整------cell(3.1111111)==4;
4、rand():
取随机数(用于返回0-1的随机数);
5、ceil(rand()*1000):
取1-1000的随机数;
6、sqrt():
用于运算平方根
6.3–4、字符串函数
1、concat(字符串连接函数)
select concat('aaa','bbb');---->aaabbbselect concat('aaaa',null);---->null
2、insert(字符串替换函数)
select insert('mysql',2,2,'lk');---》mlkql#从第 2 个字符开始替换 2 个字符
3、lower(转小写)
select lower('QWER');-----》qwer
4、upper(转大写)
select upper('qwer');----》QWER
5、left():返回字符串s的前n个字符
select left('hello',3);---》hel
6、right():返回字符串s的后n个字符
select right('hello',3);---》llo
7、substring(str,n,len):截取字符串
将字符串str在第n个位置,截取len个长度的字符串
select substring('hello',2,3);----》ell
8、lpad(‘str’,len,‘str1’):加在字符串左边
select lPAD('hello',9,'world');--->worlhello#len--->最大长度==9
9、rpad(‘str’,len,‘str1’):加在字符串右边
select lPAD('hello',9,'world');----》helloworl#len--->最大长度==9
10、repeat(循环打印)
select repeat('mysql',5);---->mysqlmysqlmysqlmysqlmysql
11、replace(替换指定的字符)
select REPLACE('mysql','m','z');----》zysql
12、if (exp,v1,v2):
如果表达式exp成立,则执行v1,否则执行v2
13、IFNULL(V1,V2)函数:
也是一种条件判断函数,其表示的是如果表达式V1不为空,则显示v1 的值,否则显示v2的值。
七、连接查询,联合查询
7.1、交叉查询
1、查询的是多个表的数据相乘的数据量,并且产生拼接,生成一个笛卡尔积(比如一个表中有20条,另 一条表有10条,所以一共有200条数据)
2、select */目标列表 from tb_student,tb_course;
7.2、内连接
1、目的:为了消除交叉连接中某些没有意义的数据行。也就是说在内连接查询中只有满足条件的记录才能出现在 结果集中。
2、select */目标列表 from tb_student,tb_score where
tb_student.studentNo=tb_score.studentNo;(查询字段相同的数据)。
7.3、左右外连接
1、左外连接:left outer join
select 目标列表 from tb_student left outer join tb_class on
tb_student.classno=tb_class.classno;
2、右外连接:right outer join
select 目标列表 from tb_class right outer join tb_student on
tb_student.classno=tb_class.classno;
八、分组聚合查询(group by)
1、常和聚合函数在一起
1、select 字段1
from tb_name group by 字段2
having 过滤条件;
【1、判断学生选课数是否大于等于2
select studentno,count(courseno)
from
tb_student grop by studentno
having count(courseno)>=2;
【注意】
1、字段列表1用了那几列的字段名,那么字段列表也要给出相同的字段名
2、字段2 都必须是检索列或者有效的表达式,但不可以有聚合函数 。
3、分组列中存在null值,则null将作为一个单独的分组返回,如果该列中存在多个null值,则将这些null 值所在的行分为一组
九、子查询
(in)9.1、带关键字的查询(集合查询)
1、in后面跟随的是子查询的结果集
【1、例如:查询选修了课程名为计算机基础的学生信息
select * from tb_student where studentNo in(select studentNo from tb_score where courseNo
in(select courseNo from tb_course where courseName='计算机基础'));
(运算符)9.2、带运算符的子查询
1、内带比较 运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层返回 的是单值时,可以用<,<=,>,>=,=,<>,!=等比较运算符构造子查询。
9.3、带any关键字的子查询:
(涉及到比区间 某个 大或小时用)
例子:查询男生中比某个女生出生早的学生姓名和出生年份。
select studentName,year(birthday)
->from tb_student
->where sex=’男’and year(birthday) <any (select year(birthday) from tb_student where sex =’女’);
1、 =ANY:其功能与关键字in一样
2、>ANY(>=ANY):比子查询中返回数据记录中最小的还要大于(大于等于)的数据记录;
3、<ANY(<=ANY):比子查询中返回数据记录中最大的还要小于(小于等于)的数据记录;
9.4、带all关键字的子查询:
(涉及到比区间 所有 大或小时用)
例子:查询男生中比所有女生出生晚的学生姓名和出生年份。
>select studentName,year(birthday)
->from tb_student
->where sex=’男’and year(birthday) >all(select year(birthday) from tb_student where sex =’女’);
1、>all(>=all):比子查询中返回数据记录中最大的还要大于(大于等于)的数据记录;
2、<all(<=all):比子查询中返回数据集中最小的还要小于(小于等于)的数据记录;
9.5、带exists关键字的子查询:
1、使用关键字exists构造子查询时,系统对子查询进行运算以判断它是否返回结果集。如果子查询的结果集不为 空,则exists返回的结果为true,此时外层查询语句将进行查询,如果子查询的结果集为空,
exists
select studentno,studentname from tb_student where exists(select * from tb_class where
b_student.classno=tb_class.classno and classname='计算机14-1班');
not exists (与exists相反)
select studentno,studentname from tb_student where not exists(select * from tb_class where
tb_student.classno=tb_class.classno and classname='计算机14-1班');
9.6、限制查询数量
---->limit关键字查询(查询个数)[起始位置从0开始](放在结尾)
1、select * from tb_score order by studentno asc limit 1(起始位置) ,5(个数);
//查询学号排名前5的数据
十、视图(view)的操作
10.1、视图的基本操作
1、什么是视图:站在不同角度去观察数据
2、复制整张表:
create tb_table1(新表名) as select * from tb_table(旧表名) ;
3、创建视图:
create view 视图名 as select 目标字段 from tb_student
4、删除视图:
drop view 视图名
5、修改视图数据:
update 视图名 set 字段名2=新数据 where 字段名1=数据;
6、创建视图
【1】、create or raplace view 视图名 as 查询语句 ;
【2】、alter view 视图名 as 查询语句、
7、查看视图的基本信息
desc view 视图名;
8、查看视图的详细信息
show create view 视图名 \g;
10.2、视图的作用
1、保护原表的数据
2、当原数据表的字段太多或数据量太大,减少数据量的请求
3、在视图里增删改查会影响到基本表
10.3、视图
1、当视图来着与一张表的时候------视图可以(增删改查);
2、当视图来着与多表的时候-----视图不能进行(增加,删除)。、
十一、触发器(trigger)
11.1、触发器的基本概念
1、当预定义事件(如用户修改指定表或视图中的数据时)发生时,触发器会自动执行
2、概念:触发器可以用来对表实施复杂的完整性约束,触发器的运行往往伴随着事件的执行
3、触发器是数据库对象之一,都需要声明与执行(执行要伴随着事件触发来激活)
11.2、触发器的优点
1、触发器运行的前提是:表的数据被做了修改(增删改等等~~)3打错
11.3、创建触发器
1、create trigger 触发器名 before/after trigger_event on 表名 for each row trigger_body
执行的时间 触发器的事件 每一行都执行 触发器语句
2、drop trigger 名字;
11.4触发器的类型
1、insert 触发器
2、update 触发器
3、delete 触发器
4、多条语句的触发器:
delimiter关键字:用于修改系统结束的标志
begin
执行触发器激活后的语句
end
十二、事件(event)
12.1、事件的概念
even:事件
schedule:调度器
even schedule:事件调度器(属于系统,不属于数据库的)
事件:事件用完就会销毁
12.2、查看事件调度器的状态
windows下修改my.ini文件中加上 event_scheduler=1或 event_scheduler=on来开启。
linux 下修改my.cnf文件中加上 event_scheduler=1或 event_scheduler=on来开启。
方法一:show variables like 'event_scheduler';
方法二:select @@event_scheduler;
方法三:select processlist
12.3、打开与关闭事件调度器
方式一:set global event_scheduler=1;
方式二:set global event_scheduler=true;
12.4、三种事件:
at:
在某个时间后执行命令
every(配合starts使用):
在某个时间范围内,间隔多久循环执行
every 数值 单位
starts:
例如:starts timestamp+[interval 数值 单位]—开始时间
ends timestamp+[interval 数值 单位]—结束时间
12.5、创建事件
例如:1分钟后创建一个test表
create event e_create on schedule at now()+interval 1 minute
事件 事件名 调度器 时间点
do create table test (name char(20),btrthday date);
执行的业务逻辑代码
例如:每5秒像表test中插入一条数据并且在两分钟后结束
create event e_insert on schedule every 5 second starts now() ends now()+interval 2
minute do insert into test values('zzj','2002-08-06');
12.6、修改事件
12.6–1、开启事件:
alter event 事件名 enable on slave;
12.6–2、暂停事件:
alter event 事件名 disable on slave;
12.6–3、修改事件名:
alter event 原事件 rename 新事件名
十三、存储过程与存储函数
13.1、概念:
【1】、一个存储过程是一个功能模块,一个函数是多个功能模块的集合
13.2、存储过程与存储函数的功能:
【1】、灵活性:能够提高数据库的处理速度,同时也可以提高数据库编程的灵活性
13.3、存储过程的两种模式
【1】、声明式语句(create,update,seleect),过程式语句
13.4、存储过程的优缺点:
13.5、例子:
编写一个程序在数据库db_school中创建一个存储过程,用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为一个指定的性别(使用该存储过程修改学生表中的学号、性别)。
create procedure sp_update(in student_no char(10),student_sex char(2)) begin update
变量名字 形参 1 形参2
tb_student1 set sex=student_sex where studentNo=student_no; end$$
13.6、存储函数:
13.6–1、概念:
在mysql中,存在一种与存储过程十分相似的过程式数据库对象——存储函数。
13.6–2、注意
存储函数必须包含return 语句,此语句不允许出现存储过程中
13.7、使用存储过程变量:
call 变量名(形参 数据类型) ;
13.8、查看储存过程
【1、查看所有库的存储过程:
show procedure status \G;
【2、查看当前数据库的指定存储过程
show create procedure 名字 \G;
13.9、存储函数与存储过程的区别:
【1、存储函数不能拥有输出参数。存储过程拥有输出参数。
【2、可以直接对存储函数调用,不需要使用call语句,存储过程调用需要。
【3、存储函数中必须包含return语句,此语句不允许出现在存储过程中。
十四、变量
14.1、局部变量
14.1–1、作用范围:
在begin。。。and之间,声明也是一定在begin…and之间
14.1–2、declare:
声明局部变量: declare 变量名
14.1–3、select:
select 变量名;
14.2、 全局变量
系统当中包含的变量,用@@来表示,不能随便定义
14.2–1例如:事件调度器
select @@event_scheduler;
14.3、用户变量
用户根据自己需求而创建的变量,作用范围是整个会话当中定义用户变量,用一个@符合表示-----set 变量名=10;
14.3–1、set语句(给变量赋值)
set @var_name=value
14.3–2、declare(定义数据类型)
declare 变量名 数据类型
14.3–3、select…into:
14.3–3--1、概念:
将选定列的值直接储存局部变量,可以运用在储存过程。
14.3–3--2、语法:
select 字段 into 局部变量 from 表名 where 条件;
十五、流程控制
1、条件判断语句
1–1、条件表达式语法1
if 条件表达式 then 执行语句1;
else 执行语句2;
end if;
1–2、条件表达式语法2
if 条件表达式1 then 执行语句1;
else if 条件表达式2 then 执行语句2;
...
else 执行语句n;
end if;
1–3、例题:比较指定的两个数的大小,并输出最大值
#方法一
DELIMITER $$
CREATE PROCEDURE max1(a INT,b INT)
BEGIN
IF a>=b THEN SELECT a;
ELSE SELECT b;
END IF;
END$$
DELIMITER ;
CALL max1(10,20);
#方法二:
DELIMITER $$
CREATE PROCEDURE max2(a INT,b INT)
BEGIN
DECLARE c INT;
IF a>=b THEN SET c=a;
ELSE SET c=b;
END IF;
SELECT c;
END$$
DELIMITER ;
CALL max2(10,20);
1–4、例题3:通过学号,判断民族,如果是汉族,不变,如果不是汉族,就改为少数民族
DELIMITER $$
CREATE PROCEDURE change_nation(sno INT)
BEGIN
DECLARE c VARCHAR(20);
SELECT nation INTO c FROM tb_student1 WHERE studentNo=sno;
IF c!='汉族' THEN UPDATE tb_student1 SET nation='少数民族' WHERE studentNo=sno;
END IF;
END$$
DELIMITER ;
CALL change_nation(2023212121);
2、case条件判断表达式
2–1、case条件表达式语法
#a、写法一
case 表达式
when 值1 then 执行语句1;
when 值2 then 执行语句2;
...
else 执行语句n;
end case;
2–2、case条件表达式语法
#b、写法二
case
when 条件表达式1 then 执行语句1;
when 条件表达式2 then 执行语句2;
...
else 执行语句n
end case;
2–3、例题:输入姓名与成绩判断等级,并向student_level 表中插入数据–姓名,等级
#创建
create table score_level (name varchar(30),lev varchar(30));
#改结束符
delimter $$
#创建储存过程
CREATE PROCEDURE if_level(name varchar(30),lev int)
BEGIN
case
when lev>=0 && lev<60 then insert into score_level values(name,'不及格');
when lev>=60 && lev<80 then insert into score_level values(name,'良');
when lev>=80 && lev<=100 then insert into score_level values(name,'优秀');
else insert into score_level values(name,'你输入的学生成绩有误');
end case;
END$$
#验证存储过程
call if_level('张三',80)
结果:
select * from score_level;
+------+------+
| name | lev |
+------+------+
| 张三 | 优秀 |
+------+------+
1 row in set (0.06 sec)
3、循环语句:
3–1、while循环:
while
SQL语句---
end while;
3–1-1、计算1–100的和且不包含5的倍数:
#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
while i<=100 do
if i%5 !=0 then set sum1=sum1+i;
end if;
set i=i+1;
end while;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$
3–2、repeat循环
repeat
sql语句---
until 循环条件
3–2-1、例题:求100以内的奇数和
#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
repeat
if i%2 !=0 then set sum1=sum1+i;
end if;
set i=i+1;
until i>100
end repeat;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$
3–3、loop循环
sum_loop:loop
sql语句----
if 循环条件 leave loop;
end loop;
#sum_loop---是loop循环的标签
3–3-1、例题:求100以内的偶数和
#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
sum_loop:loop
if i%2 =0 then set sum1=sum1+i;
end if;
set i=i+1;
if i>100 then leave sum_loop;
end if;
end loop;
select sum1;
END $$
#验证存储过程
call for_if_sum()$$
3–3-1、loop的迭代:
例题:求100以内的奇数和:
#换结束符
delimiter $$
#创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `for_if_sum`()
BEGIN
DECLARE i int DEFAULT 0;
DECLARE sum1 int DEFAULT 0;
sum_loop:loop
if i%2 =0 then
set i=i+1; #因为初始值为0当if判断是一直迭代所以要先i=i+1
iterate sum_loop;
end if;
set sum1=sum1+i;
set i=i+1;
if i>99 then leave sum_loop;
end if;
end loop;
select sum1;
END$$
#验证存储过程
call for_if_sum()$$
十六、游标(光标–cursor)
1、概念:
【1、要查看一张表的内容使用select语句,select语句就会一次性的到一条或者多条的记录
【2、场所:存储过程和触发器(主要为存储过程)
【3、游标相当于-----容器(内存块)
2、使用方法:
2–1、使用游标
【1、要使用游标要先—open cur(游标名)----打开游标
2–2、关闭游标
【1、关闭游标----close cur(游标名)----关闭游标
2–3、查看游标里面的内存
fetch cur into 变量名
2–4、遍历游标
declare continue handler for not found set b=false;
【1、当循环的过程中,如果游标里面的数据被遍历完了,或者是select语句查询结果为空的时候将设置为false
2、例子:
#寻找 tb_student1 中学号与2014310102相同的人有多少
#当我想要对5行数据中的每一行数据进行操作----用到循环和游标
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`( )
BEGIN
declare a int default null; #定义一个变量来存放人数的总数
declare b int default 1; #作为判断语句
declare cur cursor for select count(studentno) from tb_student1 where studentno=2014310102; #建立一个游标
declare continue handler for not found set b=0; #系统用来结束游标的遍历
open cur; #打开游标
fetch cur into a; #将游标里的数据存放在
repeat #repeat循环
select a as 总数; #查询变量a(因为前面已经赋值了)
fetch cur into a; #将游标里的数据存放在
until b=0 #循环判定条件
end repeat; #结束循环
close cur; #关闭游标
END $$
#验证存储过程
call NewProc()$$