目的
本文主要记录一些常见的mysql知识, 可以理解为mysql扫码贴。
了解mysql
数据库: 一个保存着有组织的数据的容器。
数据库软件: DBMS, 数据库是通过DBMS来操作的容器。
表: 某中特定数据类型的结构化清单
模式(schema): 关于数据库和表的布局以及特性的相关信息
列(column): 表由列组成, 列中保存着表中某部分信息, 每一列都有相应的数据类型
数据类型: 所容许的数据类型, 它限制该列所存储的数据结构
行(row/record): 表中的一行记录
主键(primary key): 唯一标识表中每行的这个列(这组列), 每个表都应该拥有一个主键
复制代码
满足主键的两个条件:
1. 任意两行都不会具有相同的主键值
2. 每行都要有一个主键值(不能为空)
复制代码
像mysql, oracle, sql server等数据库都是基于客户机-服务器的数控库。 服务器部分是负责所有数据的访问与处理, 一般是安装在数据库服务器上, 客户机则是与用户打交道的软件。
以mysql为例:
服务器软件: mysql DBMS
客户机: 支持mysql的工具, 如java,node,python等编程语言
复制代码
使用mysql
连接数据库, 需要主机名,端口,一个合法的用户名, 或者用户口令。
// 以本地连接为例
mysql -u root -p
复制代码
选择数据库使用USE关键字。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| s3 |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use s3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
复制代码
查看一个数据库中的列表: show tables;
mysql> show tables;
+--------------+
| Tables_in_s3 |
+--------------+
| person |
+--------------+
1 row in set (0.00 sec)
复制代码
查看表中的列: show columns from dbName;
mysql> show columns from person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | int(11) | NO | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
复制代码
上述查看的方式还可以使用describe语句
mysql> describe person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | int(11) | NO | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
复制代码
show status: 查看服务器状态信息。
show errors: 显示服务器错误信息
show warnings: 显示服务器警告信息
show grants: 显示授予用户的安全权限
复制代码
查看创建表的sql语句: show create table tableName;
mysql> show create table person;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`sex` int(11) NOT NULL,
`birth` date DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码
查看创建数据库的语句: show create database dbName;
mysql> show create database s3;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| s3 | CREATE DATABASE `s3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码
检索数据
主要介绍使用select语句从数据库中检索一个或者多个数据列。
- 检索单个列
mysql> select name from person;
+-------+
| name |
+-------+
| kobe |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
| james |
+-------+
11 rows in set (0.00 sec)
复制代码
- 检索多个列
检索多个列, 列名之间使用逗号隔开。
mysql> select name, sex from person;
+-------+-----+
| name | sex |
+-------+-----+
| kobe | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
| james | 1 |
+-------+-----+
11 rows in set (0.00 sec)
复制代码
- 检索所有列
使用通配符*来进行。
mysql> select * from person;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | james | 1 | 2013-09-12 |
| 3 | james | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
| 8 | james | 1 | 2013-09-12 |
| 9 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
复制代码
- 检索不同的值
如果检索数据时候不想要出现重复的结果,可以使用distinct关键字. distinct关键字需要放在所有列的前面, 因此不能部分使用distinct。
mysql> select distinct name from person;
+-------+
| name |
+-------+
| kobe |
| james |
+-------+
2 rows in set
(0.00 sec)
复制代码
- 限制结果
使用limit(不多于)关键字限制检索出来的数量。
mysql> select * from person limit 5;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | james | 1 | 2013-09-12 |
| 3 | james | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
复制代码
limit后面可以跟两个值, 第一个为起始位置, 第二个是要检索的行数。
mysql> select * from person limit 5, 5;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
| 8 | james | 1 | 2013-09-12 |
| 9 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
5 rows in set (0.00 sec)
复制代码
mysql> select * from person limit 0,1;
+-----+------+-----+------------+
| sid | name | sex | birth |
+-----+------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
+-----+------+-----+------------+
1 row in set (0.00 sec)
复制代码
limit可以配合offset使用, 如limit 4 offset 3(从行3开始后的四行)。
mysql> select * from person limit 3,4
-> ;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
mysql> select * from person limit 4 offset 3;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 4 | james | 1 | 2013-09-12 |
| 5 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | james | 1 | 2013-09-12 |
+-----+-------+-----+------------+
4 rows in set (0.00 sec)
复制代码
- 使用完全限定的表名
mysql> select distinct person.name from s3.person;
+-------+
| name |
+-------+
| kobe |
| james |
+-------+
2 rows in set (0.00 sec)
复制代码
排序检索数据
讲述使用select语句的order by子句,来对检索出来的数据进行排序。 order by子句可以取一个或者多个列的名字。
- 普通的排序
mysql> select * from person;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 1 | kobe | 1 | 2018-11-27 |
| 2 | wade | 1 | 2013-09-12 |
| 3 | cup | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 5 | se | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-12 |
| 9 | ass | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | jams | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
mysql> select * from person order by name;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 9 | ass | 1 | 2013-09-12 |
| 3 | cup | 1 | 2013-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 6 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-09-12 |
| 11 | jams | 1 | 2013-09-12 |
| 8 | jjs | 1 | 2013-09-12 |
| 1 | kobe | 1 | 2018-11-27 |
| 5 | se | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 2 | wade | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
复制代码
- 按照多个列进行排序
下面这个例子标示仅在相同的name时候才对birth字段进行排序。
mysql> select * from person order by name, birth;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 9 | ass | 1 | 2013-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 6 | james | 1 | 2015-09-12 |
| 11 | jams | 1 | 2011-09-12 |
| 8 | jjs | 1 | 2013-09-23 |
| 1 | kobe | 1 | 2018-11-27 |
| 5 | se | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 2 | wade | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
复制代码
- 指定排序方向
数据排序不限于升序(a-z), 升序是默认的排序方式, 同样我们也可以通过指定desc关键字来表示降序。
mysql> select name from person order by name desc;
+-------+
| name |
+-------+
| wade |
| sssw |
| se |
| kobe |
| jjs |
| jams |
| james |
| james |
| james |
| cup |
| ass |
+-------+
11 rows in set (0.00 sec)
复制代码
mysql> select * from person order by name desc, birth;
+-----+-------+-----+------------+
| sid | name | sex | birth |
+-----+-------+-----+------------+
| 2 | wade | 1 | 2013-09-12 |
| 7 | sssw | 1 | 2013-09-12 |
| 5 | se | 1 | 2013-09-12 |
| 1 | kobe | 1 | 2018-11-27 |
| 8 | jjs | 1 | 2013-09-23 |
| 11 | jams | 1 | 2011-09-12 |
| 4 | james | 1 | 2013-09-12 |
| 10 | james | 1 | 2013-11-12 |
| 6 | james | 1 | 2015-09-12 |
| 3 | cup | 1 | 2003-09-12 |
| 9 | ass | 1 | 2013-09-12 |
+-----+-------+-----+------------+
11 rows in set (0.00 sec)
复制代码
desc关键字只能应用到它前面到列名。如果你想对多个列进行降序排序那么你需要每个列指定desc。
order by 与 limit的顺序问题: limit子句要放在order by 后面。
mysql> select name from person order by name desc limit 4;
+------+
| name |
+------+
| wade |
| sssw |
| se |
| kobe |
+------+
4 rows in set (0.00 sec)
``
note: 明天继续更新。。。。复制代码