数据库原理与技术 by.XHF

基本概念

1.数据库是什么数据库是用来存储数据，给软件显示
2.RDBMS关系型数据库管理系统核心：用表存储数据表头有多个字段名，代表每一列数据的含义表中存的是一行一行的数据每一行数据有多个字段值表包含多行数据一个数据库中可以存多个表
3.SQL结构化查询语言关系型数据库系统操作数据的语言oracle,mysql,sqlite不区分大小写，selcet SELECT SeLCet
4.MySqlmysql 瑞典一个公司，被卖给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 studentsdrop 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=03、-- 删除一条数据，只是修改了这条数据的is_delete 改为1update students set is_delete=1 where name='老夫子6'4、-- 查询所有学生时，不显示删除的学生select * from students where is_delete=06.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 studentsselect 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 studentsselect 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 tinyint6.查询select 列1、列2... from 表名 wheregroup 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.courseNo2、左连接（查询的结果为两个表匹配到的数据加上左表特有的数据，对于右表中不存在的数据使用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.courseNo3、右连接（查询的结果为两个表匹配到的数据加上右表特有的数据，对于左表中不存在的数据使用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\bin3、备份某个数据库到某个文件中mysqldump –uroot –p tt > ceshi.sql输入mysql的密码4、恢复1、以管理员身份运行cmd程序2、进入到mysql安装目录下的bin目录3、先创建新的数据库 ceshi_back4、把前面备份的文件恢复到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 students2.-- 包含字符个数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)返回删除了右空格的字符串strselect ltrim('  bar   '),rtrim('  bar   '),rtrim(ltrim('  bar   '));5.-- 大小写转换lower(str)/upper(str)select lower('aBcD'),upper('aBcD');

数学函数    1.-- 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0select 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 resfrom students自定义函数
(命令行使用delimiter $$ 用于设置分割符，默认为分号)1、在navicat查询中，执行下面sql语句，可以在navicat中的数据库下面的函数中找到创建的自定义函数create function my_trim(aaa varchar(100)) returns varchar(100)beginreturn rtrim(ltrim(aaa));end2、使用自定义函数select my_trim('   name    ')

存储过程

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

视图

    1、创建视图（视图本质就是对查询的封装）-- 视图可以隐藏真正的表结构，对于比较重要的数据（如银行），只让别人访问视图，没有权限使用真正的表create view v_stu_score_course asselect stu.name,stu.class,sc.score,cs.name as a from students stuINNER JOIN scores sc on stu.studentNo=sc.studentNoINNER JOIN courses cs on cs.courseNo=sc.courseNo2、使用视图select * from v_stu_score_course3.删除视图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_teacherAFTER DELETE ON teacher  FOR EACH ROW 

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！