create table student (
stid int primary key auto_increment,
stname VARCHAR(20),
stbirth DATE
)auto_increment=1000;
/*-------------------插入数据------------------*/
insert into student values(1000,'张三','2015-04-05');/*单值插入*/
insert into student(stname)values ('李四');/*单值插入*/
insert into student values(1002,'李五','2015-04-05'),(1003,'李六','2015-04-05');/*多行插入*/
insert into student set stname='李七',stbirth='2019-08-13';
/*-------------------修改数据------------------*/
update student set stname='学生';/*全部修改数据*/
/*-------------------按条件修改数据---------------*/
update student set stname='张三' where stid>1002;
update student set stname='张四' where stid>1002 and stid<1004;
update student set stname='张五' where stid between 1002 and 1004;
update student set stname='张六' where stid not in(1001,1002);
update student set stname='李四' where stname like'张%';
update student set stname='张三李' where stname like '%四';
update student set stname='张哇哇哈哈' where stname like '%三%';
update student set stname='张五' where stname like '张_';/*两位*/
/*以张开头至少两位*/
update student set stname='bbb' where stname like '张_%';
/*-------------------删除数据------------------*/
create table student (
stid int primary key auto_increment,
stname VARCHAR(20),
stbirth DATE
)auto_increment=1000;
insert into student(stname)values ('李四');/*单值插入*/
drop table student; /*删除表,ddl*/
truncate table student;/*删除表数据,ddl,*/
delete from student;/*删除表的数据,dml,*/
delete from student where stname='李四';
/*-------------------查询数据------------------*/
/*
select * | 列名称,...,列名
from 表名称
where condition
group by 列名 | 聚合的结果
having
order by 列名称|结果集中某个列 asc|desc
limit 行标,行数 -- 分页查询
*/
use world;
select * from country;
select Name from country;
select * from country where Name like 'C____';
--聚合函数 sum(),avg(),count(),max(),min()
/*统计每个州的人数*/
select Continent,sum(Population)from Country
group by Continent
having sum(Population)>30000000
ORDER BY sum(Population) desc;
select count(*) from Country;
SQL 增、删、改、查基本语法
最新推荐文章于 2024-06-22 22:33:28 发布