MySQL三种语句类型

数据定义DDL

Create

Create table if not exists table_name(
‘column_name’ data_type(data_length) not null primary key auto_increment,
Column_name data_type(data_length) default default_value,
…
)engine = innoDB default charset=code_type
Eg:

create table if not exists emp_temp(
 empno int not null auto_increment,
 ename varchar(120) not null,
 hiredate datetime default CURRENT_TIMESTAMP ,
 primary key (empno)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- 拷贝数据表
Create table copy_table_name  as 
Select * from table_name

Alter

alter table table_name
Add new_columnName data_type | 添加新字段
Add primary key(pk_name) |添加主键
Add constraint check_name check(column_condition)|添加检查约束
Modify column_name new_data_tyepe |修改字段类型
Drop column_name |删除字段
Drop primary key(pk_name) |取消主键
Drop constraint check_name |取消检查约束
Change column_name new_column_name data_type|修改字段名称及类型
Rename new_table_name |重命名表名

Drop

alter table_name |删除整表

数据操纵DML

Insert

–插入单行记录

insert into tableName(columnName,...) values('columnValue',....);

尽量不要不指定具体字段

---

–插入多行记录

插入数据来源其他表

insert into tableName (columnName,..) 
select columnName,..
from tableName 
where condition

插入数据非其他表

insert into tableName(colunmnName,..)
select columnName,..
union all
select columnName,..
union all

…

---

update

update tableName,…
Set columnName = newColumnValue,
...
Where updateConditon

• where后只能跟某一个条件
• set后只能跟具体的数值
• 字段的投影只能放在取表时

---

Eg：
我们需要另一个表的条件连接才能正确更新指定字段
这里选择利用子查询拿到符合条件的字段
**不能将子查询写在set的赋值中或者where中
**可以理解 update相当于from 表结构只能从这里提取

update employees as em,(select jobs.JOB_ID from jobs where jobs.JOB_TITLE like '%manager') as em2
set  em.SALARY= em.salary*1.5 
where em.JOB_ID = em2.JOB_ID;

---

delete

delete from tableName where deleteConditions;

---

select

Mysql 执行顺序

• 根据执行顺序 来合理分配重要条件的书写顺序 有效缩减结果集 提升检索效率
• 理解执行顺序 能更好的应用语句服务于不同场景

(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table> 
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition> 
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list> 
(6)  WITH {CUBE | ROLLUP} 
(7)  HAVING <having_condition> 
(10) ORDER BY <order_by_list> |默认asc升序|desc降序
**where 中不能写聚合函数 可以写having

Eg：

• 子查询

select *
from (select * from employees where DEPARTMENT_ID in (10,20)) as em2
GROUP BY em2.department_id
having em2.salary > avg(salary);

• Exists 条件筛选 在表1中寻找符合表二条件的字段投影
• exists本身不输出查询 只返回符合结果 true|false

select * 
from employees as em1
where EXISTS
(select * from jobs where em1.JOB_ID = jobs.JOB_ID and jobs.JOB_TITLE not like '%manager');
Case when
Select salary，
Case  when  salary >3000 then 4000
When then ..
Else ..
End as newSalary
From table 

---

数据控制DCL

grant、revoke

目前涉及不多，后续…