01.基础查询和运算符

一.sql基本语法

1.1 sql语言的分类

1.DDL:数据定义语言，即从无到有创建。

1.CREATE

2.ALTER

3.DROP 删结构

4.RENAME

5.TRUNCATE 清空，但表结构还在

2.DML:数据操作语言，即修改已经存在的记录，使用频率极高

1.INSERT

2.DELETE删一条记录。

3.UPDATE

4.SELECT 使用频率极高且场景很多，与sql优化也有关系

3.DCL:数据控制语言，即控制DML的语言

1.COMMIT 提交 (事物)

2.ROLLBACK 回滚 (事物)

3.SAVEPOINT 保存点 (可以回滚到具体的保存点上)

4.GRANT (权限赋予)

5.REVOKE (权限回收)

1.2 Sql语言的规则与规范

1.2.1 基本规则

1.sql语句可以写在一行也可以多行，但是为了提高可读性，存在子语句时分行写，必要时使用缩进。(一个查询语句写8行)

2.每条命令可以以；或\G或\g结束，；在只执行一条命令或者在多个命令中的最后一条命令时可以不写，在特殊的环境内\G和\g使用的频率较高(linux)。

3.关键字不能缩写也不能分行

4.标点符号中所有的括号和引号必须成对结束，字符串、日期时间类型的变量要用单引号，列的别名要用双引号且不建议省略。

1.2.2 sql大小写规范

mysql在windows环境下大小写不敏感，但是在linux下mysql对大小写敏感。

书写规范：数据库名，表名，表别名，字段名，字段别名都小写，关键字，函数名和绑定变量都大写。

1.2.3 如何导入现有的数据表(sql脚本)？

方式1：(使用命令行 source) 文件的全路径名

方式2：图形化界面导入

1.2.4 sql语句的特性

1.如果一个字符串为一个隐式的数值，那么无论操作符是什么mysql都会尝试将其转换为一个数字。

示例：

SELECT 1 + '1' FROM tb_a #sql会尝试将'1'转换为数字
SELECT 'a'+'1' FROM tb_a #如果是非隐式类型会转换失败变成0
SELECT '1'> 2 FROM tb_a 

2.空(null)值参与运算，结果一定是null。

1.3 基本SELECT语句

1.3.1 SELECT .....FROM

示例:

SELECT * FROM 表名

TIP:*代表所有列，也可以指定列名用逗号隔开。

1.3.2 列的别名

列的别名即隐藏原先字段名称，使用其别名输出，创建方法为：字段名 关键词 别名，举例如下：

1.使用空格

SELECT sid id FROM stu;

2.使用AS关键词

SELECT sid AS id FROM stu;

3.使用双引号""

SELECT sid "id" FROM stu;

//以上三种方法都可以直接使用中文

分析以下创建别名的方式是否会出现错误：

SELECT sid money id FROM stu;

//解决方法：使用双引号将整个别名覆盖

1.3.3 去除重复行

即去除字段中元素重复的行，只保留其中一个，如果有多个元素，那么只有当它们完全相同才会被去除。关键字：DISTINCT

示例：

SELECT DISTINCT age FROM stu;#如果age字段有重复元素那么只会保留它们其中的一个。

SELECT DISTINCT sid,age FROM stu;#如果sid和age的字段有完全相同的元素，那么只会保留它们其中的一个，有一个元素不同就会被保留，这样做没有意义，除非超大型数据库需要多个限制条件进行去重查询。

注意，如果你想要去重的同时查询其它内容，需要配合子查询和进一步添加排序或限制条件。

1.3.4 空值参与运算

空值即字段内的元素是null。

1.null不等同于0，更不能直接当作0去对待，且空值不等于空字符串和'null'。

2.只要空值参与了运算那么其结果也一定是null。

示例：无论如何计算，只要age和score任意一个为null，那么结果一定是null。

SELECT * FROM stu;
SELECT sid,sname,age,score,age*(1 + score) * 12 "年成绩" FROM stu;

// 如果必须使用null参与计算，可以使用IFNULL函数，如果score为null就将其替换为0在计算。

示例1：

SELECT sid,sname,age,score,age*(1 + IFNULL(score , 0)) * 12 "年" FROM stu;

示例2：

如果age为null也将其替换为0再计算，注意age如果为0时有一个乘数为0；

 

1.3.5 着重号 ``

如果你的字段内有保留字、数据库系统关键字或与常用方法冲突，如果坚持使用请用着重号将其引起来。

示例：如果使用的表有关键字，会报错。

//加上着重号即可正常读取

1.3.6 查询 "常数"

为所有查询到的字段添加一个"常数"，这个常数即可以是字符串，也可以是数字，甚至可以是一段计算的结果，添加位置取决于"常数"的位置。

示例字段：

示例1：字符串和数字

示例2：不同位置

 

示例3：计算结果，甚至可以为它加上别名

1.4 显示表结构

DESCRIBE 或DESC后跟表名，打印表中的所有结构信息 。

1.5 过滤数据

过滤数据即使用sql过滤语句where筛选出指定条件的数据，增删改查都可用。

示例：

//注意，虽然SQL在Windows中大小写是不敏感的。

但是，数据库和查询语句的大小写对不上依旧可以正常执行并输出结果是因为Mysql本身不严谨造成的，并非查询语句和Windows本身对大小写不敏感的问题。此问题如果换成Oracle数据库如果查询语句和数据库内的数据大小写对不上不会显示出来。

二.算术运算符

2.1 算数运算符

算数运算符是指：加+ ， 减- ，乘*，除/(div)， 取余%(mod)

示例1：加减运算

SELECT 100, 100 + 0 , 100 - 0 , 100 + 50,100 + 50 * 30 , 100+35.5,100-35.5
FROM tb_a

注意以下语句：

SELECT 100 + '1' from tb_a;

//此处是101，java语言的+有两个作用，一个用于计算，一个用于字符串拼接，但是注意在sql语句当中，+只有运算的作用。

在算术运算中，如果是非隐式的数值，会将字符串作为0看待，例如：

SELECT 100 + 'a' #结果为100
FROM tb_a

SELECT 'a' + 'a' #结果为0

示例2：乘除运算

SELECT 100, 100 * 1 , 100 * 1.0 , 100 / 1.0,100 / 2, 100 div 0

注意在mysql中，如果除不尽有小数，因此，在mysql中的除法运算总会是浮点型，即使被除数/除数没有小数。

而在java中，只要你的变量没有被定义关于小数的函数，那么由计算而产生的小数便总是会被舍弃。

在mysql中如果分母为0，那么结果绝对为null。

示例3：取余运算%

SELECT 12 % 3 , 12 % 5 , 12 MOD -5 , -12 % 5 , -12 % -5
FROM tb_a

注意，取余运算结果的符号只与被除数的正负有关。

示例4：取余小练习：查询id为偶数的所有员工的信息

代码：

SELECT * FROM stu WHERE sid % 2 = 0

2.2 比较运算符

比较运算符即判断某个表达式是否成立，如果为真结果为1，如果为假则结果为0，mysql的比较运算符分为符号运算符和关键字运算符。此外，在mysql的比较运算符中还有可能返回Null(搅屎棍)。

2.2.1 符号运算符

符号运算符有： = 等于 ， <=> 安全等于 ， 不等于<> != , < , <= , > , >=

示例1：= 等于

SELECT 1 = 2 , 1 != 2 , 1 ='1' , 1 = 'a' , 0 = 'a', 'a' = 'a' , 'ab' = 'ab', 'a' = 'b';
FROM tb_a

注意在比较运算中，如果等式两边都为非隐式数值时，sql会将其转换为ASCII码进行比较，而不是直接转换为0。如果等式只有一方为字符串，那么依旧会被转换成0.

示例2：比较运算符在实际搜索中为null的情况

SELECT * FROM stu where age = 'Null'

//只要null参与了运算，那么结果一定是null，搜索原理为判断表内字段age 名称是否等于null，但是null参与运算只会返回null，所以没有任何结果。

示例3：<=> 安全等于

安全等于的功能与等于完全相同，唯一的区别是安全等于可以对为空的字段进行判断，为Null而生，除了此方法，使用关键字运算符ISNULL 或 IS NULL也可以。

SELECT * FROM stu where age <=> null

其它运算符较为常见，不再举例。

2.2.2 关键字运算符

关键字运算符有以下11种：

1.IS NULL 为空

2.IS NOT NULL 不为空

3.LEAST 最小值

4.GREATEST 最大值

5.BETWEENAND 两值之间

6.ISNULL 为空

7.IN 属于，一个值是否属于某个表

8.NOT IN 不属于，一个值是否不属于某个表

9.LIKE 模糊匹配

10.REGEXP 正则表达式

11.RLIKE 正则表达式

示例1：IS NULL、IS NOT NULL和ISNULL的使用

SELECT * FROM stu WHERE age  IS NOT NULL 
#查询stu表中age字段非空的全部内容，常用
SELECT * FROM stu WHERE age  IS NULL 
#查询stu表中age字段为空的全部内容，常用
SELECT * FROM stu WHERE ISNULL(age) 
#查询stu表中age字段为空的全部内容
SELECT * FROM stu WHERE NOT age  <=> NULL  
#查询stu表中age字段非空的全部内容，注意NOT位置

示例2：GREATEST和LEAST(最大值和最小值)的使用

SELECT LEAST('g','s','k','b','n'),GREATEST('z','m','w','o','p') FROM stu
#输出ASCII码最小的一个和输出ASCII码最大的一个
#如果为字符串，那么会相互比较ASCII码，LEAST是输出最小的ASCII码，GREATEST是输出最大的ASCII码

SELECT LEAST(oid,uid) FROM `order`
#如果比较的是字段名，那么挨个会比较它们的ASCII码，直到有一个不同为止，随后输出ASCII码较小的那一个字段。
#与字段长度没有关系，如果是中文和英文比较，那么会直接输出英文
#如果是英文和数字比较，会直接输出数字
#中文相互排序会依照其字典，尽量使用英文和数字。
SELECT GREATEST(oid,uid) FROM `order`
#同上，但是是输出ASCII码较大的一个字段

SELECT LEAST(LENGTH(oid),LENGTH(uid)) FROM `order`
#函数length比较字段的长度并输出较短的那一个字段的具体长度。
SELECT GREATEST(LENGTH(oid),LENGTH(uid)) FROM `order`
#同上但输出较长的那一个字段的具体长度。
#如果需要输出具体字段名，请前往单行函数。

示例3：BETWEEN ... AND ...两值之间

SELECT * FROM stu WHERE age BETWEEN 5 AND 20
#查询年龄在5-20之间的所有字段信息。注意参数只能是数字，第一个参数是下限，第二个参数是上限。查询结果包括5和20。
SELECT * FROM stu WHERE age >= 5 AND age <= 20
#写法2
SELECT * FROM stu WHERE age >= 5 && age <= 20
#写法3
#注意参数位置，between关键字后用于指定范围，不能添加字段名

SELECT * FROM stu WHERE age NOT BETWEEN 5 AND 20
#查询年龄不在5-20之间的所有字段信息。注意参数只能是数字，第一个参数是下限，第二个参数是上限。查询结果包括5和20。
SELECT * FROM stu WHERE age < 5 OR age > 20
#写法2(或)

示例4：IN和NOT IN ， 是和非

SELECT * FROM stu where age IN (2,19,17)
#查询年龄是2，19，17的人的所有信息
SELECT * FROM stu where age = 2 OR age = 19 OR age = 17
#不使用IN的写法

SELECT * FROM stu where score NOT IN (177,52)
#查询成绩非177和52的人的所有信息
SELECT * FROM stu where score != 177 AND score != 52
#不使用关键字NOT IN的写法，不等于177，且不等于52

示例5：Like 模糊查询

SELECT * FROM stu where sname LIKE '%j%';
#查询名字中包含J的学生信息，%代表未知的字符个数，单Like关键字使用此运算符会失效。
SELECT * FROM stu where sname LIKE 'j%';
#查询名字中以J开头的学生信息
SELECT * FROM stu where sname LIKE '%j';
#查询名字中以J结尾的学生信息

SELECT * FROM stu where sname LIKE '%j%' and sname LIKE '%h%';
#查询名字中既包含j又包含h的学生信息
SELECT * FROM stu where sname LIKE '%j%h%' OR sname LIKE '%h%j%'
#查询名字中既包含j又包含h的学生信息写法2

SELECT * FROM stu where sname LIKE '_o%';
#查询姓名字段第二个字符为o的学生的信息，每个下划线_代表1个未知字符。

SELECT * FROM stu where sname LIKE '\__o%';
#查询姓名字段第一个字符为下划线，第三个字符为o的学生的信息，使用了转义字符\来避免查询到第一个未知字符带来的结果，使查询更准确。
#转义字符也可以使用ESCAPE关键字自定义。

示例6：REGEXP和RLIKE正则表达式(正则表达式在部分语言中通用,较重要)

注意，正则表达式是独立于Mysql的一个服务，这里仅简单涉及，详细可翻阅附录的正则表达式查询

1'^'：^后面的字符代表该字符串是否以.....开头,例如 ^a代表该字符串是否以a开头

2.'$'：$前面的字符代表该字符串是否以.....结尾，例如a$代表该字符串是否以a结尾

1. '.':代表任意一个未知字符，与LIKE中的_功能相同。

4."[....]":匹配在方括号内的任何一个字符，例如[abc]匹配a或b或c，可以使用-表示范围，例如[a-z]表示匹配任何字母。

5.'*' 匹配在它前面的多个字符，比如X*就是匹配任何数量的X字符。

SELECT 'shkstart' REGEXP '^s' ,'shkstart' REGEXP 't$' ,'shkstart' REGEXP 'hk'FROM stu;
#是否以s开头，是否以t结尾，字段内是否包含hk
SELECT 'shkstart' REGEXP 't..t','shkstart' REGEXP '[sg]'
#要详细搜索t什么东西，但是中间2个字符我不知道，看是否存在。[sg]这两个字符只要存在一个就返回1.

在mysql中，大部分的多条件语句一般都需要将所有字段书写完整，不仅仅只有AND关键字，其它应用场景也要注意。

示例：

SELECT * FROM stu where score != 177 AND 52
#该写法会导致score = 52的字段信息出现

正确写法：

SELECT * FROM stu where score != 177 AND score != 52

虽然大部分语句可以直接跟数字，比如OR、AND等，但是会导致条件逻辑不清晰进而造成结果不正确，虽然mysql对此并不会提示错误。

2.3逻辑运算符

逻辑运算符主要用于判断表达式的真假，在mysql中，逻辑运算符会返回1，0，null三种结果，而在java中，逻辑运算符会返回true和false。

1.逻辑非：NOT 或 ！

逻辑非即将结果颠倒

示例：注意下方语句NOT和！的位置

SELECT* FROM stu WHERE NOT age != 29
#查询stu表中年龄不为29的学生的所有信息
SELECT* FROM stu WHERE age NOT BETWEEN  2 AND 20
#查询stu表中年龄范围不为2-20的学生的所有信息
SELECT* FROM stu WHERE age IS NOT NULL
#查询stu表中年龄非空的学生的所有信息
SELECT* FROM stu WHERE NOT age <=> NULL
#查询stu表中年龄非空的学生的所有信息

2.逻辑与：AND 或 &&

逻辑与即查询的内容要同时满足两个条件都为真时才会输出，与JAVA的AND功能相同。

示例：注意AND语句参数的用法

SELECT* FROM stu WHERE age = 29 AND age = 2
#查询stu表年龄为29且年龄为2的学生所有信息 <-这不可能所以无结果
SELECT* FROM stu WHERE age = 29 AND sname = "Mark J"
#查询stu表中年龄为29且名字为Mark J的学生的所有信息

3.逻辑或：OR 或 || [难点]

逻辑或的查询的语句两个条件只要满足其一为真便会直接输出，在mysql中要注意与java中的逻辑或区分，sql的OR只要任何条件满足就会直接输出其多个条件对应的查询数据，全部对应，全部输出。sql没有分支条件，而java的OR只要满足任意一个条件会使整个式子都为真且Java的分支条件语句可以再次细分判断。

它们的功能不完全相同：sql满足多少输出多少，不想输出只能删除条件。Java一个满足，不使用分支条件会全部输出，也可以使用分支条件指定想要输出的内容。

示例：

SELECT* FROM stu WHERE age = 29 OR age = 2
#查询stu表中年龄为29和年龄为2的学生的所有信息

4.逻辑异或：XOR，不常用

逻辑异或即查询的两个条件一真一假时才会输出，否则不会输出。

示例：

SELECT* FROM stu WHERE age = 29 XOR sname = "老刘"
#查询stu表中年龄为29且年龄不为2或年龄不为29且年龄为2的学生的所有信息。
SELECT * FROM stu where score != 177 XOR score !=52
#查询stu表中成绩为177和成绩为52的所有学生的信息。

5.注意：OR可以和AND一起使用且AND的优先级要比OR高，但是在实际的使用中AND的优先级不一定比OR高，所以，在设计sql语句时如果拿不准，使用()。更多见运算符优先级

示例：

1.(条件1)AND(条件2) OR (条件1)AND(条件2)

2.(条件1 OR 条件2)AND(条件1 OR 条件2)

2.4 位运算符(二进制重点)

位运算符使用频率较小，一般用在sql权限和网络协议包处理特定比特位等方面，sql优化不会轻易使用位运算符，SQL优化一般会优化select查询减少磁盘IO，索引以及通过优化表设计提高其查询性能。

1.&

2.|

3.^

4.~

5.>>

6.<<

原理：

①二进制：二进制在数学领域中是一种逢2进一的计数制度，而十进制在数学领域中是一种逢10进一的制度，即十进制可以累计到9，而二进制只能累计到2，也就是说，十进制有9个(1-9)计数，加上0总共有10个计数。而二进制只有1一个计数，加上0总共只有两个计数(即0和1)。故称之为二进制，计算机领域底层全部使用二进制作为进位计数制。

②二进制的权重：在计算机领域中，一个byte字节为8个bit位，即8个二进制计数，在计算机存储中，一个字节是最小的存储单位，故一个字节内从左至右的权重占比如下：

所以，二进制数10110101表示10进制为181

③&按位且运算符：以十进制的12为例，只有12的二进制位与5的二进制位都为1才会判定该二进制位为1，故结果为4：

④|按位或运算符：同理，12的二进制位与5的二进制位只要它们其中一个为1就会判定该二进制位为1，故结果为13.

⑤^按位异或运算符：同理，12的二进制位与5的二进制位必须一个为1一个为0才会判断该二进制位为1，故结果为9

⑥~按位取反运算符：带有此运算符的值二进制全部取反，例如示例中将1的所有二进制位数0变为1，1变为0

⑦>>,<< 左移一位和右移一位：左移一位代表将当前值放大2倍，右移一位代表将当前值除以2，此功能极少用，需要注意放大时如果超出定义的字段类型长度会报错且超出最大支持长度结果为0，如果值为1右移一位结果为0；

示例：

SELECT 12 & 5 , 12 | 5 , 12 ^5 FROM tb_a;
SELECT 10 & ~1 FROM tb_a;

2.5运算符的优先级

附录

;更多的正则表达式内容需要查阅其它资料。