mysql基础

炸鸡嘎嘎

已于 2022-04-29 09:16:17 修改

阅读量520

点赞数 2

分类专栏： mysql 文章标签： linux

于 2022-04-18 17:00:07 首次发布

本文链接：https://blog.csdn.net/QWQ45597316/article/details/124248179

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

1.关系型数据库介绍
2. mysql安装与配置
3.MYSQL的基础操作
- 3.1mysql库操作
- 3.2mysql表操作
4.权限管理
- 4.1创建授权grant

1.关系型数据库介绍

1.1数据结构模型

数据结构模型主要有：

层次模型
网状结构
关系模型(常用)

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

1.2 RDBMS专业名词

常见的关系型数据库管理系统：

MySQL：MySQL，MariaDB，Percona-Server
PostgreSQL：简称为pgsql
Oracle
MSSQL

SQL：Structure Query Language，结构化查询语言

约束：constraint，向数据表提供的数据要遵守的限制

主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。一个表只能存在一个
惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）一个表可以存在多个
外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
检查性约束

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

1.3 关系型数据库的常见组件

数据库：database
表：table，由行（row）和列（column）组成
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发器：trigger
事件调度器：event scheduler

1.4 SQL语句

SQL语句有三种类型：

DDL：Data Defination Language，数据定义语言
DML：Data Manipulation Language，数据操纵语言
DCL：Data Control Language，数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP:删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

2. mysql安装与配置

2.1 mysql安装

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
- 程序包管理器管理的程序包：
  - OS Vendor项目
  - 官方提供的：操作系统发行商提供的
- deb

准备工作

*配置mysql的yum源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm   //下载
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm //安装
[root@localhost ~]# yum clean all   //清理缓存
[root@localhost ~]# yum makecache   //建立本地缓存方便下载
*下载mysql的软件包并安装
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.37-1.el7.x86_64.rpm  //client 客户端
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.37-1.el7.x86_64.rpm //common
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.37-1.el7.x86_64.rpm  //devel
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.37-1.el7.x86_64.rpm  //libs
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.37-1.el7.x86_64.rpm  //server服务器
[root@localhost ~]# yum install -y *rpm  //安装此目录下的所有rpm包

2.2 mysql配置

[root@localhost ~]# systemctl enable --now mysqld  //设置开机自启
[root@localhost ~]# systemctl status mysqld.service  //查看确保mysql开启 并设置为开机自启
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset:>
   Active: active (running) since Mon 2022-04-18 15:36:27 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 173578 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysql>
  Process: 173049 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/>
 Main PID: 173580 (mysqld)
    Tasks: 27 (limit: 11160)
   Memory: 378.9M
   CGroup: /system.slice/mysqld.service
           └─173580 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld>

[root@localhost ~]# ss -antl  //查看3306端口是否开启(3306端口是mysql的默认端口)
State     Recv-Q    Send-Q         Local Address:Port         Peer Address:Port    
LISTEN    0         128                  0.0.0.0:111               0.0.0.0:*       
LISTEN    0         32             192.168.122.1:53                0.0.0.0:*       
LISTEN    0         128                  0.0.0.0:22                0.0.0.0:*       
LISTEN    0         5                  127.0.0.1:631               0.0.0.0:*       
LISTEN    0         80                         *:3306                    *:*       
LISTEN    0         128                     [::]:111                  [::]:*   

#登录mysql
[root@localhost ~]# grep "password" /var/log/mysqld.log  //查看mysql临时密码
2022-04-18T08:10:56.865610Z 1 [Note] A temporary password is generated for root@localhost: k)wGiD9Y<H+a
[root@localhost ~]# mysql -uroot -p'k)wGiD9Y<H+a'   //利用临时密码登录mysql  u接用户 p接密码 中间不可有空格
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> set password = password('123.Com!');   //密码需要数字 大写字母 小写字母和符号  推荐使用这个
Query OK, 0 rows affected, 1 warning (0.00 sec)

#修通过策略来修改密码
mysql> set global validate_password_policy=0;  //将设置密码的复杂性设为最低
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1; //设置密码的长度最小为1
Query OK, 0 rows affected (0.00 sec)

mysql> set password = password('123.com');
Query OK, 0 rows affected, 1 warning (0.00 sec)

#尝试用新密码登录
[root@localhost ~]# mysql -uroot -p123.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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自动升级，这里需要卸载最开始安装的yum源
rpm -e mysql57-community-release

2.3使用图形化工具连接数据库

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本

mysql> GRANT ALL ON *.* TO 'root'@'192.168.220.1' IDENTIFIED BY '123.Com!';
Query OK, 0 rows affected, 1 warning (0.00 sec)  //设置策略 让192.168.220.1这个ip可以连接到数据库
mysql> create database aaa;  //创建一个数据库用作测试
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)


[root@localhost ~]# systemctl disable --now firewalld.service   //关闭防火墙
[root@localhost ~]# setenforce 0  //关闭selinx  临时

在Navicat软件内创建连接输入需要连接的虚拟机ip和数据库密码
在这里插入图片描述

创建完成后双击连接可以看到创建的测试库aaa
在这里插入图片描述

2.4客户端连接数据库

服务器
服务器登录mysql后为客户机做策略让其拥有登录的权限

mysql> GRANT ALL ON *.* TO 'root'@'192.168.220.100' IDENTIFIED BY '123.Com!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

客户机

#配置mysql的yum源
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm 
[root@localhost ~]# yum clean all
[root@localhost ~]# yum makecache 
#客户端下载mysql的源码包
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.37-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.37-1.el7.x86_64.rpm  http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.37-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.37-1.el7.x86_64.rpm http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.37-1.el7.x86_64.rpm
[root@localhost ~]# yum -y install *.rpm  //安装
#测试登录
[root@localhost ~]# mysql -uroot -p123.Com! -h192.168.220.145  //-h指定ip
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>

3.MYSQL的基础操作

3.1mysql库操作

创建数据库

#语法
CREATE DATABASE 数据库名字 [数据库选项]

示例

#创建一个数据库名为school
mysql> CREATE DATABASE school;
Query OK, 1 row affected (0.01 sec)

显示数据库

#显示查看所有数据库
mysql> CREATE DATABASE school;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

#显示数据库的创建指令
mysql> SHOW CREATE DATABASE school;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

使用数据库

mysql> USE school;
Database changed  //当出现此字段时就是已经进入此库了

修改数据库

#语法
ALTER DATABASE 数据库名字 库选项

示例

mysql> CREATE DATABASE zsh;
Query OK, 1 row affected (0.00 sec)

mysql> ALTER DATABASE zsh CHARSET gbk COLLATE gbk_chinese_ci;  //修改此数据库的字符集和校对集(如果校队集修改必须同时改变字符集)
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE DATABASE zsh;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| zsh      | CREATE DATABASE `zsh` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

mysql> DROP DATABASE zsh;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据库用户创建
语法：CREATE USER ‘username’@‘host’ [IDENTIFIED BY ‘password’];

mysql> CREATE USER 'zsh'@'127.0.0.1' IDENTIFIED BY '789.Com!';
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mysql -uabc -p456.Com! -h127.0.0.1;  //登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> DROP USER 'zsh'@'127.0.0.1';  //删除用户  需要在root用户内
Query OK, 0 rows affected (0.00 sec)

3.2mysql表操作

创建数据表

#语法
create table [数据库名.]表名(
	字段名 字段类型,
	...
    字段名 字段类型
)[表选项];

示例

mysql> USE school;  //创建表前需要先进入到库内
Database changed 
mysql> CREATE TABLE student(
    -> id int(11) not null primary key auto_increment,
    -> name varchar(100) not null,
    -> age tinyint(4));
Query OK, 0 rows affected (0.01 sec) 
//创建stduent数据表 id字段类型为int限制字段长度11 非空 设置为主键 自增长
name字段 类型为varchar字段长度为100 非空
age字段 类型为tinyint 字段长度为4

显示数据表

mysql> SHOW TABLES;  //查看所有表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES FROM school;  //查看指定库内的表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE student;  //显示数据表的创建指令
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#查看表的状态
mysql> SHOW TABLE STATUS LIKE 'student'\G;  
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4
    Create_time: 2022-04-20 14:50:50
    Update_time: 2022-04-20 16:01:59
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

#查看帮助信息
mysql> HELP CREATE  DATABASES；

查看数据表
查看数据表的操作有三种效果都是一样的

DESC 表名
DESCRIBE 表名
SHOW COLUMNS FROM 表名

示例

mysql> DESC student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

新增字段

mysql> desc abc;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE abc ADD name varchar(100);  //为表abc新增一个name字段
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

#在指定字段后添加新字段
mysql> ALTER TABLE abc ADD age tinyint(4)  after name;  //在name字段后添加age字段
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#在最前面的添加一个字段
mysql> ALTER TABLE abc add card int first;  //为表abc添加一个字段card 放在最前面
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| card  | int(11)      | YES  |     | NULL    |       |
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#删除指定字段
mysql> ALTER TABLE abc DROP card;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

更改数据表

mysql> CREATE TABLE abc(
    -> id int(10));
Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE abc TO qwe;  //修改表名
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| qwe              |
| student          |
+------------------+
2 rows in set (0.00 sec)

删除数据表

mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| qwe              |
| student          |
+------------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE qwe;   //删除qwe数据表
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

插入数据

mysql> INSERT INTO student (name,age) VALUES ('tom',20),('tom',26),('jerry',null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
//插入多条数据

查看表内数据

语法 SELECT * FROM 表名

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1 当表名很长时用别名代替

条件判断语句WHERE

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作	AND OR NOT

ORDER BY：排序，默认为升序（ASC）

ORDER BY语句	意义
ORDER BY ‘column_name’	根据column_name进行升序排序
ORDER BY ‘column_name’ DESC	根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2	根据column_name进行升序排序并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2	根据column_name进行升序排序并且略过第1个结果取后面的2个结果

#查看表内数据信息
mysql> SELECT * FROM student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | tom   |   26 |
|  3 | jerry | NULL |
+----+-------+------+
3 rows in set (0.00 sec)

#查看表内指定数据
mysql> SELECT name,age FROM student;
+-------+------+
| name  | age  |
+-------+------+
| tom   |   20 |
| tom   |   26 |
| jerry | NULL |
+-------+------+
3 rows in set (0.00 sec)

#查看表id为1的数据
mysql> SELECT * FROM student WHERE id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

#按降序排列
mysql> SELECT * FROM student order by age desc;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | tom   |   26 |
|  4 | tom   |   20 |
|  3 | jerry | NULL |
+----+-------+------+
3 rows in set (0.00 sec)

#跳过一个取一个
mysql> SELECT * FROM student order by age limit 1,1;  //第一个1为跳过多少 第二个1为取多少
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

#查看name=tom age<=20的
mysql> SELECT * FROM student where name='tom' AND age<=20;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

#查看年龄范围为20到30的
mysql> SELECT * FROM student WHERE age BETWEEN 20 and 30;
+----+------+------+
| id | name | age  |
+----+------+------+
|  2 | tom  |   26 |
|  4 | tom  |   20 |
+----+------+------+
2 rows in set (0.01 sec)

删除数据

#删除匹配的数据
mysql> DELETE FROM student WHERE age<=20;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | tom   |   26 |
|  3 | jerry | NULL |
+----+-------+------+
2 rows in set (0.00 sec)

修改数据

#修改jerry的age改为50
mysql> UPDATE student SET age=50 WHERE name='jerry';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | tom   |   26 |
|  3 | jerry |   50 |
+----+-------+------+
2 rows in set (0.00 sec)

truncate语句
truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间
truncate	删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

语句类型

特点

delete

DELETE删除表内容时仅删除内容，但会保留表结构
DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间

truncate

删除表中所有数据，且无法恢复
表结构、约束和索引等保持不变，新添加的行计数值重置为初始值
执行速度比DELETE快，且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放
对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表

#删除整张表的数据
mysql> SELECT * FROM student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | tom   |   26 |
|  3 | jerry |   50 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> TRUNCATE student;
Query OK, 0 rows affected (0.00 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM student;
Empty set (0.00 sec)

4.权限管理

4.1创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示方式	意义
.	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

语法：GRANT priv_type,… ON [object_type] db_name.table_name TO ‘username’@‘host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

mysql> create user 'abc'@'127.0.0.1' identified by '456.Com!';  //创建一个用户kurumi并设置密码为456.Com!
Query OK, 0 rows affected (0.01 sec) 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| zsh                |
+--------------------+
6 rows in set (0.00 sec)

mysql> GRANT ALL ON *.* TO 'abc'@'localhost' IDENTIFIED BY '456.Com!'; //给abc用户权限可以登录访问数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL ON *.* TO 'abc'@'%' IDENTIFIED BY '456.Com!';//授权abc用户在所有位置上远程登录访问ltt1数据库
Query OK, 0 rows affected, 1 warning (0.00 sec)


#切换abc用户登录mysql
[root@localhost ~]# mysql -uabc -p456.Com!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 GRANTS;
+--------------------------------------------------+
| Grants for abc@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abc'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

#查看指定用户的权限
mysql> SHOW GRANTS FOR abc;  //查看用户abc的权限
+------------------------------------------+
| Grants for abc@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abc'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'abc'@'localhost'; //abc@localhost的权限
+--------------------------------------------------+
| Grants for abc@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abc'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'abc'@'127.0.0.1'; //abc@127.0.0.1的权限
+-----------------------------------------+
| Grants for abc@127.0.0.1                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'abc'@'127.0.0.1' |
+-----------------------------------------+
1 row in set (0.00 sec)

取消授权REVOKE
语法：REVOKE priv_type,… ON db_name.table_name FROM ‘username’@‘host’;

mysql> SHOW GRANTS FOR 'abc'@'localhost';
+--------------------------------------------------+
| Grants for abc@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'abc'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE SELECT ON *.* FROM 'abc'@'localhost';  //取消abc@localhost 的select 权限
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'abc'@'localhost';  //查看时 只是取消了select 的权限其它的还在
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for abc@localhost                                                                                                                                                                                                                                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'abc'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)