MySQL-学习笔记

最新推荐文章于 2024-06-15 16:09:53 发布
最新推荐文章于 2024-06-15 16:09:53 发布
阅读量212 收藏
点赞数
分类专栏： JAVA 文章标签： mysql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zjj960908/article/details/126289889
版权

  • SQL语句分类

DML（Data Manipulation Language）数据操纵语言
如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

DDL（Data Definition Language）数据库定义语言
如：create table之类

DCL（Data Control Language）数据库控制语言
如：grant、deny、revoke等，只有管理员才有相应的权限

DQL（Data Query Language）数据库查询语言
如: select 语法
SQL不区分大小写

  • 数据库备注

  1.  命名规则 ：字母开头，长度不能超过30个字符（不同数据库，不同版本会有不同），不能使用SQL的保留字，如where,order,group，只能使用如下字符az、AZ、0~9、$ 等，多个单词用下划线隔开
  2. 字符：char长度固定，不足使用空格填充，char(11)存储abc，占11位。查询速度极快但浪费空间最多容纳2000个字符，varchar变长字符串，最多容纳4000个字符，archar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2（以utf8编码计算的话，一个汉字在u8下占3个字节）
  3. 数字：tinyint,int整数类型，float,double小数类型,numeric(5,2) decimal(5,2)—表示总共5位,其中可以有两位小数
  4. 时间：date 包含年月日,time时分秒,datetime包含年月日和时分秒,timestamp时间戳(从1970年1月1日到指定日期的毫秒数)
  5. 字段约束: 主键约束(key),唯一且不能为空;非空约束(not null),值不能为空，但可以重复，唯一约束（unique），值必须是唯一的（即不能重复），但null至可重复，默认约束（deafult）默认值，检查约束（check),外键约束（foreign key）

备注：插入时由于中文乱码报错，报错字段太长，可执行命令 set names gbk;

mysql -uroot -p  连接到数据库服务器

  •  创建数据库

  create database bd1 DEFAULT CHARACTER SET utf8；创建数据库bd1

  • 数据库常用操作

show databases;     查询所有数据库

drop database db1; 删除数据库db1

use db2 ;使用数据库

char ，长度固定，不足使用空格填充，最多容纳2000个字符，空间浪费但查询快，varchar长度固定，不足使用空格填充，最多容纳2000个字符，节省空间，查询稍慢

tinyint 相当于java中byte,只占用一个字节,用的不多,1个字节

smallint 相当于java中的short,2个字节

int整数类型 ,整数中默认都爱使用这个类型,占4个字节

bigint 相当于java中的long类型,8个字节

float,double小数类型

float 4字节的单精度的浮点数

double 8字节的双精度的浮点数

salary double(10,2) 表示double占10个位,其中小数位是2位

numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中有两位小数

decimal和numeric表示精确的整数数字

date 包含年月日 

time时分秒

datetime包含年月日和时分秒

timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

blob 二进制数据，可以存放图片、声音，容量4g

  • 创建表

创建学生信息表

 create table student(
     sno int primary key  auto_increment,
     name varchar(50),
     sex varchar(10)
     ); 

  • 表的常用操作

alter table student add column tel varchar(11);   表中插入列tel

desc student; 查看表结构

show tables; 查看所有表

drop table user;删除表

insert into student values(1,'张三','女','12345678911');向表中插入记录

update student set sex = '男' where sno = 1; 更新表记录字段信息

select * from student; 查询表记录信息

delete from student where sno = 3;删除表记录信息

select * from student order by sno desc;查询表记录信息并按sno降序排序

select count(*) from student; 查询表记录总条数

备注：主键自增策略（auto_increment)

  •  基础函数

SELECT loc ,LENGTH(loc) FROM dept;  -- 数据长度
SELECT dname,UPPER(dname) FROM dept -- 数据转大写
SELECT dname,SUBSTR(dname,2) FROM dept; -- 从第2为开始截取[2,length]
SELECT dname,SUBSTR(dname,1,4) FROM dept; -- 截取[1,4]
SELECT dname,CONCAT(dname,'123') FROM dept; -- 拼接数据
SELECT dname,CONCAT(dname,'123' , 'hello') FROM dept;-- 可以拼接多个值
SELECT dname,REPLACE(dname,'a','666') FROM dept -- 把a字符替换成666
SELECT ename,sal,comm,IFNULL(comm, 0)+sal comm FROM emp; #判断,如果comm是null,用0替换
SELECT comm,ROUND(comm) FROM emp;-- 直接四舍五入取整
SELECT comm,ROUND(comm,1) FROM emp; -- 四舍五入并保留一位小数
SELECT comm,CEIL(comm) ,FLOOR(comm) FROM emp; -- ceil向上取整,floor向下取整

SELECT NOW() -- 年月日 时分秒
SELECT CURDATE() -- 年月日
SELECT CURTIME() -- 时分秒
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) FROM emp ;

 

  •  条件查询&聚合分组查询

SELECT DISTINCT loc FROM dept;  -- 去重
SELECT * FROM emp WHERE ename LIKE 'l__';#查询出以l开头的后面有两个字符的的员工信息
SELECT * FROM emp WHERE ename LIKE '%a%';#查询出名字包含a的员工信息
SELECT * FROM emp WHERE comm IS NULL;#查询奖金为null的记录
SELECT * FROM emp WHERE comm IS NOT NULL;#查询奖金不为null的记录
SELECT * FROM emp WHERE sal BETWEEN 3000 AND 10000;#等效
SELECT * FROM emp LIMIT 2;#列出emp表中前两条记录

SELECT * FROM emp LIMIT 3,2;#3表示第四条记录开始 2表示展示2条记录
SELECT * FROM emp ORDER BY sal;#查询出的结果按照sal升序排序(从小到大)--默认的排序方式
SELECT * FROM emp ORDER BY sal DESC;#查询出的结果按照sal降序排序(从大到小)
SELECT * FROM emp WHERE hiredate<'2015-1-1';#入职时间早于(<)2015年1月1日
SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2015 AND 2019;#2015年到2019年入职的员工信息
SELECT empno,ename,sal*13+IFNULL(comm,0)*13 年薪 FROM emp;


SELECT COUNT(*) FROM emp;#获取总记录数
SELECT COUNT(1) FROM emp;#效果和*一样
SELECT MAX(sal) FROM emp;#求sal中的最大值
SELECT MIN(sal) FROM emp;#求sal中的最小值
SELECT SUM(sal) FROM emp;#求sal的总和
SELECT AVG(sal) FROM emp;#求sal的平均值
SELECT deptno,MAX(sal),AVG(sal) FROM emp GROUP BY deptno;#查询每个部门的最高薪资和平均薪资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) < 8000;#平均工资小于8000的部门
SELECT deptno,COUNT(*)FROM emp GROUP BY deptno;#每个deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp GROUP BY deptno  HAVING COUNT(deptno)>1;#次数多于1次的deptno
#1.	where可以使用在增删改查语句中,但是having只能用于查询语句
#2.	where条件在分组前进行的,having是在分组之后执行的,所以where会比having先执行
#3.	where子句中不能包含聚合函数,但是having可以包含聚合函数


CREATE VIEW emp1 AS SELECT * FROM emp WHERE YEAR(hiredate) BETWEEN 2017 AND 2019;  #创建视图

CREATE TABLE tb_user_address (
user_id INT PRIMARY KEY,
address VARCHAR(200),
FOREIGN KEY(user_id) REFERENCES tb_user(id) #外键
);

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

 事务4个特性ACID：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。最小的单位,不可以再被分割

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

银行完成转账操作,内部有非常多的规则

A:1000元 1000-100 900

B:1000元 1000+100 1100

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

A 1000 1000-100 900

B 1000 1000-100 900

A和B同时对C进行转账

C 1000 1000+100+100 1100

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务隔离分为不同级别，包括

读未提交（Read uncommitted）

    读到事务没有提交的数据, 安全性最差,可能发生并发数据问题,性能最好,不用

读提交（read committed）

    读到事务已经提交的数据, Oracle默认的隔离级别

可重复读（repeatable read）

    可以重复的读到一个数据, MySQL默认的隔离级别,安全性较好,性能一般

串行化（Serializable）

    表级锁，读写都加锁，当一个事务在操作一个数据时,另一个事务不得干预,效率低下,安全性高,不能并发,不用

  • 索引 

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。单值索引：一个索引只包括一个列，一个表可以有多个列,也就是可以有多个单值索引。唯一索引：索引列的值必须唯一(不能重复)，但允许有空值；主键会自动创建唯一索引。复合索引：一个索引同时包括多列(一个表中有三个列要加索引,一次加三列)，当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

SHOW INDEX FROM dept;#查看索引，主键会自动创建索引
CREATE INDEX loc_index ON dept(loc); #创建单值索引
CREATE UNIQUE INDEX bindex ON dept(loc);-- 创建唯一索引
CREATE INDEX fuhe_index ON dept (dname, loc) #创建复合索引
EXPLAIN 
SELECT * FROM dept WHERE loc='二区'; #复合索引失效
EXPLAIN
SELECT * FROM dept WHERE dname='accounting'; #复合索引生效

  •  表关联&嵌套查询

SELECT * FROM emp e INNER JOIN dept d ON e.deptno =d.deptno; #内连接
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno =d.deptno;#左外连接
SELECT * FROM emp e RIGHT JOIN dept d ON d.deptno =e.deptno; #右外连接
SELECT * FROM EMP WHERE DEPTNO=( SELECT DEPTNO FROM EMP WHERE ENAME = 'tony'); #嵌套查询
SELECT * FROM emp WHERE job IN ( SELECT job FROM emp WHERE ename IN ('hana' ,'leo') );#嵌套查询

 SQL的执行顺序

  1.  FROM [left_table] 选择表
  2. ON <join_condition> 链接条件
  3. (<join_type> JOIN <right_table> 链接
  4. WHERE <where_condition> 条件过滤
  5.  GROUP BY <group_by_list> 分组
  6. AGG_FUNC(column or expression),... 聚合
  7. HAVING <having_condition> 分组过滤
  8. SELECT (9) DISTINCT column,... 选择字段、去重
  9. ORDER BY <order_by_list> 排序
  10. LIMIT count OFFSET count; 分页

  • SQL优化 

  1.  询SQL尽量不要使用select *，而是具体字段，* 查询较慢且不会使用索引
  2. 避免在where子句中使用or来连接条件 ,or很可能会使索引失效
  3. 使用varchar代替char, char容易造成内存空间浪费
  4. 数值替代字符串类型存储更优
  5. 查询尽量避免返回大量数据返回，数据量较大时建议分页查询
  6. 使用explain分析你SQL执行计划，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引
  7. 优化like语句,模糊查询中使用like很可能始查询索引失效
  8. 索引不宜太多，一般5个以内

  9. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率

  10. 索引不适合建在有大量重复数据的字段上，因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了
  11. 避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
  12. 去重distinct过滤字段要少
  13. 批量插入性能提升，mysql默认每一条sql都是一个事务单元,也就是每一条sql执行了,都需要开启事务,关闭事务,批量提交,只需要提交关闭一次事务INSERT INTO student (id,NAME) VALUES(4,'齐雷'),(5,'刘昱江');

  14. 批量删除优化，避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。可分批执行.

  15. 提高group by语句的效率

    反例：先分组，再过滤
select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';
正例：先过滤，后分组
select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;
    1. 最左特性

    创建复合索引，也就是多个字段

    ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

    满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

    EXPLAIN

    SELECT * FROM student WHERE NAME='陈子枢'

    没有出现左边的字段，则不满足最左特性，索引失效

    EXPLAIN

    SELECT * FROM student WHERE salary=3000

    复合索引全使用，按左侧顺序出现 name,salary，索引生效

    EXPLAIN

    SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000

    虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

    EXPLAIN

    SELECT * FROM student WHERE salary=3000 AND NAME='陈子枢'

    1. 适当使用索引，如where条件时候或排序需要排序的字段
    2. 当需要使用多表关联时，优先使用inner join,使用left join时尽量把数据量小的表放左边，同理使用right join也是
    3. 尽量使用union 代替union all, union：会对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序, union all：对两个结果集进行并集操作，包括重复行，不会对结果进行排序。

哈哈~haha
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL-Notes学习笔记
01-27
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并...
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
SQL/Oracle——第九章 视图、索引、序列
cmpter的博客
11-26 377
视图 是一个虚拟表，所有看到的东西都来自于原表 conn system/rjxy123456;--必须在system下才能授权 grant create view to scott; --给scott账户授予了一个创建视图的权限 conn scott/tiger; create or replace view emp_view as select empno,ename,job,deptno f...
mysql 索引类型及创建索引（普通索引、唯一索引、主键索引、复合索引、全文索引）
yygr的博客
04-07 5436
https://blog.csdn.net/donghaixiaolongwang/article/details/60749314?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2%7Edefault%7ECTRLIST%7ERate-1.pc_relevant_default&depth_1-utm_source=distribute.pc_relevant_t0.none-ta
mysql基础解析
m0_56420338的博客
02-15 700
mysql基础解析
MySQL中的索引
daqi1983的博客
10-28 3620
索引
MYSQL数据库对象命名规范
qq_39240694的博客
11-04 2623
数据库对象 数据库对象是数据库的组成部分，常见的有以下几种：表（Table ）、索引（Index）、视图（View）、图表（Diagram）、缺省值（Default）、规则（Rule）、触发器（Trigger）、存储过程（Stored Procedure）、 用户（User）等。命名规范是指数据库对象如数据库（SCHEMA）、表（TABLE）、索引（INDEX）、约束（CONSTRAINTS）等的命名约定。 数据库对象全局命名规范 1、命名使用具有意义的英文词汇，词汇中间以下划线分隔 2、命名只能使
MySQL - 学习笔记 - SELECT语句
湖边的小屋圣迹
02-05 485
MySQL - 学习笔记 - SELECT语句语句的执行顺序参考资料 语句的执行顺序 参考资料 官方文档：MySQL 5.7 Reference Manual / … / SELECT Statement / 13.2.9 SELECT Statement MSDN：MS SQL Server SELECT 语句的逻辑处理顺序 菜鸟教程：MySQL 查询数据 程序猿小旺：MySQL中SQL查询语句的执行顺序，你知道吗？ ...
MySQL - 学习笔记 - 一行转多行
湖边的小屋圣迹
02-04 906
MySQL - 学习笔记 - 一行转多行应用场景工欲善其事必先利其器1. SUBSTRING_INDEX(str, delim, count)2. 内连接：INNER JOIN 简写 JOIN3. 辅助表实现参考资料 应用场景 老系统 接手一个老系统，没有单独的图片表。 所有上传的图片直接将路径存在了业务表中。 多张图片路径以;分隔。 新需求 将所有业务表中的图片路径files保存到图片表 t_images 的 url 中。 业务表中原路径 files 改为 t_images 中的主键。 工欲善其事
MySQL - 学习笔记 - 查询结果插入到另一个表
湖边的小屋圣迹
02-07 933
MySQL - 学习笔记 - 查询结果插入到另一个表CREATE TABLE 查询结果创建表INSERT INTO 查询结果插入表 CREATE TABLE 查询结果创建表 DROP TABLE IF EXISTS `目标表`; -- 如果目标表存在先删除（根据业务需要） CREATE TABLE `目标表` AS SELECT * FROM `源数据表`; INSERT INTO 查询结果插入表 -- 先创建表 （可选：自己根据业务需要判断） DROP TABLE IF EXISTS `目标表`; C
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
MySQL5.7学习笔记.pdf
09-07
MySQL 5.7学习笔记 数据库概述： 数据库（Database）是一种存储数据的“仓库”，其本质是一个文件系统，保存了一系列有组织的数据。数据库管理系统（Database Management System）是一种操纵和管理数据库的大型...
关于Mysql的学习笔记
06-24
这是我看B站Mosh讲Mysql视频做的一些笔记，也是第一次做笔记，用的是Typora。因为是初次使用Markdown格式做笔记，所以还不知道picgo这种东西，所以，截图都没有了，哈哈哈，凑活看吧。
【MySQL】服务器配置和管理
p_fly的博客
06-09 809
MySQL服务器通常说的是mysqld程序。mysqld 是 MySQL 数据库服务器的核心程序，负责处理客户端的请求、管理数据库和执行数据库操作。管理员可以通过配置文件和各种工具来管理和监控 mysqld 服务器的运行本文将介绍下面三个内容：服务器启动选项：可以在命令行和配置文件中指定，用于配置服务器的行为和特性。服务器系统变量：反映了启动选项的当前状态和值，其中一些变量可以在服务器运行时修改。服务器状态变量：包含了关于运行时操作的计数器和统计信息。
【MySQL】（基础篇十二） —— 子查询
最新发布
dao_cao_renshuai的博客
06-15 287
本文介绍什么是子查询以及如何使用它们。
Mysql--基础知识点--86--慢查询
Chasing__Dreams的博客
06-11 783
mysql 慢查询
【MySQL】索引（上）
开心星人的博客
06-11 773
索引是一种用于快速查询和检索数据的数据结构，排序好的数据结构。 优点：加快检索速度；通过创建唯一性索引，可以保证行数据的唯一性；通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 缺点：创建和维护索引需要耗费时间，本身存储也要耗费一定空间不同引擎对索引的支持情况 哈希表、有序数组、B+树、B树、红黑树，二叉树哈希表：只适用于等值查询的场景，用这种索引做不了范围查询，必须全表扫描。查询效率高有序数组：有序数组在等值查询和范围查询场景中的性能就都非常优秀，但是一旦更新数据就得挪动后面的元素，成本太高
LeetCode //MySQL - 175. Combine Two Tables
Made in Code
06-13 770
【代码】LeetCode //MySQL - 175. Combine Two Tables。
Ubuntu修改MySQL的tmpdir参数失败的解决方法
q2972112的博客
06-15 189
但修改后重启MySQL服务时提示查看log文件也没发现相关提示； 经过测试（删除修改后的参数即可启动MySQL服务成功），推测应该是新的临时文件夹出现了问题，但是权限也给了，为啥呢？ 解决方法 搜索得知1，Linux内核安全模块可能限制MySQL写入新的临时目录。配置文件定义程序的文件访问权限，而的配置文件可能不允许写入指定的新临时目录。 要解决此问题，需要更新的配置文件，以允许MySQL写入新的临时目录。 https://stackoverflow.com/questions/1894465
MySQL架构学习笔记
06-13
MySQL是一种关系型数据库管理系统，其架构主要由以下几个组件构成： 1. 连接器（Connection Manager）：负责与客户端建立连接、进行身份认证、维护连接池等。 2. 查询缓存（Query Cache）：缓存查询结果，避免重复...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值