注意: 数据库去重 DISTINCT
例如:
SELECT DISTINCT s.sname FROM sc INNER JOIN s ON sc.sno=s.sno INNER JOIN c ON sc.cno=c.cno NOT IN(1)
1.创建数据库
create database 数据库名 CHARACTER set utf8; 以防乱码
2.选择数据库
use 数据库名;
3.新增数据
insert into 表名 VALUES 数据
insert into 表名(
字段1 数据类型1,
字段2 数据类型2,
字段3 数据类型3)
insert into 表名 VALUES(字段1 数据类型1),(字段2 数据类型2);
4.修改数据
update 表名 set 字段=值 where 约束条件
5.删除语句
delete from 表名 where id=1;(约束条件) 单条删除
delete from 表名; 删除所有数据
6.子查询
select * from 表名1 where id=(select sid from 表名2 where name=‘钉钉’) 类似
7.多表联查 (内连接/外连接)
select 表1.字段,表2.字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段 (两表)
select 表1.字段,表2.字段,表3.字段 FROM 表1 inner join 表2 on 表1.字段=表2.字段 inner join 表3 on 表1.字段=表3.字段 where 条件 (三表)
inner JOIN /left join /right join三者区别
right join 以右边表为主,左边没有,用null代替
select student.sname,teacher.tcourse from student right join teacher on teacher.tid=student.sid
left join 以左边表为主,右边没有,用null代替
select student.sname,teacher.tcourse from student left join teacher on teacher.tid=student.sid
inner join 显示共同有的
select student.sname,teacher.tcourse from student inner join teacher on teacher.tid=student.sid
full join 支持oracle 不支持mysql union (连接左右)
select student.sname,teacher.tcourse from student full join teacher on teacher.tid=student.sid
select student.sname,teacher.tcourse from student right join teacher on teacher.tid=student.sid union select student.sname,teacher.tcourse from student left join teacher on teacher.tid=student.sid