静态网站如何共用一个头部和尾部,如何选择网站公司,重庆奉节网站建设,怎么样百度搜到自己的网站MySQL笔记 文章目录 MySQL的三层结构SQL语句分类sql语句数据库操作创建数据库查看、删除数据库 表操作创建表mysql常用数据类型(列类型)查询表、插入值创建表练习创建一个员工表emp 修改表mysql约束primary key(主键)not null(非空)unique(唯一)foreign key(外键)check自增长 索… MySQL笔记 文章目录 MySQL的三层结构SQL语句分类sql语句数据库操作创建数据库查看、删除数据库 表操作创建表mysql常用数据类型(列类型)查询表、插入值创建表练习创建一个员工表emp 修改表mysql约束primary key(主键)not null(非空)unique(唯一)foreign key(外键)check自增长 索引索引的代价索引原理哪些列上适合使用索引主键索引唯一索引(UNIQUE)普通索引(INDEX)全文索引(FULLTEXT)[适用于MylSAM]索引使用 事务引出事务基本操作事务细节事务隔离级别查看和设置事务隔离级别全局修改事务隔离级别事务的acid特性 数据库CRUD语句单表查询表达式与as语句运算符排序使用group by子句对列进行分组使用having子句对分组后的结果进行过滤关键字DISTINCT 去重 函数统计函数统计 - count求和函数 - sum平均值 - avg最大值 max最小值 min 字符串函数数学函数时间日期函数加密和系统函数流程控制函数 单表查询加强分页查询 多表查询笛卡尔集自连接 子查询单行子查询多行子查询多行子查询中使用all与any操作符 多列子查询子查询临时表 表复制与去重合并查询union,union all外连接 存储引擎基本介绍主要的存储引擎/表类型特点如何选择表的存储引擎 视图基础概念视图的基本使用视图中可以在使用视图视图最佳实践 用户管理创建、删除用户用户修改密码mysql中的权限给用户授权回收用户权限权限生效指令 mysql命令备份数据库备份库的表恢复数据库 MySQL的三层结构 SQL语句分类
DDL:数据定义语句[create表库…] DML:数据操作语句[增加insert,修改update,删除delete] DQL:数据查询语句[select] DCL:数据控制语句[管理数据库比如用户权限grant revoke]
TCL:不是王牌电视。是事务控制语言包括事务提交commit;事务回滚rollback;
sql语句 数据库操作
创建数据库 在创建数据库表的时候为了规避关键字可以使用反引号解决 CREATE DATABASE CREATE #创建一个名称为hsp_db01的数据库
CREATE DATABASE hsp_db01;#创建一个utf8字符集的hsp_db02数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8;#创建一个使用utf8字符集并带校对规则的hsp_db03数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin;查看、删除数据库
#显示数据库
SHOW DATABASES;#显示数据库创建语句
SHOW CREATE DATABASE db_name;#数据库删除语句
DROP DATABASE [IF EXISTS] db_name;表操作
创建表 mysql常用数据类型(列类型) 枚举类型 字符串使用细节 char(4) //这个4表示字符数最大255不是字节数不管是中文还是字母都 是放四个按字符计算. varchar(4) //这个4表示字符数不管是字母还是中文都以定义好的表的编码 来存放数据 不管是中文还是英文字母都是最多存放4个是按照字符来存放的 char(4)和 varchar(4)这个4表示的是字符而不是字节不区分字符是汉字还是字母 什么时候使用char,什么时候使用varchar 1.如果数据是定长推荐使用char,比如md5的密码邮编手机号身份证号码等.char(32) 2.如果一个字段的长度是不确定我们使用varchar,比如留言文章 查询速度char varchar 在存放文本时也可以使用Text数据类型.可以将TEXT列视为 VARCHAR列注意Tet不能有默认值.大小0-216字节如果希望存放更多字可以选择 MEDIUMTEXT 0-2^24或者LONGTEXT 0~2^32 查询表、插入值
# 查询所有从t1表条件是nametom
SELECT * FROM t1 WHERE NAME tom;# 给t3表添加一个值
INSERT INTO t3 VALUES(127);# 显示表的结构和约束
desc 表名创建表练习
创建一个员工表emp 修改表 mysql约束 primary key(主键) primary key不能重复而且不能为null 一张表最多只能有一个主键但可以是复合主键比如idname) 复合主键 idname同时相同才不允许添加 not null(非空) unique(唯一) foreign key(外键) 1.外键指向的表的字段要求是primary key或者是 unique 2.表的类型是innodb,这样的表才支持外键 3.外键字段的类型要和主键字段的类型一致长度可以不同 4.外键字段的值必须在主键字段中出现过或者为null[前提是外键字段允许为null] 5.一旦建立主外键的关系数据不能随意删除了 外键示意图 check MySQL从8.0.16版本开始支持CHECK约束并使其生效。在MySQL 8.0.16之前的版本中虽然允许使用CHECK (expr)形式的检查约束语法但实际上解析之后会忽略该子句CHECK约束并不会对数据进行检查而是仅仅作为注释存在。 CHECK约束是一种完整性约束用于限制表中数据的取值范围可以确保插入或更新的数据满足特定的条件。它有助于维护数据的完整性防止非法数据的插入或更新并且通过在数据库层面进行数据验证可以减少应用程序中的错误处理逻辑提高性能。 在MySQL中CHECK约束可以分为基于列的CHECK约束和基于表的CHECK约束。基于列的CHECK约束应用于单个列限制该列的取值范围而基于表的CHECK约束则应用于整个表可以涉及多个列的组合条件。 因此如果需要使用CHECK约束来限制MySQL表中数据的取值范围请确保使用的MySQL版本为8.0.16或更高版本。 商店表设计 CREATE DATABASE shop_db;-- 商品goods
CREATE TABLE goods (goods_id INT PRIMARY KEY,goods_name VARCHAR (64) NOT NULL DEFAULT ,unitprice DECIMAL (10, 2) NOT NULL DEFAULT 0 CHECK (unitprice 1.0 AND unitprice 9999.99),category INT NOT NULL DEFAULT 0,provider VARCHAR (64) NOT NULL DEFAULT
);-- 客户customer
CREATE TABLE customer (-- 客户customer_id VARCHAR (8) PRIMARY KEY,-- 姓名name VARCHAR (64) NOT NULL DEFAULT ,-- 地址address VARCHAR (64) NOT NULL DEFAULT ,-- 邮箱email VARCHAR (64) UNIQUE NOT NULL,-- 性别sex ENUM (男, 女) NOT NULL,-- 身份证card_Id CHAR(18)
); -- 购买
CREATE TABLE purchase (-- 订单号order_id INT UNSIGNED PRIMARY KEY,-- 客户号customer_id CHAR(8) NOT NULL DEFAULT ,-- 商品号goods_id INT NOT NULL DEFAULT 0,-- 购买数量nums INT NOT NULL DEFAULT 0,FOREIGN KEY (customer_id) REFERENCES customer(customer_id),FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);自增长 1.一般来说自增长是和primary key配合使用的 2.自增长也可以单独使用[但是需要配合一个unique] 3.自增长修饰的字段为整数型的虽然小数也可以但是非常非常少这样使用) 4.自增长默认从1开始你也可以通过如下命令修改alter table 表名 auto increment xxx; 5.如果你添加数据时给自增长字段列指定的有值则以指定的值为准如果指定了自增长一般来说就按照自增长的规则来添加数据 索引 说起提高数据库性能索引是最物美价廉的东西了。不用加内存不用改程序不用调sql,查询速度就可能提高百倍干倍。 索引的代价 1.磁盘占用 2.对dml(update delete insert)语句的效率影响会对索引进行维护对速度有影响 索引原理 没有索引为什么会慢因为全表扫描 使用索引为什么会快形成一个索引的数据结构比如二叉树 哪些列上适合使用索引 1.较频繁的作为查询条件字段应该创建索引 select from emp where empno 1 2.唯一性太差的字段不适合单独创建索引即使频繁作为查询条件 select from emp where sex ‘男’ 3.更新非常频繁的字段不适合创建索引 select from emp where logincount 1 4.不会出现在WHERE子句中字段不该创建索引 主键索引 主键自动的为主索引(类型Primary key) 唯一索引(UNIQUE) 如果某列的值是不会重复的则优先考虑使用unique索引否则使用普通索引 普通索引(INDEX) 全文索引(FULLTEXT)[适用于MylSAM] 一般开发不使用mysql自带的全文索引而是使用全文搜索Solr ElasticSearch (ES) 索引使用
查询表是否有索引 添加、删除索引 事务 什么是事务 事务用于保证数据的一致性它由一组相关的dml语句组成该组的dl语句要么全部成功要么全部失败。如转账就要用事务来处理用以保证数据的一致性。 事务和锁 当执行事务操作时(dml语句)mysql:会在表上加锁防止其它用户改表的数据.这对用户来讲是非常重要的 引出事务 基本操作 回退事务 在介绍回退事务前先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务当结束事务时(commit),会自动的删除该事务所定义的所有保存点.当执行回退事务时通过指定保存点可以回退到指定的点。 提交事务 使用commiti语句可以提交事务.当执行了commiti语句子后会确认事务的变化、结束事务、删除保存点、释放锁数据生效。当使用commit语句结束事务子后其它会话【其他连接】将可以查看到事务变化后的新数据【所有数据就正式生效】 事务细节 1.如果不开始事务默认情况下dml操作是自动提交的不能回滚 2.如果开始一个事务你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态 3.你也可以在这个事务中还没有提交时创建多个保存点. 比如 savepoint aaa; 执行dml savepoint bbb; 4.你可以在事务没有提交前选择回退到哪个保存点 5.mysql的事务机制需要lnnoDB的存储引擎才可以使用MyISAM不支持 6.开始一个事务start transaction 或者 SET autocommitoff; 事务隔离级别 事务隔离级别介绍 1.多个连接开启各自事务操作数据库中数据时数据库系统要负责隔离操作以保证各个连接在获取数据时的准确性。通俗解释 2.如果不考虑隔离性可能会引发如下问题 脏读 不可重复读 幻读 查看和设置事务隔离级别 全局修改事务隔离级别 事务的acid特性 数据库CRUD语句 1.如果不使用where子句将删除表中所有数据。 2.Delete语句不能删除某一列的值可使用update设为null或者 3.使用delete语句仅删除记录不删除表本身。如要删除表使用drop table语句。drop table 表名 单表查询 表达式与as语句 运算符 排序 使用group by子句对列进行分组 使用having子句对分组后的结果进行过滤 关键字
DISTINCT 去重 函数
统计函数
统计 - count count(*)和count(列)的区别 *解释count(*)返回满足条件的记录的行数 count(列)统计满足条件的某列有多少个但是会排除为null的情况 求和函数 - sum ■统计一个班级数学总成绩 ■统计一个班级语文、英语、数学各科的总成绩 ■统计一个班级语文、英语、数学的成绩总和 ■统计一个班级语文成绩平均分 注意sum仅对数值起作用否则没有意义。 注意对多列求和“”号不能少。 平均值 - avg 最大值 max 最小值 min 字符串函数 数学函数 rand()返回一个随机浮点值y,范围在0到1之间即其范围为0≤V≤1.0)。若已指定一个整数参数W,则它被用作种子值用来产生重复序列。 1.如果使用rand()每次返回不同的随机数在0≤v≤1.0 2.如果使用 rand(seed)返回随机数范围0≤v≤1.0如果seed不变该随机数也不变了 时间日期函数 加密和系统函数 流程控制函数 单表查询加强 分页查询 多表查询
笛卡尔集 自连接 自连接是指在同一张表的连接查询 子查询
单行子查询 多行子查询 多行子查询中使用all与any操作符 多列子查询 请思考如何查询与allen的部门和岗位完全相同的所有雇员并且不含allen本人 (字段1字段2…)(select 字段1字段2 from…) 子查询临时表 查询每个部门的信息包括部门名编号地址和人员数量 表复制与去重 合并查询union,union all 外连接
1.左外连接(如果左侧的表完全显示我们就说是左外连接) 2.右外连接(如果右侧的表完全显示我们就说是右外连接) 存储引擎
基本介绍 InnoDB存储引擎支持事务、行级锁定和外键 MRG_MYISAM存储引擎收集相同的MyISAM表 MEMORY存储引擎基于哈希的存储在内存中对临时表有用 BLACKHOLE存储引擎写入到这的数据会被销毁 MYISAM存储引擎不支持事务 CSV存储引擎CSV存储引擎 ARCHIME存储引擎存档类型的存储引擎 显示当前数据库支持的存储引擎
show engines;主要的存储引擎/表类型特点 1.MyISAM不支持事务、也不支持外键但其访问速度快对事务完整性没有要求 2.InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起 MyISAM存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留 数据和索引。 3.MEMORY?存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应 一个磁盘文件。MEMORY类型的表访问非常得快因为它的数据是放在内存中的 并且默认使用HASH索引。但是一旦服务关闭表中的数据就会丢失掉表的结构还 在。 如何选择表的存储引擎 修改存储引擎
-- 指令修改存储引擎
ALTER TABLE t29 ENGINE INNODB;视图
看一个需求 基础概念 1.视图是一个虚拟表其内容由查询定义。同真实的表一样视图包含列其数据来自对应的真实表基表 2.视图和基表关系的示意图 对视图的总结 1.视图是根据基表来创建的视图是虚拟的表 2.视图也有列数据来自基表 3.通过视图可以修改基表的数据 4.基表的改变也会影响到视图的数据 视图的基本使用
create view 视图名 as select 语句 创建视图 alter view 视图名 as select 语句 修改视图结构 SHOW CREATE VIEW 视图名 查看创建视图的指令 drop view 视图名1视图名2 删除视图 视图中可以在使用视图 视图最佳实践 用户管理 原因当我们做项目开发时可以根据不同的开发人员赋给他相应的mysql操作权限所以mysql数据库管理人员(root)根据需要创建不同的用户赋给相应的权限供人员使用 不同的数据库用户登录到DBMS后根据相应的权限可以操作的数据库和数据对象表视图触发器都不一样 user表的重要字段说明 创建、删除用户 1.在创建用户的时候如果不指定Host,则为%%表示表示所有IP都有连接权限 create user xxx; 2.你也可以这样指定 create user’xx’‘192.168.1.%‘表示Xxx用户在192.168.1.*的ip可以登 录mysql 3.在删除用户的时候如果host不是%需要明确指定用户’‘host值 DROP USER jack; – 默认就是DROP USER ‘jack’’%’ 用户修改密码 mysql中的权限 给用户授权 回收用户权限 权限生效指令
FLUSH PRIVILEGES;mysql命令
备份数据库 要在Dos下执行mysqldump指令其实在mysql安装目录\bin mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n d:\\文件名.sql备份库的表
mysqldump -u 用户名 -p 数据库 表1 表2 表n d:\\文件名.sql恢复数据库 进入Mysql命令行再执行 source 文件名.sql
