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）：