sql语句的基本语法

1.创建表
CREATE TABLE students (
student_id INT ,
name VARCHAR ( 4 ) NOT NULL,
score VARCHAR ( 20 ) NOT NULL
);
删除表
DROP TABLE students
2.插入数据
INSERT INTO students
VALUES
        ( 1, 'ltt1', 30),
        ( 1, 'ltt1', 90),
        ( 2, 'ltt2', 40),
        ( 2, 'ltt2', 80),
        ( 3, 'ltt3', 40),
        ( 3, 'ltt3', 70);
3.删除数据语句（或者根据条件删除）

DELETE FROM user

SELECT * FROM students

delete from students where student_id = 1 limit 2
insert into user (id,username,emile,birthday,is_acticve) value (5,'ltt5',89144,'1995-01-03',2)

4.更新语句
update ecom.students set name = 'ltt1' where name = 'ltt';

UPDATE students set settime = '20240820' WHERE score = 40;
UPDATE students set  settime = NULL WHERE score = 40 AND name = 'ltt3'

5. 查询每个学生的总成绩
SELECT NAME,SUM(score) s FROM students GROUP BY student_id ORDER BY sum(score) desc

查询学生总成绩大于110分学生的姓名以及分数
 SELECT NAME,SUM(score) s FROM students GROUP BY student_id HAVING s >110

查询学生总成绩大于110分的总人数
SELECT
    COUNT(*) c
FROM
    (SELECT NAME,SUM(score) s FROM students GROUP BY student_id) t
WHERE
    s > 110

6.union查询(除重复的行)

SELECT student_id u_id  FROM students UNION SELECT id u_id  FROM user ;

7.添加字段
ALTER TABLE students ADD COLUMN  settime CHAR(50);
 删除字段
ALTER TABLE students DROP COLUMN notime;

8.转换日期格式

select * from students where create_time >= DATE_ADD(str_to_date('2024-08-14','%Y-%m-%d'),INTERVAL 5 DAY)
select DATE_ADD(str_to_date('2024-08-19','%Y-%m-%d'),INTERVAL 1 DAY)
select str_to_date(settime,'%Y%m%d') from students --格式需要与数据格式匹配

 

select
    left(STR_TO_DATE(settime,'%Y'),8    
from
    students
SELECT * FROM students
--日期转换函数
select UNIX_TIMESTAMP(now());
-- 输出：1712752702 
select UNIX_TIMESTAMP()
select date_format(update_time,'%Y-%m-%d %H:%m:%s') from students
select from_unixtime(update_time) FROM students

 SELECT DATE_ADD('2024-04-10',INTERVAL 1 DAY);
 select date_sub('2024-04-10',INTERVAL 1 DAY)
 select date_add(create_time,INTERVAL 2 MONTH) FROM students