- 23-6-27
1、MySQL中不能使用 = NULL
或 != NULL
等比较运算符在列中查找 NULL 值,因为NULL不可以进行运算
用IS NULL
或 IS NOT NULL
才会进行NULL值或非NULL值得查找。
2、聚合函数不能用在WHERE子句后面,WHERE子句无法与聚合函数一起使用
查询的执行顺序:
from > join > on > where > group by > avg,sum.... > having > select > distinct > order by> limit
3、不可重复读或幻读的解决方法:读取数据时加共享锁,写数据时加排他锁,都是事务提交才释放锁
脏读的的解决方法:修改时加排他锁,直到事务提交后释放,读取时加共享锁
4、删除单个表中的数据
使用 DELETE 语句从单个表中删除数据,语法格式为:
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下:
<表名>:指定要删除数据的表名。
ORDER BY 子句:可选项。表示删除时,表中各行将按照子句中指定的顺序进行删除。
WHERE 子句:可选项。表示为删除操作限定删除条件,若省略该子句,则代表删除该表中的所有行。
LIMIT 子句:可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
注意:在不使用 WHERE 条件的时候,将删除所有数据。
- 23-6-28
1、SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。
1 数据查询语言DQL
数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块:SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>
2 数据操纵语言DML
数据操纵语言DML主要有三种形式:
- 插入:INSERT
- 更新:UPDATE
- 删除:DELETE
3 数据定义语言DDL
数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如:CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表 视图 索引 同义词 簇。DDL操作是隐性提交的!不能rollback
4 数据控制语言DCL
数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。如:
- GRANT:授权。
- ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点。回滚—ROLLBACK回滚命令使数据库状态回到上次最后提交的状态。其格式为:SQL>ROLLBACK;
- COMMIT [WORK]:提交。在数据库的插入、删除和修改操作时,只有当事务在提交到数据库时才算完成。在事务提交前,只有操作数据库的这个人才能有权看到所做的事情,别人只有在最后提交完成后才可以看到。
2、删除触发器: DROP TRIGGER
3、sql中有两种方式表示不等于,一种是"<>“(不含引号),另一种是”!="(不含引号),用法是一样的。
4、 ROUND(m,d) 函数用于把数值字段四舍五入为指定的小数位数;
TRUNCATE(m,d) 函数是按照小数位数进行数值截取,没有四舍五入。
5、MySQL中ALTER TABLE命令的用法
MySQL中ALTER TABLE命令可以修改数据表的表名或数据表的字段。
修改表名或索引名:RENAME();RENAME也可以更改列名,后面要加TO,且它只会更改列的名字,并不更改定义。
修改字段定义和名称:MODIFY()或CHANGE()。但是MODIFY只改字段定义,不改名字;CHANGE是两个都可以修改。
修改字段默认值:可以用ALTER 字段名 SET DEFULT 更改值。
- 23-6-29
1、Having 子句能够对 Group by 产生的分组条件进行过滤,不能单独使用,对分组后的数据进行过滤。
换句话说Select后面的字段列表里没有出现的字段,having中不能使用
WHERE是在GROUP BY分组之前进行条件筛选,后面不可以跟聚合函数。
HAVING是在GROUP BY分组之后进行条件筛选,后面可以直接跟聚合函数。
2、删除表
delete 可以精确删除,只删除表的数据,不会删除结构。
truncate 能删除表的数据,也能删除表的结构
drop 完全删除表
3、COALESCE()函数,依次参考各参数表达式,遇到非[null]值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
COALESCE ( expression,value1,value2……)
COALESCE()函数的第一个参数expression为待检测的表达式,而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。
如果expression不为空值则返回expression;否则判断value1是否是空值,
如果value1不为空值则返回value1;否则判断value2是否是空值,
如果value2不为空值则返回value2;
……
如果所有的表达式都为空值,则返回NULL。
4、回收表的操作功能语句revoke … on … from。
在 MySQL 中,@‘%’ 是表示任何主机的通配符。
- 23-6-30
1、事务锁
共享锁S:共享锁锁定的资源可以被其他用户读取,但是其他用户无法修改,在执行select时,sql server会对对象加共享锁。(其他人可读不可写) 。
排它锁X:(独占锁)其他人不能读也不能写(所以不会多重更新)。更新锁U:当SQL Server准备更新数据时,它首先对数据对象作更新锁锁定,这样数据将不能被修改,但可以读取。等到SQL Server确定要进行更新数据操作时,他会自动将更新锁换为独占锁,当对象上有其他锁存在时,无法对其加更新锁。
架构锁:在执行依赖于表架构的操作时使用。架构锁的类型为:架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
2、SQL语言的组成部分有:数据定义语言、数据处理语言、数据控制语言、数据库事务。
3、MySQL SUBSTR() 函数 :截取字符串
SUBSTR( )与SUBSTRING( )意思相等
SUBSTR (str, pos, len):截取从pos位置开始到最后的所有str字符串
参数说明:
str:列名/字符串;
pos:起始位置;mysql中的起始位置pos是从1开始的;如果为正数,就表示从正数的位置往下截取字符串(起始坐标从1开始),反之如果起始位置pos为负数,那么表示就从倒数第几个开始截取;
len:截取字符个数/长度。
4、LENGTH() 函数返回字符串的长度(以字节为单位)。
5、joins 关系
-
23-7-3
1、STUFF() 函数删除字符串的一部分,然后将另一部分插入到字符串中,从指定位置开始。
STUFF(string, start, length, new_string)
string 必需。要修改的字符串
start 必需。string中开始删除部分字符的位置
length 必需。要从 string 中删除的字符数
new_string 必需。在 start 位置插入 string 的新字符串 -
23-7-6
1、SQL语言共分为四大类:数据查询语言DQL,数据操纵语言DML,数据定义语言DDL,数据控制语言DCL。
2、drop,truncate,delete 区别
三者的执行速度:drop > truncate > delete
- drop:删除内容和定义,释放空间。(表结构和数据一同删除)
- truncate:删除内容,释放空间,但不删除定义。(保留表结构,仅删除数据)
- delete:删除内容,不删除定义,也不释放空间。
释放空间可以体现在:
*通过delete删除的行数据是不释放空间的,如果表id是递增式的话,那么表数据的id就可能不是连续的;
*通过truncate删除数据是释放空间的,如果表id是递增式的话,新增数据的id又是从头开始,而不是在已删数据的最大id值上递增。
- 23-7-14
1、字母大小写转换函数
大写函数Upper()或者UCASE()函数
小写转换Lower()或者LCASE()函数
2、DATEDIFF() 返回两个日期之间的时间差
语法:DATEDIFF(datepart,startdate,enddate)
datepart是单位例如:year,quarter(季度),month,week,day,hour,minute,second,millisecond(毫秒);
DATEDIFF(year,‘2010-12-31’,‘2019-01-01’)
结果返回12
3、DATE_ADD()常常用户在Mysql的sql中实现对日期类型的操作,比如增加或者减少,但是不改变原来的数据,只是对查询的数据做处理,
语法:DATE_ADD(date,INTERVAL expr unit)
interval是固定标志,expr 表示数量,可以为正负,表示加减,unit表示日期类型 可以是yy,ww,dd等分别表示,年,周,天等
例如
// 得到当前时间增加1个小时的结果
select date_add(2023-7-14,interval 1 day)
- 23-7-18
1、CHARINDEX (str,strl):会在第二个字符表达式中搜索一个字符表达式,这将返回第一个表达式(如果发现存在)的开始位置。
CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )
expressionToFind :目标字符串,就是想要找到的字符串,最大长度为8000 。
expressionToSearch :用于被查找的字符串。
start_location:开始查找的位置,为空时默认从第一位开始查找。
2、
All():对所有数据都满足条件,整个条件才成立,>=all()等价于max,<=all()等价于min;
Any():只要有一条数据满足条件,整个条件成立,>any()等价于>min,<any()等价于<max;
some的作用和Any一样
- 23-7-19
1、
INSERT INTO 语句用于向一张表中插入新的行。
SELECT INTO 语句从一张表中选取数据插入到另一张表中。常用于创建表的备份复件或者用于对记录进行存档。
select * into table2 from table1 where ....
select into from 要求目标表不存在,因为在插入时会自动创建。
insert into select from 要求目标表存在
- 23-7-20
1、 数据库权限
GRANT<权限> on "表名(或列名)" to "用户" @"IP地址" (IP地址无限制可输入'%' )
REVOKE <权限> on "表名(或列名)" FROM "用户"
2、建立视图的语法:
CREATE VIEW 视图名[(字段1),字段2),(字段3)...] AS select 语句
其中:[(字段1),字段2),(字段3)…] 可省
3、SQL 游标中的 insensitive参数
当SELECT语句中使用DISTINCT、GROUP BY、HAVING UNION语句时游标将会自动设定INSENSITIVE选项
4、REGEXP 来操作正则表达式的匹配。
^ 该符号表示匹配输入字符串的开始位置;
$表示匹配输入字符串的末尾位置;
[…] 表示匹配所包含的任意一个字符;
[^…]表示不能匹配括号内的任意单个字符;
x|y 这条竖线表示匹配x 或匹配y。 所以本题A选项错在会匹配不是’C’或’h’的数据;
5、防止SQL注入,需要注意以下几个要点:
永远不要信任用户的输入。对用户的输入进行校验,可以通过正则表达式,或限制长度;对单引号和双"-"进行转换等。
永远不要使用动态拼装sql,可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接。 不要把机密信息直接存放,加密或者hash掉密码和敏感的信息。
应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装 。
sql注入的检测方法一般采取辅助软件或网站平台来检测,软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具。
MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入,XSS攻击等。
- 23-7-24
1、 子查询知识点(来源:机智的豆子):
1.where型子查询:指把内部查询的结果作为外层查询的比较条件。子查询:单列单值
2.from型子查询:把内层的查询结果当成临时表,供外层sql再次查询。子查询:多行多列
3.in子查询:内层查询语句仅返回一个数据列,这个数据列的值将供外层查询语句进行比较 子查询:单列多行
4.exists子查询:把外层的查询结果,拿到内层,看内层是否成立,简单来说后面的返回true,外层(也就是前面的语句)才会执行,否则不执行。
5.any子查询:只要满足内层子查询中的任意一个比较条件,就返回一个结果作为外层查询条件。
6.all子查询:内层子查询返回的结果需同时满足所有内层查询条件。 比较运算符子查询:子查询中可以使用的比较运算符如 “>” “<” “= ”“!=”
2、UNION 合并多个SELECT的结果集,只列出不重复的值,它会过滤重复值。UNION ALL 不过滤重复值。
3、 聚合函数之间不能直接嵌套使用
max(sum(age)) 是错误的
4
MySQL查询语句的执行顺序
FROM > JOIN > ON > WHERE >GROUP BY > HAVING(聚合函数) > SELECT > ORDER BY > LIMIT
5、索引
唯一索引不允许两行具有相同的索引值,包括NULL值,允许有空值
每个表只允许有一个全文索引
聚集索引具有完全独立于数据行的结构,所以不需要将物理数据页中的数据按列重新排序。
主键索引:它是一种特殊的唯一索引,不允许有空值。 通常不会删除主键,因为设计主键一定与业务逻辑无关。
普通索引:即一个索引值包含单个列。一个表可以有多个单例索引它是最基本的索引,没有任何限制普通索引允许被索引的数据列包含重复的值
唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
联合索引:一个索引包含多个列,专门用于组合搜索,其效率大于索引合并
全文索引:全文索引基本上不可能用到。如果有这样的需求,请用第三方框架
- 23-7-25
1、
Charindex():返回字符串在另一个字符的起始位置。
charindex(查找字符,被查字符,开始位置)
SubString():截取字符串中的一部分字符。
SubString(查找字符,开始位置,截取长度)
Stuff():删除指定长度的字符,并在指定的起点处插入另一组字符。(替换)
stuff(列名,开始位置,长度,替代字符串)
2、
修改表:ALTER TABLE 表名 修改选项 。选项集合:
| ADD COLUMN <列名> <类型> – 增加列
| CHANGE [COLUMN] <旧列名> <新列名> <新列类型> – 修改列名或类型| ALTER [COLUMN] <列名> { SET DEFAULT <默认值> | DROP DEFAULT } – 修改/删除列的默认值
| MODIFY [COLUMN] <列名> <类型> – 修改列类型
| DROP [COLUMN] <列名> – 删除列
| RENAME TO <新表名> – 修改表名
| CHARACTER SET <字符集名> – 修改字符集
| COLLATE <校对规则名> } – 修改校对规则(比较和排序时用到)