day02数据库的基本概念,MySQL数据库软件,SQL
数据库的基本概念
1.数据库的英文单词:DataBase 简称:DB
2.什么数据库:
用于存储和管理数据的仓库
3.数据库的特点:
1.持久化存储数据的.其实数据库就是一个文件系统
2.方便存储和管理数据
3.使用了统一的方式操作数据库 -- SQL
4.常见的数据库软件
参见<MySQL基础.pdf>
MySQL数据库软件
1.安装
参见<MySQL基础.pdf>
2.卸载
1.去mysql的安装目录找到my.ini文件
复制 datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
2.卸载MySQL
3.删除C:/ProgramData目录下的MySQL文件夹.
3.配置
MySQL服务启动
1.手动
2.cmd ->services.msc 打开服务的窗口
3.使用管理员打开cmd
net start mysql : 启动mysql的服务
net stop mysql: 关闭mysql服务
MySQL登陆
1.mysql -uroot -proot
2.mysql -hip -uroot -p连接目标的密码
3.mysql --host=ip --user=root --password=连接目标的密码
MySQL退出
1.exit
2.quit
MySQL目录结构
1.MySQL安装目录
配置文件 my.ini
2.MySQL数据目录
几个概念
数据库:文件夹
表:文件
数据:数据
SQL
1.什么是SQL?
Stryctured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则.每一种数据库操作的方式存在不一样的地方,称为”方言”
2.SQL通用语法
1.SQL 语句可以单行或者多行书写,以分号结尾.
2.可使用空格和缩进来增强语句的可读性.
3.MYSQL数据库的SQL语句不区分大小写,关键字建议使用大写.
4.3种注释
* 单行注释: -- 注释内容 或 # 注释内容(myql 特有)
* 多行注释: /* 注释 */
3.SQL分类
1)DDL(Data Definition Language) (DDL 数据定义语言) 如：建库，建表.关键字:create,drop,alter等
2)DML(Data Manipulation Language)(DML 数据操纵语言)，如：对表中的记录操作增删改.关键字:insert,delete,update等
3)DQL(Data Query Language)(DQL 数据查询语言)，如：对表中的查询操作.关键字:select,where等
4)DCL(Data Control Language)(DCL 数据控制语言)，如：对用户权限的设置.关键字:GRANT,REVOKE等
DDL:操作数据库.表
1.操作数据库:CRUD
1.C(Create):创建
创建数据库:
create database 数据库名称;
创建数据库,判断不存在,再创建:
create database if not exists 数据库名称;
创建数据库,并指定字符集
create database 数据库名称 character set 字符集名;
练习:创建db4数据库,判断是否存在,并指定字符集为gbk
create database if not exists db4 character set gbk;
2.R(Retrieve):查询
查询所有数据库的名称:
show databases;
查询某个数据库的字符集:查询某个数据库的创建语句
show create database 数据库名称;
3.U(Update):修改
修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
4.D(Delete):删除
删除数据库
drop database 数据库名称;
判断数据库存在,存在再删除
drop database if exists 数据库名称;
5.使用数据库
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;
操作表
1.C(Create):创建(在java一般有一个实体类就会与之对应创建一个表)
1.语法:
create table 表名(
列名1 数据类型1(长度),
列名2 数据类型2,
...
列名n 数据类型n
);
注意:最后一列,不需要加逗号(,)
数据库类型:
1.int:整数类型
age int,
2.double:小数类型
score double(5,2)
3.date日期,只包含年月日,yyyy-MM-dd
4.datetime:日期,包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5.timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
如果将来不给这个字段赋值,或赋值为null,则默认使用当前的系统时间,来自动赋值
6.varchar:字符串(可变长度的字符)
name varchar(20):姓名最大20个字符
zhangsan 8个字符 张三 2个字符
创建表
create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);
复制表:
create table 表名 like 被复制的表名;
2.R(Retrieve):查询
查询某个数据库中所有的表名称
show tables;
查询表结构
desc 表名;
3.U(Update):修改
--1.修改表名
alter table 原表名 rename to 新的表名;
2.修改表的字符集
alter table 表名 character set 字符集名称;
3.添加一列(字段)
alter table 表名 add 列名 数据类型(长度);
4.修改列名称 类型
alter table 表名 change 列名 新列名 新数据类型(长度);
5.改字段的类型(长度):
alter table 表名 modify 列名 新数据类型(长度); 扩容
6.删除列
alter table 表名 drop 列名;
4.D(Delete):删除
drop table 表名;
drop table if exists 表名;
客户端图形化工作:SQLYog
**DML:增删改表中数据(pdf第8节)
1.添加数据:
语法:
insert into 表名(列名1,列名2,...列名n)values(值1,值2,...值n);
insert into student (id,name,age,sex) values (1, '孙悟空', 20, '男');
注意:
1.列名和值要一一对应.
2.如果表名,不定义列名,则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3.除了数字类型,其他类型需要使用引号(单双都可以)引起来
2.删除数据:
语法:
delete from 表名[where 条件]
delete from student where id=1; --删除 id 为 1 的记录
-- 删除数据 删除 name 为 崔斯塔娜 并且 id = 1的记录
DELETE FROM student2 WHERE NAME='崔斯塔娜' AND id=1;
注意:
1.如果不加条件,则删除表中所有记录.
2如果要删除所有记录
1.delete from 表名; -- 不推荐使用.有多少条记录就会执行多少次删除操作.(日志中有记录,可恢复)
2.TRUNCATE TABLE 表名; -- 推荐使用,效率更高 先删除表,然后再创建一张一样的表.(属于DDL,面试笔试常考)
3.修改数据:
语法:
update 表名 set 列名1 = 值1,列名2 = 值2...[where 条件];
注意:
1.如果不加任何条件,则会将表中所有记录全部修改.(业务场景不多)
DQL:查询表中的记录
select*from 表名;
1.语法(详见下方day03)
2.基础查询
1.多个字段的查询
select 字段名1,字段名2...from 表名;
select name,age from student;
注意:
如果查询所有字段,则可以使用*来替代字段列表.
2.去除重复
distinct(有两个结果集字段一样才可以去重)
select distinct address from student;
3.计算列
一般可以使用四则运算计算一些列的值.(一般只会进行数值型的计算)
ifnull(表达式1,表达式2):null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null
如果该字段为null后的替换值.
4.起别名
as:as也可以省略
-- 给结果集的字段起别名
SELECT NAME 名字 ,address 地址 FROM student3;
SELECT NAME as 名字 ,address as 地址 FROM student3;
-- 给表起别名
SELECT t.name 名字 ,t.address 地址 FROM student3 t
3.条件查询
1.where字句后跟条件
2运算符
> 、< 、<= 、>= 、= 、<>
BETWEEN...AND:
在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾
IN( 集合) :
集合表示多个值，使用逗号分隔
IS NULL:
查询某一列为 NULL 的值，注：不能写=NULL
例-- 查询 math 分数大于 80 分的学生
select * from student3 where math>80;
-- 查询 english 分数小于或等于 80 分的学生
select * from student3 where english <=80;
-- 查询 age 等于 20 岁的学生
select * from student3 where age = 20;
-- 查询 age 不等于 20 岁的学生，注：不等于有两种写法
select * from student3 where age <> 20;
select * from student3 where age != 20;
LIKE ' 张%' :模糊查询
占位符:
_:单个任意字符
%多个任意字符
例:-- 查询姓马的学生
select * from student3 where name like '马%';
select * from student3 where name like '马';??????不行
33 / 33
-- 查询姓名中包含'德'字的学生
select * from student3 where name like '%德%';
-- 查询姓马，且姓名有两个字的学生
select * from student3 where name like '马_';
and 或 &&
or 或 ||
not 或 !
例:-- 查询 age 大于 35 且性别为男的学生(两个条件同时满足)
select * from student3 where age>35 and sex='男';
-- 查询 age 大于 35 或性别为男的学生(两个条件其中一个满足)
select * from student3 where age>35 or sex='男';
-- 查询 id 是 1 或 3 或 5 的学生
select * from student3 where id=1 or id=3 or id=5;
day03 DQL查询语句 约束 数据库的设计
DQL 查询语句
1排序查询
语法:order by
SELECT 字段名 FROM 表名 WHERE 字段= 值 ORDER BY 字段名 [ASC|DESC];
asc 升序,默认值
desc 降序
--日期类型的字段是可以排序的,离我们最近的日期大的,反之是小的.
例:select * from student3 t order by t.math desc;
2..聚合函数:将一列数据作为一个整体,进行纵向的计算
1.count:计算个数
1.一般选择非空的列:主键 --性能
2. count(*)
select count(id) from student;
2.max:计算最大值
select max(math) from student;
3.min:计算最小值
select min(math) from student;
4.sum:计算和
select sum(math) from student;
5.avg:计算平均值
select avg(math) from student;
注意:聚合函数的计算,排除null
解决方案:
1.选择不包含非空的列进行计算
2IFNULL函数
3.分组查询:
1.语法:group by 分组字段;
2.注意
1.分组之后查询的字段:分组字段,聚合函数
按照性别分组,分别查询男女同学的数学平均分
select sex,avg(math) from student group by sex;
按照性别分组,分别查询男女同学的数学平均分,人数
select sex,avg(math),count(id) from student group by sex;
按照性别分组,分别查询男女同学的数学平均分,人数 要求:数学分数低于70分的人不参加分组
select sex,avg(math),count(id) from student where math > 70 group by sex;
查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2;
按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
2.where和having的区别?
1.where在分组之前进行限定,如果不满足条件,则不参与分组.having在分组之后进行限定,如果不 满足结果,则不会被查询出来.
2.where后不可以跟聚合函数,having可以进行聚合函数的判断
4.分页查询
1.语法:limit开始的索引,每页查询的条数;
2.公式:开始的索引 = (当前的页码 -1)*每页显示的条数
--每页显示3条记录
select*from student limit 0,3; --第1页
select*from student limit 3,3; --第2页
select*from student limit 6,3; --第3页
3. limit 是一个MySQL"方言"
分组排序
-- select distinct ...from...where...group by...having...order by...limit
select sex,min(math) mins from student3 where math > 60 and name like ‘%马%’ group by sex having mins >60 order by mins desc limit 5;
约束
概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性.
分类:
1.主键约束:primary key
2.非空约束:not null
3.唯一约束:unique
4.外键约束:foreign key
非空约束:not null
1.创建表时添加约束
CREATE TABLE stu(
id INT;
NAME VARCHAR(20) NOT NULL --非空
2.创建表后,添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
3.删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束:unique,某一列的值不能重复
1.注意:
唯一约束可以有多个null值,其他不能重复
2.创建表时,添加唯一约束
CREATE TABLE stu(
id INT;
phone_number VARCHAR(20) unique --手机号
3.删除唯一约束
ALTER table stu drop index phone_number;
4.在表创建完后,添加唯一约束
ALTER table stu modify phone_number varchar(20) unique;
主键约束:primary key
1.注意:
1.含义:非空且唯一
2.一张表只能有一个字段为主键
3.主键就是表中记录的唯一标识
2.在创建表时,添加主键约束
create table stu(
id int primary key, --给id添加主键约束
name varchar(20)
);
3.删除主键(自增后不能删主键)
--错误 alter table stu modify id int;
alter table stu drop primary key;
4.创建完表后,添加主键
alter table stu modify id int primary key;
5.自动增长:
1.概念:如果某一列是数值类型的,使用auto_increment 可以完成值的自动增长
2.在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment, --给id添加主键约束(自增)
name varchar(20),not null, --非空
sex varchar(4) unique --唯一
);
3.删除自动增长
ALTER TABLE stu MODIFY id INT;
4.添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束:foreign key 让表与表产生关系,从而保证数据的正确性.(pdf5.5)
1.在创建表时,可以添加外键
语法:
create table 表名(
....
外键列
constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
例:constraint emp_dept_fk foreign key (dep_id) references department(id)
);
2.删除外键
alter table employee drop foreign key emp_dept_fk;
alter table 表名 drop foreign key 外键名称;
3.创建表之后,添加外键
alter table employee add constraint emp_dept_fk foreign key(dep_id) references department(id)
alter table 表名 add constraint外键名称 foreign key(外键字段名称) references 主表名称(主表列名称)
4.级联操作:在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
1.添加级联操作
语法:alter table 表名 add constraint 外键名称
foreign key(外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;
2.分类
1. 级联更新：ON UPDATE CASCADE
2. 级联删除：ON DELETE CASCADE
数据库的设计
1.多表之间的关系
1分类
1.一对一(了解):
如:人和身份证
2.一对多(多对一):
如:部门和员工
分析:一个部门有多个员工,一个员工只能对应一个部门
3.多对多
如:学生和课程
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2.实现关系
1.一对多
如:部门和员工
实现方式:在多的一方建立外键,指向一的一方的主键.
2.多对多
如:学生和课程
实现方式:多对多关系实现需要借助第三张中间表.中间表至少包含两个字段,这两个字段作为第 三章表的外键,分别指向两张表的主键.
3.一对一(了解)
如:人和身份证
实现方式:可以在任意一方添加唯一外键指向另一方的主键.
多表设计
1，一个实体对应创建一个表，不要掺杂到一起
2，每个表中要保证有主键字段
3，根据实体之间的三种关系所对应的原则去创建关联字段(至于是否建立成真正的外键，看需求)
2.数据库设计的范式
概念:设计数据库时,需要遵循的一些规范.要遵循后边的范式要求,必须先遵循前边的所有范式要求.
分类:
1.第一范式(1NF):每一列都是不可分割的原子数据项
2.第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
1.函数依赖:
2.完全函数依赖:
3.部分函数依赖:
4.传递函数依赖:
3.第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)
数据库的备份和还原
1.命令行:
语法:
备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
还原:
1.登陆数据库
2.创建数据库
3.使用数据库
4.执行文件.source 文件路径