数据库
数据库的基本概念
- 数据库的英文单词 : DataBase 简称 : DB
什么数据库 ?
- 用于存储和管理数据的仓库
数据库的特点
-
持久化存储数据的。其实数据库就是一个文件系统
-
方便存储和管理数据
-
使用了统一的方式操作数据库 – SQL(适用于关系型数据库)
我们先了解几个数据库:
-
MYSQL: 开源免费的数据库,小型的数据库.已经被 Oracle 收购了.MySQL6.x 版本开始收费
-
Oracle: 收费的大型数据库,Oracle 公司的产品。Oracle 收购 SUN 公司,收购 MYSQL
-
DB2:IBM 公司的数据库产品,收费的。常应用在银行系统中
-
SQLServer: MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
-
SyBase:已经淡出历史舞台。但其所在公司提供了一个非常专业数据建模的工具:PowerDesigner
-
SQLite: 嵌入式的小型数据库,应用在手机端
常用数据库:MYSQL,Oracle
MySQL
在 web 应用中,使用的最多的就是 MySQL 数据库,原因如下:
-
①开源、免费
-
②功能足够强大,足以应付 web 应用开发(最高支持千万级别的并发访问)
MySQL本来为sun公司所属,被 Oracle 收购后,MySQL6.x 版本开始收费
MySQL的安装和卸载我不多说了,我说一下配置
配置
MySQL服务启动三种方式
①手动
②cmd–> services.msc 打开服务的窗口
③使用管理员打开cmd
-
net start mysql : 启动mysql的服务
-
net stop mysql:关闭mysql服务
MySQL登录
-
①mysql -uroot -p密码(账户一般都为root)(mysql -uroot -p回车后,在输入的密码就是不可见的,可以保护信息)
-
②mysql -hip地址 -uroot -p连接目标的密码
-
③mysql --host=ip地址 --user=root --password=连接目标的密码
MySQL退出
-
①exit
-
②quit
MySOL目录结构:
①MySQL安装目录:
- basedir=“我们安装时所选的地址”
②MySQL数据目录:
- datadir=“C:/ProgramData/MySQL/MySQL Server5.5/Data/”(一般为这个,看你下的是几版本了,我下的是5.5版本)(此地址为数据库,表,数据的所在地)
(以上的目录信息我们可以在我们安装时选的地址中的配置文件 “my.ini” 中看到)
我们要知道MySQL中的几个概念
-
数据库:其实就是个文件夹
-
表:其实是数据库中文件
-
数据:数据(其实是文件的内容)
它们的成分模型图大致如下
(计算机安装了MySQL数据库服务器软件后就可以称我们的计算机是一个MySQL服务器了,服务器指的是硬件Java软件)
SQL
我们要对数据库,表,数据进行操作的话,就需要知道SQL
什么是SQL?
-
SQL(Structured Query Language):结构化查询语言
-
其实就是定义了操作所有关系型数据库(Relational DBMS)的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”
SQL通用语法
-
SQL 语句可以单行或多行书写,以分号(;)结尾(分号是为了结束当前功能)(在图形化管理MySQL数据库的工具SQLyog中,一条命令不用“;”也行,但同时执行多条命令没“;”会报错)
-
可使用空格和缩进来增强语句的可读性
-
MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写
3种注释
-
单行注释:- - (空格)注释内容【空格不能省略】 或 #(空格可有可无)注内容(mysql 特有)(要带分号)
-
多行注释:/ *注释 * /(不带分号)
SQL语句可以分为四大类,对数据库,表,数据进行操作时用到的SQL语句是不一样的
SQL分类
-
①DDL(Data Definition Language)数据定义语言。用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter 等
-
②DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字:insert,delete,update 等
-
③DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字 : select,where 等
-
④ DCL(Data control Language)数据控制语言(了解)。用来定义数据库的访问权限和安全级别,及创建用户。关键字: GRANT、REVOKE 等
这四大类的使用场景结合上图模型图
DDL:操作数据库、表
提到操作,我们就需要知道CRUD
①操作数据库
C(Create):创建
-
创建数据库:
create database 数据库名称; -
创建数据库,判断不存在,再创建:
create database if not exists 数据库名称; -
创建数据库,并指定字符集:
create database 数据库名称 character set 字符集名;
练习 : 创建db4数据库,判断是否存在,并制定字符集为gbk
- create database if not exists db4 character set gbk;
R(Retrieve):查询
-
查询所有数据库的名称:
show databases; -
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
一般我们查询会出现下面四个数据库,但我们在数据目录只能发现下面三个数据库(文件夹)
而第一个:information_schema数据库是用来描述数据库MySQL里面的一些信息,比如说有哪些表,有哪些库的名字,但这里面存放的表不是真正的表,而是视图(以后会了解)
U(Update):修改
- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称
D(Delete):删除
-
删除数据库为:
drop database 数据库名称; -
判断数据库存在,存在再删除
-
drop database if exists 数据库名称;
使用数据库
-
查询当前正在使用的数据库名称:
select database(); -
使用数据库:(相当于进入文件夹)
use 数据库名称;
②操作表
C(Create):创建
语法:
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
…
列名n 数据类型n
);
注意:最后一列,不需要加逗号 (,)
数据库中的一些常见数据类型:
-
int:整数类型
如:age int -
double:小数类型
如:score double(5,2)【5为数字的位数,2为保留的小数位,比如:999.99,有5位数,保留了两位小数】 -
date:日期,只包含年月日(yyyy-MM-dd)
-
datetime:日期,包含年月日时分秒(yyyy-MM-dd HH:mm:ss)
-
timestamp:时间戳类型,包含年月日时分秒(yyyy-MM-ddHH:mm: ss)
如果将来不给这个时间戳类型的字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值 -
varchar:字符串
name varchar(20),姓名最大20个字符(如zhangsan:8个字符;张三:2个字符)
了解这些后,就可以建一个表了,比如,下面这个表
任务:建一个表
操作展示
CREATE TABLE student(
id INT,
NAME VARCHAR(35),
age INT,
score DOUBLE(4,1),
brith DATE,
date_change TIMESTAMP
);
详细的数据类型如下
复制表
- create table 表名 like 被复制的表名:
R(Retrieve):查询
-
查询某个数据库中所有的表名称
show tables;
展示:
-
查询某个表的字符集:查询某个表的创建语句
show create table 表名; -
查询表结构
desc 表名;
展示:
U(Update):修改
-
修改表名:
alter table 表名 rename to 新的表名;
rename table 原表名 to 新表名; -
修改表的字符集:
alter table 表名 character set 字符集名称; -
添加一列
alter table 表名 add 列名 数据类型; -
修改列名称类型
alter table 表名 modify 列名 新数据类型;
alter table 表名 change 列名 新列名 新数据类型; -
删除列
alter table 表名 drop 列名;
D(Delete):删除
- drop table 表名;
- drop table if exists 表名;
DML:增删改表中数据
添加数据:
语法:
- insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);
注意:
- 列名和值要一一对应
- 如果表名后,不定义列名,则默认给所有列添加值
- insert into 表名 values(值1,值2,…值n);除了数字类型,其他类型需要使用引号(单双都可以)引起来
删除数据:
语法:
- delete from 表名 where 条件
注意:
- 如果不加条件,则删除表中所有记录
- 如果要删除所有记录
delete from 表名;(不推荐使用。有多少条记录就会执行多少次删除操作)(适用于删除一行的情况)
truncate table 表名;(DDL命令)(推荐使用,效率更高 先删除表,然后再创建一张一样的表)(table可以省略)
修改数据:
语法:
- update 表名 set 列名1 = 值1,列名2 = 值2,…where条件;
注意:
- 如果不加任何条件,则要将表中所有记录全部修改
DQL:查询表中的记录
语法:
select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having分组之后的条件
order by 排序
limit 分页限定
给大家准备一些数据便于操作
CREATE TABLE student (
id INT, – 编号
NAME VARCHAR(20), – 姓名
age INT, – 年龄
sex VARCHAR(5), – 性别
address VARCHAR(100), – 地址
math INT, – 数学
english INT – 英语
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,‘马云’,55,‘男’,‘杭州’,66,78),
(2,‘马化腾’,45,‘女’,‘深圳’,98,87),(3,‘马景涛’,55,‘男’,‘香港’,56,77),
(4,‘柳岩’,20,‘女’,‘湖南’,76,65),(5,‘柳青’,20,‘男’,‘湖南’,86,NULL),
(6,‘刘德华’,57,‘男’,‘香港’,99,99),
(7,‘马德’,22,‘女’,‘香港’,99,99),(8,‘德玛西亚’,18,‘男’,‘南京’,56,65);
表大概如下
基础查询
-
多个字段的查询
select 字段名1,字段名2… from 表名;
注意:如果查询所有字段,则可以使用*来替代字段列表
select * from 表名; -
去除重复
distinct(如果选择多个字段的话,必须显示的数据完全一样才能去重)
SELECT DISTINCT 字段名1,字段名2 FROM 表名
如:SELECT DISTINCT math,english FROM student
- 计算列
一般可以使用四则运算计算一些列的值。 (一般只会进行数值型的计算)
ifnull (表达式1,表达式2):null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null
表达式2:如果该字段为null后的替换值
SELECT IFNULL(字段名,替换值) FROM 表名
如:SELECT IFNULL(english,0) FROM student
- 起别名
as:as也可以省略
SELECT 字段名1 AS 想改的名1,字段名2 AS 想改的名2 FROM 表名
如:SELECT math+IFNULL(english,0) AS 总分 FROM student
条件查询
where子句后跟条件
运算符
-
<,>,<=,>=,=,<>(不等于)
-
between…and
-
in(集合)
-
LIKE:模糊查询
占位符:
_:单个任意字符
%:多个任意字符 -
is null
-
and 或&&
-
or 或||
-
not 或 !
如:查询student表中年龄大于20岁的学生
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age>= 20;
查询年龄等于20岁
SELECT * FROM student WHERE age = 20;
查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
查询年龄大于等于20 小于等于30
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
查询年龄22岁,18岁,25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age=25;
SELECT * FROM student WHERE age IN (22,18,25);
查询英语成绩为null
SELECT * FROM student WHERE english = NULL; (不对的)
null值不能使用 = (!=) 判断
SELECT * FROM student WHERE english IS NULL;
查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
查询姓马的有哪些 ? like
SELECT * FROM student WHERE NAME LIKE ‘马%’;
查询姓名第二个字是化的人
SELECT * FROM student WHERE NAME LIKE “_化%”
查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE’_ _ _’
查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE ‘%德%’;
排序查询:order by
语法:order by 子句
- order by 排序字段1 排序方式1,排序字段2 排序方式2…
排序方式:
- ASC:升序,默认的
- DESC:降序
SELECT * FROM 表名 ORDER BY 字段名 排序方式
如:SELECT*FROM student ORDER BY math DESC
注意:
- 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件
聚合函数:将一列数据作为一个整体,进行纵向的计算(即查看有几行数据)
- count:计算个数
一般选择非空的列:主键(后面会说到主键)
count(*)、count(1)、count(列名)
SELECT COUNT( * ) FROM 表名
如:SELECT COUNT( * ) FROM student
【count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略为NULL的值。
count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略为NULL的值。
count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是指空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计】
- max:计算最大值
- min:计算最小值
SELECT MAX(字段名) FROM 表名
如:SELECT MAX(math) FROM student
SELECT MIN(IFNULL(english ,0))AS 英语 FROM student
- sum:计算和
SELECT SUM(字段名) FROM 表名
如: SELECT SUM(english) 英语总分 FROM student
- avg:计算平均值
SELECT AVG(字段名) FROM 表名
如:SELECT AVG(math) 数学平均分 FROM student
SELECT AVG(IFNULL(english,0)) 英语平均分 FROM student
注意: 聚合函数的计算,排除null值
解决方案:
-
①选择不包含非空的列进行计算
-
②IFNULL函数
分组查询:group by ,having
语法:
- group by 分组字段;
SELECT 分组字段,聚合函数 FROM 表名 GROUP BY 分组字段
如:SELECT sex FROM student GROUP BY sex
SELECT sex ,SUM(english) FROM student GROUP BY sex
注意:
- 分组之后查询的字段可以为:分组字段,聚合函数
SELECT 分组字段,聚合函数 FROM 表名 WHERE 条件 GROUP BY 字段分组 HAVING 聚合函数
如: SELECT sex 性别 ,AVG(IFNULL(english,0))平均分,COUNT(*)人数 FROM student WHERE IFNULL(english,0)>70 GROUP BY sex HAVING 人数>2
where 和 having 的区别?
-
where 在分组之前进行限定,如果不满足条件,则不参与分组
-
having在分组之后进行限定,如果不满足结果,则不会被查询出来
-
where 后不可以跟聚合函数,having可以进行聚合函数的判断
(我们可以这样理解,聚合函数是对结果集的计算,而where正处于确定结果集的过程)
分页查询:limit
- 语法:limit 开始的索引,每页查询的条数;
- 公式:开始的索引 = (当前的页码 - 1)* 每页显示的条数
–如每页显示3条记录
SELECT * FROM 表名 LIMIT 页码的索引,每页显示的条数;
如: SELECT * FROM student LIMIT 0,3; – 第1页
SELECT * FROM student LIMIT 3,3; – 第2页
SELECT * FROM student LIMIT 6,3; – 第3页
如果第一个参数是 0 可以省略写;
如:select * from student limit 5;
注意:limit 是一个MySQL"方言"
约束
概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性
分类
-
主键约束: primary key
-
非空约束:not null
-
唯一约束 :unique
-
外键约束:foreign key
非空约束: not null,某一列的值不能为null
- 创建表时添加约束
CREATE TABLE 表名(
字段名 数据类型 NOT NULL
);
如:CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL – name为非空
);
- 创建表完后,添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
如:ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
- 删除name的非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
如:ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束 : unique,某一列的值不能重复
注意:
- 唯一约束可以有NULL值但是只能有一条记录为null
在创建表时,添加唯一约束
CREATE TABLE 表名(
字段名 数据类型 UNIQUE
);
如:CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE – 手机号
);
删除唯一约束
ALTER TABLE 表名 DROP INDEX 字段名;
如:ALTER TABLE stu DROP INDEX phone number;
在表创建完后,添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIOUE;
如:ALTER TABLE stu MODIFY phone number VARCHAR(20) UNIOUE;
ALTER TABLE 表名 ADD UNIQUE(字段名);
如:ALTER TABLE stu1 ADD UNIQUE(score);
主键约束:primary key
注意:
-
含义:非空且唯一
-
一张表只能有一个字段为主键
-
主键就是表中记录的唯一标识
在创建表时,添加主键约束
create table 表名(
字段名 数据类型 primary key
);
如:create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
如:ALTER TABLE stu DROP PRIMARY KEY;
创建完表后,添加主键
ALTER TABLE 表名 MODIFY 字段名 INT PRIMARY KEY;
如:ALTER TABLE stu MODIFY Id INT PRIMARY KEY;
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2)
如:ALTER TABLE stu1 ADD PRIMARY KEY(score)
自动增长:
概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值的自动增长(当我们填入较大的值之后,会自增较大的值,当我们再填入较小的之后,不会自增较小的值,而是依然自增较大的值)
要求:在创建表时,添加主键约束,并且完成主键自增长
create table 表名(
字段名 数据类型 primary key auto_increment
);
如:create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
删除自动增长
ALTER TABLE 表名 MODIFY 字段名 数据类型;
如:ALTER TABLE stu MODIFY id INT;
添加自动增长
ALTER TABLE 表名 MODIFY 字段名 数据类型 AUTO INCREMENT;
如:ALTER TABLE stu MODIFY id INT AUTO INCREMENT;
注意:当我们在定义主键的字段上定义自增长后,要先删除自增长,才能删除主键
外键约束:foreign key,让表于表产生关系,从而保证数据的正确性
给大家一些数据,便于操作
– 创建部门表(id,dep_name,dep_location)
– 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
– 创建员工表(id,name,age,dep_id)
– 多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, – 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
– 添加 2 个部门
INSERT INTO department VALUES(NULL, ‘研发部’,‘广州’),(NULL, ‘销售部’, ‘深圳’);
– 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘李四’, 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘王五’, 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘老王’, 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘大王’, 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘小王’, 18, 2);
创建员工表(id,name,age,dep_id)
创建部门表(id,dep_name,dep_location)
在创建表时,可以添加外键
语法
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
创建表之后,添加外键(外键值可以为null,但不能为除了主键值以外的其他值)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称); REFERENCES 主表名称(主表字段名称);
级联操作
添加级联操作
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称);
REFERENCES 主表名称(主表字段名称) ON UPDATE CASCADE ON DELETE CASCADE;
分类:
-
级联更新:ON UPDATE CASCADE
-
级联删除:ON DELETE CASCADE