mysql 常用命令练习

管理表格

创建一个包含三列的新表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 '';从表中删除列cALTER 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重命名为productsALTER TABLE product RENAME TO products;将列names重命名为nameALTER 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 = 100select value,set_time from sys_config sc WHERE value  100select 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 NULLselect value,set_time from sys_config sc WHERE value is not  NULLselect value,set_time from sys_config sc WHERE value BETWEEN 50 AND 200select 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 ASCselect * 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_timeSELECT 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 和 t2SELECT n.*,a.ages FROM names n INNER JOIN ages a on n.name = a.name左连接t1和t1SELECT 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变量类型

链接