数据库简介
- IO流数据存储弊端
- 效率低
- 存取都麻烦
- 一般只能保存小量数据
- 只能保存文本数据
什么是DB
- Database:数据库
- 数据库本质上就是一个文件集合 数据库就是存储数据的仓库,本质上是一个文件系统,数据会按照特定的格式保存到文件中,用户可以对数据库中的数据进行增删改查操作
什么是DBMS
- Database Management System: 数据库管理系统
- DBMS就是管理数据库文件的软件
- 常见的DBMS有: MySQL、oracle、sqlserver、DB2、Sqlite
DBMS是指一种管理数据库的大型软件,用于创建、管理、维护数据库文件,对数据库文件进行统一的管理和控制,用户通过数据库管理系统
访问数据库中的数据
数据库软件的分类
- 关系型数据库: 经过数学理论验证,可以将现实生活中任意关系保存关系型数据库中,关系型数据库中以表为存储单位
- 非关系型数据库: 用来解决一些特定场景,比如解决缓存问题,解决高并发问题 Redis数据库(以键值对的形式保存数据)
主流关系型数据库介绍
- MySQL: 08年被Sun收购 09年Sun被Oracle收购,开源 ,由于被Oracle收购后性能大幅提升导致MySQL用户量大增,Oracle用户减少,公司计划把MySQL闭源,原MySQL程序员离开Oracle公司,创建了MariaDB数据库(Maria是公司老板的女儿名字) 市场排占有率第一
- Oracle: Oracle公司,最贵,性能最高,市场占有率排第二,闭源,拉里艾莉森32
- SQLServer:微软公司,闭源,市场占有率第三,主要应用在.net开发的网站中
- DB2:IBM公司产品,闭源
- sqlite:轻量级数据库 只有几十k,应用在嵌入式设备中(包括移动设备)
开源和闭源
- 开源:开放源代码 免费,盈利方式靠卖服务 用户多,有大牛程序员会无偿的维护和升级
- 闭源:源代码私有 盈利方式靠卖软件+服务 来钱快,会有大牛们攻击,但是公司内部会花钱雇人维护升级
安装数据库软件
- 端口号使用默认的3306
- 字符编码选择utf8
什么是SQL
- Structured(结构化) Query(查询) Language(语言):结构化查询语言
- 用于用户和数据库软件(DBMS)交流的语言,不管是增删还是改查都需要使用SQL语言和DBMS交流
- SQL是写在客户端中或Java代码中,发送给DBMS,由DBMS解析执行
数据库服务器
- 服务器是高配置的电脑
- 数据库服务器就是在电脑上安装了DBMS
- DBMS(mysql) 具备网络访问能力
如何连接MySQL数据库 Linux下打开终端 Windows里面打开命令行
- 在终端或命令行中输入 mysql -uroot -p 然后回车 ,有密码的写密码 如果没有密码的直接敲回车
- 退出命令 exit
SQL规范
- 以;结尾
- 关键字之间要有空格 可以有多个空格
- SQL语句中可以1个或多个换行
- 关键字不区分大小写
1. 数据库相关sql
- 查看所有数据库:
show databases;
- 创建数据库:
create database db1;
- 删除数据库:
drop database db1;
- 查看单个数据库详情:
show create database db1;
- 指定字符集:
create database db1 character set utf8/gbk;
- 使用数据库:
use db1;
2. 表相关sql
- 创建表:
create table t1(id int,name varchar(10));
- 查看所有表:
show tables;
- 删除表:
drop table t1;
- 查看单个表:
show create table t1;
- 创建表指定引擎和字符集:
create table t1(id int) engine=myisam/innodb charset=utf8/gbk;
- 查看表字段信息:
desc t1;
- 修改表:
修改表名 rename table t1 to t2;
- 修改表字符集和引擎:
alter table t1 engine=myisam/innodb charset=utf8/gbk;
- 添加表的字段:
alter table t1 add age int first/(after xxx)
- 删除表字段:
alter table t1 drop age;
- 修改表字段名称和类型:
alter table t1 change age abc int;
- 修改表的类型和位置:
alter table t1 modify age int first/(after xxx);
3. 数据相关sql
- 插入数据:
insert into t1 values(值1,值2),(值1,值2),(值1,值2)
insert into t1 (字段名1,字段名2) values (值1,值2),(值1,值2);
- 查询数据:
select * from t1; select name from t1;
select name,age from t1 where id<100;
- 修改数据:
update t1 set age=100 where id=10;
- 删除数据:
delete from t1 where id<10;
主键约束
什么是主键:表示表中数据唯一性的字段称之为主键(主键只有一个)
什么是约束: 约束就是创建表的时候给表中字段添加的限制条件
主键约束:添加了主键约束的字段,保存的数值必须唯一而且非空
- 格式:
create table t1(id int primary key,name varchar(10));
- 测试唯一: 以下代码会报错 主键值不能重复
insert into t1 values(1,'刘备');
insert into t1 values(1,'张飞');
- 测试非空:
insert into t1 values(null,'关羽');
主键+自增约束
- 格式:
create table t2(id int primary key auto_increment,name varchar(10));
- 测试自增:
insert into t2 values(null,'关羽');
insert into t2 values(null,'悟空');
insert into t2 values(10,'八戒');
insert into t2 values(null,'沙僧');
delete from t2 where id>5;
insert into t2 values(null,'二郎神');
- 自增的特点:
- 值为null时会自动赋值
- 以表中曾出现的最大值基础上+1;
- 删除数据自增值不减
- delete清空表 自增数值不变
- 如何让自增数值清零? 使用truncate关键字 ,相当于:
drop+create truncate table t2;
注释 comment
- 创建表的时候可以通过comment关键字对字段进行描述
- 格式: create table t3(id int primary key auto_increment comment ‘这个字段是主键’,ename varchar(10),sal int comment ‘这是工资’);
- 通过 show create table t3; 查看字段的注释
`和’的区别
- `的作用:是用来修饰表名和字段名的,可以省略
- '的作用:是用来修饰字符串的
数据冗余
由于表设计不够合理,出现大量的重复数据,称为数据冗余。可以通过拆分表的形式,把可能重复的数据保存到一张新的表中,在原表中只需要通过一个id建立关系即可。这种建立关系的字段称之为外键。
练习一: 保存商品的信息包括(商品id,商品名称,商品价格,库存,分类名称,上级分类)
- 创建商品表(item):商品id, 商品名称(name),商品价格(price),库存(num),分类id(category_id)
create table item(id int primary key auto_increment,name varchar(10),price int,num int,category_id int);
- 创建分类表(category):分类id ,分类名称(name),上级分类
create table category(id int primary key auto_increment,name varchar(10), parent_id int);
- 以上表中插入数据:
一、电器分类下的电视机分类下的康佳电视价格4480,库存38
二、第二条数据 鞋分类下的男鞋分类下的男运动鞋分类下的耐克运动鞋价格380,库存99
- 插入第一条数据:
insert into category values(null,'电器',null);
insert into category values(null,'电视机',1);
insert into item values(null,'康佳电视',4480,38,2);
- 插入第二条数据:
insert into category values(null,'鞋',null);
insert into category values(null,'男鞋',3);
insert into category values(null,'男运动鞋',4);
insert into item values(null,'耐克运动鞋',380,99,5);
练习二:请设计表保存以下数据:保存教学部下java教研部的老师信息:苍老师工资200 年龄18岁,然后再保存集团总部下销售部,销售A部的员资50,年龄28岁工李白工
- 创建部门表:
create table dept(id int primary key auto_increment,name varchar(10),parent_id int);
insert into dept values(null,'集团总部',null);
insert into dept values(null,'教学部',1);
insert into dept values(null,'Java教研部',2);
insert into dept values(null,'销售部',1);
insert into dept values(null,'销售A部',4);
- 创建员工表:
create table emp(id int primary key auto_increment,name varchar(10),sal int,age int,dept_id int);
insert into emp values(null,'苍老师',200,18,3),(null,'李白',50,28,5);
事务
什么是事务:事务是数据库中执行sql语句的最小工作单元,把多条sql语句放在事务中可以保证多条sql要么全部成功,要么全部失败。
- 测试事务:
- 创建person表::
create table person(id int primary key auto_increment,name varchar(10),money int);
- 插入数据:
insert into person values(null,'超人',200),(null,'钢铁侠',1000);
- 查看数据库的自动提交状态
show variables like '%autocommit%';
- 关闭自动提交 0 打开自动提交 1
set autocommit=0;
转账验证过程
- 把自动提交关掉:
set autocommit=0;
- 让超人+300 :
update person set money=500 where id=1;
- 此时打开另外一个终端
use db2;
查询数据select * from person;
(数据库中的数据此时并没有修改) - 回到第一个终端窗口,让钢铁侠
300 update person set money=700 where id=2;
(当前窗口查询数据时因为查询的是内存中的数据,所以数据变了,但是此时去第二个窗口查看数据库中的数据并没有发生改变) - 在第一个窗口中执行手动提交
commit;
此时会把内存中的多次SQL在内存中的执行结果 同时提交到数据库中(此时两个终端全部发生改变)
is null
- 查询奖金为null的所有员工信息
select * from emp where comm is null;
- 查询没有上级领导(mgr)的员工信息
select * from emp where mgr is null;
is not null
- 查询emp表中有奖金的员工信息
select * from emp where comm is not null;
别名
- 查询emp表中所有的姓名,把ename改成姓名
select ename as '姓名' from emp;
select ename '姓名' from emp;
select ename 姓名 from emp;
去重:distinct
- 查询所有员工从事的工作
select distinct job from emp;
比较运算符 >,<,>=,<=,=,!=和<>
emp
:comm奖金、job工作职位、sal工资、mgr领导编号、deptno部门编号t_item
:price单价、num库存
- 查询工资小于等于1600的所有员工姓名和工资
select ename,sal from emp where sal<=1600;
- 查询部门编号是20的所有员工姓名、职位job、部门编号
select ename,job,deptno from emp where deptno=20;
- 查询职位是manager的所有员工的姓名和职位
select ename,job from emp where job='manager';
- 查询部门不是10号部门的所有员工的姓名和部门编号 使用两种写法
select ename,deptno from emp where deptno!=10;
select ename,deptno from emp where deptno<>10;
- 查询t_item表中单价等于23的商品名称和价格
select title,price from t_item where price=23;
- 查询单价不等于8443的商品信息
select * from t_item where price!=8443;
and 和 or
and
和java中的&&效果一样or
和java中的||效果一样
- 查询不是10号部门并且工资小于3000的员工信息
select * from emp where deptno!=10 and sal<3000;
- 查询部门编号是30或者上级领导为7698的员工姓名,职位,上级领导编号和部门编号
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;
in
- 查询工资等于5000,1500,3000的员工信息
select * from emp where sal=5000 or sal=1500 or sal=3000;
select * from emp where sal in (5000,1500,3000);
between x and y 查询x和y之间的数值(包含x和y)
- 查询工资在2000和4000之间的员工信息
select * from emp where sal>=2000 and sal<=4000;
select * from emp where sal between 2000 and 4000;
like
- like:用于模糊查询
- _: 代表单个未知字符
- %:代表0个或多个未知字符
- 举例
- 包含字符a:%a%
- 以a开头:a%
- 以a结尾:%a
- 第二个字符是a:_a%
- 倒数第三个字符是a:%a__
- 第二个字符是a 最后一个字符是b:_a%b
like案例
- 查询标题中出现笔记本的商品标题
select title from t_item where title like '%笔记本%';
- 查询单价在50到200之间的得力商品(title中出现得力)
select * from t_item where price between 50 and 200 and title like '%得力%';
- 查询有图片的得力商品(image字段不为null)
select * from t_item
where image is not null and title like '%得力%';
- 查询标题中不包含得力的商品信息
select * from t_item
where title not like '%得力%';
- 查询有赠品的的商品信息(在sell_point字段中包含赠字)
select * from t_item
where sell_point like '%赠%';
排序
- order by写在where的后面
- order by后面写排序的字段名,可以写一个或多个
- 默认排序规则为升序,也可以指定规则: 升序(asc)降序(desc)
- 举例:
select name,money from person where money>5000 order by money asc/desc;
分页查询:limit
-
limit x,y
x代表跳过的条数 y代表查询的条数(每页的条数) -
limit,写在最后
-
举例1:查询第一页的10条数据
limit 0,10
-
举例2:查询第五页的8条数据
limit 32,8
-
举例3:查询第三页每页3条数据
limit (3-1)*3,3
-
查询所有商品的价格,价格升序,显示第二页,每页7条数据
select price from t_item order by price limit 7,7;
- 查询工资前三名的员工信息
select * from emp order by sal desc limit 0,3;
- 查询20部门中工资最高的员工信息
select * from emp where deptno=20 order by sal desc limit 0,1;
- 查询dell商品中价格最便宜的商品名称和价格
select title,price from t_item where title like '%dell%' order by price limit 0,1;
日期相关函数
select 'helloworld';
- 获取当前日期+时间 now()
select now();
- 获取当前的日期
select curdate();
- 获取当前时间
select curtime();
- 从年月日时分秒中提取年月日 和提取时分秒
select date(now());
select time(now());
- 从年月日时分秒中提取年,月,日,时,分,秒
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());