数据库【DML（数据操纵语言）】

目录

1 增加语句

 2 删除语句和TRUNCATE

3 更新语句

4 replace语句

5 select查询语句

5.1 简单的SQL查询

5.2 等值判断

5.3 不等判断

5.4 逻辑运算符

 5.5 查询时的别名使用

 5.6 常见的条件查询

 5.7 分组

 5.8 分组后的筛选

5.9 结果排序

5. 10 分页查询

5.11 分表查询

5.12 外键和多表关联

6 SQL中函数

6.1 聚合函数

6.2 数值型函数

6.3 字符型函数 

6.4 日期和时间函数

6.5  流程控制函数 

---

1 增加语句

增加语言，就是给某张表进行数据插入

语法：

insert INTO table_name[(field1 [, field2 ……]) values(value1 [, vaule2 ……])  [,()]];

# 查看表 tb_user 的结构
desc tb_user;

# 向表 tb_user 中插入一条记录
INSERT INTO tb_user (id, name, age) VALUES (NULL, "gxc", 19);

# 向表 tb_user 中插入多条记录
INSERT INTO tb_user (name, age) VALUES ( "赵六", 21),("李四",25);

# 查询表 tb_user 中的所有记录
select * from tb_user;

 2 删除语句和TRUNCATE

删除语言，请注意删除的条件！！！如果不带条件，则删除全表。

语法结构：

delete from table_name [where 条件];
truncate table table_name;

delete from tb_user where id=10;

-- 如果没有条件，则清空全表数据【慎重！！】
delete from user;


-- truncate清空全表[注意：truncate删除数据是不经过数据字典]
truncate table students;

3 更新语句

更新就是修改表中的数据

语法结构：

update table_name set 字段1=新值 [, 字段2=新值, 字段3=字段3 + 1]   [where 条件];

update tb_user set age=90 where id=1;
update tb_user set age=age+5 where id=4;

4 replace语句

该语句是集更新和插入为一体的一个语句

如果表中没有这条数据，则执行插入，否则执行更新

注意：replace的更新，本质是先删除，再插入

 replace into tb_user(id, name) values(100, "test");
replace into tb_user(id, name) values(100, "test--test");

5 select查询语句

在开发中，查询语句是使用最多，也是CRUD中，复杂度最高的sql语句

查询的语法结构：

select *|字段1 [, 字段2 ……] from 表名称 [, 表名称2……] [where 条件] [group by 字段 [having 分组后的筛选]] [order by 字段 [desc|asc] [字段2 [desc|asc] ……]] [limit 分页]

5.1 简单的SQL查询

-- 查询所有数据
select * from tb_user;

-- 查询需要的字段信息
select id, name from tb_user;
-- 查询一个字段，一个等值条件
select name from tb_user where id = 7;

select 字段列表
from 表名称
where 条件

select语句中的特殊情况：

对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）
对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）
运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。
	SELECT last_name, salary, salary*12
	FROM employees;
	
补充：+ 说明
-- MySQL的+默认只有一个功能：运算符
SELECT 100+80; # 结果为180
SELECT '123'+80; # 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203
SELECT 'abc'+80; # 转换不成功，则字符型数值为0，结果为80
SELECT 'This'+'is'; # 转换不成功，结果为0
SELECT 'This'+'30is'; # ？猜测下这个结果是多少？
SELECT NULL+80; # 只要其中一个为NULL，则结果为NULL

5.2 等值判断

条件中，出现了相等值的判断，一般采用=进行判断。

• = 判断两次的值是否相等

• is 判断空null

• is not null来判断不为空

• <=> 可以判断null或者普通值

5.3 不等判断

• != 不等于

• <>也是不等于

5.4 逻辑运算符

逻辑运算符是多条件关联的一种方式。

与或非

• and

• or

• not

注意：在sql中，如果要提升条件的运行顺序，或者提高条件的优先级别，则需要使用括号来提升

 5.5 查询时的别名使用

查询时，将结果的显示字段，使用一个其他名称来代替，就是别名

 5.6 常见的条件查询

使用WHERE子句限定返回的记录
WHERE子句在FROM 子句后
	SELECT[DISTINCT] {*, column [alias], ...}
	FROM table–[WHEREcondition(s)];
	
WHERE中的字符串和日期值
字符串和日期要用单引号扩起来
字符串是大小写敏感的,日期值是格式敏感的
    SELECT last_name, job_id, department_id
    FROM employees
    WHERE last_name = "king";
    
    
WHERE中比较运算符:
    SELECT last_name, salary, commission_pct
    FROM employees
    WHERE salary<=1500;    
    
其他比较运算符
使用BETWEEN运算符显示某一值域范围的记录
    SELECTlast_name, salary
    FROM employees
    WHERE salary BETWEEN 1000 AND 1500; 
    
使用IN运算符
使用IN运算符获得匹配列表值的记录
    SELECTemployee_id, last_name, salary, manager_id
    FROM employees
    WHERE manager_id IN (7902, 7566, 7788);
    
使用LIKE运算符
  使用LIKE运算符执行模糊查询
  查询条件可包含文字字符或数字
  (%) 可表示零或多个字符
  ( _ ) 可表示一个字符
    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_A%';
    
使用IS NULL运算符
  查询包含空值的记录
      SELECT last_name, manager_id
      FROM employees
      WHERE manager_id IS NULL;
  
  
  逻辑运算符
  使用AND运算符
    AND需要所有条件都是满足T.
    
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100–4 AND job_id='CLERK';
  使用OR运算符
    OR只要两个条件满足一个就可以
  
    SELECT employee_id, last_name, job_id, salary
    FROM employees
    WHERE salary>=1100 OR job_id='CLERK';
  使用NOT运算符
    NOT是取反的意思
    	SELECT last_name, job_id
        FROM employees
        WHERE job_id NOT IN ('CLERK','MANAGER','ANALYST');
        
        
  使用正则表达式：REGEXP
  	<列名> regexp '正则表达式'
  	select * from product where product_name regexp '^2018';

 5.7 分组

sql中，分组是一种统计概念；查询的数据，进行数据分析时，可能需要将相同的数据分成一组

结构：

group by 字段

注意：分组查询时，查询字段必须是分组的字段，或者是聚合函数

 

 5.8 分组后的筛选

如果直接在分组后的结果集上进行条件判断，将条件写在where中，会报错，因为：结果集是分组后才能做的判断，而where实在查询前的条件判断。

所以不能使用where，必须使用having。

再次强调，having必须写在group by之后！！！没有分组，就不能写having，但是分组可以没有having

5.9 结果排序

将查询结果，以特定的顺序展示（升序或者降序）

语法结构：

# 语法结构
order by 字段 
order by 字段 asc|desc;
order by 字段 asc|desc, 字段2 ;

5. 10 分页查询

select语句，查询数据时，可能结果会非常多，此时就不能直接展示，分页展示。

总数量（all_data）：查询 select count(*)

每页展示的数量(page_size)：程序员定

当前页(cur_page)：默认第一页，用户自己点击选择

总页数(all_page)：总数量 % 每页的数量 == 0 整除后的商 ： 商 + 1

limit num  # 查询多少条

limit num1, num2; # num1: 偏移量, num2 : 每页的数量  

limit cur_page * (page_size - 1), page_size;

5.11 分表查询

数据直接都存储在一张表中：

• 如果数据很大，性能会出现问题

• 将不同的数据，如果放在同一个表中，可能数据冗余

• 数据冗余，会导致数据可能出错

将不同的类型，采用不同的数据表进行存储，如果两张表或者多张表之间存在关联关系，则可以采用外键来描述这种关联关系。

主表中，一般是一个字段，改字段一般是从表的主键。

 

5.12 外键和多表关联

外键：指的是两张或者多张表之间关联关系的字段。

外键约束：是表的约束，是约束表在插入外键数据时能够正确的插入。

如何添加约束：

# 在创建表的同时，将外键约束添加上去
# 首先保证班级表创建成功
# 插入正确的数据
create table grade(
	id int auto_increment,
    name varchar(50) unique,
    primary key(id)
)
insert into grade(name) value("Java精品班"), ("python数据分析班"), ("网络安全班"), ("云原生高级班");
外键约束
外键是构建于一个表的两个字段或者两个表的两个字段之间的关系
外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
	
格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

多表关联查询：

当两张或者多张表之间存在了关联关系，往往多表查询，如果查询。

• 交叉连接

• 内连接

• 外链接

  • 左外连接

  • 右外连接

• 自连接

• 全连接

• 自然连接 （不建议使用）

6 SQL中函数

SQL中默认提供很多系统内置函数，帮助开发者实现各种需要的功能

6.1 聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

6.2 数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求平方根
POW 和 POWER	两个函数的功能相同，返回参数的幂次方
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号

6.3 字符型函数 

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CHAR_LENGTH	计算字符串长度函数，返回字符串的字节长度，注意两者的区别
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT(str,pos,len,newstr)	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT(str,len)	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE(s，s1，s2)	字符串替换函数，返回替换后的新字符串
SUBSTRING(s，n，len)	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串
STRCMP(expr1,expr2)	比较两个表达式的顺序。若expr1 小于 expr2 ，则返回 -1，0相等，1则相反
LOCATE(substr,str [,pos])	返回第一次出现子串的位置
INSTR(str,substr)	返回第一次出现子串的位置

6.4 日期和时间函数

函数名称	作用
CURDATE() CURRENT_DATE() CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME() CURRENT_TIME() CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW	返回当前系统的日期和时间值
SYSDATE	返回当前系统的日期和时间值
DATE	获取指定日期时间的日期部分
TIME	获取指定日期时间的时间部分
MONTH	获取指定日期中的月份
MONTHNAME	获取指定曰期对应的月份的英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
YEAR	获取年份，返回值范围是 1970〜2069
DAYOFWEEK	获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1 〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH 和 DAY	两个函数作用相同，获取指定日期是一个月中是第几天，返回值范围是1~31
DATEDIFF(expr1,expr2)	返回两个日期之间的相差天数，如 SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
TIME_TO_SEC	将时间参数转换为秒数，是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

6.5  流程控制函数 

函数名称	作用
IF(expr,v1,v2)	判断，流程控制，当expr = true时返回 v1，当expr = false、null 、 0时返回v2
IFNULL(v1,v2)	判断是否为空，如果 v1 不为 NULL，则 IFNULL 函数返回 v1，否则返回 v2
CASE	搜索语句