INSERT-- 插入 (创建记录)
DELETE-- 删除 (删除记录)
UPDATE-- 修改(修改记录)
SELECT -- 检索 (从一个或多个表检索某些记录)
---
**1.insert语句**
--第一个表单没有指定要插入数据的列的名称,只提供要插入的值,即可添加一行新的数据:
INSERT INTO STUDENT_ZL(Id,Name,Sex,Home,School,Graduation_Time)
VALUES (1,'张三','男','北京市朝阳区','野鸡大学','2022/6/30');
INSERT INTO
INSERT INTO STUDENT_ZL (Id,Name,Sex,Home,Graduation_Time)
VALUES (7,'张三','男','重庆市沙坪坝区','2022/6/30');
--第二种,如果要为表中的所有列添加值,则不需要在SQL查询中指定列名称。但是,请确保值的顺序与表中的列顺序相同。INSERT INTO语法如下所示:
INSERT INTO STUDENT_ZL
VALUES (2,李四','男','上海市浦东新区','野鸡大学','2022/6/30');
INSERT INTO STUDENT VALUES (3,'王五','13263248231');
INSERT INTO STUDENT VALUES (4,'lihua','13262348231');
INSERT INTO STUDENT VALUES (5,'xiaoli','15263248239');
INSERT INTO STUDENT VALUES (6,'王五','1435248231');
--第三种,
INSERT INTO STUDENT_ZL(Id,Name)
SELECT Id,Name from STUDENT where Id NOT IN (SELECT Id from STUDENT_ZL);
--第四种,插入csv,txt文件
BULK INSERT A_TEST FROM 'E:\test.csv'
WITH
(
datafiletype = 'char',
fieldterminator = ',', -- 分隔符
rowterminator = '\n' -- 换行符
);
**2.select语句**
select * from STUDENT_ZL;读取表中所以数据;
select Id,Name from STUDENT_ZL order by Id DESC;
select * from STUDENT_ZL where Graduation_Time = '2022-06-30'order by Id DESC;
select count(Id)as school_number,count(Sex),School from STUDENT_ZL where Graduation_Time = '2022-06-30' group by School
select count(Id)as school_number --被聚合字段,使用了聚合函数的字段
,School --聚合字段
from STUDENT_ZL
where Graduation_Time = '2022-06-30' --where条件在聚合前,不能使用聚合判断
group by School --分组操作,使用后select的内容必须为聚合字段与被聚合字段
Having count(Id)<10; --having条件使用要求要在聚和后,可以使用聚和判断
```
**select子查询:
**3.update函数**
UPDATE STUDENT_ZL SET Graduation_Time = '2022-06-30' WHERE Graduation_Time is null;
UPDATE STUDENT_ZL SET Sex = '女' WHERE Sex is Null;
**4.delete函数**
DELETE FROM table_name WHERE condition;
删除表中满足条件condition的数据
DELETE * FROM table_name;删除所有数据不删除表结构