首页 > 代码库 > SQL数据库操作(CURD)

SQL数据库操作(CURD)

 对数据仓库的操作(CURD)

 新增

create database db_test;

技术分享

新增的时候设置编码:

create database da_test_1 character set utf-8;

 技术分享

删除:

drop database da_test_1;

 技术分享

修改:

修改数据仓库的字符编码集:

 alter database db_test character set ‘gbk‘;

技术分享

查看:

获取所有的数据仓库:

 show databases;

技术分享

查看数据仓库的编码:

show create database db_test;

技术分享

选择数据仓库:

use db_test;

技术分享

查看当前选择的是哪个数据仓库:

select database();

技术分享

对数据表的操作(CURD):

新增:

create table emp(

id int primary key auto_increment,

name varchar(50) not null,

workid varchar(32) unique not null,

salary double

);

技术分享

查看:

查看表结构:

desc emp;

技术分享

查看该数据仓库中的所有表:

show tables;

技术分享

查看表的字符编码:

 show create table person;

技术分享

修改:

增加一列字段:

alter table emp add telephone varchar(20);

技术分享

修改现有列的 数据类型和约束:

alter table emp modify telephone varchar(30) not null;

技术分享

修改现有列的名称:

 alter table emp change telephone phone varchar(11);

技术分享

删除存在的列:

 alter table emp drop phone;

技术分享

修改表的名字:

rename table emp to person; 

技术分享

修改表的字符编码:

alter table person character set utf8;

技术分享

删除:

drop table person;

技术分享

对表中记录的操作(CUD):

新增:

insert into (id,name,workid) values (null,‘xiaoshitou‘,‘P001‘);

技术分享

插入数据时的注意事项:

1、值与列一一对应。并且有多少个列,需要写多个值。如果某一个列没有值。可以使用null。表示插入空。

2、值的数据类型,与列被定义的数据类型要相匹配。并且值得长度。不能够超多定义的列的长度。

3、字符串类型:插入字符类型的数据,必须英文的单引号括起来。在mysql中。

4date 时间类型:也必须用英文的单引号括起来,如 ‘yyyyMMdd,yyyy-MM-dd,yyyy/MM/dd’ 。

5、在插入数据的时候,如果某些列可以为null,或者是自动增长的列,或者有默认值的,在插入的时候可以省略。

6、如果给表中的所有列插入数据,这时可以省略表名后面的列名,直接写values

 

修改:

 

update emp set salary = 15000 where id = 1;

技术分享

删除:

 delete from emp where id=1;

技术分享

删除表中所有的记录:

truncate table emp;

技术分享

delete from emp; truncate table emp;的区别?

前者是一条一条记录删除,

后者是先删除表,然后重新建一张表

对表中记录的查询操作(R):

准备工作(创建表和添加数据):

 

create database day02;use day02;create table student(    id int primary key auto_increment,    name varchar(32) not null,    age int ,    gender varchar(10) not null,    score double not null,    birthday date);insert into student (id,name,age,gender,score,birthday) values(null,zhangsan,23,male,98.99,1990-09-09);insert into student (id,name,age,gender,score,birthday) values(null,lisi,23,,56.99,1990-02-09);insert into student (id,name,age,gender,score,birthday) values(null,王五,24,,75.99,1988-01-01);insert into student (id,name,age,gender,score,birthday) values(null,赵六,25,,80.99,1980-11-12);insert into student (id,name,age,gender,score,birthday) values(null,王思聪,null,,84,null);

数据表中记录查询:

查询表中所有的记录:

 select * from student;

技术分享

查询表中指定列的数据:

 select name,age,score from student;

 技术分享

按条件来查询:

查询分数大于80的所有学生信息: select * from student where score >80;

技术分享

过滤重复数据:

获取学生表中所有的年龄,不能重复

select distinct age from student;

技术分享

对查询的结果进行排序:

用学生的age进行降序排序

select * from student order by age desc;

技术分享

先按照age进行降序排列,年龄相同时,利用分数升序排列:

select * from student order by age desc,score asc;

技术分享

给列起别名:

可以用as,也可以不用

 select id as ‘GongHao‘,name ‘XingMing‘ from student;

技术分享

聚合函数:

count函数:

统计个数,统计一共有多少学生:

技术分享

count的注意点:count在根据指定的列统计的时候,如果这一列中有null 不会被统计在其中。

sum函数:

求和方法:统计该班级所有学生的分数:

select sum(score) from student;

技术分享

sum的注意点:

1、如果使用sum 多列进行求和的时候,如果某一列中的有null,这一列所在的行中的其他数据不会被加到总和。

2、可以使用mysql 数据库提供的函数 ifnull(列名,)

3、在数据库中定义double类型数据,是一个近似值,需要确定准确的位数,这时可以把这一列设计成numeric类型。numeric(数据的总列数,小数位数)

 

avg 函数:

求平均值,求班级的平均年龄

select avg(age) from student;

技术分享

max, min函数:

求最大值和最小值:求班级的最高分,最低分

select max(score) as ‘最高分‘,min(score) ‘最低分‘ from student;

技术分享

group by 分组函数:

准备工作:

create table student(    name varchar(30),    sex char(10),    course varchar(30),    score int);insert into student values(张三, , 语文, 93);insert into student values(张三, ,数学, 96);insert into student values(张三, , 英语, 99);insert into student values(李四, , 语文, 90);insert into student values(李四, ,数学, 85);insert into student values(王五, , 语文, 80);insert into student values(王五, ,数学, 75);insert into student values(王五, , 英语, 70);

表中的数据为:

 技术分享

求:每个学生的总成绩?

分析:要求每个人的总成绩,首先需要按学生姓名来分组,然后对分组的成绩求和,就可以完成。

 技术分享

技术分享

求平均分大于80的学生?

select name,avg(score) from student group by name having avg(score) > 80;

技术分享

select语句执行的顺序:

1、from :确定数据源

2、Where : 指定条件对记录进行筛选

3、Group by : 对记录进行分组

4、聚合函数:如sumcountavg...

5、Having :对分组的数据,进行筛选

6、计算所有的表达式

7、使用order by 对结果进行排序

8、Select对数据的显示

数据库的备份和恢复:

备份:

mysqldump -u root -p mytestdb > e:t/est.sql

技术分享

恢复:

注意:恢复的时候,首先应该自己创建一个数据库

create database dbtest;

执行:mysql -u root -p db_test < e:/test.sql

技术分享

数据库的多表设计:

多对多的关系:

一个A可以对应多个B,一个B可以对应多个A;比如说两张表:学生和课程表,一个学生可以选择多门课程;一门课程也可以被不同的学生选择。

设计方式:

创建一个中间表C,增加两列,引入AB表中主键,作为外键。

外键的增加方式:

1、已经创建好的表中,添加外键:

alter table C add foreign key(A_id) references A(id);

alter table C add foreign key(B_id) references B(id);

2、在定义表的时候增加外键

create table C(

A_id int,

B_id int,

foreign key(A_id) references A(id),

foreign key(B_id) references B(id)

);

一对多的关系:

一个A对应多个B,而多个B只能对应一个A

设计方式:

在多的一张表中增加一列,引入一表中主键作为外键。

一对一的关系:

一个A只能对应一个B,一个B也只能对应一个A

设计方式:

在任意一张表增加一列,引入另一张表的主键作为自己的外键。

表设计案例:

需求:设计学生成绩管理系统数据表

1、每个教师可以教多门课程

2、每个课程由一个老师负责

3、每门课程可以由多个学生选修

4、每个学生可以选修多门课程

5、学生选修课程要有成绩

分析:

教师和课程是一对多的关系,得在课程表中增加一列,引入教师表的主键,作为自己的外键;

课程和学生是多对多的关系,得创建一张中间表,增加两列,引入课程和学生的主键,作为自己的外键;

学生选修课需要成绩,所以得在学生和课程的中间表增加一列,存放成绩

Sql语句:

创建教师表:

create table teacher(

id int primary key auto_increment,

name varchar(50)

);

创建课程表:

create table course(

id int primary key auto_increment,

name varchar(50),

teacher_id int,

foreign key(teacher_id) references teacher(id)

);

创建学生表:

create table student(

id int primary key auto_increment,

name varchar(50)

);

创建学生课程中间表:

create table studentcourse(

student_id int,

course_id int,

score double,

foreign key(student_id) references student(id),

foreign key(course_id) references course(id)

);

多表查询:

准备工作:

create table A(  A_ID int primary key auto_increment,  A_NAME varchar(20) not null);insert into A values(1,苹果);insert into A values(2,橘子);insert into A values(3,香蕉);create table B(    A_ID int primary key auto_increment,   B_PRICE double);insert into B values(1,2.30);insert into B values(2,3.50);insert into B values(4,null);

内链接:

查询两张表中公共的部分:

查询出每种水果的价格:

方式一:

select a.A_ID,a.A_NAME,B_PRICE from a,b where a.A_ID = b.A_ID;

技术分享

方式二:

select a.A_ID,a.A_NAME,B_PRICE from a inner join b on a.A_ID = b.A_ID;

技术分享

 

左外连接:

以左边的表为基础去右边的表查询,如果有关联的记录,就显示出来,如果没有对应的记录就显示null

select * from a left join b on a.A_ID = B.A_ID;

技术分享

全连接:

就是左连接和右连接之后,去掉重复数据,就用union,不需要去重就用union all

select a.*,b.* from a left join b on a.A_ID = B.A_ID

UNION

select a.*,b.* from b left join a on a.A_ID = B.A_ID;

技术分享

不去重:

select a.*,b.* from a left join b on a.A_ID = B.A_ID

UNION ALL

select a.*,b.* from b left join a on a.A_ID = B.A_ID;

技术分享

事务:

事务就是:在处理一件完整的事情,要么全部成功,要么全部失败;在数据库中,执行一组sql语句,要么全部成功,要么全部失败

 

开启事务:start transaction;

提交事务:commit;

回滚事务:rollback;

事务的特性(ACID:

 

A:原子性,一件事务不可分割,要么发生,要么全不发生

C:一致性, 事务执行前后一致性,成功则提交,失败回滚

I:隔离性,一个用户的事务,不能被其他用户的事务影响

D:持久性,事务一旦提交,对数据库的改变就是永久性的

 

如果不考虑事务的隔离性,事务的并发会出现:

脏读:读取其他事务未提交的数据

不可重复读:在一个事务内,多次读取表中的数据,结果不一致(与脏读的区别是:读取的是已经提交的),主要是针对记录的值(update操作)

虚读:在一个事务内,多次查询的表的数据不一致,主要是针对记录的个数(insertdelete

SQL查询强化案例:

学生选课信息查询案例:

准备工作:

CREATE TABLE teacher (  id int(11) NOT NULL primary key auto_increment,  name varchar(20) not null unique );CREATE TABLE student (  id int(11) NOT NULL primary key auto_increment,  name varchar(20) NOT NULL unique,  city varchar(40) NOT NULL,  age int ) ;CREATE TABLE course(  id int(11) NOT NULL primary key auto_increment,  name varchar(20) NOT NULL unique,  teacher_id int(11) NOT NULL,  FOREIGN KEY (teacher_id) REFERENCES teacher (id));CREATE TABLE studentcourse (   student_id int NOT NULL,   course_id int NOT NULL,   score double NOT NULL,   FOREIGN KEY (student_id) REFERENCES student (id),   FOREIGN KEY (course_id) REFERENCES course (id));insert into teacher values(null,关羽);insert into teacher values(null,张飞);insert into teacher values(null,赵云);insert into student values(null,小王,北京,20);insert into student values(null,小李,上海,18);insert into student values(null,小周,北京,22);insert into student values(null,小刘,北京,21);insert into student values(null,小张,上海,22);insert into student values(null,小赵,北京,17);insert into student values(null,小蒋,上海,23);insert into student values(null,小韩,北京,25);insert into student values(null,小魏,上海,18);insert into student values(null,小明,广州,20);insert into course values(null,语文,1);insert into course values(null,数学,1);insert into course values(null,生物,2);insert into course values(null,化学,2);insert into course values(null,物理,2);insert into course values(null,英语,3);insert into studentcourse values(1,1,80);insert into studentcourse values(1,2,90);insert into studentcourse values(1,3,85);insert into studentcourse values(1,4,78);insert into studentcourse values(2,2,53);insert into studentcourse values(2,3,77);insert into studentcourse values(2,5,80);insert into studentcourse values(3,1,71);insert into studentcourse values(3,2,70);insert into studentcourse values(3,4,80);insert into studentcourse values(3,5,65);insert into studentcourse values(3,6,75);insert into studentcourse values(4,2,90);insert into studentcourse values(4,3,80);insert into studentcourse values(4,4,70);insert into studentcourse values(4,6,95);insert into studentcourse values(5,1,60);insert into studentcourse values(5,2,70);insert into studentcourse values(5,5,80);insert into studentcourse values(5,6,69);insert into studentcourse values(6,1,76);insert into studentcourse values(6,2,88);insert into studentcourse values(6,3,87);insert into studentcourse values(7,4,80);insert into studentcourse values(8,2,71);insert into studentcourse values(8,3,58);insert into studentcourse values(8,5,68);insert into studentcourse values(9,2,88);insert into studentcourse values(10,1,77);insert into studentcourse values(10,2,76);insert into studentcourse values(10,3,80);insert into studentcourse values(10,4,85);insert into studentcourse values(10,5,83);

-- 1、查询平均成绩大于70分的同学的学号和平均成绩

select student_id,FORMAT(avg(score),2) as ‘平均成绩‘ from studentcourse group by student_id having avg(score) > 70;

技术分享

-- 2、查询所有同学的学号、姓名、选课数、总成绩

select student.id,student.name,t.countCourse,t.sumScore from student,(

select student_id,count(*) as ‘countCourse‘,sum(score) as ‘sumScore‘ from studentcourse group by student_id

) as t where student.id = t.student_id;

技术分享

-- 3、查询学过赵云老师所教课的同学的学号、姓名

select id,name from student where id in (

select student_id from studentcourse where course_id in(

select id from course where teacher_id = (

select id from teacher where name=‘赵云‘)));

 技术分享

-- 4、查询没学过关羽老师课的同学的学号、姓名

select id,name from student where id not in (

select student_id from studentcourse where course_id in(

select id from course where teacher_id = (

select id from teacher where name=‘关羽‘)));

技术分享

-- 5、查询没有学三门课以上的同学的学号、姓名

select id,name from student where id in (

select student_id from studentcourse group by student_id having count(*) <= 3);

技术分享

-- 6、查询各科成绩最高和最低的分

select course_id,max(score),min(score) from studentcourse group by course_id;

技术分享

-- 7、查询学生信息和平均成绩

select student.*,format(t.avgScore,2) ‘平均分‘ from student,(

select student_id,avg(score) as ‘avgScore‘ from studentcourse group by student_id) as t

where student.id = t.student_id;

技术分享

-- 8、查询上海和北京学生数量

select city,count(*) from student group by city having city in (‘北京‘,‘上海‘);

技术分享

-- 9、查询不及格的学生信息和课程信息

select student.*,course.* from student,course,(

select student_id,course_id from studentcourse where score < 60) as t

where student.id = t.student_id and course.id = t.course_id;

技术分享

-- 10、统计每门课程的学生选修人数(超过四人的进行统计)

select course_id,count(*) from studentcourse group by course_id having count(*) > 4;

技术分享

 

SQL数据库操作(CURD)