mysql查询整张表中的空值_033 MySQL的更新删除、事务处理和基本查询

1. MySQL中的默认值处理

(1) 在MySQL中如何定义默认值？

在MySQL中可以使用DEFAULT为字段设定一个默认值。如果在插入数据时并未指定该列的值，那么MySQL会将默认值添加到该列中。

实例：创建emp3表，该表包含emp_id主键且自动增长，包含name，包含address该列默认值为“未知”。

create table emp3(emp_id int primary key auto_increment,name varchar(20),address varchar(50) default 'unknown');

实例：修改emp3表，添加job_id该列默认值为0。

alter table emp3 add column job_id int default 0;

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

实例：向emp3表中添加数据，要求address列与job_id列使用默认值作为该列的值。

insert into emp3(name) values('Lucas');

或者全列插入用default占位：

insert into emp3 values(default,'Alex',default,default);

2. MySQL中的更新操作

(1) 在MySQL中更新表中数据的语句是什么？

Update 表名 set 列名=值，列名=值 where 条件

实例：更新emp3表中的id为1的数据，添加address为Beijing。

update emp3 set address ='BeiJing' where emp_id=1;

实例：更新emp3中id为2的数据，将地址修改为与id为1用户的地址相同。

Oracle：

update emp3 set address =(select address from emp3 where emp_id=1) where emp_id=2;

MySQL中：

update emp3 e,(select address from emp3 where emp_id=1)t set e.address=t.address where e.emp_id=2;

实例：更新emp3中id为2的数据，将地址修改为与id为1用户的地址相同。

因为MySQL中set之后不能包括更新表的子查询，但是我们可以把需要的信息查询出来再套一层查询。

update emp3 set address ='ShangHai' where emp_id=1;

update emp3 e set e.address = (select t1.address from (select address,emp_id from emp3)t1 where t1.emp_id=1) where e.emp_id=2;

(2) MySQL中的数据更新有什么特点？

更新的表不能再set和where中用于子查询；

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

3. MySQL中的删除数据

(1) 在MySQL中删除表中数据有几种方式？他们之间有什么区别？

Delete from 表名 where 条件

实例：删除emp3中emp_id为1的雇员信息。

delete from emp3 where emp_id=1;

使用truncate清空表。

truncate表名。

truncate emp3;

delete与truncate区别：

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

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

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

(2) 删除emp3表中emp_id为1的雇员信息。

delete from emp3 where emp_id=1;

4. MySQL的事务处理

(1) 在MySQL中事务默认的处理机制是什么机制？

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

(2) 如何关闭MySQL中的事务自动提交？

Start transaction

Dml

Commit|rollback

5. MySQL的基本查询1

(1) MySQL中查询数据的语法格式是什么？

Select *|投影列 from 表名；

实例：查询departments表中所有的数据

Select * from departments;

(2) 在查询语句中支持哪些算术表达式？

+：加法运算；

-：减法运算；

*：乘法运算；

/：除法运算。

%：求余运算返回余数。

实例：计算employees表中的员工全年薪水加100以后的薪水是多少？

Select employee_id,last_name,email,12*salary+100 from employees;

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

(3) 在MySQL中查看数据库编码的语句是什么？

select iss.SCHEMA_NAME,iss.DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA iss where iss.SCHEMA_NAME='database1';

(4) 删除数据库的语句是什么？

Drop database;

6. MySQL的基本查询2

(1) 在MySQL中算术表达式中含有空值结果是什么？

会返回空值

(2) 在MySQL中如何为结果列起别名？

与Oracle中一致：

Select last_name name from employees;

(3) 在MySQL中如何做结果集的连接处理？

MySQL中并不支持||作为连字符，需要使用concat函数。在参数数量上与oracle的concat函数有区别。可以支持多个拼接，而oracle只能一次拼接两个，拼接多个需要嵌套。

查询雇员表中的所有数据，将所有数据连接在一起，每列值中通过#分割。

select concat(employee_id,'#',last_name,'#',email,'#',salary,'#',commission_pct) from employees;

(4) 在MySQL中如何剔除重复数据？

在select语句中用distinct关键字来去除重复行。

select distinct dept_id from employees;

7. MySQL的约束和排序数据

(1) 在MySQL中支持哪些比较运算符？

等于=

大于>

大于等于>=

小于<

小于等于<=

不等于!=或者<>

(2) 在MySQL中如何实现模糊查询？

通过like关键字

%表示任意多个任意字符

_表示一个任意字符。

查询employees中第二个字母是e的雇员信息。

select * from employees where last_name like '_e%';

注意Oracle中的占位符需要用escape关键字来定义，MYSQL中只需用下划线_就可以。

(3) 在模糊查询中占位符用什么符号表示？

%表示任意多个任意字符

_表示一个任意字符。

(4) 在MySQL中支持哪些逻辑运算符？

And or not

查询employees表中雇员薪水是5000的并且名字中含有d的雇员信息。

select * from employees where last_name like '%d%' and salary=5000;

(5) 在MySQL中如何做范围查询？

用between and

In()

大于小于

(6) 在MySQL中如何判断空值？

判断空 is null

判断非空 is not null

select * from employees where commission_pct is not NULL;

(7) 在MySQL中如何对结果集做排序处理？

Order by 子句排序

Asc 升序排序 默认

Desc 降序排序

select * from employees ORDER BY salary;

也支持多列排序，根oracle一样。

8. MySQL中常见单行函数1

(1) 在MySQL中处理字符大小的函数有哪些？

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

UPPER（str）：转换大小写混合的字符串为大写字符串。

(2) 在MySQL中常见的字符函数有哪些？

CONCAT（str1,str2,...）将str1,str2等字符串连接起来。

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

LENGTH（str）：获取str的长度

INSTR（str,sbustr）获取substr在str中的位置

LPAD(str,len,padstr)/RPAD(str,len,padstr)左填充或右填充

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

LTRIM(str)：从str中删除左侧开头的空格

RTRIM（str）：从str中删除右侧结尾的空格

REPLACE(str,from_str,to_str)：将str中的from_str替换为to_str（会替换掉所有符合from_str的字符串）

(3) 在MySQL中常见的数字函数有哪些？

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

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

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

MOD(arg1,arg2)：取余。

(4) 在MySQL中常见的日期函数有哪些？

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

CURDATE()：返回系统当前日期，不返回时间

CURTIME（）：返回当前系统中的时间，不返回日期。

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

DAYOFWEEK(date):日期d这天是星期几。1星期日。2星期一

DAYOFYEAR(date)：返回指定年份的天数

DAYNAME（date）:返回date这一天是星期几。英文全拼

LAST_DAY(date):返回dete日期当月的最后一天

9. MySQL中常见单行函数2

(1) 在MySQL中常见的转换函数有哪些？

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

实例：SELECT DATE_FORMAT(SYSDATE(),'%Y年%m月%d日')；

STR_TO_DATE(date,format):将字符串转换成日期（类型oracle中的to_date()）

%a 缩写星期名

%b 缩写月名

%c 月，数值

%D 带有英文前缀的月中的天

%d 月的天，数值（00-31）

%e 月的天，数值（0-31）

%f 微秒

%H 小时（00-23）

%h 小时（01-12）

%I 小时（01-12）

%i 分钟，数值（00-59）

%j 年的天（001-366）

%k 小时（0-23）

%l 小时（1-12）

%M 月名

%m 月，数值（00-12）

%p AM或PM

%r 时间，12小时（hh:mm:ss AM或PM）

%S 秒（0-59）

%s 秒（0-59）

%T 时间，24小时(hh:mm:ss)

%U 周（00-53）星期日是一周的第一天

%u 周（00-53）星期一是一周的第一天

%V 周（01-53）星期日是一周的第一天，与%X使用

%v 周（01-53）星期一是一周的第一天，与%x使用

%W 星期名

%w 周的天（0=星期日，6=星期六）

%X 年，其中的星期日是周的第一天，4位，与%V使用

%x 年，其中的星期一是周的第一天，4位，与%v使用

%Y 年，4位

%y 年，2位

(2) 在MySQL中常见的通用函数有哪些？

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

NULLIF（expr1,expr2）判断expr1与expr2是否相等，如果相等则返回null，如果不相等则返回expr1

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

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

CASE WHEN THEN ELSE END条件函数

10. MySQL的多表查询

(1) 在MySQL中可以使用哪些方式对多表查询？

等值连接：

查询雇员King所在的部门名称

SELECT d.department_name from employees e , departments d where e.dept_id=d.department_id and e.last_name='King';

非等值连接：

创建sal_level表，包含lowest_sal,highest_sal,level.

实例：查询所有雇员的薪水等级

select e.*,s.level from employees e,sal_level s where e.salary BETWEEN s.low_sal and s.high_sal;

自连接：SELECT em.last_name,ma.last_name from employees em,employees ma where em.manager_id=ma.employee_id;

11. MySQL的外连接查询

(1) 在MySQL中支持几种外连接查询？

左外连接left outer join：

select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.dept_id=d.department_id;

(2) 在MySQL中如何将两个结果集合并？

MySQL中不支持FULL OUTER JOIN连接，可以使用union实现完全连接。

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

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

(3) 结果集合并的语法结构是什么？

Select 投影列 from 表名 left outer join 表名 on 连接条件 union select 投影列 from 表名 right outer join 表名 on 连接条件

实例：查询所有雇员的名字及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。

(select e1.last_name,d1.department_name from employees e1 LEFT OUTER JOIN departments d1 on e1.dept_id=d1.department_id) union (SELECT e2.last_name,d2.department_name from employees e2 RIGHT OUTER JOIN departments d2 on e2.dept_id=d2.department_id);

12. MySQL中使用SQL99标准查询数据

SQL99中的交叉连接（CROSS JOIN）做笛卡尔乘积

Natural join 自然连接，根据两个表中列名相同的自动连接。

修改employees表中的dept_id列将该列的名称修改为department_id。

alter table employees CHANGE COLUMN dept_id department_id int;

使用自然连接查询所有有部门的雇员的名字和部门名称

select e.last_name,d.department_name from employees e NATURAL join departments d where e.last_name='Lucas';

使用内连接INNER JOIN查询雇员名字为Lucas的雇员ID，薪水与部门名称。

select e.last_name,d.department_name from employees e INNER JOIN departments d on e.department_id=d.department_id;