mysql基础

小白風

已于 2022-07-26 20:59:17 修改

阅读量266

点赞数

分类专栏：服务管理文章标签： mysql 数据库 centos

于 2022-07-26 20:58:39 首次发布

本文链接：https://blog.csdn.net/qq_58114167/article/details/126003147

版权

服务管理专栏收录该内容

25 篇文章 0 订阅

订阅专栏

mysql基础

dnf安装mariadb

[root@localhost ~]# yum install -y mariadb*
CentOS Stream 8 - AppStream                       203  B/s | 4.4 kB     00:22    
CentOS Stream 8 - BaseOS                          4.0 kB/s | 3.9 kB     00:00    
CentOS Stream 8 - Extras                          892  B/s | 2.9 kB     00:03    
依赖关系解决。
==================================================================================
 软件包            架构   版本                                    仓库       大小
==================================================================================
安装:
 mariadb           x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 6.0 M
 mariadb-backup    x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 6.1 M
 mariadb-common    x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream  64 k
 mariadb-connector-c
                   x86_64 3.1.11-2.el8_3                          appstream 200 k
 mariadb-connector-c-config
                   noarch 3.1.11-2.el8_3                          appstream  15 k
 mariadb-connector-c-devel
                   x86_64 3.1.11-2.el8_3                          appstream  68 k
 mariadb-connector-odbc
                   x86_64 3.1.12-1.el8                            appstream 118 k
 mariadb-devel     x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 1.1 M
 mariadb-embedded  x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 4.9 M
 mariadb-embedded-devel
                   x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream  44 k
 mariadb-errmsg    x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream 234 k
 mariadb-gssapi-server
                   x86_64 3:10.3.28-1.module_el8.3.0+757+d382997d appstream  51 k

设置开机自启

[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 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-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('1');
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye
[root@localhost ~]# mysql -uroot -p1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-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)]>

还有另外一种方法这里就不演示了

wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm  下载rpm包
rpm -Uvh  mysql57-community-release-el7-10.noarch.rpm/   安装
yum  module disable mysql  禁用mysql  
yum -y install mysql-community-server mysql-community-client  mysql-community-common mysql-community-devel  --nogpgcheck 安装相关插件
systemctl start mysqld.service  设置开机自启
systemctl enable --now  mysqld  
 grep "password" /var/log/mysqld.log            在日志文件中找出临时密码
 mysql -uroot -p加临时密码
 然后再改密码
 ALTER USER 'root'@'localhost' IDENTIFIED BY “密码”

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.28-MariaDB, for Linux (x86_64) using readline 5.1
[root@localhost ~]# mysql -uroot -p1 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-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)]> 
[root@localhost ~]# mysql -uroot -p1 -h 127.0.0.1 -e 'SHOW DATABASES;
> '
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

服务器监听的两种socket地址

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

MySQL 数据类型

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

数值类型

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，16 777 215)	大整数值
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的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小( bytes)	范围	格式	用途
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	混合日期和时间值，时间戳

字符串类型

字符串类型指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 类型，可存储的最大长度不同，可根据实际情况选择。

DDL操作

数据库操作

//创建数据库
MariaDB [(none)]> create database if not exists lxx;
Query OK, 1 row affected (0.000 sec)
//查看当前实例有哪些数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxx                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
//删除数据库
MariaDB [(none)]> drop database lxx;
Query OK, 0 rows affected (0.002 sec)

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

表操作

创建表
在数据库lxx里创建表ndaye
MariaDB [(none)]> use lxx; 
Database changed
创建表
MariaDB [lxx]> CREATE TABLE ndaye (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); 
Query OK, 0 rows affected (0.009 sec)
查看表
MariaDB [lxx]> show tables;
+---------------+
| Tables_in_lxx |
+---------------+
| ndaye         |
+---------------+
1 row in set (0.001 sec)
删出表
MariaDB [lxx]> drop table ndaye;
Query OK, 0 rows affected (0.005 sec)

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

用户操作

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

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

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

数据库用户创建
MariaDB [lxx]> CREATE USER 'host'@'127.0.0.1' IDENTIFIED BY '1';
Query OK, 0 rows affected (0.001 sec)

MariaDB [lxx]> exit
Bye
[root@localhost ~]# mysql -uhost -p1 -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.3.28-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)]> 
删除数据库用户
MariaDB [(none)]> DROP USER 'host'@'127.0.0.1';
Query OK, 0 rows affected (0.001 sec)

查看命令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 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |

+----------+-----------------------------+---------------------+--------+
....
40 rows in set (0.001 sec)

MariaDB [(none)]> show engines;   //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)                   | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| ARCHIVE            | YES     | gzip-compresses tables for a low storage footprint                               | NO           | NO   | NO         |
| FEDERATED          | YES     | Allows to access tables on other MariaDB servers, supports transactions and more | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| OQGRAPH            | YES     | Open Query Graph Computation Engine (http://openquery.com/graph)                 | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
12 rows in set (0.001 sec)




MariaDB [(none)]> show databases;    //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxx                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)


MariaDB [(none)]> show tables from lxx;    //不进入某数据库而列出其包含的所有表
+---------------+
| Tables_in_lxx |
+---------------+
| ndaye         |
+---------------+
1 row in set (0.001 sec)
                                            


MariaDB [(none)]> use lxx;
Database changed
MariaDB [lxx]> desc ndaye;                       //查看表结构
+-------+--------------+------+-----+---------+-------+
| 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.002 sec)
                              
 //查看某表的创建命令                
 MariaDB [(none)]> show create table lxx.ndaye;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| ndaye | CREATE TABLE `ndaye` (
  `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.000 sec)
查看某表的状态
MariaDB [(none)]> use lxx;
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 [lxx]> show table status like 'ndaye'\G 
*************************** 1. row ***************************
            Name: ndaye
          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 22:36:02
     Update_time: NULL
      Check_time: NULL
       Collation: latin1_swedish_ci
        Checksum: NULL
  Create_options: 
         Comment: 
Max_index_length: 0
       Temporary: N
1 row in set (0.001 sec)

获取帮助

//获取命令使用帮助
//语法：HELP keyword;
mysql> HELP CREATE TABLE;       //获取创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
 ......
 ......

DML操作

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

INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
MariaDB [(none)]> use lxx;
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 [lxx]>  insert into ndaye(id,name,age) values (2,'jerry',23),(3,'ndy',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL); 
Query OK, 6 rows affected (0.005 sec)
Records: 6  Duplicates: 0  Warnings: 0

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
MariaDB [lxx]> select * from ndaye;   //查询所有字段
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
6 rows in set (0.000 sec)
   //查询名字
+-----------+
MariaDB [lxx]> select name from ndaye;   
+-----------+
| name      |
+-----------+
| jerry     |
| ndy       |
| sean      |
| zhangshan |
| zhangshan |
| lisi      |
+-----------+
6 rows in set (0.000 sec)
// 以年龄大小升序排列
MariaDB [lxx]> select * from ndaye order by age;          
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      | NULL |
|  6 | zhangshan |   20 |
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  5 | zhangshan |   26 |
|  4 | sean      |   28 |
+----+-----------+------+
6 rows in set (0.001 sec) 
// 以年龄大小降序排列
MariaDB [lxx]> select * from ndaye order by age desc;  
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | ndy       |   25 |
|  2 | jerry     |   23 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
6 rows in set (0.001 sec)
// 升序排序取前两个
MariaDB [lxx]> select * from ndaye order by age limit 2;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      | NULL |
|  6 | zhangshan |   20 |
+----+-----------+------+
2 rows in set (0.000 sec)

// //以升序排序跳过第一个取下面两个
MariaDB [lxx]> select * from ndaye order by age limit 1,2;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  6 | zhangshan |   20 |
|  2 | jerry     |   23 |
+----+-----------+------+
2 rows in set (0.000 sec)

//查询年龄大于等于28岁的人
MariaDB [lxx]> select * from ndaye where age >=28;
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | sean |   28 |
+----+------+------+
1 row in set (0.004 sec)
//查询年龄大于等于20和id大于1的人
MariaDB [lxx]> select * from ndaye where age >=20 and id >1;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
5 rows in set (0.001 sec)
//查询年龄在20到28之间的人
MariaDB [lxx]> select * from ndaye where age between 23 and 28;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
+----+-----------+------+
4 rows in set (0.000 sec)
//查找年龄不为空的内容
MariaDB [lxx]> select * from ndaye where age is not NULL;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
5 rows in set (0.000 sec)
//查找年龄是空的内容
MariaDB [lxx]> select * from ndaye where age is  NULL;
+----+------+------+
| id | name | age  |
+----+------+------+
|  7 | lisi | NULL |
+----+------+------+
1 row in set (0.000 sec)

update语句

//将名字为lisi的年龄改为98岁
MariaDB [lxx]> update ndaye set age = 98 where name = 'lisi';
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [lxx]> select * from ndaye;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      |   98 |
+----+-----------+------+
6 rows in set (0.000 sec)

delete语句

DML操作之删操作delete
// 删除ndaye这个表里面叫名字lisi的这个内容
MariaDB [lxx]> delete from ndaye where name = 'lisi';  注释：字符串一点要打单引号
Query OK, 1 row affected (0.002 sec)

MariaDB [lxx]> select * from ndaye;   
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | ndy       |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
5 rows in set (0.000 sec)
//删除整张表
MariaDB [lxx]> delete from ndaye;
Query OK, 5 rows affected (0.001 sec)

MariaDB [lxx]> select * from ndaye;  注释：delete删除表是还有备份的可以复原
Empty set (0.000 sec)

MariaDB [lxx]> desc ndaye;
+-------+--------------+------+-----+---------+-------+
| 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.001 sec)

truncate语句

truncate与delete的区别：

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

语句类型

特点

delete

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

truncate

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

MariaDB [lxx]> select * from ndaye;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
6 rows in set (0.000 sec)

MariaDB [lxx]> truncate ndaye;
Query OK, 0 rows affected (0.008 sec)

MariaDB [lxx]> select * from ndaye;
Empty set (0.000 sec)

MariaDB [lxx]> desc ndaye;
+-------+--------------+------+-----+---------+-------+
| 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.001 sec)

DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

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

指定要操作的对象db_name.table_name

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

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

//授权lxx用户在数据库本机上登录访问所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxx                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> grant all on *.* to 'lxx'@'192.168.132.100' identified by '1';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> grant all on *.* to 'lxx'@'127.0.0.1' identified by '1';
Query OK, 0 rows affected (0.001 sec)
//授权job可以在任何主机上远程登陆访问所有数据库和内容
MariaDB [(none)]> grant all on *.* to 'job'@'%' identified by '1';
Query OK, 0 rows affected (0.000 sec)
//授权lxx用户在192.168.132.100上远程登录访问lxx数据库
MariaDB [(none)]> grant all on lxx.* to 'lxx'@'192.168.132.100' identified by '1';
Query OK, 0 rows affected (0.001 sec)

查看授权

MariaDB [(none)]> show grants   //查看当前用户的权限信息
    -> ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
//查看指定用户wangqing的授权信息
MariaDB [(none)]> show grants for 'lxx'@'192.168.132.100';
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for lxx@192.168.132.100                                                                                            |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `lxx`@`192.168.132.100` IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
| GRANT ALL PRIVILEGES ON `lxx`.* TO `lxx`@`192.168.132.100`                                                                |
+---------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

取消授权revoke

MariaDB [(none)]> show grants for 'lxx'@'192.168.132.100';
+------------------------------------------------------------------------------------------------------------------+
| Grants for lxx@192.168.132.100                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `lxx`@`192.168.132.100` IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
| GRANT ALL PRIVILEGES ON `lxx`.* TO `lxx`@`192.168.132.100`                                                       |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
MariaDB [(none)]>  flush privileges;
Query OK, 0 rows affected (0.000 sec)
注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

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

实战案例

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

MariaDB [(none)]> create database lx;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lx                 |
| lxx                |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> use lx;
Database changed
MariaDB [lx]> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.007 sec)

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

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

MariaDB [lx]> select  * from student;
Empty set (0.001 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 [lx]> insert into student (id,name,age)  values(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); 
Query OK, 11 rows affected (0.001 sec)
Records: 11  Duplicates: 0  Warnings: 0

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

4修改lisi的年龄为50

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

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

5.以age字段降序排序

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

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

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

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

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

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

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

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

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

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

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

11.修改wangwu的年龄为100

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

MariaDB [lx]> select * from student  where name='wangwu';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  9 | wangwu |  100 |
+----+--------+------+
1 row in set (0.000 sec)

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

MariaDB [lx]> delete from student where name='zhangshan' and age<=20;
Query OK, 0 rows affected (0.000 sec)
MariaDB [lx]>  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      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.000 sec)

001 sec)


11.修改wangwu的年龄为100

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

MariaDB [lx]> select * from student where name=‘wangwu’;
±—±-------±-----+
| id | name | age |
±—±-------±-----+
| 9 | wangwu | 100 |
±—±-------±-----+
1 row in set (0.000 sec)


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

MariaDB [lx]> delete from student where name=‘zhangshan’ and age<=20;
Query OK, 0 rows affected (0.000 sec)
MariaDB [lx]> 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 | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
±—±------------±-----+
11 rows in set (0.000 sec)