工作中实战sql命令集合

查询

SHOW DATABASES; 列出所有数据库

select * from table_awhere tbName like 'cn%'; # 查询名字cn开头的表名，百分号为通配符

select * from table_awhere tbName not like 'cn%'; # 查询名字不以cn开头的表名，百分号为通配符

show create table table_a; # 查询table_a的建表语句
DESC table_a; # 查看数据库的表结构

select * from tb_datamanager_tableset; 查询gbase表存储时间的 gbase库
SELECT id, name, gender, score FROM students ORDER BY score DESC, name; 按照分数倒序查询，同分数的按照name查询

SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;按照分数查询每页三行数据，从第四个索引开始（索引是从0开始的）

SELECT COUNT(*) boys FROM students WHERE gender = 'M'; 查询所有男孩数量

SELECT class_id, COUNT(*) num FROM students GROUP BY class_id; 按照班级分组，看出各个班级的学生人数

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id = c.id; 把c表的内容根据class_id查入到s表，而且是查询两者都有的数据

INNER JOIN是选出两张表都存在的记录

LEFT OUTER JOIN是选出左表存在的记录：

RIGHT OUTER JOIN是选出右表存在的记录：

FULL OUTER JOIN则是选出左右表都存在的记录：

SHOW CREATE VIEW table_a; # 查看表视图

查看排序
倒序
SELECT * FROM users ORDER BY created_at DESC;

正序
SELECT * FROM users ORDER BY created_at ASC;

创建

CREATE DATABASE test; # 创建一个数据库

create table table_a like table_b; # 以table_b为模版创建一个空表table_a,字段数据格式创建语句和表table_b一致

CREATE TABLE statistics (

id BIGINT NOT NULL AUTO_INCREMENT,

class_id BIGINT NOT NULL,

average DOUBLE NOT NULL,

PRIMARY KEY (id)

); # 创建一个新表

插入

insert into table_a_bak select * from table_a; # 将一个表的数据插入另一个表，通常用于备份另一个表

INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id; 写入各班平均成绩

INSERT INTO students (class_id, name, gender,score) VALUES (2, '小花', 'F',87),(2, '小黄', 'M',82);
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。 ID有变化

INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。ID无变化

修改

在执行UPDATE语句时要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用UPDATE更新
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
UPDATE students SET score=score+10 WHERE score<80;

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL; # 给students表新增一列birth

加载

LOAD DATA INFILE 'sftp://root:root@127.0.0.1/opt/backup/20240701/test.csv.gz' INTO TABLE table_a fields terminated by ',' null_value '\N' lines terminated by '
' MAX_BAD_RECORDS 20000 timestamp format '%Y-%m-%d %H:%i:%s.%f' datetime format '%Y-%m-%d %H:%i:%s.%f' trace 1 nosplit;

删除

DROP TABLE students; # 删除一个表

DROP DATABASE test; # 删除一个数据库：
ALTER TABLE students DROP COLUMN birthday; # 要删除列

truncate table table_a; # postgres数据库删除表