MySQL数据库使用细节

青旅半醒 2022-05-20 09:41 321阅读 0赞

1、删除一张表中的所有数据，使用命令：truncate table\_name，不要使用delete table\_name。虽然二者都可以删除，但前者速度更快。删除后仍然保留表的结构、索引、约束等设置。

2、SQL语句中的数据连接：

①concat：将任意2个字段连接起来，组成一个单独的值。组成形式可用separator属性设置分隔符来确定，默认没有分隔符。

②group\_concat：将由group by子句产生的结果中的字段连接起来。组成形式可用separator属性设置分隔符来确定，默认就是个逗号。

3、SQL语句中的数据类型转换：CAST(expr AS type)，例如：将grade字段的值转换为无符号整型：CAST（grade AS unsigned）。

其实，CAST()函数还可以将计算结果以某种形式转换，例如：将成绩的平均数保留1位小数显示。

![70][]

4、mysql中的数值函数rand()返回0到1内的随机数。

①利用此函数可以产生0到100内的任意随机整数：select ceil(100\*rand())

②产生50—100之间的随机数：update table\_name set score = floor( 50 + rand() \* (100 - 50))

5、对含有TEXT、BLOB类型字段的表，使用OPTIMIZE TABLE table\_name来优化表，删除表中出现的“空洞”。

6、日期类型DATE、TIME、DATETIME、TIMESTAMP的选择问题：

①只需记录年份，用一个字节的year类型；

②记录年月日时分秒，并且距离的年份比较久远，用DATETIME，不要用TIMESTAMP，因为TIMESTAMP表示的日期范围比DATETIME短得多；

③记录的日期若要让不同时区的用户使用，用TIMESTAMP，因为只有TIMESTAMP能和时区相对应。

7、with子句可将数据定义为临时表。例如：

WITH e AS(

SELECT \* FROM emp);

SELECT \* FROM e;

8、MySQL常用命令，在Navicat for MySQL中也能用：

*  查看MySQL版本：select version();
 *  查看当前用户：select current\_user();
 *  显示单表信息：show table status like "table\_name";
 *  显示正在操作数据库的进程数：show processlist;
 *  显示表中的所有索引：show index from table\_name;
 *  查看查询语句的执行情况，常用于SQL优化：explain select \* from table\_name where a = 1

9、enum枚举类型：

表中设计的值是\{“上午”，”下午”，”晚上”\}，默认是顺序是1,2,3。前端如果传1的话，存入数据库中去的是“上午”，从表中取出来的也是“上午”而不是1，所以前端传递参数应该为“上午”而不是1，1是系统中使用的而不是用户使用的。

10、mariadb是MySQL的一个分支，我用的是xampp里面的，它的默认用户名为root，默认密码为空。可根据需要自行修改。

11、SQL语句中使用case when……then……else……end进行条件判断。例如：

select  ( case when max(age) is 0 then 0 else max(age) end ) max\_age from table\_name

12、用SQL语句中的replace()修改特定字符。

例如：name的值原来都包含一个2，如“张老师2”，现在想在同一位置将其都改为3，如“张老师3”：update table\_name set \`name\` = REPLACE(name,'2','3');

13、用SQL语句中的函数配合使用完成特定需求。

例如：原来account值为“20001”，现在想改为“30001”：  
update table\_name set account = CONCAT('3',RIGHT(account,LENGTH(account)-1)) ；

14、为查询出来的结果集添加一列，并且该列的值是固定的。

例如：为查询出的结果集添加一个名为alias\_column，值为100的列。

SELECT T.\*,100 as alias\_column FROM table\_name T;

15、将查询出来的结果集编号。例如：编号是从1开始的连续自然数。

select (@rowNo:= @rowNo+1) as rowNO,vt1.\*

from table\_name vt1,

(select  @rowNo := 0) vt2

16、limit的用法：

/\*当没有指定位置偏移量时，只取4条时，可以这样写\*/

SELECT \* FROM t LIMIT 4;

/\*当指定了位置偏移量时，从第3条起取4条时，可以这样写\*/

SELECT \* FROM YourTableName LIMIT 2,4;/\*因为索引是从0开始计数的，所以第3条对应的索引就是2\*/

17、查看SQL语句的执行计划：explain SQL语句。explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

18、如果经常查询所谓的靠后的数据，比如说Id较大的数据，或者说是时间维度上较新的数据，可以采用**倒叙扫描索引**的方式来实现**高效分页查询：**也就是先倒序，再正序。例如：

select \*

from （

select \* from t1 order by ID limit 99980,20

）t

order by ID

19、SQL语句中获取系统当前时间：now()函数，获取的格式为“年-月-日 时：分：秒”。

![70 1][]

20、如何定位到一张表的奇偶行。例如：需求是：将id为奇数行的sex改为“男”，偶数行的sex改为“女”：

奇数行：update table\_name set sex = '男' where  id&1 ;

偶数行：update table\_name set sex = '女' where  id=(id>>1)<<1 ;

21、MySQL中的**进制转换**：

二进制转为十进制：select conv(101010,2,10);结果是42

十进制转为十六进制：select conv(999,10,16);结果是3E7

22、关于MySQL组合索引（复合索引）的最左优先原则：最左优先是说复合索引的第一个字段必须出现在where中，这个索引才会被用到。只要组合索引最左边第一个字段出现在where中，那么不管后面的字段出现与否或者出现顺序如何，MySQL引擎都会自动调用这个索引来优化查询效率。为什么这么说？因为：根据最左优先原则可以知道B树建立索引的过程，假设有一个3列索引（col1，col2，col3），那么MySQL只会建立3个索引：（col1），（col1，col2），（col1，col2，col3）。所以，只要where条件中出现了字段col1（MySQL后自动调整where条件中各字段的顺序以最大化利用索引），那么该索引就用得上。

23、**截取字符串方法**。MySQL中表的varchar类型字段的值“既有汉字又有数字”，那么用left(),right()去截取不成功。因为汉字与数字所占的大小不一样。如用select length(name) from table\_name where id =1; 语句查询可知，“周周”返回值为6，“周周1”返回值为7，“周周周1”返回值为10，可以得出结论：一个汉字长度为3，一个数字长度为1。所以以下语句用于将“周周3”改为“周周”，“周周3”改为“周周”。

update table\_name set name =substring(name,1,3) where length(name)=10 ;

update table\_name set name =substring(name,1,2) where length(name)=7 ;

24、MySQL 变量赋值有3种方式：

①SELECT 'Hello' into @x;  \#变量x没有被声明过

②SET @y='Goodbye';        \#变量y没有被声明过

③SET z =2;                        \#变量z已经被声明过了

25、如何解决MySQL自动递增导致的数据不连续问题。

**问题描述**：数据库表中有id为1,2,3的3条数据，在测试过程中不小心加了一个id为9的数据，此时，数据库共有4条数据。但测试通过了需要将测试数据（id为9）删除，那么新增的数据会以10开头（因为id设置为自增），那么问题就来了，好好的一张表，怎么id就从10开始了，中间那部分（4-9）难道就不能用了？如果id能从4开始多好呀！

**需求**：id为9的数据被删除后，id从4开始而不是10开始。

**解决办法**：将**auto\_increment****取值为下一个即将开始的id**，本例中取值为3后面的4.   alter table\_name auto\_increment = 4。

26、MySQL中交换两条数据的值，或者交换两列数据的值都需要**用别名标记同一张表来创造出“两张表”的效果**，这样才会交换成功，否则没有别名的话交换不成功。

例如：update **table\_name as a, table\_name as b** set a.col1=b.col2, a.col2=b.col1 where a.id=b.id;\#交换table\_name的col1列与col2列的值

27、在MySQL中，经过测试得知：null,0,1 这3个值在max()函数中，依次是1>0>null。

28、MySQL中最大值、最小值的查找方式有2种情况：

①查找**多列中的最大值用**GREATEST()**，最小值用**LEAST()

SELECT GREATEST(1,2,3,4);   \#结果：4

SELECT LEAST(1,2,3,4);           \#结果：1

②查找一列中的最大值用max()，最小值用min()

SELECT MAX(col1);

SELECT MIN(col1);