SQL常用语句（大全）

我们先看一下表的结构：

 

-- 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 student2
     WHERE 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);