一、数据库概述
1、作用:为了数据持久化,实现方便的数据管理。
2、数据库为了方便数据存储和管理,将数据存储到电脑硬盘,通过数据库管理系统管理数据。
3、分类:(1)关系型数据库
以表为单位,表与表之间存在关系。
Oracle:功能强大,收费;
MySQL:快捷、可靠 开源、免费;属于Oracle公司。
SQL Server(微软):只能安装到Windows操作系统
DB2(IBM):适合处理海量数据,收费。
(2) 非关系型数据库
键=值
Redis
MongdoDB
二、Mysql数据库
1、MySql是一种关系型数据库管理系统。
-h :主机名
-P:端口号
-u:用户名
-p:密码
2、常用命令
show databases
show tables
show tables from
3、可视化工具
SQLyog、Navicat
4、语法规范
不区分大小写,建议关键字大写,表名、表名、列明小写
注释 # – /* */
5、 mysql 数据库软件的名字
sqlyog客户端功能
sql语言 结构化查询语言—脚本语言,不需要编译,直接通过某种解释器解释执行。
三、DDL
1、DDL–数据(表结构)定义语言,存储之前要先创建数据库、表。
2、常见的语句:
create 创建数据库并设置编码格式
alter 修改字符集
drop 删除数据库
rename 修改表名字
3、创建,删除数据库
– 创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8;
– 删除数据库
DROP DATABASE school_db;
– 修改字符集
ALTER DATABASE school_db CHARSET gbk;
4、数据库表的基本概念
1、数据表 :数据库基本存储单位,表的最简单形式是由行和列构成。
2、记录 :一行信息,一行数据是指一条完整的记录。
3、字段:存储哪个信息,字段是表里的一列,用于保存每条记录的特定信息。
5、数据库存储数据的特点
(1)将数据放在表中,表再放在库中
(2)一个数据库中可以有多个表,每个表都有一个名字,用来标识自己。表名唯一性。
(3)表具有一些特性,这些特性定义了数据在表中如何存储,类似java中的“类设计。
(4)表由列设计,我们也称列为字段。所有表都是由一个或多个列组成,每一列类似java中的“属性”。
(5)表中的数据是按行存储的,每一行类似于java中的“对象”。
6、设计表
一个表中存储一类信息
表的基本信息
表中的字段
字段的数据类型和长度
约束 数据约束
7、数据类型
char(n)长度为n的字符 定长 4 即使存储了一个字符也占4个长度
varchar(n)最大长度为n 可变 数据存储了四个字符,长度为4
date 年月日 日期
datetime 年月日 时分秒 时间
float(4,1) 三位整数,一位小数 ,总长度为4
double(5,2)三位整数, 两位小数 ,总长度为5
blob 是一个二进制大对象,可以容纳可变数量的数据,大文本,存储图片,新闻视频信息。
8、表中的约束
主键:primary key主键约束,not null不能为空,unique不能重复,一个表中只能有一个主键的约束 ,auto_increment主键自动增长,选填,类型是整数。
其中不能为空的约束 一个表中可以有多个
唯一的约束 一个表中可以有多个
检查约束 设置条件 例如 check(age>18)
默认值 default _value
字段注释 comment ‘注释’;
-- 创建数据库
CREATE DATABASE IF NOT EXISTS school_db CHARSET utf8;
-- 删除数据库
DROP DATABASE school_db;
-- 修改字符集
ALTER DATABASE school_db CHARSET gbk;
-- 创建表
-- 姓名 职工号 性别 生日 身高 电话 操作时间
CREATE TABLE t_teacher(
NAME VARCHAR(4),
num INT(8),
sex CHAR(1),
bairthday DATE,
height FLOAT(4,1),
phone CHAR(11),
operttime DATETIME
)
-- 删除表
DROP TABLE t_teacher;
CREATE TABLE t_teacher(
-- 将num设置为主键,每行自动加一
num INT PRIMARY KEY AUTO_INCREMENT,
-- name 是非空唯一的
NAME VARCHAR(4) NOT NULL UNIQUE,
-- sex 性别默认是男
sex CHAR DEFAULT '男',
-- 身高的位数在三位整数和一位小数,总共三位,且身高大于130.0
height FLOAT(4,1) CHECK(height>130.0),
phone CHAR(11),
operttime DATETIME,
birthday DATE
)
-- 删除表
DROP TABLE t_teacher;
-- 修改表名
RENAME TABLE teacher TO t_teacher;
-- 修改表结构
CREATE TABLE t_student LIKE t_teacher;
-- 添加删除主键约束
ALTER TABLE t_teacher ADD PRIMARY KEY (num);
ALTER TABLE t_teacher DROP PRIMARY KEY;
-- 设置主键自增 ,删除主键自增
ALTER TABLE t_teacher MODIFY num INT(8) AUTO_INCREMENT;
ALTER TABLE t_teacher MODIFY num INT(8);
-- 设置列不能为空 可以为空
ALTER TABLE t_teacher MODIFY NAME VARCHAR(4) NOT NULL;
ALTER TABLE t_teacher MODIFY NAME VARCHAR(4) NULL;
-- 添加唯一约束 删除唯一约束
ALTER TABLE t_teacher ADD CONSTRAINT uni_bairthday UNIQUE(bairthday);
ALTER TABLE t_teacher DROP INDEX uni_bairthday;
-- 添加 删除检查约束
ALTER TABLE t_teacher ADD CONSTRAINT height CHECK(height>140.0);
ALTER TABLE t_teacher DROP CHECK height;
-- 修改表结构 添加 删除 修改 列的名称 类型
ALTER TABLE t_teacher ADD weight FLOAT(5,2) AFTER height;
ALTER TABLE t_teacher DROP weight;
ALTER TABLE t_teacher CHANGE heightt height FLOAT(4,1);
ALTER TABLE t_teacher MODIFY phone INT ;
四、DML
1、DML–数据操纵语言
2、常用语句:insert 插入数据;delete删除数据;update修改数据。
-- 1 插入数据
INSERT INTO t_teacher(NAME,sex,bairthday,height,weight,phone,operttime)
VALUES('张三','女',NULL,180.2,NULL,NULL,NOW());
-- 2
INSERT INTO t_teacher SET NAME='王五',weight=290.3;
-- 3
INSERT INTO t_teacher(NAME,sex,height)
VALUES('王辉','女',178),('张华','男',198);
DROP TABLE t_student;
-- 4 t_student复制表t_teacher结构 ,复制t_teacher表数据
CREATE TABLE t_student LIKE t_teacher;
INSERT INTO t_student(NAME,sex,height)SELECT NAME,sex,height FROM t_teacher;
-- 修改
UPDATE t_teacher SET bairthday= '2020-09-09' WHERE num=3;
-- 删除
DELETE FROM t_teacher WHERE num=1;
-- 清空整张表
TRUNCATE TABLE t_teacher;
五、DQL-基础查询
DQL --数据查询语句,使用频率最高的语句,可以从一个表中查询数据,也可以从多个表中查询数据。
-- select 查询列表(结果) from 表名
-- select 查询列表(结果)from 表名 where 条件 排序 分组 行数限制 子查询
-- 多表 关联查询
-- 查询结果进行处理
-- 全部列查询
SELECT *FROM t_teacher;
-- 查询需要的列
SELECT NAME,sex FROM t_teacher;
-- 查询常量
SELECT 100 FROM t_teacher;
-- 查询表达式
SELECT 10*10 NAME FROM t_teacher;
-- 查询函数
SELECT virsion();
SELECT CHAR_LENGTH(NAME) FROM t_teacher;
-- 去除重复行
SELECT DISTINCT NAME,bairthday FROM t_teacher;
单行函数:如concat、length、ifnull等
分组函数:做统计使用,又称为统计函数、聚合函数、组函数
单行函数:
1、字符函数
-- 查询结果处理
-- 使用函数对查询结果进行处理
-- 函数:
-- 分类:
-- 单行函数 : 对查询的每行数据进行操作,查询5行,处理5行
-- 分组函数 聚合函数 :多行转为一行
-- 字符函数 length(列名) 字节个数 一个中文三个字节
SELECT num, LENGTH(NAME) FROM t_teacher;
-- char_length (列名) 字符个数
SELECT num,CHAR_LENGTH(NAME) FROM t_teacher;
-- concat(str1,str2...) 连接多个字符串 as定义别名
SELECT CONCAT(NAME,sex)AS NAME FROM t_teacher;
-- upper() lower() 将字符串变为大写 小写
SELECT UPPER(NAME) FROM t_teacher;
SELECT LOWER(NAME) FROM t_teacher;
-- substring(str,pos,length) 截取字符串 ,位置从1开始
SELECT SUBSTRING(NAME,1,2) FROM t_teacher;
-- instr(str,指定字符) 类似java中的index of 返回子串第一次出现的索引
SELECT INSTR(NAME,'d') FROM t_teacher;
-- trim(str) 列 去掉前后的空格
SELECT TRIM(NAME) FROM t_teacher;
SELECT TRIM('a'FROM NAME) FROM t_teacher;
-- lpad(列,指定最终长度,填充的子串) 填为指定的长度
SELECT LPAD(NAME,4,'e') FROM t_teacher;
SELECT RPAD(NAME,4,'f') FROM t_teacher;
-- replace (str,old,new)替换所有的子串
SELECT REPLACE(NAME,'d','t') FROM t_teacher;
2、逻辑函数
-- 逻辑函数
-- case when 条件 then 结果1 else 结果2 end; 可以有多个when
SELECT
NAME,
CASE WHEN weight>150 THEN '偏胖'
WHEN weight>100 AND weight<150 THEN '正常'
ELSE '偏瘦' END
FROM t_teacher
-- ifnull
SELECT IFNULL(bairthday,NOW()) FROM t_teacher;
-- if
SELECT IF(height>160,'高个子','低个子') FROM t_teacher ;
3、数学函数、日期函数
-- 四舍五入
SELECT ROUND(2.22);
-- 向上进位
SELECT CEIL(1.1);
-- 向下进位
SELECT FLOOR(1.9);
-- 截取三位
SELECT TRUNCATE(3,2224,3);
-- 取模
SELECT MOD(10,3);
-- 随机数
SELECT RAND();
-- 返回当前系统日期+时间
SELECT NOW();
-- 返回当前系统日期,不包含时间
SELECT CURDATE();
-- 返回当前时间,不包含日期
SELECT CURTIME();
-- 可以获取指定的部分,年、月、日、小时、分钟、秒
SELECT YEAR(operttime) FROM t_teacher;
SELECT MONTH(operttime) FROM t_teacher;
SELECT DAY(operttime) FROM t_teacher;
-- str_to_date 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('2020-2-5','%Y-%m-%d');
-- date_format 将日期转换成字符串
SELECT COUNT(*), DATE_FORMAT(operttime,'%Y-%m') bir FROM t_teacher GROUP BY bir;
-- dateiff 返回两个日期相差的天数
SELECT NAME, DATEDIFF(NOW(),operttime)FROM t_teacher;
1、 分组函数
-- 求和(sum) 平均值(avg) 最大值(max) 最小值(min) count(非空)计数
SELECT SUM(height) FROM t_teacher;
SELECT AVG(height) FROM t_teacher;
SELECT MAX(weight) ,sex FROM t_teacher GROUP BY sex;
SELECT MIN(weight) FROM t_teacher;
-- * 列 主键
SELECT COUNT(height) FROM t_teacher;
SELECT COUNT(*) FROM t_teacher;
SELECT COUNT(num) FROM t_teacher;
一、条件查询
使用where 子句,将不满足条件的行过滤掉,where子句紧随from子句。
比较:=,!,或<> ,>,<,>=,<=
逻辑运算:
and 与
or 或
not 非
-- 查询结果进行处理
SELECT 100,NAME FROM t_student
SELECT 10*10,NAME FROM t_student
SELECT height+5,NAME FROM t_student
SELECT VERSION()
SELECT CHAR_LENGTH(NAME) FROM t_student
-- * 所有列
SELECT * FROM t_student
-- 查询特定列(建议使用的)
SELECT num,NAME,sex FROM t_student
-- 去除重复数据, 针对查询出来的结果, 要求是所有列都是相同
SELECT DISTINCT * FROM t_student
SELECT DISTINCT NAME,sex FROM t_student
二、条件查询
模糊查询
like:是否匹配于一个模式,一般和通配符搭配使用,可以判断字符型数值或数值型。
通配符:% 任意多个字符,包含0个字符 _任意单个字符
between and 两者之间,包含临界值
in 判断某字段的值是否属于in列表中的某一项
is null (为空的) 或 is not null (不为空的)
1、UNION
UNION ALL
当使用union时,mysql会把结果集中重复的记录删掉,而使用union all,mysql会把所有的记录返回,且效率高于union。
2、排序
order by 子句排序,order by 排序列 ASC/DESC
3、数量限制
limit 子句:对查询的显示结果限制数目
SELECT*FROM TABLE LIMIT OFFSET ROWS
SELECT*FROM TABLE LIMIT 0,5
三、分组查询
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]