10.23 数据库学习总结

数据库学习总结"/>

10.23 数据库学习总结

数据库的分类

1.关系型:

• 定义:表与表之间存在关系
• 常见的关系型数据库: mysql  sqlserver   oracle

2.非关系型

• 定义：表和表之间不存在关系,每张表都是独立的
• 常见非关系型数据库: Redis   mongodb

主要学习的内容Mysql

特点
Mysql 数据库的特点:体积小、速度快、总体拥有成本低，尤其是开放源码 

Mysql 的使用

简单的SQL命令:

#查看版本号：
select version();
#查看本地所有的数据库：
show databases;
#查什么写什么：
select xxxx';
#切换数据库：
use test; 
#查看所有的表：
show tables;
#退出:
Exit

表结构

表结构先确定列  确定列先确定数据类型。

数据类型   

数值型  ：  int   bigint  decimal 
字符型   ：char -定长   varchar---变长       text/longtext

时间型   ：    date--年月日    datetime--年月日 时分秒

SQL中的分类

DDL数据定义语言(Data Definition Language)

代表关键字:create  创建,drop 删除, alter修改

DML数据操作语言(Data Manipulation Language)

代表关键字:insert 增,delete 删,update改 

DQL数据查询语言(Data Query Language)  

代表关键字:select 查询 

DDL 数据库定义语言

1.create 创建     

语法 create Table  表名(

列, 数据类型

);

代码如下

CREATE TABLE  t_order(
id int AUTO_INCREMENT PRIMARY KEY,
nun int,
addr VARCHAR(50),
name  VARCHAR(10),
phone CHAR(11)
)

2.drop 删除

语法: drop table 想删除的表名

代码如下

drop table 想删除的表名

3.DDL的增强  关键字alter

1.修改表名  关键字rename.

语法 alter table 表名 rename 新表名

代码

alter table student rename student_01

 2.更改字段 关键字 change   注意 change 可以更改字段,也可以更改字段的数据类型.

语法:-alter table表名change列名 新列名数据类型；

代码

alter table t_order change addr saddr VARCHAR(12);

3.添加列关键字 add

语法:alter table表名add列名类型；

代码

alter table student add name2 char(3);

4.-删除列关键字drop

语法:alter table表名drop列名

代码

alter table student drop sname;

5.更改新的列数据类型:关键字 modify

语法: -alter table表名modify列名新数据类型；

代码

alter table t_order 表名 MODIFY nun 列名 char(2) 新的数据类型 ;

DDL中的约束:

1.主键自增   关键字  AUTO_INCREMENT  PRIMARY KEy

创建时添加主键自增代码

CREATE TABLE  t_order(
id int AUTO_INCREMENT PRIMARY KEY,   主键自增添加
nun int,
addr VARCHAR(50),
name  VARCHAR(10),
phone CHAR(11)
)

2.主键  关键字 PRIMARY KEY

在创建时创建主键

CREATE TABLE  t_order(
id int  PRIMARY KEY,
nun int,
addr VARCHAR(50),
name  VARCHAR(10),
phone CHAR(11)
)

3.外键 /物理外键  关键字foreign key(主键) reference 另一张表的表名(另一张表中想要的外键)

定义:

定义： 一个表中的外键是另一个表中的主键

项目中不允许加物理外键， 只加逻辑外键。

逻辑外键  ： 就是不使用外键， 写上就行。

CREATE TABLE  t_order(
id int  PRIMARY KEY,
nun int,
addr VARCHAR(50),
name  VARCHAR(10),
phone CHAR(11)
foreign key(id) reference 另一张表的表名(另一张表中想要的外键)
)

4.默认值和非空值  关键字default 默认值 not null 非空

在创建表格时添加

create table temp2(id int not null,`name` varchar(30) default  'abc',sex varchar(10) not null default '男'）；

5. 唯一 关键字 unique 

在创建表格是添加唯一约束

create table student01(`name` varchar(5),id int UNIQUE  // 创建表格时添加唯一)

DML（数据操作语言） 重点

1.Insert 新增

INSERT into student(name,age,sex)VALUES（'张三'，l8,'男')；

2.update  修改 

update表名set列名1=值，列名2=值where列名=值；
UPDATE t_student set score='100'  where id=2;直接修改不用管以前的 直接覆盖

3.删除   delete

删除
delete from表名where列名=值；
delete from student where name='张三'；

DQL(数据查询语言)

select  查询

select 列限定 from 表限定 where 行限定

DQL 中的条件判断

1.and  且,和,的意思,一般用于 必须符合两个添加的判断,等同于java中的 &&

select *from student WHERE name='张三' AND score>91;

2.or或的意思,一般用于 符合一个添加判断的情况下,等同于java中的 ||.

select *from student WHERE name='张三' or score>91;

3.between  and   在...之间  

语法 :

select 列限定 from 表限定 where 列名 between 值1 and 值2;

select *from student WHERE score BETWEEN 95 AND 1000;

4.In  在指定数据中

语法 :

select 列限定 from 表限定 where 列名 in(值1,值2....);

select *from student WHERE score in(98,97);    in( 写谁找谁)

5.模糊查询like  注意点: % 匹配任意个数的任意字符 _ 匹配单个任意字符

语法 : select 列限定 from 表限定 where 列名 like  '值' ;如果想要查询 _ 或者 % 需要转义  \%   \_

删除学生信息表中，姓“小”的学生。(2分)
DELETE   FROM student_info WHERE name LIKE '小%'

6.Order by 排序

语法 : select 列限定 from 表限定 order by 列名 asc/desc  注意点:Asc : 升序Desc : 降序

SELECT * FROM student ORDER BY score DESC  /倒叙
SELECT * FROM student ORDER BY score Asc # 正序

7.Limit   限制条数,通常和order by一起使用,因为我们使用排序之后,再去获取前几条数据,比较有价值,比如成绩前三名

语法 : select 列限定 from 表限定 limit 条数;

select *FROM student LIMIT 1;

select 列限定 from 表限定 limit 开始值(不包含) ,条数;

select *from student LIMIT 1,3;  不包括1 取3个值

经典题

查询成绩表中每个学生的成绩，包括学生学号、姓名、课程、成绩、成绩等级(score_level)字段，其中成绩等级字段取值逻辑为：根据成绩score字段，60<=score<75为及格，75<=score<85为良，85<=score<=100为优，其余为不及格。SELECT *,(CASE WHEN score<75 and score>=60 THEN '及格'WHEN score<85 and score>=75 THEN  '良'WHEN score<100 and score>=85 THEN'优'END 
)as score FROM student_score as ss JOIN student_info  as si  on ss.stdno=si.stdno 

 MQL 中的group by 组函数      

先分组再排序

先分组再过滤

count(*) : 总条数

#count(*) : 总条数
select COUNT(*)as '总条数' FROM student WHERE id;

max(字段名) : 最大值

#max(字段名) : 最大值
select max(score) as'最大值' FROM student WHERE score;

min(字段名) : 最小值

#min(字段名) : 最小值
select min(score) as'最小值' FROM student WHERE score;

avg(字段名) : 平均值

#avg(字段名) : 平均值
select avg(score) as'平均值' FROM student WHERE score;

sum(字段名) : 总和

#sum(字段名) : 总和
select sum(score) as'总和' FROM student WHERE score;

MQL常用函数：

1.返回字符个数  关键字char_length()

select CHAR_LENGTH('武正旭');

2.返回字符所占字节数 关键字select  length('中国'); 注意点  MySQL中，一个UTF8编码的汉字占3个字节

SELECT length('中国')

3.拼接符  关键字 concat  

#select  concat(  '=',  'a',  'b',  'c');

4.拼接符select CoNCAT_WS ('|','a','v','g');  第一个字符是分割符

select CoNCAT_WS ('|','a','v','g')

5. upper('abcd');转成大写

select upper('acas')

6.select  lower('ABCD');转小写.

select lower('ABCD')

7.  切割select  substring(  '系统信息类',  1,  3  );  三个参数 第一个是切割内容,第二个是开始切割的位置下标,第三个是从开始切割的位置往后切割几刀.

select SUBSTRING('欢乐麻将',1,2)；

DQL中的Having    过滤   先分组再过滤

从HAVING中得出的执行顺序：表限定>行限定>列限定

having  可以使行限定 最后执行

DQL合并执行

union 去重

union all 不去重

DQL中的子查询

三种情况的说明及注意事项

在select 后边的子查询

在from 后边的子查询

在where后边的子查询 

1.在select 后边  注意点

1.一定要在两个表之间找好对应关系（teacher.id必须是主键或者必须保证teacher.id在teacher表中是唯一的）

2.子查询中只能有一个字段（子查询的结果必须是一行一列）

使用子查询的时候，建议大家养成使用别名的好习惯，这样可以让我们的查询语句更加清晰。别名可以用来命令新字段，也可以用来命名新表.

select*,(select name from teacher where id=s.teacher id)as'老师姓名'from student as s;

2.在from 后边  注意点

当位于FROM后面时，要注意

1.我们可以把子查询当成一张虚拟的表

2.必须要有别名,因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作

select *,
case rank
when 'A' then '优'
when 'B' then '良'
when 'C' then '差'
end rank_ch
from (
select *,
case // 不常用的 case when
when score < 60 then 'C' 
when score >=60 and score <80 then 'B' 
when score >=80 then 'A' 
end as rank 
from student
) a;

3.在where 后边

注意点

当位于WHERE后面时，要注意

1. 多条数据要用in而不要用=,如果确定子查询的结果为一行一列的话,就可以用 = 等于
2. 如果返回结果为多行一列的话 要用 in , 一列是必须的,必须是一列

3.子查询中的SELECT后面只能有一个字段（多个字段的话会报错）

DQL中的多表查询

1.行转列

什么是行转列 :通过SQL语句 实现这样的功能,就叫行转列

正常的查询结果是这样

 

可是我想要的结果是这样...

 

end

2..场景一(多行转一行多列)

 

可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：

select name,max(case coursewhen 'java' then scoreend) Java, max(case coursewhen 'MySQL' then scoreend) MySQLfrom test_9group by name;

思路分析 :

首先我们默认的情况 每个名字都输出两次,而最终结果只有一次名字,所以肯定是 以名字分组 group by

select * from test_9 group by name;

对我们来说 ,id,课程,和分数都不需要了,只需要有名字 然后再把java和mysql放上去

select name , 1 as java , 1 as MySQL from test_9  group by name;

然后再使用聚合函数聚合（此处理解“聚合”，相当于把多行数据压扁成一行）

select name,max(case coursewhen 'java' then scoreend) Java, max(case coursewhen 'MySQL' then scoreend) MySQLfrom test_9group by name;

场景二(多行转一行一列)

相关函数

concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行

思路分析 :

第一步：拆分问题，先按分组的思路

select name,1 as '各科成绩' from test_9 group by name;

 第二步：将课程名与成绩拼接成一列

select name,concat(course,'=',score) as '各科成绩'from test_9 group by name;

 第三步：利用group_concat函数将多行压扁到一行

select name,group_concat(course,'=',score) as '各科成绩'from test_9 group by name;

 第四步：修改分隔符（默认是逗号）

select name,group_concat(course,'=',score separator ' | ') as '各科成绩'from test_9 group by name;

 第五步：按课程名称排序

select name,group_concat(course,'=',score order by course asc  separator ' | ') as '各科成绩'from test_9 group by name;

DQL-连接查询  多表查询

最简单的多表查询 : select * from 表1,表2;

内连接: 

 inner join 

特点:丢失表的特有属性

外连接:

1.left join   保留左表的特有属性, 丢失右表的特有属性

2.  right   join   保留右表的特有属性, 丢失左表的特有属性

在使用 join 连接查询 时，on和where条件的区别如下：
    1、on条件是在生成临时表时使用的条件,需要和链接查询一起使用。必须放两张表关联关系的逻辑外键
    2、where条件是在临时表生成好后，再对临时表进行过滤的条件。

  

完整版的政旭教学

-- 数据库
-- 查看数据库
show databases;-- DDL  数据库定义语言  databases defined language    create  alter  drop
create table demo1(
ids int PRIMARY KEY auto_increment,
name char(22),
addr varchar(22)
)ENGINE = INNODB  default CHARSET=utf8alter table demo1 rename demonewalter table demonew add newlie varchar(22)
alter table demonew add FOREIGN KEY(ids) REFERENCES demo(id)alter table demonew change newlie oldlie varchar(22)alter table demonew drop oldliealter table demonew MODIFY  name varchar(22)
alter table demonew modify name varchar(22) not null 
alter table demonew modify name varchar(22) default 'wzx'drop table demonew -- DML数据操作语言   insert   update   delete   
insert  into demo1   VALUES(3,'b','asda')update demo set id = 120 where id =  1delete from demo where id = 120

-- DQL   数据查询语言
# 条件判断
# 组函数
# 常用函数
select *
from demoselect *
from demo
where id = 2select *
from demo
where id between 1 and 100select *
from demo
where id in (2, 3)select *
from demo
where id is not nullselect *
from demo
where id = 2or id = 111select *
from demo
where name like 'b'select *
from demo
limit 0,5select *
from demo
order by id ascselect name, count(*)
from demo
group by nameselect upper('asdasa')
select lower('ADASDASD')select version()select concat('1', 'a')select concat_ws('=', 'a', 'b')# 子查询，嵌套查询，多表联查
select *
from demo
where id in (select ids from demo1)
select *
from demo
where id in (2, 3, 111, 121)alter table demoadd addr varchar(22)select *
from demoinner joindemo1ON DEMO.ID = demo1.idsselect *
from demoleft join demo1 on demo.id = demo1.idsselect *
from demoright join demo1 on demo.id = demo1.ids