数据库（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比较运算符

2.2.2逻辑运算符

 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_reference
 SET 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.聚合函数

例子：

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_references
 GROUP 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 ⽤于对表中真实数据的条件过滤

9.内置函数 

9.1日期函数

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数学函数 

例如：

select ROUND(RAND(),6);

select CRC32('MYSQL');