复习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

;

posted @ 2018-06-20 20:25  yangli0504  阅读(128)  评论(0编辑  收藏  举报