数据库概述
什么是数据库?
存储和管理数据的仓库
早期: 层次式数据库、网络型数据库
现在:关系型数据库、非关系型数据库
什么是关系数据库?
底层以二维表的形式保存数据的库就是关系型数据库
学生编号 | 姓名 | 年龄 |
---|---|---|
1001 | 重庆森林 | 35 |
1002 | 花样年华 | 36 |
常见关系型数据库
Sql Server:微软提供,收费,适用于一些中型或大型的项目中,在java中的使用占比不高(.NET中使用的较多)
Oracle:甲骨文公司提供,收费,适用于一些大型或者超大型的项目中,在java中的使用占比非常高
mysql:瑞典MySQLAB公司提供,免费开源,适用于一些小型或者中型的项目中,在Java中的使用占比较高(小巧轻量)
DB2:IBM公司提供,收费,在一些银行、金融等行业中使用较多。在java中的使用占比也不高。
Sqlite:迷你数据库,嵌入式设备中(安卓、苹果手机、pad)
数据库相关概念
1、什么是数据库服务器
数据库服务器就是一个软件(比如mysql软件)将数据库软件安装在电脑上,当前电脑就是 一个数据库服务器。就可以对外提供存取数据的服务
在一个数据库服务器中可以创建多个数据库(dataBases),每一个数据库都是一个单独的仓库。
2、什么是数据库
数据库就是存储和管理数据的仓库,通常情况下,一个网站的中的所有数据会存放在一个数据库中。
3、什么是表
一个数据库中可以创建多张表,每张表用于存储一类信息(数据库),例如:
jd.com中的用户数据 tb_user(表)
jd.com中的商品数据 tb_product(表)
jd.com中的订单数据 tb_order(表)
4、什么表记录·
一张表中可以包含多行表记录,每一行表记录用于存储某一个具体的数据
学生编号 | 姓名 | 年龄 |
---|---|---|
1001 | 重庆森林 | 35 |
1002 | 花样年华 | 36 |
什么是 SQL 语言
SQL是一门用于操作关系型数据库的通用的语言(使用SQL可以操作所有的关系型数据库)
使用SQL可以操作数据库、表、表记录
(1)创建数据库、删除数据库、修改数据库、查询数据库
(2)创建表、删除表、修改表、查询表
(3)新增表记录、删除表记录、修改表记录、查询表记录
使用SQL也可以操作存储过程/视图/索引等。
提示:SQL是一个标准通用的操作关系型数据库的语言(普通话),每个数据库厂商为了增强自己数据库的功能,都提供了支持自己数据库的语言,称之为数据库的方言。方言不通用!
连接 MySQL 服务器
以下操作在 dos 窗口下完成:
1、连接 MySQL 服务器:
mysql -uroot -p密码
-u:后面的root是用户名,这里使用的是超级管理员 root;
-p:(小写的p)后面的root是密码,这是在安装MySQL时就已经指定的密码;
2、连接 mysql 服务器并指定IP和端口:
mysql -uroot -proot -h127.0.0.1 -P3306;
-h:后面给出的127.0.0.1是服务器主机名或ip地址,可以省略的,默认连接本机;
-P:(大写的P)后面的3306是连接端口,可以省略,默认连接3306端口;
3、退出客户端命令
quit 或 exit 或 \q
注:(1) 在 cmd 中连接 MySQL 服务器之后,可以使用 #、/**/、-- 等符号添加注释
(2) 在 cmd中连接MySQL 服务器之后,在书写SQL语句时可以通过 \c 取消当前语句的执行
数据库及表操作
创建、删除、查看数据库
提示: (1)SQL语句对大小写不敏感。推荐关键字使用大写,自定义的名称(库名,表名,列名等)使用小写。
show databases; --查看当前数据库服务器中的所有库
create database mydb1; --创建 mydb1 库
(2)并且在自定义名称时,针对多个单词不要使用驼峰命名,而是使用下划线连接。(例如:tab_name,而不是 tabName )
01 查看 MySQL 服务器中所有数据库
show databases;
02 进入某一数据库(进入数据库后,才能操作库中的表和表记录)
use test;
03 查看当前数据库中的所有表
show tables;
04 删除mydb1库
drop database mydb1;
当删除的库不存在时,如何避免错误产生?
drop database if exists mydb1;
05 重新创建mydb1库,指定编码为 utf8
需要注意的是, MySQL 中不支持横杠 (-) ,所以 utf-8 要写成 utf8
create database mydb1 charset utf8;
如果存在mydb1,则先删除,再重建
drop database if exists mydb1;
create database mydb1;
如果不存在则创建mydb1
create database if not exists mydb1;
06 查看建库时的语句(并验证数据库库使用的编码)
语法:SHOW CREATE DATABASE 库名;
show create database mydb1;
创建、删除、查看表
07 进入mydb1库,删除 stu 学生表(如果存在)
语法:DROP TABLE 表名;
use mydb1;
drop table if exists stu;
08 创建 stu学生表(编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]),建表的语法:
CREATE TABLE 表名(
列名 数据类型,
列名 数据类型,
...
列名 数据类型
);
SQL语句:
create table stu(
id int,
name varchar(50),
gender varchar(10),
birthday date,
score double
);
09 查看 stu学生表结构
语法:desc 表名
desc stu;
新增、更新、删除表记录
10 往学生表( stu)中插入记录(数据)
语法:INSERT INTO 表名(列名1,列名2,列名3…) VALUES(值1,值2,值3…);
--如果是在 cmd 中执行插入记录的语句,先 set names gbk; 再插入记录!
set names gbk;
insert into stu(id,name,gender,birthday,score) values(1001,'重庆森林','male','1995-1-1',100);
insert into stu values(1002,'mary','female','1994-1-1',60);
insert into stu values(1003,'tom','female','2005-1-1',70);
insert into stu values(1,'男','male','1990-9-0',20);
提示:
(1)当为所有列插入值时,可以省写列名,但值的个数和顺序必须和声明时列的个数和顺序保持一致!
(2)SQL语句中的值为字符串或日期时,值的两边要加上单引号(有的版本的数据库双引号也可以,但推荐使用单引号)。
(3)(针对 cmd 窗口)在插入数据之前,先设置编码:set names gbk;
或者用一下命令连接 MySQL 服务器:
mysql --default-character-set=gbk -uroot -proot
等价于:
mysql -uroot -proot
set names gbk;
11 查询 stu 表所有学生的信息
语法:SELECT 列名 | * FROM 表名
select * from stu;
12 修改 stu 表中所有学生的成绩,加10分特长分
update stu set score=score+10;
13 修改 stu表中编号为1的学生成绩,将成绩改为83分
update stu set score=83 where id=1;
提示:where子句用于对记录进行筛选过滤,保留符合条件的记录,将不符合条件的记录剔除。
14 删除 stu表中所有的记录
删除记录语法: DELETE FROM 表名 [where条件]
delete from stu;
仅删除符合条件的
delete from stu where score>100;
MySQL 的数据类型
数值类型
tinyint:占用1个字节,相对于 java 中的 byte
smallint:占用2个字节,相对于java中的short
int:占用4个字节,相对于java中的int
bigint:占用8个字节,相对于java中的long
float:4字节单精度浮点类型,相对于java中的float
double:8字节双精度浮点类型,相对于java中的double
字符串类型
char(n)
定长字符串,最长255个字符。n表示字符数
所谓的定长,是当插入的值长度小于指定的长度时,剩余的空间会用空格填充。
varchar(n)
变长字符串,最长不超过65535个字节,n表示字符数
所谓的不定长,是当插入的值长度小于指定的长度时,剩余的空间可以留给别的数据使用
大文本(长文本)类型
最长65535个字节,一般超过255个字符列的会使用text
另,text也分多种,其中 bigtext 存储数据的长度约为4GB。
char(n)、varchar(n)、text都可以表示字符串类型,其区别在于:
char(n)在保存数据时,如果存入的字符串长度小于指定的长度n,后面会用空格补全,因此可能会造成空间浪费,但是char类型的存 储速度较varchar和text快
因此char类型适合存储长度固定的数据,这样就不会有空间浪费,存储效率比后两者还快!
varchar(n)保存数据时,按数据的真实长度存储,剩余的空间可以留给别的数据用,因此varchar不会浪费空间
因此varchar适合存储长度不固定的数据,这样不会有空间的浪费
text是大文本类型,一般文本长度超过255个字符,就会使用text类型存储
日期类型
date: 年月日
time: 时分秒
datetime: 年月日时分秒
timestamp:
时间戳(实际存储的是一个时间毫秒值),与datetime存储日期格式相同。两者的区别是:
timestamp 最大表示2038年,而datetime范围是1000~9999
timestamp在插入数据、修改数据时,可以自动更新成系统当前时间
MySQL 的字段约束
主键约束:
如果为一个列添加了主键约束,那么这个列就是主键,主键的特点是唯一且不能为空。
作为一个唯一标识,唯一的表示一条表记录(作用类似于人的身份证号,可以唯一的表示一个人一样。)
添加主键约束,例如将id设置为主键:
create table stu(
id int primary key,
...
);
如果主键是数值类型,为了方便插入主键(并且保证插入数据时,主键不会因为重复而报错),可以设置一个主键自增策略。
将id主键设置为自增
create table stu(
id int primary key auto_increment,
...
);
非空约束:
如果为一个列添加了非空约束,那么这个列的值就不能为空,但可以重复。
例如为password添加非空约束:
create table user(
password varchar(50) not null,
...
);
唯一约束:
如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的(即不能重复),但可以为空。例如为username添加唯一约束及非空约束:
create table user(
username varchar(50) unique not null,
...
);
将 stu 表的 id 设为 主键自增,name 设为唯一:
drop table if exists stu;
create table stu(
id int primary key auto_increment, -- 将id设置为主键(唯一且不能为空),并且设置为自增
name varchar(20) unique,
gender varchar(10),
birthday date,
score double
);
而后往 stu 表中插入记录:(id 值都设为 null ,系统自动会为 id 值从1 开始递增)
insert into stu(id,name,gender,birthday,score)
value(null,null,'male','1985-1-1',78);
insert into stu value(null,null,'male','1994-10-1',88);
insert into stu value(null,'花样年华','female','1995-10-1',68);
基础查询
准备数据:
-- -----------------------------------
-- 创建db10库、emp表并插入记录
-- -----------------------------------
-- 删除db10库(如果存在)
drop database if exists db10;
-- 重新创建db10库
create database db10 charset utf8;
-- 选择db10库
use db10;
-- 删除员工表(如果存在)
drop table if exists emp;
-- 创建员工表
create table emp(
id int primary key auto_increment, -- 员工编号
name varchar(50), -- 员工姓名
gender char(1), -- 员工性别
birthday date, -- 员工生日
dept varchar(50), -- 所属部门
job varchar(50), -- 所任职位
sal double, -- 薪资
bonus double -- 奖金
);
-- 往员工表中插入记录
insert into emp values(null,'aa','男','1995-10-25','市场部','销售人员','1800','300');
insert into emp values(null,'bb','男','1994-11-6','市场部','销售人员','2500','600');
insert into emp values(null,'cc','女','1996-09-14','市场部','销售人员','1400','300');
insert into emp values(null,'dd','男','1991-05-18','市场部','部门总监','4200','500');
insert into emp values(null,'ee','男','1993-11-18','市场部','销售人员','1600','500');
insert into emp values(null,'ff','女','1998-07-18','就业部','销售人员','3700','600');
insert into emp values(null,'ii','男','1995-08-18','就业部','部门总监','4850','400');
insert into emp values(null,'gg','男','1999-09-18','就业部','销售人员','3200','600');
insert into emp values(null,'kk','男','1990-10-18','就业部','销售人员','2700','400');
insert into emp values(null,'ll','男','1980-12-18',null,'CEO','5000',null);
SELECT 语句用于从表中选取数据。结果被存储在一个结果表中(称为结果集)
语法:SELECT 列名称 | * FROM 表名
提示:(1) *(星号)为通配符,表示查询所有列。
(2)但使用 *(星号)有时会把不必要的列也查出来了,并且效率不如直接指定列名
15 查询 emp表中的所有员工,显示姓名,薪资,奖金
select name,sal,bonus from emp;
16 查询emp表中的所有部门和职位
select dept,job from emp;
如果查询的结果中,存在大量重复的记录 使用DISTINCT 剔除重复的记录
select distinct dept,job from emp;
Where 子句查询
WHERE子句查询语法:SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值WHERE子句后面跟的是条件,条件可以有多个,多个条件之间用连接词(or | and)进行连接
下面的运算符可在 WHERE 子句中使用:
17 查询 emp 表中薪资大于3000的所有员工,显示员工姓名、薪资
select name,sal from emp where sal>3000;
18 查询 emp 表中总薪资(薪资+奖金)大于3500的所有员工,显示员工姓名、总薪资
select name,sal+bonus from emp where sal+bonus>3500;
ifnull(列, 值)函数: 判断指定的列是否包含null值,如果有null值,用第二个值替换null值
select name,ifnull(sal,0)+ifnull(bonus,0) from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;
注意查看上面查询结果中的表头,如何将表头中的 sal+bonus 修改为 “总薪资”
使用as可以为表头指定别名
select name as 姓名,ifnull(sal,0)+ifnull(bonus,0) as 总薪资 from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;
另外as可以省略
select name 姓名,ifnull(sal,0)+ifnull(bonus,0) 总薪资 from emp where ifnull(sal,0)+ifnull(bonus,0)>3500;
19 查询 emp表中薪资在3000和4500之间的员工,显示员工姓名和薪资
select name,sal from emp where sal >=3000 and sal <=4500;
提示: between…and… 在…之间
select name,sal from emp where sal between 3000 and 4500;
20 查询 emp表中薪资为 1400、1600、1800的员工,显示员工姓名和薪资
select name,sal from emp where sal=1400 or sal=1600 or sal=1800;
或者
select name,sal from emp where sal in(1400,1600,1800);
21 查询薪资不为1400、1600、1800的员工
方式一:select *from emp where not(sal=1400 or sal =1600 or sal=1800);
方式二:select *from emp where sal not in(1400,1600,1800);
方式三:select *from emp where sal!=1400 and sal!=1600 and sal!=1800;
22 查询 emp表中薪资大于4000和薪资小于2000的员工,显示员工姓名、薪资。
select name,sal from emp where sal>4000 or sal <2000;
23 查询emp表中薪资大于3000并且奖金小于600的员工,显示员工姓名、薪资、奖金
select name,sal,bonus from emp where sal>3000 and bonus <600;
处理null值
24 查询没有部门的员工(即部门列为null值)
select *from emp where dept is null;
思考:如何查询有部门的员工(即部门列不为null值)
select *from emp where dept is not null;
select *from emp where not(dept is null);