1.数据库概述 数据库概念:用于存储和管理数据的仓库 数据库特点: 1.持久化存储数据 2.方便存储和管理数据 3.使用了统一的方式(SQL)操作数据库 2.MySQL数据库 数据库的打开和关闭 net start mysql net stop mysql 数据库的登录和退出 mysql -uroot -proot 登录 mysql --host=ip地址 -uroot -p目标密码 Exit quit 退出 数据库的软件和数据库、数据表、表记录的关系 1.现有数据库软件 2.数据库的软件上,可以创建多个数据库(文件夹) 3.数据库中可以创建多个数据表(文件) 4.数据表中有表记录(数据) 3.SQL指令(重点) 3.1 Sql概述 Sql :Structured Query Language:结构化查询语言 就是一种操作所有关系型数据库的规则 Sql的通用语法: 单行注释:-- 注释内容和 #注释内容 多行注释:/* 注释内容*/ 3.2 sql 常见的分类:常见分类有4种 DDL:数据库定义语言 作用:给创建数据库和数据表以及修改数据库和数据表的 DCL: 数据库控制语言 作用:给数据库和数据表进行授权操作 DML: 数据库操作语言 作用:对表中的记录(数据)进行添加、修改、删除操作 DQL:数据库查询语言 作用:对表中的记录(数据)进行查询操作 3.3 sql操作数据库和表 使用DDL创建、修改数据库和数据表(CRUD) Create :创建 Retrieve :查询 Update:修改 Delete:删除 查询所有数据库的名称 Show databases; 查看某个数据库中的字符集和创建语法 Show Create database 数据库名称; 判断数据库是否存在,不存在则创建 Create database if not exists 数据库名称; 创建指定的字符集的数据库 Create database 数据库名称 character set 字符集名称; 判断数据库是否存在,不存在则创建,并指定数据库的字符集 Create database if not exists 数据库名称 character set 字符集名称; 修改指定数据库的字符集: Alter database 数据库名称 character set 字符集名称; 删除数据库 Drop database 数据库名称; 在删除数据库之前先判断其是否存在,存在则删除 Drop database if exists 数据库名称; 使用数据库 查询当前正在使用的数据库: Select database(); Use 数据库名称;使用数据库 操作表 查询指定数据库中的所有表的名称 Show tables; 查询表结构 Desc 表名称; 创建表 Create table 表名(列1 列1数据类型,列2 列2数据类型, ..列n 列n数据类型); Int 整数类型 eg: age int, Double 小数类型 eg : score double(5,2); 5代表该数共有5位,小数点后边有2位 Date 日期类型 只包含年月日 yyyy-MM-dd Datetime 日期类型 包含年月日时分秒 yyyy-MM-dd HH-mm-ss Timestamp 时间撮类型 : 包含年月日时分秒 如果不给该字段赋值,那么将使用当前系统时间自动赋值 Varchar 字符串类型 eg: name varchar(32) :括号中的数字代表字符串的最大长度是32 创建表 Create table 表名( Age int, Name varchar(20), Score double(5,2), Birthday date, Insert_time timestamp ); 删除表 Drop table 表名; 删除之前先判断表是否存在,存在则删除 Drop table if exists 表名; 复制表 Create table 表名 like 被复制的表名 修改表名 Alter table 表名 rename to 新的表名; 修改表的字符集 Alter table 表名 character set 字符集名称; 表中添加一列: Alter table 表名 add 列名 数据类型; 修改列名和数据类型 Alter table 表名 change 旧列名 新列名 数据类型 Alter table 表名 modify 列名 新数据类型 删除列 Alter table 表名 drop 列名; DML 操作表记录(添加insert into , 修改 update , 删除 delete) 添加数据 Insert into 表名(列名1,列名2,..列名n) values(值1,值2,..值n); 删除数据 Delete from 表名 [where 条件]; 修改数据 Update 表名 set 列名1 = 值1,列名2 = 值2,...列名n=值n[where 条件]; DQL数据库查询语言 : 查询表记录 查询所有表记录 Select * from 表名; 查询多字段 Select 字段1,字段2,..字段n from 表名; 去除重复的结果集 Select distinct 字段名 from 表名; 两列之间的四则运算 Select 列1,列2,列1 + 列2 as 新列名 from 表名; -- 新列名 是列1 + 列2 生成的新列的名字 Ifnull(表达式1,表达式2) : 表达式1代表可能出现的null值的列,表达式2代表替换null之后的值 Day03-mysql 1.单表查询 1.0 模糊查询 Like 占位符_ : 代表单个任意字符 占位符%: 代表多个任意字符 查找表中指定字段以张字开头的数据 Select * from 表名 where 字段名 like ‘张%’; 查找表中指定字段第二个字是张的数据 Select * from 表名 where 字段名 like ‘_张%’; 查找表中指定字段包含汉字是张的数据 Select * from 表名 where 字段名 like ‘%张%’; 1.1条件查询 Where 子句后跟条件 运算符 < > <= >= = != <> Between.. and :在..之间 eg : 在20 和30之间 Between 20 and 30; In(集合) : 小括号中可以填写多个值 eg :in(18,22,25); Is null : 判断值是null值 And 和 && Or 和 || Not 和 ! 1.2 排序查询 语法 : order by 字段1排序方式1,字段2 排序方式2... 使用方式: Select * from 表名 order by 字段名 排序方式; 注意:如果没有写排序方式,则默认按照升序(asc)排序;如果有多个条件进行排序,则当前边条件值一样时,才会进行后边条件判断 降序排序:desc; 1.3 分组查询 基本语法: group by 字段 Select 分组字段,聚合字段 from 表名 Where 分组之前的条件判断 Group by 分组字段 Having 分组后的条件判断/聚合字段 Order by 字段 排序方式; 对分组之后的数据进行排序 Where和having之间的区别: where是在分组前进行判断,having是在分组之后进行判断 Where后不能使用聚合函数,而having后边可以写聚合函数 1.4 分页查询 分页:数据太多,一次性把数据查询出来后,数据展示效果不好,用户体验不好 分页操作:不同数据库的分页操作是不相同的 Mysql: 使用limit进行分页,其他数据库不能使用数据库进行分页 语法: Limit 参数1,参数2; 参数1:数据的起始位置(索引),从0开始,代表第一条数据 参数2:代表每页要显示的数据的个数 参数1 = (参数2 - 1 ) * 参数2; 1.5 聚合函数查询 Sum(字段) : 求和 Avg(字段) : 计算平均值 Count(字段) :计算个数 Max(字段) : 计算最大值 Min(字段) : 计算最小值 注意: 聚合函数中的参数是表的字段 聚合函数在进行运算时,null值不参与运算 聚合函数一般对int类型进行计算,其他类型计算无意义 在使用count(字段) 时,字段一般是主键字段(非空唯一) Select sum(ifnull(有null值的字段,0)) from 表名; 1.6 综合案例 2. 约束 1. 概述: 对表中的数据进行限定, 保证数据的的正确性, 有效性和完整性 一种规范(限制), 数据库约束就是对表中的数据进行规范, 让数据具有某种特点 2. 分类: 1. 主键约束 : primary key 2. 非空约束: not null 3. 唯一约束: unique 4. 外键约束: foreign key * 非空约束: not null 1. 创建表时添加约束 CREATE TABLE 表名( idINT, NAMEVARCHAR(20) NOT NULL ); 2. 创建表完后, 添加非空约束 ALTER TABLE 表名 MODIFY NAME VARCHAR(20) NOT NULL; 3. 创建表完后, 去除非空约束 ALTER TABLE表名 MODIFY NAME VARCHAR(20); * 唯一约束: unique, 某一列的值不能重复 1. 注意事项: 唯一约束可以有NULL值, 但是只能有一条记录为null 2. 在创建表时, 添加唯一约束 CREATE TABLE 表名( idINT, phone_numberVARCHAR(20) UNIQUE -- 添加唯一约束 ); 3. 删除唯一约束 ALTER TABLE 表名 DROP INDEX phone_number; 4. 在表创建完后, 添加唯一约束 ALTER TABLE 表名 MODIFY phone_number VARCHAR(20) UNIQUE; * 主键约束: primary key 1. 注意事项: 1. 含义: 非空且唯一 2. 一张表只能有一个字段主键 3. 主键就是表中记录的唯一 2. 在创建表时, 添加主键 CREATE TABLE 表名( idINT PRIMARY KEY,-- 给id添加主键约束 NAMEVARCHAR(20) ); 3. -- 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY; 4. -- 创建完表后, 添加主键 ALTER TABLE 表名 MODIFY id INT PRIMARY KEY; 5. 自动增长: 概念: 如果某一列是数值类型的, 使用 auto_increment 可以来完成值的自动增长 -- 创建表时添加自动增长 CREATE TABLE 表名( idINT PRIMARY KEY AUTO_INCREMENT,-- 给id添加主键约束 NAMEVARCHAR(20) ); -- 删除自动增长 ALTER TABLE 表名 MODIFY id INT; -- 添加自动增长 ALTER TABLE 表名 MODIFY id INT AUTO_INCREMENT; * 外键约束 : foreign key , 让表与表产生关系, 从而保证数据的正确性 1. 在创建表时, 添加外键 语法: create table 表名 { . . . Constraint 外键名称 foreign key (外键列的名称)references 主表名称(主表列名称) }; 2. 删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; 3. 添加外键: ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称) 4. 外键的特点: 1. 外键的值不能随意写 2. 外键的值引用于另一张表的主键值 3. 外键的值可以为null , 但是为null没有任何意义 5. 外键的设计原则 主表 子表 6. 级联操作 : 只有表与表之间有关联关系, 才能进行级联操作 作用: 当主表的数据发生变化时, 那么字表的数据会级联发生变化 级联操作: 一般情况下, 操作是对修改(update)和删除(delete) 分两种情况: 1. 当主表的数据变化时, 子表的数据保持一致 on updatecascade on update cascade 2. 当主表的数据变化时, 子表的数据(外键的数据) 设置为null 语法: on update set null ondelete set null 1. 添加级联操作 语法:ALTER TABLE 表名 ADDCONSTRAINT 外键名称 OREIGNKEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADEON DELETE CASCADE ; 2. 分类: 1.级联更新:ON UPDATE CASCADE 2.级联删除:ON DELETE CASCADE 3.多表关联 多表之间的关系 1. 一对一 : (了解, 很少存在) 如: 人和身份证的关系 分析: 一个人只有一个身份证, 一个身份证只能对应一个人 如: 用户表和 信息表 主表(子表)可以是任意一方 2. 一对多(多对一) : 如: 部门表和 员工表 分析: 一个部门有多个员工, 一个员工只能对应一个部门 主表: 一方表, 这里值的就是部门表 子表: 多方表, 这里指的就是员工表 外键设计在子表里面 3. 多对多: (通俗的来说, 是强化版的一对一) 如: 学生表和 课程表 分析: 一个学生可以选择很多门课程, 一个课程也可以被很多学生选择 主表(子表) 可以是任意一方 (不推荐这样区分和设计外键) 数据库设计的范式 1. 概念: 设计数据库时, 需要遵循的一些规范, 要遵循后边的范式要求, 必须先遵循前边的所有 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。 2. 分类: 第一范式(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基础上消除传递依赖) 四 数据库的备份与还原 1. 命令行: 语法: 备份 : mysqldump -u用户名 -p密码 数据库名 > 保存路径 还原 : 1. 登录数据库 2. 创建数据库 3. 使用数据库 4. 执行文件。source 文件路径 2. 图形化工具: 4.数据库的设计范式 Mysql 多表查询和事务 1.多表查询(掌握) 多表查询语法 Select 列名列表 From 表明列表 Where.... Eg: select * from 表1名,表2名; 笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况. 要完成多表查询,需要消除无用的数据 消除不合理的数据(无用的数据)多表查询分类: 内连接查询:获取有连接关系的两个表的交集 隐式内连接:使用where条件消除无用的数据 -- 查询所有员工信息和对应的部门信息 Select * from emp,dept where emp.dept_id =dept.id; -- 查询员工姓名,性别和部门的名称 Select t1.name, T1.gender, T2.name From 表1 t1, 表2 t2, Where T1.dept_id = t2.id; 显式内连接 语法: select 字段列名 from 表1名 [inner] join 表2名 on 条件 消除无效的数据条件:外键的值 = 主键的值 (外键的值引用了主键的值) -- 查询 员工和部们的信息 SELECT * FROM emp,dept WHERE emp.dept_id = dept.id; -- 查询 员工姓名,员工的部门编号 ,部门的部门编号,部门名称 SELECT t1.id, t1.NAME, t2.id, t2.NAME FROM empt1, deptt2 WHERE t1.dept_id = t2.id; -- 查询 入职日期早于2011年的员工姓名 ,以及所在的部门编号,部门名称 SELECT t1.NAME, t2.id, t2.NAME FROM empt1, deptt2 WHERE t1.join_date < '2011-01-01' AND t1.dept_id= t2.id; -- 查询 不是开发部的员工姓名 和 部门名称 SELECT t1.NAME, t2.NAME FROM empt1, deptt2 WHERE t2.NAME!= '开发部' AND t1.dept_id= t2.id; -- 查询 没有部门的员工姓名和部门编号 SELECT t1.NAME, t1.dept_id FROM empt1 WHERE t1.dept_idIS NULL; -- 查询 有部门的员工姓名,部门编号和 部门名称 SELECT t1.NAME, t1.dept_id, t2.NAME FROM empt1, deptt2 WHERE t1.dept_idIS NOT NULL AND t1.dept_id= t2.id; 外连接查询 左外连接语法:select * from表1 left [outer] join 表2 on 条件 左表:表1 右表:表2 查询表1的所有数据以及和表2交集的部分 查询表中所有 查询的是左表所有数据以及其交集的部分 右外连接语法:select * from表1 right [outer] join 表2 on 条件 左表:表1 右表:表2 查询的是表2所有的数据以及和表1交集的部分 子查询 概念:查询中嵌套查询,称嵌套查询为子查询 子查询分类情况 第一种情况:子查询作为一个值(单行单列) -- 查询员工工资小于平均工资的人 Select * from 表名 where (select avg(sal) from 表名); 第二种情况:子查询作为一个数组(多行单列) -- 查询员工薪水中排名前2的员工信息 Eg:select * from 表名 where salary in(select salary from 表名 order by desc ); 第三种情况:子查询作为一个表(多行多列),用在from后边 Eg:-- 查询出部门编号、部门名称、部门位置、部门人数 SELECT e.dept_id,COUNT(e.id) FROM empe GROUP BY e.dept_id; -- 查询每个部门的人数 SELECT * FROM deptd ,(SELECT e.dept_id , COUNT(e.id) total FROM empe GROUP BY e.dept_id) t2 WHERE d.id = t2.dept_id; 2.事务(了解) 事务的基本介绍 概念:如果一个包含多个步骤的业务操作被事务管理,这些操作要么同时成功,要么同时失败 操作: 开启事务 start transaction 回滚 rollback : 把数据恢复到初始状态 提交事务 commit : 把数据持久保存到表中 在mysql数据库中,事务默认自动提交 一条DML(增删改)语句会自动提交一次事务 事务提交的两种方式 手动提交:需要先开启事务在提交 Oracle数据库默认是手动提交事务 自动提交:在mysql数据库中,事务默认自动提交,一条DML(增删改)语句会自动提交一次事务 修改事务的默认提交方式: set@@autocommit = 0; 查看事务的默认提交方式:select@@autocommit 1代表自动提交,0代表手动提交 事务的四大特征 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败 持久性:当事务提交或回滚后,数据库会持久化保存数据 隔离性:多个事务之间相互独立 一致性:事务操作前后,数据总量不变 事务的隔离级别 概念:多个事务之间隔离的,相互独立的,如果多个事务操作同一批数据,会引发一些问题,设置不同的隔离级别就可以解决这些问题 存在问题: 1.脏读:一个事务读取到另一个事务中没有提交的数据 2.不可重复读(虚读):在同一个事务中,两次读取到的数据不一样 3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改 隔离级别: 1.read uncommitted : 读未提交 产生的问题:脏读 不可重复读(虚读) 幻读 2.Read committed : 读已提交 产生的问题:不可重复读(虚读) 幻读 解决脏读 3.Repeatable read : 可重复读 产生的问题: 幻读 解决脏读和不可重复读 4.Serializable :串行(xing)化 可以解决所有的问题 注意:隔离级别从小到大安全性越来越高,效率越来越低 数据库查询隔离级别 Select @@ tx_isolation 数据库设置隔离级别 Set global transaction isolation level 级别字符串
|