create table aa(id int(1) PRIMARY key,name char(20));
create table cc(s_id int(1) PRIMARY key,score char(20));
insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99');
insert into cc(s_id,score)values(1003,'94');
select * from aa;
select * from cc;
# 内连接 INNER JOIN 对于两表当中有字段相同的值就可以用内连接进行两表连接
select * FROM aa INNER JOIN cc ON aa.id=cc.s_id;
# 左连接 LEFT JOIN 以左表为主,对于不符合条件的数据显示null
select * FROM aa LEFT JOIN cc ON aa.id=cc.s_id;
# 右连接 RIGHT JOIN 以右表为主,对于不符合条件的数据显示null
select * FROM aa RIGHT JOIN cc ON aa.id=cc.s_id;
# 基本链接 WHERE 对于两个表中有字段值相同的就可以用基本链接
select * FROM aa,cc where aa.id=cc.s_id;
# 硬连接 UNION 机械连接 ==》条件aa表的字段数目需要和cc表的字段数目一样
SELECT * from aa UNION SELECT * FROM cc;
SELECT * from cc UNION SELECT * FROM aa;
select * from aa;
select * from cc;
# 临时表
-- 查询张三的成绩
SELECT * from aa,cc WHERE aa.id=cc.s_id # 作为一个临时表
SELECT t.score from (SELECT score from aa,cc WHERE aa.id=cc.s_id)t WHERE t.name="zhangsan"
# 嵌套等于 方法
SELECT id from aa where name='zhangsan'
select score from cc WHERE s_id=(SELECT id from aa where name='zhangsan')
# 嵌套in 方法
select score from cc WHERE s_id in (SELECT id from aa where name='zhangsan')
desc dept;
SELECT * from dept;
desc emp;
SELECT * from emp;
1.列出每个部门的平均收入及部门名称;
2.财务部门的收入总和;
3.It技术部入职员工的员工号
4.财务部门收入超过2000元的员工姓名
5.找出销售部收入最低的员工的入职时间;
6.找出年龄小于平均年龄的员工的姓名,ID和部门名称
7.列出每个部门收入总和高于9000的部门名称
8.查出财务部门工资少于3800元的员工姓名
9.求财务部门最低工资的员工姓名;
10.找出销售部门中年纪最大的员工的姓名
11.求收入最低的员工姓名及所属部门名称:
12.求李四的收入及部门名称
13.求员工收入小于4000元的员工部门编号及其部门名称
14.列出每个部门中收入最高的员工姓名,部门名称,收入,并按照收入降序;
15.求出财务部门收益最高的俩位员工的姓名,工号,收益
16.查询财务部低于平均收入的员工号与员工姓名:
17.列出部门员工数大于1个的部门名称;
18.列出部门员工收入不超过7500,且大于3000的员工年纪及部门编号;
19.求入职于20世纪70年代的员工所属部门名称;
20.查找张三所在的部门名称;
21.列出每一个部门中年纪最大的员工姓名,部门名称;
22.列出每一个部门的员工总收入及部门名称;
23.列出部门员工收入大于7000的员工号,部门名称;
24.找出哪个部门还没有员工入职;
25.先按部门号大小排序,再依据入职时间由早到晚排序员工信息表 ;
26.求出财务部门工资最高员工的姓名和员工号
27.求出工资在7500到8500之间,年龄最大的员工的姓名和部门名称。
show TABLES
select * from student
desc student
alter TABLE student change id id INT(20)
alter TABLE student DROP PRIMARY key
show index from student # 查看创建的索引
create index aa on student(name) # 给student表的name字段创建索引为aa(普通索引)
create index bb on student(age,class) # 同时给两个字段创建索引
alter table student drop INDEX aa
alter table student drop INDEX bb
create UNIQUE INDEX gg on student(id) # 创建一个唯一索引
#(注意点:有数据相同字段值不能创建唯一索引)
-- 添加了一个主键就是添加了一个主键索引
create UNIQUE INDEX aa on student(age)
alter table student drop INDEX gg
select * from student
desc student
show TABLES
CREATE view dcs as (select id,age,name FROM student)
show create view dcs # 查看创建的视图
CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `dcs`
AS (select `student`.`id` AS `id`,`student`.`age` AS `age`,`student`.`name` AS `name` from `student`)
select * from student # 查看原表
select * from dcs # 查看视图表
UPDATE student set age=30 where id=21 # 修改原表的数据,视图图表数据也会改动
UPDATE dcs set age=32 where id=22 # 修改视图表的数据,原表数据也会改动
drop view dcs;
约束
主键约束:primary KEY
非空约束: not NULL
自增长约束: auto_increment
默认值约束:default
以上4个约束 是对表结构的约束
外键约束:foreign key
create table dcs1(id int(4)PRIMARY key,name varchar(10)) ENGINE=INNODB ;
create table dcs3(sid int(4)primary key,sname varchar(10),CONSTRAINT duoceshi FOREIGN key(sid)REFERENCES dcs1(id))ENGINE=INNODB;
show create table dcs3 # 查看完整的表结构和建表语句
CREATE TABLE `dcs1` (
`id` int(4) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
show create table dcs2
CREATE TABLE `dcs2` (
`sid` int(4) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sid`),
CONSTRAINT `fk_sid` FOREIGN KEY (`sid`) REFERENCES `dcs1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `dcs2` (
`sid` int(4) NOT NULL,
`sname` varchar(10) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
dcs1 ==》主表
dcs2 ==》子表
constraint fk_sid 约束 外键约束的名称是fk_sid
FOREIGN KEY (`sid`) 外键约束创建在dcs2表的sid字段上
ENGINE=InnoDB 数据库存储的引擎 默认的myisam引擎不起作用
desc dcs1
desc dcs2
select * from dcs1;
select * from dcs3;
INSERT INTO dcs3 VALUES(2,'xiaojiu') # 主表不存在的数据,在任何的子表中无法插入相关的数据
DELETE from dcs1 where id=1
DELETE from dcs3 where sid=1 # 删除主表的数据,需要先删除子表的数据
INSERT INTO dcs1 VALUES(1,'xiaowang'),(2,'xiaoliu')
INSERT INTO dcs3 VALUES(1,'xiaowu')
ALTER TABLE dcs2 drop FOREIGN key fk_sid # 删除外键约束(如果有自增长,需要先删除)
存储过程的格式:
drop PROCEDURE if EXISTS dcs3; # 增减健壮性,如果dcs3这个存储过程存在,则删除
CREATE PROCEDURE dcs3(); # 创建一个存储过程,名字为dcs3
BEGIN # 存储过程的开始
# 存储过程的函数体,也就时SQL语句
#drop table if EXISTS 表名 (如果创建表需要增强健壮性)
# 创建表结构
# 插入数据
# 查询表结构和表数据
end # 存储过程的结束
call dcs3()
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi()
BEGIN
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
select * from mm where id = 1;
end
call duoceshi()
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi(n INT) # 形式参数
BEGIN
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
select * from mm where id = n;
end
call duoceshi(2) # 实际参数
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi(n int) # (n int) n就是形式参数,参数的类型是int整形
BEGIN
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
if n = 0 THEN # 判断语句 if条件单分支
select COUNT(*) from mm; # 如果调用的参数等0 就执行这条语句
else
select MAX(score) from mm; # 如果调用的参数不能与0 就执行这条语句
end if; # 有几个if 就要用几个end if
end
call duoceshi(0)
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi(n int) # (n int) n就是形式参数,参数的类型是int整形
BEGIN
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
if n = 0 THEN # 判断语句 if条件多分支
select COUNT(*) from mm; # 如果调用的参数等0 就执行这条语句
else if n >0 && n<8 THEN # && 与的意思
select MAX(score) from mm;
else if n >= 8 THEN
select * from mm ORDER BY score DESC;
ELSE
select * FROM mm;
end if;
end if;
end if; # 有几个if 就要用几个end if
end
call duoceshi(1)
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi(n int) # (n int) n就是形式参数,参数的类型是int整形
BEGIN
DECLARE i int(20) DEFAULT(SELECT count(*) FROM mm); # DECLARE 定义一个变量i 类型为int整形
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
if n = 0 THEN # 判断语句 if条件多分支
select COUNT(*) from mm; # 如果调用的参数等0 就执行这条语句
else if n >0 && n<i THEN # && 与的意思
select MAX(score) from mm;
else if n >= i THEN
select * from mm ORDER BY score DESC;
ELSE
select * FROM mm;
end if;
end if;
end if; # 有几个if 就要用几个end if
end
call duoceshi(9)
drop PROCEDURE if EXISTS duoceshi;
CREATE PROCEDURE duoceshi(n int) # (n int) n就是形式参数,参数的类型是int整形
BEGIN
DECLARE i int(20) DEFAULT(SELECT count(*) FROM mm); # DECLARE 定义一个变量i 类型为int整形
drop TABLE if EXISTS mm;
CREATE table mm(id int(20) PRIMARY key auto_increment,score INT(20));
INSERT INTO mm VALUES(1,89),(2,88);
INSERT INTO mm VALUES(3,25),(4,67);
INSERT INTO mm VALUES(5,89),(6,99);
INSERT INTO mm VALUES(7,19),(8,62);
if n = 0 THEN # 判断语句 if条件多分支
select COUNT(*) from mm; # 如果调用的参数等0 就执行这条语句
else if n >0 && n<=i THEN # && 与的意思
select MAX(score) from mm;
ELSE
WHILE n>i DO
INSERT INTO mm(score)VALUES(48);
set i=i+1;
end WHILE;
end if;
end if; # 有几个if 就要用几个end if
end
call duoceshi(100)
select * from mm |
|