Mysql个人总结

前言又来水字数啦这次主要讲一下MySQL的常用概念难点的就必须上项目讲解了而且比较基础面试基本都会问一些用的话不少优化都从这里入手执行命令中括号多为可选内容不要直接中括号操作数据库1、创建数据库CREATE DATABASE [IF NOT EXISTS] 数据库名;2、删除数据库DROP DATABASE [if EXISTS] 数据库名;3、使用数据库--如果表名或者字段名是特殊字符则需要带use 数据库名;4、查看数据库SHOW DATABASES;表操作建表CREATE TABLE IF NOT EXISTS student( id INT(4) NOT NULL AUTO_INCREMENT COMMENT 学号, name VARCHAR(30) NOT NULL DEFAULT 匿名 COMMENT 姓名, pwd VARCHAR(20) NOT NULL DEFAULT 123456 COMMENT 密码, sex VARCHAR(2) NOT NULL DEFAULT 女 COMMENT 性别, birthday DATETIME DEFAULT NULL COMMENT 出生日期, address VARCHAR(100) DEFAULT NULL COMMENT 家庭住址, email VARCHAR(50) DEFAULT NULL COMMENT 邮箱, PRIMARY KEY (id) )ENGINEINNODB DEFAULT CHARSETutf8括号内分别对应列名‘id’类型INT括号内是定最大长度字节默认属性【字段属性UnSigned无符号的声明了该列不能为负数ZEROFILL0填充不足位数的用0来填充 , 如int(3),5则为005Auto_InCrement通常理解为自增自动在上一条记录的基础上默认1通常用来设计唯一的主键必须是整数类型可定义起始值和步长当前表设置步长(AUTO_INCREMENT100) : 只影响当前表SET auto_increment_increment5 ; 影响所有使用自增的表(全局)NULL 和 NOT NULL默认为NULL , 设置为NOT NULL , 则该列必须有值DEFAULT默认的用于设置默认值例如,性别字段,默认为男 , 否则为 “女” ; 若无指定该列的值 , 则默认值为男的值】主键PRIMARY KEY(id)快速搜索创建索引使用【另外在实际操作中将一个表的值放入第二个表来表示关联所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时第二个表中保存这些值的属性称为外键(foreign key)也在此定义,另一个表classKEY FK_birthday (birthday),//起别名CONSTRAINT FK_birthday FOREIGN KEY (birthday) REFERENCES class(stu_birth)。-- 创建表的时候没有外键关系ALTER TABLE student ADD CONSTRAINT FK_birthday FOREIGN KEY(birthday) REFERENCES class(stu_birth);都是物理外键数据库级别的外键不建议使用避免数据库过多造成困扰】最后comment别名,到这里基本结束但面试会问你引擎ENGINEINNODBInnodb引擎Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。MyIASM引擎(原本Mysql的默认引擎)不提供事务的支持也不支持行级锁和外键。MEMORY引擎所有的数据都在内存中数据的处理速度快但是安全性不高。DEFAULT CHARSETutf8是识别中文字符集创建完查看是否正确SHOW TABLES;-- 查看所有表DESC 表名;-- 显示表的具体结构需要修改修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]数据库的表操作基础中的基础大家记得增删改查四字真言就行其中查询是关键称为DQLData QueryLanguage数据查询语言其他三称为DMLData Manipulation Luaguge数据操作语言DML1、添加增INSERT INTO 表名([字段1,字段2..])VALUES(值1,值2..),[(值1,值2..)..];2、删除删DELETE FROM 表名 [WHERE 条件];3、修改改UPDATE 表名 SET 字段1值1,[字段2值2...] WHERE 条件[];DQL单表查询select 分组函数分组后的字段from 表【where 筛选条件】group by 分组的字段【having 分组后的筛选】【order by 排序列表asc升序/desc降序】like “ ”【模糊查询百分号%表示任意长度的任意字符序列。下划线_表示任意单个字符】limit offset,pagesize;//offset偏移量展示结果行数页面大小行数lag(column_name,offset,default_value)行前lead(column_name,offset,default_value)行后;字段可加聚合函数函数描述max最大值min最小值sum和avg平均值count计数rank排名窗口函数over (partition by... order by )多表查询直接join是去掉符合where条件项多表合成一张表self join一张表里列相关例如上下级SELECTe.name AS 员工姓名,m.name AS 经理姓名FROM employees eLEFT JOIN employees m ON e.manager_id m.employee_id;结果集合查询union和union all都是两个查询结果合并但后者不去重子查询在where子句中嵌套一个子查询语句前接exists或者not exists判断子条件是否有结果SELECT customer_id, customer_nameFROM customers cWHERE EXISTS (SELECT 1FROM orders oWHERE o.customer_id c.customer_id);接any和all是逻辑判断SELECT ProductName FROM ProductsWHERE ProductID ANY (SELECT ProductIDFROM OrderDetailsWHERE Quantity 10);相当于不等于不太常用补充说明可查询后结果直接dmlINSERT INTOtarget_tableSELECT * FROMsource_tableWHEREcondition;条件也有其他写法SELECT ProductName, Price,CASEWHEN Price 20 THEN Low CostWHEN Price BETWEEN 20 AND 50 THEN Medium CostELSE High CostEND AS PriceCategoryFROM Products;加密md5全数据型不建议一般是在后台对单表字段加密UPDATE testMD5 SET pwdMD5(pwd) WHERE id1;UPDATE testMD5 SET pwdMD5(pwd);前些天发现的一个小程序可以练习一下InsCode - 让你的灵感立刻落地事务1、特性ACID原子性Atomicity 原子性是指事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生。一致性Consistency 事务前后数据的完整性必须保持一致。隔离性Isolation 事务的隔离性是多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离。持久性Durability 事务一旦被提交则不可逆被持久化到数据库中接下来即使数据库发生故障也不应该对其有任何影响2、隔离事务隔离级别脏读不可重复读幻读读未提交read-uncommitted是是是读已提交read-committed否是是可重复读repeatable-read否否是串行化serializable否否否串行化读写都要加锁可重复读仅写操作DML加锁读已提交Read CommittedMySQL的读已提交实际是语句级别快照。与可重复读级别主要有两点不同1获得ReadView的时机。每个语句开始执行时获得ReadView可见性判断是基于语句级别的ReadView。读的策略与可重复读类似。2写锁的使用方式。这里不需要GAP LOCK只使用记录锁。并且事务只持有被UPDATE/DELETE记录的写锁可重复读需要保留全部写锁直到事务结束而读已提交只保留真正更改的。读未提交Read Uncommitted读最新的数据不管这条记录是不是已提交。不会遍历版本链少了查找可见的版本的步骤。这样可能会导致脏读。对写仍需要锁定策略和读已提交类似避免脏写。3、事务执行流程1️⃣ 关闭自动提交SET autocommit0;2️⃣ 事务开启START TRANSACTION -- 标记一个事务的开始从这个之后的sql都在同一个事务内3️⃣ 成功则提交失败则回滚-- 提交持久化成功COMMIT-- 回滚回到原来的样子失败ROLLBACK4️⃣ 事务结束SET autocommit1; -- 开启自动提交​5️⃣ 其他操作SAVEPOINT 保存点名; -- 设置一个事务的保存点ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点RELEASE SAVEPOINT 保存点名; -- 撤销保存点索引可以提升批量操作的检索效率主键索引-- 创建表的时候指定主键索引CREATE TABLE tableName(......PRIMARY INDEX (columeName))-- 修改表结构添加主键索引ALTER TABLE tableName ADDPRIMARY INDEX(columnName)普通索引-- 直接创建普通索引CREATEINDEXindexName ON tableName (columnName)-- 创建表的时候指定普通索引CREATE TABLE tableName(......INDEX [indexName] (columeName))-- 修改表结构添加普通索引ALTER TABLE tableName ADD INDEX indexName(columnName)唯一索引说是唯一但可多个-- 直接创建唯一索引CREATEUNIQUE INDEXindexName ON tableName(columnName)-- 创建表的时候指定唯一索引CREATE TABLE tableName(......UNIQUE INDEX [indexName] (columeName));-- 修改表结构添加唯一索引ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)全文索引-- 增加一个全文索引ALTER TABLE student ADDFULLTEXT INDEXStudentName(StudentName);-- EXPLAIN 分析sql执行的情况EXPLAIN SELECT * FROM student; -- 非全文索引EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST(d);权限-- 创建用户CREATE USER zsr IDENTIFIED BY 123456-- 删除用户DROP USER zsr-- 修改当前用户密码SET PASSWORD PASSWORD(200024)-- 修改指定用户密码SET PASSWORD FOR zsr PASSWORD(200024)-- 重命名RENAME USER zsr to zsr2-- 用户授权(授予全部权限,除了给其他用户授权)GRANT ALL PRIVILEGES on *.* TO zsr2-- 查询权限SHOW GRANTS FOR zsr-- 查看root用户权限SHOW GRANTS FOR rootlocalhost-- 撤销权限REVOKE ALL PRIVILEGES ON *.* FROM zsr备份保证重要的数据不丢失、数据转义最好留个备份直接拷贝物理文件.sqlMySQL数据表以文件方式存放在磁盘中,每次对应数据库右键转储为sql文件需要时右键执行即可当然自带的备份功能也行表间关系范式逐级加深逐级包含1NF:列属性间原子性符合即可2NF:不存在部分依赖例子产品和订单订单包含所有产品符合2NF否则不符合3NF不存在传递依赖主字段不冗余例子每个订单对应一种商品唯一依赖BCNF不传递依赖也不部分依赖数仓保密数据复制到仓库中操作创建DELIMITER //CREATE PROCEDURE GetCustomersByCityIN City VARCHAR(50),IN PostalCode VARCHAR(10)/*sqlserver是参数前加使用也一样*/BEGINSELECT * FROM CustomersWHERE City City AND PostalCode PostalCode;END //DELIMITER;使用CALL GetCustomersByCity(London);分层命名规范及作用操作层表名通常为ods_[业务域]_[表名]_inc(增量基本是天dsyyymmmddd) 或ods_[业务域]_[表名]_all(全量)明细层数据清洗 处理空值NULL、异常值、错误数据。码值转换 将业务系统的数字码值转换为可读的中文或英文描述如 status1 - status_desc待支付。维度退化 (Dimensional Degeneration) 将常用的维度属性如用户姓名、商品名称、类目直接关联到事实表中生成宽表减少上层JOIN。字段规范化 统一日期格式、金额单位、枚举值标准。数据整合 将来自不同源的同一实体的数据合并如App订单和Web订单合并为统一订单表。适度聚合 可以做一些非常轻度的、不可逆的聚合如计算单条订单的实付金额 原价 - 折扣但不涉及跨记录的统计。————————————————版权声明本文为CSDN博主「走过冬季」的原创文章遵循CC 4.0 BY-SA版权协议转载请附上原文出处链接及本声明。原文链接https://blog.csdn.net/winterPassing/article/details/154397376表名通常为dwd_[业务域]_[主题]_[子主题]_inc。服务层公共指标 计算一些高频使用的、跨业务的指标如用户日行为汇总dws_user_daily_agg商品销售日汇总dws_sku_daily_agg区域销售月汇总dws_region_monthly_agg维度组合 按 user_id, ds或 category_id, province, ds 等组合进行GROUP BY。避免过度汇总 不做过于复杂的、特定场景的聚合保持一定的灵活性。————————————————版权声明本文为CSDN博主「走过冬季」的原创文章遵循CC 4.0 BY-SA版权协议转载请附上原文出处链接及本声明。原文链接https://blog.csdn.net/winterPassing/article/details/154397376表名通常为dws_[业务域]_[统计粒度]_[汇总周期]_agg。应用层表名为业务含义ads_gmv_daily_report,连接这里讲java其余语言引入对应库import java.sql.*;DriverManagerDriverManager驱动管理//1.加载驱动Class.forName(com.mysql.cj.jdbc.Driver);本质上执行DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());//3.连接,获得数据库对象connectionConnection connection DriverManager.getConnection(url, username, password);connection代表数据库因此可以设置事务自动提交事务回滚等然后执行就行statement.executeQuery();//查询操作,返回结果 statement.execute();//执行sql statement.executeUpdate();//用于增删改,返回受影响的行数ResultSet查询的结果集封装了所有查询的结果结语本人开发水平不够其实就比小白好不多少了遇到问题还是靠搜索引擎专门独立开发一个整体应用想想都头疼毕竟看我这种新手的项目还不如直接根据课题找大佬们的项目先拿来走流程而且代码从零开始太恐怖了我现在基本都是在做附加功能前端还好后端尤其是java这块真的是没彻底由自己上手过毕竟各种依赖和注解就够呛了前端只要找准框架然后找好UI库再逐个功能实现然后css调节样式最后缓存和路由管理优化就行了下篇文章估计会从这个角度出发。万一哪天被赶鸭子上架学了和做了后端项目就再试着讲讲现在这环境后端新人是真难入啊而且工作后发现沟通才是大头所以万一哪天兴起做视频烦请大家不要嫌弃看看。