【数据库系列教程】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;