SQL基础语法汇总

1. select 查询

   select * from Student;		//查询Student所有信息
   

2. distinct 去重

   select distinct 列名 from 表名 //查询表中唯一的列名(去除重复)
   

3. where 条件选择

   where里还能添加between、=、!=、>、<、like、and、or等
   select * from Student where age > 20;	//查询Student表中年龄大于20岁的学生的所有信息
   

4. and和or 把两个或者多个条件连接起来

   select * from Student where name='张三' and age=20;	
   //查询Student表中姓名为张三并且年龄为20岁的学生的所有信息
   select * from Student where name='张三' or age=20;	
   //查询Student表中姓名为张三或者年龄为20岁的学生的所有信息
   

5. order by 用来排序的，可以根据字母排序，也可根据数字排序，默认为升序，降序需加上DESC

   select * from Student order by age;	  //Student表中按年龄升序排序
   

6. group by 用来分组

   select * from Student where age > 20 group by gender;	
   //根据性别进行分组，并查出性别大于20岁的学生信息
   

7. having 对分组结果指定条件

   select * from Student where age > 20 group by gender having count(*)>1;	
   //根据性别进行分组，并查出性别大于20岁的学生信息
   

8. insert into 插入语句

   //往Student表中插入一条数据
   insert into Student(name, age, gender) values('张三',21, 1); 
   insert into Student(name, age, gender) values('李四',18, 0); 
   insert into Student(name, age, gender) values('王五',23, 1); 
   insert into Student(name, age, gender) values('赵六',20, 0); 
   insert into Student(name, age, gender) values('韩七',24, 1); 
   insert into Student(name, age, gender) values('秦八',19, 0); 
   

9. update 更新数据

   update Student set age=22 where name='张三';//更新Student表中名字为张三的学生年龄为22岁
   

10. delete 删除表中的行

    delete from Student where name='张三';	//删除Student表中名字叫张三的学生
    

11. create database database_name 创建数据库

12. drop database database_name 删除数据库

13. create table table_name 创建表

    drop table `Student`;
    create table `Student` (
    	id int(11) NOT NULL auto_increment,
        name varchar(255) NOT NULL,
        age int(11) NOT NULL,
        gender int(1) NOT NULL,
        primary key(id)
    );
    

14. truncate table table_name 清空表中的数据

15. Alter table table_name add 字段 字段类型; Alter是用来改变表或者数据库的关键字