mysql基础原理及配置

于 2020-05-22 18:23:11 发布
阅读量774 收藏
点赞数
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/lnsistw/article/details/106261854
版权
本文详细介绍了MySQL的基础知识，包括MySQL的简介、基本术语、SQL语句的使用，以及数据库的安装配置。内容涵盖DDL、DML、DCL操作，如创建、删除数据库和表，用户管理和权限控制等，还提供了SQL语句的实例操作。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1.概述

1.1MySQL简介

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。
MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。
MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。
MySQL是个关系型数据库管理系统，由瑞典MySQL AB公司开发，后来被Sun公司收购，Sun公司后来被Oracle公司收购，目前属于Orac旗下。

1.2基本术语：

SQL即Structure Query Language（结构化查询语言）

DB即DataBase（数据库），数据库按照数据结构来组织，存储和管理数据的仓库。数据库提供了很多功能，可以通过SQL语句来定义来操作数据

DBMS即DataBase Management System（数据库管理系统），操作和管理数据库的大型软件，用于建立、使用和维护数据库

RDBMS即Relational DataBase Management System（关系型数据库管理系统），关系型数据库使用表来保存数据，使用表和表之间的关系来处理数据 还为数据的安全性、完整性、并发控制和数据恢复提供了保证。常见的数据库服务软件：Oracle、MySQL(Oracle公司);SQL Server、Access(微软)

（Key）:用来标识行的一列或多列。
主关键字（Primary Key）:表示为一条记录（行）的唯一标识关键字，一个表中只有一个主键。又称主键。
侯关键字（Candidate Key）：唯一的标识表中的一行而又不含多余属性的一个属性集。
公共关键字（Common Key）：关系之间的联系是通过相容或相同的属性或属性组来表示。若两个关系中具有相容或相同的属性或属性组，那么这个属性或属性组被称为两个关系的公共关键字。
外关键字（Foreign Key）：若公共关键字在一个关系中是主键，那么这个公共关键字被称为另一个关系的外键。所以，表示两个关系之间的联系，又称外键。
！主键和外键的名称可以不同，但类型必须相同！

关系型数据库的常见组件：
数据库：database

  • 表：table，由行（row）和列（column）组成
    表是一种结构化的文件，可用于存储特定类型的数据，表中的每一行，也称为一条记录
    表中的一个字段，所有表都是由一个或多个列组成的。表中的每一列，称为属性，字段。
  • 索引：index
    将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储。
  • 视图：view
    视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
  • 用户：user
  • 权限：privilege
  • 存储过程：Stored procedure
    存储过程就是一条或多条SQL语句的集合，可视为批处理文件，但是其作用不仅用于批处理。
  • 存储函数：function
  • 触发器：trigger
    触发器的执行不是由程序调用，而是由事件来触发、激活从而实现执行。
  • 事件调度器：event scheduler

1.3 SQL语句：

DDL：Data Defination Language，数据定义语言

  • CREATE：创建
  • DROP：删除
  • ALTER：修改

DML：Data Manipulation Language，数据操纵语言

  • INSERT：向表中插入数据
  • DELETE：删除表中数据
  • UPDATE：更新表中数据
  • SELECT：查询表中数据

DCL：Data Control Language，数据控制语言

  • GRANT：授权
  • REVOKE：移除授权

2.MySQL安装与配置

2.1 mysql安装

mysql安装方式有三种：

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

2.2 安装MySQL

1.下载并安装MySQL官方的下载源

[root@wyt1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@wyt1 ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-10.noarch.rpm

2.安装MySQL

[root@wyt1 ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm

3.安装MySQL服务器

[root@wyt1 ~]# yum -y install mysql-community-server

4.设置开机自动启动

[root@wyt1 ~]# systemctl enable --now mysqld

5.查看监听端口是否启动

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

6.在日志中找到临时密码

[root@wyt1 ~]# grep "password" /var/log/mysqld.log
2020-05-22T04:11:49.636055Z 1 [Note] A temporary password is generated for root@localhost: 103sltNbi9:_

7.用临时密码登录

[root@wyt1 ~]# mysql -uroot -p103sltNbi9:_

8.修改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.01 sec)

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

mysql> quit
Bye

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

[root@wyt1 ~]# 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语句后退出，常用于脚本

root@wyt1 ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

[root@wyt1 ~]# mysql -uroot -p'wyt123'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> quit
Bye

[root@wyt2 ~]# mysql -uroot -pwyt123 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye
[root@wyt2 ~]# mysql -uroot -p -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> quit
Bye
[root@wyt2 ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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

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

4. mysql数据库操作

4.1 DLL操作

4.1.1 数据库操作

创建数据库

MariaDB [(none)]> create database if not exists wyt;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wyt                |
+--------------------+
5 rows in set (0.01 sec)

删除数据库

MariaDB [(none)]> drop database if exists wyt;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

4.1.2 表操作

创建表

MariaDB [(none)]>  create database if not exists wyt; //创建wyt数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use wyt //进入wyt数据库
Database changed
MariaDB [wyt]> create table w (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); //创建w表
Query OK, 0 rows affected (0.01 sec)

MariaDB [wyt]> show tables; //查看当前数据库有哪些表
+---------------+
| Tables_in_wyt |
+---------------+
| w             |
+---------------+
1 row in set (0.01 sec)
MariaDB [wyt]> desc w; //查看表的结构
+-------+--------------+------+-----+---------+-------+
| 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)

MariaDB [wyt]> show create table w; //查看建表语句
---+
| w     | CREATE TABLE `w` (
  `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)

删除表

MariaDB [wyt]> drop table if exists w;
Query OK, 0 rows affected (0.01 sec)

MariaDB [wyt]> show tables; //查看表
Empty set (0.00 sec)

删除列

MariaDB [wyt]> desc w; //查看表的结构
+-------+--------------+------+-----+---------+-------+
| 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)

MariaDB [wyt]> alter table w drop id;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wyt]> desc w; //查看表的结构
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

修改表名

MariaDB [wyt]> show tables; //查看表
+---------------+
| Tables_in_wyt |
+---------------+
| w             |
+---------------+
1 row in set (0.01 sec)
MariaDB [wyt]> rename table w to y;
Query OK, 0 rows affected (0.01 sec)
MariaDB [wyt]> show tables; //查看表
+---------------+
| Tables_in_wyt |
+---------------+
| y             |
+---------------+
1 row in set (0.00 sec)

修改列名

MariaDB [wyt]> desc y; //查看表的结构
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [wyt]> alter table y change name mi varchar;
Query OK, 0 rows affected (0.04 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [wyt]> desc y; //查看表的结构
+-------+------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| mi    | varchar(100) | YES  |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加列

MariaDB [wyt]> desc y; //查看表的结构
+-------+------------+------ +-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| mi    |varchar(100) | YES  |     | NULL    |       |
| age   |tinyint(4)   | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
ariaDB [wyt]> alter table y add id int;
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [wyt]> desc y; //查看表的结构
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| mi    |varchar(100)| YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
| id    | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4.1.3 用户操作

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：
IP地址，如：192.168.232.130
通配符
%：匹配任意长度的任意字符，常用于设置允许从任何主机登录
_：匹配任意单个字符

创建数据库用户

MariaDB [(none)]> create user tom@'192.168.232.128' identified by '123456';
Query OK, 0 rows affected (0.02 sec)
[root@wyt1 ~]# mysql -utom -p123456 -h192.168.232.130 //使用创建的用户密码登录
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

删除数据库用户

MariaDB [(none)]> drop user tom@192.168.232.128;
Query OK, 0 rows affected (0.02 sec)

4.1.4查看命令SHOW

查看支持的所有字符集

MariaDB [(none)]> 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 |
......

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

MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| 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         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

查看数据库信息

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wyt                |
+--------------------+
5 rows in set (0.00 sec)

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

MariaDB [(none)]> SHOW TABLES FROM wyt;
+---------------+
| Tables_in_wyt |
+---------------+
| y             |
+---------------+
1 row in set (0.00 sec)

查看表结构

MariaDB [(none)]> desc wyt.y;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| mi    |varchar(100)| YES  |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
| id    | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

查看某表的创建命令

MariaDB [(none)]> show create table wyt.y;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| y     | CREATE TABLE `y` (
  `mi` int(11) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看某表的状态

MariaDB [(none)]> use wyt; //进入wyt数据库
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
MariaDB [wyt]> show table status like 'y'\G //查看y表的状态
*************************** 1. row ***************************
           Name: y
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: NULL
    Create_time: 2020-05-22 17:08:51
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

4.1.5获取帮助

获取创建数据库的帮助

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

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] 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: http://dev.mysql.com/doc/refman/5.5/en/create-database.html

获取创建表的帮助

MariaDB [wyt]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
    .......

4.2 DML操作

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

4.2.1 INSERT语法（创建）

语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),(…),…

MariaDB [(none)]> use wyt //进入wyt数据库  
MariaDB [wyt]> insert into w value(1,'tom',18);   //插入一行
Query OK, 1 row affected (0.01 sec)

MariaDB [wyt]> select * from w; 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)
MariaDB [wyt]> insert w values(2,'jerry',20),(3,'xiaoli',16),(4,'dawang',22),(5,'lisha',25);  //插入多行
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [wyt]> select * from w;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | jerry  |   20 |
|  3 | xiaoli |   16 |
|  4 | dawang |   22 |
|  5 | lisha  |   25 |
+----+--------+------+
5 rows in set (0.01 sec)
MariaDB [wyt]> create table wang(id int primary key auto_increment not null,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.01 sec)

MariaDB [wyt]> desc wang;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [wyt]> insert wang value(1,'tom',18);  //插入一行
Query OK, 1 row affected (0.00 sec)

MariaDB [wyt]> select * from wang;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)
MariaDB [wyt]> insert wang(name,age) values('jerry',20),('lisha',19),('Andy',22);
Query OK, 3 rows affected (0.01 sec) //自动增长插入行数
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
+----+-------+------+
4 rows in set (0.01 sec)

4.2.2 SELECT语句（查询）

字段column表示法
*表示所有字段

MariaDB [(none)]> use wyt;
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
MariaDB [wyt]> 
MariaDB [wyt]> select * from wang;//查询
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
+----+-------+------+
4 rows in set (0.01 sec)

as 字段别名，如col1 AS alias1
当表名很长时用别名代替

MariaDB [wyt]> select id,age as score from wang;
+----+-------+
| id | score |
+----+-------+
|  1 |    18 |
|  2 |    20 |
|  3 |    19 |
|  4 |    22 |
+----+-------+
4 rows in set (0.00 sec)

条件判断语句WHERE

MariaDB [wyt]> select * from wang where age = 20;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
+----+-------+------+
1 row in set (0.01 sec)

MariaDB [wyt]> select name from wang where age = 20;
+-------+
| name  |
+-------+
| jerry |
+-------+
1 row in set (0.00 sec)
MariaDB [wyt]> select * from wang where id = 1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)

MariaDB [wyt]> select * from wang where age = 18;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)

条件逻辑操作and（并且）

MariaDB [wyt]> select * from wang where age = 18 and name = 'tom';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)
MariaDB [wyt]> select * from wang where age between 18 and 20;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
+----+-------+------+
3 rows in set (0.00 sec)

MariaDB [wyt]> select * from wang where age >= 18 and age <= 20;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
+----+-------+------+
3 rows in set (0.00 sec)

条件逻辑操作or(或者)

MariaDB [wyt]> select * from wang where age = 18 or name = 'jerry';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
+----+-------+------+
2 rows in set (0.00 sec)

LIKE：模糊匹配

MariaDB [wyt]> select * from wang where age like '2%';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  4 | Andy  |   22 |
+----+-------+------+
2 rows in set (0.00 sec)

MariaDB [wyt]> select * from wang where age like '2_';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  4 | Andy  |   22 |
+----+-------+------+
2 rows in set (0.00 sec)

RLIKE：基于正则表达式进行模式匹配

MariaDB [wyt]> select * from wang where id rlike '[1]';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)

MariaDB [wyt]> select * from wang where age rlike '2[0-9]';
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  4 | Andy  |   22 |
+----+-------+------+
2 rows in set (0.00 sec)
MariaDB [wyt]> select * from wang where name rlike '^t';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)
MariaDB [wyt]> select * from wang where name rlike '^[a-z]{3}$';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.00 sec)

IS NOT NULL：非空

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
MariaDB [wyt]>  select * from wang where age is not null;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
+----+-------+------+
4 rows in set (0.00 sec)

IS NULL：空

MariaDB [wyt]>  select * from wang where age is null;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  5 | zhuli | NULL |
+----+-------+------+
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个结果
MariaDB [(none)]> use wyt;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
MariaDB [wyt]>  select * from wang where age is not null order by age; //排除空的
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  3 | lisha |   19 |
|  2 | jerry |   20 |
|  4 | Andy  |   22 |
+----+-------+------+
4 rows in set (0.00 sec)

MariaDB [wyt]>  select * from wang where age is not null order by age limit 1; //排序排除空的，并且取前面一位
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.01 sec)

MariaDB [wyt]>  select name from wang where age is not null order by age limit; //以名字排序排除空的，取出以名字排序的第一位
+------+
| name |
+------+
| tom  |
+------+
1 row in set (0.00 sec)
MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli | NULL |
+----+-------+------+
5 rows in set (0.00 sec)

MariaDB [wyt]>  select * from wang order by age limit 1,2; //排序，并且略过第1个结果取后面的2个结果|
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  3 | lisha |   19 |
+----+-------+------+
2 rows in set (0.00 sec)

MariaDB [wyt]>  select * from wang order by age desc; //降序排序
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  4 | Andy  |   22 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  1 | tom   |   18 |
|  5 | zhuli | NULL |
+----+-------+------+
5 rows in set (0.00 sec)
MariaDB [wyt]>  select * from wang order by age desc limit 1; //降序排并取出年龄最大的
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | Andy |   22 |
+----+------+------+
1 row in set (0.01 sec)

4.2.3 update语句（修改）


MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli | NULL |
+----+-------+------+
5 rows in set (0.03 sec)

MariaDB [wyt]> update wang set age = 25 where id = 5; //修改列表
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli |   25 |
+----+-------+------+
5 rows in set (0.00 sec)

4.2.4 delete语句（删除）

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   18 |
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli |   25 |
+----+-------+------+
5 rows in set (0.00 sec)

MariaDB [wyt]> delete from wang where id = 1; //删除列表部分数据
Query OK, 1 row affected (0.00 sec)

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli |   25 |
+----+-------+------+
4 rows in set (0.00 sec)
MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
|  5 | zhuli |   25 |
+----+-------+------+
4 rows in set (0.00 sec)
MariaDB [wyt]> delete from wang order by age desc limit 1; //删除列表部分数据
Query OK, 1 row affected (0.02 sec)

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
+----+-------+------+
3 rows in set (0.00 sec)
MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | jerry |   20 |
|  3 | lisha |   19 |
|  4 | Andy  |   22 |
+----+-------+------+
3 rows in set (0.00 sec)

MariaDB [wyt]> delete from wang; //只删除表内容，保留表结构
Query OK, 3 rows affected (0.00 sec)

MariaDB [wyt]> select * from wang;
Empty set (0.00 sec)
MariaDB [wyt]> desc wang;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [wyt]> insert wang(name,age) values('jerry',20),('lisha',19),('Andy',22);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [wyt]> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  6 | jerry |   20 |
|  7 | lisha |   19 |
|  8 | Andy  |   22 |
+----+-------+------+
3 rows in set (0.00 sec)

4.2.5 truncate语句（删除）

truncate与delete的区别：

  • truncate不能删除单条数据，只能删除全部数据
  • truncate删除的数据在没有备份的情况下无法恢复。而delete删除数据会生成日志可以通过回滚日志恢复数据
  • truncate删除数据后，设定了自动递增的列，计数会从初始值开始。
  • 对于有外键约束引用、加入了索引视图的表，不能使用truncate删除数据
MariaDB [wyt]> select * from w;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | jerry  |   20 |
|  3 | xiaoli |   16 |
|  4 | dawang |   22 |
|  5 | lisha  |   25 |
|  0 | Andy   | NULL |
+----+--------+------+
7 rows in set (0.00 sec)

MariaDB [wyt]> truncate w; //删除整个表
Query OK, 0 rows affected (0.02 sec)

Empty set (0.00 sec)

MariaDB [wyt]> desc w;
+-------+--------------+------+-----+---------+-------+
| 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)
MariaDB [wyt]> insert w values(1,'tom',18),(2,'jerry',20),(3,'xiaoli',16),(4,'dawang',22),(5,'lisha',25);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [wyt]> select * from w;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | jerry  |   20 |
|  3 | xiaoli |   16 |
|  4 | dawang |   22 |
|  5 | lisha  |   25 |
+----+--------+------+
5 rows in set (0.00 sec)

4.3 DCL操作

4.3.1 创建授权grant

权限类型

  • ALL 所有权限
  • SELECT 读取内容的权限
  • INSERT 插入内容的权限
  • UPDATE 更新内容的权限
  • DELETE 删除内容的权限

操作对象

  • *.* 所有库的所有表
  • db_name 指定库的所有表
  • db_name.table_name 指定库的指定表

授权tom用户在本机上可创建表

MariaDB [wyt]> create user 'tom'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [wyt]> grant insert on wyt.w to 'tom'@'localhost'; 
Query OK, 0 rows affected (0.00 sec)
MariaDB [wyt]> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec) 
MariaDB [wyt]> insert w(name,age) value('dw',15); //可创建表
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [wyt]> select * from w; //没有权限查询
ERROR 1142 (42000): SELECT command denied to user 'tom'@'localhost' for table 'w'

授权wei用户在本机上可查询

MariaDB [wyt]> grant select on wyt.w to 'wei'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [wyt]> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec) 
MariaDB [wyt]> select * from w;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | jerry  |   20 |
|  3 | xiaoli |   16 |
|  4 | dawang |   22 |
|  5 | lisha  |   25 |
|  0 | dw     |   15 |
+----+--------+------+
6 rows in set (0.00 sec)
MariaDB [wyt]> insert w(name,age) value('xq',16); //没有权限创建
ERROR 1142 (42000): INSERT command denied to user 'wei'@'localhost' for table 'w'

授权xiaowang用户在本机上登录访问所有数据库和表，并拥有所有权限

MariaDB [wyt]> grant all on *.* to 'xiaowang'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

4.3.2查看授权

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

MariaDB [wyt]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*509ADEB77D88D9C1914F1FCF99F45C1E971FE2A9' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户xiaowang的授权信息

MariaDB [wyt]> grant all on *.* to 'xiaowang'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [wyt]> show grants for 'xiaowang'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for xiaowang@localhost                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'xiaowang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.3.3 取消授权REVOKE

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

MariaDB [wyt]> revoke select on *.* from 'xiaowang'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [wyt]> show grants for 'xiaowang'@'localhost';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for xiaowang@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 'xiaowang'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.实例

1.创建一个以你名字为名的数据库，并创建一张表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)

MariaDB [(none)]> create database wangyitong;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use wangyitong;
Database changed
MariaDB [wangyitong]> create table student(id int (11) primary key auto_increment not null,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

MariaDB [wangyitong]> 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)

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

MariaDB [wangyitong]> select * from student;
Empty set (0.01 sec)

3.往新建的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 |
+----+-------------+------+

MariaDB [wangyitong]> insert student(name,age) value('tom',20),('jerry',23),('wangqing',25),('sean',28),('zhangshan',26),('zhangshan',20),('lisi',null),('chenshuo',10),('wangwu',3),('qiuyi',15),('qiuxiaotian',20);
Query OK, 11 rows affected (0.01 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [wangyitong]> select * from student;
+----+-------------+------+
| 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 |
+----+-------------+------+
11 rows in set (0.00 sec)

4.修改lisi的年龄为50

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

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

5.以age字段降序排序

MariaDB [wangyitong]> update student order by age desc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order by age desc' at line 1
MariaDB [wangyitong]> 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 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

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

MariaDB [wangyitong]> select * from student order by age limit 2,3;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
| 10 | qiuyi     |   15 |
|  1 | tom       |   20 |
|  6 | zhangshan |   20 |
+----+-----------+------+
3 rows in set (0.01 sec)

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

MariaDB [wangyitong]> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  9 | wangwu    |  100 |
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.01 sec)

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

MariaDB [wangyitong]> select * from student where name = 'zhangshan';
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.01 sec)

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

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

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

MariaDB [wangyitong]> 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)

11.修改wangwu的年龄为100

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

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

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

MariaDB [wangyitong]> delete from student where name = 'zhangshan' and age <=20;
Query OK, 1 row affected (0.00 sec)

MariaDB [wangyitong]> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
10 rows in set (0.01 sec)
Insistw
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值