SQL个人易忽略

最新推荐文章于 2024-03-29 21:49:42 发布
最新推荐文章于 2024-03-29 21:49:42 发布
阅读量375 收藏
点赞数
分类专栏： 查漏补缺 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_48848473/article/details/119854649
版权

mysql个人易忽略的点

一，表创建好后添加操作

1.表中添加列元素：

alter table user2 add column address varchar(20);（修改表）

2.表中某属性修改：

alter table user2 change address varchar(10);

3.表中某属性添加：

alter table user2 add address not null default '北京';

4.表添加主键：

alter table user2 add primary key(id);

二，API使用

1.substr索引

#substr(1,2,3)截取字符串-1是字段名2是开始位置3是总长度
SELECT dname,SUBSTR(dname,2),SUBSTR(dname,2,3) FROM dept

这里的开始位置的索引是从1开始的，而非一般的从0开始

三，条件查询

1.like模糊查询

#like 像,模糊查询,通常配合%作为占位符
#查询名字里包含a的员工信息
SELECT * FROM emp WHERE
#ename like '%a%' #包含a,动态匹配0~n个字符
ename LIKE 'l%' #以l开头,动态匹配0~n个字符
SELECT * FROM emp WHERE job LIKE '_总'#_通配一个字符 表示某总，总之前必须也只能有一个字符

2.判断是否为null

#查询mgr是null的员工信息
SELECT * FROM emp 
WHERE mgr IS NULL  #是null
#WHERE mgr IS not NULL #不是null

3.between包含问题

#查询sal在3000到10000内的员工信息
SELECT * FROM emp WHERE 
#sal > 3000 and sal< 10000
sal BETWEEN 3000 AND 10000 #包含[3000,10000]

between包头包尾
4.order by排序

#order by 排序
SELECT * FROM emp ORDER BY sal ASC #默认ASC,升序
SELECT * FROM emp ORDER BY sal DESC #倒序
SELECT * FROM emp ORDER BY hiredate #按照日期的数字升序
SELECT * FROM emp ORDER BY ename #a-z升序
SELECT * FROM emp ORDER BY job 
#拿着每个汉字对应的数字(查u8) ,升序排序

5.对时间类型的操作

#统计2019年入职的员工
SELECT * FROM emp WHERE 
#year(hiredate)=2019
hiredate>'2019-1-1' AND hiredate<'2019-12-12'
#统计2017年以前入职的员工
SELECT * FROM emp WHERE 
YEAR(hiredate)<2017
#统计2015~2017年入职的员工
SELECT * FROM emp WHERE 
YEAR(hiredate) BETWEEN 2015 AND 2017
#统计员工入职了几年
SELECT *,YEAR(NOW())-YEAR(hiredate) FROM emp

6.奖金为null，总工资计算

SELECT sal,comm,
(sal+IFNULL(comm,0))*16 AS 年薪 #as用来设置别名
FROM emp

ifnull判断该元素为null，则改为你指定的元素，这里我指定为0，null与int型数据相加显然是不显示的，所以这里把null指定为0，便可实现相加的功能

四，聚合函数

1.count统计个数

#求总个数count
SELECT COUNT(comm) FROM emp#不统计comm字段值是null的-低效
SELECT COUNT(*) FROM emp#都统计
SELECT COUNT(1) FROM emp#都统计-高效

2.group by 分组

#统计每个岗位的最高薪
select job, max(sal) from emp
group by job;

在这里插入图片描述
3.having 的使用

#having的作用:用来对分组后的数据,进一步过滤
#统计每个部门的平均薪资,只要>10000的记录
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>10000

having与where

#统计每个岗位的最高薪资,只要>8000的记录
SELECT job,MAX(sal) FROM emp
#先过滤再分组高效,但是where里不能用聚合函数
#where MAX(sal)>8000  #会报错
GROUP BY job
HAVING MAX(sal)>8000
ORDER BY MAX(sal) #升序排序

先使用where过滤在分组显然会更高效，但是where不能使用聚合函数，所以having与where的基本区别的就是having是使用聚合函数时使用

#统计每年的入职人数,只要2017年以后的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出现聚合函数
GROUP BY YEAR(hiredate)
#having YEAR(hiredate)> 2017 
#having里使用的过滤条件必须是查到的结果

五，事务

开启事务后，你所执行的一系列操作都需要提交，否则操作不会对表产生影响，常用的mysql会自动开启事务并提交事务，而Oracle需要自己手动提交数据–>commit

mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(18,'java','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(19,'java2','shanghai2');
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交事务
Query OK, 0 rows affected (0.03 sec)

六，字段约束

1.字段：

CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
sex VARCHAR(10) DEFAULT '男' #设置默认值
)

2.检查约束（不常用）：

#检查约束:给指定字段设置合法值的检查规则
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
age INT,
CHECK(age>0 AND age<200)#设置检查
)

3.外键约束：
在字表中 foreign key(本表的主键) references 关联表(主键)

#外键:通过指定的SQL语句描述了两张表的关系
#约束:子表添加记录时id必须在取自主表
     #删除主表记录时,必须没有被子表使用着
#怎么确定谁是子表谁是主表?子表中有外键的SQL
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
)
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#表明和哪张表的哪个字段有关系
#foreign key(本表的主键) references 关联表(主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
)

七，索引

为了提高数据库的查询效率,可以使用索引.
给哪些字段设计索引? 查询需要量比较大. 字段的值比较大的时候…
分类:
单值索引(一个索引只包含一个列) create index 索引名 on 表名(字段名)
唯一索引(索引列的值不能重复) alter table 表名 add unique(字段名)
复合索引(一个索引包含多个列) alter table 表名 add index 索引名(字段名,1,1,1)
好处: 大大的提高了SQL的查询效率
坏处: 索引本身也是一张表,表里的数据和真正的表里的数据是重复的,浪费了空间.
注意最左原则！！！

#索引的分类:单值/唯一/复合
#1.创建 单值索引:一个索引包含了一个列
CREATE INDEX job_index ON emp(job)
#创建 唯一索引
#alter table emp add unique(deptno)报错,deptno的值重复啦
ALTER TABLE emp ADD UNIQUE(ename)
#创建 复合索引:最左特性
ALTER TABLE emp ADD INDEX many_index(ename,job,hiredate)

#2.查看索引
SHOW INDEX FROM emp

#3.使用索引
EXPLAIN 
#用来观察SQL的执行计划,主要看有没有用索引
#(观察两列的结果:key和possible_keys)
SELECT * FROM emp WHERE job='经理'#背后会用job索引

EXPLAIN
SELECT * FROM emp WHERE empno=100#背后会用主键索引

EXPLAIN
SELECT * FROM emp WHERE ename='jack'#背后会用唯一索引
SELECT * FROM emp WHERE ename='jack' AND job='经理'#复合索引生效
SELECT * FROM emp WHERE hiredate='2010-1-1'#复合索引失效
SELECT * FROM emp WHERE job='经理' AND hiredate='2010-1-1'#复合索引失效

八，关联查询

笛卡尔积：查询所有的数据，假设a表有x条数据，b表有y条数据，则会查出x*y条数据
inner join 只查询a表与b表的交集
left join 查询左边所有数据，右边不符合的用null代替
right join 查询右边所有数据，左边不符合的用null代替
工作中,常用的是: 小表 left join 大表,小表驱动大表

#多表联查:一张表已经无法满足业务需求,需要联合查询多张表
#方式1:笛卡尔积
SELECT * FROM dept,emp #查出了所有数据
SELECT * FROM dept,emp
     #表名.字段名 
WHERE dept.deptno=emp.deptno#两张表的关联关系
AND dept.dname='accounting'

#方式2:连接查询join
   #inner join/left join/right join
#工作中,常用的是: 小表 left join 大表,小表驱动大表
SELECT * FROM emp 
#inner join dept #两边都满足的交集
LEFT JOIN dept #左边的所有和右边满足了的
#right JOIN dept #右边的所有和左边满足了的
ON dept.deptno=emp.deptno #描述字段
WHERE dept.dname='accounting' #具体过滤条件

#方式3:子查询:把上次的查询结果作为条件再次查询
#1.根据部门名称查部门编号dept
SELECT deptno FROM dept WHERE dname='accounting'
#2.把查到的编号作为条件,查员工信息emp
SELECT * FROM emp WHERE deptno=1
#查询部门名称是accounting的所有信息
SELECT * FROM emp WHERE deptno=(
  SELECT deptno FROM dept WHERE dname='accounting'
)

九，视图

可以把SQL的查询结果缓存起来,存入视图中.
好处: 简化了SQL的编写
坏处: 没法做SQL的优化,占用空间.
其实也不常用

#1.创建视图:缓存了查询结果
CREATE VIEW `cgb2106`.`emp_view` 
AS
#以下的SQL执行的结果会被缓存到视图中
(SELECT * FROM emp WHERE ename LIKE '%a%');

#2.使用视图
#SELECT * FROM emp WHERE ename LIKE '%a%'
SELECT * FROM emp_view 
#直接查的视图,视图就是一张特殊的表,最好只做查询

十，SQL优化

1, 尽量使用字段名代替*
2, 做表设计时,字段的类型最好是varchar代替char
3, 字段里的值,最好用数字代替字符串
4, 尽量把过滤条件精细,能用and不用or
5, 索引的设计,最多5个,不能太多
6, 模糊查询,尽量要确定开始元素,让索引生效
7, 数据库对数字没有严格的要求,name=123索引失效,name='123’索引生效
8, 无用的索引最好及时删除## 标题

DRHJ
关注
专栏目录
Python易忽略要点记录一
记录一些有用的东西
12-16 2222
想要拥有不平凡的人生，请先拿出不平凡的努力。 前言 此内容为学习廖雪峰Python教程的学习笔记，内容是个人认为常常疏忽或是未了解的知识点，不具连贯性，如要学习Python，推荐前往廖雪峰官方站点学习。 基础常见问题 编码问题 1.在最新的Python 3版本中，字符串是以Unicode编码的。 2.字符串默认类型为str类型，在内存中以Unicode表示，一个字符对应若干个字节。 3.这常常会导致一些问题，特别是在进行相关字符串加密解密时，一定要考虑到先把字符串转为以字节为单位的bytes 可以直接转.
SQL全篇
MM999666的博客
12-01 1584
SQL全篇 一、SQL语句类型 1、DML：（Data Manipulation Language）数据操纵语言 DML用于查询与修改数据记录，包括如下SQL语句： ==INSERT：==添加数据到数据库中 ==UPDATE：==修改数据库中的数据 ==DELETE：==删除数据库中的数据 ==SELECT：==选择（查询）数据 SELECT是SQL语言的基础，最为重要。 （横向改记录） 2、DDL：（Data Definition Language）数据定义语言 DDL用于定义数据库的结构，比如创建、修改
用SQL语句定义两个主键
源于和谐
02-29 1万+
有时候需要将两个字段作为 联合主键，SQL语句如下CONSTRAINT   PK_Name   PRIMARY KEY   (col1,col2)说明：col1,col2分别为需要组成联合主键的两个字段 
Sql查询表主键
qq_35840262的博客
04-27 2万+
sql语句查询一个表的主键的字段名mysql mysql 1. 查询整个链接库的所有表的 主键 SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME<> 'dtproperties' 2. 查询某一个数据库中的某一个表的主键字段名（方法一） SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_U
了解主键，外键，如何关联，关联的意义。做连接和有连接，内连接的实际用法与情况
m0_55398762的博客
12-09 567
主键，外键，如何关联，关联的意义(1条消息) 关于数据库主键和外键（终于弄懂啦）_harbor1981的博客-CSDN博客_主键和外键做连接和有连接，内连接的实际用法与情况内连接，显示两个表中有联系的所有数据;左链接，以左表为参照,显示所有数据;右链接，以右表为参照显示数据;(1条消息) 外连接与内连接，左连接与有连接区别_大梁、的博客-CSDN博客_左连接有链接bigintBigInt是一种新的数据类型，用于当整数值大于Number数据类型支持的范围时。这种数据类型允许我们安全地对大整数执行算术操作，表示
SQL server双主键或多主键设置（两种方式）
qq_44858151的博客
08-24 1万+
双主键 alter table [数据表名] with nocheck add constratint [PK_主键名字] primary key nonclustered ( [package_id], [Idcard] ) 多主键 alter table [数据表名] with nocheck add constratint [PK_主键名字] primary key nonclustered ( [package_id], [Idcard] )
SQL server设置两个主键
热门推荐
qq_55786319的博客
10-09 2万+
SQL server设置多重主键 方法1 可视化操作，用ctrl+鼠标左键点击想设置的主键，然后鼠标右键点击设置主键 方法2 用T-SQL语句，创建表时 primary key(列名1,列名2) 举个栗子： 这样就把studentID和courseID都设置为主键啦。 方法3 还是T-SQL语句，修改表时 alter table 表名 add constraint pk_name primary key (列名1,列名2) 举个栗子： ...
mysql sql注入漏洞修复_SQL注入漏洞解析与靶场复现
weixin_36043375的博客
02-26 1296
1. 概述SQL注入(SQL Injectioin)漏洞是注入漏洞中危害性最高的漏洞之一。形成的原因主要是在数据交互过程中，前端的数据传入后端时，没有作严格的验证过滤导致传入的“数据”拼接到了SQL语句中。被数据库当作SQL语句的一部分执行，从而使得数据面临被脱库、恶意破坏篡改甚至造成整个系统权限沦陷等一系列危害。注入攻击的本质是把用户输入的数据当作代码执行。造成注入攻击有两个关键条件：①用户能够...
【THM】SQL Injection（SQL注入）-初级渗透测试
最新发布
追风少年亚索的博客
03-29 2265
撰写本文目的仅仅提供个人查看笔记用途，大家可以去官网查看，都一样的
Oracle SQL
墨鱼菜鸡
07-11 560
W3School SQL 教程：http://www.w3school.com.cn/sql/index.asp 菜鸟教程之 SQL 教程：http://www.runoob.com/sql/sql-tutorial.html 十步完全理解 SQL：http://blog.jobbole.com/55086/ 易百教程：http://www.yii...
两个主键怎么设置tsql_如何在sql server中设置两个主键？
weixin_39645041的博客
12-21 2641
展开全部主键是数据库表的一个重要属性，建立主键可以避免表中存在完全相同的记录，也就是说主键在e68a8462616964757a686964616f31333365633938一张表中的记录值是唯一的。建立主键有两种方法：一种是在数据库提供的GUI环境中建立，另一种是通过SQL语句执行建立，下面分别介绍。1、在数据库提供的GUI环境中建立。输入表信息后按Ctrl键同时选中多行，然后点上面的主键按钮...
双主键关联映射（double primary key）
weixin_30781631的博客
05-22 422
A Foreign key refering com.wawagame.backend.trade.hbentity.ProductEntity from com.wawagame.backend.trade.hbentity.OrdersEntity has the wrong number of column. should be 2 当一个表里有双主键： 在进行多对一映射时： 当该表作...
用T-sql语句设置两个主键sqlserver
qq_44198216的博客
03-17 3061
通过PRIMARY KEY(列名1,列名2)这条语句即可实现
mysql两表多主键关联查询语句_一张表多个字段是另一张表的主键，求关联查询语句...
weixin_33076057的博客
01-21 1450
引用:和期望的结果有差距，无法进一步操作。准确的说，想要的效果是查询结果是一条记录……能实现吗？更正一下数据库结构:A表：id、name、one、two、threeB表：id、name、parentid(B表自关联)现在项目中的需求是，把全部的数据列表，每一条A记录占一行，A记录中onetwothree的位置显示为B表中对应的name。请各位高手帮忙解答一下，谢谢！语句：--测试数据;WITH...
一张表多个字段是另一张表的主键，关联查询语句
weixin_30265171的博客
06-08 861
CREATE TABLE `User`( 　　`Id` BIGINT AUTO_INCREMENT NOT NULL, 　　`Name` VARCHAR(10) NULL, 　　`Phone` VARCHAR(100) NULL, 　　`IsDeleted` TINYINT NULL, 　　PRIMARY KEY (Id)); CREATE TABLE `Ema...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值