管理表格

創建一個包含三列的新表
CREATE TABLE products (id    INT,name  VARCHAR(255) NOT NULL,price INT DEFAULT 0,PRIMARY KEY(id) // 自增
);
從數據庫中刪除表
DROP TABLE product;
向表中添加新列
ALTER TABLE product ADD column_name VARCHAR(255) DEFAULT '';
從表中刪除列c
ALTER TABLE product DROP COLUMN column_name
添加約束
ALTER TABLE product ADD constraint check_price_positive check (age>=0);
刪除約束
ALTER TABLE product DROP constraint check_price_positive;
將表從product重命名為products
ALTER TABLE product RENAME TO products;
將列names重命名為name
ALTER TABLE products  CHANGE names name INT
將列name的數據類型改為varchart(255)
ALTER TABLE products MODIFY name VARCHAR(255);
刪除表中的所有數據
TRUNCATE TABLE products

從表中查詢數據

從表中查詢列c1、c2中的數據
select value,set_time from sys_config
查詢表中的所有行和列
select * from sys_config sc 
查詢數據并使用條件篩選行
select value,set_time from sys_config sc WHERE value = 100
select value,set_time from sys_config sc WHERE value <> 100
select value,set_time from sys_config sc WHERE value like '%F%'
select value,set_time from sys_config sc WHERE value not like '%F%'
select value,set_time from sys_config sc WHERE value IS NULL
select value,set_time from sys_config sc WHERE value is not  NULL
select value,set_time from sys_config sc WHERE value BETWEEN 50 AND 200
select value,set_time from sys_config sc WHERE value IN (64, 65, 66)
select value,set_time from sys_config sc WHERE value not IN (64, 65, 66)
查詢表中的不同行(去重)
select distinct  value,set_time, from sys_config sc
按升序或降序對結果集排序
select * from sys_config sc ORDER  BY set_time ASC
select * from sys_config sc ORDER  BY set_time DESC
跳過行的偏移并返回下n行(分頁)
SELECT * FROM sys_config  sc ORDER BY value limit  10 OFFSET 0
使用聚合函數對行進行分組
SELECT value,MAX(set_time),count(*)  FROM sys_config  sc GROUP BY value,set_time
SELECT value,avg(set_time)  FROM sys_config  sc GROUP BY value,set_time
使用HAVING子句篩選組(過濾)
SELECT value,avg(set_time)  FROM sys_config  sc GROUP BY value,set_time having value <>100

從多個表查詢

內部連接 t1 和 t2
SELECT n.*,a.ages FROM names n INNER JOIN ages a on n.name = a.name
左連接t1和t1
SELECT n.*,a.ages FROM names n left JOIN ages a on n.name = a.name

修改數據

在表格中插入一行
INSERT INTO sys_config(variable,value,set_time,set_by) VALUES(1,2,'2023-07-11 18:29:24',4);
在表格中插入多行
INSERT INTO sys_config(variable,value,set_time,set_by) 
VALUES(15,2,'2023-07-11 18:29:24',4),(5,6,'2023-07-11 18:29:24',7);
將行從t2插入t1(跨表)
INSERT INTO names(id,name,num)
SELECT id,name,1 AS num FROM ages;
更新列c1中所有行的新值
UPDATE names SET num = 1;
更新列c1、c2中與條件匹配的值
UPDATE names SET num = 11 where name = 2;
刪除表中的行子集
DELETE FROM names WHERE id = 1;

sql變量類型

鏈接