MySQL常用增删改查语句

查询

代码语言：javascript复制

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

left join即使没有查到数据，左面表也会显示在查询结果(即select的表数据会显示)

表内操作：增删改

代码语言：javascript复制

# 向表内增加数据
INSERT INTO movies (CLOUMN1, COLUMN2) VALUES (4, "Toy Story 4"); 

# 修改表内数据
UPDATE movies SET director = "John Lasseter"
WHERE id = 2;

# 删除表内数据
DELETE FROM movies
where year < 2005;

表操作：创建、修改、删除

代码语言：javascript复制

create table if not exists mytable(
id integer primary key default 1,
name varchar)

# 给表增加一列
ALTER TABLE Movies
  ADD COLUMN Aspect_ratio FLOAT DEFAULT 2.39;
  
# 删除表的某列
ALTER TABLE mytable
DROP column_to_be_deleted;

# 修改表名
ALTER TABLE mytable
RENAME TO new_table_name;

# 修改表的列名
alter table 表名 change column 列名 新列明 varchar(30)

# 删除表
DROP TABLE Movies;

join select 数据

0 人点赞