2020-06-24 17:25

关注

《MySQL必知必会》——读书总结

《MySQL必知必会》——记录_1

1. 基本概念

数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。
表：某种特定类型数据的结构化清单。
列：表中的一个字段。所有表都是由一个或多个列组成的。
行：表中的一个记录。
主键：一列（或一组列），其值能够唯一区分表中每个行。
数据类型：所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。

2.使用MySQL

连接

C:\Users\admin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.46 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql>
mysql> show columns from user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| username | varchar(255) | YES  |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

关于show语句
- show status : 用于显示广泛的服务器状态信息。
- show create database和show create table ：分别用来显示创建特定数据库表或表的MySQL语句。
- show grants ：用来显示授予用户（所以用户或特定用户）的安全权限。
- show errors和show warnings : 用来显示服务器错误或警告消息。
进一步了解show ， show help 命令可以帮到您哦~

3. 检索数据

select 语句：从一个或多个表中检索信息。

检索单个列

mysql> select username from user;
+----------+
| username |
+----------+
| root     |
| root     |
| admin    |
+----------+
3 rows in set (0.00 sec)

mysql>

检索多个列

mysql> select username , password from user;
+----------+----------+
| username | password |
+----------+----------+
| root     | admin    |
| root     | root     |
| admin    | root     |
+----------+----------+
3 rows in set (0.00 sec)

mysql>

检索所有列

mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | root     | admin    |
|  2 | root     | root     |
|  3 | admin    | root     |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql>

一般，除非你确实需要表中的每个列，否则最好别使用*统配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

检索不同的行

mysql> select distinct username from user;
+----------+
| username |
+----------+
| root     |
| admin    |
+----------+
2 rows in set (0.01 sec)

mysql>

关键字distinct 指示MySQL只返回不同的值

限制结果

fanmysql> select username from user;
+----------+
| username |
+----------+
| root     |
| root     |
| admin    |
+----------+
3 rows in set (0.00 sec)

mysql> select username from user limit 2;
+----------+
| username |
+----------+
| root     |
| root     |
+----------+
2 rows in set (0.00 sec)

mysql>

关键字limit指示MySQL限制返回结果

mysql> select username from user limit 1,2;
+----------+
| username |
+----------+
| root     |
| admin    |
+----------+
2 rows in set (0.00 sec)

mysql>

解释： select username from user limit 1,2; 是返回第一行到第二行（行数是从0开始的哦~）

使用完全限定的表名

mysql> select user.username from user;
+----------+
| username |
+----------+
| root     |
| root     |
| admin    |
+----------+
3 rows in set (0.00 sec)

mysql>

4.排序检索数据

排序数据

mysql> select username from user;
+----------+
| username |
+----------+
| root     |
| root     |
| admin    |
+----------+
3 rows in set (0.00 sec)

mysql> select username from user order by username;
+----------+
| username |
+----------+
| admin    |
| root     |
| root     |
+----------+
3 rows in set (0.00 sec)

mysql>

关键字order by指示MySQL对sername列以字母的顺序排序

按多个列排序

mysql> select username ,password from user order by username,password;
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
| root     | admin    |
| root     | root     |
+----------+----------+
3 rows in set (0.00 sec)

mysql>

指定排序方向

mysql> select username ,password from user order by username desc;
+----------+----------+
| username | password |
+----------+----------+
| root     | admin    |
| root     | root     |
| admin    | root     |
+----------+----------+
3 rows in set (0.00 sec)

mysql>

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序。还可以使用order by子句以降序（从A到Z）顺序排序。为了进行降序排序必须指定DESC关键字。

如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

5.过滤数据

使用where子句

mysql> select username ,password from user where username='admin';
+----------+----------+
| username | password |
+----------+----------+
| admin    | root     |
+----------+----------+
1 row in set (0.00 sec)

mysql>

where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between	在指定的两个值之间

检查单个值

mysql> select id , username ,password from user where id = 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  3 | admin    | root     |
+----+----------+----------+
1 row in set (0.00 sec)

mysql>

mysql> select id , username ,password from user where id < 5;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | word     | 321      |
|  1 | root     | admin    |
|  2 | root     | root     |
|  3 | admin    | root     |
|  4 | hello    | 123      |
+----+----------+----------+
5 rows in set (0.00 sec)

mysql> select id , username ,password from user where id < 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | word     | 321      |
|  1 | root     | admin    |
|  2 | root     | root     |
+----+----------+----------+
3 rows in set (0.00 sec)

mysql>

mysql> select id , username ,password from user where id <= 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | word     | 321      |
|  1 | root     | admin    |
|  2 | root     | root     |
|  3 | admin    | root     |
+----+----------+----------+
4 rows in set (0.00 sec)

mysql>

不匹配检查

mysql> select id , username ,password from user where id <> 3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  0 | word     | 321      |
|  1 | root     | admin    |
|  2 | root     | root     |
|  4 | hello    | 123      |
+----+----------+----------+
4 rows in set (0.00 sec)

mysql>

范围值检查

mysql> select id , username ,password from user where id between 1 and 4;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | root     | admin    |
|  2 | root     | root     |
|  3 | admin    | root     |
|  4 | hello    | 123      |
+----+----------+----------+
4 rows in set (0.00 sec)

mysql>

空值检查

null 无值，它域字段包含0、空字符串或仅仅包含空格不同。

mysql> select username from user where password is null;
+----------+
| username |
+----------+
| haha     |
+----------+
1 row in set (0.00 sec)

mysql>

6. 数据过滤

and操作符

and 用在where子句中的关键字，用来指示检索满足所有给定的条件的行。

mysql> select id,username from user where id=3 and username='admin';
+----+----------+
| id | username |
+----+----------+
|  3 | admin    |
+----+----------+
1 row in set (0.00 sec)

mysql>

or操作符

or 用在where子句中的关键字，用来指示检索满足所有给定条件的行。

mysql> select id,username from user where id=3 or username='hello';
+----+----------+
| id | username |
+----+----------+
|  3 | admin    |
|  4 | hello    |
+----+----------+
2 rows in set (0.00 sec)

mysql>

in操作符

in 操作符用来指定条件范围，范围中的每个条件都可以进行匹配

mysql> select id,username from user where id in (1,5);
+----+----------+
| id | username |
+----+----------+
|  1 | root     |
|  5 | haha     |
+----+----------+
2 rows in set (0.00 sec)

mysql>

mysql> select id,username from user where id  not in (1,5);
+----+----------+
| id | username |
+----+----------+
|  0 | word     |
|  2 | root     |
|  3 | admin    |
|  4 | hello    |
+----+----------+
4 rows in set (0.00 sec)

mysql>

in操作符的优点

在使用合法的选项清单时，in操作符的语法更清楚且更直观。
在使用in时，计算的次序更容易管理（因为使用的操作符更少）
in操作符一般比or操作符清单执行更快
in的最大优点是可以包含其他的select语句，使得能够更动态地建立where子句。

7.使用通配符进行过滤

like操作符

百分号（%)通配符

%表示任何字符出现任意次数

mysql> select username from user where username like 'h%';
+----------+
| username |
+----------+
| hello    |
| haha     |
+----------+
2 rows in set (0.00 sec)

mysql>

区分大小写 根据MySQL的配置方式，搜索可以是区分大小写的。

下划线（_)通配符

mysql> select username from user where username like 'h_llo';
+----------+
| username |
+----------+
| hello    |
+----------+
1 row in set (0.00 sec)

mysql> select username from user where username like 'h__lo';
+----------+
| username |
+----------+
| hello    |
+----------+
1 row in set (0.00 sec)

mysql> select username from user where username like 'h___o';
+----------+
| username |
+----------+
| hello    |
+----------+
1 row in set (0.00 sec)

mysql> select username from user where username like 'h____';
+----------+
| username |
+----------+
| hello    |
+----------+
1 row in set (0.00 sec)

mysql>