我们先看一下表的结构：

 

-- sql语句创建表

CREATE TABLE student2 (id int(11) NOT NULL AUTO_INCREMENT,stuname varchar(10) NOT NULL,class varchar(20) NOT NULL,sex int(11) NOT NULL,major varchar(20) NOT NULL,mathstore int(11) DEFAULT NULL,yuwenstore int(11) DEFAULT NULL,engstore int(11) DEFAULT NULL,PRIMARY KEY (id)
)

-- sql语句删除表

delete table student2;

-- 添加表字段

Alter table student2 add column historystore int;

-- 删除表字段

alter table student2 drop column historystore;

-- 修改表字段类型、长度

alter table student2 modify column sex char;

-- 查询语句

 select * from student2 where id=5;

-- 删除语句

delete from student2 where id = 5;

-- 修改语句

update  student2 set intime=1249524739 where sex=1;

-- 添加语句

insert into student2(stuname,class,sex,major,mathstore,yuwenstore,engstore) 
values('张四8','三班',1,'计算机',86,99,99);

-- 日期格式化函数

Select from_unixtime(intime) from student2 where sex=1;

-- 字符串函数（分割，类型转换）

select LEFT(stuname,1) as leftsub ,RIGHT(stuname,2) as rightsub,SUBSTRING(stuname,2,3) as sub from student2;
select   * from student2 where CONVERT(INT,hisstore)=41;

-- 求和函数

select sum(yuwenstore) as 二班语文成绩 from student2 where class='二班';

-- if语句

SELECT SUM( IF(mathstore > engstore ,1 ,0) ) AS mannum,SUM( IF(mathstore > engstore AND sex > 1, 1 ,0) ) AS womannum FROM student2;

-- select 嵌套查询

SELECT stuname  
FROM Student2`student2`
WHERE id IN(SELECT id  FROM student2WHERE class='一班');

 -- 内连接

 SELECT   student2.*,student.*   FROM  student2 INNER   JOIN   student     ON   student.stuname=student2.stuname;   

  -- 左连接 

SELECT   student2.*,student.*   FROM  student2 LEFT   JOIN   student     ON   student.stuname=student2.stuname;

     -- 右连接

SELECT   student2.*,student.*   FROM  student2 RIGHT   JOIN   student     ON   student.stuname=student2.stuname;     

 -- 全连接

SELECT   student.*,student.*   FROM  student   FULL JOIN   student     ON   student.stuname=student.stuname;      

 -- 创建索引
 

CREATE INDEX SClass ON student2 (class);