DataBase
为了方便数据的存储和管理,将数据按照规则将数据存储在硬盘上。
数据库管理系统(DBMS):
MySQL数据库:
是一个关系型数据库管理系统,最早由瑞典MySQLAB公司开发,目前被oracle收购
有开源版本,也有付费版本
具有快速,可靠,易于使用的特点
支持标准sql,支持多种操作系统,支持多种编程语言连接他自己
关系数据库
以数据表为单位,表与表之间存在关系
非关系型数据库 redis
缓存 key :value
sql()结构化查询语言
一种特殊的编程语言,用于对数据库管理操作和数据库的增删改查操作
是一种标准的数据库操作语言,一般的数据库管理系统都支持(一些语法上略有不同)
在sql语言中根据操作不同,又分为不同的sql语句:
ddl:数据(结构)定义语言,用于创建和修改数据库
常用的语句:create ,alter,drop,rename
创建数据库并设置编码格式
CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]
CREATE DATABASE borrow
DROP DATABASE borrow;
CREATE DATABASE IF NOT EXISTS borrow CHARSET utf8
CREATE TABLE student (sname CHAR(10),sage VARCHAR(2),ssex VARCHAR(2));
DROP TABLE student;
删除数据库
DROP DATABASE 数据库名 / [IF EXISTS数据库名];
DROP DATABASE borrow;
DROP TABLE student;
修改字符集
ALTER DATABASE 数据库名 CHARSET gbk;
RENAME TABLE student TO stu;
RENAME TABLE stu TO student;
ALTER TABLE student ADD majorid INT
数据库表的基本概念
1、数据表 表(table)是数据存储的最常见和最简单的形式,是构成关系型数据库的基本元素。 表的最简单形式是由行和列组成,分别都包含着数据。
每个表都有一个表头和表体,表头定 义表名和列名 .表中的行被看作是文件中的记录,表中的列被看作是这些记录的字段。
2、字段 字段是表里的一列,用于保存每条记录的特定信息。如客户订单表的字段包括“订单 ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。
数据表的一列包含了特定字段的全部信息。
3、记录 记录也被称为一行数据,是表里的一行数据。
设 计 表
● 对于具体的某一个表,在创建之前,需要确定表的下列特征:
● 表名(表信息)
● 表中的字段
● 字段的数据类型和长度
● 哪些约束
设计表(数据类型)
char(n) 长度为n的定长字符串,最大长度255个字符
varchar(n) 最大长度为n的可变长字符串
date 日期, 包含年月日
datetime 年月日 时分秒
整数
浮点
decimal 数据类型(M,D) M:精度,数据的总长度; D:标度,小数点后的长度
主键:
在一张表中代表唯一的一条记录,不能为空,不能重复
约束:
PRIMARY KEY 设置主键约束
NOT NULL 不能为空约束
UNIQUE 唯一性约束
检查约束 设置条件
外键约束 主键自动增长,设置为自动增长时,
只能为整数类型 AUTO_INCREMENT
默认值 DEFAULT default_value 字段注释: comment '注释'
CREATE TABLE student(
num INT,
sname VARCHAR(10),
gender CHAR(1),
birthday DATE,
phone CHAR(11),
adress VARCHAR(30),
height DECIMAL(3,2),
reg_time DATETIME
)
DROP TABLE student;
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10) NOT NULL COMMENT '学号',
gender CHAR(1) NOT NULL DEFAULT '男',
birthday DATE,
phone CHAR(11) NOT NULL UNIQUE,
adress VARCHAR(30),
height DECIMAL(3,2) CHECK(height<2.60),
reg_time DATETIME
)
RENAME TABLE student TO stu;
RENAME TABLE stu TO student;
DROP TABLE student;
CREATE TABLE student(
num INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10) NOT NULL COMMENT '学号',
gender CHAR(1) NOT NULL DEFAULT '男',
birthday DATE,
phone CHAR(11) NOT NULL UNIQUE,
adress VARCHAR(30),
height DECIMAL(3,2) CHECK(height<2.60),
reg_time DATETIME
)
CREATE TABLE stu LIKE student;
DML
● 数据操纵语言DML(Data Manipulation Language)
● 常用语句: insert,delete,update
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('李明','2004-09-14','15678941251','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('王五','2004-09-14','15678941252','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('李虎','2004-09-14','15678941253','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('二蛋','2004-09-14','15678941254','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('毛蛋','2004-09-14','15678941255','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('马达','2004-09-14','15678941256','汉中',1.60,NOW());
INSERT INTO student(sname,birthday,phone,adress,height,reg_time) VALUES('马达da','2004-09-14','15678941257','汉中',1.60,NOW());
修改数据 UPDATE 表名 SET 列名 = ‘新值’WHERE 条件
DELETE FROM 表名 WHERE 条件 TRUNCATE TABLE 表名;清空整张表
DQL-基础查询
● DQL(Data Query Language)数据查询语言查询是使用频率最高的一个操作, 可以从一个表中查询数据,也可以从多个表中查询数据。
● 基础查询
● 语法: select 查询列表 from 表名;
● 特点: 查询列表可以是:表中的字段、常量、表达式、函数 查询的结果是一个虚拟的表格
-- length():获取参数值的字节个数
SELECT sname,LENGTH(sname) FROM student
-- char_length()获取参数值的字符个数
SELECT sname,CHAR_LENGTH(sname) FROM student
-- concat(str1,str2,.....):拼接字符串
SELECT CONCAT(sname,':',gender)AS NAME FROM student
-- upper()/lower():将字符串变成大写/小写
SELECT UPPER(sname),LOWER(sname) FROM student
-- substring(str,pos,length):截取字符串 位置从1开始
SELECT SUBSTRING(sname,2,3) FROM student
-- instr(str,指定字符):返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR(sname,'明') FROM student
-- trim(str):去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
SELECT TRIM(sname) FROM student
SELECT TRIM('李' FROM sname) FROM student
-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT LPAD(sname,5,'a'), RPAD(sname,5,'a') FROM student
-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
-- replace(str,old,new):替换,替换所有的子串
SELECT REPLACE(sname,'马达','马达加大') FROM student
/*
逻辑处理
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null,如果为null,则返回指定的值,否则返回
原本的值
if函数:if else的 效果 if(条件,结果1,结果2)
*/
SELECT sname,
(CASE WHEN height>=1.80 THEN '高个子'
WHEN height>=1.60 THEN '正常身高'
ELSE '低个子' END
)AS height, gender FROM student
SELECT sname,IFNULL(adress,'暂未录入')AS adress FROM student
SELECT sname,IF(height>1.60,'正常','矮个子')AS height FROM student
/*
● 单行函数
日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间
curtime():返回当前时间,不包含日期
可以获取指定的部分,年、月、日、小时、分钟、秒
YEAR(日期列),MONTH(日期列),DAY(日期列) ,HOUR(日期列) ,MINUTE(日期列)
SECOND(日期列)
str_to_date(字符串格式日期,格式):将日期格式的字符转换成指定格式的日期
date_format(日期列,格式):将日期转换成字符串
datediff(big,small):返回两个日期相差的天数
*/
SELECT sname,NOW(),CURDATE(),CURTIME() FROM student
/*
● 分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:sum 求和、avg 平均值、max 最大值、min 最小值、count 计数
(非空)
1.sum,avg一般用于处理数值型max,min,count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count(*)用作统计行数
*/
SELECT SUM(height) FROM student;
SELECT AVG(height) FROM student;
SELECT MAX(birthday) FROM student;
SELECT MIN(birthday) FROM student;
SELECT COUNT(*) FROM student;
/*
● 条件查询
使用WHERE 子句,将不满足条件的行过滤掉,WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
*/
SELECT *FROM basketplayer WHERE height>=190 AND weight>100
SELECT * FROM basketplayer WHERE height>200 OR weight>90 OR num=1
-- in 判断某字段的值是否属于in列表中的某一项
SELECT *FROM basketplayer WHERE brithday NOT IN('1982-01-17','1980-09-12')
/*
● 条件查询
模糊查询
LIKE :是否匹配于一个模式 一般和通配符搭配使用,可以判断字符型数值
或数值型. 通配符: % 任意多个字符
between and 两者之间,包含临界值;
in 判断某字段的值是否属于in列表中的某一项
IS NULL(为空的)或 IS NOT NULL(不为空的)*/
SELECT *FROM student WHERE sname LIKE '李%'
SELECT *FROM student WHERE sname LIKE '%%'
SELECT * FROM student WHERE height BETWEEN 1.60 AND 1.95
SELECT *FROM student WHERE adress IS NULL
SELECT *FROM student WHERE adress IS NOT NULL
/*
1、UNION 的语法如下:
[SQL 语句 1]
UNION
[SQL 语句 2]
2、UNION ALL 的语法如下:
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时,mysql 会把结果集中重复的记录删掉,而使用union all ,
mysql 会把所有的记录返回,且效率高于union 。
*/
SELECT sname,gender,birthday FROM student WHERE height >1.85
UNION
SELECT sname,gender,birthday FROM student WHERE adress='汉中'
-- union会删掉重复的元素 ,union all 会将所有记录返回
SELECT sname,gender,birthday FROM student WHERE height >1.85
UNION ALL
SELECT sname,gender,birthday FROM student WHERE adress='汉中'
/*
• 排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序,desc代表的是降序,如果不写,默认是升序
order by子句中可以支持单个字段、多个字段
*/
SELECT *FROM student ORDER BY height
-- 如果不写,默认是升序, desc是降序
SELECT *FROM student ORDER BY height DESC
/*
• 数量限制
limit子句:对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
*/
SELECT * FROM student LIMIT 0,2;
SELECT * FROM student LIMIT 2,2;
SELECT * FROM student LIMIT 4,2;
/*
分组查询
语法:
select 分组函数,列(要求出现在group by的后面)
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
*/
SELECT COUNT(*),location FROM basketplayer GROUP BY location
SELECT SUM(height),location FROM basketplayer GROUP BY location
SELECT AVG(height),location FROM basketplayer GROUP BY location
-- 统计每年出生的人数
SELECT COUNT(*),DATE_FORMAT(brithday,'%Y') FROM basketplayer GROUP BY DATE_FORMAT(brithday,'%Y')
-- 分组统计名字数量 查询出哪些名字重复了
-- where 是对原始表中的数据进行过滤
-- HAVING 在group by 后面使用 为分组的数据添加过滤条件
SELECT COUNT(*) ,sname FROM student GROUP BY sname HAVING COUNT(*)>1