数据库SQL语句从入门到进阶

1. 创建表

create table people(id int(11),name char(11),phone char

(20),pwd varchar(40));

 2.  增加语句

     insert into people values(9,'gang',13023299931,'qwert');

3.向特定列增加语句

insert into people(id,name,phone ) values(9,'gang',13023299931);

4.删除表全部内容

   delete from people；

5.删除某行数据

delete from people where id =9（字符要加引号）

6.查询数据

select * from people

select name,phone from  people  where id =5

7.更新表中的某个或者某几个数据

update people set phone ='12345',name ='gangjian' where id =5

 8.双表连接查询

  首先新建两张表分别为男女朋友用户信息表

create table userman (id int (10) primary key auto_increment ,age int (10),name varchar (40));

insert into userman (age,name)values (23,'wq'),(24,'er'),(25,'ty'),(26,'sd'),(27,'cv'),(28,'cz'),(29,'sv');

create table userwoman (id int (10) primary key auto_increment, use_id int(10), age int (10),name varchar (40));

insert into userwoman (use_id, age, name)values (2,23,'fff'),(4,25,'hhhhh'),(6,26,'llll');

需求：1.筛选出所有有对象的用户信息

      等值连接

select * from userman,userwoman where userman.id = userwoman.use_id;

select * from userman join userwoman on userman.id = userwoman.use_id;

select * from userman inner join  userwoman on userman.id = userwoman.use_id;

select * from userman  cross join userwoman on userman.id = userwoman.use_id;

筛选出所有人的对象信息

左连接：

select * from userman left join userwoman on userman.id = userwoman.use_id;

右连接

select * from userwoman right join userman  on userman.id = userwoman.use_id;

1. 筛选出没有对象的信息

select * from userman left join userwoman  on userman.id = userwoman.use_id where userwoman.use_id is null;