数据库（3）--针对列的CRUD操作

1.Create 新增

语法：

insert into 表名 （列名）values （列）...

创建一个学生表用于演示：

create table if not exists student(
id bigint comment '编号',
name varchar(20) comment '姓名'
);

1.1直接增加

insert into student values (1,'张三');

不在into 与 表名间声明列名

1.2声明增加单列

insert into (id,name) values (1,'张三');

 1.3声明增加多列

 

insert into (id,name) values (1,'张三'),(2,'李四');

2.Retrieve 检索

语法：

SELECT

[ DISTINCT ]

select_expr [, select_expr ] ...

[ FROM table_references ]

[ WHERE where_condition ]

[ GROUP BY { col_name | expr }, ...]

[ HAVING where_condition ]

[ ORDER BY { col_name | expr } [ ASC | DESC ], ... ]

[LIMIT {[ offset ,] row_count | row_count OFFSET offset }]

 功能较多，我们一个一个介绍

2.1select

先介绍普通查询

2.1.1全列查询

 

select * from 表名;

能打印出表中存储信息

注意desc tables；是打印表的属性 

2.2.2指定列查询 

select id from 表名;

 

在指定列查询时，可以对打印的数据进行微调，具体是：

对数据进行运算（即表达式）

对属性起别名

 2.2.2.1对数据进行运算

select id+1 from student;

也可以把类型相同的属性相加 ：

select id+id from student;

2.2.2.2 对属性起别名

select id+id as 表达式 from student;

 加上as 后跟别名（as也可以省略）

 

2.2.3结果去重查询 

语法：

select distinct 列名/* from 表名;

有 重复：

 使用去重查询后：

注意：只有全部属性都相同才会去重 

2.2 where条件查询

 语法：

select * from 表名 where 判断条件;

2.2.1比较运算符

运算符	说明
>,    >=,    <,    <=	使用与其他语言无异
=	既可以赋值又可以判断是否相等 但是不可以判断null是否等于null
<=>	专门用于null=null  返回值为1/0
!=, <>	不相等
value BETWEEN a0 AND a1	[a0, a1]  如果在这个范围中就返回1 NOT BETWEEN则取反
value IN (option, ...)	如果value 在optoin列表中，则返回TRUE(1)，NOT IN则取反
IS NULL	是null返回1
IS NOT NULL	不是null返回1
LIKE	模糊匹配，% 表⽰任意多个(包括0个)字符； _ 表⽰任意⼀个字符 NOT LIKE则取反

2.2.2逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意⼀个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

 2.3order by排序

语法：

SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | 
DESC], ... ;

-- ASC 为升序（从⼩到⼤）
-- DESC 为降序（从⼤到⼩）
-- 默认为 ASC

 

注意：

MYSQL中会自动排序，如果没有order by返回的顺序仅参考，不可信。

如果比较对象中有null，默认null是最小的值（比所有数都小，包括负数）

order by 中可以使用别名

如：

这是因为order by 是对select后的对象排序，在select中起的别名可以在order by 中识别

但是where不可以，因为where是先判断再select 

2.4 分页查询

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

作用：可以搭配order by排序成对取出前几个

3.Update修改

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET assignment [, assignment] ...[WHERE where_condition][ORDER BY ...][LIMIT row_count]

例如： 

注意：

不加where条件时，会导致全表数据被列新，谨慎操作 

4.Detele删除 

语法：

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] 

比如：

注意：

执⾏Delete时不加条件会删除整张表的数据，谨慎操作 

 5.截断表

语法：

TRUNCATE [TABLE] 表名;

可以快速将整个表清空 

说明：

只能对整表操作，不能像 DELETE ⼀样针对部分数据

不对数据操作所以⽐DELETE更快，TRUNCATE在删除数据的时候，不经过真正的事物，所以⽆法回滚

会重置AUTO_INCREMENT 项

6. 插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...;

例如： 将  去重后  的表的原表互换 并保留原表

create table student_new like student;
insert into student_new (name,id,math,english)select distinct name,id,math,english from student;rename table student to student_new, student_new to student;

 7.聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最⼤值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最⼩值，不是数字没有意义

例子：

select count(*) from student;
select count(math) from student where math >50;
select sum(math) from student;
select avg(math) from student;
select min(math) from student where math>70;
select min(math),max(math) from student;

8.Group by 分组查询

将有相同属性的对象分组，便于集中统计计算与分析

语法：

SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)FROM table_referencesGROUP BY {col_name | expr}, ... [HAVING where_condition]

 例如：

select role,ROUND(avg(salary),1) from cmp group by role;
select role,count(salary) from cmp where salary>1000.00 group by role;
-- 3                        1       2                        4         

上面的数字是执行顺序   group by也可以使用别名

但是正因为where比group by先执行，所以无法对分组后的数据进行判断

MYSQL中引入了having关键字专门用于解决此问题

8.1having子句

例如：

select role,ROUND(avg(salary)) from cmp group by role having ROUND(avg(salary))>1000.00;

Having 与Where 的区别

• Having ⽤于对分组结果的条件过滤

• Where ⽤于对表中真实数据的条件过滤

9.内置函数 

9.1日期函数

函数	说明
CURDATE()	返回当前⽇期，同义词 CURRENT_DATE ， CURRENT_DATE()
CURTIME()	返回当前时间，同义词 CURRENT_TIME ， CURRENT_TIME([fsp])
NOW()	返回当前⽇期和时间，同义语 CURRENT_TIMESTAMP ， CURRENT_TIMESTAMP
DATE(data)	提取date或datetime表达式的⽇期部分
ADDDATE(date,INTERVAL exprunit)	向⽇期值添加时间值(间隔)，同义词 DATE_ADD()
SUBDATE(date,INTERVAL exprunit)	向⽇期值减去时间值(间隔)，同义词 DATE_SUB()
DATEDIFF(expr1,expr2)	两个⽇期的差，以天为单位，expr1 - expr2

select curdate();
select curtime();
select now();
select date(now());
select adddate(curdate(),interval 31 day);
select subdate(curdate(),interval 1 month);
select datediff(curdate(),subdate(curdate(),interval 1 month));

9.2字符串处理函数

select name,char_length(name),length(name) from cmp;

select concat(name,'的工资为：',salary) as 工资 from cmp;

 

select concat_ws(',',name,ROUND(salary)) as 工资 from cmp;

 拼接后的字符串⽤逗号隔开

 

select lcase('ABC');

 

select ucase('abc');

9.3数学函数 

函数
ABS(X)	返回X的绝对值
CEIL(X)	返回不⼩于X的最⼩整数值，同义词是 CEILING(X)
FLOOR(X)	返回不⼤于X的最⼤整数值
CONV(N,from_base,to_base)	不同进制之间的转换
FORMAT(X,D)	将数字X格式化为“#，###，###”的格式。##'，四舍五⼊到⼩数点后D位，并以字符串形式返回
RAND([N])	返回⼀个随机浮点值，取值范围 [0.0, 1.0)
ROUND(X), ROUND(X,D)	将参数X舍⼊到⼩数点后D位
CRC32(expr)	计算指定字符串的循环冗余校验值并返回⼀个32位⽆符号整数

例如：

select ROUND(RAND(),6);

 

select CRC32('MYSQL');