数据库的增删改查

DDL

DDL操作表

DML

添加数据

修改数据

DQL查询

基础查询

drop table if exists stu;
select *from stu
create table stu(
id int,
name varchar(5),
age int,
sex varchar(5),
address varchar(10),
math double(5,2),
english double(5,2),
hire_date date
);
alter table stu add address varchar(10);
Insert INTO stu(id,name,age,sex,address,math,english,hire_date)
VALUES
(1,'马云',55,'男','杭州',66,78,'1995-09-01'),
(2,'马化腾',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'2001-01-11'),
(4,'陆佰',20,'女','湖南',76,65,'1997-09-05'),
(5,'刘青',20,'男','湖北',86,NULL,'1998-09-01'),
(6,'刘德华',45,'男','香港',33,56,'1998-09-11'),
(7,'张学友',22,'女','香港',99,99,'1997-02-11'),
(8,'德玛西亚',18,'男','南京',67,76,'1994-09-02');
select name,age from stu;
select distinct address from stu;
select name ,math as 数学成绩,english as 英语成绩 from stu;

条件查询

模糊查询

drop table if exists stu;
select *from stu
create table stu(
id int,
name varchar(5),
age int,
sex varchar(5),
address varchar(10),
math double(5,2),
english double(5,2),
hire_date date
);
alter table stu add address varchar(10);
Insert INTO stu(id,name,age,sex,address,math,english,hire_date)
VALUES
(1,'马云',55,'男','杭州',66,78,'1995-09-01'),
(2,'马化腾',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'2001-01-11'),
(4,'陆佰',20,'女','湖南',76,65,'1997-09-05'),
(5,'刘青',20,'男','湖北',86,NULL,'1998-09-01'),
(6,'刘德华',45,'男','香港',33,56,'1998-09-11'),
(7,'张学友',22,'女','香港',99,99,'1997-02-11'),
(8,'德玛西亚',18,'男','南京',67,76,'1994-09-02');
select name,age from stu;
select distinct address from stu;
select name ,math as 数学成绩,english as 英语成绩 from stu;
select *from stu where age>=20;
select *from stu where age>20;
select* from stu where age>20&&age<30;
select *from stu where hire_date BETWEEN '1998-01-01' and '2022-02-01';
select*from stu where age in(18,19,20);
select*from stu where english is NULL;
select*from stu where english is not null;
select*from stu where name like '马%';
select*from stu where name like'_斯%';
select*from stu where name like'%刘%';

排序查询

分组查询

-- 查询学生信息按升序排列
select *from stu order by age asc;
-- 查询学生信息按数学成绩降序排列.
select*from stu order by math desc,english desc;
select count(id) from stu;
select count(english)from stu;
select min(math) from stu;
select sum(math) from stu;
select avg(math) from stu;
select min(english) from stu;

-- 查询学生信息按升序排列
select sex,avg(math)from stu GROUP BY sex;
select sex,avg(math),count(*) from stu group by sex;
select sex,avg(math),count(*) from stu where math>70 group by sex;
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>1;

分页查询

约束

约束的概念和分类

drop table if exists emp;
create table emp
(
    id int primary key auto_increment,
    ename varchar(50)charset utf8 null null unique,
    joindate date not null,
    salary double(7,2)not null,
    bonus double(7,2)default 0
);
-- 主键非空且唯一
insert into emp(id, ename, joindate, salary,bonus) VALUES
(1,'张三','1999-11-11',8000,5000);
insert into emp(id, ename, joindate, salary,bonus) VALUES
    (null,'张三','1999-11-11',8000,5000);
insert into emp(id, ename, joindate, salary,bonus) VALUES
    (1,'张三','1999-11-11',8000,5000);
insert into emp(id, ename, joindate, salary,bonus) VALUES
    (2,'李四','1999-11-11',8000,5000);
insert into emp(id, ename, joindate, salary) VALUES
    (3,'王五','1999-11-11',8000);
-- 演示自动增长
insert into emp(ename, joindate, salary,bonus) VALUES
    ('赵六','1999-11-11',8000,5000);
insert into emp(id, ename, joindate, salary,bonus) VALUES
    (null,'赵六2','1999-11-11',8000,5000);
alter table emp modify id int not null;
insert into emp(id, ename, joindate, salary,bonus) VALUES
    (null,'赵六2','1999-11-11',8000,5000);
alter table emp modify  id int ;

select *from emp;

外键约束

数据库设计

多对多

数据库多表查询

内连接

select *from dept,ewp where dept.id=ewp.dep_id;
select*from ewp join dept on ewp.dep_id=dept.id;

外连接

select*from ewp left join dept on ewp.dep_id=dept.id;
select*from ewp right join dept on ewp.dep_id=dept.id;

子查询

select *from stu where math>(select math from stu where name='马云');

select *from (select *from ewp where age>18) t1,dept where t1.dep_id=dept.id;

事物