mysql基础 ​

alter改操作 ​

添加列

alter table <table_name>
add <column_name> <data_type>;

alter table <table_name>
add <column_name> <data_type>;

修改列

alter table <table_name>
modify <column_name> <data_type>;

alter table <table_name>
modify <column_name> <data_type>;

修改列

alter table <table_name>
change <column_name> <data_type>;

alter table <table_name>
change <column_name> <data_type>;

删除列

alter table <table_name>
drop <column_name>;

alter table <table_name>
drop <column_name>;

数据表更名

alter table <table_name>
rename  <new_table_name>;

alter table <table_name>
rename  <new_table_name>;

表操作 ​

使用select查询结果创建表 ​

create table <table_name> 
SELECT * FROM <table_name>;

create table <table_name> 
SELECT * FROM <table_name>;

删除表 ​

drop table <table_name>;

drop table <table_name>;

约束 ​

主键
primary key ;
非空
not null;
唯一
unique;
默认
default;
检查
check;
外键
foreign key;

insert增操作 ​

-- 增加一条记录

INSERT INTO <table_name> (字段1, 字段2, 字段3, ...) 
VALUES (对应值1, 对应值2, 对应值3, ..., );

INSERT INTO <table_name> (字段1, 字段2, 字段3, ...) 
VALUES (对应值1, 对应值2, 对应值3, ..., );

-- 向表中添加数据

INSERT INTO <table_name>
SET 字段1 = value1, 字段2 = value2, 字段3 = value3, ...;

INSERT INTO <table_name>
SET 字段1 = value1, 字段2 = value2, 字段3 = value3, ...;

-- 通过SELECT语句将数据插入表中

INSERT INTO <table_name>
SELECT 字段1, 字段2, 字段3, ... FROM <an<other_table>_name>;

INSERT INTO <table_name>
SELECT 字段1, 字段2, 字段3, ... FROM <an<other_table>_name>;

-- 从一个表中复制数据到另一个表中

INSERT INTO <table_name>
SELECT * FROM <an<other_table>>;

INSERT INTO <table_name>
SELECT * FROM <an<other_table>>;

delete删操作 ​

DELETE FROM <table_name> WHERE <condition>;

DELETE FROM <table_name> WHERE <condition>;

update改操作 ​

UPDATE <table_name>
SET 字段1 = value1, 字段2 = value2, ...
WHERE <condition>;

UPDATE <table_name>
SET 字段1 = value1, 字段2 = value2, ...
WHERE <condition>;

select查操作 ​

1. 查询表中的所有列

   sql

   SELECT * 
   FROM <table_name>

   SELECT * 
   FROM <table_name>

   1
   2
   

2. 查询指定列

   sql

   SELECT 字段1, 字段2 
   FROM <table_name>

   SELECT 字段1, 字段2 
   FROM <table_name>

   1
   2
   

3. 查询指定列并指定条件

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
   WHERE <condition>

   SELECT 字段1, 字段2 
   FROM <table_name> 
   WHERE <condition>

   1
   2
   3
   

4. 查询指定列并按照指定列排序

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
   ORDER BY 字段1 ASC/DESC

   SELECT 字段1, 字段2 
   FROM <table_name> 
   ORDER BY 字段1 ASC/DESC

   1
   2
   3
   

5. 查询指定列并分组

   sql

   SELECT 
       字段1, 
       字段2, 
       COUNT(*) as count 
   FROM <table_name> 
   GROUP BY 字段1, 字段2

   SELECT 
       字段1, 
       字段2, 
       COUNT(*) as count 
   FROM <table_name> 
   GROUP BY 字段1, 字段2

   1
   2
   3
   4
   5
   6
   

6. 查询指定列并使用子查询

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
   WHERE 字段1 IN (SELECT 字段1 FROM <other_table>)

   SELECT 字段1, 字段2 
   FROM <table_name> 
   WHERE 字段1 IN (SELECT 字段1 FROM <other_table>)

   1
   2
   3
   

7. 查询指定列并使用连接查询

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
       JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   SELECT 字段1, 字段2 
   FROM <table_name> 
       JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   1
   2
   3
   

8. 查询指定列并使用左连接查询

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
       LEFT JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   SELECT 字段1, 字段2 
   FROM <table_name> 
       LEFT JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   1
   2
   3
   

9. 查询指定列并使用右连接查询

   sql

   SELECT 字段1, 字段2 
   FROM <table_name> 
       RIGHT JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   SELECT 字段1, 字段2 
   FROM <table_name> 
       RIGHT JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

   1
   2
   3
   

10. 查询指定列并使用外连接查询

    sql

    SELECT 字段1, 字段2 
    FROM <table_name> 
        FULL OUTER JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

    SELECT 字段1, 字段2 
    FROM <table_name> 
        FULL OUTER JOIN <other_table> ON <table_name>.字段1 = <other_table>.字段1

    1
    2
    3
    

11. 查询指定列并使用聚合函数

    sql

    SELECT 字段1, COUNT(*) as count 
    FROM <table_name> 
    GROUP BY 字段1

    SELECT 字段1, COUNT(*) as count 
    FROM <table_name> 
    GROUP BY 字段1

    1
    2
    3
    

12. 查询指定列并使用like模糊匹配

    sql

    SELECT 字段1, 字段2 
    FROM <table_name> 
    WHERE 字段1 LIKE '%abc%'

    SELECT 字段1, 字段2 
    FROM <table_name> 
    WHERE 字段1 LIKE '%abc%'

    1
    2
    3