为什么学习数据库
实现数据持久化存储到本地,能够方便的进行管理和操作,(增删改查)
什么是数据库
为了方便数据的存储和管理,按照特点的规则将数据存储在硬盘上(持久保存)
DB:数据库(DataBase)
DBMS:数据库管理系统(DataBase Management System)-- 存储+管理
SQL:(Structure Query Language)结构化查询语言,用于对用于对数据库操作管理
数据库分类
关系型数据库:
存储单位是表
学生信息表,课程信息表,成绩信息表都存在着关系,这些关系组织在一起,形成了关系数据库。
关系型数据库是系统存储数据的根基。
SQL Server:微软产品 付费 (中型)
Oracle:大型数据库 付费
Mysql:现属于oracle 公司 有免费的 有付费的(中型)可安装在windows,linux
DB2:适合处理海量数据,收费。
非关系型数据库:
key:value MongdoDB,Redis
MySQL
语法规范:不区分大小写 ,建议关键字大写,表名、列名小写
ctrl+s 保存文件到指定位置
注释
单行注释
#后加注释
-- 两个横线加空格
多行注释
/*
注释文字
*/
SQL-DDL
结构化查询语言(Structured Query Language)简称SQL,是一种管理数据库的编程语言,
DDL:创建和修改数据库表结构的语言
#创建一个独立的数据库
create database mysql if not exists charset utf8
#删除数据库
DROP DATABASE IF EXISTS mysql
#修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
数据库一旦创建,名字就不能修改
创建数据表
--先选中数据库
--表名具有唯一性,表由一个或多个列组成,也称为字段,表是数据存储的基本单位。
一行数据就是一条记录
-- 创建学生表 学号,姓名,性别,生日,身高,电话,地址,注册时间
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR(5) NOT NULL COMMENT '姓名',
gender CHAR(1) DEFAULT '男' COMMENT '性别',
birthday DATE,
height DOUBLE(3,2) CHECK (height<3),
phone CHAR(11) NOT NULL UNIQUE,
address VARCHAR(30),
reg_time DATETIME
)
-- 修改表名
RENAME TABLE student TO students
-- 修改表名
RENAME TABLE student TO students
-- 删除表
DROP TABLE IF EXISTS students
-- 复制表结构 只有结构,没有数据
CREATE TABLE stu LIKE students
/*创建约束
PRIMARY KEY 设置主键约束
NOT NULL 不能为空约束
UNIQUE 唯一性约束
检查约束 设置条件
*/
数据类型
char(n) 长度为n的字符 用于存储定长的字符串 n=5,但是只存储了2个字符,但是仍然占有5个字符空间 varchar(n) 最大长度为n的字符 用于存储可变长度的字符串 n=5,只存储了2个字符,只占2个空间 date 日期 datetime 时间
int float double(建议使用)
text(用于存储长文本内容)
约束
主键约束:数据库的表中,必须有一列作为主键,以表示唯一的一条记录,不能为空,不能重复。 PRIMARY KEY 设置主键约束。
AUTO_INCREMENT增量 主键自动递增。
NOT NULL 设置不能为空。
UNIQUE 设置不能重复,可在多个列中添加。
CHECK (height<3) 检查约束 设置条件。
SQL-DML
DML:数据操纵语言
-- 插入语句 自动增长不需要维护
INSERT INTO student(NAME,gender,birthday,height,phone,address,reg_time)
VALUES('张三','男','2002-8-8',1.56,'1254564','山西阿斯顿',NOW())
INSERT INTO student SET NAME='刘某',gender='男',birthday='2006-4-5',phone='645132456'
INSERT INTO student(NAME,gender,birthday,height,phone,address,reg_time)
VALUES('张三','男','2002-8-8',1.56,'12545564','山西阿斯顿',NOW()),
('张三','男','2002-8-8',1.56,'125454564','山西阿斯顿',NOW()),
('张三','男','2002-8-8',1.56,'125244564','山西阿斯顿',NOW()),
('张三','男','2002-8-8',1.56,'12546456564','山西阿斯顿',NOW())
CREATE TABLE stu LIKE student
INSERT INTO stu (NAME,gender,phone) SELECT NAME,gender,phone FROM student
SELECT NAME,gender FROM student
SELECT NAME FROM stu
修改数据、删除数据
CREATE DATABASE nba CHARSET utf8
CREATE TABLE team (
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
NAME VARCHAR(4) NOT NULL COMMENT '球队名称',
location VARCHAR(15) NOT NULL COMMENT '所在地'
)
CREATE TABLE athlete(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
NAME VARCHAR(10) COMMENT '名字',
birthday DATE COMMENT '生日',
height INT CHECK (height<300) COMMENT '身高',
weight INT COMMENT '体重',
seat VARCHAR(2) COMMENT '位置'
)
INSERT INTO team(NAME,location)
VALUES('湖人','洛杉矶'),
('火箭','休斯顿'),
('凯尔特人','波士顿'),
('骑士','克利夫兰'),
('马刺','圣安东尼奥'),
('魔术','奥兰多')
INSERT INTO athlete(NAME,birthday,height,weight,seat)
VALUES('德怀恩-韦德','1982-1-17',193,96,''),
('勒布朗-詹姆斯','1984-12-30',203,198,'前锋'),
('科比-布莱恩特','1978-8-23',198,99,'后卫'),
('德克-诺维斯基','1978-6-19',213,113,''),
('克里斯-保罗','1985-5-6',182,79,'后卫'),
('托尼-帕克','1982-5-17',187,83,'后卫'),
('凯文-加内特','1981-7-14',212,113,''),
('保罗-佩尔斯','1977-10-13',200,106,'前锋'),
('迈克尔-乔丹','1963-2-17',198,98,'前锋'),
('德怀特-霍华德','1985-12-8',210,120,'中锋'),
('姚明','1980-9-12',229,140,'中锋'),
('沙奎尔-奥尼尔','1972-3-6',215,147,'中锋')
-- 此时name长队最大为 4字符
-- 修改所有的数据为 aaa 222
UPDATE team SET NAME='aaa',location='2222'
UPDATE team SET NAME='湖人11',location='洛杉矶1111' WHERE num=1
DELETE FROM team WHERE num=6
-- 清空所有数据
TRUNCATE TABLE team
-- 此时name长队最大为 4字符
-- 修改所有的数据为 aaa 222
UPDATE team SET NAME='aaa',location='2222'
UPDATE team SET NAME='湖人11',location='洛杉矶1111' WHERE num=1
DELETE FROM team WHERE num=6
-- 删除无任何条件 清空表数据
DELETE FROM team -- 删除数据都的做法,属于DML操作
-- 清空表
TRUNCATE TABLE team -- 清空表 主键会归零 效率高
SQL-DQL
elect
查询列表 from 表名 where ..............
SELECT * FROM team
SELECT * FROM athlete
-- 查询指定列
SELECT NAME,seat FROM athlete
-- 查询后卫在表中全部信息
SELECT * FROM athlete WHERE seat='后卫'
-- 算数运算 +只能作为运算符,不能拼接字符串
SELECT NAME,height+50 FROM athlete
-- 拼接
SELECT CONCAT(NAME,":",height) FROM athlete
-- 去除重复数据 所有列都相同才是重复数据
SELECT DISTINCT NAME FROM athlete
函数
/*
单行函数: 对每行数据进行处理,处理后数据有多少条还是多少条
分组函数: 求和,平均值、最大值、最小值、计数等,处理后变为 一个
*/
/*
length():获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,.....):拼接字符串
upper()/lower():将字符串变成大写/小写
substring(str,pos,length):截取字符串 位置从1开始
instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
replace(str,old,new):替换,替换所有的子串
*/
SELECT LENGTH(NAME) FROM athlete
SELECT CHAR_LENGTH(NAME) FROM athlete
-- char_length(name)此时自动生成的名字太长 重新命名
SELECT CHAR_LENGTH(NAME)AS AAAAA FROM athlete
-- 字符串变成大写/小写
INSERT INTO athlete(NAME,birthday,height,weight,seat)
VALUES('abcde','1982-1-17',193,96,'')
SELECT UPPER(NAME) FROM athlete
-- 截取字符串 位置从1开始
SELECT SUBSTRING(NAME,1,3) FROM athlete
-- instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR(NAME,'-') FROM athlete
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM(NAME) FROM athlete
SELECT TRIM("德"FROM NAME) FROM athlete-- 德怀恩-韦德->怀恩-韦
/*
lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
replace(str,old,new):替换,替换所有的子串
*/
SELECT LPAD(NAME,10,"AAA")FROM athlete
SELECT RPAD(NAME,10,"AAA")FROM athlete
SELECT REPLACE (seat,"后卫","中卫") FROM athlete
逻辑处理 case if
/*
逻辑处理
*/
UPDATE athlete SET state=1 WHERE num=1
SELECT num,
NAME,
(CASE WHEN state=0 THEN '未注册' ELSE '注册' END)AS state
FROM athlete
SELECT *FROM athlete
SELECT num,NAME,IFNULL(seat,'没位置') FROM athlete
SELECT num,
NAME,
IF( state=0 ,'未注册' ,'注册' )AS state
FROM athlete
日期函数
/*
日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期
YEAR(日期列),MONTH(日期列),DAY(日期列) ,
HOUR(日期列) ,MINUTE(日期列) SECOND(日期列)
*/
SELECT NOW() FROM athlete;
SELECT CURDATE() FROM athlete;
SELECT CURTIME() FROM athlete;
SELECT YEAR(birthday) FROM athlete
SELECT YEAR(birthday) FROM athlete WHERE YEAR(birthday)=1982
/*
str_to_date:将日期格式的字符转换成指定格式的日期
date_format:将日期转换成字符串
datediff(big,small):返回两个日期相差的天数
*/
SELECT STR_TO_DATE('2020-4-8','%Y-%m-%d') FROM athlete
SELECT DATE_FORMAT(birthday ,"%Y年%m月%d日") FROM athlete
SELECT DATEDIFF(CURTIME(),birthday) FROM athlete
简单查询
-- :sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
-- 以上分组都会忽略 null 值
-- 统计数量
SELECT COUNT(*)FROM athlete
-- 最值 可为其他数据类型
SELECT MAX(height) FROM athlete
SELECT MIN(height) FROM athlete
-- 只能 操作数值类型
SELECT SUM(height) FROM athlete
SELECT AVG(height) FROM athlete
/*
select 查询列 from 表 where 条件
*/
SELECT * FROM athlete WHERE seat="前锋"
SELECT * FROM athlete WHERE seat !="前锋"
SELECT * FROM athlete WHERE height >200 AND seat="前锋"
SELECT * FROM athlete WHERE height >200 OR seat="前锋"
SELECT * FROM athlete WHERE height =198 OR height =210
SELECT * FROM athlete WHERE height IN(200,220,198)
SELECT * FROM athlete WHERE height NOT IN(200,220,198)
SELECT *FROM athlete WHERE seat IS NULL
SELECT * FROM athlete WHERE height >=190 AND height <=210
SELECT * FROM athlete WHERE height BETWEEN 190 AND 210
-- _代表一个字符
SELECT * FROM athlete WHERE NAME LIKE '德%'
SELECT * FROM athlete WHERE NAME LIKE '%怀%'
SELECT * FROM athlete WHERE NAME LIKE '迈_____'
union,having
CREATE TABLE students(stu_num INT COMMENT '学号',
stu_name VARCHAR(10) COMMENT '姓名' ,
SUBJECT VARCHAR(10) COMMENT '科目',
stu_score INT CHECK (stu_score<=100)COMMENT '成绩')
INSERT INTO students (stu_num,stu_name,SUBJECT,stu_score)
VALUE(1,'张三','语文',70),
(1,'张三','数学',80),
(1,'张三','英语',66),
(2,'李四','语文',50),
(2,'李四','数学',75),
(2,'李四','英语',80),
(3,'王五','语文',77),
(3,'王五','数学',55),
(3,'王五','英语',88),
(3,'王五','物理',90)
-- union 合并多条sql结果,union会把重复的记录删除, union all 不会去除重复数据,
SELECT *FROM students WHERE SUBJECT = '语文'
UNION
SELECT *FROM students WHERE SUBJECT = '数学'
-- having 对分组后的数据在进行筛选
SELECT SUM(stu_score) AS a,SUBJECT FROM students GROUP BY SUBJECT
SELECT SUM(stu_score) AS a,SUBJECT FROM students GROUP BY SUBJECT HAVING a>400
分组,排序,case多条件
-- 排序, 有条件的排序要先过滤条件, stu_num 正常排序,stu_score 降序
-- 2.查询每个人的总成绩并按从高到低排名(要求显示字段:姓名,总成绩,学号)
SELECT * FROM students ORDER BY stu_num , stu_score DESC
SELECT stu_num,stu_name,SUM(stu_score)AS '总成绩'
FROM students
GROUP BY stu_num,stu_name
ORDER BY SUM(stu_score) DESC
-- 3.查询每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)
SELECT stu_num,stu_name,AVG(stu_score)AS '平均成绩'
FROM students
GROUP BY stu_num,stu_name
-- 4.查询各门课程的平均成绩(要求显示字段:课程,平均成绩)
SELECT SUBJECT,AVG(stu_score)AS '各科平均成绩'
FROM students
GROUP BY SUBJECT
-- 5. 查询学生数学成绩的排名,从高到低显示(要求显示字段:学号,姓名,成绩)
SELECT stu_name,stu_num, stu_score AS '数学成绩'
FROM students
WHERE SUBJECT='数学'
GROUP BY stu_name,stu_num,stu_score
ORDER BY stu_score DESC
-- 6.查询学生成绩,按照如下格式展示, 小于60分为C,60-79为B,80-100为A
SELECT stu_name,SUBJECT,(
CASE
WHEN stu_score>79 THEN 'A'
WHEN 80>stu_score AND stu_score>60 THEN 'B'
ELSE 'C' END)AS LEVEL
FROM students
分页
SELECT * FROM students
LIMIT 3 OFFSET 0;
SELECT * FROM students
LIMIT 3 OFFSET 3;
SELECT * FROM students
LIMIT 3 OFFSET 6;
SELECT * FROM students LIMIT 0,3
SELECT * FROM students LIMIT 3,3
SELECT * FROM students LIMIT 6,3
-- limit size offect Psize
-- 第一页 size=3 Psize= size*(1-1) 0
-- 第二页 size=3 Psize= size*(2-1) 3
-- 第三页 size=3 Psize= size*(3-1) 6