MySQL入门

仍沫
706 阅读13分钟

MySQL是一个开源的、关系型的数据库管理系统。“ MySQL”中的“SQL”代表“结构化查询语言”(Structured Query Language)， SQL是用于访问数据库的最常见的标准化语言。

本文主要通过基础MySQL教程进行学习，总结和整理出基础的一些知识点。以及进行一些小练习来加深对这些知识点的理解。知识点后附上的链接基本上是MySQL官方文档中对应的知识点详情。

安装MySQL

1、用brew安装MySQL：

brew install mysql

2、查看安装的MySQL的信息

brew info mysql

打印结果：

mysql: stable 8.0.21 (bottled)
Open source relational database management system
https://dev.mysql.com/doc/refman/8.0/en/
...
/usr/local/Cellar/mysql/8.0.21
...
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

根据这些信息，进行以下操作步骤。

3、在~/.zshrc文件中添加环境变量：

echo 'export PATH="/usr/local/Cellar/mysql/8.0.21/bin:$PATH"' >> ~/.zshrc

重新执行一下~/.zshrc文件：

source r ~/.zshrc

在终端输入mysql --version，会打印出:

mysql  Ver 8.0.21 for osx10.14 on x86_64 (Homebrew)

这就是刚才新安装好的MySQL。

3、执行以下语句启动服务器：

mysql.server start

4、执行以下语句设置root用户的密码：

mysql_secure_installation

根据提示设置密码，选择了什么等级的密码就要按照要求设置相应的密码强度：

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:

5、连接数据库：

mysql -uroot -p

6、和数据库断开连接：

使用Control + D，或者输入QUIT，或者输入\q。

================ 下载MySQL工作台 ================

下载地址：dev.mysql.com/downloads/w…

要注意选择自己电脑能支持的版本下载。(直接点No thanks, just start my download.，就可以了，不用注册)

schema等同于 database。

查询基本原则

1、查询通常由一个SQL语句和一个分号组成。

2、发出查询之后，mysql会把查询发送到服务器执行并显示结果，然后打印出mysql>表示已经准备好进行另一个查询了。

3、mysql以表格形式显示查询的输出。第一行包含各列的标签，以下行显示查询结果。通常，列标签是从数据库中拿到的列名称。如果要检索表达式而不是表的列的值，mysql使用表达式本身来标记该列。

4、mysql显示返回了多少行以及查询执行了多少时间。

5、mysql不区分大小写。

6、可以在一行写多个语句。也可以在多行写一个语句，因为一个语句是以分号标明结束的。

7、如果在输入的过程中不想执行这个查询了，输入\c取消该查询。

创建和使用数据库

1、查看数据库

使用SHOW查看服务器当前有哪些数据库。

show databases;

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

SHOW DATABASES不会展示你没有权限的数据库。

2、创建数据库

(1) 最常用的语句：

create database [if not exists] database_name;

if not exists是可选的，如果加上，表明只在数据库database_name不存在的时候创建数据库，在创建重复名称的数据库的时候会给出警告:

1 row(s) affected, 1 warning(s): 1007 Can't create database 'db1'; database exists

如果不加上，在创建重复名称的数据库的时候会给出报错：

Error Code: 1007. Can't create database 'db1'; database exists

(2) 根据文档内容，在Unix操作系统下，SQL关键字是不区分大小写的，但是数据库名和表名是区分大小写的。试了下，在macOS操作系统下，数据库名也是不区分大小写的(执行create database db1;之后再执行create database DB1;会报错)。

(3) 创建了数据库之后使用use db1选择db1作为当前数据库。

(4) 也可以在启动数据库的时候，直接选择数据库：

mysql -h host -u user -p db1

(5) 比较完整的声明数据库的方式：

create database [if not exists] database_name
[character set charset_name]
[collate collation_name]

在创建的时候为新的数据库声明字符集和校对 。如果没有声明，就会使用默认的字符集和校对。

3、检查创建的数据库

show create database database_name;

执行这个指令会打印出如下内容：

mysql> show create database db1;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

表明创建的数据库db1使用了默认的utf8mb4字符集，以及默认的utf8mb4_0900_ai_ci校对方式，以及不启用数据库加密。

Character Sets, Collations, Unicode 、InnoDB Data-at-Rest Encryption。

4、访问数据库

使用USE访问数据库。USE和QUIT一样，都不需要以分号结尾。USE语句必须写在单独的一行中。

use sys

执行这一句后，会选择sys作为当前数据库。

5、查看当前被选择的数据库

使用 DATABASE() 函数:

select database();

6、删除数据库

删除数据库会删除数据库中包含的所有表以及数据库本身。

drop database [if exists] database_name;

if exists是可选的，表示只在该数据库存在的时候才删除它。删除一个不存在的数据库时，加上if exists会给出警告(Warning)，不加if exists会给出报错(Error)。

因为在MySQL中，schema和database是同义词，所以可以像下面这样使用：

drop schema [if exists] database_name;

7、设置权限

使用GRANT设置权限。

grant all on db_name.* to 'user_name'@'host_name';

以上语句允许用户名为user_name的用户访问(拥有增删改查等所有权限)主机host_name中的数据库db_name下的所有表。

8、数据库引擎

从版本5.5开始，MySQL使用InnoDB作为它的默认存储引擎。InnoDB支持事务、外键、回滚等。

Storage Engines。

创建和使用表

1、创建表

使用CREATE TABLE在一个数据库中创建一个表：

create table [if not exists] table_name (
  column_1_definition,
  column_2_definition,
  ...,
  table_constraints
) engine = stroage_engine;

使用engine = stroage_engine;声明表的存储引擎。

列定义部分是：

column_name data_type(length) [not null] [default value] [auto_increment] column_constraint;
  • column_name是列的名字。
  • data_type(length)是列的数据类型（Data Types）和可选的尺寸。比如VARCHAR(10)能存放长度为10的字符串。注意这里的10是字符的数量，而不是字节的数量。
  • NOT NULL约束这个列不包含NULL。列可能还包含其他限制，比如 CHECK, 和 UNIQUE。
  • DEFAULT为列声明了一个默认值。
  • AUTO_INCREMENT 表明列的值是在一个新行被插进表中的时候自动递增的。每个表最多只能有一个AUTO_INCREMENT的列。

在列列表的后面，可以定义表限制，比如 UNIQUE、 CHECK、 PRIMARY KEY 和 FOREIGN KEY。

================ 小练习 ================

① 创建一个表

create table if not exists table1 (
  a int auto_increment primary key,
  b varchar(100) not null,
  c date
);

a列是自增的整型数据，是表的主键的列。a列的值在表中的所有行中是独一无二的，不可能同时出现a列的值为1的行。

② 查看表的结构

describe table1;

关于VARCHAR(10)中的10是字符数量而不是字节数量，使用下面的sql就能证实：

mysql> create table if not exists x_table ( x varchar(3) );
mysql> insert into x_table values ("abc"), ("一二三");

image.png

2、修改表

2.1 新增列

(1)、添加列到表中

alter table table_name
  add new_column_name column_definition
  [first | after column_name]

first表明作为第一列，after column_name表明在某列的后面。

(2)、添加多个列到一个表中

alter table table_name
  add new_column_name column_definition
  [first | after column_name],
  ...
  add new_column_name column_definition
  [first | after column_name];
================ 小练习 ================

新增两列d、e到表中：

alter table table1
  add d int not null after c,
  add e int after d;

使用describe table1;查看表结构：

2.2 修改列

(1)、修改一列

alter table table_name
  modify column_name column_definition
  [first | after column_name]

(2)、修改多列

alter table table_name
  modify column_name column_definition
  [first | after column_name],
  ...
  modify column_name column_definition
  [first | after column_name];

后续的alter table语句（比如drop column）都和这个类似，删除一句的时候只写一个drop column，删除多句的时候，写逗号分隔的多句drop column。

================ 小练习 ================

把e列改为varchar类型：

alter table table1
  modify e varchar(100) not null first;
    
describe table1;

2.3 重命名列

alter table table_name
  change column original_name new_name column_definition
  [first | after column_name];

change column original_name new_name column_definition修改列的名称和定义。

[first | after column_name]决定列的新位置。

================ 小练习 ================

把d改成允许数据为空的名为d1的列，并且放到a列的后面：

alter table table1
  change d d1 int default null after a;

describe table1

2.4 删除列

alter table table_name
  drop column column_name;

2023年我再按照这种方式删除列，报了语法错误，可能是MySQL有过升级，查看了下用的MySQL的版本是mysql  Ver 8.0.21 for osx10.14 on x86_64 (Homebrew)。把 column字段去掉之后才能正常执行列删除。

alter table 表名 drop 列名;
================ 小练习 ================

把d1列删除：

alter table table1
  drop column d1;

describe table1;

2.5 重命名表

alter table table_name
  rename to new_table_name;

或者：

rename table old_table_name to new_table_name;
================ 小练习 ================

将表table1的名称改为table2：

alter table table1
  rename to table2;

show tables;

再把table2的名称改为table3：

rename table table2 to table3;

show tables;

3、删除表

drop table [if exists] table_name;
================ 小练习 ================

① 先按照表table3复制一些表出来用于后续删除：

create table table1 like table3;
create table table2 like table3;

show tables;

② 把tale2和table3删掉：

drop table if exists table2, table3;

show tables;

4、删除表中的所有数据

TRUNCATE TABLE删除表中的所有数据，它会删除然后重新创建该表，而不是一行一行地把行删除。

truncate [table] table_name;
  • 如果有FOREIGN KEY约束，那么TRUNCATE TABLE会失败。
  • 删除操作不能回滚。
  • 如果有AUTO_INCREMENT列，会重置它的值。
  • TRUNCATE TABLE语句影响的行数是0。

修改MySQL中的数据

1、插入数据

使用INSERT语句插入一行或者多行到一个表中。

插入一行到表中：

insert into table_name(column1, column2, ...)
values (value1, value2, value3);

插入多行到表中：

insert into table_name(column1, column2, ...)
  values 
  (value_11. value_12, value_13),
  ...
  (value_n1. value_n2, value_n3);

列的数量和值的数量必须一样，列的位置和值的位置必须对应。

================ 小练习 ================

往table1中插入两行数据：

insert into table1(a, b, c, e)
  values
  (1, '第一行', '2021-05-30', 'e列的数据'),
  (2, '第二行', '2021-05-30', 'e列的数据2');

select * from table1;

2、更新数据

使用UPDATE语句更新一个表中的数据，它改变一行或者多行的一个或者多个列的值。

update [low_priority] [ignore] table_name
  set
    column_name1 = expression1,
    column_name2 = expression2,
    ...
[where condition];
  • set分句中声明要更新的列以及新的值。
  • where分句声明要更新那些列。
  • 使用low_priority修饰符会使操作延迟，直到没有其他客户端在从表中读数据。字面意思来看就是表明这个操作的优先级比较低，先执行完其他操作再来执行这句操作。
  • ignore修饰符允许在错误发生的时候语句依然执行。
================ 小练习 ================

把刚才第一行e列的数据更新为第一行e列的数据：

update table1
    set
    e = '第一行e列的数据'
where a = 1;

select * from table1;

3、删除数据

delete from table_name
[where condition];

where分句是可选的，如果不声明where分句，delete语句会删除表中的所有行。

delete语句会返回删除的行的数量。

================ 小练习 ================

删除table1表中，a列为2的行。

delete from table1
where a = 2;

select * from table1;

4、查询数据

使用SELECT语句查询数据。

select select_list
from table_name;

select_list是要查询的列或者表达式的列表。

MySQL先评估from分句，再评估select分句。

评估顺序：from -> select。

================ 小练习 ================

从表table1中选择a列的数据：

select a from table1;

5、排序数据

使用ORDER BY修饰符排序数据。

select select_list
from table_name
order by
    column_1 [asc | desc],
    ...
    column_n [asc | desc];

ASC 表示升序(ascending)， DESC 表示降序(descending)。

如果不明确声明ASC或者DESC的话，ORDER BY默认使用的是升序。

在通过多列来排序的时候，ORDER BY会按照从上到下的顺序进行排序。

评估顺序：from -> select -> order by

================ 小练习 ================

① 先插入一点数据到表中：

insert into table1(a, b, c, e)
    values
    (2, '第二行', '2021-05-30', 'e列的值'),
    (3, '第三行', '2021-05-30', 'e列的值'),
    (4, '第四行', '2021-05-30', 'e列的值'),
    (5, '第五行', '2021-05-30', 'e列的值');

② 查找按照a列降序排序的数据：

select a, b from table1
order by a desc;

6、过滤数据

使用WHERE分句过滤数据。

select select_list
from table_name
where search_condition;
================ 小练习 ================ 
select * from table1
where a >= 3;

交互模式和批处理模式

1、交互模式

在命令行直接来输入mysql -uroot -p，输入密码后会进入交互模式。

mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

mysql>

以下是几个提示的意义，除了mysql>之外，其他都表示有未完成的内容，需要等待下一行。比如'>表示前面输了一个只包含开始的引号'，没有包含结束引号的字符串，需要等结束的引号'输入。

PromptMeaning
mysql>已经准备好，可以进行新的查询了
->等待一个多行查询的下一行
'>等待以单引号(')开头的字符串的完成的下一行
">等待以双引号(")开头的字符串的完成的下一行
`>等待以(`)开头的标志符的完成的下一行
/*>等待以/*开头的注释的完成的下一行

2、批处理模式

把要运行的语句放在一个文件中，然后告诉mysql从文件中读取输入。可以选择以下两种方式中的一种。

(1) 在终端执行：

mysql < file_name

(2) 在mysql提示下使用source或者\.指令：

mysql> source file_name;
mysql> \. file_name
================ 小练习 ================

① 在00.sql文件中添加以下内容：

use db1;
select database();

② 执行:

mysql -uroot -p < 00.sql

输入密码点击确认之后得到以下结果：

database()
db1

③ 使用mysql -uroot -p先进入交互模式，输入source 00.sql;或\. 00.sql得到的结果为：

mysql> source 00.sql;
Database changed
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

mysql> \. 00.sql
Database changed
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)
avatar
文章
阅读
粉丝
相关推荐
MySQL索引
329阅读
 · 
3点赞
Kubernetes 部署高可用 RocketMQ 集群
6.4k阅读
 · 
8点赞
手把手教你MySQL查询优化分析
12k阅读
 · 
218点赞
MySQL基础篇
528阅读
 · 
7点赞
MySQL 全局锁、表级锁、行级锁，你搞清楚了吗？
904阅读
 · 
17点赞
Redis应用-Geo
3.5k阅读
 · 
14点赞
MySQL数据库学习总结性思维导图和相关文章分享
3.1k阅读
 · 
0点赞
面试官：谈谈 MySQL 联合索引生效、失效的条件？
504阅读
 · 
0点赞
LVS+Keepalived+Nginx高可用实现
2.2k阅读
 · 
11点赞

友情链接：