MySQL基础

MySQL基础

目录

1.关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

  • 层次模型
  • 网状结构
  • 关键模型

关系模型：

二维关系：row，columm

数据库管理系统：

  • DBMS （DataBase Managment System）

关系：relational

关系型数据库管理系统：RDBMS

1.2 RDBMS专用名词

关系型数据库管理系统：

mysql：5.7版本之间都是开源 8.0后开始商业化

  • MySQL：MySQL，MariaDB，Percona-Server，sqlserver，MSsql（micresoft sql）
  • PostgreSQL：简称为pgsql
  • Oracle
  • MSSQL

非关系型数据库管理系统：

数据存放在内存当中 变量 key=value

  • mongodb
  • redis
  • memcache

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安装

mysql安装方式有三种：

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

mysql安装步骤

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh  mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# yum module disable mysql  //禁用mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck		//下载一个服务端，客户端，命令行，软件包			//--nogpgche禁止进行gpgcheck

2.2 mysql配置

安装完后设置开机自启动

[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2022-07-25 14:34:30 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 70174 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>
  Process: 70022 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 70176 (mysqld)
    Tasks: 27 (limit: 23457)
   Memory: 300.4M
   CGroup: /system.slice/mysqld.service
           └─70176 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 25 14:34:28 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 14:34:30 localhost.localdomain systemd[1]: Started MySQL Server.

查看3306端口是否监听

[root@localhost ~]# ss -anlt | grep 3306
LISTEN 0      80                 *:3306            *:*

在日志中找出密码

[root@localhost ~]# grep "password"  /var/log/mysqld.log 
2022-07-25T06:34:28.883599Z 1 [Note] A temporary password is generated for root@localhost: OFU+amdhV3Wr			//临时密码
2022-07-25T06:35:33.642430Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T06:35:34.759663Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T06:35:34.759665Z 0 [Note] Shutting down plugin 'mysql_native_password'

使用临时密码登录MySQL

[root@localhost ~]# mysql -uroot -pOFU+amdhV3Wr		//-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 3
Server version: 5.7.38

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登录密码

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

为避免mysql自动升级，需要卸载最开始安装的yum源

[root@localhost ~]# rpm -e mysql57-community-release

3.mysql的层序组成

  • 客户端
    • mysql：CLI交互式客户端程序
    • mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
    • mysqldump：mysql备份工具
    • mysqladmin
  • 服务器端
    • mysqld

3.1 mysql工具使用

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

示例

//查看当前MySQL的版本
[root@localhost ~]# mysql -V		
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

//指定服务器主机，默认为localhost，推荐使用ip地址 //套接字
[root@localhost ~]# mysql -uroot -p123456 -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 6
Server version: 5.7.38 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> 

//注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码
[root@localhost ~]# mysql -uroot -p -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38 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执行sql语句后退出，常用于脚本
[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e "show databases;"
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3.2 服务器监听的两种socket地址

socket 说明
ip socket 默认监听在tcp3306端口，支持远程通信
unix 监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock）仅支持本地通信
server地址只能是：localhost，127.0.0.1

4.mysql数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

4.1 数值类型

  • MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
  • 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围
类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 byte (-128,127) (0,255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，4 294 967 295) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小点数

4.2 日期和时间类型

  • 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。
类型 大小（byte） 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07		 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

4.3 字符串类型

  • 字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

  • CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
  • BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
  • BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
  • 有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

5.mysql数据库操作

5.1 DDL操作

5.1.1 数据库操作

创建数据库

[root@localhost ~]# mysql -uroot -p		//先进入MySQL数据库
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.38 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> show databases;		//查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
	
mysql> create database zxr;				//创建数据库
Query OK, 1 row affected (0.00 sec)

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

删除数据库

mysql> show databases;					//查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zxr                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database zxr;				//删除数据库
Query OK, 0 rows affected (0.00 sec)

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

5.1.2 表操作

创建表之前需要进入一个数据库

mysql> create database zxr;			//需要先创建一个数据库
Query OK, 1 row affected (0.00 sec)

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

mysql> use zxr;		//进入数据库里
Database changed	//进入成功

mysql> create table linux (id int not null ,name varchar(100) not null,age tinyint);		//创建表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;			//查看表
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

删除表

mysql> show tables;
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

mysql> drop table linux;			//drop删除
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

5.1.3 用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录

host值可以为

  • IP地址，如127.0.0.1
  • 通配符
    • %：配置任意长度的任意字符，常用于设置允许从任何主机登录
    • _:匹配任意单个字符

创建用户

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

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.38 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> create user 'zxr'@'127.0.0.1' identified by '123456';	//创建用户
Query OK, 0 rows affected (0.00 sec)

使用新创建的用户和密码登录mysql

[root@localhost ~]# mysql -uzxr -p123456 -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 13
Server version: 5.7.38 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>

删除用户

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.38 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> drop user 'zxr'@'127.0.0.1';			//删除用户
Query OK, 0 rows affected (0.00 sec)

mysql>

5.1.4 查看命令show

查看支持的所有字符

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |

查看当前数据库支持的所有存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看数据库信息

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

不进入数据库而列出其包含的所有表

mysql> show tables from zxr;
+---------------+
| Tables_in_zxr |
+---------------+
| linux         |
+---------------+
1 row in set (0.00 sec)

查看表结构

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

查看表的创建命令

mysql> show create table zxr.linux;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| linux | CREATE TABLE `linux` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看表的状态

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

mysql> use zxr;		//进入数据库
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
mysql> show table status like 'linux'\G
*************************** 1. row ***************************
           Name: linux
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-07-25 16:00:38
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

5.1.5 如何获取帮助

语法：HELP keyword;

示例

mysql> help create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html

5.2DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

5.2.1 INSERT语句

mysql> use zxr;
Database changed
mysql> insert into linux (id,name,age)values(2,'jerry',23),(3,'lisi',25),(4,'zhangsan',30),(5,'zhangsan',20),(6,'wangwu',null);		//一次可以插入多条记录
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

5.2.2 SELECT语句

字段column表示法

表示符 代表什么
* 所有字段
as 字段别名，当表明很长时可以用别名来代替

示例

mysql> select id from linux;
+----+
| id |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> select * from linux;			
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select id as 序号,name as 姓名 from linux;		//临时修改
+--------+----------+
| 序号   | 姓名     |
+--------+----------+
|      2 | jerry    |
|      3 | lisi     |
|      4 | zhangsan |
|      5 | zhangsan |
|      6 | wangwu   |
+--------+----------+
5 rows in set (0.00 sec)

mysql> select * from linux where age is null;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  6 | wangwu | NULL |
+----+--------+------+
1 row in set (0.00 sec)

条件判断语句WHERE

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

示例

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from linux where age = 23;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   23 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> select * from linux where name like '%z%';
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> select * from linux where  age is not null;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> select * from linux where age is null;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  6 | wangwu | NULL |
+----+--------+------+
1 row in set (0.00 sec)

mysql> select * from linux where id = 4 and age = 30;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | zhangsan |   30 |
+----+----------+------+
1 row in set (0.00 sec)

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 linux order by age;	//升序
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  6 | wangwu   | NULL |
|  5 | zhangsan |   20 |
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from linux order by age desc;		//降序
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | zhangsan |   30 |
|  3 | lisi     |   25 |
|  2 | jerry    |   23 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from linux order by id limit 2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   23 |
|  3 | lisi  |   25 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> select * from linux order by id limit 1,2;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
+----+----------+------+
2 rows in set (0.00 sec)

5.2.3 update语句

语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> update linux set age=30 where name = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   30 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

5.2.4 delete语句

语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   30 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> delete from linux where id = 5;		//删除一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  6 | wangwu   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from linux;
Query OK, 4 rows affected (0.00 sec)

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

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

5.2.5 truncate语句

truncate与delete的区别：

语句类型 特点
delete DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate 删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表 
mysql> select * from linux;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | lisi     |   25 |
|  4 | zhangsan |   30 |
|  5 | zhangsan |   20 |
|  6 | wangwu   | NULL |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> truncate linux;
Query OK, 0 rows affected (0.01 sec)

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

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

5.3 DCL操作

5.3.1 创建权限grant

权限类型(priv_type)

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

指定要操作的对象db_name.table_name

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

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

如有有报错需要重置一下密码

mysql> grant all on *.* to 'zxr'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

示例

//授权用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'zxr'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  grant all on *.* to 'zxr'@'192.168.111.135' identified by '123
456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权用户在上192.168.111.137远程登录访问数据库
mysql> grant all on zxr.* to 'zxr'@'192.168.111.137' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权用户在所有位置上远程登录访问数据库
mysql> grant all on *.* to 'zxr'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权root用户允许远程登录
mysql> grant all on *.* to 'root'@'192.168.111.135' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
mysql> grant all on *.* to 'root'@'192.168.111.1' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

image

5.3.2 查看授权

查看当前登录用户的授权信息

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户的授权信息

mysql> show grants for zxr;
+------------------------------------------+
| Grants for zxr@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zxr'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'zxr'@'localhost';
+--------------------------------------------------+
| Grants for zxr@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zxr'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'zxr'@'192.168.111.135';
+--------------------------------------------------------+
| Grants for zxr@192.168.111.135                         |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zxr'@'192.168.111.135' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'root'@'192.168.111.135';
+---------------------------------------------------------+
| Grants for root@192.168.111.135                         |
+---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.111.135' |
+---------------------------------------------------------+
1 row in set (0.00 sec)

5.3.3 取消授权revoke

mysql> REVOKE ALL ON *.* FROM 'zxr'@'192.168.111.135';
Query OK, 0 rows affected (0.00 sec)

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

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

  • GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
  • 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;		//刷新规则

实战案例

1.搭建mysql服务

下载mysql

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh  mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# yum module disable mysql  //禁用mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck		//下载一个服务端，客户端，命令行，软件包			//--nogpgche禁止进行gpgcheck

安装完后设置开机自启动

[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2022-07-25 14:34:30 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 70174 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p>
  Process: 70022 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 70176 (mysqld)
    Tasks: 27 (limit: 23457)
   Memory: 300.4M
   CGroup: /system.slice/mysqld.service
           └─70176 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 25 14:34:28 localhost.localdomain systemd[1]: Starting MySQL Server...
Jul 25 14:34:30 localhost.localdomain systemd[1]: Started MySQL Server.

查看3306端口是否监听

[root@localhost ~]# ss -anlt | grep 3306
LISTEN 0      80                 *:3306            *:*

在日志中找出密码

[root@localhost ~]# grep "password"  /var/log/mysqld.log 
2022-07-25T06:34:28.883599Z 1 [Note] A temporary password is generated for root@localhost: OFU+amdhV3Wr			//临时密码
2022-07-25T06:35:33.642430Z 0 [Note] Shutting down plugin 'validate_password'
2022-07-25T06:35:34.759663Z 0 [Note] Shutting down plugin 'sha256_password'
2022-07-25T06:35:34.759665Z 0 [Note] Shutting down plugin 'mysql_native_password'

使用临时密码登录MySQL

[root@localhost ~]# mysql -uroot -pOFU+amdhV3Wr		//-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 3
Server version: 5.7.38

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登录密码

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

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> create database zxr;
Query OK, 1 row affected (0.00 sec)

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

mysql> use zxr;
Database changed
mysql> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 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.00 sec)

3.查看下该新建的表有无内容（用select语句）

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

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

解析

mysql> insert into student (name,age)values('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangsan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

5.修改lisi的年龄为50

mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.00 sec)

6.以age字段降序排序

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

7.查询student表中年龄最小的3位同学跳过前2位

mysql> select * from student order by age;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  9 | wangwu      |    3 |
|  8 | chenshuo    |   10 |
| 10 | qiuyi       |   15 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  5 | zhangshan   |   26 |
|  4 | sean        |   28 |
|  7 | lisi        |   50 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> select * from student order by age limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
| 10 | qiuyi       |   15 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.00 sec)

8.查询student表中年龄最大的4位同学

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | wangqing  |   25 |
+----+-----------+------+
4 rows in set (0.00 sec)

9.查询student表中名字叫zhangshan的记录

mysql> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.00 sec)

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

mysql> select * from student where name = 'zhangshan' and age > 20;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
+----+-----------+------+
1 row in set (0.00 sec)

11.查询student表中年龄在23到30之间的记录

mysql> select * from student where age between 23 and 30;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.00 sec)

mysql> select * from student where age >= 23 and age <= 30;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.00 sec)

12.修改wangwu的年龄为100

mysql> update student set age =100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.00 sec)

13.删除student中名字叫zhangshan且年龄小于等于20的记录

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.00 sec)

mysql> delete from student where name = 'zhangshan' and age >= 20;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  6 | zhangsan    |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.00 sec)
posted @   事愿人为  阅读(222)  评论(0)  编辑  收藏  举报
相关博文：
· mysql实战案例
· 二进制安装mysql
· mysql基础
· mysql数据库基础
· mysql基础
阅读排行：
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通
点击右上角即可分享
微信分享提示