mysql的奇妙语法测试 最后更新时间:2024年11月11日 #### 测试一:通过子查询,一个字段返回两个值: 1. 示例sql: ```sql SELECT ( SELECT 1 AS a, 2 AS b ) AS t ``` 2. 结果: ```shell 1241 - Operand should contain 1 column(s) ``` #### 测试二:通过子查询,运行Update或Insert: 1. 示例sql: ```sql SELECT ( UPDATE test3 SET `value` = 10 ) AS a ``` 2. 结果: ```shell 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update test3 set `value` = 10) as a' at line 1 ``` #### 测试三:mysql除了0和1是否能用FALSE和TRUE直接操作 1. 示例sql: ```sql SELECT FALSE or TRUE ``` 2. 结果: ```shell ————————————————— | FALSE or TRUE | | ————————————— | | 0 | ————————————————— ``` #### 测试四:mysql支持使用阿拉伯数字建库建表吗? 1. 示例sql一: ```sql create DATABASE 123; create table 123.124( id int(10) ); ``` 2. 结果: ```shell 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123' at line 1 ``` 3. 示例sql二: ```sql create DATABASE `123`; create table `123`.`124`( id int(10) ); create FUNCTION `123`.`1234`() RETURNS INT(10) BEGIN RETURN 1; END ``` 4. 结果: ```shell OK OK OK ``` 5. 结论: msyql支持使用阿拉伯数字创建库表。但由于系统内对于数值的解析优先级高于库名表名的标识符的解析,所以需要通过\`\`符号来表示这是一个库表名标识符。 #### 测试五:不使用from,在select中标明库表字段。 1. 示例sql: ```sql select `123`.`124`.id ``` 2. 结果: ```shell select `123`.`124`.id ``` #### 测试六:构建一个function返回一个function类型 1. 示例sql: ```sql create FUNCTION `123`.test() RETURNS FUNCTION BEGIN RETURN `1234`; END ``` 2. 结果: ```sql 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FUNCTION BEGIN RETURN `1234`; END' at line 2 ``` #### 测试七:嵌套运行函数,构建一个function返回另一个function 1. 示例sql: ```sql create FUNCTION `123`.test() RETURNS INT(10) BEGIN RETURN `1234`(); END; select `123`.test() ``` 2. 结果: ```shell 1(正确返回) ``` 3. 结论 msyql的function能够支持调用其他函数,但是无法支持单纯对函数名的返回而,因此无法使用`函数名()()`的用法 #### 测试八:构建递归function 1. 示例sql: ```sql create FUNCTION `123`.test2(v INT(1)) RETURNS INT(10) BEGIN IF v = 1 THEN RETURN `123`.test2(0); ELSE RETURN 1000; END if; END; select `123`.test2(0); select `123`.test2(1); ``` 2. 结果: ```sql select `123`.test2(0) > OK > 查询时间: 0s select `123`.test2(1) > Recursive stored functions and triggers are not allowed. > 查询时间: 0s ``` 3. 结论: mysql不支持函数递归运行,但是并不在语法层面做检查,而是当触发递归时才会报错。 #### 测试九:嵌套运行函数时,内部函数不指定来源库时的搜索优先级如何 1. 前置准备: 构建两个同名函数`test100`,分别构建在数据库`a`和数据库`b`中,其中`a.test100()`返回100,`b.test100()`返回50,在`b`库中构建一个函数`test200`,该函数不指向数据库,调用`test100`. mysql连接中使用`use al;`标明当前使用库为`a`,运行`select b.test200();` 2. 示例sql: ```sql create a.test100() RETURNS INT(10) BEGIN RETURN 100; END; create b.test100() RETURNS INT(10) BEGIN RETURN 50; END; create b.test200() RETURNS INT(10) BEGIN RETURN test100(); END; select b.test100(); ``` 3. 结果: ```shell 50 ``` 4. 结论 当mysql语句中跨库调用函数时,如果函数内存在未指定库的函数,则默认来自于主函数的来源库。如果主函数来源库没有该函数,则报错。 #### 测试十:select语句不加from子句,直接使用where子句。 1. 示例sql: ```sql select 1 as a where 2 > 1; ``` 2. 结果 ```shell 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where 2 > 1' at line ``` #### 测试十一:select 不使用from子句,使用order by和group by子句 1. 示例sql ```sql select 1 as a, "测试一下啊" and 3 as b Order by a; select 1 as a GROUP BY a; ``` 2. 结果 ```shell select 1 as a, "测试一下啊" and 3 as b Order by a > OK > 查询时间: 0s | select 1 as a GROUP BY a > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY a' at line 1 > 查询时间: 0s ``` #### 测试十二:select中使用and和or 1. 示例sql ```sql select "测试" or True as a, "测试一下啊" and 3 as b; ``` 2. 结果 ```shell ————————— | a | b | | - | - | | 1 | 0 | ————————— ``` #### 测试十三:and和or以及其他表达式的优先级 1. 示例sql ```sql select 0 and 1 as a, 1 + 0 and 1 as b; select 1 and 3 >= 2, 1 >= 2 and 3; ``` 2. 结果 ```shell ————————— | a | b | | - | - | | 0 | 1 | | 1 | 0 | ————————— ``` 3. 结论 and和or语句的优先级低于其他语句的优先级。 #### 测试十四:where 子句的内容能否不使用比较运算符,使用计算运算符 1. 示例sql ```sql select * from `db`.`table` where 2-3; select * from `db`.`table` where 2-2; select * from `db`.`table` where 2+3; select * from `db`.`table` where 1; select * from `db`.`table` where 0; ``` 2. 结果 ```shell ———————— | a | | - | | 1 | | NULL | | 1 | | 1 | | NULL | ———————— ``` 3. 结论 where子句后面跟的内容可以为单纯的运算符,不过最终筛选时会根据结果判断是true还是false #### 测试十五:insert语句能否对联合表或者视图插入数据 1. 示例sql ```sql create view test_view as select t2.id as id,t2.age as age,t3.性别 as 性别, t3.`value` as `value` from test2 t2 JOIN test3 t3 on 1=1; INSERT into test_view (id,age,性别,`value`) VALUE (4,342,男,100) ``` 2. 结果 ```shell 1393 - Can not modify more than one base table through a join view 'student.test_view' ``` #### 测试十六:mysql的cross join语法的效果 1. 示例sql ```sql create TEMPORARY table test4 as select 1 as `value`, 2 as `name`; create TEMPORARY table test5 as select 4 as `value`, 3 as `name`; insert into test4 values (33,44),(55,66); insert into test5 values (77,88),(99,00); select * from test4 as ts cross join test5 as tw; ``` 2. 结果 ```shell 1 2 4 3 33 44 4 3 55 66 4 3 1 2 77 88 33 44 77 88 55 66 77 88 1 2 99 0 33 44 99 0 55 66 99 0 ``` #### 测试十七:mysql中的NATURAL join语法的使用 1. 示例sql ```sql create TEMPORARY table test4 as select 1 as `value`, 2 as `name`; create TEMPORARY table test5 as select 3 as `data`, 2 as `name`; insert into test4 values (33,3),(55,4); insert into test5 values (77,3),(99,4); select * from test4 as ts NATURAL join test5 as tw; ``` 2. 结果 ```shell | name | value | data | | 2 | 1 | 3 | | 3 | 33 | 77 | | 4 | 55 | 99 | ``` 3. 结论 MySQL会自动找出两个表中所有名称和数据类型都相同的列,并基于这些列进行连接。但这也意味着,如果两个表中有多个名称和数据类型都相同的列,你可能会得到一些意想不到的结果。 #### 测试十八:mysql中的join的using语法的使用 1. 示例sql ```sql create TEMPORARY table test4 as select 1 as `value`, 2 as `name`; create TEMPORARY table test5 as select 3 as `data`, 2 as `name`; insert into test4 values (33,3),(55,4); insert into test5 values (77,3),(99,4); select * from test4 as ts join test5 as tw USING (`name`); ``` 2. 结果 ```shell | name | value | data | | 2 | 1 | 3 | | 3 | 33 | 77 | | 4 | 55 | 99 | ``` 3. 结论 使用 USING 关键词进行连接操作可以简化语法,尤其适用于两个表有同名列的情况。使用 USING 连接时,相同列名的数据只会出现一次。
Youn9
奇思妙想