関小君刷题记

一、数据表基本操作

1. 单个主键

create table test{
    name VARCHAR(25),![image](https://cdn.nlark.com/yuque/0/2020/jpeg/1416004/1589251211920-1b37eaf9-feaa-424e-9723-ea3d95ac0a16.jpeg)
    depid INT(11),
    PRIMARY KEY(name)
}

2.联合主键,由多个字段组合共同确定一行记录。

create table test{
    name VARCHAR(25),
    depid INT(11),
    PRIMARY KEY(name,depid)
}

3.外键约束
外键用于在两个表的数据之间建立连接，一个表(从表)可以有多个外键，可以不是这个表的主键，外键可以为空，若不为空则必须等于另一个表（主表）的某一个主键的值。主要作用的是保证数据引用的完整性、一致性，定义外键后不允许删除另一个表中的具有关联关系的行。跟主键的类型保持一致。
4.唯一性约束UNIQUE
 可以确保一列或者多列不出现重复值。
5.默认约束default
depid INT(11) default 111,
depid的默认值为111
6.设置自增属性AUTO_INCREMENT,一个字段只能有一个属性使用自增属性
7.查看表结构
DESC/DESCRIBE 表名
8.show create table
可以查看创建表的详细语句，数据库引擎以及字符编码。
9、表重命名
alter TABLE 旧名 rename 新名 10、修改字段数据类型
alter TABLE 表名 MODIFY 字段名 字段类型
11、修改字段名 alter TABLE 表名 CHANGE 旧字段名 新字段名 新字段类型
12、添加字段
alter TABLE 表名 ADD 字段名 字段类型
13、删除字段
alter TABLE 表名 DROP 字段名 字段类型
14、删除表的外键约束
alter TABLE 表名 DROP FOREIGN KEY 外键约束名
15、删除没有关联的表
DROP TABLE IF EXISTS 表名（多个逗号隔开）
16、删除被其他表关联的主表
 数据表之间存在外键关联的情况下，直接删除主表会失败，因为破坏了数据表的参照完整性，如果想把父子表同时删除，先把子表删了再删 父表，如果想保留子表，就把表的外键约束条件取消掉然后删除父表即可。

二、数据类型和运算符

1、整形
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

INT(3)3只用于显示宽度，并不能限制取值范围和字节占用空间，文本的则限制字符串的长度。

2、浮点型
FLOAT(m,n),DOUBLE(m,n)
m为精度，表示总共的位数，n为标度，表示小数的位数。
3、TIMESTAMP和DATETIME的区别是， 前者（4字节）的取值范围小于后者（8字节）的；DATETIME存入什么就是什么与时区无关，TIMESTAMP是以时区UTC格式保存的，存储时对当前时区做转换，检索时再转换回当前时区，即查阅时根据不同的时区显示不同的时间。      当插入列值时并没有插入TIMESTAMP对应列的值，该列的值会被自动填充上当前时间。
4、CHAR(M),VARCHAR(M)
CHAR(4)定长度为4的字符串，M长度范围是0-255，不够长的用空格补齐，检索时空格被删除返回。
VARCHAR(4)长度可变的字符串，M长度范围是0-65535，在保存和检索时尾部的空格会被保留。
CHAR的存储速度比VARCHAR快，但是浪费存储空间。

5、TEXT
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
6、ENUM SET
ENUM只能取单值，他的数据列表是一个枚举集合，比如性别字段适合ENUM从男女中选择一个值。
SET可取多值，包括空字符串，比如存储一个人的兴趣爱好可以用SET类型。
7、算数运算符

+  -  *  /  %

8、比较运算符

> 、< 、=、 !=（或者<>）、 <= 、>=   <=>
  IN 、NOT IN、BETWEEN AND、IS NULL、IS NOT NULL、GREATEST、LEAST、LIKE、REGEXP

• <=> 安全等于，可以计算NULL的值 NULL<=>NULL返回1

• LEAST 在有两个或多个参数时返回最小值，如果是字符串返回 字母表中最靠前的字符

• GREATEST 在有两个或多个参数时返回最大值

• LIKE 通用匹配符 可以使用%(匹配任何数目的字符)或者_（只能匹配一个字符）

• REGEXP 正则表达式

'2'=2 返回1 Mysql会自动把‘2’转成整数进行比较

9、逻辑运算符（求职所得的结果均为1（true）或者0（false））
逻辑非（NOT或者！）逻辑与（AND或者&&）逻辑或（OR或者||）逻辑异或（XOR）
10、位操作运算符
与&、或|、非~、异或^、左移<<、右移>>

1、在向表中插入特殊字符一定要加\符号先转义之后在插入，常见的特殊字符有 ' 、" 、\、 \r、 \n、 \b、 \tab
2、如果想执行区分大小写的比较，在字符串前面加BINARY即可，如：BINARY 'A'='a' 返回0，而'A'='a'返回1，mysql默认不区分大小写

三、MYSQL函数

1、绝对值函数ABS(X)
2、取整函数

• CEIL(X) CEILING(X)返回不小于X的整数值，CEILING返回值转为BIGINT

• FLOOR(X) 返回不大于X的最大整数，返回值转为BIGINT
  3、随机函数RAND、RAND(X)
  返回0-1之间的数值，如果RAND(X)中的X值一样将返回同一个在0-1之间的随机值
  4、ROUND(X)、ROUND(X,y)、TRUNCATE(X,y)

• ROUND(X) 对X值四舍五入

• ROUND(X,y) y为保留小数的位数，四舍五入，ROUND(123.45,1)返回123.5 ；若y为负数，保留的小数点左边的相应位数直接保存为0，不进行四舍五入，如 ROUND(123.45,-1)返回120

• TRUNCATE(X,y)与ROUND(X,y)的区别是在截取值的时候不进行四舍五入。
  5、SIGN(X)返回X的符号 -1、0、1，分别代表负号、0、正号 6、字符长度函数
  LENGTH(‘abx’) 返回字符串占的字节数
  CHAR_LENGTH(‘abc’) 返回字符串占的字符的个数 7、字符串拼接函数
  CONCAT(v1,v2…)直接拼接v1 v2没有分隔符 CONCAT_WS(x,v1,v2…)第一个参数为分隔符，得到v1xv2,如果分隔符为null得到的值也为null。
  8、大小写转换
  LOWER(X) 或者LCASE（X）小写
  UPPER(X) 或者UCASE（X）小写
  9、获取指定长度的字符串的函数 LEFT(s,n) RIGHT(s,n) 10、删除空格的函数
  LTRIM（s） 删除左边空格
  RTRIM(s) 删除右边空格
  TRIM(s) 删除左右两边的空格
  TRIM(s1 FROM s) 删除字符串s中前后两边的字符串s1
  11、REPEAT(s,n)返回一个由字符串s重复拼接n次的字符串
  12、SPACE(n) 返回由n个空格组成的字符串
  13、REPLACE(s,s1,s2) 使用字符串s2替换字符串s中的所有字符串s1
  14、反转字符串 REVERSE(s)
  15、IF(expr,v1,v2) 如果expr表达式的值为true返回v1否则返回v2
  IFNULL(v1,v2)如果v1的值不为null则返回v1否则返回v2
  16、case函数
  CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [WHEN v3 THEN r3] [ELSE rn] END

四、数据查询

1、AND 的优先级高于OR ，两者一起用，会先执行AND的条件在执行OR的。
2、多列排序，前边的一列必须跟前边的一列具有相同的列值，才会对后边的列进行排序，否则后边的列不会进行排序。
3、DESC降序，ASC升序（默认的方式可以不加）
4、分组，[GROUP BY 字段][ HAVING <条件表达式>]，WHERE和HAVING都是过滤数据，区别是：HAVING在数据分组之后再进行过滤选择分组，WHERE作用于分组之前筛选记录，然后再进行分组。
5、GROUP_CONCAT(条件)，这里的条件可以是字段名或者是语句，如:[distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] ,作用是按照分组把同一组内的某个字段下的数据按照条件逻辑组装起来。
6、GROUP BY 子句WITH ROLLUP,会在查询结果的最后一条记录的后边增加一条记录，该记录计算查询出的所有记录的总和。使用WITH ROLLUP不能同时使用ORDER BY。
7、多字段分组，GROUP BY 字段1 字段2，先对字段1进行分组，然后在第一个字段值相同的记录中，再按照字段2进行分组，依次类推。
8、LIMIT [位置偏移量,] 行数，位置偏移量不传默认第一行，行数就是要返回的记录的条数。
9、COUNT(*)计算表中总的行数，不管某列是否有值。
COUNT(字段名)，计算指定列下总的行数，计算时将忽略空值的行。
10、MAX()函数可以对字符串进行ACSII进行比较。
11、内连接（返回两个表的交集）
表1 INNER JOIN 表2 ON 条件
此用法跟where差不多，有些情况下比where查询效率高。
12、外链接
左连接 表1 LEFT JOIN 表2 ON 条件
LEFT JOIN 返回左表的所有行的数据以及两个表的交集的数据，如果左表某一行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。
右连接 表1 RIGHT JOIN 表2 ON 条件
RIGHT JOIN 右连接是左连接的反向连接，将返回右表的所有行，如果右表的某一行在左表中没有匹配行，左表将返回空值。
13、子查询
在select子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。
子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS，子查询可以添加到SELECT、UPDATE、DELETE中，可以多层嵌套。
14、合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个的结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的，使用ALL的作用是不删除重复行也不对结果进行自动排序，查询效率会高一些。

15、DISTINCT 作用于后边的所有列，不仅是其后的第一列，这时候不同的判据就是其后的所有列的组合值是否不同了。
16、查询语句中有的字段加了引号”,代表该字段的类型为字符串类型，数值类型的则不用加。

五、增加、更新、删除数据

1、INSERT INTO table_name(columnlist) VALUES(valuelist1),(valuelist2)…(valuelistn)
一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中，效率更高。因为MYSQL执行单条INSERT语句插入多行数据，比使用多条INSERT语句快，所以在插图多条记录时，最好选择使用单条INSERT语句的方式。
2、更新
UPDATE table_name SET column_name1=value1,column_name2=value2…column_namen=valuen where condition
3、刪除
DELETE FROM table_name [WHERE condition
]
删除所有表的数据 DELETE FROM table_name
还可以用 TRUNCATE TABLE table_name，它是直接删除表后重新创建一个表，而不是删除记录速度上比DELETE快。

六、索引

优点：
1、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2、可以大大加快查询速度
3、在实现数据的参考完整性方面，可以加速表和表之间的连接
4、在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间
缺点：
1、创建索引和维护索引要耗费时间，并且随着数据的增加所耗费的时间也会增加
2、索引还占用磁盘空间，每一个索引还要占用一定的物理空间，如有大量的索引，索引文件可能会比数据文件更快达到最大文件尺寸
3、当进行增加、修改和删除的时候，索引也要动态的维护，这样就降低了索引的维护速度

1、普通索引和唯一索引
普通索引允许在定义索引的列中插入重复值和空值。INDEX \KEY
唯一索引，索引列的值必须唯一，允许有空值，UNIQUE INDEX ，如果是组合索引UNIQUE INDEX UniqidIdx(col1,col2…)，则列值的组合必须唯一，主键索引是一种特殊的唯一索引，不允许有空值。
2、单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时，遵循最左前缀集合。INDEX MultiIdx(column1,column2…),
最左前缀：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如INDEX MultiIdx（col1,col2,col3）,索引行中按照col1,col2,col3的顺序存放，索引可以搜索下边字段组合，（col1,col2）、（col1,col3）、（col1），如果不符合最左前缀，如（col2,col3），则不能使用索引查询。

3、MySQL 索引设计原则：

（1）对于经常查询的字段，建议创建索引。

（2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。

（3）避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。

（4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。

（5）不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。

（6）当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。

（7）在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引。4、删除索引
ALTER TABLE table_name DROP INDEX index_name
DROP INDEX index_name ON table_name
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除，如果组成索引的所有列都被删除，则整个索引将被删除。
**尽量使用短索引 **

未完待续

关注我获取更多知识或者投稿