-----创建数据库 create DATABASE 20201910b —使用数据库 use 20201910b -----创建表 create table student( id int PRIMARY key auto_increment, sname VARCHAR(20), ssex VARCHAR(2), sage int, sscore FLOAT(4,2) ) ------修改编码 alter database 20201910b CHARSET=utf8 --------新增数据 ----单条添加 INSERT into student(id,sname,ssex,sage,sscore) VALUES(1,“于磊”,“男”,24,80.5) -----多条添加 INSERT into student VALUES(2,“于磊1”,“男”,25,90.5),(3,“于磊2”,“女”,22,80),(4,“于磊3”,“女”,19,88) ------修改数据 update 表名 set 字段=修改的数据(name =zhangsan) where 字段=值（id=1） UPDATE student set sname=‘于三十’ where id =2 ------查询（全查，条件查询，模糊查询(_)，排序，分组） select * from +表名 select * from student -------条件查询 select * from student where id=1 or id =2 select * from student where id in(1,2,3,4) select * from student where id BETWEEN 1 and 4 ------模糊查询 SELECT * from student where sname like ‘磊%’ SELECT * from student where sname like ‘%磊’ SELECT * from student where sname like ‘%磊%’ ------升序 select * from student ORDER BY sscore ----降序 select * from student ORDER BY sscore desc ------分组查询 HAVING + GROUP BY select count(*) from student GROUP BY ssex HAVING sum(sscore)>=85
-----删除------ ------物理删除+逻辑删除------- DELETE from +表名 +where 字段=值 delete from student where id=2
------聚合函数 sum ,avg,max ,min count(), select count(sscore) from student select avg(sscore) from student select max(sscore) from student select min(sscore) from student select sum(sscore) from student
多表联查 内连接 inner join 外连接 left join ,right join 全连接 full join (不适用mysql,使用oracle) = left join+ union+right join
取别名 as 或者是不写 --------sql语句 select s.字段,c.字段，，， from student(表1) as s inner join（left join ,right join） class(表2) c on s.字段 = c.字段 ------内连接 select s.sname,s.sex,c.classroom from class c INNER JOIN student s on s.sid = c.sid SELECT sc.school,c.classroom from school as sc INNER JOIN class as c on sc.cid = c.cid ---------外连接 select s.sname,s.sex,c.classroom from class c left JOIN student s on s.sid = c.sid (左边为主表，右边有则填充，没有用null) select s.sname,s.sex,c.classroom from class c RIGHT JOIN student s on s.sid = c.sid (右边为主表，左边有则填充，没有用null) ----------全连接（不适用mysql） select s.sname,s.sex,c.classroom from class c full JOIN student s on s.sid = c.sid -------全连接 select s.sname,s.sex,c.classroom from class c left JOIN student s on s.sid = c.sid UNION select s.sname,s.sex,c.classroom from class c RIGHT JOIN student s on s.sid = c.sid
------多表联查 select c.classroom,st.sname,st.sex ,sc.school from class as c INNER join student as st on c.sid = st.sid INNER JOIN school as sc on c.cid = sc.cid
-----子查询(一个表的查询的结果作为另一个表的条件) SELECT cid from school where school =‘北京’ SELECT classroom from class where cid=(SELECT cid from school where school =‘北京’)
