数据库的CRUD

简单的数据库操作

创建表的基本语句

-- 创建学生表
create table student(
 id int,
 name varchar(30),
 sex char,
 age int,
 address varchar(255)
);

-- 创建课程表
create table course (
 id int,
 cname varchar(100)
);
-- 创建成绩表
create table scores(
 id int,
 sid int,
 cid int,
 grade int
);

对数据的添加、修改、删除操作

-- 插入语句
insert into student values(112,'孙悟空','男',500,"花果山");
insert into student values(111,"猪八戒",'男',200,"高老庄");
insert into student values(113,"沙悟净",'男',100,"泥沙河");
insert into student values(114,"唐僧",'男',30,"大唐");

-- 修改id为114 中学生的姓名和年龄以及地址
update student set name = '牛魔王' ,age = 400,address = '火焰山' where id = 114;
-- 删除id为114 的学生信息
delete from student where id = 114;

给表添加主键，以及主外键约束

-- 给student表添加主键并且设置为自增
alter table student modify column id int primary key auto_increment;
-- 给course 表添加主键并且设置为自增
alter table course modify column id int primary key auto_increment;
-- 给student表添加主键并且设置为自增
alter table scores modify column id int primary key auto_increment;

给表添加映射关键，即添加主外键

-- 给scores添加外键，即学生表id---->成绩表sid
alter table scores add constraint foreign key scores(sid) references student(id);
-- 给scores添加外键，即课程表id---->成绩表cid
alter table scores add constraint foreign key scores(cid) references course(id);

设置检查约束条件

-- 给性别添加约束，只能为男或女
alter table student add constraint check (sex = '男' or sex = '女');
-- 给成绩添加约束，成绩必须大于0小于100
alter table scores add constraint check (grade >=0 and grade <= 100);
alter table scores add constraint check (grade between 0 and 100);

排序以及模糊查询

--  升序，默认为升序
select name,age from student order by age asc;
-- 降序
select name,age from student order by age desc;
-- like查询 _ 只匹配一个字符  % 匹配0个至多个字符
select name from student where name like '%八%' ;
-- like 查询
select name,address from student where address like '花_山' ;

分组以及聚合函数

-- 查询年龄大最大值，最小值，平均值等
select count(1),max(age),min(age),avg(age),sum(age) from student group by sex;
-- 聚合函数的使用
select address,count(1) ca from student group by address having ca >= 2;

分页查询

-- 分页查询，每页显示2条数据
select * from student;
-- 第一页
select * from student limit 0,2;
-- 第二页
select * from student limit 2,2;
-- 第三页
select * from student limit 4,2;

复制一个表的结构

-- 拷贝一个student_copy表
create table student_copy select * from student where 1 <> 1;