Loading...
墨滴

念言

2021/06/22  阅读:108  主题:默认主题

MySQL笔记

一、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 collare 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 indexkey [索引名](属性名1ascdesc】,...,属性名n【(长度)】【ascdesc】);

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 过滤条件

六、函数(可以嵌套使用)

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类型)。

​ 4、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、upper():把小写转换为大写

​ 2、lower():把大写转换为小写

​ 3、left():返回字符串s的前n个字符

​ 4、right():返回字符串s的后n个字符

​ 5、substring(str,n,len):将字符串str在第n个位置,截取len个长度的字符串

​ 6、if (exp,v1,v2):如果表达式exp成立,则执行v1,否则执行v2

​ 7、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()$$

念言

2021/06/22  阅读:108  主题:默认主题

作者介绍

念言