DDL、DML、DCL、DQL相关操作

DDL (数据库定义语言)#

数据库的创建以及删除

create database mysql_test;
drop database test;

表的创建 (create)、修改 (alter)、重命名 (rename)、删除 (drop)

创建表
create table s_emp(
    id int primary key,
    last_name varchar(20),
    dept_id int);

create table s_dept(
    id int primary key,
    name varchar(20));

表s_emp是员工表，表s_dept是部门表，但是之间没有设置外键联系
修改表属性，具体看alter用法

alter table s_emp add constraint 'fk' foreign key(dept_id) references s_dept(id);

实际上等同于
create table s_emp(
    id int primary key,
    last_name varchar(20),
    dept_id int,
    constraint 'kf' foreign key(dept_id) references dept_id(id));
外键(表级约束)

DML (数据库操控语言)#

插入数据 (insert)、更新数据 (update)、删除 (delete)

 insert into s_dept values(1,"web"),(2,"bigData"),(3,"php"),(4,"java");
 insert into s_emp values(1,"lisi",1),(2,"wangwu",1),(3,"zhangsan",2),(4,"jake",3);
 insert into s_emp(id,last_name) values(5,"birup");

update s_dept set name="hadoop" where id = 1;
替换
把update换成replace即可

delete from s_emp where id=4;

DCL (数据库控制语言)#

具体看MySQL用户密码及权限修改笔记

DQL (数据库查询语言)#

等值连接

select e.id as id,last_name as name,name as dept_name from s_emp e,s_dept d where e.dept_id = d.id;

左外连接

select s.id,s.last_name,s.dept_id,d.id,d.name from s_emp s left outer join s_dept d on s.dept_id=d.id;

select s.id,s.last_name,s.dept_id,d.id,d.name from s_dept d left outer join s_emp s on s.dept_id=d.id;