数据库的基本操作

数据库发展历程

1.层次数据库和网状数据库技术阶段

ps:使用指针来表示数据之间的联系

2.关系数据库技术阶段(表格)

典型代表: Oracle DB2 MySQL SQLServer等

3.后关数据库技术阶段

关系型数据库存存数据的时候,会出现新能扩展性等缺点出现了另外一个技术
NoSQL:非关系型数据库 -->not only SQL 不仅只有SQL

常见的非关系型数据库
1.键值性存储的数据库: Redis 2.列式存储数据库: HBase(熟练)
3.文档性数据库:MongoDB 4.图形数据库: Neo4J(了解)
常见关系型数据库:
MySQL :开源免费,体积小,安装方便,速度快,适用于中小企业 -->(存储瓶颈40G左右(MySQL集群))
Oracle : 运行稳定,可移植性高,功能齐全,性能超群! 适用于大企业(付费)
DB2 : 速度快,可靠性高,界面友好,操作容易,恢复性极强,使用能海量数据, 多使用与金融或政府机构
SQLServer : 微软必属"精品" 自己理解

SQL语言

SQL是数据库标准语言

SQL包含6个部分

1.数据查询语言(DQL): 其语句也成为"数据库检索语言(语句)"用于从表中获取数据,将数据进行展示(自带的) DQL语言在各种数据库中都是通用的 select , where , order by ,group by 和 having
2.数据操作语言(DML)
向数据库插入数据insert 更新upDate 和删除 delete 他们都是对表的操作语言
3.事物处理语言(TPL)
保证被DML处理后语句可以的到即使的更新 TPL语言: transaction(事务) commit和 Rollback
4.数据控制语言(DCL) Revoke撤销,grant(许可)确定单个用户会和组用户对数据库对象的访问
5.数据库定义语言(DDL)
create创建 和 drop(删除) 对数据库中表库进行操作
6.指针控制语言(CCL)
update where current(更新当前的位置) 用于对一个或多个表的单独操作

书写规范:
1.在数据库中 SQL语句大小不敏感(即可以大小也可以小写,甚至大小写混写)
ps: SELECT / select / SElect 操作数据的关键字尽量大写

2.SQL语句即可以单行书写完成也可以多行书写完成

ps: select * form stu where stu.age > 18 一行即单行写完 
select * from stu
where stu.age > 18 两行是一体 这样可以分行写

3.在SQL语言中,关键不能进行缩写 ps:select —> sel(这是什么?)

4.为了提高可读性,一般关键大写,其他小写

5.空格和缩进可以适当的添加

6.注释
单行注释: #注释文字 单行注释(对列进行说明) – 注释文件 多行注释: /*多行注释 */

表的概述

MySQl是一种关系型数据库,关系型数据库中最重要的概念就是表
将数据存储到表中,表存储在库中
一个数据库中是可以存在多个表,每个表都有字节名字,用来表示自己,并且具备唯一性

表具有一些特性,这些特性定义了表中如何存储数据,类是与java中"类"
表是由列组成,每一列我们可以称为字段,这个字段就是就是对应在类中 “属性”,所有的表都是由一个列或多个列组成
表中数据是按行存储的,每一行类似于Java中所创建的"对象"
ps:创建表的时候其实就是在创建表中的列即字段,向表中存到数据其实就是相当于创建了"对象"

MySQL的数据存储引擎

MySQL中的数据用各种不同的技术存在文件中,这个些技术中的每一种技术都使用不同存储机制,索引技巧,锁定的水平

并且最终提供不同的功能和能力

选择不同存储技术,可以得到不同存储效果

1.MyISAM:拥有较高的插入和查询速度,但是不支持事务和外键 

2.InnoDB:支持事务,支持外键,支持锁定行,性能较低 

ps:InnoDB存储引擎具备提交,回滚和崩溃恢复的事务能力,对不MyISAN处理能力会差一些,且会占用更过的磁盘空间 以保存数据和索引

MySQL中常用的类型

ps:

无论是那个中小数都是确定其存范围
DOUBLE(s,p) 小数类型, 可以存放整数, s代表的是范围,p代表的是精度
money double(5,2)整数和小数一共占5位 其中小数占2位999.99 最大值 最小值是-999.99

double 和float不够精准 可以使用decimal定点小数数据类型
FLOAT和DOUBLE --> float/Float和 double/Double

char(size)定长字符,0~255字节 ,size值N个字符,size大小决定了可以存多少个字符,若超过了当前字符范围那么会被截 取并发出警告

varchar(size) 变长字符,0~65535 这个65536的主是从MySQL5开始,之前是指支持到255,size据定了可以存多少个字 符,若超过了当前字符范围那么会被截取并发出警告

一般若存储大量字符串,比如文章,可以选用TEXT系列 在MySQL若存字符串需要使用 [’ ']单引号

数据库可以存放图形,.音频和视频,二进制对象(0-4GB) 就需要使用二进制类型
ps:若存Java中boolean存储到数据库中, 可以使用bit 或直接存0或1 非0 即为真 0代表false 1代表true

SQL分类

SQL --> 结构化查询语言.SQL是美国国家标准局(ANSI)确定为关系型数据库语言的美国标准,后来被国际化组织(ISO)采

用为关系数据库的国籍标准语言 各大数据库厂商都需要支持ISO这个SQL标准–> 普通话

各个数据库厂商在标准的基础上做出自己的扩展 --> 方言

DDL数据定义语言,用来定义数据库对象:库,表,列等等

DML数据操作语言,用来定义数据库记录

DQL数据查询语言,用来查询记录的

DCL数据库控制语言,用来定义数据安全和访问级别

ps:在mysql客户端中写语句 必须添加 [;] 分好

DDL操作

可以使用标准SQL create(创建),alter(修改),drop(删除) 操作数据库:
创建一个数据库 create database 数据库名; 查询当前哪些数据库 show databases;
删除数据库 drop database 数据库名; 查看当前数创建数据库的信息 show create database 数据库名; 若使用数据库 use 数据库名;
操作表:
create table 表名(
列名1 数据类型 [约束], 列名2 数据类型 [约束], .....
列名N 数据类型 [约束] );

在某些书写SQL语句中会出现使用两个[`db3`] 这是一个反引号, 这个反引号可以使用在表名列名上可以区分关键字和表名 和列名
#创建一张学生表
create table student(
id int,
name varchar(100),
age int
);
/*
表的约束(针对的某一列)
1.非空约束 [NOT NULL] -->当前内容为null2 2.设置列的默认值 [DEFAULT]
3.主键约束 PRIMARY KEY 非空且唯一 4.主键自增长:AUTO_INCREMENT
*/
#查看表的字段信息 desc student
#向表中增加一列 image
#alter table 表名 add 列名 数据类型; 
alter table student add image blob;

#修改name列,长度为60
#alter table 表名 modify 要修改的列名 数据类型; 
alter table student modify name varchar(60);

#删除image列,一次只能删除一列 #alter table 表名 drop 列名; 
alter table student drop image;

#将表名改为user
#rename table 原表名 to 新表名 rename table student To user;

#修改表的字符集
#alter table 表名 character set 字符集; alter table `user` character set gbk;

#查看表的创建细节
#show create table 表名; show create table `user`;

#修改列名字
#alter table 表名 change 原列名 新列名 数据类型;
alter table `user` change `name` useranme varchar(100);
#删除表(慎用!!!!删除之前一定要确认不用了在删!!!!!!) 
drop table `user`;

DML操作(重要)

DML操作是对表中的数据可进行增,删,该的操作,不要和DDL混淆了 insert(插入),update(更新),delete(删除) ps:在Mysql中写入字符串请使用单引号日期也是单引号 插入操作:
语法: insert into 表名(列名1,列名2....) values(列值1,列值2....) ps:列名与列值的类型,个数,顺序要 一 一 对应
MySQL允许插入null值
创建一张表:Worker
列 id,name,gender,birthday(date),money长度为是10,精度为2,context 插入3个值
#创建表
CREATE TABLE worker (
  id int(11),
  name varchar(100),
  gender varchar(10),
  birthday date,
  money double(10,2),
  context text
);

#向表中插入数据
#语法 insert into 表名(列名1,列名2....) values(列值1,列值2....)
insert into worker(id,name,gender,birthday,money,context) values(1,'zhangsan','man','1995- 5-10',10000,'good boy');
insert into worker(id,name,gender,birthday,money,context) values(2,'lisi','woman','1899-1- 10',10000000,'good girl');
insert into worker(id,name,gender,birthday,money,context) values(3,'wangwu','man','2019-5- 10',9992,'good boy');

修改操作:
语法: update 表名 set 列名 = 列值 .... where 条件
ps:不添加 where 条件会对正表中数据进行修改
#更新数据
#将所有员工的薪水都修改为5000元
#语法: update 表名 set 列名 = 列值 .... where 条件
update worker set money = 5000;

#将姓名为zhangsan的员工薪水修改为3000元 where后面即使修改条件 
update worker set money = 3000 where name='zhangsan';

#将姓名为lisi的员工薪水修改为2000元,性别修改为man
update worker set money = 2000,gender='man' where name = 'lisi';

#将性别为man的薪水添加100元
update worker set money = money+100 where gender='man';

删除操作:
语法: delete from 表名 where 条件 ps:若不写where就是正表中数据都删除
#删除(慎重!!!!! 删之前一定慎重!!!!一定要小心!!!!! 确定好了在删!!!!!) #语法: delete from 表名 where 条件
#删除表中姓名'zhangsan'的记录
delete from worker where name = 'zhangsan';

#删除表中所有数据
delete from worker;

#使用truncate删除表中记录(慎用!!!!!!!!!!)
truncate table worker;
/*
delete 删除表中数据,表的结构还在,删除后的数据还是可以找回来 truncate 删除相当于drop表,删除后的数据不会再回来,并且比delete块 */

DQL操作(重要)

DQL是数据库查询语言 数据库执行DQL语句时不会对数据进行改变,而是让数据库发送结果给客户端 查询返回的结果集是一张虚拟表
查询关键字 select
语法: select 列名 from 表名
ps 表名的后面可以添加 where-->Group by --> Having --> order by--> limit 信息语法:
select 要查询的类名(若使用*代表所有的类)
from 要查询的表名
where 查询条件
group by 对结果进行分组
having 分组之后的条件
order by 对结果进行排序
limit 结果结果(分页)
/*
导入了一张商品表


/*
id 商品id productName 商品名称 dir_id 商品分类编号 salePrice 零售价格 supplier 供应商 brand 品牌 cutoff 折扣 costPrice 成本价格 表中类对应的翻译
*/
#简单查询 查询出真个表的数据 可以使用* 输出的顺序是和表中存储数据的顺序是一致
#1.查询所有商品的信息
select * from product;

#2.查询指定列中存储的数据 id productName salePrice
select id,productName,salePrice from product; 

#列名或表名和当前MySQL中的关键字重名,可以使用反引号`id`对当前名称加以决定,写表名和列名的时候不要使用关键 #可以将上面的查询修改为
select `id`,`productName`,`salePrice` from `product`;


#3.查询商品中的分类编号
select dir_id from product;
#3.1去除多余重复数据 - distinct 使用方式是select关键的后面列名的前面 select distinct dir_id from product; #这样写只有两个类中的数据想都相同的时候,才会被认为是同一个
#select distinct dir_id,salePrice from product;
/*
实现数学运算符查询
+-*/
对数字类型即小数或整数可以使用运算符创建表达式 对date类型可以使用算数运算符有(+ -)
运算规则:
1.先算乘除再算加减
2.同级运算符顺序是从左向右执行 3.表达式中使用"小括号"可以强行修改运算符的优先级
*/
#4.查询所有货品id,名称和批发价格(批发价格=零售*折扣)
select id,productName,salePrice * cutoff from product; #5.查询所有货品id,名称和各进50个的成本价(成本= costPrice) select id,productName,costPrice*50 from product; #6.查询所有货品id,名称 各进50个,并且每个运费1元的成本
select id,productName,(costPrice+1)*50 from product;
/**
设置类的别名 as
1.改变列的标题
2.用于表示计算结果的含义
3.作为列的别名 4.如果别名中使用了特殊字符或者是强制大小写敏感,或有空时都需要添加单引号
select 原列名 as 别名 from 表名
别名可以据需在这个select语句中使用
*/
#6.查询所有货品id,名称 各进50个,并且每个运费1元的成本(别名版本)
select id,productName,(costPrice+1)*50 as totalCostPrice from product;
#6.1可以省略as
select id,productName,(costPrice+1)*50 totalCostPrice from product;
/*
过滤查询
select 列 from 表 where 限定的条件; 比较运算符
=等于 >大于 >=大于等于 <小于 <=小于等于 !=(<>)不等于小括号中是另外一种写法 */
#7.查询货品零售价格为119的所有货品信息
select * from product where salePrice = 119; #8.查询货品汇名称为罗技G9X的所有货品信息
select * from product where productName = '罗技G9X'; #9.查询货品名称不为罗技G9X的所有货品信息
select * from product where productName != '罗技G9X';
select * from product where productName <> '罗技G9X';-- <>也代表不等于 #10.查询分类编号不等于2的货品信息
 select * from product where dir_id != 2;
#11.查询id,货品名称,批发价格大于350的货品
select id,productName,salePrice*cutoff from product where salePrice*cutoff > 350; #别名
#select id,productName,salePrice*cutoff pf from product where pf > 350;
/**
这里出现的问题如下就是SQL语句执行顺序
1.from 确定从那一张表中去查询
2.where 从表中直接筛选出符合条件数据
3.select 从筛选之后的结果集中显示出某些列
ps:order by语句
4.order by 会对查询结果进行排序
MySQL查询的时候需要区分大小写
*/
/*
逻辑运算符
AND(&&) 表示所有条件都满足返回true
OR(||) 只要有一个条件满足返回true
NOT(!) 条件满足得到false 条件不满足得到true
*/
#12.查询id,货品名称,批发价格在300-400之间的货品
select id,productName,salePrice * cutoff from product where salePrice*cutoff >= 300 and salePrice*cutoff <= 400;

#13.查询id,货品名称,分类编号为2,4的所有货品
select id ,productName,dir_id from product where dir_id = 2 OR dir_id = 4; 
#14.查询id,货品名称,分类编号不为2的所有货品
#select id,productName,dir_id from product where dir_id != 2;
select id,productName,dir_id from product where NOT dir_id = 2; 

#15.查询id,货品名称,分类编号的货品条件零售价格大于等250或者 成本大于等于200
select id,productName,dir_id,salePrice,costPrice from product where salePrice >= 250 OR costPrice >= 200;
/*
优先级运算 NOT --> AND ---> OR 使用小括号会修改优先级
*/
/*
范围查询 between and
这个运算符使用在数字类型或日期类型中
格式:
select 列名 from 表名 where 列名 between 最小值 and 最大值;
从最小值开始到最大值结束的一个区间
*/

#16.查询id,货品名称,零售价格在300-400之间的货品
select id.,productName,salePrice from product where salePrice BETWEEN 300 AND 400; -- 相当于 >=300 && <=400
/*
使用in运算符
判断列的值是否在指定的集合中 --> OR
格式:
select * from 表名 where 列名 in(值1,值2); 只要在这个in后面小括号出现,值就满足
 
 */
#查询id,货品名称,分类编号为2,4的所有货品
select id ,productName,dir_id from product where dir_id in(2,4); #查询id,货品名称,分类编号不为2,4的所有货品
select id ,productName,dir_id from product where NOT(dir_id = 2 OR dir_id = 4); select id ,productName,dir_id from product where NOT dir_id in(2,4);
/*
查询空值is NUll
MySQL是支持存储数据为null
在MySQL为null不仅代表是字符串,代表所有没有数据的
格式:select * from 表名 where 列 is NUll
*/
#插入空值数据
insert into product(id,productName,dir_id,salePrice,supplier,brand,cutoff,costPrice) values(null,null,null,null,null,null,null,null);
select * from product;
#查询数据中是否有商品名是否有null
select * from product where productName IS NULL;
/**
模糊查询
使用like运算符执行统配查询,查询条件可以包文字,或字符或数字
通配符:
% 可以表零个或多个任意字符
_ 可以表示一个字符
*/
#查询id,货品名称,货品名称需要匹配 '罗技M'
# %在后 就是以XX开头 %在前 以XX结尾
select id,productName from product where productName like '罗技M%' #查询id,货品名称,分类编号,零售价格大于等于200的货品或名称匹配'罗技M1'后两个字符
select id,productName,dir_id,salePrice from product where salePrice >= 200 OR productName like '%罗技M1__';
/*结果排序 order by 子句进行查询进行排序
ASC 升序,默认 desc 降序
格式 select 列 from 表名 where 条件 order by 列 [ASC/DESC]....; 可以拍排多列 这里出现的问题如下就是SQL语句执行顺序
1.from 确定从那一张表中去查询
2.where 从表中直接筛选出符合条件数据
3.select 从筛选之后的结果集中显示出某些列
ps:order by语句
4.order by 会对查询结果进行排序
*/
#单例排序
#查询id,货品名称,分类编号,零售价格并且按零售健价格升降输出
#升序
select id ,productName,dir_id,salePrice from product order by salePrice ASC; #降序
select id ,productName,dir_id,salePrice from product order by salePrice DESC; #别名
select id ,productName,dir_id,salePrice as ps from product order by ps DESC;
#多列排序
 
#查询id,货品名称,分类编号,零售价格,先按分类编号排序,在按零售讲个排序
select id,productName,dir_id,salePrice from product ORDER BY dir_id DESC,salePrice ASC; #会先依据第一排序列为基础,然后在第二排序列继续排序
#查询M系列并按照批发价格排序加上别名
select id,productName, salePrice * cutoff as pf from product where productName like '%M%' ORDER BY pf ASC;

select语句 先执行from --> 接着执行where --> 在执行select -->最后执行order by
优先级运算 NOT --> AND —> OR 使用小括号会修改优先级