数据库原理与技术 by.XHF

基本概念

1.数据库是什么
    数据库是用来存储数据，给软件显示
2.RDBMS
    关系型数据库管理系统
    核心：用表存储数据
        表头有多个字段名，代表每一列数据的含义
        表中存的是一行一行的数据
        每一行数据有多个字段值
        表包含多行数据
        一个数据库中可以存多个表
3.SQL
    结构化查询语言
    关系型数据库系统操作数据的语言
    oracle,mysql,sqlite
    不区分大小写，selcet SELECT SeLCet
4.MySql
    mysql 瑞典一个公司，被卖给sun，sun又被卖给oracle
    开源 免费 支持多平台
        

数据类型与约束

1.数据类型
    整数：int，有符号范围（-2147483648 ~2147483647），无符号范围（0 ~ 4294967295），长度没有意义
    小数：decimal，如decimal(5,2)表示共存5位数，小数占2位，整数占3位，如果插入的小数点后面超出长度，会自动的四舍五入
    字符串：varchar，范围（0~65533），如varchar(3)表示最多存3个字符，一个中文或一个字母都占一个字符
    日期时间: datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），如'2020-01-01 12:29:59'

    查看任何一个数据类型的使用
    1、打开mysql命令行客户端
    2、help 数据类型名称，如 help tinyint
2.约束
        主键(primary key)：代表一条记录的唯一标识，此自动的值必须唯一不能重复，不能为空;
        自动递增：从1开始，一直累加，值不会取以前的值
        表清空：删除所有数据，不删表结构，自动递增的值继续累加
        表截断：删除所有数据，不删表结构，自动递增的值从1开始
        应用时：主键+自动递增+无符号

        非空(not null)：此字段不允许填写空值
        惟一(unique)：此字段的值不允许重复，通过sql语法演示
        默认值(default)：当不填写此值时会使用默认值，如果填写时以填写为准
        外键(foreign key)：维护两个表之间的关联关系，后面多表时讲

SQL语言

1.表操作
    创建表
        -- 创建学生表，字段要求如下：注释 ctrl + / -- 姓名(长度为10) 
           create table students(name varchar(10))

        -- 创建学生表，字段要求如下：取消注释 ctrl + shift + /-- 姓名(长度为10)， 年龄
           create table sutdents1(name varchar(10),age tinyint unsigned)

        -- 创建学生表，字段要求如下： -- 姓名(长度为10)， 年龄，身高(保留小数点2位)
           create table students2(id int unsigned primary key auto_increment,name varchar(10),age tinyint 
           unsigned,height decimal(5,2))
    删除表
        -- 删除学生表
           drop table students
           drop table if exists sutdents
    删除并创建新表
           drop table if exists students;
           create table students(name varchar(10),age int)

2.增删改查
    查询
        -- 查询性别为男的数据
           select * from stu where sex='男'
    插入
        -- 插入一个学生，设置所有字段的信息，值的顺序与表中字段的顺序对应
           insert into students values('亚瑟',20)
        -- 插入一个学生，只设置姓名，值的顺序与给出的字段顺序对应
           insert into students(name) values('鲁班')
           insert into students(age) values(30)
           insert into students(age,name) values(30,'亚瑟2')
        
        -- 插入查询出来的数据
           insert into goods_cate(cate_name) select distinct cate from goods
        -- 在创建表时插入数据
           create table goods_brand(
            brand_id int unsigned primary key auto_increment,
            brand_name varchar(20)) 
            select distinct brand_name from goods
        -- 备份表数据
           create table goods_back select * from goods
        
        -- 当表中有auto_increment的字段时，添加数据时使用0或者 default 或者 null 来占位
           insert into students values(0,'老夫子3',20);
    插入多条数据
           insert into students values(0,'老夫子3',20);
           insert into students values(0,'老夫子4',20);
           insert into students values (0,'老夫3',20),(0,'老夫4',20),(0,'老夫5',20)
           insert into students(id,name) values (0,'老夫3'),(0,'老夫4'),(0,'老夫5')
    修改数据
        -- 更新数据 设置某一个学生的年龄加3岁
           update students set age=age+3 where name='亚瑟3'
    删除数据
        -- 删除数据
           delete from students where name='亚瑟3'
    逻辑删除
        1、-- 添加字段，标识数据是否被删除 is_delete
        默认设置为0，代表数据没有被删除
        2、-- update students set is_delete=0
        3、-- 删除一条数据，只是修改了这条数据的is_delete 改为1
           update students set is_delete=1 where name='老夫子6'
        4、-- 查询所有学生时，不显示删除的学生
           select * from students where is_delete=0

6.sql查询
    给字段起别名
        -- select name as 姓名,age as 年龄,hometown as 家乡          from students where name='王昭君'
        -- select name 姓名,age 年龄,hometown 家乡 from students where name='王昭君'
    给表起别名
           select s.name,s.age from students as s
    消除重复数据
           select distinct age,class from students
           select distinct * from students
    比较运算
        -- 例1：查询小乔的年龄
           select age from students where name='小乔'
        -- 例2：查询20岁以下的学生
           select * from students where age<20
        -- 例3：查询家乡不在北京的学生
           select * from students where hometown<>'北京
    逻辑运算
        -- 例2：查询女学生或'1班'的学生
           select * from students where sex='女' or class='1班'
        -- 例3：查询非天津的学生
           select * from students where not hometown='天津'
    模糊查询
        -- 例1：查询姓孙的学生
           select * from students where name like '孙%'
        -- 例2：查询姓孙且名字是一个字的学生
           select * from students where name like '孙_'
        -- 例3：查询叫乔的学生
           select * from students where name like '%乔'
        -- 例4：查询姓名含白的学生
           select * from students where name like '%白%'
    范围查询
        -- 例1：查询家乡是北京或上海或广东的学生
           select * from students where hometown in ('北京','上海','广东')
           select * from students where hometown not in ('北京','上海','广东')
        -- 例2：查询年龄为18至20的学生  between 20 and 18 小值在前
           select * from students where age between 18 and 20
    判空
        -- 例1：查询没有填写身份证的学生
           select * from students where card is null
        -- 例2：非空
           select * from students where card is not null
        -- 例3：判断身份证为空字符
           select * from students where card=''
    排序
        -- 例1：查询所有学生信息，按年龄从小到大排序
           select * from students order by age asc
    降序
           select * from students order by age desc
        -- 例2：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
           select * from students order by age desc,studentNo

    聚合函数
        -- 例1：查询学生总数 
           select count(*) as 学生总数 from students
           select count(name) from students
        -- count(card) 不统计为null数据
           select count(card) from students
        -- 例2：查询女生的最小年龄
           select min(age) from students where sex='女'
        -- 例3：查询1班的最大年龄
           select max(age) from students where class='1班'
        -- 例4：查询北京学生的年龄总和
           select sum(age) from students
        -- 例5：查询女生的平均年龄
           select avg(age) from students where sex='女'
    分组
        -- 例1：查询各种性别的人数
           select sex,count(*) from students group by sex
        -- 例2：查询各种年龄的人数
           select age,count(*) from students group by age
        -- 例1：查询男生总人数
           select sex,count(*) from students group by sex having sex='男'
           select count(*) from students where sex='男'
    分页
           select * from 表名 limit start,count 
        -- 从start开始，获取count条数据，start索引从0开始
        -- 例1：查询前3行学生信息
           select * from students limit 0,3
        -- 已知：每页显示m条数据，求：显示第n页的数据
           select * from students limit (n-1)*m,m

第一节总结

1.SQL语句
    表 创建 create 能看懂  删除 drop   能看懂
    增 insert 学会 能写
    删 delete 能看懂
    改 update 能看懂
    查 select 重点 重点 重点 能熟练写
    保留小数 round(avg(*),保留小数位) 

2.数据类型
    1.int(1) 长度没有意思
    2.varchar(5) 5个字符
    3.decimal(5,2) 3个整数，2个小数，添加数据超过长度，自动四舍五入
    4.datetime命令行客户端，使用，查看某一个数据类型的使用帮助
    5.help tinyint

    6.查询select 列1、列2... from 表名 where
      group by 分组
      order by 排序
      having 分组后过滤
    7.实体A对实体B为1对1，则在表A或表B中创建一个字段，存储另一个表的主键值
    8.实体A对实体B为1对多：在表B中创建一个字段，存储表A的主键值
    9.实体A对实体B为多对多：新建一张表C，这个表只有两个字段，一个用于存储A的主键值，一个用于存储B的主键值

连接查询

笛卡尔积（两个表数据个数相乘）
1、等值连接（普通：select * from 表1,表2 where 表1.列=表2.列）
          （推荐内连接：先匹配，不产生大量笛卡尔积无效值select * from 表1 inner join 表2 on 表1.列=表2.列）
    
    -- 如果没有的数据，则不显示，取交集

    -- 查询学生信息及学生的成绩
       select stu.name,sc.score from students as stu ,scores as sc where stu.studentNo=sc.studentNo
    -- 查询成绩信息及课程信息 内连接
       select sc.score,cs.name from scores sc inner join courses cs on sc.courseNo=cs.courseNo
    -- 查询学生信息及学生的课程对应的成绩
       select stu.name 姓名,sc.score 成绩,cs.name 课程名 from students stu,scores sc,courses cs where stu.studentNo=sc.studentNo and sc.courseNo=cs.courseNo
    -- 查询学生信息及学生的课程对应的成绩 内连接
       select stu.name,sc.score,cs.name from students stu inner join scores sc on stu.studentNo=sc.studentNo inner join courses cs on sc.courseNo=cs.courseNo

2、左连接（查询的结果为两个表匹配到的数据加上左表特有的数据，对于右表中不存在的数据使用null填充，取并集）select * from （左）表1 left join 表2 on 表1.列=表2.列
    -- 例1：查询所有学生的成绩，包括没有成绩的学生
       select stu.name,sc.score from students stu left join scores sc on stu.studentNo=sc.studentNo
    -- 例2：查询所有学生的成绩，包括没有成绩的学生，需要显示课程名
       select stu.name,sc.score,cs.name from students stu left join scores sc on stu.studentNo=sc.studentNo left join courses cs on sc.courseNo=cs.courseNo

3、右连接（查询的结果为两个表匹配到的数据加上右表特有的数据，对于左表中不存在的数据使用null填充，取并集）select * from （左）表1 right join 表2 on 表1.列=表2.列
    -- 课程表插入一些数据
       insert into courses values (0, '语文'),(0, '数学');
    -- 例1：查询所有课程的成绩，包括没有成绩的课程
       select sc.score,cs.name from scores sc right join courses cs on sc.courseNo=cs.courseNo
    -- 例2：查询所有课程的成绩，包括没有成绩的课程，包含学生
       select sc.score,cs.name,stu.name from scores sc right join courses cs on sc.courseNo=cs.courseNoleft join students stu on stu.studentNo=sc.studentNo

    -- 左右连接：join前是左表，后是右表

自关联 子查询

1、自关联（省市县这种用一个表格创建，存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，故此自己和自己关联，达到查询表内下级信息的作用 Where A.编号=B.上级）
    -- 例1：查询一共有多少个省
       select count(*) from areas where pid is null
    -- 例2：查询河南省的所有城市
       select * from areas p,areas c where p.aid=c.pid and p.atitle='河南省'
    -- 例3：查询郑州市的所有区县
       select * from areas p,areas c where p.aid=c.pid and p.atitle='郑州市'
       select * from areas p inner join areas c on p.aid=c.pid where p.atitle='郑州市'

2、子查询(子查询是可以独立存在的语句,是一条完整的 select 语句)
    
    标量子查询: 子查询返回的结果是一个数据(一行一列)
    -- 例1：查询班级大于平均年龄的学生
       select * from students where age>(select avg(age) from students)
    -- 例2：查询王昭君的成绩，要求显示成绩
       select * from scores where studentNo=(select studentNo from students where name='王昭君')

    列子查询：返回的结果是一列(一列多行)
    -- 例3：查询18岁的学生的成绩，要求显示成绩('002','006')
       select * from scores where studentNo in(select studentNo from students where age=18)

    行级子查询: 返回的结果是一行(一行多列)
    -- 例4：查询男生中年龄最大的学生信息
       标量：select * from students where sex='男' and age=(select max(age) from students where sex='男')
       标量：select * from students where (sex,age)=('男',30)
       行级：select * from students where (sex,age)=(select sex,age from students where sex='男' order by age desc limit 1)

    表级子查询：返回的结果是多行多列
    -- 例5：查询数据库和系统测试的课程成绩
    -- select * from 数据源
       select * from scores sc inner join (select * from courses where name in ('数据库','系统测试')) c on sc.courseNo=c.courseNo

第二节总结

    1.子查询中特定关键字使用
    --01 in 范围（select * from students where age in (select age from students where age between 18 and 20)）
    格式: 主查询 where 条件 in (列子查询)
    --02 any | some 任意一个  
    格式: 主查询 where 列 = any (列子查询)
    在条件查询的结果中匹配任意一个即可,等价于 in
    --03 all
    格式: 主查询 where 列 = all(列子查询) : 等于里面所有
    格式: 主查询 where 列 <>all(列子查询) : 不等一其中所有

    条件查询：where 字段 <,>,=,!=，and ,or, not，in,between，and，like（% _）
    排序：order by asc,desc（降序）
    聚合函数：sum max min avg count(*) 
    分组：group by having
    分页：limit 0,3 游标 索引 角标
    连接查询：等值连接（from 表1，表2  inner join） 左连接 left join  右连接 right join
    子查询：标量子查询 列子查询 行子查询 表级子查询

命令行客户端使用

    1、数据库操作
    -- 查看所有数据库show databases;
    -- 使用数据库use 数据库名;
    -- 查看当前使用的数据库select database();
    -- 创建数据库create database ceshi charset=utf8;
    -- 删除数据库drop database ceshi;
    2、数据表
    -- 查看当前数据库中所有表show tables;
    -- 查看表结构desc 表名;
    -- 查看表的创建语句show create table students;
    3、备份
    1、以管理员身份运行cmd程序
    2、进入到mysql安装目录下的bin目录
    cd C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin
    3、备份某个数据库到某个文件中mysqldump –uroot –p tt > ceshi.sql输入mysql的密码
    4、恢复
    1、以管理员身份运行cmd程序
    2、进入到mysql安装目录下的bin目录
    3、先创建新的数据库 ceshi_back
    4、把前面备份的文件恢复到ceshi_back数据库中mysql -uroot –p ceshi_back < ceshi.sql输入mysql的密码

SQL函数

字符串函数
    1.-- 拼接字符串concat(str1,str2...)
      select concat(12,34,'ab');
      select name,sex,hometown,concat(name,'是',hometown,'的',sex,'生') as des from students
    2.-- 包含字符个数length(str)
      select length('abc');
    3.-- 截取字符串
    --left(str,len)返回字符串str的左端len个字符
    --right(str,len)返回字符串str的右端len个字符
    --substring(str,pos,len)返回字符串str的位置pos起len个字符
      select substring('abc123',2,3);pos指的位置时从1开始(bc1)
    4.-- 去除空格
    --ltrim(str)返回删除了左空格的字符串str
    --rtrim(str)返回删除了右空格的字符串str
      select ltrim('  bar   '),rtrim('  bar   '),rtrim(ltrim('  bar   '));
    5.-- 大小写转换lower(str)/upper(str)
      select lower('aBcD'),upper('aBcD');

数学函数    
    1.-- 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0
      select round(1.6,1);
    2.-- 求x的y次幂pow(x,y)
      select pow(2,3);
    3.-- 获取圆周率PI()
      select round(PI(),10)
    4.-- 随机数rand()，值为0-1.0的浮点数
      select rand();
      select rand(),s.* from students s order by rand() limit 1
      (select * from students order by rand() limit 1)

日期时间函数
    1.-- 当前日期current_date()
    2.-- 当前时间current_time()
    3.-- 当前日期时间now()
      select current_date(),current_time(),now();
    4.-- 日期格式化date_format(date,format)
      select date_format(now(),'%Y-%m-%d/%h/%i/%s');
    5.-- 流程控制  case语法
      select 
      case 1 
      when 1 then 'one'
      when 2 then 'two' 
      else 'zero' 
      end as result;
    6.-- 姓 美女 帅哥
      select left(name,1) ,sex,
      case sex 
      when '男' then concat(left(name,1),'帅哥')
      when '女' then concat(left(name,1),'美女')
      else '保密'
      end as res
      from students

自定义函数
(命令行使用delimiter $$ 用于设置分割符，默认为分号)
    1、在navicat查询中，执行下面sql语句，可以在navicat中的数据库下面的函数中找到创建的自定义函数
      create function my_trim(aaa varchar(100)) returns varchar(100)
      begin
      return rtrim(ltrim(aaa));
      end
    2、使用自定义函数
      select my_trim('   name    ')

存储过程

    1、在navicat查询中，执行下面sql语句，可以在navicat中的数据库下面的函数中找到创建的存储过程
      delimiter //
      create procedure proc_stu()
      begin
      select * from students;
      end//
    2、使用存储过程
      call proc_stu();
    3、存储过程和函数都是为了可重复的执行操作数据库的 sql 语句的集合。存储过程和函数都是一次编译,就会被缓存起来,下次使用就直接命中缓存中已经编译好的 sql, 不需要重复编译。减少网络交互,减少网络访问流量

视图

    1、创建视图（视图本质就是对查询的封装）
    -- 视图可以隐藏真正的表结构，对于比较重要的数据（如银行），只让别人访问视图，没有权限使用真正的表
      create view v_stu_score_course as
      select stu.name,stu.class,sc.score,cs.name as a from students stu
      INNER JOIN scores sc on stu.studentNo=sc.studentNo
      INNER JOIN courses cs on cs.courseNo=sc.courseNo
    2、使用视图
      select * from v_stu_score_course
    3.删除视图
      drop view v_stu_score_course;

事务

    1、提交
        两个命令行客户端 左边客户端 右边客户端
        1、左边客户端：查询学生信息
           select * from students;
        2、右边客户端：开启事务，插入数据
           begin;
           insert into students(studentNo,name) values ('013','abc');
        3、右边客户端：查询数据，此时有新增的数据
           select * from students;
        4、左边客户端：查询数据，发现并没有新增的数据
           select * from students;
        5、右边客户端：完成提交
           commit;
        6、左边客户端：查询，发现有新增的数据
           select * from students;

    2、回滚
        两个命令行客户端 左边客户端 右边客户端
        1、左边客户端：查询学生信息
           select * from students;
        2、右边客户端：开启事务，插入数据
           begin;
           insert into students(studentNo,name) values ('014','aaa');
        3、右边客户端：查询数据，此时有新增的数据
           select * from students;
        4、左边客户端：查询数据，发现并没有新增的数据
           select * from students;
        5、右边客户端：回滚
           rollback;
        6、左边客户端：查询，发现没有新增的数据
           select * from students;
        7、右边客户端：查询，发现没有新增的数据
           select * from students;

    3、事务介绍：
        1.开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中（begin；）
        2.提交事务，命令如下，将缓存中的数据变更维护到物理表中（commit;）
        3.回滚事务，命令如下，放弃缓存中变更的数据（rollback;）

索引（目录）

    1、当数据库中数据量很大时，查找数据会变得很慢，优化方案：索引（B-Tree内部实现）
    2、CMD开启运行时间监测：set profiling=1;
    3、查看执行的时间：show profiles;

    #方式一：建表时创建索引#
       create table create_index(
       id int primary key,
       name varchar(10) unique,
       age int,
       key (age)# (索引) #);

    # 方式二：对于已经存在的表，添加索引 #
       reate index 索引名称 on 表名(字段名称(长度))
       如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致。
       字段类型如果不是字符串，可以不填写长度部分。
    # 创建表时，对于主键和unique字段，自动创建索引 #
    
    4、为表title_index的title列创建索引：
       create index title_index on test_index(title(10));
    5、查看索引：show index from 表名;
    6、注释：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件但是,在互联网应用中,查询的语句远远大于增删改的语句,甚至可以占到80%~90%,所以也不要太在意,只是在大数据导入时,可以先删除索引,再批量插入数据,最后再添加索引

    7、分析查询（DBA数据库性能优化分析）
       explain（key表示所用到的索引，rows表示扫描分析行数）
       select * from test_index where title='test10000'

外键

    1、从表添加、修改数据时，受主表的约束
    2、主表删除数据时，如果已经被从表依赖了，不能删除
       alter table 从表名 add foreign key (从表字段) references 主表(主表字段);
    3、创建外键时，主表和从表的两个字段类型要保持一致
       alter table goods add foreign key (cate_id) references goods_cates(cate_id);
    4、对关系字段进行约束，当为从表中的关系字段填写值时，会到关联的主表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并报错
    5、查看外键和建表语句show create table 表名（constraint）
    6、删除alter table goods drop foreign key 外键名称;
    7、注释：在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

触发器

    1、语法
    CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_statement;

    CREATE TRIGGER trig_teacher
    AFTER DELETE ON teacher  FOR EACH ROW