数据库基本操作概述-增删改查

增删改查

字符串查询

• 多个字符串拼接
  • select CONCAT(‘My’,‘S’,‘QL’); 输出MySQL
  • select CONCAT(列名，列名)；两个列合并 +AS ‘自定义列名’ 可以自己设置这个列的名称
• 字符串替换
  • select INSERT(旧字符串，起始位置，替换字符个数，新字符)
    • 这里的起始位置的索引是从1开始而不是和java一样的从0开始
• 字符串转小写
  • select LOWER(字符串)；
• 字符串转大写
  • select UPPER(字符串)；
• 截取字符串
  • select SUBSTRING(字符串，起始位置，截取个数)；
    • 起始下标从1开始

聚合函数

• 对多条数据单列进行统计，返回统计后的一行结果；
• 求和
  • select SUM(列名) from 表名；
• 平均值
  • select AVG(列名) from 表名；
• 最大值
  • select MAX(列名) from 表名；
• 最小值
  • select MIN(列名) from 表名；
• 总行数
  • select COUNT(列名) from 表名；
• 聚合函数自动忽略null值

分组查询

• 查询各部门总人数

  • GROUP BY 分组依据，必须在WHERE之后生效

• 分组查询中select显示的列只能是分组依据或者是聚合函数列，不能出现其他列

分组过滤查询

• 使用过滤必须先使用分组
• 分组查询的GROUP BY 后面跟HAVING 过滤规则
• 过滤规则

限定查询

• select LIMIT 起始位置，查询行数；
  • 起始下标为0
• 一般用于分页查询，一页10条，差三页
  • select * from limit 0,10；第一页
  • select * from limit 10,10；第二页
  • select * from limit 20,10；第三页

SQL语句编写顺序

• SELECT 列名 指定查询的列名
• FROM 表名 指定数据来源
• WHERE 限定条件 对查询数据进行第一次过滤
• GROUP BY 分组
• HAVING 过滤条件 对分组之后的数据进行二次过滤
• ORDER BY 排列顺序 升序降序
• LIMIT 起始行，总条数 限定查询结果

子查询

• WHERE(子查询结果)

• 将子查询一行一列的结果作为外部查询的条件，做二次查询

• 子查询得到的一行一列的结果才能作为外部查询的等值或者非等值判断的条件

• 多行单列

• 查询结果是一个多行单列的数据

• salary > ALL（多行单列数据）比这个数据的任何一个数据都高

• salary > ANY（多行单列数据） 比这个数据任意一个高就可以

• 多行多列

• 作为一张临时表

• 临时表没有名字需要后面加 AS

• 将子查询多行多列的结果作为外部查询的一张表，做二次查询

• 子查询作为临时表，为其赋予一个临时的表名

合并查询

• 查询结果1 UNION 查询结果2

• 将两个查询结果进行纵向拼接

• 列名使用查询结果1的列名

• 列数必须相同，列的数据类型可以不同

• 查询结果相同自动去重 两个不同行各个列的数据都相同我们称为相同，执行union语句会去重

• 查询结果1 UNION ALL查询结果2

• 显示重复的查询结果

表连接查询

• select 列名 from 表1 连接方式 表2 ON 连接条件
• 连接方式
  • 内连接查询
    • INNER JOIN
    • 如果不指定连接条件，会造成笛卡尔积的结果
  • 左外连接
    • 左外连接是以左表为主表依次向右匹配，匹配到，返回结果
    • 匹配不到，则返回null值填充
  • 右外连接
    • 右外连接，是以右表为主表，依次向左匹配，匹配到返回结果
    • 匹配不到，返货null值填充

DML操作

• 新增
  • INSERT INTO 表名（列1，列2，列3，…）VALUE（值1，值2，值3，…）；
  • 一行受影响代表增加成功
• 修改
  • UPDATE 表名 SET 列1 = 新值1，列2 = 新值2，…WHERE条件
  • WHERE的作用是指定哪一部分数据进行操作 否则会整表更新
• 删除
  • DELETE FROM 表名 WHERE 条件；
  • 仅仅是删除数据，对表的结构没有改变
• 清空整表数据
  • TRUNCATE TABLE 表名；
  • 与DELETE不同的是，TRUNCATE是先把表销毁，在按照原表的格式创建一行新表；

数据表操作

数据类型

• 数据类型

  • 类型	大小	范围	范围	用途
    INT	4字节	-231~231-1	0~2^32	大整数值
    DOUBLE	8字节	-1.79E+308~2.22E-308	0~1.79E+308	双精度浮点数表示
    DOUBLE(M,D)	8字节，M表示长度，D表示小数位数	同上，但是受 M和D的约束	同上 受M和D的约束	双精度浮点数值
    DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D，M最大值为65	依赖于M和D，M最大值为65	小数值

• 日期类型

  • 类型	大小	范围	格式	用途
    DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
    TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
    YEAR	1	1901/2155	YYYY	年份值
    DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
    TIMESTAMP	4	1970-01-01 00:00:00 结束时间是 2038-01-19 11:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 字符串类型

  • 类型	大小	用途
    CHAR	0-255字符	定长字符串char(10)10个字符
    VARCHAR	0-65535字符	变长字符串
    BLOB	0-65535字节	二进制形式的长文本数据
    TEXT	0-65535字节	长文本数据

  • CHAR定长字符串类型，当存储的字符小于其容量时会用空格将其空出的数据填补使其满足长度为10个字符

  • VARCHAR变长字符串，会根据输入的字符长度自动调节字符串大小

  • 对一些长度明确的字符串使用CHAR，而一些商都不明确的使用VARCHAR

  • BOLB一般用于存储照片，视频之类的资源，一般不会用数据库存储而是存储在服务器

  • TEXT 长文本数据，不用指定长度

数据表的创建 CREATE

• CREATE TABLE 表名（列名 数据类型 约束 ，列名 数据类型 约束 ，…）[charset=utf8]

  • CREATE TABLE `subject`(
    	subjectID INT ,
    	subjectNAME VARCHAR(20),
    	subjectHOURS INT
    )CHARSET=utf8;
    

数据表修改 MODIFY

• ALTER TABLE 表名 操作；

  • #向现有表添加列
    ALTER TABLE `subject` ADD gradeID INT;
    

  • 向现有表添加列需要添加数据类型

  • 添加列完成后列中没有数据 使用null填充

  • #修改表中的列
    ALTER TABLE  `subject` MODIFY subjectNAME VARCHAR(10);
    #修改subjectNAME列中数据类型的长度由20变为10
    

  • 修改表中的某列时，也要写全列的名字，数据类型，约束

删除表中的列 DROP

• ALTER TABLE 表名 DROP 列名

  • 直接删除表中的列

  • #删除表中的列
    ALTER TABLE `subject` DROP gradeID;
    

  • 删除列时，一次只能删除一列；

改变列名称 CHANGE

• ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 约束；

  • 新列名要指定数据类型或约束

  • #改变列的名称
    ALTER TABLE `subject` CHANGE `subjectID` classID INT;
    
    

修改表名 RENAME

• ALTER TABLE 旧表名 RENAME 新表名

  • #修改表的名称
    ALTER TABLE `subject` RENAME `subject1`;
    

删除表 DROP

• DROP TABLE 表名；

  • #删除表
    DROP TABLE `subject1`;