mysql中sql表的基本操作

90 阅读 0 评论 60 点赞

我是靠谱客的博主成就鸭子，最近开发中收集的这篇文章主要介绍mysql中sql表的基本操作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

约束建库：

create database day15;
use day15;

-- 创建学生表st3, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
create table st3 (
   id int,
   name varchar(20) unique
);

desc st3;
-- 添加一个同名的学生
select * from st3;

insert into st3 values(1,'张三');
-- 添加重复的： Duplicate entry '张三' for key 'name'
insert into st3 values(2,'张三');

-- 创建表学生表st4, 包含字段(id,name,gender)其中name不能为NULL
create table st4(
   id int,
   name varchar(20) not null,
   age int
);

desc st4;

-- 添加一条记录其中姓名不赋值
-- Column 'name' cannot be null
insert into st4 values(1,null,20);

select * from st4;
-- Field 'name' doesn't have a default value
insert into st4 (id,age) values(1,30);

-- 创建一个学生表 st5，包含字段(id,name,address)， 地址默认值是广州
create table st5(
   id int,
   name varchar(20),
   address varchar(30) default '广州'
);

desc st5;

-- 添加一条记录,使用默认地址
select * from st5;
insert into st5 (id,name) values (1,'张三');

insert into st5 values (1,'张三',default);

-- 添加一条记录,不使用默认地址
insert into st5 values(2,'李四','深圳');

外键约束：

-- 创建一个员工表
create table emp (
    id int primary key auto_increment,  -- 主键自增长
    `name`  varchar(20),
    age int,
    dep_name varchar(10),  -- 部门名
    dep_location varchar(20)  -- 部门所在城市
);

select * from emp;
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小乔', 18, '公关部', '东莞');
/*
   这种表设计上有问题：
   1. 会出现大量重复的数据
   2. 会出现修改或删除的问题，如：更新2号员工：销售部 广州。如果一个部门只有一个员工
*/

delete from emp where id = 7;

-- 解决方法：将部门设计成一张表
create table department (
   id int primary key auto_increment,
   dep_name varchar(10),  -- 部门名
   dep_location varchar(20)  -- 部门所在城市
);

-- 员工表
create table employee (
    id int primary key auto_increment,  -- 主键自增长
    `name`  varchar(20),
    age int,
    dept_id int  -- 外键，引用部门表中主键
);

-- 添加部门表的记录
insert into department values(null,'研发部', '广州'),(null, '销售部', '深圳');
select * from department;

-- 添加员工信息
INSERT INTO employee (NAME, age, dept_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dept_id) VALUES ('王五', 20, 1);

INSERT INTO employee (NAME, age, dept_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dept_id) VALUES ('小王', 18, 2);

select * from employee;

-- 违反外键约束
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);

-- 删除从表 employee
drop table employee;

-- 创建从表 employee 并添加外键约束emp_depid_fk
create table employee (
    id int primary key auto_increment,  -- 主键自增长
    `name`  varchar(20),
    age int,
    dept_id int,  -- 外键，引用部门表中主键
    foreign key (dept_id) references department(id)
);

-- 部门错误的数据添加失败
-- Cannot add or update a child row: a foreign key constraint fails 
INSERT INTO employee (NAME, age, dept_id) VALUES ('小乔', 18, 6);

-- 删除employee表的employee_ibfk_1外键
alter table employee drop foreign key employee_ibfk_1;

-- 在employee表情存在的情况下添加外键
alter table employee add  foreign key (dept_id) references department(id);

-- 要把部门表中的id值2，改成5，能不能直接更新呢？
select * from department;
select * from employee;
-- Cannot delete or update a parent row: a foreign key constraint fails (不能直接修改主表中主键)
update department set id=5 where id=2;

-- 要删除部门id等于1的部门, 能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails(如果有从表引用了主表中主键，主表中这条记录不能删除)
delete from department where id=1;

-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;

-- 添加外键约束，级联更新和级联删除
alter table employee add constraint fk_emp_dept 
foreign key (dept_id) references department(id) on update cascade on delete cascade;

-- delete from employee where id > 6;
select * from employee;

-- 把部门表中id等于1的部门改成id等于9
select * from department;
update department set id=9 where id=1;

-- 删除部门号是2的部门
delete from department where id=2;

表与表之间的关系：

-- 一对一的关系
-- 主表
create table stu(
   id int primary key,  -- 主键
   name varchar(20)  
);

-- 从表
create table info(
    id int primary key,  -- 主键
    address varchar(20),
    use_name varchar(10),
    weight double,
    -- 创建外键，外键又是主键
    foreign key(id) references stu(id)
);

/*
一对多，一个分类对应多条线路
因为sql中不区分大小写：如果有多个单词，中间使用_分隔
*/
/* 
创建旅游线路分类表tab_category
cid旅游线路分类主键，自动增长
cname旅游线路分类名称非空，唯一，字符串100
*/
create table tab_category(
   cid int primary key auto_increment,
   cname varchar(100) not null unique   
);

/*
创建旅游线路表tab_route
rid旅游线路主键，自动增长
rname旅游线路名称非空，唯一，字符串100
price价格
rdate 上架时间，日期类型
cid 外键，所属分类
*/
create table tab_route(
   rid int primary key auto_increment,
   rname varchar(100) not null unique,
   price double,
   rdate date,
   cid int,  -- 外键
   foreign key(cid) references tab_category(cid)
);

-- 多对多的关系
/*
创建用户表tab_user
uid用户主键，自增长
username用户名长度100，唯一，非空
password密码长度30，非空
name真实姓名长度100
birthday生日
sex性别，定长字符串1
telephone手机号，字符串11
email邮箱，字符串长度100
*/
create table tab_user(
   uid int primary key auto_increment,
   username varchar(100) unique not null,
   password varchar(30) not null,
   name varchar(100),
   birthday date,
   sex char(1),
   telephone varchar(11),
   email varchar(100)
);


/* 
创建收藏表tab_favorite
rid 旅游线路id，外键
date 收藏时间
uid用户id，外键
rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
   rid int,  -- 线路id的外键
   `date` datetime,
   uid  int ,  -- 用户id的外键
   -- 创建复合主键
   primary key(rid, uid),
   foreign key(rid) references tab_route(rid),  -- 关联了线路的主键
   foreign key(uid) references tab_user(uid)  -- 关联了用户的主键
);

表的连接：

# 创建部门表
create table dept(
  id int primary key auto_increment,
  name varchar(20)
);

insert into dept (name) values ('开发部'),('市场部'),('财务部');  
# 创建员工表
create table emp (
  id int primary key auto_increment,
  name varchar(10),
  gender char(1),   -- 性别
  salary double,   -- 工资
  join_date date,  -- 入职日期
  dept_id int,
  foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);  

insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

-- 查询孙悟空在哪个部门名字
select * from emp;

select * from dept;

-- 需求：查询所有的员工和所有的部门
-- 查询2张表结果是，是2张表记录的乘积，称为笛卡尔积
select * from emp,dept;

-- 如何消除笛卡尔积：条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接，使用where，没有用到join...on

-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只显示2列
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;

-- 显示内连接, on后面就是表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;


-- 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
-- 1. 确定查询哪些表
select * from emp e inner join dept d;
-- 2. 确定表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;
-- 3. 如果有其它的查询条件，添加条件
select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
-- 4. 确定查询哪些列
select e.id 编号, e.name 姓名, e.gender 性别, e.salary 工资, d.name 部门名
 from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

-- 左连接
-- 需求：在部门表中增加一个销售部，需要查询所有的部门和员工，将部门表设置成左表，员工表设置成右表
select * from dept;
insert into dept values(null,'销售部');


-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;

-- 左连接：左表中所有的记录都出现在结果，如果右表没有匹配的记录，使用NULL填充

-- 右连接
select * from emp;
-- 需求：在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
insert into emp values(null, '沙僧','男',6666,'2013-02-24',null);

-- 希望员工的信息全部显示出来
-- 使用内连接查询 
select * from dept d inner join emp e on d.id = e.dept_id;

-- 使用右外连接查询 
select * from dept d right join emp e on d.id = e.dept_id;

子查询：

-- 需求：查询开发部中有哪些员工
select * from emp;

select id from dept where name='开发部';

select * from emp where dept_id=1;

-- 写成一句：使用子查询
select * from emp where dept_id=(select id from dept where name='开发部');

select * from emp where dept_id=(select id from dept where name='市场部');

-- 案例：查询工资最高的员工是谁？ 
-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary=(select max(salary) from emp);

-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');

-- 查询工资大于5000的员工，来自于哪些部门，得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;

-- 2. 再查询在这些部门id中部门的名字
-- Subquery returns more than 1 row
select * from dept where id=(select dept_id from emp where salary > 5000);
select * from dept where id in(select dept_id from emp where salary > 5000);

-- 列出工资高于在1号部门工作的所有员工，显示员工姓名和工资、部门名称。
-- 1. 查询1号部门所有员工的工资，得到多行单列
select salary from emp where dept_id=1;

-- 2. 使用大于号不能计算，怎么办 
select * from emp where salary > all (select salary from emp where dept_id=1);
-- any表示任何一个，all所有
select * from emp where salary > any (select salary from emp where dept_id=1);

-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';

-- 2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;

select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;

多表查询演示：

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

select * from dept;
select * from emp;
select * from job;
select * from salarygrade;

-- 查询所有员工姓名，工资，工资等级
-- 1. 确定查询哪些表
select * from emp e inner join salarygrade s;
-- 2. 确定表连接的条件
select * from emp e inner join salarygrade s on e.salary between s.losalary and s.hisalary;
-- 3. 确定查询哪些列
select e.ename 姓名, e.salary 工资, s.grade 工资等级
 from emp e inner join salarygrade s on e.salary between s.losalary and s.hisalary;

-- 查询经理的信息。显示经理姓名，工资，职务名称，部门名称，工资等级
-- 1. 确定查询哪些表
-- 2. 确定表连接的条件
-- 3. 确定查询哪些列
select e.ename 姓名, e.salary 工资, j.jname 职务, d.dname 部门名, s.grade 工资等级
 from emp e inner join dept d on e.dept_id = d.id
inner join job j on j.id = e.job_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where j.jname='经理';

-- 查询部门编号、部门名称、部门位置、部门人数
select * from dept;
-- 部门人数需要查询员工表
select d.*, count(e.dept_id) 部门人数 from dept d left join emp e on d.id = e.dept_id group by d.id;

select * from dept d left join emp e on d.id = e.dept_id

-- 员工的姓名及其直接上级的姓名
select * from emp e left join emp m on e.mgr = m.id;

select e.ename 员工名字, IFNULL(m.ename,'BOSS') 上级名字 from emp e left join emp m on e.mgr = m.id;

-- 查询工资高于公司平均工资 员工所有信息，部门名称，上级领导，工资等级。没有上级的员工显示为"自己"
-- 查询公司平均工资
select avg(salary) from emp;
-- 1. 确定查询哪些表：员工表，员工表，部门表，工资等级
select * from emp e left join emp m on e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary;
-- 2. 确定表连接的条件
-- 3. 确定查询哪些列
select e.*,d.dname 部门名, ifnull(m.ename,'自己') 上级名字, s.grade 工资等级  from emp e left join emp m on
 e.mgr = m.id inner join dept d on d.id = e.dept_id inner join salarygrade s
on e.salary between s.losalary and s.hisalary where e.salary > (select avg(salary) from emp);