20 Jun 18 复习mysql
#1. 操作文件夹
增：create database db1 charset utf8;
查：show databases;
改：alter database db1 charset latin1;
删: drop database db1;
#2. 操作文件
先切换到文件夹下：use db1
增：create table t1(id int,name char);
复制表结构+记录（除了key）： create table new_t1 select * from t1;
只复制表结构： create table new_t1 select * from t1 where 1=2;
Create table new_t1 like t1;
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
查：show tables
改：alter table t1 modify name char(3);
alter table t1 change name name1 char(2);
alter table t1 rename t2;
alter table t1 add id int, name char(16) not null;
alter table t1 add age int(3) not null default 22 first;
alter table t1 add sex enum(‘male’, ‘female’) default ‘male’after id;
alter table t1 drop name;
alter table t1 drop primary key;
alter table service engine = innodb;
删：drop table t1;
#3. 操作文件中的内容/记录
增：insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
Insert into t1(name) values(‘egon1’);
查：select * from t1;
改：update t1 set name='sb', ** = **, where id=2 and **=**;
删：delete from t1 where id=1;
清空表：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，
#4. 联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port)); #联合唯一
#5. 多列主键
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);
#6. Foreign key
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#7. 单表查询
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
关键字的执行优先级
From
On (join condition )
Join
where
group by
having
select
distinct
order by
limit
#8 多表查询
全外连接：
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
