初学数据库

1.了解相关概念
      数据：描述客观事务的符号  data
      数据库：是一个存放数据的容器  database ----  db
      数据库对象：表，视图，存储过程......
      数据库管理系统：mysql，SqlServer，Oracle........dbms
      数据库系统：dbs
2.数据库管理系统模型
      层次模型：最早使用
                       树状结构
                       有且只有一个根节点
                       其他节点有且只有一个父节点
      网状模型:  是层次模型的扩展，结构复杂，实现的算法难以规范法
                       允许节点有多于一个父节点
                      可以有一个以上的节点没有父节点
      关系模型：使用最多
                       实体与实体之间的联系--->二维表  
                       描述的一致性
                       可以直接表示多对多的关系
                       关系必须是规范化的关系
                       建立在数学概念
      面向对象模型
3.结构化查询语言：
       DML:数据操纵语言，用于对数据的增删改   insert into,delete,update
       DDL:数据定义语言，用于创建，修改数据库及其中的对象   creat,drop,alter
       DCL:数据控制语言，用于对数据库访问权限的授予和撤销，完整性规则描述，事务控制    commit,rollback,grant,revoke
       DQL:数据查询语言，用于对数据进行检索查询  select
4.编码规范：
       命名规范：驼峰式命名--->studNo
                         _ ------>stud_no   ---->mysql中使用此命名方式
注释：  #：单行注释
/**/:多行注释
书写规范：缩进符Tab
其他：       关键字是不区分大小写，creat,drop,delete
非关键字：在windows系统下，大小写不敏感
                在Linux系统下,对大小写有严格要求
5.E-R图：enetity-relation
         实体：矩形框
         属性：椭圆形
         联系：联系名用菱形，通过连线进行连接
6.约束类型：
    主键：Primary key
    外键：Foreign key
    默认值:Default
    检查：Check
    唯一：Unique
    非空：Not NULL
7.评价表的设计质量：三大范式
    1Nf：每一列是不可再分的
    2Nf：建立在1NF基础上，每一列都与主键相关。
    3Nf：建立在2NF基础上，每一列都与主键直接相关，并非间接相关。

1.关系型数据库特性：
         指采用了关系模型来组织数据的数据库
         最大特点是事物的一致性
         关系模型就是二维表格模型，而一个关系数据库就是由二维表及其之间的联系所组成的一个数据组织
2.非关系型数据库特性：
            使用健值对存储数据。
    分布式
    一段不支持ACID特性
    非关系数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。
2020 3 17
1.启动和停止MySQL服务
方式一：通过命令
在搜索框输入cmd    选择以管理员身份运行
在cmd中输入net stop mysql   停止服务
在cmd中输入net start mysql   启动服务
方式二：右击我的电脑->管理-->服务-->mysql-->停止或启动
2.mysql的配置文件：my.ini,修改之后一定要重启服务
3Mysql的可视化工具：mysql-font，navicat ，小海豚
4字符的相关概念
    字符：是人类最小的表一字符，eg:ab~A
    字符集：字符和编码的集合
字符序：同一个字符集内字符之间的比较准则。字符序的命名规则：一字符集开头，中间是国家或者general，后面以ci(对大小写不敏感)/cs(对大小写敏感)/bin(二进制编码)结尾。
查看MySQL服务实例所支持的字符集：show character set;
查看MySQL服务实例所使用的字符集：show variables like 'character%';
查看MySQL服务实例所支持的字符序：show collation;
查看MySQL服务实例所使用的字符集：show variables like 'collation%';
5.设置mysql服务实例的字符集：
    1.永久性的修改
    修改配置文件：my.ini中default-character-set=字符集，修改之后重启服务
    2.临时性的修改
    方式一：一个个修改
    set  character_set_results=字符集；
    方式二：同时修改三个
    set names 字符集；    
    方式三：mysql--default-character-set=字符集-uroot-proot

6数据库的相关操作
  创建数据库的语法：create database 数据库名；会自动创建....\data\数据库名\db.opt查看数
  查看数据库的详细信息：show create database 数据库名；
  查看MySQL中所有的数据库：show databases;
  切换数据库：use 数据库名；
  删除数据库：drop database 数据库名；
7.存储引擎
    存储引擎是基于表的
  查看MySQL所支持的存储引擎：show  engines;
 innodb 和myisam的特点：
       innodb：支持外键，支持事务，用于全文搜索，若对表中的数据进行大量的增删改操作时，选用此存储引擎。
       myisam：不支持外键，不支持事务，不用于全文搜索，用于对表中的数据进行大量查询。
 设置默认的存储引擎：set default_storage_engine=存储引擎名；一般是innodb;

创建表的基本语法：
   create table 表名（
    字段名 字段类型
    .........
） 若出现对应的数据库文件夹下出现.frm文件，说明该表的存储引擎是innodb。
    修改表的存储引擎：alert table 表名 engine=存储引擎名；
    若出现对应的数据库文件夹下出现.myd和myi的文件，说明该表的存储引擎是myisam。
    查看表的结构：desc 表名；
查看表的详细信息：show create table  表名；
删除表：drop table 表名；
添加数据：insert into 表名 values（值1，值2）
查询表中所有的数据：select *from 表名；
8.innodb表空间
1.共享表空间：show variables like 'innodb_data_file_path';默认值
2.独享表空间：开启innodb_file_per_table,默认是关闭off
    set global innodb_file_per_table=on;
     查看独享表空间的状态：show variabes like  'innodb_data_file_path';
9.mysql中的变量
     自定义变量：@开头
      系统变量：@@开头
    全局系统变量：global
        查看全局系统变量：show global variables;
        查看某个全局系统该变量 ：show global variables like '变量名'                    修改全局系统变量的值：set global 变量名=值；set @@global.变量名=值;

    会话系统变量：session
        查看会话系统变量：show session variables;
            查看某个全局系统该变量 ：show [session ]variables like '变量名'
        修改会话系统变量的值：set [session] 变量名=值；set @@[session].变量名=值;
10.mysql数据库的备份和恢复：

   备份：mysqldump
    1.备份某个数据库的结构，不包含数据，到d:\a.sql
    mysqldump -uroot -proot -d 数据库名>d:\a.sql
       2.备份某个数据库的结构，包含数据，到d:\b.sql
    mysqldump -uroot -proot 数据库名>d:\b.sql
          3.备份某个数据库某张表的结构，不包含表中的数据，到d:\b.sql
    mysqldump -uroot -proot -d 数据库名 表名1 表名2>d:\b.sql
            4备份某个数据库某张表的结构，包含表中的数据，到d:\b.sql
    mysqldump-uroot-proot 数据库名 表名1 表名2>d:\b.sqlmy
    恢复数据：mysql -uroot -proot 数据库名<d:\a.sql 恢复单个数据库，必须先创建一个数据库
2020年3月23日笔记
1.mysql的数据类型：
       数值类型：
              整数类型：（从上往下，取值范围依次增大）
                                                       有符号                                    无符号
                     tinyint： 1字节        （ -128，127）                         （0，255）
                     smallint：2字节       （-32768，32767）                 （0，65535）
                     mediumint：3字节
                     int：4字节
                     bigint：8字节
              小数类型：
                     精确小数类型：decimal（总长度，小数点后面的位数）
                                             decimal（6，3）：这个数总共是6位，小数点后面的小数占了3位，888.999
                     浮点数类型：float(4字节，单精度)、double（8字节，双精度）
       字符串类型：
              定长字符串类型：char
              不定长字符串类型：
                          varchar:比较长的字符串
                          text：用于文本形式，tinytext，text，mediumtext，longtext
       日期类型：
              date:日期   YYYY-MM-DD
              time:时间   HH:II:SS
              year:年份
              datetime:YYYY-MM-DD HH:II:SS
              timestamp:YYYY-MM-DD HH:II:SS
                                 若给此数据类型的字段插入null,实际上显示的是系统的当前时间
                                 此数据类型与时区相关
       复合类型:
             enum类型：类似于单选框，只能选一个，最多能存储65535个元素
             set集合类型：类似于复选框，可以选多个，最多只能存储64个元素
       二进制类型：可以存储短的二进制数，也可以存储图片，视频，音频等
             binary
             varbinary
             bit
             blob：tinyblob，blob，mediumblob，longblob
补充：#查询当前的时区：show variables like 'time_zone';
          #修改时区：set time_zone = '+12:00';
          sql_mode的值：
                   ansi:非严格模式，使语法和行为更符合标准，若添加的数值有误，则会警告，不会报错，仍然可以添加进去，但是会去除不符合的值
       strict_trans_tables:严格模式，直接报错
                   traditional:严格模式，直接报错
          查看sql_mode的值：show variables like 'sql_mode';
          修改sql_mode的值：set sql_mode='值'；
          mysql数据库中sql_mode默认严格模式；
2.表管理细化
   创建表的语法：
          create table 表名（
                   字段名 数据类型 [约束条件]，
                   字段名 数据类型 [约束条件]，
                    ......
       [其他约束条件],
                   [其他约束条件]
            ）其他选择（存储引擎，字符集等）
   创建表的同时设置约束，eg:
   create table student(
    s_id tinyint auto_increment primary key,--->学号为主键,且自增
                s_name varchar(20) not null,--->姓名为非空约束
                s_sex enum('男','女')，--->性别为检查约束，mysql中不支持check，若用check，不报错，但是不起作用，可以通过复合数据类型或者触发器解决
                s_card char(18) not null unique,--->身份证号为非空约束和唯一约束，
                s_age tinyint not null default 18,--->年龄为非空约束和默认约束
                c_id tinyint not null,--->该字段是此表中外键，与class中的主键c_id相关联，可以说是一样的两个字段
                #其他约束条件，比如外键约束
                constraint fk_id foreign key(c_id) references class(c_id)on delete cascade on update cascade--->当父表中删除或更新数据时，子表中相关的数据也会删除或更新
   )engine=存储引擎名 default charset=字符集 auto_increment=新的初始值--->可写可不写
   补充：外键所在的表是子表，与之关联的表是父表
             若不写on delete cascade on update cascade，则取默认值，即当父表中删除或更新数据时，子表中有相关的数据，则会报错，除非同时修改；
             设定默认约束后，若想使用默认值，添加数据时表名后名必须显式声明要添加的字段：
             alter table 表名（字段1，字段2，....）values(值1，值2，.....)
3.设置自增列：
   自增列的前提是该字段必须是主键，数据类型是整型，默认从1开始，步长为1
   方式一:在创建表的时候同时设置自增列
              字段名 数据类型 auto_increment primary key
   方式二：表已创建，在字段上添加自增
              alter table 表名 modify 字段名 数据类型 auto_increment primary key;
   设定自增列的初始值：
   方式一：alter table 表名 auto_increment=新的初始值;--->设定自增列初始值
   方式二：set session auto_increment_offset=新的初始值;--->设定自增列初始值
   方式三：创建表的时候设定，在（）外面写auto_increment=初始值
   set session auto_increment_increment=新的步长值;--->设置自增列的步长
4.复制表
   只复制结构：create table 新表 like 源表；
   复制结构和数据：create table 新表 select * from 源表；
5.修改字段的相关信息
   删除字段：alter table 表名 drop 字段名；
   添加字段：alter table 表名 add 新字段名 新数据类型 [新约束条件] [first | after 旧字段名]；
                    #first：添加在第一个；after 旧字段名：添加在旧字段名的后面
   修改字段：alter table 表名 change 旧字段名 新字段名 新数据类型  [约束条件]；
                    alter table 表名 modify 字段名 新数据类型 [约束条件]；
6.修改约束的相关信息
   添加约束（唯一，主键，外键）：alter table 表名 add constraint 约束名 约束类型 (字段名)   -->此种方式方便删除约束
   eg:alter table student add constraint pk_id primary key(s_id);
   删除约束：alter table student drop primary key；--->删除主键约束，若主键上没有自增，则此方法可以删除
                    若主键上有自增，则需删除自增，才可以删主键
                    eg:alter table 表名 modify 字段名 数据类型;--->通过修改字段的方式将自增去掉

                    alter table 表名 drop foreign key 约束名；--->删除外键约束
                    alter table 表名 drop index 约束名；--->删除唯一约束
  #对于默认和非空约束可通过字段的修改来设置
7.修改表的其他选项
   alter table 表名 engine=新的存储引擎类型;
   alter table 表名 default charset=新的字符集;
8.重命名表
   rename table 旧表名 to 新表名；
   alter table 旧表名 rename 新表名；
9.删除表：
   若表与表不存在关系，直接删：drop table 表名；
   若表与表存在外键约束关系：先删子表，再删父表；
2020年3月31日   
1.    mysql的索引
索引：类似于目录，加大查询速度 任何终端类型都可以设置
索引基于存储引擎，存储引擎不同，所使用的引擎就不同
Innodb：使用个索引是聚簇索引.frm 表记录的数据和索引数据在同一个文件中
Myisam：使用个索引是非聚簇索引.myd(表记录数据).myi（索引数据）不同的文件
2.    索引的缺点
一个表中的索引不是越多越好
A．    占空间
B．    维护数据和索引、效率低
C．    耗费时间比较长
3.    索引的分类
普通索引：mysql基本索引类型，没有任何限制，允许在定义索引的列中插入null和重复值
唯一索引：索引列中的值是唯一的，不允许有null
主键索引：在表中多个字段组合上创建索引
全文索引：只能建立在char，varchar，text数据类型上myisam中最早支持全文索引的，innodb在mysql5.6开始支持的
4.    创建索引
方式一：
Create  table  表名（
    字段名1  数据类型  【约束条件】
字段名2  数据类型  【约束条件】
……………
【其他约束条件】
………………
[unique|fulltext]index [索引名]（字段名【长度】）à创建索引
）其他选项（如存储引擎，字符集等选项）
方式二：在已有表上创建索引
   语法一：create [unique|fulltext] index 索引名 on 表名（字段名[(长度)]）
   语法二：alter table 表名 add {unique|fulltext} index 索引名(字段名[(长度)]）
5.删除索引
Drop index 索引名 on 表名
Alter table 表名 drop index 索引名
6.表记录的更新操作
1.使用insert语句插入单条新记录：insert into 表名（字段列表） values(值列表)
2.批量插入多条记录：insert into 表名【（字段列表）】values（列表1），（列表2）。。。
3.使用insert…select插入结果集：insert into 目标表名[(字段列表1)] select（字段列表2） from 源表 where 条件表达式
   4.使用replace插入新记录：
         语法格式1：replace into 表名【（字段列表）】 values（值列表）
          语法格式2：replace [into] 目标表名【（字段列表）】 values（值列表）
         语法格式3：replace [into] 表名 set 字段1=值1 ，字段2=值2………
   注意：使用replace语句向表插入新纪录时，如果新纪录的主键值或唯一性约束的字段值与已有记录相同，则已有记录先被删除，新纪录才会被插入。
5.    表记录的修改：update 表名set 字段1=值1，字段2=值2……[where 条件表达]
6.    表记录的删除：
     Delete  from  表名 [where条件]；
     Truncate table 表名；
区别：1.delete 可以删除部分数据或者全部数据，而truncate只能删除全部数据
      2.使用delete删除部分数据或者全部数据后，再添加新数据，自增列不会从头开始，而truncate清空数据之后，自增列从头开始。
      3.数据大的时候，truncate删除速度比delete快
      4.truncate不支持事务回滚，而delete支持事务回滚
7.mysql的特殊字符序列
         特殊字符用\进行转义

20204月7日
1.表记录的检索
Select 语句语法：
     Select 字段列表
 from 数据源
      [ group by分组字段[ having 条件表达式]]
[ order by 排序字段[ asc | desc ］]
注意点：注意可选项的顺序
字段列表用于指定检索字段。
where子句用于指定记录的过滤条件。
group by子句用于对检索的数据进行分组。
having 子句通常和group by子句一起使用，用于过滤分组后的统计信息。
order by 子句用于对检索的数据进行排序处理，默认为升序asc。
1.查询teacher中所有数据  *
select * from teacher
#2.查询中姓名和联系方式   使用某些字段
select teacher_name,teacher_contact from teacher;
#3.修改显示的字段名  用as  也可不用
select teacher_name as 姓名,teacher_contact as 联系方式 from teacher;
select teacher_name as '姓名',teacher_contact as '联系方式' from teacher;
select teacher_name  姓名,teacher_contact  联系方式 from teacher;
#4.查询班级班表中所有的院系名，但院系名不能重复   去重distinct 一定要写在select后面
#可以去重多个字段，前提是多个字段的数据是相同的
select distinct department_name from classes;
select distinct class_name,department_name from classes;
#5.分页查询limit start length start 默认起始位置为0 0代表第一行记录
select * from classes limit 0,3;
select * from classes limit 3,3;
select * from classes limit 6,3;

多表查询：
   内连接:   inner join  只显示两张表中相互对应的数据，多张表谁在前谁在后都一样
   外连接
     左外连接:  left join
     右外连接:   right join
     完全连接：mysql目前不支持
语法条件：select 字段列表 from 表名1[连接类型] join 表2
     on 表1和表2 之间的连接条件
注意：表1和表2 之间的连接条件指;两表之间的相同字段

#查询学生表学号，姓名，联系方式，班号，班级名称
select student_no,student_name,student_contact,student.class_no,class_name
from student
inner join classes
on student.class_no=classes.class_no
另一种方式
select student_no,student_name,student_contact,student.class_no,class_name
from student,classes
where student.class_no=classes.class_no;
#使用左外连接
#以left join 为分界线，左边的表为主表，右边的表为副表
#显示的结果：以主表为主，主表的数据会全部显示
#主表中的数据在副表中没有对应的数据，则应null填充
select student_no,student_name,student_contact,student.class_no,class_name
from student
left join classes
on student.class_no=classes.class_no;

select student_no,student_name,student_contact,student.class_no,class_name
from classes
left join student
on student.class_no=classes.class_no;
#使用右外连接
#以right join 为分界线，右边的表为主表，左边的表为副表
#显示的结果：以主表为主，主表的数据会全部显示
#主表中的数据在副表中没有对应的数据，则应null填充
select student_no,student_name,student_contact,student.class_no,class_name
from student
right join classes
on student.class_no=classes.class_no;

select student_no,student_name,student_contact,student.class_no,class_name
from classes
right join student
on student.class_no=classes.class_no;

#三张表
/*
select 字段列表 from 表1 [连接类型] join 表2 on 表1和表2之间的连接条件
[连接类型] join 表3 on 表2和表3之间的连接条件
*/
#1.查询学生的详细信息student，课程的详细信息course以及每个学生选修课的分数choose
#choose与student和course两张表同时存在关系
select student.*,course.*,score from student
inner join choose on choose.student_no = student.student_no
inner join course on course.course_no = choose.course_no;

#使用where子句过滤结果集
#语法：select 字段列表 from 表 where 条件表达式；-->表达式1 比较运算符 表达式2--->结果是true或者false
#常用的比较运算符有=（等于） (大士）、E（大于等卡） (小王) 、<（小等子）、
# （不等于）、!=（不等于）、！<（不小于）、!>（不大于）
#where条件，对于一张表
#1.查询院系是软件工程的所有班级信息
select * from classes where department_name='软件工程';
#2.查询分数>90的选课信息
select * from choose where score > 90;
#查询课程号为4且分数>90的选课信息
select * from choose where score 90 and course_no = 4;
#对于多张表
#查询16软工1班-->classes的所有学生信息student 使用内连接
select student.*,class_name,department_name from student
inner join classes on classes.class_no = student.class_no
where class_name ='16软工1班';
#is [not] null 运算符
#1.查询已经分班的所有学生信息 class_no 一定不为null 不能写！=nuli
select * from student where class_no is not null;
#2.查询未分班的所有学生信息
select * from student where class_no is null;
#逻辑运算符 and or (逻辑非，单目运算符)
#1.查询学号大于00005且已分班的所有学生信息
select * from student where student_no > 00005 and class_no is not nul
#2.查询学号大于00005或者未分班的所有学生信息
select * from student where student_no > 00005 or class no is null:
#3.查询学号大于00005的所有学生信息
select * from student where student_no > 00005;
select * from student where !(student_no <= 00005);
#between....and. 在. 和...之间
#查询选课表中分数在[80-90]之间的选课信息
select * from choose where score between 80 and 90;
#成员运算符in 语法：in(值列表)
#查询教师编号为002，005，009的教师信息
select * from teacher where teacher_no in ('002', '005','009');
#模糊查询：like %：匹配0n个字符 只能匹配一个字符
#1.查询姓王的所有老师信息
select * from teacher where teacher_name like'王%';
#2.查询班级中不带软工的所有信息
select * from classes where class_name not like '%软工%';
#3.查询姓名中包含 的所有老师信息 特殊字符需要\进行转义
select * from teacher where teacher_name like '%\_%';
-------------------------------------------------
#order by排序
#语法：order by 字段名1 [ascldesc] [···，字段名n [ascldesc] ]
#1.将选课信息中的成绩按降序进行排列
select * from choose order by score desc;
#2.将选课信息中的成绩按降序进行排列，若成绩相同，按选课号升序排列
select * from choose order by score desc,choose_no asc;

-------------------------------------------------------------
#聚合函数：累加求和sum()函数、平均值avg()函数、
# 统计记录的行数count(函数、最大值max（）函数和最小值min（）函数
#1.查询学生表的总人数 一行是一个学生，即统计多少行
select count(student_no) from student;
select count(*) from student;
select count(class_no) from student;#count()遇到null，自动忽略
#2.查询选课表中学号为00001的总成绩 sum （)
select sum(score) from choose where student_no ='2012001';
#3.查询选课表中学号为00001的平均成绩 avgO
select avg(score) from choose where student_no ='00001';
#4.查询选课表中学号为00001总共选修几门课
select count(choose_no) from choose where student_no ='2012001';
#5. 查询学号为00002的最高成绩和最低成绩
select max(score),min(score) from choose where student_no ='00002';

------------------------------------------------------------------
#group by  分组
#语法：group by 字段列表［ having条件表达式][with_rollup]
#注意：分组语句中select后面可以查询的字段：group by跟的字段，聚合函数
#1.将学生按照班号分组，统计每个班的人数：
select class no，count(student_no) from student group by class_no;
#2.统计每个学生选修了多少门课
select student no. count(course_no)from choose group by student_no;
#3.统计每个学生选修了多少门课，并显示每位学生的最高分，最低分，平均分，总分
select student_no, count(course_no),max(score),min(score),avg(score),sum(score)
from choose group by student_no;
#3.统计每个学生选修了多少门课，并显示每位学生的最高分，最低分，
#平均分，总分,显示平均分大于90的信息
#having :是针对分组之后的过滤，没有group by 就没有having
select student_no, count(course_no),max(score),min(score),avg(score),sum(score)
from choose
group by student_no
having avg(score)>90;
#group_concat:将字符串拼接起来
#5.使用group_concat将分组之后的学生名单拼接起来
select class_no, count(student_no), group_concat(student_name) from student
 group by student_no
select group_concat('鼠','年','大','吉');
#6.with rollup:分组之后的再次统计， 在最后一行显示
select class_no, count(student_no) ,group_concat(student_name) from student
group by class_no with rollup;

   2020 年   4.14
/*mysql的编程基础：
好处：便于代码维护和代码的重用性
方式：函数、触发器、存储过程、事件
1.基础知识 常量
   常量的分类：字符串、数值、十六进制、日期时间、二进制、null
2.用户自定义变量
     用户会话变量： 以@开头  在本次会话期间有效
     局部变量：  不以@开头  在一定的代码范围内有效
3.用户会话变量的定义与使用
 定义有两种方式：set、select
   第一种方式：set @user_variable1=expression1 [,@user_variable2= expression2,…]
   第二种方式：
     第一种语法格式：select @user variable1:=expression1 [user variable2:= expression2
     第二种语法格式：select expression1 into @user_variable1. expression2 into @user_variable2....
4.用户会话变量和sql语句同时使用
     set 变量名 = （select语句）
     select @ 变量名：= （select语句）：
     select @变量名：=三 字段名或者函数 from 表；
     select 字段名或者函数 into 变量名 from 表；
     select 字段名或者函数 from 表 into 变量名；
5.局部变量
  使用declare定义，必须定义在存储程序中（函数、存储过程、触发器、事件），作用域也在此。
  适合场合：
     a. 局部变量定义在存储程序的begin-end语句块之间，此时局部
       变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。；
     b. 局部变量作为存储过程或者函数的参数使用，此时虽然
       不需要使用declare命令定义，但需要指定参数的数据类型。
     c.局部变量也可以用在SQL语句中。
6.局部变量和会话变量的区别
     a.局部变量不使用@，会话变量使用 工
     b. 局部变量使用declare定义，并指明数据类型，才能用set和select进行赋值
       会话直接使用set和select进行定义和赋值
     c.会话变量作用域和生存周期大于局部变量；
     d.局部变量名不能跟字段名一样；

*/
#布尔值的测试 true:1 false:0
select 2 <1;
#测试null
select null =null,null is null;
#使用set定义用户会话变量
set @name='wangxx',@age=18;
set @name='njing';
select @name,@age;
#使用select语句定义用户会话变量
select @s_name='17软工2班',@s_class:='南理工';#执行会有结果集
select @s_name,@s_class;
select '学校' into @s_job,'教师' into @s_delay;#执行没有结果集
select @s_job,@s_delay;
--------------------------------------------------

#全文搜索 match against
/*
select字段列表 from 表名
where match （全文索引字段1，全文索引字段2....）against(搜索关键字 [全文检索方式 ])
5 */

-----------------------------------------------------

#使用正则表达式进行模糊查询
/*
之前：like %
现在：正则表达式 regexp ’匹配模式
*/
#1.查询课程名中包含数据的课程信息 ‘数据'
select * from course where course_name regexp '数据';
#2.查询课程名中以java开头的课程信息 java
select * from course where course_name regexp '^java';
#3. 查询课程名中以s结尾的课程信息 S
select * from course where course_name regexp 's$';
#4.查询课程名中以java开头，以设计结尾的课程信息
select * from course where course_name regexp '^java.*设计$';
#5.查询教师表中联系方式以10或12开头，后面有9位数字的教师信息
select * from teacher where teacher_contact regexp '^1[02][0-9]（9）]';

------------------------------------------------------
#2. 子查询
/*
   如果一个select语句能够返回单个值或者一列值，
   且该select语句嵌套在另一个SQL语句中 (例如select语句、
   insert语句、update语句或者delete语句）中，
   那么该select语句称为子查询（也叫内层查询）
   包含子查询的SQL语句称为主查询（也叫外层查询）。
分类：相关子查询：子查询不能单独执行，依赖主查询; 整个语句的执行顺序：先主后子
    非相关子查询：此种用的最多 子查询语句能单独执行，不依赖主查询；整个语句的执行顺序：先子后主
*/
#1.查询16软工2班所有学生的详细信息，使用子查询 非相关子查询
#a.通过题目要求查询学生的班号
select class_no from classes where class_name='16软工2班';
#b.查询指定班号的所有学生的详细信息
select * from student where class_no = 2;
#合并
select * from student where class_no = (select class_no from classes where class_name='16软工2班'）；
#2.查询学生平均分大于java语言程序设计的最高分的所有学生信息，使用子查询
#a. java语言程序设计的最高分
select max(score) from choose where course_no = (select course_no
from course where course_name='java语言程序设计');
#b.学生平均分大于a中的值就是100
select student_no,avg(score) from choose group by student_no having avg(score) > 100;
#合并：
select student_no, avg(score) from choose group by student_no having avg(score)
(select max(score) from choose where course no (select course_no
from course where course_name='java语言程序设计'））;
#子查询返回多个值 in：查询先执行子、后执行剂
#1.查询16软工2班和16软工3班所有学生的详细信息，使用子查询
select * from student where class_no in
(select class no from classes whereclass_name=16软工2班’or class_name='16软工3班'）;
#2.查询选修java语言程序设计和mysql数据库总学生人数，使用子查询
select count(distinct student_no) from choose where course_no in (
select course_no from course where course_name='java语言程序设计' or  course_name='mysql数据库'）;
#子查询中的exists运算符：先执行主查询， 再执行子
#1.检索所有申请选修课的老师信息 相关子查询
select * from teacher where exists(
select * from course where course.teacher_no = teacher.teacher_no);
#何时使用in和exists
#若主表的记录大于子查询的记录，用in，反之用exists
#子查询与any运算符 只要有一次比较为true，就是true
#表达式 > any(子查询)：大于子查询中最小的数据
#表达式<any(子查询)：小于子查询中最大的数据
#1.查询学生平均分大于java语言程序设计学生成绩最低分的学生信息 使用子查询和any运算符
#a.查询java语言程序设计学生成绩
select score from choose where course_no =(select course_no from course where course_name='java语言程序设计');
#.学生的平均分大于a中的值
select student_no,avg(score) from choose group by student_no having avg(score) >
any(select score from choose wherecourse (select course_no from course where course_name ='java语言程设计'));
#子查询和all运算符  需要每次比较为true 就是true
#表达式>all(子查询)：大于子查询中最大的数据
#表达式<all（子查询）：小于子查询中最小的数据
#1、查询学生平均分小于java语言程序设计学生成绩最低分的学生信息 使用子查询和all运算符
select student_no,avg(score) from choose group by student_no having avg(score)
all(select score from choose where course no (select course no from course where course_name ='java语言程设计'));

----------------------------------------------------------------------
#1.合并结果集
/*注意点：合并多个表中查询的结果，但是对select语句有要求
      a.select语句查询的字段要一摸一样
      b.select语句查询的字段个数相同，数据类型要相同或者相近，顺序要一致
union：结果会去重相同的数据
union all：结果不会去重相同的数据
语法：select 语句 union [all] select 语句
*/
select student_no,student_name,student_contact from student
union
select teacher_no,teacher_name,teacher_contact from teacher;
select student_no,student_name,student_contact from student
union all
select teacher_no,teacher_name,teacher_contact from teacher;
   

2020/4/28
运算符：
算术运算符：
 +（加）、-（减）.*（乘）、/（除）、%（求余）以及div（求商）
div的结果是取整数舍弃小数。
比较运算符：
 <> 同 != 不等于
 <=> 相等或等于空
 is null 是空
 between and 区间
 in 集合
 like 模式匹配 (用于模糊查询)
 regexp 正则表达式
逻辑运算符：
 8& 同 and 与操作
 ！ 同 not 非操作
 xor 异或操作： 相同为假 不同为真
位运算符：
 &|~ ^>> <<
 begin-end语句块 ：作为函数或过程的一个语句执行声明块。
 重置命令结束标记：
   delimiter 标记
定义函数：
必须有数据返回。
创建函数语法：
create function 函数名（参数1，参数2，.)
returns 返回值的数据类型
[函数选项]
begin
 函数体；
 return 语句；
end;
参数的语法：
  ...函数名（ 参数名 类型[（精度）］）….·
函数命名规范：
   fun_开头 或 _fun结尾
函数的选项：
1、
  language sql：用于说明函数体使用SQL语言编写。
2、
  deterministic（确定性）：
  当同样函数参数相同时，返回的结果每次都一样
  1+1 = 2
  not deterministic（默认值）：不确定，比如当前系统时间。
  当同样函数参数相同时，返回的结果每次都可能不一样
3、
  contains sql: 不包含读写语句。（默认的）
  no sql：不包含sql语句
  reads sql data：包含select 不包含更新语句
  modifies sql data：包含更新语句。
4、
  sql security: 用户使用许可
  definer：只能由创建者调用 (默认)
  invoker：全部用户都可调用
5、
  comment：给函数添加注释
删除函数：
  drop function 函数名；
查看自定义函数：
show function status；--查看全部的自定义函数
show function status like 模式；-- 通过模式模糊匹配函数
流程控制：
条件控制语句：
   if then:
语法：
  if 条件表达式1 then
      语句块1;
  [elseif 条件表达式2 then
      语句块2]...
  [else
      语句块n]
  end if;
【case】：
语法：
case 表达式
       when 值1 then
            语句块1；
       when 值2 then
            语句块2；
       else
          语句块n;
end case;
循环语句：
【loop】简单循环：
语法：
  [循环标签：] 1oop
             循环体；
            end loop;
循环标签：用于跳出和跳过循环使用的标记。
【while】带条件循环：
          当条件满足时，进入循环体。否则跳出循环
语法：
          [循环标签：]while 条件表达式 do
                        循环体；
              end while [循环标签];
2020 5.9
王祥祥  11:44:22
内置函数：
数学函数：
pi() 圆周率
radians(x）角度x转换为弧度
degrees(x）弧度x转换为角度
正弦函数sin(x)
余弦函数cos(x)
tan(x)正切函数
余切函数cot(x)
反正弦函数asin(x)
反余弦函数acos(x)
反正切函数atan(x)
sqrt()平方根函数
pow(x,y) x的y次方 同power(x,y)
exp(x) e的x次方
log(x) 自然对数
求近似值函数：
round(x)求x的整数 有四舍五入的
x=1.5 结果 2
x=1.4 结果 1
round(x,y)y需要保留的小数位 有四舍五入的
x=1.125 y=2 结果 1.13
截取
truncate(x,y）将x保留小数y位 没有四舍五入的
x=1.125 y=2 结果 1.12
ceil(x)天花板 截取x最大的整数
-1.2 -1 0 1.2 2 找最靠近自己的右边的整数
x=1.2 结果 2
x=-1.2 结果 -1
floor(x） 地板 截取x最小的整数
-2  -1.2  -1  0  1  1.2  2 找最靠近自己的左边的整数
x=1.2 结果 1
x=-1.2 结果 -2
随机数：
rand()生成0-1之间的小数 不会为0或1
字符串函数：
char_length(x) x中有多少个字符
x="abc我好”结果 5
length(x) x中字符占了多少个字节
假设一个中文占两个字节
x="abc我好”结果 7
单向加密：加密密码
password(x）对x进行加密 返回41位加密字符串
md5(x） 返回32位的加密字符串
加密和解密：通讯的加密
encode(x,key)函数与decode(password,key)
key：秘钥 (秘码本)
字符串连接函数：
concat(x1,x2,…..）字符串拼接
concat（“1","2","3"）结果"123"
修剪函数：
ltrim(x)去除x左边的全部空格
X="  a bc  “结果 “a bc  ”
rtrim(x) 去除x右边的全部空格
X=” a bc  ” 结果 ”  a bc"
trim(x) 去除x右边和右边的全部空格
X=" a bc 结果 “a bc"
left(x,n）截取x的左边的n个字符
x="abcdefg" n=3 结果"abc"
right(x,n)截取x的右边的n个字符
x="abcdefg”n=3 结果"efg"
substring(x,start,length）获取x的子字符串从第start字符开始取length个。
x="abcdefg”start=3 length=2 结果"cd"
replace(x,old,new）把字符串x内的old字符串替换为new字符串
upper(x)\ucase(x) 将x内的字母转换为大写
lower(x)\lcase(x）将x内的字母转换为小写
以上的字符函数不会改变原字符串只是返回新的字符串。
日期和时间函数：
curdate()、current_date() 服务器当前日期
curtime()、current_time() 服务器当前时间
now(）当前日期和时间
year(x)函数
month(x)函数
dayofmonth(x)函数
hour(x)函数
minute(x)函数
second(x)函数
microsecond(x)
年、月、日、时、分、秒、微秒
extract(type from x)
type:year、month、day、hour、minute、second、microsecond
通过type给定的值 来获取x的字段，
monthname(x）返回x的月份的名字
weekday(x) 星期的名字
dayofweek（x）返回本星期的第几天 星期日为1星期一为2...
datediff(x1,x2)日期x1与x2之间的相隔天数
时间格式化函数 p：188
time_format(t,f)    通过f的格式返回t的字符串表示。
f可以用：
   %H 24小时制的时 缺位补0
   %h  12小时制的时 缺位补0
%i   分
%S   秒
日期格式化函数
date_format(d,f)
f:
%Y   4位表示年 1999
%y   2位表示年 99
%m  月
%b  月份缩写
%d  日
%w  星期
视图： 本质是一个select语句。
是一张虚拟的表，可以通过查询得到一些结果提供给其他查询进行使用。
被视图查询的表叫做基本表(基表)。
作用：
   1、简化操作：可以将复杂的查询通过创建视图来简单的查询到。
   2、隐藏或加密特殊字段：可以将字段进行隐藏，或者返回处理完的字段信息。
3.提高数据的逻辑独立性
创建视图语法：
create view 视图名［（视图字段列表）]
as
select语句
视图名：以view_ 开头或  _view结束
也可以用v_ 开头
视图分为普通视图和检查视图
检查视图local 检查视图与cascade检查视图
检查视图使用在更新语句上比较常见
触发器：
用于监视一张表的insert、update以及delete操作。
创建触发器的语法：
create  trigger  触发器名  触发时间  触发事件
on 表名 ［for each row]
begin
  触发程序
end;
触发器名：tri_  trigger_开头
触发时间：before操作之前触发与after操作之后触发。
触发事件：insert、update以及delete
for  each  row: 代表的是行级触发器，每一条记录都会触发一次触发器的执行。 【mysql中只是行级触发器。】
语句级触发器：执行一次sql语句无论影响多少条记录都只进行一次触发。
触发器的程序不是人为调用的，而是根据触发事件和触发时间自动执行的。
old关键字与new关键字;
old关键字:删除或修改之前的记录信息
new关键字:插入或修改之后的新的记录信息
insert：只有new
delete：只有old
update：有new和old
10条注意事项的总结：
  1、触发器不能select返回结果。
  2、不能有相同的触发时间和事件的触发器。
  3、触发器中不能使用事务
  4、因为只有行级触发器，每一行操作都会进行触发，带来的结果就是执行效率下降的情况。
  5、触发器程序不能进行表的更新操作。
  6、触发器必须进过严格测试。
综上所述：能不用触发器就不要去使用触发器。
存储过程：
函数和存储过程都是mysql中的子程序
create procedure 存储过程名（参数1，参数2，..）
[存储过程选项]
begin
     存储过程语句块；
end;
存储过程没有返回值
调用：
call关键进行调用：
语法：
    call 存储过程名（参数……）；
过程的参数分为：
 输入参数(默认的)：in 参数名 类型
 输出参数： out 参数名 类型
 输入输出参数： inout 参数名 类型
存储过程选项：和函数的选项一样
存储过程简称过程。
过程和函数相同：
1.    都是数据库的子程序，都可以完成一些复杂的逻辑操作
2.    都是为了减少使用难度，减少数据库的维护成本
3.    提高了数据库的安全性
过程和函数的不同
1.    函数必须有返回值，且只能有一个返回值。
过程没有返回值的，可以通过out或inout参数来进行赋值的返回
可以有多个输出或输入输出参数来进行返回
2.    函数只能通过select into语句对一个变量进行赋值，不能进行select 语句结果的返回。
过程，没有对select语句进行限制
3.    函数可以嵌入到sql语句中。
过程必须要通过call关键字进行调用，且必须是独立的一条语
句。
4.    函数内部不能使用事务，函数执行代码中限制比较多。
过程可以使用全部的语法。
5.    在应用程序中，比如java，函数可以包含在sql语句里一起发送
给数据库服务器执行的。
过程，必须要使用特殊的执行方法来进行执行。
游标：临时数据存放空间。
使用游标的流程：
1、declare 声明一个游标：
   语法：
    declare 游标名 cursor
   for
   select语句
2、open 打开游标：
  语法：
  open 游标名；
声明游标时，select语句并没有执行
打开游标时，服务器才执行select语句，并将结果放入到游标中
3、fetch 遍历游标：
语法：
fetch 游标名
into 变量名1,变量名2...
变量是用来获取字段的值的。
其数量必须保持和select语句的字段数量一致。
这里需要通过循环语句进配合，如果执行到最后一条记录
后会抛出ERROR 1329 (02000)：No data to FETCH，可以使用错误处理来结
束游标的遍历。
4、close 关闭游标：

语法：
close 游标名；
事务：
    在使用更新语句修改（修改，插入）必须使用事务进行数据提交或回滚
    事务是用来保证数据库中的数据完整性的
事务的ACID的特性
1.atomicity原子性
   原子性用于标示事务是否完全地完成，要么全部提交，要么全部回滚
2.Consistency 一致性
  事务的一致性保证了事务完成后，数据库能够处于一致性状态
3.Isolation 隔离性
  多个事务同时执行时，事务相互之间不受干扰，多线程中的加锁
4.Durabilily 持久性
  事务一旦完成，必然将保持在数据库里的数据时持久性的
隔离性：隔离级别：
   没有隔离性会出现三种问题：
1.    脏读：一个事务读取到另外一个事务未提交的数据
2.    不可重复读：同一个事务中两条相同的查询语句获取的结果不一致
3.    幻读：一个事务中，第一次查询的结果和第二次查询的结果不同，第二次查询的结果是另外一个事务提交后的内容
四种隔离级别：
1.Read  uncommitted 读取未提交的数据
     会出现【脏读|不可重复读|幻读】
2. Read  uncommitted 读取提交的数据
     会出现【不可重复读|幻读】
3.repeatable  read 可重复读
     会出现【幻读】
4.serializable 完全隔绝事务
事务的提交：
    commit命令进行提交事务
Rollback进行回滚
Mysql默认情况下打开了事务的自动提交。
Set autocommit=0：关闭自动提交，提交事务必须使用commit