#进阶1:基础查询
/*
语法:
SELECT 查询列表 FROM 表名;
特点:
1、查询列表可以是:表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格
*/
USE study;#使用哪个数据库
DESC yuangong; #查看表结构
#1.查询表中的单个字段
SELECT yuangongname FROM yuangong;
#2.查询表中的多个字段
SELECT yuangongname,word FROM yuangong;
#3.查询表中所有的字段
SELECT * FROM yuangong;
#4.查询常量值
SELECT 5000;
SELECT '小王';
#5.查询表达式
SELECT 100%98;
#6.查询函数(SELECT 后可以使用函数)
SELECT VERSION();
#7.起别名
/*
①便于理解
②如果要查询的字段有重名的情况,使用别名可以区分开
注意:在别名中出现特殊字符时,别名使用' '或者" "引起来
*/
#方式一
SELECT 100%98 AS 结果;
#方式二
SELECT 100%98 结果;
#8.去重 DISTINCT
SELECT DISTINCT wage FROM yuangong;
#9.+号的作用
/*
只是一个运算符的作用
拼接的时候使用 concat() 函数
*/
SELECT CONCAT(yuangongname,wage) FROM yuangong;
/*补充:IFNULL(需要判断的字段,0)
在拼接过程中某些字段可能为null,拼接后所有结果都为null,
使用ifnull()函数,使null转为0,就可以拼接
*/
#进阶2:条件查询
/*
语法:
SELECT
查询列表(第三步)
FROM
表名(第一步)
WHERE
筛选条件(第二步)
分类:
一、按条件表达式筛选
条件运算符: > < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:&& || !
AND OR NOT
作用:用于连接条件表达式
三、模糊查询
LIKE
特点:
一般和通配符搭配使用
通配符:
% 任意多个字符,包含0个字符
_ 任意单个字符
BETWEEN AND
使用BETWEEN AND 可以提高语言的简洁度
包含临界值(是个闭区间)
两个临界值不能调换位置(等价id>=2 AND id<=7)
IN
含义:判断某字段的值是否属于in列表中的某一项
特点:
使用in提高语言简洁度
IN列表的值类型必须一致或兼容
不支持使用通配符使用
IS NULL
=或者!= 不能用于判断null值
IS NULL 或者 IS NOT NULL 可以判断null值
*/
#一、按条件表达式筛选
SELECT * FROM yuangong WHERE wage>=5000;
#二、按逻辑表达式筛选
SELECT * FROM yuangong WHERE wage>=5000 AND wage<=20000;
SELECT * FROM yuangong WHERE NOT(wage>=5000 and wage<=20000) OR id>=3;
#三、模糊查询
#1.LIKE
SELECT * FROM yuangong WHERE yuangongname LIKE '%小%';
SELECT * FROM yuangong WHERE yuangongname LIKE '_$_%' ESCAPE '$';# ESCAPE关键字表示:转义,使$为转义字符,或者直接使用'\'和java一样的转义字符
#2.BETWEEN AND
SELECT * FROM yuangong WHERE id>=2 AND id<=7;
SELECT * FROM yuangong WHERE id BETWEEN 2 AND 7;
#3.IN
SELECT * FROM yuangong WHERE bumenTableId=1 OR bumenTableId=2 OR bumenTableId=4;
SELECT * FROM yuangong WHERE bumenTableId IN(1,2,4);
#4.IS NULL
SELECT * FROM yuangong WHERE bonus IS NULL;
SELECT * FROM yuangong WHERE bonus IS NOT NULL;
#安全等于
SELECT * FROM yuangong WHERE bonus <=> NULL;
/*
IS NULL:仅仅可以判断null值;可读性较高
<=> :既可以判断null值,又可以判断普通的数值;可读性较低
*/
#进阶3:排序查询
/*
语法:
SELECT
查询列表 (第三步)
FROM
表 (第一步)
【WHERE
筛选条件】 (第二步)
ORDER BY
排序列表 【ASC|DESC】 (第四步)
特点:
1.ASC代表的是升序,DESC代表的是降序,如果不写,默认是升序
2.ORDER BY 子句中可以支持单个字段、多个字段、表达式、函数、别名
3.ORDER BY 子句一般是放在查询语句的最后面,LIMIT 子句除外
*/
SELECT * FROM yuangong ORDER BY id DESC;
SELECT * FROM yuangong WHERE wage >= 5000 ORDER BY id DESC;
#按表达式
SELECT * FROM yuangong WHERE wage >= 5000 ORDER BY id-1 ASC;
#按别名
SELECT *,id+1 AS 'id+1' FROM yuangong WHERE wage >= 5000 ORDER BY 'id+1' ASC;
#按函数排序
SELECT LENGTH(word) 字节长度,id FROM yuangong ORDER BY LENGTH(word) DESC;
#按多个字段排序
SELECT * FROM yuangong ORDER BY wage ASC,id DESC;
#进阶4:常见函数
/*
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节
2、提高代码的重用性
调用:SELECT 函数名(实参列表) 【FROM 表】;
特点:
①函数名
②函数功能
分类:
1、单行函数
如 CONCAT(str1,str2,...)、LENGTH(str)、IFNULL(expr1,expr2)等
2、分组函数
功能:做统计使用,又称统计函数、聚合函数、组函数
*/
#单行函数
#一、字符函数
#1.LENGTH() 获取参数值的字节个数
SELECT LENGTH('HLY');
SELECT LENGTH('HLY最美');
#2.CONCAT(str1,str2,...) 拼接字符串
SELECT id, CONCAT(id,'_',bumenTableId) id_bumentaleId FROM yuangong;
#3.UPPER(str) 将字符大写,LOWER(str) 将字符小写
SELECT UPPER('hly');
SELECT LOWER('HLY');
#4.SUBSTR(),SUBSTRING()截取字符 索引从 1 开始
#截取从指定索引处后面的字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) 结果;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) 结果;
#5.INSERT 返回字串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS 结果;
#6.TRIM 去掉 头 尾 的特定字符
SELECT LENGTH ( TRIM(' 张翠山 ')) AS 结果;
SELECT TRim('1' FROM '111111张111111翠1111111山11111111') AS 结果;
#7.LPAD(str,len,padstr) 用指定的字符实现左填充指定长度
SELECT LPAD( '殷素素',10,'#') AS 结果;
#7.RPAD(str,len,padstr) 用指定的字符实现右填充指定长度
SELECT LPAD( '殷素素',10,'#') AS 结果;
#二、数学函数
#1.ROUND(X) 四舍五入
SELECT ROUND(11.2);
SELECT ROUND(-11.2);
#2.CEIL(X) 向上取整
SELECT CEIL(1.2);
#3.FLOOR(X) 向下取整
SELECT FLOOR(1.2);
#4.TRUNCATE(X,D) 截断(小数点后保留几位)
SELECT TRUNCATE(1.23456789,1);
#5.MOD(N,M) 取余 N-N/M*M 结果与被除数有关
SELECT mod(10,-3);
#三、日期函数
#1.NOW() 返回当前系统的日期+时间
SELECT NOW();
#2.CURDATE() 返回当前系统日期,不包含时间
SELECT CURDATE();
#3.CURTIME() 返回当前时间,不包含日期
SELECT CURTIME();
#4.可以获取指定的部分,年、月、日、时、分、秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
#5.STR_TO_DATE(str,format) 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1997,10,12','%Y,%c,%d');
#6.DATE_FORMAT(date,format) 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日');
#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五、流程控制函数
#1.IF(expr1,expr2,expr3) if else 的效果
SELECT if(10>5,'大','小') as 结果;
/*2.CASE case_value (要判断的字段或者表达式)
WHEN when_value(常量) THEN
statement_list(要显示的值或者语句)
...
ELSE
statement_list(默认要显示的值或者语句)
END CASE;
语法:
情况1:类似于switch
case 变量或表达式
when 值1 then 语句1;
when 值2 then 语句2;
...
else 语句n;
end
情况2:
case
when 条件1 then 语句1;
when 条件2 then 语句2;
...
else 语句n;
end
应用在begin end 中或外面
*/
SELECT wage,
CASE
WHEN wage>5000 THEN
'a'
ELSE
'b'
END FROM yuangong;
#********************************************************************************
#二、分组函数
/*
功能:做统计使用,又称统计函数、聚合函数、组函数
分类:
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
特点:
1.sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2.以上分组函数都忽略null值
3.可以和distinct搭配实现去重运算
4.count 函数单独介绍
使用count(*)做统计行数
5.和分组函数一同查询的字段有限制;要求是group by后的字段
*/
#1.简单使用
SELECT SUM(id) FROM yuangong;
SELECT AVG(id) FROM yuangong;
SELECT MAX(id) FROM yuangong;
SELECT MIN(id) FROM yuangong;
SELECT COUNT(id) FROM yuangong;
SELECT SUM(id) ,AVG(id) , MAX(id), MIN(id), COUNT(id) FROM yuangong;
#2.参数类型支持哪些类型
/*sum avg 只适合数值型
max min 都适合
count 任何类型
*/
#3.是否忽略null
/*null+任何值=null
平均值只计算非null,null不参与计算
*/
#4.和distinct搭配
SELECT SUM(DISTINCT wage) ,SUM(wage) FROM yuangong;
#5.count 函数的详细介绍
SELECT COUNT(wage) FROM yuangong;
SELECT COUNT(*) FROM yuangong;
SELECT COUNT(1) FROM yuangong;
#效率:
/*MYISAM存储引擎下,count(*)的效率高
INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些
*/
#6.和分组函数一同查询的字段有限制
SELECT id ,COUNT(id) FROM yuangong;#id 列本来显示12行但是只显示一行
#进阶5:分组查询
/*
语法:
SELECT 分组函数,列(要求出现在group by 的后面 )
FROM 表
【WHERE 筛选条件】
GROUP BY 分组的列表
【HAVING 筛选条件】
【ORDER BY 子句】
注意:
查询列表必须特殊,要求是分组函数和group by 后出现的字段
特点:1、分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 GROUP BY 子句前面 WHERE
分组后筛选 分组后的结果集 GROUP BY 子句后面 HAVING
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的,就优先考虑使用分组前筛选
2、GROUP BY字句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或者函数(用的较少)
3、也可以添加排序(排序放在整个分组查询的最后)
*/
#案例一
SELECT MAX(wage),word FROM yuangong GROUP BY word;
#案例二
SELECT yuangongname, avg(wage),word FROM yuangong WHERE yuangongname LIKE '%小%' GROUP BY word HAVING AVG(wage)>10000;
#进阶6:链接查询
/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到链接查询
分类:
按年代分类:
sql192标准:仅仅支持内连接
sql199标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
按功能分类
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
*/
#sql192
#1.等值连接
/*
1.多表等值连接的结果为多表的交集部分
2.n表连接,至少需要n-1个连接条件
3.多表顺序没有要求
4.一般需要为表起别名
5.可以搭配前面介绍的所有子句使用,比如排序、分组、筛选...
*/
SELECT yuangongname,yuangong.word,bumenTablename FROM yuangong,bumentable WHERE yuangong.bumenTableId=bumentable.bumenTableid;
#2.非等值连接
/*
和等值连接相反,条件为(!=,>=,<=)
*/
#3.自连接
/*
在同一张表中查询(需要给表起别名,容易区分)
*/
#sql199
/*
语法:
SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名
ON 连接条件
【WHERE 筛选条件】
【GROUP BY 分组】
【HAVING 筛选条件】
【ORDER BY 排序列表】
连接类型分类:
内连接:INNER
外连接:
左外:LEFT 【OUTER】
右外:RIGHT 【OUTER】
全外:FULL 【OUTER】
交叉连接:CROSS
*/
#一、内连接
/*
语法:
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件;
分类:
等值
非等值
自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
④inner join连接和sq192语法中的等值连接效果是一样的,都是查询多表的交集
*/
#二、外连接
/*
应用场景:用于查询一个表中有,另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接,LEFT JOIN 左边的是主表
右外连接,RIGHT JOIN 右边的是主表
3、左外和右外交换两个表的顺序,可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1没有的
*/
#三、交叉连接
/*
语法:
SELECT 查询列表
FROM 表1 别名
CROSS JOIN 表2 别名;
*/
#进阶7:子查询
/*
含义:
出现在其他语句内部的select语句,称为子查询或内查询
内部嵌套其他select语句的查询,成为外查询或主查询
分类:
按子查询出现的位置:
SELECT后面:
仅仅支持标量子查询
FROM后面:
支持表子查询
WHERE 或者 HAVING后面:
标量子查询(单行)
列子查询(多行)
行子查询
EXISTS后面:
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
*/
#一、WHERE 或者 HAVING 后面
/*
1.标量子查询(结果集只有一行一列)
2.列子查询(结果集只有一列多行)
3.行子查询(结果集有一行多列)
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询,一般搭配着单行操作符使用:
> < >= <= = <>
列子查询,一般搭配着多行操作符使用
IN/NOT IN、 ANY/SOME、 ALL
④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
*/
#二、SELECT后面
/*
仅仅支持标量子查询(一行一列)
*/
#三、FROM后面
/*
将子查询结果充当一张表,要求必须起别名
*/
#四、EXISTS后面(相关子查询)
/*
先执行外查询后执行内查询
语法:
EXISTS(完整的查询语句)
结果:1 或者 0
1表示:存在值
0表示:不存在值
*/
#DML语句
/*
数据操作语言:
插入:INSERT
修改:UPDATE
删除:DELETE
*/
#一、插入语句
#经典插入方式一:
/*
语法:
INSERT INTO 表明(列名, ...) VALUES (值1, ...);
*/
#1.插入的值的类型要与列的类型一致或兼容
#2.不可以为null的列必须插入值,
#可以为null的值插入值方式:
#方式一:值为NULL
#方式二:列名就不写
#3.列的顺序是可以调换的,但值要对应列名
#4.列数和值的个数必须一致
#5.可以省略列名,默认所有列,而且列的顺序和表中的列的顺序一致
#方式二:
/*
语法:
INSERT INTO 表名
SET 列名=值,列名=值,...
*/
/*
方式一支持多行插入,方式二只能单行插入
方式一支持子查询,方式二不支持
*/
#二、修改语句
/*
1.修改单表的记录
语法:
UPDATE 表名
SET 列=新值,列表=新值,...
WHERE 筛选条件;
2.修改多表的记录
语法:
sql192:
UPDATE 表1 别名,表2 别名
SET 列=新值,...
WHERE 链接条件
AND 筛选条件;
sql199:
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN表2 别名
ON 连接条件
SET 列=新值,...
WHERE 筛选条件;
*/
#三、删除语句
/*
方式一:DELETE
语法:
1.单表删除
DELETE FROM 表名 WHERE 筛选条件
2.多表删除
sql192:
DELETE 表1的别名,表2的别名
FROM 表1 别名,表2 别名
WHERE 链接条件
AND 筛选条件;
sql199:
DELETE 表1的别名,表2的别名
INNER|LEFT|RIGHT JOIN表2 别名
ON 连接条件
WHERE 筛选条件;
方式二:TRUNCATE (删除整个表的信息)
语法:
TRUNCATE TABLE 表名;
*/
#DDL
/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除
创建:CREATE
修改:ALTER
删除:DROP
*/
#一、库的管理
#1.库的创建
#CREATE DATABASE [IF NOT EXISTS](重复判断) 库名;
#2.库的修改
#1.ALTER DATABASE 库名 CHARACTER SET 编码格式;
#3.库的删除
# DROP DATABASE IF EXISTS 库名;
#二、表的管理
#1.表的创建
/*
CREATE TABLE 表名(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
...
列名 列的类型 【(长度) 约束】
)
*/
#2.表的修改
/*
ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名 【列的类型 约束】;
*/
#①修改列名
#ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 列的类型;
#②修改列的类型或约束
#ALTER TABLE 表名 MODIFY COLUMN 列名 列的类型;
#③添加新列
#ALTER TABLE 表名 ADD COLUMN 列名 列的类型;
#④删除列
#ALTER TABLE 表名 DROP COLUMN 列名;
#⑤修改表名
#ALTER TABLE 表名 RENAME TO 新表名;
#3.表的删除
# DROP TABLE IF EXISTS 表名;
#4.表的复制
/*
1.仅仅复制表的结构
CREATE TABLE 新表名 LINK 被复制的表名;
2.复制表的结构外加数据
CREATE TABLE 新表名 SELECT * FROM 被复制的表名;
3.只复制部分数据
CREATE TABLE 新表名 SELECT * FROM 被复制的表名 WHERE 筛选条件;
4.仅仅只复制部分结构
CREATE TABLE 新表名 SELECT 字段1 字段2 FROM 被复制的表名 WHERE 0;
*/
#常见的数据类型
/*
数值型:
整形
小数:
定点数
浮点数
字符型:
较短的文本:CHAR、VARCHAR
较长的文本:TEXT、BLOB(较长的二进制数据)
日期型:
*/
#原则
/*
算选择的类型越简单越好,能保存数值的类型越小越好
*/
#一、整型
/*
分类:
TINYINT、SMALLINT、MEDIUMINT、int/INTEGER、BIGINT
1 2 3 4 8 (字节)
特点:
①如果不设置无符号还是有符号,默认是有符号,如果想设置无符号,需要添加unsigned关键字
②如果添加的数值超过了整形的范围,会报out of range异常,并且插入临界值
③如果不设置长度会有默认长度
④长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
*/
#二、小数
/*
分类:
1.浮点型
FLOAT(M,D)
DOUBLE(M,D)
2.定点型
dec(M,D)
DECIMAL(M,D)
特点:
①M和D:
M:整数部位+小数部位
D:小数部位
如果超过范围,则插入临界值
②M和D都可以省略,如果是decimal,则M默认为10,D默认为0;如果是float和double,则会根据插入的数值的精度来决定精度
③定点型的精度较高,如果要求插入数值的精度较高如货币运算等则考虑使用
*/
#三、字符型
/*
1.较短的文本:
CHAR
VARCHAR
2.较长的文本:
TEXT
BLOB(较长的二进制数据)
特点:
写法 M的意思 特点 空间的消耗 效率
char char(M) 最大的字符数,可以省略,默认为1 固定长度的字符 比较消耗 高
varchar varchar(M) 最大的字符数,不可以胜略 可变长度的字符 比较节省 低
3.其他:
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
*/
#四、日期型
/*
分类:
date只保存日期
time只保存时间
year只保存年
datetime只保存日期+时间
timestamp只保存日期+时间
特点:
字节 范围 时区等的影响
datetime 8 1000-9999 不受
timestamp 4 1970-2038 收
*/
#常见约束
/*
含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确性和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
DEFAULT:默认,用于保证该字段有默认值
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
CHECK:检查约束【mysql中不支持】
FOREIGN KEY:(REFERENCES)外键,用于限制两个表的关系,用于保证该字段的值的值必须来自于主表额关关联列的值
在从表添加外键约束,用于医用主表中某列的值
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
1.列级约束:
六大约束语法上都支持,但外键约束没有效果
2.表级约束:
除了非空、默认,其他的都支持
*/
#1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可
支支持:默认、非空、主键、唯一
*/
#2.添加表级约束
/*
语法:在各个字段的最下面
【CONSISTENT 约束名】 约束类型(字段名)
*/
/*
外键:
1、要求在从表中设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或者兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或者唯一)
4、插入数据时,先插入主表在插入从表;删除数据时,先删除从表,在删除主表
*/
#二、修改表时添加约束
/*
1、添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
2、添加表级约束
ALTER TABLE 表名 ADD 【CONSISTENT 约束名】 约束类型(字段名) 【外键的引用】
*/
#三、修改表时删除约束
/*
*/
#标识列
/*
又称自增长列
含义:可以不用手动的插入值,系统提供默认的序列值
特点:
1.标识列必须和主键搭配吗?不一定,但是要求是一个key
2.一个表至多只有一个标识列
3.标识列的类型:只能是数值型
4.标识列可以通过 SET aut_increment_increment=3;设置步长为3;
可以通过手动插入值,设置起始值
*/
#一、创建表时设置标识列
#auto_increment 属性设置
#二、修改表时设置标识列
ALTER TABLE 表名 MODIFY COLUMN 字段 类型 约束 auto_increment;
#三、修改表时删除标识列
ALTER TABLE 表名 MODIFY COLUMN 字段 类型 ;