MySQL基础命令与案列解析

Wesley13
• 阅读 576

SQL的基本语法规范

1、sql不区分大小写,但是习惯上关键字、系统保留字、函数名称大写,表名和列名小写;
2、sql使用空格或者回车来分隔每个单词都一样,习惯上每个子句换行,WHERE子句中的每个条件占一行,子查询单独占一行;
3、复杂的语句最好加上注释进行说明;
4、sql语句中使用分号作为分隔符,系统读取到分号才会执行语句。

数据定义语言(DDL)

​ DDL是用于描述数据库中要存储的现实世界实体的语言,主要是用在定义或改变结构,数据类型,表之间的链接和约束等初始化工作上,大多在建立表时使用。

1.CREATE

​ create命令为创建,可创建数据库、数据表、存储过程。

创建数据库 create database

在登录MySQL服务后,使用create命令创建数据库,语法为:create database name;

创建数据表 create table

必须要有创建表的权限,表名,列名可以由字母、数字和下划线组成,必须以字母开头,不能是系统关键字

create table 表名(列名1 数据类型,列名2 数据类型,……列名n 数据类型);

/*注释:创建test表*/
CREATE TABLE test
(
#注释:属性(列名)testid,整数类型
testid INT,
testname VARCHAR(20),
age TINYINT,
gender CHAR(1),
birth DATE,
score FLOAT(4,2)
);

数字类型:
int 整数
float(m,n) 小数,最多位数为m,精确到小数点后n位
double(m,n) 双精度小数,最多位数为m,精确到小数点后n位
n会占用m的位数,float(7,2)表示5位整数2位小数

字符类型:
char(n) 最大长度是n,固定长度的字符类型,n的最大值是255字节
varchar(n) 最大长度是n,可变长度的字符类型,n的最大值是65535字节

​ char(n) 如果存储的数据长度不到n个字节,会用空格补齐到n个字节
​ varchar(n) 按照数据的实际长度存储

​ char浪费存储空间,查询效率高
​ varchar节省存储空间,查询效率低

时间和日期类型
date 日期
time 时间
datetime 时间+日期
timestamp 时间戳

创建存储过程 create procedure

​ 存储过程是MySQL编程的容器,mysql编程是将编程语言和sql语句结合,自动执行很多数据库操作,完成复杂的功能。所有的代码必须写在存储过程中进行保存,当需要执行这些代码时,调用存储过程的名字,实现相应的功能。

固定版式为:

delimiter //
create procedure 存储过程名称(参数1,参数2,……参数n)
begin
程序代码;
end;
//
delimiter ;
/*delimiter 就是分隔符的意思,在存储过程中,每一行代码结束后都会有一个分号,如果系统仍旧使用分号作为分隔,会将每一行看作是一个语句,直接执行。
delimiter // 表示告诉系统不要用分号作为分隔符,而是使用//,其他符号也可以,如$$
delimiter ; 表示告诉系统重新将分号作为分隔符,delimiter后面必须有空格,不能换行*/

存储过程可以有参数也可以没有,具体根据需要。即使没有参数括号必须有,存储过程具体要实现什么功能,相关的语句和代码写在begin和end之间。如打印helloworld:

DELIMITER //
CREATE PROCEDURE hello()
BEGIN
SELECT 'hello world';
END;
//
DELIMITER ;
/*执行过后只是将存储过程创建并保存,并不会执行其中的语句,如果要调用存储过程,使用 call 存储过程名称*/
CALL hello()

存储过程中根据需要可以定义参数,参数是临时存储和传递值的中间量,可以在存储过程外赋值,然后将值传递到存储过程内,参与运算,得到相应的结果;也可以在存储过程内获取运算的结果,传递到存储过程外。定义参数的方式:参数类型 参数名称 数据类型
in 类型表示将数据从外部传递到内部的参数
out 类型表示将结果从内部传递到外部的参数
in out 上述两个功能都具备的参数

#编写一个存储过程,要求输入一个testid,在测试表(test表)查询出对应的testname
DELIMITER //
CREATE PROCEDURE p_testid (IN a INT)
BEGIN
SELECT testname FROM test
WHERE testid = a;
END;
//
DELIMITER ;
#调用过程,有参数的过程必须给参数赋值
CALL p_testid(17968);

要给参数或者变量赋值,有2种方法
1.手动赋值
set 参数/变量=value;

​ 2.将查询结果赋值给参数或变量

​ select … into 参数/变量 from ……;

/*输入两个人name,计算他们的score之差,使用out类型的参数获取*/
DROP PROCEDURE IF EXISTS pro_minus;
#如果有名称为pro_minus的储存过程(procedure),则删除该储存过程
DELIMITER //
CREATE  PROCEDURE pro_minus(IN a VARCHAR(20),IN b VARCHAR(20),OUT c FLOAT(7,2))
BEGIN
DECLARE ascore FLOAT(7,2) DEFAULT 0;
DECLARE bscore FLOAT(7,2) DEFAULT 0;
SELECT score INTO ascore FROM test
WHERE testname = a;
SELECT score INTO bscore FROM test
WHERE testname = b;
SET c=ascore-bscore;
END;
//
DELIMITER ;
/*存储过程中可以声明变量来临时存储和传递值,但是只能在内部使用
不像参数可以从内部或外部双向传递值
变量声明:declare 变量名称 数据类型 [default 默认值];
必须先声明变量才可以使用*/
CALL pro_minus('KING','JACK',@score);

2.ALTER

表结构修改

在工作中,一般不推荐在表创建成功后再去改表结构,尤其是当表中已经有数据之后,如果确实因为特殊原因需要修改结构,使用alter语句。

1)添加列,在语句最后可以使用first表示在表的第一列添加,也可以用 after 列名 ,放到某列之后

alter table 表名 add 列名 数据类型;

2)删除列

alter table 表名 drop (column) 列名;

3)修改列名/数据类型

alter table 表名 change 原列名 新列名 数据类型;

4)添加默认值

alter table 表名 alter (column) 列名 set default 默认值;

5)删除默认值

alter table 表名 alter (column) 列名 drop default;

约束修改

约束就是数据应该满足的条件或者规则,不满足条件的数据不能被放入表中,约束以列为单位进行定义,规定某列的数据必须满足的条件

主键(primary key)约束:定义某一列为主键,该列数据不能重复也不能为空,每张表主键只能有一个。

外键(foreign key)约束:定义某一列为外键,参照其他表的主键,取值可以为空,如果不为空则必须与主键的值对应,外键只能定义成表级约束。

定义语法:

foreign key(列名) references 表名(列名)

非空(not null)约束:定义某一列的取值不能为空,非空只能定义成列级约束

唯一(unique)约束:定义某一列的值必须唯一

检查(check)约束:mysql不支持

约束可以在创建表的同时创建,也可以在创建表之后创建,约束分为列级约束和表级约束两种,两者效果一样,只是定义的方式不一样

create table 表名
(
列名1 数据类型 [default 默认值] [列级约束1 列级约束2 ……],
列名2 数据类型 [default 默认值] [列级约束1 列级约束2 ……],
……
列名n 数据类型 [default 默认值] [列级约束1 列级约束2 ……],
constraint 约束名称1 约束类型(列名),
constraint 约束名称2 约束类型(列名),
……
);
*/
CREATE TABLE test3
(
testid INT PRIMARY KEY,
testname VARCHAR(20) NOT NULL,
deptno INT,
FOREIGN KEY(deptno) REFERENCES dept(deptno),
phone BIGINT UNIQUE
);
DESC test3;

INSERT INTO test3
VALUES (1000,'xiaoming',10,13800138000);

SELECT * FROM test3;

INSERT INTO test3
VALUES (1005,'xiaoqiang',40,13800138005);

创建表之后可以修改约束

添加主键约束

alter table 表名 add constraint 约束名称 primary key(列名);

删除主键约束

alter table 表名 drop constraint primary key;

添加唯一约束

alter table 表名 add unique 列名;

删除唯一约束

alter table 表名 drop index 列名;

添加非空约束

alter table 表名 change 原列名 新列名 数据类型 not null;

删除非空约束

alter table 表名 change 原列名 新列名 数据类型;

添加外键约束

alter table 表名 add foreign key(列名) references 表名(列名);

删除外键约束

alter table 表名 drop foreign key 外键名称;


SHOW CREATE TABLE test3;
/*
CREATE TABLE `test3` (
  `testid` int(11) NOT NULL,
  `testname` varchar(20) NOT NULL,
  `deptno` int(11) DEFAULT NULL,
  `phone` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`testid`),
  UNIQUE KEY `phone` (`phone`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

test3_ibfk_1 就是系统自动分配给外键约束的名称
*/

3.DROP

删除表DROP

drop table 表名;

4.TRUNCATE

截取表中的数据TRUNCATE

truncate table 表名;

drop,truncate,delecte三者之间的区别:

drop 属于DDL语句,删除整张表;直接生效不能回滚;
truncate 属于DDL语句,只能快速删除所有数据,不能指定范围;直接生效不能回滚;站在表的层面,快速清空数据;
delete 属于DML语句,可以删除全部也可以删除部分;需要提交才能生效,可以回滚;必须进入表,按照行来删除数据。

数据操作语句(DML)

1、INSERT

INSERT INTO 语句用于向表格中插入新的行和数据

insert into 表名 values (value1,value2,...valueN);

按照表的原有结构放入数据

INSERT INTO dept VALUES (60,'Tech','Mian Yang');

执行的dml语句和ddl语句都是以事务的形式存在的,事务必须提交才能永久生效,可以回滚撤销操作,mysql默认自动提交是开启的,关闭自动提交:
SET autocommit=0;

COMMIT; #提交
ROLLBACK; #回滚

DDL语句都是自动提交的,并且不能关闭

insert into 表名(列名1,列名2,……列名n) values (value1,value2,...valueN);

按照自定义的列放入数据

INSERT INTO emp(empno,ename,job,sal) VALUES (8000,'xiaoming','HR',2500);

未定义的列默认放入空值,也可以使用null放入空值

INSERT INTO dept VALUES (80,'Training',NULL);

使用函数放入数据

INSERT INTO emp(empno,ename,job,hiredate,sal,deptno) 
VALUES (8001,'xiaoqiang','TESTER',CURDATE(),3000,20);

insert into 表名 select …… ; 将查询结果直接放入另一张表,拷贝数据

INSERT INTO bonus
SELECT ename,job,sal,comm FROM emp
WHERE comm IS NOT NULL;

ROLLBACK;#回滚操作

mysql允许一个insert语句同时放入多行数据

insert into 表名 values (value1,value2,...valueN),(value1,value2,...valueN), (value1,value2,...valueN),...;

2、UPDATE

Update 语句用于修改表中的数据,格式为

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

如果不写where子句,会修改所有行的数据

UPDATE emp SET sal=3000;#更新所有的sal
UPDATE emp SET sal=1200 WHERE ename = 'SMITH';#只更新一条sql
SELECT * FROM emp;#查询emp表校验
ROLLBACK;#回滚操作

可以在已有数据的基础上进行修改,30号部门表现不错,每人涨薪300

UPDATE emp SET sal=sal+300 WHERE deptno=30;
ROLLBACK;#回滚操作

注意,在set和where后面,都可以使用子查询,但不能使用本表数据的子查询

3、DELETE

DELETE 语句用于删除表中的行,如果不写where子句,会删除所有数据

DELETE FROM 表名称 WHERE 列名称 = 值

数据查询语句(DQL)

1、select基础

1)*代表所有列

SELECT * FROM 表名称;
#SQL 语句对大小写不敏感。SELECT 等效于 select。

2)查询指定的列

SELECT 列名称 FROM 表名称;

3)对查询结果进行运算

#+  - *  /
#查询每个员工的年薪
SELECT ename,12*sal FROM emp;
#员工的年终奖定为年薪的10%再加上500
SELECT ename,(12*sal)*0.1+500 FROM emp;

4)空值参与运算得到结果也是空值

#计算员工的年收入,emp表不存在comm列
SELECT ename,sal,comm,12*(sal+comm) FROM emp;

5)别名

通过使用 SQL,可以为列名称和表名称指定别名(Alias)

#查询结果中可以给列起别名加以说明
/*
列名 别名
列名 AS 别名
别名中如果包含特殊字符(空格),需要加双引号
双引号只会在起别名的时候出现,其他的所有地方都只能用单引号而不能用双引号
*/
SELECT ename xingming,job zhiwei FROM emp;
SELECT ename AS xingming,job AS zhiwei FROM emp;
SELECT ename 姓名,job 职位 FROM emp;
SELECT ename "xing ming",job "zhi wei" FROM emp;

#别名主要用来标识运算结果
SELECT ename,(12*sal)*0.1+500 AS nianzhongjiang FROM emp;

6)表达式

#表达式根据数据类型的不同分为数字表达式、字符表达式和日期表达式
#手动输入的表达式可以写在select后面,表中有多少行,表达式就显示多少次
#手动输入数字表达式可以直接写,日期和字符表达式必须加上单引号
SELECT 123.456,12*60*48,'I am the king' FROM emp;

#mysql允许只写select,不写from;oracle必须要写select和from,不能缺少
#如果要查询的数据都是手动输入,与表中数据无关,可以不写from
SELECT 123.456,12*60*48,'I am the king';

SELECT 'who are you',ename FROM emp;

SELECT job,'JOB' FROM emp;
#手动字符串必须加单引号,mysql在windows平台字符串不区分大小写,但是在linux区分大小写
#其他的数据库包括编程语言,字符串都要区分大小写

7)distinct去重

SELECT job FROM emp;
SELECT DISTINCT job FROM emp;

SELECT DISTINCT deptno FROM emp;

#多列去重
SELECT DISTINCT job,deptno FROM emp;

#普通列不能和去重的列同时查询,因为行数不匹配,不能组成一张表
#select ename,distinct job from emp;

8)limit分页查询

SELECT * FROM emp;
#如果在查询结果中,想要得到固定某几行的结果,使用limit
#limit 写在查询语句最后
#limit m,n 表示从第m行开始显示n行数据,第一行的行号是0
SELECT * FROM emp
LIMIT 2,5;

#limit n  表示显示前n行数据
SELECT * FROM emp
LIMIT 3;

2、限定和排序

1)where子句限定范围

#where子句后面必须是一个完整的逻辑表达式,结果只有两种true或者false
#在where子句中,逻辑表达式通常是比较运算,比较条件是否满足

=  >  <  >=  <=  != <>

where子句限定条件通常是用表中的某一列数据作为条件,返回满足条件的数据

查询20号部门的员工信息

SELECT * FROM emp WHERE deptno = 20;

查询工资高于2000的员工信息

SELECT * FROM emp WHERE sal > 2000;

查询职位不是manager的员工信息

SELECT * FROM emp WHERE job != 'MANAGER';

手动输入字符串要加单引号,最好在匹配的时候大小写一致

手动输入字符串要加单引号,最好在匹配的时候大小写一致

查询年薪小于20000的员工信息 列的别名不能出现在where子句中

SELECT ename,job,sal,12*sal nianxin,deptno FROM emp WHERE 12*sal < 20000;

其他的数据库不支持*加上其他列一起查询,mysql允许

SELECT *,12*sal nianxin FROM emp;

where子句中的条件也可以是两列数据相互比较

SELECT * FROM emp WHERE sal < comm;

where子句中作为条件的列可以不出现在select后面,但是这样的语句结果不够直观

SELECT ename,job,deptno FROM emp WHERE sal <= 1500;

where子句中比较运算符的两端数据类型必须一致,如果是其他数据库,数据类型不一致时会直接报错,mysql由于是弱数据类型的语言,所以不会报错,只是这样的语句没有意义

SELECT * FROM emp WHERE ename > 2;

如果没有数据满足条件,则一行数据都没有

SELECT * FROM emp WHERE deptno > 200;

#如果有满足条件的数据,至少会返回一行,只是可能数据的值是空值

SELECT comm FROM emp WHERE ename = 'SMITH';

2)常用的特殊比较运算

in(value1,value,2…valueN), 列表匹配
SELECT * FROM emp WHERE job IN (‘MANAGER’,‘CLERK’,‘PRESIDENT’);

between操作符:
最小值 and 最大值,范围匹配,最大值和最小值的位置不能变
SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

like操作符
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式,模糊匹配(对字符)
通配符,可以匹配或者代表其他字符的特殊符号,SQL 通配符必须与 LIKE 运算符一起使用。

# _ 表示1个任意字符
# % 表示任意个任意字符,包括0个1个和多个

SELECT * FROM emp
WHERE ename LIKE ‘S%’;

SELECT * FROM emp
WHERE ename LIKE ‘%S’;

SELECT * FROM emp
WHERE ename LIKE ‘%A%’;

SELECT * FROM emp
WHERE ename LIKE ‘_A%’;

SELECT * FROM emp
WHERE ename LIKE ‘_____’;

#is null, 匹配空值(不能写成 =null)
SELECT * FROM emp
WHERE comm IS NULL;

3)逻辑运算

and 与运算,所有的条件都要满足才能返回结果
SELECT * FROM emp WHERE deptno = 20 AND sal > 2000;

or 或运算,多个条件满足任意一个就能返回结果
SELECT * FROM emp WHERE deptno = 20 OR sal > 2000;

not 非运算,返回不满足条件的结果
SELECT * FROM emp WHERE NOT sal > 2000;
如果是数学符号的比较运算,not必须写在最前面
如果是英文的比较运算,not可以写在最前也可以写在运算符中间
SELECT * FROM emp
WHERE deptno NOT IN (10,20);

4)运算优先级

数学运算>比较运算>NOT>AND>OR

可以使用括号改变运算优先级

SELECT * FROM emp
WHERE deptno = 20
OR sal > 2000
AND job IN (‘MANAGER’,‘CLERK’);

SELECT * FROM emp
WHERE (deptno = 20
OR sal > 2000)
AND job IN (‘MANAGER’,‘CLERK’);

5)排序ORDER BY子句

order by也在查询语句的后面,但是在limit之前
asc 表示升序排列,默认可以不写
desc 表示降序排列
SELECT * FROM emp ORDER BY sal ASC;

所有的数据类型都可以排序
SELECT * FROM emp WHERE deptno = 20 ORDER BY hiredate DESC;

SELECT * FROM emp ORDER BY ename;

可以使用别名排序
SELECT _,12_sal nianxin FROM emp ORDER BY nianxin DESC;

多列排序
SELECT * FROM emp ORDER BY deptno,sal DESC;
多列排序时,有重复数据的列写在前面

#查询工资排名前三的员工信息
SELECT * FROM emp
ORDER BY sal DESC
LIMIT 3;

3、单行函数

#函数可以实现特定的功能或者运算,给函数输入数据,函数经过处理,得到相应的结果
f1(X)=3x+2
f1(4)=14
f2(X,Y)=5x+4y
f2(3,2)=23

#mysql中系统自带的单行函数,提供了数据特殊处理的方式
#可以协助我们快速对特定数据进行处理
#系统中的函数数量非常多,这里介绍一些常用函数

#单行数据针对表中的每一行数据分别进行处理或运算,得到相应的结果
#单行函数按照处理的数据类型分为:数值函数,字符函数,日期函数,逻辑函数

1)数值函数

#round(m,n) 将数字m四舍五入到小数点后n位
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0);

#n可以不写,默认精确到整数
SELECT ROUND(123.456);

#n可以为负数,精确到小数点前n位
SELECT ROUND(126.456,-1),ROUND(173.456,-2),ROUND(823.456,-3);

#truncate(m,n) 将数字m截取到小数点后n位
SELECT TRUNCATE(123.456,1),TRUNCATE(123.456,2),TRUNCATE(123.456,0);

#truncate必须两个参数都有,不能省略n
#n可以为负数,截取到小数点前n位
SELECT TRUNCATE(126.456,-1),TRUNCATE(173.456,-2),TRUNCATE(823.456,-3);

#floor(m) 向下取整,返回小于等于m的最大整数
#ceil(m) 向上取整,返回大于等于m的最小整数
SELECT FLOOR(23),FLOOR(23.45),FLOOR(-23.45);
SELECT CEIL(23),CEIL(23.45),CEIL(-23.45);

#mod(m,n) 计算m除以n的余数
SELECT MOD(12,5),MOD(123.45,5.7),MOD(12,0);

#rand() 生成一个0到1之间的随机数
SELECT RAND();
#要得到一个1到10之间的随机整数
SELECT ROUND(1+RAND()*9);

2)字符函数

#concat(str1,str2,…,strN) 将多个字符串连接在一起
SELECT CONCAT(‘abcdefg’,‘xyz’);
SELECT CONCAT(ename,job) FROM emp;
SELECT CONCAT(ename,’ is a ',job) FROM emp;

#length(str) 计算字符串的长度
SELECT LENGTH(‘abcdefg’);
SELECT ename,LENGTH(ename) FROM emp;

#函数处理过后的数据也可以写在where子句中作为条件
SELECT * FROM emp
WHERE LENGTH(ename)=4;

#left(str,n) 截取字符串最左边n个字符
#right(str,n) 截取字符串最右边n个字符
SELECT ename,LEFT(ename,2),RIGHT(ename,2) FROM emp;

#mid(str,m,n) 从字符串的第m个字符开始截取长度为n的字符串
SELECT ename,MID(ename,2,3) FROM emp;

#n可以不写,默认截取后面所有的字符
SELECT ename,MID(ename,3) FROM emp;

#m可以为负数,从右向左数第m个字符开始截取
SELECT ename,MID(ename,-2) FROM emp;

#replace(str1,str2,str3) 在str1中找到str2替换为str3
SELECT REPLACE(‘He love you’,‘He’,‘I’);
SELECT ename,REPLACE(ename,‘A’,‘XXXXX’) FROM emp;

3)日期函数

#日期的格式是’YYYY-MM-DD’
#时间的格式是’HH:MM:SS’

#查询1981年6月之前入职的员工信息
SELECT * FROM emp
WHERE hiredate < ‘1981-06-01’;

#curdate() 返回当前系统日期,curtime 返回当前系统时间
SELECT CURDATE(),CURTIME();

#now() 返回当前系统日期+时间
SELECT NOW();

#year(date) 返回日期所在的年份,month(date) 返回日期所在的月份,day(date)返回日期的天数
SELECT *,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp;

#查看冬天入职的员工
SELECT * FROM emp
WHERE MONTH(hiredate) IN (10,11,12);

#DATE_ADD(date,interval n 单位) 给日期加上n个单位时间,单位可以是year,month,day
SELECT DATE_ADD(CURDATE(),INTERVAL -5 MONTH);

4)逻辑函数

#if(条件判断,value1,value2) 如果条件成立则返回value1,如果条件不成立则返回value2
#对员工的工资进行判断,员工工资大于等于3000的显示高富帅,其他显示矮矬穷
SELECT *,IF(sal>=3000,‘高富帅’,‘矮矬穷’) FROM emp;

#对员工的工资进行判断,员工工资大于等于3000的显示高富帅,
#2000到3000之间的显示中产
#1000到2000之间显示小资
#1000以下矮矬穷
SELECT *,IF(sal>=3000,‘高富帅’,IF(sal>=2000,‘中产’,IF(sal>=1000,‘小资’,‘矮矬穷’))) FROM emp;

#ifnull(x,y) 如果x的值为空,则返回y,如果x不为空则返回x
SELECT ,12(sal+IFNULL(comm,0)) nianshouru FROM emp;

/*
case x
when value1 then result1
when value2 then result2

when valueN then resultN
else resultN+1
end

判断x的值,如果x=value1则返回result1
如果x=value2则返回result2
……
如果x=valueN则返回resultN
如果以上值都不满足返回resultN+1
_/
#根据员工不同的职位涨不同幅度的工资
#clerk涨10%,salesman涨15%
#manager涨20%,其他人不变
SELECT _,
CASE job
WHEN ‘CLERK’ THEN sal

_1.1
WHEN ‘SALESMAN’ THEN sal

_1.15
WHEN ‘MANAGER’ THEN sal*1.2
ELSE sal
END addsal
FROM emp;

4、多表查询

1)笛卡尔积

#查询员工的信息和其所在部门的信息
SELECT * FROM emp;
SELECT * FROM dept;

SELECT ename,job,sal,dname,loc FROM emp,dept;

#集合A中的所有元素和集合B中的所有元素,组成的有序对成为笛卡尔积,记作A×B
#在关系型数据库中,多表查询时如果没有连接条件,会产生笛卡尔积
#连接条件就是两张表之间数据的关系,是A表中的某几列和B表中的某几列数据的联系
#连接条件写在where子句中,通常为:表1.列1=表2.列2
#多表查询本质是先生成笛卡尔积,再在笛卡尔积中筛选满足条件的数据
#查询n张表,至少要有n-1个连接条件

2)多表查询的语法规范

#多表查询时,必须使用表名来限定所有列名,表名.列名
#1是为了避免重名的列报错
#2是为了提高查询效率
SELECT emp.ename,emp.job,emp.sal,emp.deptno,dept.dname,dept.loc FROM emp,dept;

#多表查询时,给表起别名,再使用表的别名来限定列名
#表的别名尽量简单,用一个字母能够区分多个表就可以
#多表查询时,先写from,from单独占一行
SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d;

#多表查询时,如果查询结果中有同名的列,起不同的别名加以区分
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d;

3)等值连接

#两张表之间数据的关系是相等关系
#通常是指主键和外键的对应

#查询员工的信息和其所在部门的信息
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

#如果多表查询时还有其他的普通限定条件,也写在where子句中,用and和连接条件连接
#查询工资高于2000的员工信息以及其所在的部门信息
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;

4)不等值连接

SELECT * FROM salgrade;

#查询员工的信息和其所在的工资等级
SELECT e.,s.
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

5)自连接

#如果一张表的数据内部有联系,主键和外键在同一张表
#要将这样的关系体现出来使用自连接
#首先将一张表看作是两张表,再将两张表的主键和外键进行对应
#给同一张表起不同的别名,用不同的别名代表不同的表

#查询emp表中员工的姓名和其上级的姓名
SELECT w.ename,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno;

6)外连接

#在多表查询中,有的数据不满足连接条件,在内连接中是无法显示
#如果要显示这样的数据,使用外连接
#如果要显示左边表中不满足连接条件的数据,使用左外连接:left outer join
#如果要显示右边表中不满足连接条件的数据,使用右外连接:right outer join

#查询emp表中员工的姓名和其上级的姓名,没有上级的员工也显示
SELECT w.ename,m.ename manager
FROM emp w LEFT OUTER JOIN emp m
ON w.mgr = m.empno;

#查询员工信息和部门信息,没有员工的部门也显示
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

#全外连接full outer join,mysql暂时不支持

【练习】
1.查询EMP、DEPT表,输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
SELECT e.ename,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;

2.查询工资等级为3/4/5级的员工姓名,工资,工资等级
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade > 2;

3.显示职位是CLERK的员工姓名,工资,工资等级,部门名称
SELECT e.ename,e.job,e.sal,s.grade,d.dname
FROM emp e,dept d,salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.job = ‘CLERK’;

4.查询emp表,显示员工姓名及其经理的姓名,没有经理的员工也需要显示
SELECT w.ename,m.ename manager
FROM emp w LEFT OUTER JOIN emp m
ON w.mgr = m.empno;

5.列出EMP表中部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SELECT d.dname,e.*
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

5、多行函数

1)多行函数

#针对多行数据进行运算,只得到一个结果,就是多行函数
#又称为分组函数,或聚合函数

#sum() 求和
SELECT SUM(sal) FROM emp;

#avg() 求平均值
SELECT AVG(sal) FROM emp;

#sum和avg都只能对数字进行运算

#count() 计数(行数)
SELECT COUNT(ename) FROM emp;
SELECT COUNT(*) FROM emp;

#max() 最大值,min() 最小值
SELECT MAX(sal),MIN(sal) FROM emp;
SELECT MAX(hiredate),MIN(hiredate) FROM emp;
SELECT MAX(ename),MIN(ename) FROM emp;

#分组函数在计算时会忽略空值
SELECT AVG(comm),COUNT(comm) FROM emp;
SELECT SUM(comm)/14,SUM(comm)/4 FROM emp;

#如果要考虑空值,使用ifnull转换
SELECT AVG(IFNULL(comm,0)) FROM emp;

#可以使用where子句去限定分组函数计算的范围
#找出职位是salesman的员工的最高工资
SELECT MAX(sal) FROM emp
WHERE job = ‘SALESMAN’;

#找出10号部门的平均工资
SELECT AVG(sal) FROM emp
WHERE deptno = 10;

#oracle中普通列和分组函数是不能直接一起查询的
#mysql虽然允许这样的语句执行,但是结果没有意义
#select ename,count(sal) from emp;

2)group BY子句

#查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno;

#select后面既有普通列又有分组函数时,必须写group by子句
#并且出现在select后面的普通列必须也在group by后面

#查询每个职位的人数
SELECT job,COUNT(*) FROM emp
GROUP BY job;

#可以多列分组
#查询每个部门各个职位的最低工资
SELECT deptno,job,MIN(sal) FROM emp
GROUP BY deptno,job;

3)having子句

#查询部门人数多于5人的部门编号
/*
select deptno,count(_) from emp
where count(
_) > 5
group by deptno;

where子句中不能出现分组函数
_/
#要对分组函数的结果进行限定,使用having子句
#having必须在group by后面
#使用group by可以不用having,但是使用having必须使用group by
SELECT deptno,COUNT(

_) FROM emp
GROUP BY deptno
HAVING COUNT(*) > 5;

6、子查询

#查询工资高于allen的员工信息
SELECT sal FROM emp
WHERE ename = ‘ALLEN’;

SELECT * FROM emp
WHERE sal > 1600;

#当查询语句的条件不是客观数据,而是表中的数据时,使用子查询
#子查询又叫做嵌套查询,将一个查询的结果作为另一个查询的条件
#子查询写在主查询中,必须使用括号表示运算顺序
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename = ‘ALLEN’) ;

#习惯上,将子查询写在比较运算的右边,而且子查询单独占一行
#主查询中作为条件的列,必须和子查询的结果数据类型一致

1)单行子查询

#子查询的结果如果是一行一列数据,就是单行子查询

#查询跟allen的部门和职位都一样的员工信息
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = ‘ALLEN’)
AND job =
(SELECT job FROM emp WHERE ename = ‘ALLEN’);

#主查询和子查询的数据可以不在同一张表
#查询allen所在的部门名称
1.多表查询
SELECT d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename = ‘ALLEN’;
#多表查询先生成笛卡尔积,再在笛卡尔积中找满足条件的数据
#一共找了14*4=56次

2.子查询
SELECT dname FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = ‘ALLEN’);
#先执行子查询,找了14次,再在主查询中查询,找了4次
#一共找了14+4=18次

#子查询的效率高于多表查询
#如果查询结果来自多张表,只能使用多表查询
#如果查询结果来自一张表,只是用到另一张表的数据作为条件
#可以用子查询也可以用多表查询,推荐使用子查询

#子查询中可以使用分组函数
#查询emp表中工资最高的员工姓名
SELECT ename FROM emp
WHERE sal =
(SELECT MAX(sal) FROM emp);

#having子句中可以使用子查询
#查询部门的平均工资高于所有员工平均工资的部门
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) >
(SELECT AVG(sal) FROM emp);

2)多行子查询

#子查询的结果如果是多行一列数据,就是多行子查询
#多行子查询必须使用多行比较运算符

#in 匹配多个值
#查询工资大于等于3000的员工所在的部门名称
SELECT dname FROM dept
WHERE deptno IN
(SELECT DISTINCT deptno FROM emp WHERE sal >= 3000);

#any,多个条件满足其中任意一个就可以返回结果
#查询工资高于任意一个部门的平均工资的员工信息
SELECT * FROM emp
WHERE sal > ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);
#大于any表示大于最小值,小于any表示小于最大值

#all,多个条件必须全部满足才能返回结果
SELECT * FROM emp
WHERE sal > ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno);
#大于all表示大于最大值,小于all表示小于最小值

3)多列子查询

#子查询的结果如果是多行多列数据,就是多列子查询
#in 匹配多列的值

#查询跟allen的部门和职位都一样的员工信息
SELECT * FROM emp
WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE ename = ‘ALLEN’);
#主查询中列的数量、顺序以及数据类型必须和子查询中的列一致

#子查询的结果可以看作是一张表,再进行查询
#此时子查询写在from后面,必须起别名
SELECT * FROM
(SELECT ename,job,sal,comm,12*sal nianxin,sal+IFNULL(comm,0) yongjin FROM emp) e
WHERE yongjin > 2000;

#先用一个查询语句构造出一张表,再和其他表做多表查询

7、集合操作

#集合操作是指,将查询结果看作是一个集合,集合与集合之间可以做集合操作
#集合操作:合集、交集、差集、补集
#mysql中只支持合集操作,union,union all
#集合操作的效率高于条件组合的查询效率
#进行集合操作的查询语句,查询的列必须一致

SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;

#union all会显示重复数据
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION ALL
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;

数据控制语句(DCL)

DCL(Data Control Language)是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

事物控制语句(TCL)

执行的dml语句和ddl语句都是以事务的形式存在的,事务必须提交才能永久生效,可以回滚撤销操作,mysql默认自动提交是开启的,关闭自动提交:
SET autocommit=0;

COMMIT; #提交
ROLLBACK; #回滚

DDL语句都是自动提交的,并且不能关闭

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
8个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这