mysql 学习笔记 day05

/*


insert into join_teacher values(4, '孙武', 'male');
insert into join_class values(4,'php0505','202');
链接查询
先将所有数据链接起来再进行查询；
内连接，外连接，自然链接


*/




--------------------------------------内连接------------------------------------
-- 数据内部的内连接  链接的多个数据必须存在
-- mysql 中默认是 inner join 可以写成 join
-- on 跟链接条件 也可以用 where
  table_left inner join table_right on ;


-- 比较标准的语法 on 链接条件， where 过滤条件
select t.t_name, tc.begin_date, tc.days from join_teacher t inner join join_teacher_class tc on t.id = tc.t_id where days > 15;


select t.t_name, tc.begin_date, tc.days from join_teacher t cross join join_teacher_class tc;


select id , days, t_name from join_teacher inner join join_teacher_class using (id);


--------------------------------------外连接------------------------------------


-- 如果负责链接的一个或多个数据不存在 外连接 可以有链接不到的数据
--  外连接不能用where作为链接条件，但可以使用on 和 using
select t.t_name, tc.begin_date, tc.days from join_teacher t left outer join join_teacher_class tc on t.id = tc.t_id;


-- 当字段重名时写表名
-- 左外连接
-- 在连接时，如果出现左边表，数据链接不到右边表的情况，则左边表数据保留，
-- 如果出现右边表的数据左边表没有，右边表的数据丢弃
-- outer join  outer 可以省略
select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, tc.days from 
join_teacher t left join join_teacher_class tc on t.id = tc.t_id;




-- 右外连接
select t.id as t_id, tc.id as tc_id, t.t_name, tc.begin_date, tc.days from 
join_teacher t right join join_teacher_class tc on t.id = tc.t_id;




--------------------------------------自然链接-----------------------------------------------


/*
通过mysql自己的判断选用连接类型
内：natural join
外：left natural join
right natural join


连接时支持多表同时连接
*/


/*
outfile 操作
dumpfile
*/
--  设置数据格式
-- fields terminated
-- lines terminated
select * from teacher;


select * into outfile 'e:/one.txt' from teacher;
select * into outfile 'e:/one.txt' fields terminated by ',' from teacher 


select * from dumpfile 'e:oe' from teacher;


-----------------------------------插入数据------------------------------------------------------




insert into table_name(column_name) values(value);


insert into table_name set column_name1=value2, column_name2=value2;


insert into table_name(column_name, column_name2) values
(value1,value2),
(value1,value2);


-- 插入数据时主键冲突
--默认报错，表示不会插入
-- 可以insert插入 on duplicate key update 不加set;
insert into table_name(id, t_name)values(1,'ji')
on duplicate key update t_name='ji';


-- 用select结果作为插入数据
insert into table_name1 (column_name1, column_name2) select column_name1,column_name2 from table_name2;




-- replace 主键不冲突直接插入，否则替换
replace into teacher values(1,'han','dd', 90);
    alter table table_name drop primary key;-- 如果主键自动增长，执行不成功


-- load data infile; 在导出时将主键删除掉，导入时再新建主键




-----------------------------------删除数据------------------------------------------------------


--delete 允许使用limit 限制删除的条数 limit 配合order by 使用
delete from table_name order by days limit 10;


delete from one, two using one join two on one.public_field = two.public_field;


-- 清空表
truncate 
-- 类似 
delete from table
-----------------------------------更新数据------------------------------------------------------
update one join two on one_public_field  = two_public_field set one_data=value where id = value;


------------------------------------视图操作--------------------------------------------------------
create table info_teacher(
id int primary key auto_increment,
t_name varchar(20),
salary decimal(10,2)
);


insert into info_teacher values
(null, '韩信',180.90),
(null, '李白', 230.65),
(null, '韩飞', 159.03);


create view v_teacher as select id, t_name from info_teacher;
insert into v_teacher values(null, '江湖');


+----+--------+
| id | t_name |
+----+--------+
|  1 | 韩信   |
|  2 | 李白   |
|  3 | 韩飞   |
|  4 | 江湖   |
+----+--------+
4 rows in set (0.00 sec)


mysql> select * from info_t
+----+--------+--------+
| id | t_name | salary |
+----+--------+--------+
|  1 | 韩信   | 180.90 |
|  2 | 李白   | 230.65 |
|  3 | 韩飞   | 159.03 |
|  4 | 江湖   |   NULL |
+----+--------+--------+
4 rows in set (0.00 sec)
/*
视图是动态的，只保存了一条sql语句；2014/3/11
drop view v_teacher;
*/


drop view if exists v_teacher;


alter view v_name as select * from info_teacher;