国际网站建设标准,在灵璧怎样做网站,建设教育信息网站工作总结,深圳网站制作长沙文章目录 前言1.数据库概述1.1 数据库相关概念1.2登录MySQL#xff1a;1.3 MySQL常用命令1.4表#xff1a;1.5SQL语句分类#xff1a; 2.CRUD操作2.1 DQL1.基础查询基础查询#xff08;简单查询#xff09;条件查询#xff1a;排序查询#xff1a;分组查询#xff1a;分… 文章目录 前言1.数据库概述1.1 数据库相关概念1.2登录MySQL1.3 MySQL常用命令1.4表1.5SQL语句分类 2.CRUD操作2.1 DQL1.基础查询基础查询简单查询条件查询排序查询分组查询分页查询 2 链接查询链接方式分类 3 子查询where子查询from子句中的子查询select后面的子查询 4 union合并查询结果集5 limit 查询 2.2 DDL2.2.1 create表的创建2 .2.2 alter修改表2.2.3 drop删除表快速删除表 2.3DML2.3.1插入 insert2.3.2修改 Update2.3.3删除 delete 2.4 表2.4.1 表结构的增删改查2.4.2约束 constraint1 非空约束 not null2 唯一性约束 unique3 主键约束 primary key :PK4 外键约束foreign key FK 2.5 数据处理函数2.2.1 单行处理函数2.2.2 多行处理函数 3 存储引擎4 事务4.1 实现事务4.1.1 提交事务 commit4.1.2 回滚事务 rollback 4.2 事务的特性4.2.1 事务的隔离性1 事务的隔离级别 5 索引5.1创建索引5.2索引失效5.3 索引的分类 6 视图6.1 创建视图6.2 删除视图6.3 视图的CRUD 7 DBA命令7.1 新建用户7.2 授权7.2.1 回收授权 7.3 数据的导入导出7.3.2数据的导入 8 数据库设计的三规范9 SQL高级应用8.1 T-SQL程序设计8.1.1 变量8.1.2 流程控制语句 8.2存储过程8.2.1 存储过程 的有点8.2.2 存储过程的分类8.2.3 创建存储过程 8.3触发器8.3.1 分类8.3.2 创建DML触发器8.3.3 创建DDL触发器 前言
MySQL专栏汇总 :MySQL 必死 1.数据库概述
1.1 数据库相关概念
数据库DataBase (DB)
按照一定格式存储数据的一些文件的组合存储数据的仓库数据文件日志文件。具有一定特定格式的数据。
数据库管理系统DataBase Management DBMS
专门用来管理数据库中的数据的数据库管理系统可以对数据当中的数据进行增删改查
SQL 结构化查询语言
使用DBMS负责执行SQL语句来完成数据库中的增删改查。SQL是一套标准语言主要学习SQL语句。SQL语句可以在MySQL、Oracle、DB2中使用。
三者之间的关系 DBMS ----执行----- SQL -------操作---- DB 1.2登录MySQL
mysql修改登录用户名和秘密
# 修改密码-- 1.进入cmd
-- 2. 输入
mysql -u root -p;
-- 3.输入旧密码
Enter password: *****
-- 4.选择数据库
mysql use 库名;
-- 输入修改语句信息
mysql UPDATE user SET password PASSWORD(新密码) WHERE user 用户名;-- 刷新
mysql flush privileges;#修改用户名
-- 1.进入cmd
-- 2. 输入
mysql -u root -p;
-- 3.输入旧密码
Enter password: *****
-- 4.选择数据库
mysql use 库名;
-- 输入修改语句信息
mysql UPDATE user SET u user(新用户名) WHERE user root;
mysql flush privileges;
mysql exit1.在DOS窗口下隐藏密码形式 打开mysqlnet start mysql 关闭mysqlnet stop mysql 进入mysql指令mysql -u root -p 关闭mysql指令exit退出
2.一步到位登录MySQL指令显示密码形式
mysql -uroot -p123456 // -p 后面的是MySQL 的密码 1.3 MySQL常用命令 查看数据库 show Database;使用数据库 use test;use 数据库名 创建数据库 create database test1create database 数据库名 查看表 show tables;sql中导入数据 执行sql脚本 source 全路径指定sql脚本的路径路径不能出现中文 查看MySQL版本 select version();查看当前使用的数据库 select database();1.4表
数据库中的最基本单元 表
表结构 行row数据 / 记录 列column字段 每一个字段都有字段名数据类型约束等属性 字段名 数据类型字符型、日期型、时间型、整型等 约束
1.5SQL语句分类 DDL 数据定义语言 (Data Definition Language) 对表结构进行操作create创建alter修改drop删除 DML 数据操作语言 (Data Manipulation Language) 对表当中的数据进行增删改查 操作表中的数据datainster 插入delete 删除update 修改 DQL 数据查询语言 (Data Query Language) select 语句 DCL 数据控制语言 (Data Control Language) 授权GRANT撤销权限REVOKE TCL 事务控制语言 事务提交commit事务回滚rollback
2.CRUD操作
2.1 DQL
SQL语句是通用的以英文分号 ; 结束MySQL数据库的SQL语句不区分大小写关键字建议使用大写。注释 单行注释-- 注释内容或 # 注释内容(MySQL特有)多行注释/* 注释 */
1.基础查询 基础查询简单查询 查询所有列 (字段) SELECT * FROM 表名; -- 查询所有字段 效率低、可读性差、开发中不建议查询单个/多个字段 SELECT 字段1字段2,···,字段n
FROM 表名; -- 查询单个/多个字段 去重查询 SELECT DISTINCT 去重的字段1 FROM 表名; SELECT DISTINCT name FROM student; -- 去重复的姓名 为字段起别名 SELECT 字段1 AS 别名名称 FROM 表名; -- 方式一SELECT 新字段名 字段1 FROM 表名; -- 方式二-- 列如SELECT name AS 姓名 FROM student; -- 方式一SELECT name 姓名 FROM student; -- 方式二SELECT 姓名 name FROM studnet; -- 方式三**别名只是在显示中并不会修改到表中的字段名**别名中间存在有空格会出现报错不符合语法编译错误。若是字段中间必须要有空格必须使用单引号或者双引号括起来sqlSELECT name AS 姓 名 FROM student; -- 标准字符串单引号括起来SELECT name AS 姓 名 FROM student; -- 不推荐不标准注意1. 单引号是标准的字符串形式2. 双引号在mysql中可以使用在oracle中不可以使用字段可以使用数学表达式 给字段进行加减乘除 条件查询 查询出符合条件的内容 条件查询语法 SELECT 字段1,字段2,···,字段n,FROM 表名WHERE 条件;条件符号 符号说明等于或!不等于小于小于等于大于大于等于between ···· and两个值之间当同于 and 遵循左小右大is null为空is not null 不能为空and并且or或者in包含相当于多个 or not in 不包括在内like模糊查询支持%或下划线匹配%通配符匹配任意个字符_下划线匹配一个字符 SELECT name ,age FROM student WHERE age 55 ; -- 等于SELECT name ,age FROM student WHERE age ! 55; -- 不等于SELECT name ,age FROM student WHERE age 55;SELECT name ,age FROM student WHERE age 55; -- 小于SELECT name ,age FROM student WHERE age 55; -- 小于等于SELECT name ,age FROM student WHERE age 55 ; -- 大于SELECT name ,age FROM student WHERE age 55; -- 大于等于SELECT name ,age FROM student WHERE age BETWEEN 23 AND 30; -- 年龄在23到30之间SELECT name ,age FROM student WHERE age 23 AND 30 ;SELECT name ,age FROM student WHERE age IS NULL; -- is null is不能改为等号SELECT name ,age FROM student WHERE age IS NOT NULL; -- is not null is不能改为等号SELECT name ,age FROM student WHERE name 张三 AND age 45; -- endSELECT name ,age FROM student WHERE name 张三 OR name 小三; -- OR 有一个就可以查找到SELECT name ,age FROM student WHERE age 15 AND name 张三 OR name 小三; -- and 和 or 同时出现这一句中先执行 and 在执行 or SELECT name ,age FROM student WHERE age 15 AND (name 张三 OR name 小三); -- 正确的写法 让 or 先执行SELECT name ,age FROM student WHERE age IN(15,35,45,25); -- 年龄在15、25、35、45中的SELECT name ,age FROM student WHERE age NOT IN(15,35,45,25); -- 年龄不在15、25、35、45中的name 和 age模糊查询LIKE SELECT 字段1 AS 别名名称 FROM 表名; -- 方式一SELECT 新字段名 字段1 FROM 表名; -- 方式二-- 列如SELECT name AS 姓名 FROM student; -- 方式一SELECT name 姓名 FROM student; -- 方式二SELECT 姓名 name FROM studnet; -- 方式三**别名只是在显示中并不会修改到表中的字段名**别名中间存在有空格会出现报错不符合语法编译错误。若是字段中间必须要有空格必须使用单引号或者双引号括起来sqlSELECT name AS 姓 名 FROM student; -- 标准字符串单引号括起来SELECT name AS 姓 名 FROM student; -- 不推荐不标准注意1. 单引号是标准的字符串形式2. 双引号在mysql中可以使用在oracle中不可以使用SELECT name ,age FROM student WHERE LIKE %小% ;-- 含有‘小’的名字SELECT name ,age FROM student WHERE LIKE 张% ;-- 姓张的SELECT name ,age FROM student WHERE LIKE %森 ;-- 以森结尾的SELECT name ,age FROM student WHERE LIKE %_垂%; -- 第二个字为垂的SELECT name ,age FROM student WHERE LIKE %__森%; -- 第二个字为森的SELECT name ,age FROM student WHERE LIKE %_% ;-- 找出名字中含有下划线的。 因为下划线具有特殊含义需要把下划线进行转义SELECT name ,age FROM student WHERE LIKE %\_%; -- 正确 √ 排序查询 排序总是在最后执行 语法 SELECT 字段1,字段2,···,字段n,
FROM 表名
ORDER BY 排序字段名1 [排序方式1], 排序字段名2 [排序方式2] ...;排序方式 ASC 升序 默认值不写就是默认升序 DEAC 降序 多个字段排序 如果有多个排序条件当前边的条件值一样时才会根据第二条件进行排序 SELECT name ,age FROM student ORDER BY age ; -- 按照年龄 默认升序SELECT name ,age FROM student ORDER BY age DESC; -- 降序SELECT name ,age FROM student ORDER BY age ASC ; -- 指定升序-- 多个字段排序 如果有多个排序条件当前边的条件值一样时才会根据第二条件进行排序SELECT name ,age,score FROM student ORDER BY age DESC,name DESC ; -- 若是年龄相同才能进行name排序-- 根据字段的位置进行排序SELECT name ,age FROM student ORDER BY 2; -- 根据第二列排序综合案例查询年龄在20到50之间并且更具年龄进行降序排序 SELECTname,ageFROM studentWHEREage 20 AND age 50ORDER BYage DESC ;分组查询 分组函数 分组函数在使用前必须进行分组若是没有分组则一个表就是一个组 语法 SELECT 分组函数名列名FROM 表- count 计数- sum 求和- avg 平均值- max 最大值- min 最小值- 注意1. 自动忽略NULL2. count(*) 和count(具体字段)的区别- count(*) 统计总行数不忽略null- count(具体字段)表示统计该字段下所有**不为NULL的元素的总数**。忽略null3. 分组函数不能直接使用在where子句中。4. 所有的分组函数可以组合在一起用。分组查询语法 先进行分组对每一组的数据进行操作。 SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定]GROUP BY 分组字段名 [HAVING 分组后条件过滤];- where不参与分组having是在分组之后的where不能对聚合函数进行判断having可以。- **关键字执行顺序from -- where -- group by -- having-- select --order by**~~~mysqlSELECT COUNT(name) FROM student ; -- 可以执行因为SELECT在分组后执行SELECT name ,age FROM student WHERE age MIN(age) -- 不能执行 因为进行WHERE时 还没有进行分组案例 -- 1.找出每个工作岗位的工资和-- 思路按照工作岗位分组然后对工资求和SELECT job,SUM(sal)FROMempGROUP BYjob; -- 2.找出 每个部门不同工作岗位的最高薪资-- 两个字段进行分组SELECTdeptID,job,MAX(sal)FROMempGROUP BYdeptID,job ;-- 3.找出部门的最高薪资要求显示最高薪资大于3000的(方法一)SELECTdeptID,MAX(sal)FROMempGROUP BYdeptID HAVINGMAX(sal) 3000;(方法二) SELECTdeptID,MAX(sal)FROMempWHERE sal 3000GROUP BYdeptID ;-- 优化策略where 和 having 优先选择wherewhere实现不了再选择having。-- having的执行效率低-- 4.找出每个部门平均薪资要求显示平均薪资高于2500的 -- 这个就不能使用where错误的写法where子句中不能进行写分组函数 SELECTdeptID,AVG(sal)FROMempwhere avg(sal) 2500GROUP BYdeptID ;(正确写法)SELECTdeptID,AVG(sal)FROMempGROUP BYdeptID HAVING AVG(sal) 2500;-- 5.找出每个岗位的平均薪资要求显示平均薪资大于1500的出去mag岗位之外。 SELECT job,AVG(sal) avgsalFROMempWHERE job! magGROUP BYjobHAVING AVG(sal) 1500ORDER BYavgsal DESC; -- jiangx- SELECT 语句中含有GROUP BY 语句SELECT后面只能跟参与分组以及分组函数的字段其他字段不可以写上去 添加其他的字段在oracle中会报错- HAVING 子句不能单独使用必须和GROUP BY 一起使用。 - HAVING对分组之后的进行条件刷选。分页查询 语法SELECT 字段名 FROM 表名 起始索引 查询数目的条数 – 起始索引公式当前页的起始索引 当前页码 - 1 *每页显示的条数 分页查询limit是MySQL数据库的方言 Oracle分页查询使用rownumber SQL Server分页查询使用top 去重查询 关键字 select distinct name from student;distinct 只能出现在所有查询字段的最前方 可以使用分组函数
2 链接查询
多表查询
链接方式分类 内连接 完全能匹配上这个条件的数据查询出来 多个表之间的关系的平等的关系 等值链接 -- 查询每个员工所在部门名称显示员工名和部门名
-- SQL92 :结构不够清晰
select s.sname,y.yname
fromstudent s , yanjiu y
where s.sno y.sno;-- SQL99 表链接的条件是独立的连接之后还可以继续添加条件 inner可以省略
select s.sname,y.yname
fromstudent s
[inner] join yanjiu y
ons.sno y.sno;非等值链接 条件不是等量关系 -- 工资介于salgrade中的最低和最高之间 用到两个表
select e.ename,e.sal,s.grade
fromemp e
joinsalgrade s
one.sal between s.losal and s.hisal 自链接一个表写成两个表 -- 查询员工的上级领导要求显示员工名和对应的领导名
selecta.ename 员工名, b.ename 领导名
from emp a
joinemp b
on a.mgr b.empno; 外连接 多个表之间有主次的关系主表和副表 主表就是要全部查询出来的副表的符合条件才会查询出来 左链接 -- 左外查询 左边的是主表
select s.sname,y.yname
fromstudent s left join yanjiu y
ons.sno y.sno;-- 查询员工的上级领导要求显示员工名和对应的领导名
selecta.ename 员工名, b.ename 领导名
from emp a
left joinemp b
on a.mgr b.empno; 右链接 -- 右外查询 右边的是主表 outer可以省略
select s.sname,y.yname
fromstudent s
right [outer] join yanjiu y
ons.sno y.sno; 左右链接之间可以相互转换 外连接的查询结果条数一定是 内连接的查询结果条数 全连接
笛卡尔积现象当两张表进行链接查询没有任何条件查询的时候最终查询结果条数是两张表条数的乘积
避免笛卡尔积现象连接时加条件满足这个条件的记录就筛选出来
select sname,yname
fromstudent,yanjiu
where student.sno yanjiu.sno-- 起别名
select s.sname,y.name
fromstudent s,yanjiu y
where s.sno y.sno-- 减少链接次数
3 子查询 SELECT语句中嵌套子查询被嵌套的SELECT语句为子查询 where子查询
-- 找出比最低工资高的员工姓名和工资
SELECT ename, sal
FROMemp
WHEREsal (SELECT MIN(sal) FROM emp);from子句中的子查询 from后面的子查询可以将子查询的查询结果当作一个临时表 -- 找出每个岗位的平均工资的薪资等级
select t.*,s.grade
from (select job,avg(sal) avgsal from emp group by job) as t
joinsalgrade s
ont.avgsal between s.losal and s.hisal select后面的子查询
返回的结果只能有一条数据多余一条数据就会报错
4 union合并查询结果集
SELECT name ,age FROM student WHERE name 张三
UNION
SELECT name ,age FROM student WHERE name 小三;效率高链接一次新表匹配次数就会翻倍union可以减少匹配次数还可以将结果集拼接起来 union在进行结果集的合并时要求两个结果集的列数相同
5 limit 查询
将查询结果集的一部分取出来使用在分页查询中
使用方法
完整写法 LIMIT startIndex从0开始 length长度
SELECT ename, sal
FROMemp
ORDER BYsal desc
LIMIT5 ; -- 取前五条SELECT ename, sal
FROMemp
ORDER BYsal desc
LIMIT1,5 ; -- 前六1-6通用分页 每页显示 6页条记录 第1页limit 06 【0、1、2、3、4、5】 第2页limit 66 【6 7 8 9 10 11】 第3页limit 126 第n页limit (n-1) * 66 开始的index pageSize * n - 1 limit (pageNO - 1) * pageSize , pageSize2.2 DDL
对表的结构进行操作
2.2.1 create表的创建 语法 CREATE TABLE 表名
(
字段名1 数据类型,
字段名1 数据类型,
字段名1 数据类型,
字段名n 数据类型
);表名规范 以 t_table1 或者 tbl_ 开始 -- 可读性高创建表实列 -- 建立学生表
CREATE TABLE t_student
(sno int(10),name varchar(22),age int(3),sex char(5),email varchar(255)
);-- 建立学生表指定默认值
CREATE TABLE t_student
(sno int(10),name varchar(22),age int(3) default 女,sex char(5),email varchar(255)
);2. 数据类型
数据类型就是属性 有一些常用的例如整数数据 字符数据日期数据货币数据等
数字类型
数据类型范围占用的字节bigint-263 ~263 -18字节int-231~231-14字节 11字符smallint- 215 ~ 215-12字节tinyint0~2551字节float-1.79E308~3.40E384或者8字节
时间类型
数据类型输出time123529.123 精确度到秒后面三位时分秒date2007-05-08 年月日短日期smalldatetime2007-05-08 12:35:00datetime2007-05-08 12:35:29.123(精确到后面 就是小数点后面三位) 长日期datetime2007-05-08 12:35:29.1234567(精确到小数点后面三七位)
字符串类型
类型说明char[(n)]固定长度255长度 。 n用于定义字符串长度必须在1~8000之间。varchar[(n|max)]可变长度。n用于定义字符串长度可以在1~8000之间。nchar[(n)]固定长度的Unicode字符串数据。n用于定义字符串长度必须在1~4000之间。nvarchar可变长度的Unicode字符串数据。n用于定义字符串长度必须在1~4000之间。clob字符大对象。最多可以存储4G的字符串超过255个字符使用它blob二进制大对象。存储图片、声音、视频等媒体数据插入数据时必须使用IO流
2 .2.2 alter修改表
修改数据表
修改表名 ALTER TABLE 表名 RENAME TO 新的表名;修改列名和数据类型 ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;添加一列 ALTER TABLE 表名 ADD 列名 数据类型;删除列 ALTER TABLE 表名 DROP 列名修改数据类型 ALTER TABLE 表名 MODIFY 列名 新数据类型;
2.2.3 drop删除表
语法
DROP TABLE 表名
DROP TABLE IF EXISTS 表名 -- 存在就删除快速删除表 语法 truncate table 表名;特点物理删除删除效率高不可以恢复删除数据表结构保留不能删除单条数据
删除数据表
删除表DROP TABLE 数据表名drop 表TABLE if exists 表名
2.3DML
对表中的数据进行修改
2.3.1插入 insert 语法 insert into 表名(字段名1,字段名2,字段名3,...) values(值1,值2,值3,...)字段名和值要一一对应数量对应数据类型要对应。 插入实列 insert into t_student(sno,name,age,sex,eamil) values(1,张三,15,女,1234567qq.com) -- 按照顺序insert into t_student(name,sno,age,sex,eamil) values(张三,1,15,女,1234567qq.com) -- 字段顺序可以调整但是字段名和值一定要对应insert into t_student(sno,name) values(张4,2) -- 只要某些字段,插入数据后其他的字段就会是nullinsert into t_student() values(1,李4,12,女,123456744qq.com) -- 省略字段名就必须写上所有的字段名插入多条记录 INSERT
INTO 表名 (列名1,列名2.…
VALUES (值1,值2...), (值1,值2...), (值1,值2…);insert插入日期 格式化format() 将查询结果插入到一张表中 少 用 insert into 表名 select * from 表名 ;select * into 新表名 from 表名;2.3.2修改 Update 语法格式 update表名
set字段名1 值1,字段名2 值2,字段名3 值3,字段名n 值n
where条件;没有条件将会将整个表更改
2.3.3删除 delete
表中的数据被删除了但是数据在硬盘上的真实存储空间不会被释放删除效率低可回滚 语法格式 delete
from表名
where条件没有条件将会把整个表内的数据进行删除 删除大表删除表中的全部数据 2.4 表
2.4.1 表结构的增删改查
一旦设计好表就很少会对表进行修改修改成本高就要对java代码进行修改使用工具navicat修改
2.4.2约束 constraint
给字段添加约束保证表中数据的完整性有效性保证表中的数据有效列约束直接在列后面添加约束表级约束约束没有添加在列的后面
非空约束not null唯一约束unique主键约束primary leyPK外键约束foreign keyFK级联更新检查约束checkmysql不支持
1 非空约束 not null 约束的字段不嫩改为null CREATE TABLE t_student
(sno int(10) not null,name varchar(22) not null,age int(3),sex char(5),email varchar(255)
);2 唯一性约束 unique 可以为null 但是不可以重复 drop table if exists t_student
CREATE TABLE t_student
(sno int(10) unique, -- 列级约束name varchar(22),age int(3),sex char(5),email varchar(255)
);insert into t_student() values(1,李4,12,女,123456744qq.com)
insert into t_student() values(2,李4,12,女,123456744qq.com)
-- 在插入数据的时候sno的值则不能重复 多个字段字段联合起来具有唯一性 CREATE TABLE t_student
(sno int(10),name varchar(22),unique(sno,name) -- sno ,name 联合起来唯一。表
);
insert into t_student() values(1,李4,12,女,123456744qq.com)
insert into t_student() values(2,李武,12,女,123456744qq.com)
-- 分开的snoname可以重复但是snoname 联合起来就不可以重复not null 和unique联合使用 CREATE TABLE t_student
(sno int(10) not null unique, -- sno变成主键name varchar(22)
);3 主键约束 primary key :PK 主键约束一个表只能有一个主键 主键字段字段添加主键约束 主键值每一行记录的唯一标识。 建议使用int 、bigint、char等类型不建议使用varchar来做主键主键值定长的。 特征
not null unique 主键值不能为空不能重复 任何每一张表都要有主键没有就是非法的 drop table if exists t_student
CREATE TABLE t_student
(sno int primary key , name varchar(22)
);-- 表级约束添加主键约束
drop table if exists t_student
CREATE TABLE t_student
(sno int , name varchar(22),primary key(sno)
);-- 一个字段做主键单一主键-- 多个字段做主键复合主键 不要使用
drop table if exists t_student
CREATE TABLE t_student
(sno int , name varchar(22),primary key(sno,name)
);自然主键自然数使用多 业务主键主键值与业务紧密关联。使用少业务变动会影响到业务会改变 -- 自动维护主键值
drop table if exists t_student
CREATE TABLE t_student
(sno int primary key auto_increment, -- 使sno自增auto_incrementname varchar(22)
);4 外键约束foreign key FK 术语 外键约束外键字段字段添加上外键约束外键值外键字段中的每一个值。可以为null 父表被引用的表 子表引用的表
顺序 理解不要死记
删除表 先删除子表再删除父表 创建表 先创建父表再创建子表 删除数据 先删除子数据再删除父数据 插入数据 先插入父再插入子
CREATE TABLE t_calss -- 父表
(classno int primary key , -- 使sno自增auto_incrementclassname varchar(22)
);CREATE TABLE t_student -- 子表
(sno int primary key auto_increment, name varchar(22),classcno int foreign key(classno) references t_class(classno)
);2.5 数据处理函数
又称为单行处理处理函数
2.2.1 单行处理函数
一个输入对应一个输出
函数名说明lower大写变成小写upper小写变成大学substr取子串被截取的字符串起始下标截取的长度length取长度trim去前后空格str_to_date将字符串转换成日期date_fromat格式化日期round四舍五入rand生成随机数ifnull将null转换成一个具体值
SELECT loser(name),age FROM student -- name 内容变成小写SELECT round(123456.123,0) FROM studnet ; -- round(参数1参数2) 参数1要四舍五入的数参数2保留多少位小数2.2.2 多行处理函数
多个输入对应一个输出
分组函数 / 聚合函数 / 多行处理函数 在上面的分组查询中查看 这里的内容
3 存储引擎
了解 表存储 / 组织 建表时指定存储引擎 engine -- 指定存储引擎
CHARSET -- 指定表的字符编码MySQL支持的搜索引擎 show engines \G -- 显示目前数据库版本支持的数据库引擎MyISAM MyISAM的表具有以下的特征 格式文件一存储表结构的定义(mytable.frm)数据文件―存储表行的内容(mytable.MYD )索引文件–存储表上索引(mytable.MYI)索引是一本书的目录缩小扫描范围提高效率 InnoDB MySQL默认存储引擎重量级引擎支持事务保证数据库的安全支持事务回滚服务器崩溃了支持自动恢复表和索引存储在一个表空间内 MEMORY 数据存储在内存当中断电就没有表数据及索引被存储在内存中查询快效率高不需要与硬盘交互。
4 事务
transaction
事务完整的业务逻辑。
事务的DML语句
insert
update
delete数据的增删改查要考虑安全 多条DML语句共同来联合完成 多条语句同时成功或者同时失败
4.1 实现事务
4.1.1 提交事务 commit
清空事务性活动的日志文件将数据全部彻底持久化到数据库表中提交事务标志着事务的结束。全部成功的结束。
mysql 默认情况下是自动提交事务
自动提交不符合我们开发的业务必须多条同时执行成功才进行提交业务。
-- 关系自动提交机制先执行
start transaction4.1.2 回滚事务 rollback
将之前所有的DML 操作全部撤销并且清空事务性活动的日志文件回滚事务标志着事务的结束。全部失败的结束。
-- 在经过一系列的增删改查之后
rollback ;回滚到上一次的提交
4.2 事务的特性 A 原子性 说明事务是最小的工作单元不可以再分。 C 一致性 所有的事务要求在同一个事务当中所有操作必须同时进行或者同时失败保证数据的一致性。 I 隔离性 A事务和B事务之间具有一定的隔离。 A和B事务同时操作同一个表结果会怎么样 D持久性 事务最终结束的一个屏障。事务提交将没有保存到硬盘上的数据保存到硬盘上。
4.2.1 事务的隔离性
1 事务的隔离级别 读未提交read uncommitted 最低的隔离级别 《提交之前就可以读到》 事务A可以读取到事务B未提交的数据脏读现象 Dirty read理论情况下 读已提交read committed 《提交之后才能读到》 事务A只能读取到事务B提交之后的数据解决了脏读现象不可重复读取数据每一次读取到的数据是真实数据 可重复读repeatable read 《提交之后也读不到 读取到开启事务时的数据。事务不结束数据就不会改变》 事务A开启之后每一次在事务A中读取到的数据都是一致的。即使事务B修改数据事务A中读取到的数据依然没有改变。解决了不可以重复读问题出现幻影读读取的数据都是幻想 序列化 / 串行化serializable 最高的隔离级别 效率最高解决所有问题 事务要进行排队不能并发
查看事务隔离级别
select tx_isolation设置全局隔离级别
set global transaction isolation level read uncommitted -- 读wei5 索引
索引是在数据表字段上添加的提高查询效率一个字段可以添加一个索引多个字段联合起来也可以添加索引
添加索引条件
数据量庞大以条件查询的形式存在很少的DMLinsert 、update、delete操作
5.1创建索引
创建索引语法
create index 索引名 on 表名字段;删除索引
drop index 索引名 on emp;底层原理二叉树B-tree 查看索引 explain select * from 表 where 条件 5.2索引失效
模糊查询尽量避免以“%” 开始则会开始进行索引查询否则不会进行索引查询。索引查询时必须知道第一个字母是什么使用 or 的情况失效or两边字段同时有索引才会走索引。复合索引没有使用左侧的列查找where当中索引列参加了运算在where当中索引列使用了函数
5.3 索引的分类
单一索引 单个字段添加索引复合索引 两个或多个字段添加索引主键索引 主键上添加索引唯一性索引 具有unique 约束的字段上添加索引 唯一性较弱的字段上索引用处不大
union 不会使用索引失效
6 视图 view 不同角度看同一份数据
6.1 创建视图
create view 视图名 as select * from 表名室友DQL语句才能以view的形式创建
6.2 删除视图
drop view 视图名6.3 视图的CRUD
视图的操作会影响到原表的操作。视图是一张临时表
7 DBA命令
7.1 新建用户
CREATE USER username IDENTIFIED BY password;username你将创建的用户名,password该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
7.2 授权
grant all privileges on dbname.tbname to usernamelogin ip identified by password with grant option;dbname*表示所有数据库tbname*表示所有表login ip%表示任何ippassword为空表示丌需要密码即可登录with grant option; 表示该用户还可以授权给其他用户
用户权限表
权限名权限描述alter修改数据库的表create创建新的数据库戒表delete删除表数据drop删除数据库/表index创建/删除索引insert添加表数据select查询表数据update更新表数据all允许任何操作usage只允许登录
7.2.1 回收授权
revoke privileges on dbname[.tbname] from username;刷新权限
flush privileges;7.3 数据的导入导出
7.3.1数据的导出 在dos命令窗口进行 导出指定数据库 C:\Users\25849mysqldump bookshopD:\bookshop.sql -uroot -p123456导出指定表 C:\Users\25849mysqldump bookshop emp D:\ bookshop_emp.sql -uroot -p1234567.3.2数据的导入
C:\Users\25849mysql -uroot -p123456
mysqlcreate database bookshop;
mysqluse bookshop;
mysqlsource D:\bookshop.sql8 数据库设计的三规范
数据表的设计依据对数据库表的设计 第一范式1NF要求任何一张表必须要有主键每一个字段为原子性字段不可以再分。 第二范式2NF建立在第一范式基础上要求所有非主键字段完全依赖于主键不存在部份依赖 第三范式3NF建立在第二范式基础上要求所有非主键字段直接依赖于主键不存在传递依赖 减少数据表的冗余
9 SQL高级应用
8.1 T-SQL程序设计
8.1.1 变量 全局变量 -- 开头的变量系统定于和维护用户无法进行修改或管理 局部变量 declare i int -- 使用关键declare 声明变量 i数据类型为int 局部变量赋值 -- 使用 set 或 select
set i 123
select i 18.1.2 流程控制语句
1 if 语句
# 类似 java 中的 if ··· else if ··· else
if 条件表达式命令行或程序块
else 命令行或程序块2 begin ··· end语句
BEGIN命令行或程序块
END3 IF [NOT] EXISTS 语句
IF [NOT] EXISTS(select 子查询)命令行或程序块
else 命令行或程序块 4 CASE 语句
格式一
CASE 表达式WHEN 表达式 THEN 表达式···WHEN 表达式 THEN 表达式[ELSE 表达式]
END 格式二
CASE 表达式WHEN 表达式 THEN 表达式···WHEN 表达式 THEN 表达式[ELSE 表达式]
END SELECT SNo,CNo,Score CASEWHEN Score is null THEN 未考WHEN Score 60 THEN 不及格WHEN Score 60 AND Score 90 THEN 良好WHEN Score 90 THEN 优秀END
FROM SC5 WHILE 语句
WHILE (条件)命令行或程序块8.2存储过程
8.2.1 存储过程 的有点
模块化的程序设计高效率的执行较少网络流量可以作为安全机制使用
8.2.2 存储过程的分类
系统存储的过程用户自定义存储过程扩展存储过程
8.2.3 创建存储过程
DROP TABLE IF EXISTS t_student;CREATE TABLE t_student
(id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255) NOT NULL,age INT(11) NOT NULL
);INSERT INTO t_student VALUES(NULL,懿,22),(NULL,小懿,18);8.3触发器
8.3.1 分类
DML 触发器DDL 触发器
8.3.2 创建DML触发器
格式
CREATE TRIGGER 触发器名称
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [INSERT] | [UPDATE] | [DELETE] }
AS
SQL语句[,...n]例子修改student 表数据修改之后查询修改后的数据。
--创建修改之后的触发器
CREATE TRIGGER trig_student_After
ON student
FOR UPDATE
AS PRINT THE TRIGGER IS AFTERSELECT * FROM studentFOR 和AFTER 作用一样
8.3.3 创建DDL触发器
CREATE TRIGGER 触发器名称ON { ALL SERVER | DATABASE }
{ FOR | AFTER }
{ 事件类型|事件组}[,...n]
AS
SQL语句[,...n]例子插入数据库后输入 ‘创建数据库’
CREATE TRIGGER trig_create
ON ALL SERVERAFTER CREATE_DATABASE
ASPRINT 创建数据库
