Mysql数据库（中）——增删改查的学习（全面，详细）

上一篇主要对查询操作进行了详细的总结，本篇主要对增删改操作以及一些常用的函数进行总结，包括流程控制等；以下的代码可以直接复制到数据库可视化软件中，便于理解和练习；

常用的操作：
#函数：
SELECT BIN(10),HEX(10),OCT(10);
#if(a,b,c)如果a为true返回吧，否则返回c
SELECT last_name,salary,if(salary>6000,'高工资','低工资') 'grades'
FROM employees;
SELECT last_name,commission_pct,if(commission_pct IS NOT NULL,commission_pct,0) 'commission_pct'
FROM employees;
SELECT last_name,salary,case when salary>=15000 then '很牛'
                             when salary>=10000 then '牛'
                             when salary>=8000 then '可以'
                             ELSE '草根' END 'details'
FROM employees;
#case when then when then when then else end:
#练习：查询部门号为10，20，30的员工信息，若部门号为10，则打印其工资的1.1倍，20号部门，工资的1.2倍，30号部门，1.3倍，其他1.4倍；
SELECT employee_id,department_id,last_name,salary,case department_id when 10 then salary*1.1
when 20 then salary*1.2
when 30 then salary*1.3
ELSE salary*1.4 END 'details'
FROM employees 
WHERE department_id IN (10,20,30);
#常见的聚合函数：AVG,SUM,MAX,MIN,COUNT:其中max和min适合数值类型、字符串类型和日期类型的字段；
SELECT AVG(salary),SUM(salary)
FROM employees;
SELECT MAX(salary),last_name,employee_id
FROM employees;
#count查询对应属性有值的数据个数；不包含null的；
SELECT COUNT(salary),COUNT(employee_id),COUNT(department_id)
FROM employees;
#想知道表中有多少条记录：！
SELECT COUNT(*)
FROM employees;
#group by分组进行操作：
#例如查询每个部门的平均工资：
SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;
#练习：查询各个department_id,job_id的平均工资
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;
#对于group by的使用，select后面所要查询的东西，必须放在group by后面，group by后面的不一定非要写到select后面；如下：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY department_id;#去掉了job_id，然后仔细想按照department_id分组，每一个部门是一条数据，job_id不会单独列出来，因此这样写是错误的；
#注意，如果使用了聚合函数，那么需要将where换成having；
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000;
#创建和管理表：创建方式：
CREATE DATABASE test1;
CREATE DATABASE if NOT EXISTS test2;
SHOW DATABASES;
#展示数据库表：
USE atguigudb;
SHOW TABLES;
#修改数据库结构：alter 
#删除数据库：
DROP DATABASE if EXISTS test2;
SHOW DATABASES;
#创建表：
#方式一：
USE atguigudb
CREATE TABLE if NOT EXISTS myempl(
id INT,
emp_name VARCHAR(15),
hire_name DATE
);
SHOW TABLES; 
#方式二：基于已有的表来创建：可以使用学过的select语句来进行创建，并且数据也是有的；
CREATE TABLE myempl2
AS
SELECT employee_id,last_name,salary
FROM employees;
DESC myempl2;
SELECT *FROM myempl2;
#修改表：
#添加一个字段：
ALTER TABLE myempl
ADD salary DOUBLE(10,2);
DESC myempl;
#可以自定义位置：
ALTER TABLE myempl
ADD phone_number VARCHAR(20) FIRST;
ALTER TABLE myempl
ADD mail VARCHAR(45) AFTER emp_name;
#修改一个字段：修改属性长度：
ALTER TABLE myempl
MODIFY  emp_name VARCHAR(25);
#添加默认值：
ALTER TABLE myempl
MODIFY emp_name VARCHAR(25) DEFAULT 'aaa';
#改属性名：
ALTER TABLE myempl
CHANGE salary month_salary DOUBLE(10,2);
#删除一个字段：
ALTER TABLE myempl
DROP COLUMN mail;
#重命名表：
RENAME TABLE myempl
TO myempll;
SHOW TABLES;
#删除表
DROP TABLE myempl2;
#commit 和rollback;
#commit:提交数据，一旦执行commit，则数据永久的保存在了数据库中，意味着数据不可回滚；
#rollback:回滚数据，一旦执行则可以实现数据的回滚，回滚到最近的一次commit之后；
/*DDL和DML的说明：
DDL的操作一旦执行，不可以回滚；因为DDL执行完之后，接着会自动执行一次提交，这个set autocommit=false;不能影响DDL的提交；
DML的操作默认情况下执行后，也不可以回滚，但是如果在执行DML之前，执行了set autocommit=false;则该DML操作可以回滚；
*/
#演示delete from
COMMIT;
SELECT *FROM myempl2;
SET autocommit=FALSE;
DELETE FROM myempl2;#此时再看，表中已经没有了数据；
#此时rollback一下：
ROLLBACK;
SELECT *FROM myempl2;
#但是如果将上面SET autocommit=FALSE;去掉，就无法回滚，数据已经清除了；
/*对于truncate table 表名；这个会将表数据清空，但是不可回滚；
delete from 表名；这个操作也会将表清空，但是设置autocommit为false后，就可以回滚了；*/

#添加数据：
SHOW TABLES;
SELECT *FROM myempll;
ALTER TABLE myempll
CHANGE hire_name hire_data DATE;
ALTER TABLE myempll
CHANGE month_salary salary DOUBLE(10,2);
ALTER TABLE myempll
DROP COLUMN phone_number;
ALTER TABLE myempll
CHANGE emp_name NAME VARCHAR(25);
#方式一：一条一条添加数据：
INSERT INTO myempll VALUES(1,'TOM','2001-12-21',3400);#一定要按照属性顺序添加数据
INSERT INTO myempll(id,hire_data,salary,`name`)VALUES(2,'2002-12-19',4000,'Jerry');
INSERT INTO myempll(id,salary,`name`)VALUES(3,5000,'张三'),(4,6000,'李四');
#方式二：将查询的结果插入表中：
USE atguigudb;
INSERT INTO myempll(id,`name`,hire_data,salary)
SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE department_id IN(60,70);
SELECT*FROM myempll;
#修改数据：update
UPDATE  myempll
SET hire_data='2002-12-07'
WHERE id=3;
#同时更改多个属性：
UPDATE myempll
SET hire_data='2002-1-09',salary=9000
WHERE id=4;
#删除数据：
DELETE FROM myempll
WHERE id=1;
#数据类型：
/*decimal要比double更精确一些，double类型会出现精度损失，但是decimal不会出现精度损失；
日期类型：DATE类型，‘XXXX-XX-XX’年月日类型；
TIME类型，‘HH-MM-SS’时分秒类型，此处小时可以大于24；
DATETIME类型：年月日时分秒；