表内容操作

1、增

insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表

2、删

delete from 表
delete from 表 where id＝1 and name＝'alex'

3、改

update 表 set name ＝ 'alex' where id>1

4、查

select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1

5、其他

a、条件
select * from 表 where id > 1 and name != 'alex' and num = 12;

select * from 表 where id between 5 and 16;

select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)

b、通配符
select * from 表 where name like 'ale%' - ale开头的所有（多个字符串）
select * from 表 where name like 'ale_' - ale开头的所有（一个字符）

c、限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
select * from 表 limit 5 offset 4 - 从第4行开始的5行

d、排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

e、分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid

select num from 表 group by num having max(id) > 10

特别的：group by 必须在where之后，order by之前

f、连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid

无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid

A表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid

B表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid

g、组合
组合，自动处理重合
select nickname
from A
union
select name
from B

组合，不处理重合
select nickname
from A
union all
select name
from B

MariaDB [test]> create table tb31(
    -> nid int auto_increment primary key,
    -> name varchar(20),
    -> age int)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> create table tb32(
    -> nid int auto_increment primary key,
    -> caption varchar(20),
    -> gender varchar(2))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into tb32(caption,gender) values('kakaka','1'),('papapa','2'),('fafafa','3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
Empty set (0.00 sec)

MariaDB [test]> select * from tb32;
+-----+---------+--------+
| nid | caption | gender |
+-----+---------+--------+
|   1 | kakaka  | 1      |
|   2 | papapa  | 2      |
|   3 | fafafa  | 3      |
+-----+---------+--------+
3 rows in set (0.01 sec)

MariaDB [test]> insert into tb31(name,age) values('smoke',19);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,gender from tb32;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   1 | smoke  |   19 |
|   2 | kakaka |    1 |
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
+-----+--------+------+
4 rows in set (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,caption from tb32;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   1 | smoke  |   19 |
|   2 | kakaka |    1 |
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   5 | kakaka |    0 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
+-----+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,nid from tb32;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   1 | smoke  |   19 |
|   2 | kakaka |    1 |
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   5 | kakaka |    0 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   8 | kakaka |    1 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
+-----+--------+------+
10 rows in set (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,nid from tb32 where nid>2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   1 | smoke  |   19 |
|   2 | kakaka |    1 |
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   5 | kakaka |    0 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   8 | kakaka |    1 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
|  11 | fafafa |    3 |
+-----+--------+------+
11 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where nid=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   2 | kakaka |    1 |
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   5 | kakaka |    0 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   8 | kakaka |    1 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
|  11 | fafafa |    3 |
+-----+--------+------+
10 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where name='kakaka';
Query OK, 3 rows affected (0.00 sec)

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
|  11 | fafafa |    3 |
+-----+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where nid=3 and name='fafafa';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   3 | papapa |    2 |
|   4 | fafafa |    3 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
|  11 | fafafa |    3 |
+-----+--------+------+
7 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where nid=4 and name='fafafa';
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   3 | papapa |    2 |
|   6 | papapa |    0 |
|   7 | fafafa |    0 |
|   9 | papapa |    2 |
|  10 | fafafa |    3 |
|  11 | fafafa |    3 |
+-----+--------+------+
6 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where nid=4 or name='fafafa';
Query OK, 3 rows affected (0.00 sec)

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|   3 | papapa |    2 |
|   6 | papapa |    0 |
|   9 | papapa |    2 |
+-----+--------+------+
3 rows in set (0.00 sec)

MariaDB [test]> delete from tb31 where nid>1;
Query OK, 3 rows affected (0.00 sec)

MariaDB [test]> select * from tb31;
Empty set (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,gender from tb32;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    2 |
|  14 | fafafa |    3 |
+-----+--------+------+
3 rows in set (0.00 sec)

MariaDB [test]> insert into tb31(name,age) select caption,gender from tb32;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    2 |
|  14 | fafafa |    3 |
|  15 | kakaka |    1 |
|  16 | papapa |    2 |
|  17 | fafafa |    3 |
+-----+--------+------+
6 rows in set (0.00 sec)

MariaDB [test]> update tb31 set age=1;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 6  Changed: 4  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    1 |
|  14 | fafafa |    1 |
|  15 | kakaka |    1 |
|  16 | papapa |    1 |
|  17 | fafafa |    1 |
+-----+--------+------+
6 rows in set (0.00 sec)

MariaDB [test]> update tb31 set age=2 where nid=13;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    2 |
|  14 | fafafa |    1 |
|  15 | kakaka |    1 |
|  16 | papapa |    1 |
|  17 | fafafa |    1 |
+-----+--------+------+
6 rows in set (0.00 sec)

MariaDB [test]> update tb31 set age=2,name='opop' where nid>15;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    2 |
|  14 | fafafa |    1 |
|  15 | kakaka |    1 |
|  16 | opop   |    2 |
|  17 | opop   |    2 |
+-----+--------+------+
6 rows in set (0.01 sec)

MariaDB [test]> update tb31 set age=2,name='opop' where nid>15 and 1=1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2  Changed: 0  Warnings: 0

MariaDB [test]> select * from tb31;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  12 | kakaka |    1 |
|  13 | papapa |    2 |
|  14 | fafafa |    1 |
|  15 | kakaka |    1 |
|  16 | opop   |    2 |
|  17 | opop   |    2 |
+-----+--------+------+
6 rows in set (0.00 sec)

MariaDB [test]> select * from tb31 where nid>16;
+-----+------+------+
| nid | name | age  |
+-----+------+------+
|  17 | opop |    2 |
+-----+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select * from tb31 where nid>15;
+-----+------+------+
| nid | name | age  |
+-----+------+------+
|  16 | opop |    2 |
|  17 | opop |    2 |
+-----+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from tb31 where nid>=15;
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  15 | kakaka |    1 |
|  16 | opop   |    2 |
|  17 | opop   |    2 |
+-----+--------+------+
3 rows in set (0.00 sec)

MariaDB [test]> select * from tb31 where nid>=15 and name='kakaka';
+-----+--------+------+
| nid | name   | age  |
+-----+--------+------+
|  15 | kakaka |    1 |
+-----+--------+------+
1 row in set (0.00 sec)

MariaDB [test]> select name from tb31 where nid>=15 and name='kakaka';
+--------+
| name   |
+--------+
| kakaka |
+--------+
1 row in set (0.00 sec)

MariaDB [test]> select name,age from tb31 where nid>=15 and name='kakaka';
+--------+------+
| name   | age  |
+--------+------+
| kakaka |    1 |
+--------+------+
1 row in set (0.00 sec)

MariaDB [test]> select name,age,nid from tb31 where nid>=15 and name='kakaka';
+--------+------+-----+
| name   | age  | nid |
+--------+------+-----+
| kakaka |    1 |  15 |
+--------+------+-----+
1 row in set (0.00 sec)