【数据库系列教程】MySQL数据管理操作

上一期教程：MySQL基本语句

一、添加数据

1.命令格式

insert into 表名(字段名称,字段名称) values(数据,数据,.....)

mysql> insert into account(name, password, level) values("martin", "redhat", 10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account(name, password) values("robin", "123");
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+--------+----------+-------+
| id | name   | password | level |
+----+--------+----------+-------+
|  1 | martin | redhat   |    10 |
|  2 | robin  | 123      |     1 |
+----+--------+----------+-------+
2 rows in set (0.00 sec)

二、删除数据

1.命令格式

delete from 表名 where 条件

mysql> delete from account where level < 15;

三、更新数据

1.命令格式

update 表名 set 字段名称=新值 where 条件

mysql> update account set password="123" where name="lz";

四、数据查询

1.查询类型

  • 单表查询
  • 多表查询/连接查询
  • 嵌套查询/子查询

2.单表查询

A.导入外部数据库文件到本地

[root@localhost ~]# mysql -uroot -pWWW.1.com < jiaowu.sql

B.命令格式

mysql> select 字段名称,字段名称 from 表名 [查询子句]

mysql> select * from tutors;
mysql> select Tname, Age from tutors;
mysql> select Tname as 教师姓名, Age as 年龄 from tutors;

C.按照条件查询

mysql> select 字段名称,字段名称 from 表名 [查询子句] where 条件
a.数学运算符
  • = 等于
  • != 不等于
  • > 大于
  • >= 大于等于
  • < 小于
  • <= 小于等于
mysql> select Tname, Age from tutors where Age > 80;
mysql> select Tname, Age from tutors where Tname = "HuYiDao";
b.逻辑运算符
  • and 并且
  • or 或者
  • not 否定
mysql> select Tname, Age from tutors where Age between 70 and 80;

mysql> select * from tutors where Tname = "Huyidao" or Tname = "YiDeng" ;

IN("数据","数据", "数据")	#另外一种用法
mysql> select * from tutors where Tname in("Huyidao","YiDeng");
c.模糊查询
  • LIKE “通配符”
  • 通配符
    • % 任意字符
    • _ 任意单个字符
mysql> select Tname, Age from tutors where Tname like "%ang%";
mysql> select Tname, Age from tutors where Tname like "%ai";
  • RLIKE “正则表达式”
mysql> select Tname from tutors where Tname RLIKE "^[HN]";	#以HN开头
mysql> select Tname from tutors where Tname RLIKE "ai$";	#ai结尾
mysql> select Tname from tutors where Tname RLIKE "ang";	#包含ang
d.空值和非空
  • IS NULL
  • IS NOT NULL
mysql> select * from students where CID2 is NULL;
mysql> select * from students where CID2 is not NULL;

D.排序查询

  • order by 字段名称 [ASC|DESC]
    • ASC：升序
    • DESC：降序
mysql> select * from tutors order by Age ;
mysql> select * from tutors order by Age DESC;

E.限制查询结果行数

  • limit n[,m]
    • limit 2
    • limit 2,4
    • 忽略前n行数，显示后续的连续m行
mysql> select * from tutors limit 2;
mysql> select * from tutors limit 2,4;

F.聚合函数

  • sum( ) 求和
  • avg( ) 平均值
  • max( ) 最大值
  • min( ) 最小值
  • count( ) 计数
mysql> select avg(Age) as 平均年龄 from tutors;
+--------------+
| 平均年龄     |
+--------------+
|      67.5556 |
+--------------+
mysql> select count(*) from tutors;

G.数据分组

  • group by 字段名称 [having 条件]
  • 执行顺序：先分组 —> 对每组数据进行聚合运算 —> having 条件对聚合结果过滤
mysql> select count(*) as 人数, gender as 性别 from tutors group by Gender;
mysql> select avg(Age) as 平均年龄, gender as 性别 from tutors group by Gender;

mysql> select avg(age) as 平均年龄 from tutors group by gender having 平均年龄 > 65;

H.去重

  • distinct 字段名称
mysql> select distinct Tname from tutors;

3.嵌套查询/子查询

  • 将一个查询的结果作为另一个查询的条件使用
mysql> select Tname, Age from tutors where Age > (select avg(Age) from tutors);

mysql> select * from tutors where Age not in((select max(Age) from tutors),(select min(Age) from tutors));

4.日期时间查询

year() 年
month() 月
day() 天
date() 日期
time() 时间
hour() 小时
minute() 分钟

mysql> select day(Age) as 天 from tutors;

5.多表查询/连接查询

  • 连接查询类型
    • 内连接
    • 外连接
      • 左外连接
      • 右外连接
  • 前提: 多张表间要存在相关联的字段

A.内连接

  • 特征:：相关联字段存在相同的值时，才会显示结果
  • 语法:：select 表名.字段名称,表名.字段名称,表名.字段名称 from 表名 inner join 表名 on 相关联字段
mysql> select students.Name, students.Age, tutors.Tname
    -> from students inner join tutors
    -> on students.TID = tutors.TID;

mysql> select students.Name, students.Age, tutors.Tname 
       from students, tutors
       where students.TID=tutors.TID;

mysql> select students.Name , students.Age, courses.Cname 
    -> from students inner join courses
    -> on students.CID1 = courses.CID;

多条数据内连接

mysql> select students.Name, students.Age, courses.Cname, tutors.Tname 
    -> from students inner join courses inner join tutors
    -> on students.CID1 = courses.CID and courses.TID = tutors.TID;

B.左外连接

  • 特征：以左表为主，显示左表中所有数据；相关联存在相同值时，显示对应数据，没有相同的值时显示为NULL
  • 语法：select 表名.字段名称,表名.字段名称,表名.字段名称 from 表名 left join 表名 on 相关联字段
mysql> select students.Name, students.Age, tutors.Tname 
    -> from students left join tutors 
    -> on students.TID = tutors.TID;

mysql> select students.Name, students.Age, courses.Cname 
    -> from students left join courses
    -> on students.CID1 = courses.CID;

C.右外连接

  • 特征：以右表为主，显示右表中所有数据；相关联存在相同值时，显示对应数据，没有相同的值时显示为NULL
  • 语法：select 表名.字段名称,表名.字段名称,表名.字段名称 from 表名 right join 表名 on 相关联字段
mysql> select students.Name, students.Age, courses.Cname 
    -> from students right join courses
    -> on students.CID1 = courses.CID;