mysql基础语法

/*显示所有数据库*/
show databases

/*创建数据库*/
create database db_test

/*使用db_test数据库*/
use db_test

/*创建新表*/
create table student (stuid  int  primary key, stu_name  varchar(20) not null, stu_age  int  not null)

/*向表中插入几条数据*/
insert into student values(1,'aaaa',12)
insert into student values(2,'bbbb',13)
insert into student values(3,'cccc',14)

/*查询表中数据*/
select * from student

/*删除表中的所有数据*/
delete from student

/*复制旧表创建新表，数据结构完全一样*/
create table student2 like student

/*复制旧表中的某几列创建新表*/
create table student3 as select stuid,stu_name from student only

/*删除表*/
drop table student2


/*新增列*/
alter table student3 add stu_age int

select * from student3


/*添加主键*/
alter table student3 add primary key(stu_name)

/*删除主键*/
alter table student3 drop primary key

/*创建某列索引*/
CREATE index idx_stu on student3(stu_name)

/*删除索引*/
alter TABLE student3 drop index idx_stu

/*创建视图*/
create view view_stu as select stuid,stu_name from student

create view view_stu as select * from student

/*查看视图*/
select * from view_stu

/*删除视图*/
drop view view_stu


/*union，通过组合其他两个结果表并消去表中任何重复行而派生出一个结果表*/
/*union all，不消除重复行*/
select * from student union select * from student3

/*left outer join，左外连接，结果集返回两个表的匹配行，也包括左连接表的所有行*/
select * from student left outer join student3 on student.stuid=student3.stuid

/*right outer join，右外连接，结果集返回两个表的匹配行，也包括右连接表的所有行*/
select * from student right outer join student3 on student.stuid=student3.stuid


/*full outer join，在mysql中，不支持full join，
解决思路为：
1、先查出左连接  
2、查出右连接
3、用union 把二者结合查询*/
select * from student a  left outer join student3  b on a.stuid = b.stuid
union
select * from student a  right outer join student3 b on a.stuid= b.stuid

/*拷贝表（只拷贝数据）*/
insert into student3 select * from student