一、数据库概述
1.什么是数据库
储和管理数据的仓库
分类:关系型数据库 和非关系型数据库(类似缓存)
2.数据库相关概念
- 数据库服务器:对外提供存取数据的服务
- 表:一类的信息 eg:电商中的 用户数据 订单数据
- 表记录:存储多个具体的数据 (表中可以有多个表记录
3.sql语言(操作关系型数据库的通用语言)
- 增删改查 数据库
- 增删改查 表
- 增删改查 表记录
- 操作存储过程/视图/索引等
4.连接mysql
1. 通过cmd窗口
mysql -uroot -proot123 (注意中间的空格)
-u 跟的数据库的账号名
-p 跟的数据库的密码
2.连接mysql并指定IP和端口号
mysql -uroot -proot123 -hxxxxxx -P3306
-h 跟连接服务器的端口号 可以省略 一般默认本机
-P 跟数据库端口号 默认3306
ipcongif 查询本机ip
二、操作数据库、操作表(SQL)
创建数据库: create database 库名 charset utf8;
create database if not exists 库名 charset utf8;
查看所有数据库: show databases;
删除数据库: drop database 库名;
drop database if exists 库名;
进入/选择数据库: use 库名
创建stu表:
use mydb1;
create table stu(
id int,
name varchar(50),
gender varchar(10),
birthday date,
score double
);
删除表: drop table if exists 表名;
查看表: show tables;
查看表结构: desc 表名;
三、新增、修改、删除表记录
新增记录:
insert into stu(id,name,gender,birthday,score) value(1,'张三','男','1985-9-1',86);
-- 插入时,给所有列赋值,列名可以省略
insert into stu value(1,'张三','男','1985-9-1',86);
修改记录:
update stu set score=score+10;
-- 如果要修改id大于1的所有学生的成绩
update stu set score=score+10 where id>1;
删除记录:
delete from stu; -- 删除stu表中的所有记录
delete from stu where id>1; -- 删除id大于1的所有学生信息
drop database 库名; -- 删除指定的库
drop table 表名; -- 删除指定的表
四、查询表记录(单表查询)
1、基础查询
select * | 列名列表 from 表名
2、where子句查询
select * | 列名列表 from 表名 where 列 运算符 值
3、模糊查询
select * | 列名列表 from 表名 where 列 like 值
值中要包含 % 或 _
%: 匹配0个/1个/多个任意字符
_: 匹配1个任意字符
4、多行函数查询
count( * | 列名 ): 用于统计查询结果有多少行
max( 列名 ): 求当前这一列中的最大值
min( 列名 ): 求当前这一列中的最小值
sum( 列名 ): 对当前这一列中的所有值求和
avg( 列名 ): 对当前这一列中的所有值求平均值
5、分组查询
select * | 列名列表 from 表名 [where子句] group by 列
6、排序查询
select * | 列名列表 from 表名 [where子句] [group by 列]
order by 列 ASC|DESC
ASC: 升序(默认值,可以省略)
DESC: 降序
7、分页查询
select * | 列名列表 from 表名 [where子句] [group by 列]
[order by 列 ASC|DESC]
limit (页码-1)*每页显示的记录数,每页显示的记录数
8、其它函数查询
curdate() -- 获取日期:年月日
curtime() -- 获取时间:时分秒
sysdate()/now() -- 获取日期+时间, 年月日 时分秒
year()/month()/day()/hour()/minute()/second() -- 从日期/时间中分别获取年、月、日、时、分、秒
concat(s1,s2,...sn) -- 用于拼接字符串,s1~sn都是字符串
concat_ws(opt,s1,s2,...sn) -- 用于拼接字符串,第一个参数是一个分隔符,在后面的s1~sn在拼接时,都需要在中间拼上这个分隔符
五、mysql的数据类型
1、数值类型
tinyint --> java中的byte
smallint --> java中的short
int --> java中的int
bigint --> java中的long
float --> java中的float
double --> java中的double
2、字符串类型
2.1.char类型: 定长字符串
char类型的存储范围是: 0~255个字符
name char(10): 声明一个name列,是字符串类型,长度不能超过10个字符
如果实际存储的数据长度小于10,剩余的空间会用空格补全,因此会造成空间浪费!
char类型在存储数据的效率略高于varchar类型因此,char类型适合存储长度固定的数据(比如:学生证号,身份证号,员工编号等)
这样既不会浪费空间,存储数据的效率还较高!
2.2.varchar类型: 不定长字符串
varchar类型的存储范围是: 0~65535个字节
iso8859-1编码: 1个字符对应1个字节
GBK编码: 1个字符对应2个字节
UTF-8编码: 1个字符对应3个字节
name varchar(10): 声明一个name列,是字符串类型,长度不能超过10个字符
如果实际存储的数据长度小于10,剩余的空间还可以留给被的数据使用,不会造成空间浪费!
varchar类型适合存储长度不固定的数据,不会浪费空间!
3、日期类型:
date:年月日
time:时分秒
datetime:年月日 时分秒 2020-10-1 11:10:09
timestamp: 时间戳,格式和datetime相同,但实际存储的是从1970-1-1到指定日期的毫秒值
六、mysql的字段约束
1、主键约束(primary key)
如果为一个列添加了主键约束,那么这列的值就必须得是唯一且不为空的!
主键的作用:作为一行表记录的唯一标识(类似于人的身份证号码)。
如何添加主键约束:创建表时
create table stu(
id int primary key,
...
);
主键自增策略: 如果一个列是主键,并且类型是数值,可以为主键添加自增策略。添加了自增策略后,以后再插入数据时,可以不为主键赋值,数据库会自己维护一个变量(AUTO_INCREMENT),该变量的值从1开始,每次用完后会自动加1,当插入数据时,如果没有给主键赋值,数据库就会从AUTO_INCREMENT变量上获取一个值,作为主键值插入到表中。
如何添加主键自增策略:
create table user(
id int primary key auto_increment,
...
);主键约束补充: 如果表已经建好了,如何为id添加主键约束:
例如:将mydb1.stu表中的id设置为主键自增:
alter table stu modify id int primary key auto_increment;
再插入数据,就可以不用给id赋值了
insert into stu values(null,'张三','male','1988-1-2',83);
insert into stu values(null,'李四','male','1989-3-4',85);
2、唯一约束(unique)
如果为一个列添加了唯一约束,那么这个列的值就必须是唯一的,但可以为空(null)
如何添加唯一约束:
create table user(
...
username varchar(50) unique not null,
...
);
主键: 唯一且不为空(主键可以唯一的标识一条表记录,类似于身份证号/学生编号/员工工号)
唯一+非空: 唯一且不为空
3、非空约束(not null)
如果为一个列添加了非空约束,那么这个列的值就不能是空的,但可以重复!
如何添加非空约束:
create table user(
...
password varchar(50) not null,
...
);
4、外键约束(foreign key)
七、表关系
1、一对多(多对一)
例如: 部门 对 员工
班级 对 学生
在保存一对多或者多对一的表关系时,是在多的一方添加列,来保存一的一方的主键,从而来保存两张表数据之间的对应关系
2、一对一
例如:班级 对 教室
在保存一对一的表关系时,可以在任意一方添加列,来保存另一方的主键,从而来保存两张表数据之间的对应关系3、多对多
例如:学生 对 教师
在保存多对多的表关系时,在任何一方添加列都不合适,此时可以再创建一张表专门用于保存两张表(学生表和教师表)的对应关系,再创建的这个表中,至少有两个列,分别用于保存两张表(学生表和教师表)的主键,并且将这两个列设置为联合主键。
八、多表查询
1、连接查询
查询部门和部门对应的员工信息
select * from dept,emp;
笛卡尔积查询: 指同时查询两张表,其中一张表的记录有m条,另一张表的记录有n条,笛卡尔积查询的结果就是 m*n条。
由于笛卡尔积查询的结果中存在大量错误的数据,因此我们不会直接使用这种查询!
可以在笛卡尔积查询的结果上,添加where子句,通过where子句将结果中的错误数据剔除,保留下的就是正确数据!
-- 连接查询
select * from dept,emp
where emp.dept_id=dept.id;
-- 内连接查询
select * from dept inner join emp
on emp.dept_id=dept.id;
2、左外连接查询和右外连接查询
左外连接查询:可以将左边表中的所有记录都查询出来,右边表只显示和左边相对应的数据,如果左边表中某些记录在右边没有对应的数据,右边显示为null即可。例如:
-- 查询部门表中的所有部门,以及部门对应的员工
select * from dept left join emp
on emp.dept_id=dept.id;右外连接查询:可以将右边表中的所有记录都查询出来,左边表只显示和右边相对应的数据,如果右边表中某些记录在左边没有对应的数据,可以显示为null。
-- 查询所有员工及员工对应的部门
select * from dept right join emp
on emp.dept_id=dept.id;
九.sql的执行顺序和书写顺序
1.sql语句的书写顺序:
select 列名|* --要确定查询列有哪些
from 表名 --要确定查询哪张表
where 条件 --通过筛选过滤,剔除不符合条件的记录
group by 分组的列 --通过条件对分组的数据进行过滤筛选
order by 排序的列 --指定哪一列要以什么方式排序
limit 起始位置,页面大小 --指定返回第几页的记录以及每页显示多少条记录
2.sql执行顺序
sql的执行顺序:
from 表名 --要确定查询哪张表
where 条件 --通过筛选过滤,剔除不符合条件的记录
select 列名|* --要确定查询列有哪些
group by 分组的列 --通过条件对分组的数据进行过滤筛选
order by 排序的列 --指定哪一列要以什么方式排序
limit 起始位置,页面大小 --指定返回第几页的记录以及每页显示多少条记录
注意
where跟having都可以用作过滤筛选
where子句必须要写在分组之前,并且where子句里面不能使用聚合函数
having就可以写在分组之后,并且having子句里面可以使用聚合函数