MySQL笔记

-----创建数据库
create DATABASE  one
---使用数据库
use one
-----创建表
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