数据库基本操作

是佩奇吗？

已于 2022-07-28 10:53:59 修改

阅读量857

点赞数

文章标签：数据库 sql mysql

于 2022-07-27 17:38:17 首次发布

本文链接：https://blog.csdn.net/qq_33827423/article/details/126019842

版权

数据库

DDL操作数据库

show databases ; #查询数据库
create database db1; #创建数据库
create database if not exists db1;#查询如果数据库存在就不创建
create database if not exists db2;
drop database db1; #删除一个数据库
drop database if exists db1; #删除一个数据库之前先进行判断 被删除的数据库是否存在
use db2; #切换当前正在使用的数据库
select DATABASE(); #当前正在使用的数据库

DDL操作表

创建（Create）查询（Retrieve）修改（Update）删除（Delete）

show tables; #查询数据库中的表
desc func; #查询数据库中某个表的结构（表的结构信息）

create table 学生( 
    姓名 char(10), #（？）代表表中的数据的最大长度
    编号 tinyint, 
    地址 char(10)
);
show tables;  #查看当前存在的表
desc 学生;  #查看指定的表

数据类型：

create table Stdent(
    number tinyint(20),
    name varchar(20),
    sex char(2),
    brithday date,
    score float(4,2),
    e_maila_ddress varchar(64),
    phone varchar(18),
    state tinyint(1)
);

drop table stdent; #直接删除一个表
drop table if exists stdent; #判断是否存在该表 如果存在则删除

select * from student; #查询表的所有数据

#条件修改 将姓名为卡卡罗特的所有数据进行指定的修改
update student set name='悟空',sex='赛亚' where name='卡卡罗特';

#删除表中姓名为悟空或者是Tom的行
delete from student where name='悟空'||name='Tom';

#按照指定的字段查询并起别名
select name as 姓名 ,age as 年龄,addres as 地址 from student;

select * from student where addres!='贝吉塔星';
#占位符模糊查询（查询第一个字为贝的字段）
select * from student where addres like '贝%';
#占位符模糊查询（查询第二个字为罗的字段）
select * from student where name like '_罗%';
#占位符模糊查询（查询含有星的字段）
select * from student where addres like '%星%';

#按照年龄升序排列
select * from student order by age asc;
#按照年龄降序排序
select * from student order by age desc ;
#按照年龄升序排列,如果年龄一样按照入学时间降序排列
select * from student order by age asc ,hire_date desc;

#聚合函数的计算年龄最小的字段
select min(age) from  student;

# 分组查询,查询数学的平均分
select avg(math) from student group by name;

# 分组查询，
# 按照出生地分组，并且统计平均分与人数
select addres as 出生地, avg(math) as 平均分 ,count(name) as 统计人数 from student group by addres ;

#分页查询
#从第三页开始查询，每页只显示两行信息
select * from student limit 4,2;

drop table if exists emp;

-- 员工表
create table emp(
    #默认自增加对于数值类型
    id int primary key auto_increment, -- 员工的id且唯一
    ename varchar(50) not null unique, -- 员工的名字且唯一
    joindate date not null, -- 员工的入职日期且非空
    salary double(7,2) not null,  -- 员工的工资且非空
    bonus double(7,2) default 0 -- 员工的奖金，如果没有则为默认值0
);

#添加信息
insert into emp values(
  001,'卡卡罗特',20210101,1300.00,100
),(
  002,'贝吉塔',20210102,5400.00,200
),(
  003,'布罗利',20210103,3200.00,0
),(
  004,'特兰克斯',20210104,4300.00,120
),(
  005,'Dr，蒙奇-路飞',20210105,1389.00,190
);

insert into emp(id,ename,joindate,salary,bonus) value(6,'汉库克',20210105,13999,200);
insert into emp(id,ename,joindate,salary,bonus) value(7,'诺诺罗亚-索伦',20210305,18999,null);
#如果默认值在创建数据的时候给赋值了null那么在表中不在是默认的数值而是刚才复制的null
insert into emp(id,ename,joindate,salary,bonus) value(8,'山治',20210307,13099,0);
insert into emp(id,ename,joindate,salary) value(9,'卡普','20210307',13342);
#查询值
select * from emp;

#默认自增加对于数值类型
id int primary key auto_increment, -- 员工的id且唯一
 
#会默认自加给值
insert into emp(ename,joindate,salary,bonus) value('汉库克',20210105,13999,200);
insert into emp(ename,joindate,salary,bonus) value('诺诺罗亚-索伦',20210305,18999,null);
#如果默认值在创建数据的时候给赋值了null那么在表中不在是默认的数值而是刚才复制的null
insert into emp(ename,joindate,salary,bonus) value('山治',20210307,13099,0);
insert into emp(id,ename,joindate,salary) value(null,'卡普','20210307',13342);

-- --------------------------------------------- 约束 (外键) --------------------------------------- 准备数据create table dept(    id   int auto_increment comment 'ID' primary key,    name varchar(50) not null comment '部门名称')comment '部门表';INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4, '销售部'), (5, '总经办');create table emp(    id  int auto_increment comment 'ID' primary key,    name varchar(50) not null comment '姓名',    age  int comment '年龄',    job varchar(20) comment '职位',    salary int comment '薪资',    entrydate date comment '入职时间',    managerid int comment '直属领导ID',    dept_id int comment '部门ID')comment '员工表';INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id) VALUES            (1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),            (3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),            (5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);-- 添加外键alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);-- 删除外键alter table emp drop foreign key fk_emp_dept_id;-- 外键的删除和更新行为alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

-- 多对多 ----------------create table student(    id int auto_increment primary key comment '主键ID',    name varchar(10) comment '姓名',    no varchar(10) comment '学号') comment '学生表';insert into student values (null, '黛绮丝', '2000100101'),(null, '谢逊', '2000100102'),(null, '殷天正', '2000100103'),(null, '韦一笑', '2000100104');create table course(    id int auto_increment primary key comment '主键ID',    name varchar(10) comment '课程名称') comment '课程表';insert into course values (null, 'Java'), (null, 'PHP'), (null , 'MySQL') , (null, 'Hadoop');create table student_course(    id int auto_increment comment '主键' primary key,    studentid int not null comment '学生ID',    courseid  int not null comment '课程ID',    constraint fk_courseid foreign key (courseid) references course (id),    constraint fk_studentid foreign key (studentid) references student (id))comment '学生课程中间表';insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

-- --------------------------------- 一对一 ---------------------------create table tb_user(    id int auto_increment primary key comment '主键ID',    name varchar(10) comment '姓名',    age int comment '年龄',    gender char(1) comment '1: 男 , 2: 女',    phone char(11) comment '手机号') comment '用户基本信息表';create table tb_user_edu(    id int auto_increment primary key comment '主键ID',    degree varchar(20) comment '学历',    major varchar(50) comment '专业',    primaryschool varchar(50) comment '小学',    middleschool varchar(50) comment '中学',    university varchar(50) comment '大学',    -- unique 修饰是指唯一约束    userid int unique comment '用户ID',    constraint fk_userid foreign key (userid) references tb_user(id)) comment '用户教育信息表';insert into tb_user(id, name, age, gender, phone) values        (null,'黄渤',45,'1','18800001111'),        (null,'冰冰',35,'2','18800002222'),        (null,'码云',55,'1','18800008888'),        (null,'李彦宏',50,'1','18800009999');insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values        (null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),        (null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),        (null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),        (null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);

多表查询

笛卡尔积的消除查询

select * from emp,dept where emp.dept_id=dept.id;

-- 查询每一个员工以及关联的部门名称(隐式内连接)select emp.name,dept.name from emp,dept where emp.id=dept.id;-- 这里通过给表添加一个别名  直接通过别名来检索数据select e.name,d.name from emp e , dept d where e.dept_id = d.id;

左外连接

-- 查询emp表中的所有的数据和对应的部门信息(左外连接)select * from emp e left outer join dept d on e.id=d.id;

右外连接

-- 查询dept中的所有信息和对应的员工信息(右外连接)select * from dept d right outer join emp e on d.id=e.id;

自连接查询

自连接查询需要注意的是，由于同时公用的是一张表，所以在查询的时候需要进行表的匿名

-- 薪资大于500年龄小于50岁的员工select * from emp where salary>5000union allselect * from emp where age>50;-- union all 关键字的作用是值将两次的查询结果进行了合并-- 这样查询出的结果会出现 重复的字段信息

*可以看到张三丰的字段出现了两次

-- 去除union all 关键字中的 all 就可以去除查询结果中的重复的关键字select * from emp where salary>5000unionselect * from emp where age>50;

需要注意的一点就是
union all的关键字合并查询两条select语句所查询的字符都是必须相同的，否则会因为字段的数量不同出现查询的错误

-- 查询销售部的所有的员工的信息select * from emp where id= (select id from dept where name='销售部');-- 查询在韦一笑之后入职的所有的员工的信息select * from emp where entrydate>(select entrydate from emp where name='韦一笑');

-- 查询销售部和市场部所有的员工信息select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

-- 查询比研发部任意一人工资搞高的员工select * from emp where salary > some (select salary from emp where dept_id=(select id from dept where name='研发部'));

多表查询

-- 查询员工的姓名、年龄、职位、部门信息(隐式连接)select e.name,e.age,e.job, d.name from emp e,dept d where e.dept_id=d.id;-- 查询年龄小于30岁的员共的姓名，年龄，职位，部门信息(显示内连接)select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id and e.age>30;-- 查询所有员工的部门名称与部门信息-- distinct 过滤重复的信息select distinct d.name,d.id from emp e ,dept d where e.dept_id=d.id;-- 查询所有的年龄大于40岁的员工的，以及员工归属的部门名称，如果员工没有分配部门，也有显示出来-- 这里需要注意的是 员工如果没有分配部门也会显示出来select e.*,e.name from emp e left join dept d on e.dept_id = d.id where e.age>40;-- 查询员工的工资等级select e.name,s.grade 工资等级,e.salary,s.losal 最低工资,s.hisal 最高工资 from   emp e,salgrade s where e.salary>s.losal and  e.salary<s.hisal;--  关键字 between ... and ...在最小值与最大值 之间select e.name,s.grade 工资等级,e.salary,s.losal 最低工资,s.hisal 最高工资 from emp e,salgrade s where e.salary between s.losal and s.hisal;-- 查询"研发部"所有的员工、工资等级等信息select e.salary from emp e,dept d where e.dept_id=d.id and d.name='研发部';select e.name,s.grade,d.name from emp e,salgrade s,dept d where e.dept_id=d.id  and (e.salary between s.losal and s.hisal) and d.name='研发部';-- 查询’研发部‘员工的平均工资select avg (e.salary) from emp e,dept d where e.dept_id=d.id and d.name='研发部';-- 查询比灭绝薪资高的所有员工select  e.salary from emp e where e.name='灭绝';select * from emp e where e.salary>(select  e.salary from emp e where e.name='灭绝');-- 查询低于本部门平均工资的员工信息select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.id=2;select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.id=5;select e.*  from emp e where e.salary> (select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.id=e.dept_id);-- 查询所有的学生选课情况-- 展示出所有的学生的名称 学号以及课程名称-- 同过学生的id找到对应课程的id-- s.id 学生id-- k.id 课程idselect k.courseid from student s,course c,student_course k where  s.id=k.studentid;-- 通上面查找的id返回我们的课程名--select c.name,s.name from student s,course c,student_course k where s.id=k.studentid and c.id =k.courseid;

事务

-- 事务提交select @@autocommit;set  @@autocommit = 0; -- 设置为手动提交-- 查询张三的账户余额select * from account a where a.name='张三';-- 张三的账户减少1000元update  account a set a.money=a.money-1000  where a.name='张三';-- 程序抛出异常-- 李四的账户增加1000元update account a set a.money=a.money+1000 where a.name='李四';-- 查询李四的账户余额-- 本地可以查询到数据的修改  可以理解为数据只是修改了本地  但是数据的本生是没有发生改变的select * from account ;-- commit 指令将数据提交到 其他的会话窗口后更新提交的数据  commit;

事务处理的方式二

-- --------------start  transaction 开启事务的提交-------------- 开启手动提交start transaction ;-- 张三的账户减少1000元update  account a set a.money=a.money-1000  where a.name='张三';程序抛出异常-- 李四的账户增加1000元update account a set a.money=a.money+1000 where a.name='李四';-- 查询李四的账户余额-- 本地可以查询到数据的修改  可以理解为数据只是修改了本地  但是数据的本生是没有发生改变的select * from account ; -- 确认提交事务commit;-- 回滚事务rollback;