MySQL的简单使用(一)

2020-11-20 本文已影响0人哆啦_

下载MySQL
终端执行MySQL命令
1. 会发现 command not found: mysql
2. 因为MySQL没有添加到环境变量中（如果想要在任意地方命令得到执行必须把命令添加到环境变量中）
3. Mac添加环境变量：终端执行 export PATH=$PATH:/usr/local/mysql/bin

终端连接MySQL

mysql -uroot -p********

-u是user的缩写，后面跟着登录的用户名，这里是root（可以没有空格）
-p是password的缩写后面跟着的是密码（可以没有空格）

或者可以mysql -uroot -p 然后回车，再输入密码

常见命令

show databases; 展示所有的数据库（默认会有四个数据库）

// 默认数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

information_schema：信息数据库。其中包括MySQL在维护的其他数据库、表、列、访问权限等信息

performance_schema：性能数据库。记录着MySQL Server数据库引擎在运行过程中资源消耗相关的信息

mysql：用于存储数据库管理者的用户信息、权限信息以及日志信息等

sys：相当于是建议版的performance_schema，将性能数据库中的数据汇总成更容易理解的形式；

create database 名称;:创建数据库

select database();：查看当前使用的数据库，没有选择的话就是NULL

+------------+
| database() |
+------------+
| NULL       |
+------------+

use 数据库名;：使用选择的数据库

show tables;：查看当前数据库中有哪些表

create table users(
    -> name varchar(10),
    -> age int,
    -> height double);

创建一张表，并指定表中字段以及类型。这里创建一个名为users的表，表中字段有name，age以及height。

select * from 表名; 查询表中所有的数据

insert into 表名 (字段1,字段2，...) vaules (vaule1,value2, ...);：插入数据

insert into users (name, age, height) values ('lilei', 20, 1.88);

GUI工具

使用终端操作数据库有很多不方便的地方，比如语句没有高亮、没有任何提示、格式不美观，容易出现错误等等。所以在开发中可以使用一些GUI工具

常见的MySQL的GUI工具有很多，比如：Navivat（收费），SQLYog（免费），TablePlus（常用功能都可以使用，但有一些限制）

这里使用的是Navicat，下载安装之后，点击左上角的连接，选择MySQL，创建一个连接

image.png

创建之后双击我们创建的连接名，就可以连接到数据库。可以看到我们已经有的数据库。

SQL语句

跟数据库进行沟通的语言就是SQL。SQL是Structured Query Language的缩写，称之为结构化查询语言。使用SQL编写的语句就是SQL语句，SQL语句可以用于对数据库进行操作。

常见的关系型数据库SQL语句都是比较类似的。所以学习了MySQL之后再去学习Oracle或者其他关系型数据库也是很方便的。

SQL语句的常用规范：

* 通常关键字是大写的，比如`CREATE`、`TABLE`、`SHOW`等
* 一条语句结束后需要以`;`分号结尾
* 如果关键字作为表名或者字段名，需要使用``进行包裹。

SQL语句的分类

常见的SQL语句可以分为四类：

DDL（Data Definition Language）：数据定义语言
- 可以通过DDL语句对数据库或者表进行：创建、删除、修改等操作
DML（Data Manipulation Language）：数据操作语言
- 可以通过DML语句对表进行：添加、删除、修改等操作
DQL（Data Query Language）：数据查询语言
- 可以通过DQL从数据库中查询记录
DCL（Data Control Language）：数据控制语言
- 对数据库、表的权限进行相关访问控制操作

数据库的操作

image.png

字符集：代表存到数据库中的数据以什么样的编码存储，默认为utf8mb4。但不能正常存储emoji表情。

排序规则：ai表示排序的时候不区分轻重音，as的话表示区分轻重音。ci表示不区分大小写，cs表示区分大小写。

比如创建数据库时可以指定对应的编码和排序规则（不常用）：

CREATE DATABASE IF NOT EXISTS huya DEFAULT CHARACTER SET utf8mb4
                COLLATE utf8mb4_0900_ai_ci;

修改数据库的编码(COLLATE是排序规则)：

# 修改数据库的编码 
ALTER DATABASE bili CHARACTER SET = utf8 
                COLLATE = utf8_unicode_ci;

MySQL的数据类型

MySQL支持的数据类型有:数字类型，日期和时间类型，字符串(字符和字节)类型，空间类型和 JSON数据类型。

MySQL支持的数据类型有：数字类型，日期，时间类型，字符串(字符和字节)类型，空间类型和JSON数据类型

数字类型

MySQL数字类型有很多。

整数类型有：INTEGER，INT，SMALLINT，TINYINT，MEDIUMINT，BIGINT

Type	Storage (Bytes)	Minimum Value Signed	Maximum Value Signed	Maximum Value Unsigned
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-2⁶³	2⁶³-1	2⁶⁴-1

浮点数字类型：FLOAT,DOUBLE(FLOAT是4个字节，DOUBLE是8个字节)

精确数字类型:DECIMAL，NUMERIC (DECIMAL是NUMERIC的实现形式);

日期类型

日期和时间类型有：DATE, TIME, DATETIME, TIMESTAMP, YEAR

DATETIME和TIMESTAMP都是以格式YYYY-MM-DD hh:mm:ss显示值，他们的主要区别在于DATETIME支持的范围是1000-01-01 00:00:00到 9999-12-31 23:59:59；TIMESTAMP数据类型的范围是UTC时间：1970-01-01 00:00:01到2038-01-19 03:14:07

表约束

主键：PRIMARY KEY

主键是表中唯一的索引。永远不会重复
并且必须是NOT NULL的，如果没有设置NOT NULL，那么MySQL也会隐式的设置为NOT NULL

主键也可以是多列索引，PRIMARY KEY(key_part, ...), 一般称之为联合主键或者复合主键

CREATE TABLE IF NOT EXISTS moment_label (
  moment_id INT NOT NULL,
  label_id INT NOT NULL,
  // 联合主键
  PRIMARY KEY(moment_id, label_id)
);

唯一：UNIQUE

唯一性，使用UNIQUE约束的字段在表中必须是不同的
对于所有的引擎，UNIQUE索引允许NULL包含的列具有多个NULL值，也就是NULL值可以重复

默认值：DEFAULT

为某个字段设置默认值

自动递增：AUTO_INCREMENT

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT, # id设置为主键，并且是递增的 
    name VARCHAR(20) NOT NULL, # name设置为可变字符类型，并且长度在20以内，且不能为空 
    age INT DEFAULT 0, # age设置为INT类型 默认为0 
    phoneNum VARCHAR(20) UNIQUE DEFAULT '', # 手机号为20个字符以内的 唯一 且默认为‘’
    createTime TIMESTAMP
)

# 删除数据表
DROP TABLE users;
DROP TABLE IF EXISTS users;

修改表

修改表的名字

语法：ALTER TABLE 表名 RENAME TO 新表名;

// 将数据表users改名为user
ALTER TABLE `users` RENAME TO `user`;

添加新的列

语法：ALTER TABLE 表名 ADD 新列的名类型;

# 2.添加新的列 在user表中新增TIMESTAMP类型的updateTime字段 
ALTER TABLE `user` ADD `updateTime` TIMESTAMP;

修改字段的名字

语法：ALTER TABLE 表名 CHANGE 旧字段名新字段名新字段的类型;

# 3.修改字段名称 将user表中的phoneNum字段名修改为telphone 类型为 VARCHAR(20)
ALTER TABLE `user` CHANGE `phoneNum` `telPhone` VARCHAR(20);

修改字段的类型

语法：ALTER TABLE 表名 MODIFY 字段名新的字段类型;

# 4.修改字段的类型 将user表中的name字段的类型修改为VARCHAR(30)
ALTER TABLE `user` MODIFY `name` VARCHAR(30);

删除字段

语法：ALTER TABLE 表名 DROP 要删除的字段;

# 5.删除字段 删除user表中的age字段
ALTER TABLE `user` DROP `age`;

根据表的结构创建另一张表

语法：CREATE TABLE 新表名 LIKE 表名

只会复制表的结构，而不会把表的内容复制到新创建的表

# 根据user表的结构创建表user2
CREATE TABLE `user2` LIKE `user`;

根据表的内容创建另一张表

语法：CREATE TABLE 新表名 AS(可省略) (SELECT * FROM 表名);

# 根据user表中的内容创建user3表
// CREATE TABLE `user3` AS (SELECT * FROM `user`);
CREATE TABLE `user3` (SELECT * FROM `user`);

DML-对数据库的增删改

插入数据

语法：INSERT INTO 表名 (字段1,字段2,...) VALUES (value1,value2,...)

# 向user表中插入数据
INSERT INTO user VALUES (110,'lwy','15665431234','2020-11-11','2020-12-12');
# 向user表中插入对应字段的数据
INSERT INTO user (name,telPhone,createTime,updateTime) 
                        VALUES ('zhangsan','020-123456','2020-11-12','2020-12-13');
                        
INSERT INTO user (name, telPhone) 
                        VALUES ('lisi','030-213456');

设置字段的默认值

# createTime 默认为创建时的时间 
ALTER TABLE user MODIFY createTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
# updateTime 默认为创建时间 在修改时自动设置修改时间 
ALTER TABLE user MODIFY updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
INSERT INTO user (name, telPhone) 
                        VALUES ('lris','050-213456');

删除数据

语法：DELETE FROM 表名; DELETE FROM 表名 WHERE 条件;

# 删除user表中所有的数据 
DELETE FROM user;
# 删除user表中id为110的数据
DELETE FROM user WHERE id = 110;

更新数据

# 更新所有的数据 
UPDATE user SET name = '李四', telPhone = '012-12345600';
# 更新符合条件的数据 
UPDATE user SET name = '李四', telPhone = '012-12345600' WHERE id = 111;

DQL语句

DQL：Data Query Language(数据查询语言)

SELECT用于从一个或者多个表中检索选中的行

官方文档介绍的很详细。

基本查询

# 1.基本查询 
# 查询表中所有字段和所有数据 
SELECT * FROM products;
# 查询指定的字段 
SELECT title, price FROM products;
# 对字段结果起别名 
SELECT title AS phoneName, price AS currentPrice FROM products;

WHERE查询条件

基本查询

# WHERE后面是查询条件 
SELECT * FROM products WHERE price = 1000;
SELECT title, price FROM products WHERE price > 1000;
# 下面两种写法是一样的 都是不等于 
SELECT * FROM products WHERE price != 1000;
SELECT * FROM products WHERE price <> 1000;

逻辑运算符

# 案例一：价格1000到2000的手机 
SELECT * FROM products WHERE price > 1000 AND price < 2000;
SELECT * FROM products WHERE price > 1000 && price < 2000;
# BETWEEN AND包含等于的情况 
SELECT * FROM products WHERE price BETWEEN 1000 AND 2000;


# 逻辑或 
SELECT * FROM products WHERE price > 5000 || brand = '华为';

# 查询一个值是否为NULL 
# 查询url为NULL的数据 
SELECT * FROM products WHERE url IS NULL;
# 查询url不为NULL的数据 
SELECT * FROM products WHERE url IS NOT NULL;

模糊查询

模糊查询使用LIKE关键字，结合两个特殊的符号：

%表示匹配任意个(0个或多个)的任意字符
_表示匹配一个的任意字符；

# 查询 title中有M的数据 
SELECT * FROM products WHERE title LIKE '%M%';
# 查询title中第二个字母是P的数据 
SELECT * FROM products WHERE title LIKE '_P%';

IN：取多个值中的一个即可

SELECT * FROM products WHERE brand = '华为' || brand = '小米' || brand = '苹果';
# 这句跟上面的作用相同 
SELECT * FROM products WHERE brand IN ('华为','小米','苹果');

排序

通过ORDER BY关键字进行排序

ASC: 升序

DESC：降序

# 查询结果的排序 
# ASC: 升序 DESC：降序 
# 查询的结果根据价格的升序排序 
SELECT * FROM products WHERE brand IN ('华为','小米','苹果') ORDER BY price ASC;
# 查询的结果以价格的升序排序 价格相同时以score的降序排序 
SELECT * FROM products WHERE brand IN ('华为','小米','苹果') ORDER BY price ASC, score DESC;

分页查询

LIMIT查询的数据，OFFSET:偏移量

语法：LIMIT limit OFFSET offset

或者可以 LIMIT offset,limit

SELECT * FROM products LIMIT 20 OFFSET 0;
# 偏移量0 长度为20 等同于上面的
SELECT * FROM products LIMIT 0,20;

聚合函数

聚合函数表示对值集合进行操作的组(集合)函数

默认情况下，聚合函数相当于是将整张表的数据看作一组数据，对这一组数据进行某些操作使用的函数就是聚合函数。

1.聚合函数的使用

 # 1.1 求所有手机价格的总和 
SELECT SUM(price) FROM products;
## 设置名称 
SELECT SUM(price) AS totalPrice FROM products;
## AS可以省略 
SELECT SUM(price) totalPrice FROM products;

2.GROUP BY

GROUP BY通常和聚合函数一起使用。表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算;

按照品牌来分组计算对应的平均价格数量平均评分

SELECT AVG(price), COUNT(*), AVG(score) FROM products GROUP BY brand;

结果如下：

但可以看出来并没有显示对应的品牌名称，如果要显示对应的品牌名称

SELECT brand, AVG(price), COUNT(*), AVG(score) FROM products GROUP BY brand;

需要注意的是，因为是根据brand进行分组(GROUP BY)的，所以SELECT后面跟着的是brand，而不能是title等其他的字段。当然brand和AVG(price)等的顺序可以自由调节

HAVING的使用
如果要对分组之后的数据再进行筛选，需要使用HAVING语句，而不是WHERE

# 3.1 按照品牌来分组计算对应的平均价格 数量 平均评分之后 筛选出其中平均价格大于2000的数据 
# 可能会在结果后面跟上WHERE筛选条件 这样是语法错误的 
# SELECT brand, AVG(price) avgPrice, COUNT(*), AVG(score) FROM products GROUP BY brand WHERE avgPrice > 2000;
# 应该是 
SELECT brand, AVG(price) avgPrice, COUNT(*), AVG(score) FROM products GROUP BY brand HAVING avgPrice > 2000;

WHERE和HAVING的区别：

WHERE是用来筛选数据表中的某些条件的，HAVING是对分组(GROUP BY)之后的数据进行筛选。也就是WHERE是跟在表的后面，作用于表，HAVING是跟在GROUP BY后面，作用于GROUP BY分组之后的。

Github

MySQL的简单使用(一)

终端连接MySQL

常见命令

GUI工具

SQL语句

SQL语句的分类

数据库的操作

MySQL的数据类型

数字类型

日期类型

表约束

修改表

修改表的名字

添加新的列

修改字段的名字

修改字段的类型

删除字段

根据表的结构创建另一张表

根据表的内容创建另一张表

DML-对数据库的增删改

插入数据

删除数据

更新数据

DQL语句

基本查询

WHERE查询条件

排序

分页查询

聚合函数

1.聚合函数的使用

2.GROUP BY

猜你喜欢

热点阅读