一、DML数据管理语言
1、作用
对表中的数据进行管理操作,增删改查
二、添加数据 insert into
1、语法
> insert into 表名(字段名称,字段名称) values(数据, 数据)
mysql> insert into user(name,password,level,is_vip) values("martin", "redhat", 10, "Y");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+----------+-------+--------+
| id | name | password | level | is_vip |
+----+--------+----------+-------+--------+
| 1 | martin | redhat | 10 | Y |
+----+--------+----------+-------+--------+
1 row in set (0.00 sec)
mysql> insert into user(name,password,level,is_vip) values("demon", "123", 15, "Y"),("leah", "abc", 8, "N");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+--------+----------+-------+--------+
| id | name | password | level | is_vip |
+----+--------+----------+-------+--------+
| 1 | martin | redhat | 10 | Y |
| 2 | demon | 123 | 15 | Y |
| 3 | leah | abc | 8 | N |
+----+--------+----------+-------+--------+
3 rows in set (0.00 sec)
mysql> insert into user(name,password) values("tome", "abc123");
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+----------+-------+--------+
| id | name | password | level | is_vip |
+----+--------+----------+-------+--------+
| 1 | martin | redhat | 10 | Y |
| 2 | demon | 123 | 15 | Y |
| 3 | leah | abc | 8 | N |
| 4 | tome | abc123 | 1 | N |
+----+--------+----------+-------+--------+
4 rows in set (0.00 sec)
三、删除数据 delete
1、语法
> delete from 表名 where 条件
mysql> delete from user where name="demon";
Query OK, 1 row affected (0.00 sec)
四、更新数据 update
1、语法
> update 表名 set 字段名称=新数据 where 条件
mysql> update user set password="WWW.1.com" where name="martin";
mysql> update user set is_vip="Y" where level < 10;
五、数据查询 select
1、查询类型
- 单表查询
- 嵌套查询/子查询
- 多表查询/连接查询
内连接
外连接
左外连接
右外连接
2、单表查询
1、语法
> select 字段名称, 字段名称 from 表名 [查询子句]
2、导入jiaowu数据库
[root@localhost ~]# lftp 10.11.0.254
lftp 10.11.0.254:~> cd upload/
lftp 10.11.0.254:/upload> get jiaowu.sql
5276 bytes transferred
lftp 10.11.0.254:/upload> exit
[root@localhost ~]# mysql -uroot -pWWW.1.com < jiaowu.sql
mysql> select * from tutors;
mysql> select Tname, Age from tutors;
mysql> select Tname as 姓名, Age as 年龄 from tutors;
3、查询子句——where 条件
1) 运算符
=, !=, >, >=, <, <=
mysql> select Tname, Age from tutors where Tname="YiDeng";
mysql> select Tname, Age from tutors where Age > 80;
2) 逻辑运算符, and, or
and
mysql> select Tname, Age from tutors where Age >= 70 and Age <= 80;
mysql> select Tname, Age from tutors where Age between 70 and 80;
or 或者
mysql> select Tname, Gender, Age from tutors where Tname = "HongQigong" or Tname = "HuYidao";
mysql> select Tname, Gender, Age from tutors where Tname in("HongQigong", "HuYidao");
3) 模糊查询
字段名称 LIKE “通配符条件"
- 通配符
% 任意长度任意字符
_ 任意单个字符
mysql> select Tname from tutors where Tname like "H%";
mysql> select Tname from tutors where Tname like "%ang%";
字段名称 RLIKE “正则表达式”
mysql> select Tname from tutors where Tname rlike "^H";
4) IS NULL, IS NOT NULL
mysql> select Name from students where TID is NULL;
mysql> select Name from students where TID is not NULL;
4、排序显示结果 —— order by 字段名称 [ASC|DESC]
ASC, 默认, 升序
mysql> select Tname, Gender, Age from tutors where Gender = "M" order by Age;
DESC 降序
mysql> select Tname, Gender, Age from tutors where Gender = "M" order by Age DESC;
5、限制结果行数 —— limit [n,]m
limit 2, 只显示结果中的前2行
mysql> select Tname, Gender, Age from tutors where Gender = "M" order by Age desc limit 1;
limit 3, 1
mysql> select * from tutors limit 3,1;
6、去重显示 distinct
mysql> select distinct Tname from tutors;
7、聚合函数
- sum(字段名称)
- avg(字段名称)
- max(字段名称)
- min(字段名称)
- count(字段名称), 统计行数、计数
mysql> select avg(Age) as 平均年龄 from tutors;
+--------------+
| 平均年龄 |
+--------------+
| 67.5556 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(Tname) as 人数 from tutors;
+--------+
| 人数 |
+--------+
| 9 |
+--------+
1 row in set (0.00 sec)
8、数据分组 —— group by 字段名称 [having 条件]
注意:分组配合聚合函数用
mysql> select avg(Age) as 平均年龄, Gender as 性别 from tutors group by Gender;
+--------------+--------+
| 平均年龄 | 性别 |
+--------------+--------+
| 60.5000 | F |
| 69.5714 | M |
+--------------+--------+
2 rows in set (0.00 sec)
执行流程:
1、先分组
2、对每一组数据进行聚合运算
having 条件:对分组聚合之后的结果进行条件过滤
mysql> select avg(Age) as 平均年龄, Gender as 性别 from tutors group by Gender having 平均年龄>65;
+--------------+--------+
| 平均年龄 | 性别 |
+--------------+--------+
| 69.5714 | M |
+--------------+--------+
1 row in set (0.00 sec)
3、嵌套查询/子查询
- 场景: 将一个查询的结果作为另外一个查询的条件用
mysql> select Tname, Age from tutors where Age > (select avg(Age) from tutors where Gender = "M");
4、多表查询/连接查询
前提条件:多张表之间要存在相关联的字段,关系
- 内连接
- 外连接
4.1 内连接
1) 特征:
只有相关联字段存在相同值时,才会显示对应的结果
2) 语法
> select 表名.字段名称,表名.字段名称 from 表名 inner join 表名 on 相关联的字段
mysql> select students.Name, tutors.Tname
-> from students inner join tutors
-> on students.TID = tutors.TID;
mysql> select students.Name , courses.Cname
-> from students inner join courses
-> on students.CID1 = courses.CID;
mysql> select students.Name, courses.Cname, tutors.Tname
-> from students inner join courses inner join tutors
-> on students.CID2 = courses.CID and courses.TID = tutors.TID;
4.2 外连接
- 左外连接
- 右外连接
1) 左外连接
特征: 以左表为主,显示左表中的所有数据,相关联的表有数据则显示,没有显示NULL
2) 语法
> select 表名.字段名称,表名.字段名称 from 表名 left join 表名 on 相关联的字段
mysql> select students.Name, tutors.Tname
-> from students left join tutors
-> on students.TID = tutors.TID;
3) 右外连接
特征: 以右表为主,显示右表中的所有数据,相关联的表有数据则显示,没有显示NULL
4) 语法
> select 表名.字段名称,表名.字段名称 from 表名 right join 表名 on 相关联的字段
mysql> select students.Name, tutors.Tname
-> from tutors right join students
-> on students.TID = tutors.TID;
六、索引 Index
1、介绍
-
作用
建立合适的索引, 提升数据的查询速度 -
数据库默认查询数据的行为是全表扫描
-
根据哪个字段创建索引,需要考虑前端用户的访问行为
-
不建议在频繁更新的字段上创建索引
2、查看索引
mysql> show index from user\G;
*************************** 1. row ***************************
Table: user
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
3、创建索引
> create index index_name on tb_name(字段名称,字段名称)
4、删除索引
> drop index index_name on tb_name
5、查询分析器 explain
5.1 没有索引的情况
mysql>
mysql> explain select name, price from info where price=2000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | info | NULL | ALL | NULL | NULL | NULL | NULL | 248400 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.2 创建索引
mysql> CREATE INDEX price_key ON info(price);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
5.3 测试同样的查询
mysql> explain select name, price from info where price=2000;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | info | NULL | ref | price_key | price_key | 5 | const | 65 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
possible_keys: 表中存在的索引名称
key:查询语句使用的索引
rows:数据查询的行数
5.4 SQL语句不会使用索引的情况
- 非等值条件 != , >, <
- 模糊查询 LIKE, RLIKE
- in(数据,数据) 或者
- 隐式数据类型转换