MySQL学习day-33: 查询数据

一、MySQL 中的 DML 操作

1.添加数据(INSERT)：

（1）选择插入：

语法：INSERTINTO 表名(列名 1，列名 2，列名 3.....)VALUES(值 1，值 2，值 3......)；

（2）完全插入：

语法：INSERTINTO 表名 VALUES(值 1，值 2，值 3......)；

（3）插入多条数据：

语法：insert into 表名[(列名1,列名2....)] values(值1,值2...),(值21,值22...)...；

2.自动增长(auto_increment)：

MySQL 中的自动增长类型要求：

1）一个表中只能有一个列为自动增长。 

2）自动增长的列的类型必须是整数类型。 

3）自动增长只能添加到具备主键约束与唯一性约束的列上。 

4）删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然 后在删除约束。

（2）默认值处理：

在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指

定该列的值，那么 MySQL 会将默认值添加到该列中。

创建表时指定列的默认值：

create table 表名(列名1 类型 约束 ，列名2 类型,列名3 类型 default 默认值)；

插入数据时的默认值处理：

如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是 完全项插入需要使用 default 来占位。

3.更新数据(UPDATE)：

语法：UPDATE 表名 SET 列名=值，列名=值 WHERE 条件；

（1）mysql 的 update 的特点：

1）更新的表不能在 set 和 where 中用于子查询; 

2）update 后面可以做任意的查询。

4.删除数据(DELETE)：

（1）使用 DELETE 子句：

语法：DELETEFROM 表名 WHERE 条件；

（2）使用 TRUNCATE 清空表：

语法：TRUNCATETABLE 表名；

（3）DELETE 与 TRUNCATE 区别：

1）truncate 是整体删除（速度较快）， delete 是逐条删除（速度较慢）； 

2）truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因； 

3）truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而 不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。

5.MySQL 中的事务处理：

在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条 DML 语句 就开启了事物，并且提交了事务。

（1）关闭 MySQL 的事务自动提交：

STARTTRANSACTION 

DML操作.... 

COMMIT|ROLLBACK；

二、MySQL 查询数据

1.MySQL 的基本查询：

（1）MySQL 的列选择：

SELECT*/投影列 FROM 表名;

（2）MySQL 的行选择：

SELECT*| 投影列 FROM 表名 WHERE 选择条件；

2.SELECT 语句中的算术表达式：

1）+ ：加法运算；

2）- ：减法运算；

3）* ：乘法运算 ；

4）/ ：除法运算，返回商 ；

5）% ：求余运算，返回余数；

3.MySQL 中定义空值：

包含空值的算术表达式计算结果为空。

4.MySQL 中的列别名：

语法：SELECT 列名 AS 列别名 FROM 表名 WHERE 条件；

5.MySQL 中的连字符：

MySQL中并不支持||作为连字符，需要使用concat函数。在参数数量上与oracle的concat 函数有区别。

6.MySQL 中去除重复：

在 SELECT 语句中用 DISTINCT 关键字除去相同的行。

7.约束和排序数据：

（1）MySQL 中的比较条件：

比较运算符：等于=；大于>；大于等于>= ；小于<；小于等于<=；不等于!=或<>；

（2）模糊查询：

关键字：like

%表示任意多个任意字符 ；

_表示一个任意字符。

（3）逻辑运算符：

and 、or 、not；

（4）范围查询：

1）between...and；

2）in 表示在一个非连续的范围内。

区别：

1）如果要让选择的结果在某一个范围之内的话可以用between和in这两个关键字。

2）如果他们都为空的话，between会限制select语句让它一条记录都无法选出来，但是in的话则相反，这个限制条件不会起任何作用，会选择出所有的记录。

（5）空值判断：

1）判断空 isnull ；

2）判断非空 is not null；

（6）使用 ORDERBY 排序：

用 ORDERBY 子句排序默认升序；

1）ASC: 升序排序；

2）DESC: 降序排序；

8.MySQL 中常见的单行函数：

（1）大小写控制函数：

1）LOWER(str) 转换大小写混合的字符串为小写字符串；

2）UPPER(str) 转换大小写混合的字符串为大写字符串。

（2）字符处理：

1）CONCAT(str1,str2,...) 将 str1、str2 等字符串连接起来 ；

2）SUBSTR(str,pos,len) 从 str 的第 pos 位（范围：1~str.length）开始，截取长度为 len 的字符串；

3）LENGTH(str) 获取 str 的长度 INSTR(str,substr) 获取 substr 在 str 中的位置 ；

4）TRIM(str) 从 str 中删除开头和结尾的空格（不会处理字符串中间含有的空格）；

5）LTRIM(str) 从 str 中删除左侧开头的空格 ；

6）RTRIM(str) 从 str 中删除右侧结尾的空格 ；

（3）数字函数：

1）ROUND(arg1,arg2)：四舍五入指定小数的值。 

2）ROUND(arg1)：四舍五入保留整数。 

3）TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。 

4）MOD(arg1,arg2)：取余。

（4）日期函数：

1）SYSDATE() 或者 NOW() 返回当前系统时间，格式为 YYYY-MM-DDhh-mm-ss ；

2）CURDATE() 返回系统当前日期，不返回时间 ；

3）CURTIME() 返回当前系统中的时间，不返回日期 ；

4）DAYOFMONTH(date) 计算日期 d 是本月的第几天 ；

5）DAYOFWEEK(date) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 ；

6）DAYOFYEAR(date) 返回指定年份的天数 ；

7）DAYNAME(date) 返回 date 日期是星期几；

8）LAST_DAY(date) 返回 date 日期当月的最后一天。

（5）转换函数：

1）DATE_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to_char()）；

2）STR_TO_DATE(str,format) 将字符串转换成日期（类似 oracle 中的 to_date()）；

（6）通用函数：

1）IFNULL(expr1,expr2) 判断 expr1 是否为 null，如果为 null，则用 expr2 来代替 null （类似 oracle 的 NVL()函数）；

2）NULLIF(expr1,expr2) 判断 expr1 和 expr2 是否相等，如果相等则返回 null，如果不 相等则返回 expr1 ；

3）IF(expr1,expr2,expr3) 判断 expr1 是否为真（是否不为 null），如果为真，则使用 expr2 替代 expr1；如果为假，则使用 expr3 替代 expr1（类似 oracle 的 NVL2()函数）；

 4）COALESCE(value,...) 判断 value 的值是否为 null，如果不为 null，则返回 value；如 果为 null，则判断下一个 value 是否为 null……直至出现不为 null 的 value 并返回或者返回最 后一个为 null 的 value；

三、多表连接查询

1）确定需要查询的哪些列? 

2）确定需要查询的列分布在哪些表中? 

3）确定表之间的关联关系(主外键的关系)；

1.等值连接：

包含在内连接和外连接中，因为内连接和外连接都是需要连接条件的，条件为=则为等值连接。

2.非等值连接：

使用between...and...或不等号；

3.自连接：

自然连接是一种特殊的等值连接，要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

4.外连接(OUTERJOIN)：

1）左外连接(LEFTOUTERJOIN)；

2）右外连接(RIGHTOUTERJOIN)；

3）全外链接：：MySQL 中不支持 FULLOUTERJOIN 连接 可以使用 union 实现全完连接。

4）union all：可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。

5）UNIONALL：只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

语法：（SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件 ）UNION （SELECT 投影列 FROM 表名 RIGHTOUTERJOIN 表名 ON 连接条件）；

5.自然连接（NATURALJOIN）：

自然连接是一种特殊的等值连接，要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。

6.内连接(INNERJOIN)：

内连接基本与自然连接相同，内连接不要求两属性列同名，可以用using或on来指定某两列字段相同的连接条件。

7.聚合函数：

（1） AVG(arg)函数：对分组数据做平均值运算；参数类型只能是数字类型。

（2）SUM(arg)函数：对分组数据求和；参数类型只能是数字类型。

（3）MIN(arg)函数：求分组中最小数据；参数类型可以是字符、数字、日期。

（4）MAX(arg)函数：求分组中最大数据；参数类型可以是字符、数字、日期。

（5）COUNT 函数：返回一个表中的行数。

8.数据组（group by）：

通过group by子句对结果按某条件进行分组；通过having子语句约束。