Linux_Mysql基础

mysql基础

1. 关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

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

关系模型：
二维关系：row（行,代表一个记录），column（列，代表一个字段）

数据库管理系统：

DBMS（DataBase Management System）

关系型数据库管理系统

RDBMS（Relationship DataBase Management System）

关系：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安装

mysql安装方式有三种：

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

安装mariadb

[root@localhost ~]# yum -y install mariadb*
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 21:10:39 ago on Mon 21 Dec 2020 06:23:39 AM EST.
Dependencies resolved.
=============================================================================
 Package                      Arch   Version                 Repo       Size
=============================================================================
Installing:
 mariadb                      x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
                                                             AppStream 6.1 M
 mariadb-backup               x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
                                                             AppStream 6.0 M
 mariadb-common               x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
                                                             AppStream  62 k
 mariadb-connector-c          x86_64 3.0.7-1.el8             AppStream 148 k
 mariadb-connector-c-config   noarch 3.0.7-1.el8             AppStream  13 k
 mariadb-connector-c-devel    x86_64 3.0.7-1.el8             AppStream  63 k
 mariadb-connector-odbc       x86_64 3.0.7-1.el8             AppStream 113 k
 mariadb-devel                x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
                                                             AppStream 1.0 M
 mariadb-embedded             x86_64 3:10.3.17-1.module+el8.1.0+3974+90eded84
                
  perl-URI-1.73-3.el8.noarch                                                 
  perl-Unicode-Normalize-1.25-396.el8.x86_64                                 
  perl-constant-1.33-396.el8.noarch                                          
                                       

Complete!

2.2 mysql配置

设置开机自动启动，并且现在启动

[root@localhost ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB 10.3 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor >
   Active: active (running) since Tue 2020-12-22 03:39:18 EST; 10s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 8402 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited,>
  Process: 8267 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mariadb.servi>
  Process: 8243 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, s>
 Main PID: 8370 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 4928)
   Memory: 111.2M
   CGroup: /system.slice/mariadb.service
           └─8370 /usr/libexec/mysqld --basedir=/usr

Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: MySQL manu>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: Please rep>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: The latest>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: You can fi>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: http://dev>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: Consider j>
Dec 22 03:39:17 localhost.localdomain mysql-prepare-db-dir[8267]: https://ma>
Dec 22 03:39:17 localhost.localdomain mysqld[8370]: 2020-12-22  3:39:17 0 [N>
Dec 22 03:39:17 localhost.localdomain mysqld[8370]: 2020-12-22  3:39:17 0 [E>
Dec 22 03:39:18 localhost.localdomain systemd[1]: Started MariaDB 10.3 datab>
[root@localhost ~]# ss -antl   //3306端口号已经起来
State    Recv-Q    Send-Q       Local Address:Port       Peer Address:Port   
LISTEN   0         128                0.0.0.0:22              0.0.0.0:*      
LISTEN   0         80                 0.0.0.0:3306            0.0.0.0:*      
LISTEN   0         128                   [::]:22                 [::]:*      
[root@localhost ~]# 

登录mysql

//-u 是指定用户  
[root@localhost ~]# mysql -uroot    //这里的root用户只是数据库的管理员账户
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

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

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

MariaDB [(none)]> quit
Bye
//-p 需要输入密码登录
[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

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

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
//设置密码
MariaDB [(none)]> set password = password('123456');   //=后面的password是一个加密函数
Query OK, 0 rows affected (0.001 sec)
//-p后面加密码可以登录（不推荐这种方式登录，容易让别人看见密码）
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 12
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, 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@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 14
Server version: 10.3.17-MariaDB MariaDB Server

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

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

MariaDB [(none)]> alter user root@localhost identified by '987654312';  //修改密码
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -p123456   
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p987654312
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 17
Server version: 10.3.17-MariaDB MariaDB Server

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

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

MariaDB [(none)]> quit
Bye

3. mysql的程序组成

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

[root@localhost ~]# mysql   //tab两下
mysql                       mysql_fix_extensions
mysqlaccess                 mysqlhotcopy
mysqladmin                  mysqlimport
mysqlbinlog                 mysql_install_db
mysqlcheck                  mysql_plugin
mysql_client_test           mysql_secure_installation
mysql_client_test_embedded  mysql_setpermission
mysql_config                mysqlshow
mysql_convert_table_format  mysqlslap
mysqld_multi                mysqltest
mysqld_safe                 mysqltest_embedded
mysqld_safe_helper          mysql_tzinfo_to_sql
mysqldump                   mysql_upgrade
mysqldumpslow               mysql_waitpid
mysql_find_rows  
[root@localhost ~]# ss -antlp
State    Recv-Q    Send-Q       Local Address:Port       Peer Address:Port                                                                                
LISTEN   0         128                0.0.0.0:22              0.0.0.0:*       users:(("sshd",pid=1079,fd=5))                                              
LISTEN   0         80                 0.0.0.0:3306            0.0.0.0:*       users:(("mysqld",pid=8370,fd=21))                                           
LISTEN   0         128                   [::]:22                 [::]:*       users:(("sshd",pid=1079,fd=7))                                              
[root@localhost ~]# 

初始化数据库

[root@localhost ~]# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y    //是否更改密码
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y		//是否移除匿名账户
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n		//是否不允许root账户远程登陆
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y		//移除测试数据库和他的访问权限
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y		//重新加载权限表
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

3.1 mysql工具使用

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

演示如下

[root@localhost ~]# mysql -V
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 28
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, 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 |
+--------------------+
3 rows in set (0.126 sec)

MariaDB [(none)]> quit
Bye
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases'  //在外面查看
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@localhost ~]# 

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

创建数据库

语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';

MariaDB [(none)]> CREATE database school;
Query OK, 1 row affected (0.000 sec)

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

MariaDB [(none)]> 

删除数据库

语法：DROP DATABASE [IF EXISTS] 'DB_NAME';

MariaDB [(none)]> drop database school;
Query OK, 0 rows affected (0.311 sec)

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

4.1.2 表操作

创建表

语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型';

MariaDB [(none)]> use school;   //进入school数据库
Database changed

MariaDB [school]> CREATE TABLE student(id int not null,name varchar(50) null,age tinyint);      //创建student表
Query OK, 0 rows affected (0.145 sec)

MariaDB [school]> show tables;  //查看已创建的student表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.002 sec)

MariaDB [school]> DESC student;   //查看student表的结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.003 sec)

MariaDB [school]> 

添加表里的数据

MariaDB [school]> ALTER TABLE student ADD class varchar(20);
Query OK, 0 rows affected (0.072 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
| class | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.002 sec)

MariaDB [school]> 

删除表里的数据

MariaDB [school]> ALTER TABLE student DROP class;
Query OK, 0 rows affected (2.510 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

MariaDB [school]> 

删除表

MariaDB [school]> DROP TABLE student;
Query OK, 0 rows affected (0.032 sec)

MariaDB [school]> show tables;
Empty set (0.001 sec)

MariaDB [school]> 

4.1.5 查看帮助

//查看创建database的帮助文档

MariaDB [school]> 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: https://mariadb.com/kb/en/CREATE-database/


MariaDB [school]> 

//查看创建table的帮助文档
MariaDB [school]> 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) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.

Rules for permissible table names are given in
https://mariadb.com/kb/en/identifier-names/. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.

URL: https://mariadb.com/kb/en/CREATE-table/


MariaDB [school]> 

4.2 DML操作

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

4.2.1 INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...

插入一条数据到student表

//创建一个student表
MariaDB [leihaha]> CREATE table student(id int not null,name varchar(20),age tinyint);
Query OK, 0 rows affected (0.009 sec)
//插入数据到student
MariaDB [leihaha]> insert into student values(1,'tom',18);
Query OK, 1 row affected (0.004 sec)

MariaDB [leihaha]> select * from student;  //查看student表
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   18 |
+----+------+------+
1 row in set (0.001 sec)

MariaDB [leihaha]> 

插入多条数据到student表

MariaDB [leihaha]> insert into student values(2,'alice',18),(3,'jerry',19),(4,'natsha',23),(5,'tony',19);
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [leihaha]> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | alice  |   18 |
|  3 | jerry  |   19 |
|  4 | natsha |   23 |
|  5 | tony   |   19 |
+----+--------+------+
5 rows in set (0.001 sec)

MariaDB [leihaha]> 

注意：上面加入数据的时候需要手动输入ID号，十分麻烦，所以我们需要设置一个主键为自动增长

主键设置自动增长

//这里我们创建一个新表，因为一开始没有设置主键为自动增长
MariaDB [leihaha]> CREATE table teacher(id int not null primary key auto_increment,name varchar(50) not null,age tinyint,money float );
Query OK, 0 rows affected (0.009 sec)

MariaDB [leihaha]> 
//插入数据来看一下效果
MariaDB [leihaha]> insert into teacher(name,age,money) values('zhangsan',25,5000),('lisi',30,8000),('wangwu',27,5500),('leimou',21,10000);  //我们这里没有输入ID号，但是创建出来的ID是自动增长的，而且是累计的，一直增长下去
Query OK, 4 rows affected (0.004 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
+----+----------+------+-------+
4 rows in set (0.001 sec)

MariaDB [leihaha]> 

4.2.2 SELECT语句

字段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个结果

//DML操作之查操作select
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

可以给数据类型取一个别名

MariaDB [leihaha]> select name as '姓名',money as '薪资' from teacher;
+----------+--------+
| 姓名     | 薪资   |
+----------+--------+
| zhangsan |   5000 |
| lisi     |   8000 |
| wangwu   |   5500 |
| leimou   |  10000 |
+----------+--------+
4 rows in set (0.001 sec)

MariaDB [leihaha]> 

where定位查询

查找工资是5000的人的所以信息

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
+----+----------+------+-------+
4 rows in set (0.001 sec)

MariaDB [leihaha]> select * from teacher where money=5000;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
+----+----------+------+-------+
1 row in set (0.001 sec)

MariaDB [leihaha]> 

查找工资是5000的人的ID

MariaDB [leihaha]> select id from teacher where money=5000;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.001 sec)

MariaDB [leihaha]> 

查找工资是5000的人的姓名

MariaDB [leihaha]> select name from teacher where money=5000;
+----------+
| name     |
+----------+
| zhangsan |
+----------+
1 row in set (0.000 sec)

MariaDB [leihaha]> 

查找工资大于5000的人

MariaDB [leihaha]> select * from teacher where money > 5000;
+----+--------+------+-------+
| id | name   | age  | money |
+----+--------+------+-------+
|  2 | lisi   |   30 |  8000 |
|  3 | wangwu |   27 |  5500 |
|  4 | leimou |   21 | 10000 |
+----+--------+------+-------+
3 rows in set (0.000 sec)

MariaDB [leihaha]> 

查找名字是u结尾的信息

MariaDB [leihaha]> select * from teacher where name like '%u';
+----+--------+------+-------+
| id | name   | age  | money |
+----+--------+------+-------+
|  3 | wangwu |   27 |  5500 |
|  4 | leimou |   21 | 10000 |
+----+--------+------+-------+
2 rows in set (0.001 sec)

MariaDB [leihaha]> 

查找名字是l开头的信息或者年纪是21的信息

MariaDB [leihaha]> select * from teacher where name like 'l%' or age = 21;
+----+--------+------+-------+
| id | name   | age  | money |
+----+--------+------+-------+
|  2 | lisi   |   30 |  8000 |
|  4 | leimou |   21 | 10000 |
+----+--------+------+-------+
2 rows in set (0.000 sec)

MariaDB [leihaha]> 

注意：NULL值不等于空格输入

插入NULL值和空格输入，方便演示

MariaDB [leihaha]> insert teacher(name,age,money)values('eric',22,null),("",19,8500);
Query OK, 2 rows affected (0.016 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
|  5 | eric     |   22 |  NULL |
|  6 |          |   19 |  8500 |
+----+----------+------+-------+
6 rows in set (0.000 sec)

MariaDB [leihaha]> 

排序查询

按age从小到大排序（升序）

MariaDB [leihaha]> select * from teacher order by age;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  6 |          |   19 |  8500 |
|  4 | leimou   |   21 | 10000 |
|  5 | eric     |   22 |  NULL |
|  1 | zhangsan |   25 |  5000 |
|  3 | wangwu   |   27 |  5500 |
|  2 | lisi     |   30 |  8000 |
+----+----------+------+-------+
6 rows in set (0.000 sec)

MariaDB [leihaha]> 

按age从大到小排序（降序）

MariaDB [leihaha]> select * from teacher order by age desc;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  1 | zhangsan |   25 |  5000 |
|  5 | eric     |   22 |  NULL |
|  4 | leimou   |   21 | 10000 |
|  6 |          |   19 |  8500 |
+----+----------+------+-------+
6 rows in set (0.001 sec)

按薪资从大到小排序，且只取前两个

MariaDB [leihaha]> select * from teacher order by money desc limit 2;
+----+--------+------+-------+
| id | name   | age  | money |
+----+--------+------+-------+
|  4 | leimou |   21 | 10000 |
|  6 |        |   19 |  8500 |
+----+--------+------+-------+
2 rows in set (0.000 sec)

MariaDB [leihaha]> 

按薪资从小到大排序，跳过前2条，保留3条

MariaDB [leihaha]> select * from teacher order by money desc limit 2,3;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  1 | zhangsan |   25 |  5000 |
+----+----------+------+-------+
3 rows in set (0.000 sec)

MariaDB [leihaha]> 

查询age大于20小于30的信息

MariaDB [leihaha]> select * from teacher where age > 20 and age < 30;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
|  5 | eric     |   22 |  NULL |
+----+----------+------+-------+
4 rows in set (0.001 sec)

MariaDB [leihaha]> 

4.2.3 update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

修改更新数据

MariaDB [leihaha]> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | alice  |   18 |
|  3 | jerry  |   19 |
|  4 | natsha |   23 |
|  5 | tony   |   19 |
+----+--------+------+
5 rows in set (0.001 sec)

//找到student表中id为2的数据，把他的age设置为19
MariaDB [leihaha]> update student set age = 19 where id = 2;    //这里用where定位给id，是因为id唯一的，更加准确
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [leihaha]> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | tom    |   18 |
|  2 | alice  |   19 |
|  3 | jerry  |   19 |
|  4 | natsha |   23 |
|  5 | tony   |   19 |
+----+--------+------+
5 rows in set (0.000 sec)

MariaDB [leihaha]> 

注意：字符串类型要加引号，数字可以直接加

4.2.3 delete语句

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

删除薪资为NULL空值的数据

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
|  5 | eric     |   22 |  NULL |
|  6 |          |   19 |  8500 |
+----+----------+------+-------+
6 rows in set (0.000 sec)

MariaDB [leihaha]> delete from teacher where money is null;
Query OK, 1 row affected (0.001 sec)

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
|  6 |          |   19 |  8500 |
+----+----------+------+-------+
5 rows in set (0.000 sec)

MariaDB [leihaha]> 

删除名字为空格的数据

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
|  6 |          |   19 |  8500 |
+----+----------+------+-------+
5 rows in set (0.000 sec)

MariaDB [leihaha]> delete from teacher where name = "" ;
Query OK, 1 row affected (0.001 sec)

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
+----+----------+------+-------+
4 rows in set (0.000 sec)

MariaDB [leihaha]> 

清空teacher表的数据，保留表结构

MariaDB [leihaha]> select * from teacher;
+----+----------+------+-------+
| id | name     | age  | money |
+----+----------+------+-------+
|  1 | zhangsan |   25 |  5000 |
|  2 | lisi     |   30 |  8000 |
|  3 | wangwu   |   27 |  5500 |
|  4 | leimou   |   21 | 10000 |
+----+----------+------+-------+
4 rows in set (0.000 sec)

MariaDB [leihaha]> delete from teacher;
Query OK, 4 rows affected (0.001 sec)

MariaDB [leihaha]> select * from teacher;
Empty set (0.000 sec)

MariaDB [leihaha]> desc teacher;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| age   | tinyint(4)  | YES  |     | NULL    |                |
| money | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB [leihaha]> 

此时已经把teacher表的数据删完了，但是表结构还在

MariaDB [leihaha]> insert teacher (name,age,money)values('tom',20,9000),     ('jerry',18,5000);   //插入新数据
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [leihaha]> select * from teacher;   //ID号依旧自动增长
+----+-------+------+-------+
| id | name  | age  | money |
+----+-------+------+-------+
|  7 | tom   |   20 |  9000 |
|  8 | jerry |   18 |  5000 |
+----+-------+------+-------+
2 rows in set (0.000 sec)

MariaDB [leihaha]> 

4.2.5 truncate语句

truncate与delete的区别：

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

//语法：TRUNCATE table_name;

清空teacher表的数据，不保留结构

MariaDB [leihaha]> TRUNCATE teacher;
Query OK, 0 rows affected (0.014 sec)

MariaDB [leihaha]> select * from teacher;
Empty set (0.000 sec)

插入数据，发现工号重置了从1开始，没有累计增长了

MariaDB [leihaha]> insert teacher(name,age,money)values('eric',22,2000),("wangwu",19,8500);
Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [leihaha]> select * from teacher;
+----+--------+------+-------+
| id | name   | age  | money |
+----+--------+------+-------+
|  1 | eric   |   22 |  2000 |
|  2 | wangwu |   19 |  8500 |
+----+--------+------+-------+
2 rows in set (0.000 sec)

MariaDB [leihaha]> 

4.3 DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

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

指定要操作的对象db_name.table_name

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

4.3.2 创建授权grant

授权root用户在数据库本机上登录访问所有数据库

MariaDB [leihaha]> GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

授权root用户在192.168.110.1上远程登录访问root数据库

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'192.168.110.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.001 sec)

授权root用户在所有位置上远程登录访问root数据库

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)

刷新权限表

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

4.3.3 查看授权

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

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

MariaDB [(none)]> 

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

MariaDB [(none)]> show grants for 'root'@'192.168.110.1';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.110.1@%                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root@192.168.110.1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

4.3.4 取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';

取消指定用户root的授权信息

MariaDB [(none)]> REVOKE ALL ON *.* from 'root'@'192.168.110.1';
Query OK, 0 rows affected (0.000 sec)

刷新权限表

MariaDB [(none)]> FlUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show grants for 'root'@'192.168.110.1';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for root@192.168.110.1@%                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root@192.168.110.1'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>    //USAGE表示只有连接的权限

sql作业

1. 搭建mysql服务

[root@localhost ~]# yum -y install mariadb*    //安装mariadb
[root@localhost ~]# systemctl enable --now mariadb  //设置开机自启
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.

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)

MariaDB [lc]> CREATE table student(id int not null primary key auto_increment,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.009 sec)

MariaDB [lc]> 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.002 sec)

MariaDB [lc]> 

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

MariaDB [lc]> select * from student;
Empty set (0.001 sec)

MariaDB [lc]> 

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

MariaDB [lc]> insert into student(name,age)values('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.002 sec)
Records: 11  Duplicates: 0  Warnings: 0

MariaDB [lc]> 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.000 sec)

MariaDB [lc]> 

5.修改lisi的年龄为50

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

MariaDB [lc]> 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.001 sec)

MariaDB [lc]> 

6.以age字段降序排序

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

MariaDB [lc]> 

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

MariaDB [lc]> 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.001 sec)

MariaDB [lc]> 

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

MariaDB [lc]> 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.001 sec)

MariaDB [lc]> 

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

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

MariaDB [lc]> 

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

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

MariaDB [lc]> 

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

MariaDB [lc]> 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.001 sec)

MariaDB [lc]> 

12.修改wangwu的年龄为100

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

MariaDB [lc]> 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.000 sec)

MariaDB [lc]> 

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

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

MariaDB [lc]> 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.000 sec)

MariaDB [lc]>