数据表的操作——mysql为例（3）_给表为例。-优快云博客

本文详细介绍了SQL的基本操作，包括增加、修改、删除、查询记录的方法，以及如何使用聚合函数进行统计分析，还讲解了如何进行多表连接查询。

表记录

对于表中的内容，每一行就是一条记录，也是一个对象

增加记录

insert into 表名(字段名....)
    values(值....)
)

MariaDB [cc]> insert into ntest (id,age,name)
    -> values(1,17,"zhangsan");
Query OK, 1 row affected (0.06 sec)

insert into 表名 set 字段=值

MariaDB [cc]> insert into ntest set name="lisi";
Query OK, 1 row affected (0.00 sec)

修改记录

UPDATA 表名 set 字段=值 WHERE 筛选条件

没有删选条件会把所有字段的值都修改

MariaDB [cc]> update ntest set age=20 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除记录

delete from 表名 where 筛选条件

如果没有筛选条件会将表中的所有内容删除

MariaDB [cc]> delete from ntest where id =1;
Query OK, 1 row affected (0.04 sec)

另外一种完全删除所有记录的方式：删除表后再次创建一个同名的空表

truncate 表名

MariaDB [cc]> truncate ntest;
Query OK, 0 rows affected (0.12 sec)

查询记录

select 查询的字段 from 表名
* 代表着所有的字段

select * from 表名

MariaDB [cc]> select * from ntest;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  1 |   17 | zhangsan |
+----+------+----------+
1 row in set (0.00 sec)

对查询的数据去重

SELECT DISTINCT * FROM 表名;

给字段起别名

select 字段 as 别名 from 表名;

MariaDB [cc]> select name as NA FROM ntest;
+----------+
| NA       |
+----------+
| zhangsan |
+----------+
1 row in set (0.00 sec)

where 语句
在where后加上筛选条件实现需求的选择。

比较运算符

< >= <= != <>
between a and b 表示在数值在a与b之间
in(需要匹配的值)
对值得匹配
like 需要的匹配值
%表示匹配多个字符
_表示匹配一个字符

MariaDB [cc]> select name from ntest where name like "zh%";
+----------+
| name     |
+----------+
| zhangsan |
+----------+
1 row in set (0.00 sec)

MariaDB [cc]> select name from ntest where name like "zh_";
Empty set (0.00 sec)

逻辑运算符(用于多个条件查询)

and or not

排序

**注意：如果你有过滤条件，一定要将排序放在过滤之后** **order by 排序条件**

asc 升序
desc 降序

MariaDB [cc]> select * from ntest order by age;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  1 |   17 | zhangsan |
|  2 |   68 | wan      |
+----+------+----------+
2 rows in set (0.00 sec)

MariaDB [cc]> select * from ntest order by age desc;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  2 |   68 | wan      |
|  1 |   17 | zhangsan |
+----+------+----------+
2 rows in set (0.00 sec)

分组查询

group by 分组条件

MariaDB [cc]> select * from ntest;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  1 |   17 | zhangsan |
|  2 |   68 | wan      |
|  3 |   11 | wan      |
+----+------+----------+
3 rows in set (0.00 sec)

分组后的结果

MariaDB [cc]> select * from ntest group by name;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  2 |   68 | wan      |
|  1 |   17 | zhangsan |
+----+------+----------+
2 rows in set (0.00 sec)

having
作用和where相同只不过group by只能通过having实现过滤；

MariaDB [cc]> select * from ntest group by name having name like "zhang%";
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  1 |   17 | zhangsan |
+----+------+----------+
1 row in set (0.00 sec)

聚合函数

count(字段) 统计
sum(字段) 叠加
avg（字段）求平均值
ifnull（字段，0）归零函数：将null的值置为0；
max（字段）最大值
min (字段) 最小值
limit [跳过值]，显示值，
限制读取值

MariaDB [cc]> select * from ntest limit 1;
+----+------+----------+
| id | age  | name     |
+----+------+----------+
|  1 |   17 | zhangsan |
+----+------+----------+
1 row in set (0.00 sec)

正则匹配查询

regexp '正则表达式'

多表连接查询

笛卡尔积
简单的来说就是将所有表的相关记录相互交叉连接展示在一张表上

MariaDB [school]> select * from student;
+----+------+------+--------------+
| id | age  | name | teacher_name |
+----+------+------+--------------+
|  4 |   23 | zhan | wen          |
|  5 |   19 | zhao | lei          |
|  6 |   21 | fou  | jia          |
|  7 |   19 | zhao | lei          |
+----+------+------+--------------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
+----+------+--------------+
3 rows in set (0.00 sec)

做笛卡尔积后的结果

MariaDB [school]> select * from student,teacher;
+----+------+------+--------------+----+------+--------------+
| id | age  | name | teacher_name | id | age  | teacher_name |
+----+------+------+--------------+----+------+--------------+
|  4 |   23 | zhan | wen          |  1 |   34 | wen          |
|  4 |   23 | zhan | wen          |  2 |   45 | lei          |
|  4 |   23 | zhan | wen          |  3 |   43 | jia          |
|  5 |   19 | zhao | lei          |  1 |   34 | wen          |
|  5 |   19 | zhao | lei          |  2 |   45 | lei          |
|  5 |   19 | zhao | lei          |  3 |   43 | jia          |
|  6 |   21 | fou  | jia          |  1 |   34 | wen          |
|  6 |   21 | fou  | jia          |  2 |   45 | lei          |
|  6 |   21 | fou  | jia          |  3 |   43 | jia          |
|  7 |   19 | zhao | lei          |  1 |   34 | wen          |
|  7 |   19 | zhao | lei          |  2 |   45 | lei          |
|  7 |   19 | zhao | lei          |  3 |   43 | jia          |
+----+------+------+--------------+----+------+--------------+
12 rows in set (0.07 sec)

内连接
内连接，通俗的讲就是对表中有相同数据的一个筛选

MariaDB [school]> select * from student,teacher where student.teacher_name=teacher.teacher_name;
+----+------+------+--------------+----+------+--------------+
| id | age  | name | teacher_name | id | age  | teacher_name |
+----+------+------+--------------+----+------+--------------+
|  4 |   23 | zhan | wen          |  1 |   34 | wen          |
|  5 |   19 | zhao | lei          |  2 |   45 | lei          |
|  6 |   21 | fou  | jia          |  3 |   43 | jia          |
|  7 |   19 | zhao | lei          |  2 |   45 | lei          |
+----+------+------+--------------+----+------+--------------+
4 rows in set (0.00 sec)

通过inner join … on 实现内连接

MariaDB [school]> select * from student inner join teacher on student.teacher_name=teacher.teacher_name;
+----+------+------+--------------+----+------+--------------+
| id | age  | name | teacher_name | id | age  | teacher_name |
+----+------+------+--------------+----+------+--------------+
|  4 |   23 | zhan | wen          |  1 |   34 | wen          |
|  5 |   19 | zhao | lei          |  2 |   45 | lei          |
|  6 |   21 | fou  | jia          |  3 |   43 | jia          |
|  7 |   19 | zhao | lei          |  2 |   45 | lei          |
+----+------+------+--------------+----+------+--------------+
4 rows in set (0.08 sec)

外连接

左连接:以左边的表为主;

select * from student left join teacher on student.teacher_name=teacher.teacher_name;

右连接:以右边的表为主;

select * from student right join teacher on student.teacher_name=teacher.teacher_name;

复合查询:约束条件有多个通过and or 等词连接
子查询：通俗的讲就是查询套查询一层套一层，将最底层查询的结果作为上一层查询的约束条件;

MariaDB [school]> select * from student 
                where teacher_name =
                 (
                    select teacher_name from teacher
                                     where id = 1
                   );
+----+------+------+--------------+
| id | age  | name | teacher_name |
+----+------+------+--------------+
|  4 |   23 | zhan | wen          |
+----+------+------+--------------+
1 row in set (0.00 sec)

END !