MySQL基本语句

1、查找数据：select column_name,column_name  from t_name[where clause]

eg：

select car_id, car_name from t_carinfo   //查找全部信息

select car_id, car_name from t_carinfo where car_id=2//查找car_id=2的信息

select car_id, car_name from t_carinfo where car_name='保时捷3'//查找car_name="保时捷3"的信息

另：防止重复：select distinct(column_name) from t_name

eg:

    select distinct(name) from t_personinfo  //查找出不同名字

 查找前              查找后    

2、修改数据：update t_name set field1=new-value1,field2=new-value2[where clause]

eg:

update t_carinfo set car_id =8 where car_id=1//将car_id=1的地方改为car_id=8

update t_carinfo set car_name='宝马X4'//将所有car_name改为“宝马X4”

update t_carinfo set car_name='宝马X4' where car_id=2//将car_id=2位置处的car_name改为'宝马X4'

3、删除数据：delete from t_name [where clause]

eg:

delete from t_carinfo where car_id=8//将car_id=8的数据删除

delete from t_carinfo where car_name="宝马" and car_id=8

4、增加数据：insert into t_name (field1,field2,...fidlen)values(value1,value2...valuen)

eg:

insert into t_carinfo(car_id, car_name) values(6, "保时捷")
insert into t_carinfo(car_name) values("保时捷2")

4-2、批量增加数据：insert into t_name (field1,field2,...fidlen)values(value1,value2...valuen)，(valuen+1,valuen+2...value2n)

eg:insert into t_carinfo(car_id, car_name) values(6, "保时捷"),(8, "宝马")

5、排序：
select column_name,column_name  from t_name order by column_name(默认升序排列)
select column_name,column_name  from t_name order by column_name asc(升序排列)

select column_name,column_name  from t_name order by column_name desc(降序排列)

eg: select id ,name,salary from t_personinfo order by salary
      select id ,name,salary from t_personinfo order by salary asc
      select id ,name,salary from t_personinfo order by salary desc

6、（1）查找前几条信息

select  column_name,column_name from t_name limit number=select column_name,column_name from t_name limit 0,number

eg:

    select id ,name,salary from t_personinfo limit 2=select id ,name,salary from t_personinfo limit 0,2//查找前两条信息

     （2）查找从索引为number1的位置开始的number2条信息(number1:开始位置，number2:检索行数)

  select  column_name,column_name from t_name limit number1，number2

=select  column_name,column_name from t_name where id>number1 limit number2

eg:

   select id ,name,salary from t_personinfo limit 1,3 
 =  select id ,name,salary from t_personinfo where id>1 limit 3 //查找从索引为1开始的3条信息

     (3) 将查到的（2）中的信息按升序排列

 select  column_name,column_name from t_name where id>number1 order by column_name asc limit number2

=select  column_name,column_name from t_name order by column_name asc limit number1，number2

eg:
   select id ,name,salary from t_personinfo where id>1 order by salary asc limit 3

   select id ,name,salary from t_personinfo order by salary asc limit 1,3

    (4)通过where语句实现具体要求

eg:

   select id ,name,salary from t_personinfo where salary>=500 and salary<=800
   select id ,name,salary from t_personinfo where salary between 500 and 800

7、统计表中信息条数：select count(1) from t_name

 

注：在同时使用where语句和order by语句时，order by 位于where后面