数据库
- DataBase,DB
- 持久化储存数据,是一个文件管理系统
- 方便储存和管理数据
- 使用类统一的方法操作数据库-----SQl
MySQL卸载
- 找到MySQl的安装目录
- 找到my.ini文件打开
- 复制出#Path to the database root,默认为datadir=“C:/ProgramData/MySQL/MySQL Server 5.5/Data/”
- 控制面板卸载MySQL
- 找到上面复制的文件夹,删除ProgramData/MySQL目录,卸载干净了。文件夹默认隐藏
MySQL启动
- 我的电脑、管理、服务和应用程序、服务,找到就可以操作了(这里也是设置软件开机自启动的地方)
- cmd、services.msc、打开服务窗口
- 以管理员身份运行cmd、net start mysql(打开mysql)、net stop mysql(关闭mysql)
- 连接mysql,dos下,mysql -u用户名 -p密码(输入-p后回车,会隐藏你输入的密码)
MySQL的登录
- dos下,mysql -u用户名 -p密码(输入-p后回车,会隐藏你输入的密码)
- dos下,mysql -hip -u用户名 -p密码(输入-p后回车,会隐藏你输入的密码)
- dos下,mysql --host=ip --user=用户名 --password=密码
MySQL退出
- exit
- quit
MySQL目录结构
- MySQL安装目录
- bin,二进制文件
- data,日志文件,数据文件
- include,C语言头信息
- lib,MySQL用的jar包
- share,MySQL的错误信息
SQL
- 结构化查询语言(Structured Query Language)简称SQL
- 定义了所有操作关系型数据的规则,每一种数据库的使用都会有不一样的地方,我们称为“方言”
SQL通用语法
-
SQl语句可与单行多行书写,以分号结束
-
MySQL数据库的SQL语句不区分大小写
-
注释
-
单行注释
-
– 注释内容
-
#_注释内容
-
-
多行注释
- /* 注释 */
-
SQL分类
- 数据查询语言DQL,数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE
- 数据操纵语言DML,对数据进行操作的
- 数据定义语言DDL,数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
索引、同义词、聚簇等 - 数据控制语言DCL,数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制
数据库操纵事务发生的时间及效果,对数据库实行监视等
DDL,操作数据库、表
-
操作数据库,CRUD
-
C(create),创建
-
创建数据库
create database 数据库名;
-
创建数据库,先判断它是否存在,不存在再创建
create database if not 数据库名;
-
创建数据库,指定字符集
create database 数据库名 character set 字符集名;
-
创建db4数据库,判断是否存在,指定字符集
create database if not exists db4 character set gbk;
-
-
R(retrieve),查询
-
查询所有数据库的名称
show databases;
-
查看某个数据库的字符集,查询某个数据库的创建语句,
show create database 数据库名
-
-
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,
列名3 数据类型3,
········
列名n 数据类型n
);
注意最后一列不要加逗号
-
数据库数据类型
-
int:整数
例:age int,
-
double:小数类型
score double(指定一共有几位,小数点后几位),
-
日期类型:data(只包含年月日,yyyy-MM-dd)
-
日期:datatime(包含年月日时分秒,yyyy-MM-dd HH:mm:ss)
-
日期:timestamp,时间戳类型(包含年月日时分秒,yyyy-MM-dd HH:mm:ss)
timestamp,如果不给这个字段赋值,或者赋值为null,则自动赋值为系统时间,
-
字符串类型:varchar
例:name varchar(指定长度,例20),表示姓名最大20个字符
-
-
例,创建一个学生表
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);
-
复制表
create table 新表 like 被复制表;
-
-
R(retrieve),查询
-
查询某数据库中所有表的名称
show tables;
-
查询表结构
desc 表名;
-
-
U(update),修改
-
修改表名
alter table 旧表名 rename to 新表名;
-
修改表的字符集
alter table 表名 character set 字符集名;
-
添加一列
alter table 表名 add 列名 数据类型;
-
修改列名、列类型
-
即改名字也改数据类型
alter table 表名 change 旧列名 新列名 新数据类型;
-
只休改数据类型
alter table 表名 modify 列名 新数据类型;
-
-
删除列
alter table 表名 drop 列名;
-
-
D(delete),删除
-
删除表
drop table 表名;
-
先判断是否存在,存在就删除
drop table if exists 表名;
-
-
DML,增删改表中数据
-
添加数据
-
语法
insert into 表名(列1,列2,列3····列n)只填休要修改的列 values(值1,值2,值3~~~值n);
-
注意事项
-
列名于值需要一一对应
-
如果表名后,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,值3~~~值n);
-
除了数字类型,其他类型都需要用引号(单双引号都可以)引起来
-
-
-
删除数据
-
语法
delete from 表名 [where 条件]
-
删除所有数据
-
delete from 表名;-- 不建议使用,有多少条就删除多少次,效率低
-
TRUNCATE TABLE 表名;-- 先删除表,再建一个一摸一样的表,推荐使用
-
-
注意
- 如果不加条件,则删除表中所有数据
-
-
修改数据
-
语法
update 表名 set 列名1=值1,列名2=值2,列名3=值3,······[where 条件]
-
注意事项
- 如果不加任何条件,会将表中一列数据全部修改
-
DQL,查询表中数据
-
select *from 表名;
-
数据
CREATE TABLE student3(
id INT, --编号
NAME VARCHAR(20), --姓名
age INT, --年龄
sex VARCHAR(5),--性别
address VARCHAR(100),--地址
math INT,--类文学
english INT --英语
);
INSERT INTO student3
(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 字段列表 from 表名列表 where 条件 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
-
多个字段查询
select 字段名1,字段名2,····字段名n from 表名;
如果要查询所有字段,可以用*代替字段列表
-
去处重复
distinct
-
计算列
一般可以用一些四则运算来计算一些值(一般只会有数值型运算)
ifnull(表达式1,表达式2),null参与的计算都为null
- 表达式1表示需要判断的字段
- 表达式2表示如果判断成功修改的值
-
起别名
AS、as也可以省略
-
-
条件查询
-
where子句+条件
-
运算符,<、>、>=、<=、=、<>
-
between ······and
-
in(集合)
-
like
-
占位符
_:单个任意字符
%:多个任意字符
-
-
is null
-
&& =and
-
||=or
-
!=not
-
-
-- 查询姓名和年龄
SELECT NAME,age FROM student;
SELECT address FROM student;
-- 去除重复的结果集
SELECT DISTINCT address FROM student;
SELECT NAME,address FROM student;-- 谁来在哪
-- 计算math和english之和,如果有null参与的运算,运算结果都为null
SELECT NAME,math,english,math+english FROM student;
SELECT NAME,math,english,math+IFNULL(english,0) FROM student;
-- 起别名
SELECT NAME,math,english,math+IFNULL(english,0) AS 总分 FROM student;
-- 不写AS可以用空字符代替
SELECT NAME 名字,math 数学,english 英语,math+IFNULL(english,0) 总分 FROM student;
-- 年龄大于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岁,99岁的人
SELECT * FROM student WHERE age=22 OR age=18 OR age=99;
SELECT * FROM student WHERE age IN(22,18,99);
-- 查询英语成绩为null
SELECT * FROM student WHERE english=NULL;-- 错误写法,null不能用=、!=判断
SELECT * FROM student WHERE english IS NULL;
-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
-- 模糊查询
-- 姓马的有哪些
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 排序字段1 排序方式1,排序字段2 排序方式2~~~~;
-
排序方式
升序,ASC,默认的可以不写
降序,DESC
-
注意
如果有多个排序条件,当前一个条件一样时,用当前条件排序
-
-
聚合函数
- 将一列数据作为一个整体进行纵向计算
-
count:计算个数
一般选非空列,主键
SELECT COUNT(NAME) FROM stu;
SELECT COUNT(*) FROM stu;
-
max:计算最大值
-
min:计算最小值
-
sun:计算和
-
avg:计算平均值
-
注意
聚合函数计算会排除null
解决方案
-
选择不包含空的列
-
使用ifnull 函数
-
-
分组查询
- where和having的区别
- where在分组之前限定,如果不满足条件,则不参与分组,having在分组之后限定,如果不满足结果,则不会被查询出来
- where后不可以跟聚合函数,having可以进行聚合函数判断
-
语法,group by 分组字段
SELECT sex,AVG(score) FROM stu GROUP BY sex;
SELECT sex,AVG(score),COUNT(id) FROM stu GROUP BY sex;
-
注意
- 分组之后查询的字段,分组字段、聚合函数
-
分页查询
-
语法:limit 开始的索引,每页查询的条数
SELECT * FROM stu LIMIT 0,3;-- 第一页
SELECT * FROM stu LIMIT 3,3;-- 第2页 -
公式,开始的索引=(当前页码-1)*每页显示条数
-
limit分页操作是一种mysql方言
-
SELECT * FROM stu;-- 显示表信息
DELETE FROM stu;-- 删除表
TRUNCATE TABLE stu like stu2;-- 先删除表,再建一个一摸一样的表
UPDATE stu SET age=17 WHERE id=1;-- 修改数据
-- 添加数据
INSERT INTO stu VALUES(1,"张无忌",17,60,NULL,NULL);
INSERT INTO stu VALUES(2,"张微机",19,70,NULL,NULL);
INSERT INTO stu VALUES(3,"张三丰",99,99.9,"1863-06-06",NULL);
SELECT * FROM stu ORDER BY score DESC; -- 按score升序排列
SELECT COUNT(NAME) FROM stu;-- 计算表有几行,排除null
SELECT COUNT(*) FROM stu;-- 计算表有几行,不排除null
SELECT MAX(age) FROM stu;-- 计算age最大值
SELECT MIN(age) FROM stu;-- 计算age最小值
SELECT SUM(age) FROM stu;-- 计算age和值
SELECT AVG(score) FROM stu;-- 计算age平均值
ALTER TABLE stu ADD sex VARCHAR(10);-- 添加一列
INSERT INTO stu(sex) VALUES("男");-- 把sex列,全设置为男
UPDATE stu SET sex="女" WHERE id=2;-- 把sex列,id=2设置为女
SELECT sex,AVG(score) FROM stu GROUP BY sex;-- 分别计算男女平均值
SELECT sex,AVG(score),COUNT(id) FROM stu GROUP BY sex;-- 分别计算男女平均值,并计算人数
-- 每页显示三条记录
-- 公式,开始的索引=(当前页码-1)*每页显示条数
SELECT * FROM stu LIMIT 0,3;-- 第一页
SELECT * FROM stu LIMIT 3,3;-- 第2页
约束
-
对表里面的数据进行限定,保证数据的正确性,有效性和完整性
-
分类
-
主键约束:primary key
-
非空约束:not null
-
唯一约束:unique
-
外键约束:foreign key
-
非空约束,not null
-
创建表时添加约束
CREATE TABLE stu1( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 );
-
创建表结束添加
SELECT * FROM stu1; -- 删除非空约束 ALTER TABLE stu1 MODIFY NAME VARCHAR(20); -- 创建表结束后,添加非空约束 ALTER TABLE stu1 MODIFY NAME VARCHAR(20) NOT NULL;
唯一约束,unique
- 创建表时添加唯一约束
- 删除唯一约束
- 在创建表后,添加唯一约束
-- 创建表添加非空约束
CREATE TABLE stu1(
id INT UNIQUE,-- 添加了唯一约束
NAME VARCHAR(20) NOT NULL -- name为非空
);
-- 唯一约束的值列,可以有多个null
ALTER TABLE stu1 DROP INDEX id;
-- 创建表后,添加唯一约束
ALTER TABLE stu1 MODIFY id INT UNIQUE;
主键约束,primary key
- 非空且唯一
- 一张表只能有一个字段为主键
- 主键就是表中记录的唯一标示
-
创建表时,添加主键约束
-
删除主键
-
创建表结束,添加主键
CREATE TABLE stu1( id INT PRIMARY KEY,-- 添加主键约束 NAME VARCHAR(20) ); -- 删除主键 ALTER TABLE stu1 DROP PRIMARY KEY; -- 创建完表,添加主键 ALTER TABLE stu1 MODIFY id INT PRIMARY KEY;
-
自动增长
-
如果某一列是数值类型,使用auto_increment,可以来完成自动增长
-
添加表时,完成主键约束,并且自增长
-
删除主键约束
-
添加主键约束,在表创建完成后
CREATE TABLE stu1( id INT PRIMARY KEY AUTO_INCREMENT,-- 添加主键约束,添加自动增长 NAME VARCHAR(20) ); -- 删除自动增长 ALTER TABLE stu1 MODIFY id INT; -- 添加自动增长 ALTER TABLE stu1 MODIFY id INT AUTO_INCREMENT;
- 自动增长至于上一条记录的值有关
-
-
外键约束,foreign key
- 让表与表之间存在关系,保证数据的正确性
-
创建表时添加外键
-
语法
create table 表名( 信息字段 外键列 constraint 外键名称 foreign key 外键列名 references 主表名称(主表列名称) );
-
-
删除外键·
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
在表创建好,后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表名称(主表列名称);
-- 有冗余
-- 解决方案,分成两张表
-- 一方,主表
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_depa_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加两个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
SELECT * FROM department;
-- 添加员工,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);
SELECT * FROM employee;
SELECT * FROM department;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_depa_fk;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depa_fk FOREIGN KEY (dep_id) REFERENCES department(id);
级联操作
-
添加级联
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE;
-
级联更新, ON UPDATE CASCADE
-
级联删除, ON DELETE CASCADE
-- 有冗余
-- 解决方案,分成两张表
-- 一方,主表
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_depa_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加两个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
SELECT * FROM department;
-- 添加员工,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);
SELECT * FROM employee;
SELECT * FROM department;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_depa_fk;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depa_fk FOREIGN KEY (dep_id) REFERENCES department(id);
DROP TABLE emp;
DROP TABLE department;
DROP TABLE employee;
-- 添加外键,设置级联连更新
ALTER TABLE employee ADD CONSTRAINT emp_depa_fk FOREIGN KEY (dep_id)
REFERENCES department(id) ON UPDATE CASCADE;
-- 级联删除
ALTER TABLE employee ADD CONSTRAINT emp_depa_fk FOREIGN KEY (dep_id)
REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
数据库的设计
多表之间的关系
-
一对一关系
人和身份证
-
一对多关系
部门和员工
-
多对多
学生和课程
-
实现以上关系
-
一对多
再多的一方建立外键,指向一的一方
-
- 多对多,需要使用第三张表
- 一对一,在任意一方添加外键,指向对方主键,或者合为一张表
- 旅游网站
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
/*创建用户表 tab_user
uid 用户主键,自增长
username 用户名长度 100,唯一,非空
password 密码长度 30,非空
name 真实姓名长度 100
birthday 生日
sex 性别,定长字符串 1
telephone 手机号,字符串 11
email 邮箱,字符串长度 100
*/
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
rid INT, -- 线路id
DATE DATETIME,
uid INT, -- 用户id
-- 创建复合主键
PRIMARY KEY(rid,uid), -- 联合主键
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);
范式
-
设计数据库时,需要遵守的规范
-
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
-
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
-
原表
- 第一范式(1NF):数据库表的每一列都是不可分割的原子数据项
- 数据冗余严重
- 数据添加有问题,会出现不合法
- 数据删除,会将系的数据也删除
-
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
-
函数依赖,A—>B,如果A属性、属性组的值,可以唯一确定B属性的值,则称B依赖于A
学号------》姓名 学号、课程名称---------》分数
-
完全依赖,A—>B,如果A是一个属性组,则B属性值的确定要依赖A属性组中的属性
学号、课程名称---------》分数
-
部分函数依赖,A—>B,如果A是一个属性组,则B属性值的确定要依赖A属性组中部分属性
学号、课程名称---------》姓名
-
传递函数依赖,A—>B,B—>C,如果通过A属性、属性组的值,可以确定唯一B属性的值,通过B属性、属性组的值,可以确定唯一C属性的值,则称C传递函数依赖于A
学号-----》系名 系名------》系主任
-
码,如果一张表中的一个属性或一个属性组,被其他所有属性依赖,则称这个属性为该表的码
该表中的码,学号、课程名称
-
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
数据库的备份还原
-
命令行
-
语法
备份,mysqldump -u用户名 -p密码 数据库名称 > 保存路径
还原
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件 source 文件路径
-
-
图形化页面