Mysql

Mysql补全计划(一)

前言

MySQL作为一个开源流行的数据库，是我们必不可少的学习工具，此系列是基础到进阶再到一些高级的操作的教程。

启动与连接

在正确安装Mysql后，首先要确保mysql服务在正常运行，在cmd命令行中，可以快捷的启动mysql的服务

1
2
3
4

# 启动服务
net start mysql
# 停止服务
net stop mysql

启动后，我们还需要进行连接，这里的连接实际上都是根据网络来进行连接的，在cmd控制台中，可以根据以下格式去进行连接

1

mysql -h[ip] -p[port] -u root -p
  • 如果是本地(localhost)，那么在启动的时候不用带上ip和port这两个参数
  • -u的意思是，你作为哪个用户进行登录
  • -p的意思是，登录的时候需要指定密码(password)

例如，我们要登录本地的mysql,就可以这样写

1

mysql -uroot -p3306

也可以这样登录

1

mysql -h127.0.0.1 -p3306 -uroot -p

SQL

DDL

数据定义语言。

创建数据库

  • 查询所有数据库

    1

    show databases;

  • 查询当前所使用的数据库

    1

    select database();

  • 创建数据库

    一般的，最简单的创建数据库的语句是

    1

    create database [database_name];

    但是实际上，这里还可以存在着一些额外的判断条件，例如，如果想要创建的数据库与已经存在的一个数据库名称相同，那么就会发生错误，我们可以使用if not exist来进行判断

    例如，创建一个名字叫做test1的数据库

    1

    create database if not exists test1;

    此外，我们还可以指定创建数据库时所使用的字符集。

    1

    create database if not exists test1 default charset utf8mb4;

    utf8mb4是最常见的字符集，这个字符集支持的字符特别多，甚至还包含了emoji。

    而另外一个常见的参数就是比较规则，实际上，常用的排序规则有两个

    • utf8mb4_general_ci：更快，但对某些字符排序不够精细。

    • utf8mb4_unicode_ci：更准确，但略慢。

    所以，一个更加完整的写法是

    1
2
3

    create database if not exists test1
default charset utf8mb4
default collate utf8mb4_general_ci;

    在开发环境中，一个更加清楚的SQL语句是一个好的习惯。

删除数据库

删除数据库的关键字是drop，一般的，删除一个数据库可以这样写

1

drop database test1;

同理，如果这个数据库不存在，那么删除也会出现错误，可以使用If exists进行判断

1

drop database if exists test1;

查看表结构

查看表的结构是一个常用操作，这里有两种方式

  • desc

    1

    desc [table_name];

    例如，查看表user的字段结构，可以使用desc user。

  • show columns from [table_name]

    1

    show columns from user;

推荐desc，简单好记！

查询指定的建表语句

可以使用show create table [table_name]这个关键字来查看创建表的时候的sql语句

1

show create table user;

数值类型

常见的数据类型就是那几大类，整形、浮点型、字符串型还有时间类型，更加细分的来说，可以分为下面的几种。可以参考c语言的各种数据类型

整形

  • tinyint,有点像char类型，1 byte，范围是[-128, 127]，
  • smallint,类比short类型，2 bytes
  • mediumint，3 bytes
  • int，类比int, 4 bytes
  • bigint,类比long,8 bytes

浮点数

float，4 bytes

double, 8 bytes

decimal, 其大小依赖于精度，注意，所有与钱有关的数据都应该使用decimal来使用。

字符串类型

image-20250614173818249

常用的数据类型有char varchar text

日期时间相关

image-20250614174044231

其中，比较有趣的是时间戳类型，当我们向表里面插入字段时，使用时间戳可以帮助我们记录下来这个数据的插入时间

1
2
3
4
5
6

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 使用当前时间
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

修改表字段

修改表内字段的排列顺序、数据类型和新建字段这些操作都是与表修改有关的操作，这些修改的字段都有一个公共前缀

1

alter table [table_name] ....

新添字段

使用add来进行添加操作

例如，要向user表中添加一个新的字段，例如height身高字段。

1

alter table user add height double

修改数据

修改数据所使用的关键字是update，例如

1

update user set name = xxx, age = xxx, .... where xxx = xxx;(后面是过滤条件)

建表语句

创建表的关键字是create，例如，要创建一个user表

1
2
3
4

create table user(
	name varchar(50),
    age int
);

建表时，一个很关键的内容就是去添加约束，下面来根据一个具体的例子，来看看怎么添加约束。

外键约束

为了防止误导，这里在建表时就添加上约束。

首先考虑一种情形，即用户和用户所在的部门的这种情形

  • 员工表应当包含员工的各种信息，像姓名、年龄、所在的职业、入职时间和所在的部门等
  • 部门应该包含部门的名称

也就是说，部门表和员工表这两个看起来没什么关系，但是，如果我们不加以外键约束，那么设想一种情况，当解散某个部门时，首先从部门表里面删除这个部门信息，如果不添加外键约束，那么删除部门表的操作并不会影响到员工表，但是员工表中还包含有部门信息。

首先来创建这两张表

  • 员工表

    1
2
3
4
5
6
7

    CREATE TABLE User(
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    job varchar(20) comment '职位',
    dept_id int comment '部门id'
);

  • 部门表

    1
2
3
4

    CREATE TABLE dept(
	id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

例如，我们要执行一个删除操作

1

delete from dept where dept_id = 1;

此时，由于外键存在约束，不允许我们删除。

外键约束可以在建表时添加，也可以在建表后补充添加。

1
2
3
4
5
6

creat table User(

    ......
    constraint fk_user_dept_id foreign key (dept_id) renferences dept (id);

);

建表后添加

1

alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept (id);

可以这样理解

  • 外键是一种约束，constraint
  • user表的dept_id是外键
  • 这个外键是对dept表的id字段的引用。

同理，可以使用下面的命令来删除外键

1

alter table user drop foreign key fk_user_dept_id;

删除更新操作

如果真的要删除数据呢？

image-20250614113727520

这个时候，还可以再添加一些约束。

如果我们想要达成，删除部门时，也可以删除其它员工的操作，就可以在设置外键的时候设置为Cascade。

1

alter table user add constraint fk_user_dept_id foreign key (dept_id) references dept (id) on update cascade on delete cascade;

管理用户

在之前登入数据库时，我们都是使用root用户进行登录的，众所周知，root权限具有所有权限，有时候我们不希望有一些删库跑路的操作，这时，就可以对外提供一些用户。

查询用户 

1

select * from mysql.user;

查询当前所有的用户，不过这个结构展示的东西太多了，我们可以先查看两个字段，一个是Host和User。

1
2
3
4
5
6
7
8
9

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

可以看到，当前用户就一个，就是root用户。其它都是一些权限。

1
2
3
4
5

+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N           
........

创建用户

  • 创建用户

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

    create user '用户名'@'主机名' identified by '密码';
mysql> create user 'zxp'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | zxp              |
+-----------+------------------+

  • 查看当前登录的用户

    1
2
3
4
5
6
7

    mysql> select user();
+---------------+
| user()        |
+---------------+
| zxp@localhost |
+---------------+
1 row in set (0.00 sec)

  • 修改用户的密码

    注意是root用户

    1

    alter user 'zxp'@'localhost' identified with mysql_native_password by '新密码';

    注意注意，这只是创建用户，用户的权限都还没有被分配。

    Tips

    在很多时候，我们需要远程连接主机，此时，就需要匹配远程主机

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | zxp              |
+-----------+------------------+

    host的配置意味着可以连接的ip地址，这里默认都是localhost，你可以配置为自己常用的ip地址，这样就可以远程登录主机

    此外，还可以使用%来进行统配匹配，%意味着任意ip地址的主机都可以连接。

用户授权

  • 权限控制

    image-20250614211824827

    • 查询权限

      可以查询root用户所有的权限。

      1

      show grants for '用户名'@'主机名';

    • 授权

      可以授予各种权利。

      1

      grant 权限 on 数据库名.表名 to '用户名'@'主机名';

      例如，可以向zxp授予test1.user的增删改查能力。

      1
2
3
4
5
6
7
8

      mysql> use test1
Database changed
mysql> create table user(
    -> id int auto_increment primary key,
    -> name varchar(50),
    -> age int
    -> );
Query OK, 0 rows affected (0.03 sec)

      使用zxp进行登录

      1

      mysql -p3307 -uzxp -p

      登录进来后，发现并没有database

      1
2
3
4
5
6
7
8

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

      这是因为root没有给用户授权。我们可以授予zxp各种权限

      1
2

      mysql> grant select on test1.* to 'zxp'@'localhost';
Query OK, 0 rows affected (0.01 sec)

      重新登录，发现显示了test1

      1
2
3
4
5
6
7
8
9

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

      我们可以查询一下试试。

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

      mysql> use test1;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from user;
Empty set (0.00 sec)

      可以看到查询不出数据(这是因为还没有插入数据)。

      使用root添加一些数据

       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

      mysql> insert into user(name, age) values ('zxp1', 18), ('zxp2', 19), ('zxp3', 20);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zxp1 |   18 |
|  2 | zxp2 |   19 |
|  3 | zxp3 |   20 |
+----+------+------+
3 rows in set (0.00 sec)

      此时再在zxp中查询一下

      1
2
3
4
5
6
7
8
9

      mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | zxp1 |   18 |
|  2 | zxp2 |   19 |
|  3 | zxp3 |   20 |
+----+------+------+
3 rows in set (0.00 sec)

      我们可以尝试一下使用zxp用户进行插入数据

      1
2

      mysql> insert into user(name, age) values ('zxp4', 22), ('zxp5', 23);
ERROR 1142 (42000): INSERT command denied to user 'zxp'@'localhost' for table 'user'

      可以看到操作直接被拒绝了，一般的，可以给用户查询、新增、更新的权限，而不给与删除权限。

      1
2
3
4
5
6
7
8

      mysql> show grants for 'zxp'@'localhost';
+-----------------------------------------------------------------------+
| Grants for zxp@localhost                                              |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `zxp`@`localhost`                               |
| GRANT SELECT, INSERT, UPDATE, ALTER ON `test1`.* TO `zxp`@`localhost` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

      此时，我们再使用zxp去更新数据试一下

      1
2
3

      mysql> insert into user(name, age) values ('zxp4', 22), ('zxp5', 23);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

      芜湖！更新成功了！

      来试一下删除时会怎么样！

      1
2

      mysql> delete from user where id = 1;
ERROR 1142 (42000): DELETE command denied to user 'zxp'@'localhost' for table 'user'

      可以看到不允许删除

      试一下删除整张表试一试

      1
2

      mysql> drop table user;
ERROR 1142 (42000): DROP command denied to user 'zxp'@'localhost' for table 'user'

      可以看到，也不允许删除。

取消授权

授权后还可以取消授权。

例如，取消插入数据的权限

1
2

mysql> revoke insert on test1.* from 'zxp'@'localhost';
Query OK, 0 rows affected (0.01 sec)

此时，我们可以再尝试一下插入数据试一试

1
2

mysql> insert into user(name, age) values ('zxp4', 22), ('zxp5', 23);
ERROR 1142 (42000): INSERT command denied to user 'zxp'@'localhost' for table 'user'

可以看到权限被禁止了。

总结

补全计划(一)到此为止，欢迎交流！

Licensed under CC BY-NC-SA 4.0
© 2024 - 2025 Xiao Peng
花有重开日，人无再少年
使用 Hugo 构建
主题 Stack 由 Jimmy 设计