1. 关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

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

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

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

1.2 RDBMS专业名词

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

MySQL：MySQL，MariaDB，Percona-ServerPostgreSQL：简称为pgsqlOracleMSSQL

事务：多个操作被当作一个整体对待就称为一个事务 要看一个关系型数据库是否支持事务，需要看其是否支持并满足ACID测试 ACID：ACID是事务的一个基本标准

A：Automicity，原子性C：Consistency，一致性I：Isolation，隔离性D：Durability，持久性

如果你对ACID感兴趣，可以查看这里了解详细说明，ACID将不作为我们讲解的重点。

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

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

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

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

关系运算：

选择：挑选出符合条件的行（部分行）投影：挑选出需要的字段连接

数据抽象方式：

物理层：决定数据的存储格式，即RDBMS在磁盘上如何组织文件逻辑层：描述DB存储什么数据，以及数据间存在什么样的关系视图层：描述DB中的部分数据

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语句类型对应操作DDLCREATE：创建DROP：删除ALTER：修改DMLINSERT：向表中插入数据建DELETE：删除表中数据UPDATE：更新表中数据SELECT：查询表中数据DCLGRANT：授权REVOKE：移除授权

2. mysql安装与配置

2.1 mysql安装

mysql安装方式有三种：

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

 

//配置mysql的yum源 [root@20liuzhenchao ~]# cd /usr/local/ [root@20liuzhenchao local]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm 下载过程略 [root@20liuzhenchao local]# ls apache apr apr-util bin etc games include lib lib64 libexec mysql57-community-release-el7-10.noarch.rpm nginx php sbin share src [root@20liuzhenchao local]# yum -y install mysql57-community-release-el7-10.noarch.rpm Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription- : manager This system is not registered with an entitlement server. You can use subscription-manager to register. Examining mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch .... Installed: mysql57-community-release.noarch 0:el7-10 Complete! [root@20liuzhenchao local]# ls /etc/yum.repos.d/ CentOS7-Base-163.repo epel.repo epel.repo.rpmnew epel-testing.repo myrepo.repo mysql-community.repo mysql-community-source.repo redhat.repo //安装mysql5.7 [root@20liuzhenchao local]# yum -y install mysql-community-server.x86_64 mysql-community-client.i686 mysql-community-common.x86_64 mysql-community-devel.x86_64 Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription- : manager This system is not registered with an entitlement server. You can use subscription-manager to register. Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package mysql-community-client.x86_64 0:5.7.23-1.el7 will be installed --> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client-5.7.23-1.el7.x86_64 .... Installed: mysql-community-client.x86_64 0:5.7.23-1.el7 mysql-community-common.x86_64 0:5.7.23-1.el7 mysql-community-devel.x86_64 0:5.7.23-1.el7 mysql-community-libs.x86_64 0:5.7.23-1.el7 mysql-community-libs-compat.x86_64 0:5.7.23-1.el7 mysql-community-server.x86_64 0:5.7.23-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.56-2.el7 Complete!

2.2 mysql配置

//启动mysql [root@20liuzhenchao ~]# systemctl start mysqld [root@20liuzhenchao ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 一 2019-04-22 14:18:23 CST; 1min 29s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 13899 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 13823 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 13902 (mysqld) CGroup: /system.slice/mysqld.service └─13902 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid 4月 22 14:18:19 20liuzhenchao systemd[1]: Starting MySQL Server... 4月 22 14:18:23 20liuzhenchao systemd[1]: Started MySQL Server. //确保3306端口已经监听起来 [root@20liuzhenchao ~]# ss -antl |grep 3306 LISTEN 0 80 :::3306 :::* //在日志文件中找出临时密码 [root@20liuzhenchao ~]# grep "password" /var/log/mysqld.log 2019-04-22T06:18:21.008637Z 1 [Note] A temporary password is generated for root@localhost: LIUPk=c3)Tp< //此处的临时密码为LIUPk=c3)Tp< //使用获取到的临时密码登录mysql [root@20liuzhenchao ~]# mysql -uroot -p Enter password: //此处输入密码，可以直接复制你的密码粘贴至此处，也可手动输入 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.25 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> //看到有这样的标识符则表示成功登录了 //修改mysql登录密码 mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye //为避免mysql自动升级，这里需要卸载最开始安装的yum源 [root@20liuzhenchao ~]# rpm -qa |grep mysql mysql-community-devel-5.7.25-1.el7.x86_64 mysql-community-libs-5.7.25-1.el7.i686 mysql-community-server-5.7.25-1.el7.x86_64 mysql-community-common-5.7.25-1.el7.x86_64 mysql-community-libs-compat-5.7.25-1.el7.x86_64 mysql-community-common-5.7.25-1.el7.i686 mysql-community-client-5.7.25-1.el7.x86_64 mysql-community-libs-5.7.25-1.el7.x86_64 mysql57-community-release-el7-10.noarch [root@20liuzhenchao ~]# yum -y remove mysql57-community-release-el7-10.noarch 已加载插件：fastestmirror, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. 正在解决依赖关系 --> 正在检查事务 ---> 软件包 mysql57-community-release.noarch.0.el7-10 将被 删除 --> 解决依赖关系完成 安装大小：30 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction 正在删除 : mysql57-community-release-el7-10.noarch 1/1 Loading mirror speeds from cached hostfile 验证中 : mysql57-community-release-el7-10.noarch 1/1 删除: mysql57-community-release.noarch 0:el7-10 完毕！

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@20liuzhenchao ~]# mysql -V mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper [root@20liuzhenchao ~]# mysql -uroot -p123456 -h127.0.0.1 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> //注意，不推荐直接在命令行里直接用-pPASSWORD的方式登录，而是使用-p选项，然后交互式输入密码 [root@20liuzhenchao ~]# mysql -uroot -p -h127.0.0.1 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> [root@20liuzhenchao ~]# mysql -uroot -p -h127.0.0.1 -e 'SHOW DATABASES;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+

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

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

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

//创建数据库 //语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME'; //创建数据库liuzhenchao mysql> CREATE DATABASE IF NOT EXISTS liuzhenchao; Query OK, 1 row affected (0.00 sec) //查看当前实例有哪些数据库 mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | liuzhenchao | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) //删除数据库 //语法：DROP DATABASE [IF EXISTS] 'DB_NAME'; //删除数据库liuzhenchao mysql> DROP DATABASE IF EXISTS liuzhenchao; Query OK, 0 rows affected (0.00 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)

4.1.2 表操作

//创建表 //语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型'; //在数据库liuzhenchao里创建表liu mysql> CREATE DATABASE liuzhenchao; //创建数据库liuzhenchao Query OK, 1 row affected (0.00 sec) mysql> use liuzhenchao; //进入liuzhenchao数据库 Database changed mysql> CREATE TABLE liu (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); //创建liu表 Query OK, 0 rows affected (0.01 sec) //查看当前数据库有哪些表 mysql> show tables; +-----------------------+ | Tables_in_liuzhenchao | +-----------------------+ | liu | +-----------------------+ 1 row in set (0.00 sec) //删除表 //语法：DROP TABLE [ IF EXISTS ] 'table_name'; //删除表liu mysql> drop table liu; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec)

4.1.3 用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录 这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

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

 

//数据库用户创建 //语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; //创建数据库用户liuzhenchao mysql> create user 'liuzhenchao'@'127.0.0.1' identified by '123456'; Query OK, 0 rows affected (0.01 sec) //使用新创建的用户和密码登录 [root@20liuzhenchao ~]# mysql -uliuzhenchao -p123456 -h127.0.0.1 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> //删除数据库用户 //语法：DROP USER 'username'@'host'; mysql> drop user 'liuzhenchao'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec)

4.1.4 查看命令SHOW

mysql> SHOW CHARACTER SET; //查看支持的所有字符集 +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | ...... ...... mysql> SHOW ENGINES; //查看当前数据库支持的所有存储引擎 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql> show databases; //查看数据库信息 +--------------------+ | Database | +--------------------+ | information_schema | | liuzhenchao | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> show tables from liuzhenchao; //不进入某数据库而列出其包含的所有表 +-----------------------+ | Tables_in_liuzhenchao | +-----------------------+ | liu | +-----------------------+ 1 row in set (0.00 sec) //查看表结构 //语法：DESC [db_name.]table_name; mysql> desc liuzhenchao.liu; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | phone_numble | varchar(11) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.07 sec) //查看某表的创建命令 //语法：SHOW CREATE TABLE table_name; mysql> show create table liuzhenchao.liu; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | liu | CREATE TABLE `liu` ( `id` int(11) NOT NULL, `name` varchar(50) DEFAULT NULL, `phone_numble` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) //查看某表的状态 //语法：SHOW TABLE STATUS LIKE 'table_name'\G mysql> use liuzhenchao; //进入数据库liuzhenchao Database changed mysql> show table status like 'liu'\G //查看liu表的状态 *************************** 1. row *************************** Name: liu 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: 2019-04-23 14:45:56 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 获取帮助

//获取命令使用帮助 //语法：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 ...... ......

4.2 DML操作

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

4.2.1 INSERT语句

//DML操作之增操作insert //语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),... mysql> use liuzhenchao; Database changed mysql> insert into liu(id,name,phone_numble) value(1,'tom',13297040973); Query OK, 1 row affected (0.00 sec) //一次插入一条记录 Query OK, 1 row affected (0.01 sec) mysql> insert into liu values(2,'jerry',33333333333),(3,'zhen',44444444444),(4,'chao',55555555555),(5,'boss',19045045021); //一次插入多条记录 Query OK, 4 rows affected (0.00 sec)

4.2.2 SELECT语句

字段column表示法

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

条件判断语句WHERE

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

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]; mysql> use liuzhenchao; Database changed mysql> select * from liu; +----+-------+--------------+ | id | name | phone_numble | +----+-------+--------------+ | 1 | tom | 13297040973 | | 2 | jerry | 33333333333 | | 3 | zhen | 44444444444 | | 4 | chao | 55555555555 | | 5 | boss | 19045045021 | +----+-------+--------------+ 5 rows in set (0.00 sec) mysql> select name from liu; +-------+ | name | +-------+ | tom | | jerry | | zhen | | chao | | boss | +-------+ 5 rows in set (0.00 sec) mysql> alter table liu add column age tinyint(4) not null; Query OK, 0 rows affected (1.72 sec) //插入一列age mysql> desc liuzhenchao.liu; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | phone_numble | varchar(11) | YES | | NULL | | | age | tinyint(4) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ mysql> update liu set age = 23 where id =1; Query OK, 1 row affected (0.00 sec) mysql> update liu set age = 30 where id =2; Query OK, 1 row affected (0.00 sec) mysql> update liu set age = 60 where id =3; Query OK, 1 row affected (0.01 sec) mysql> update liu set age = 80 where id =4; Query OK, 1 row affected (0.00 sec) mysql> update liu set age = 12 where id =5; Query OK, 1 row affected (0.00 sec) //向age列中分别插入数据 mysql> select * from liu order by age; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 5 | boss | 19045045021 | 12 | | 1 | tom | 13297040973 | 23 | | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | +----+-------+--------------+-----+ 5 rows in set (0.00 sec) mysql> select * from liu order by age desc; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 4 | chao | 55555555555 | 80 | | 3 | zhen | 44444444444 | 60 | | 2 | jerry | 33333333333 | 30 | | 1 | tom | 13297040973 | 23 | | 5 | boss | 19045045021 | 12 | +----+-------+--------------+-----+ 5 rows in set (0.00 sec) //按年龄降序排列 mysql> select * from liu order by age desc limit 2; +----+------+--------------+-----+ | id | name | phone_numble | age | +----+------+--------------+-----+ | 4 | chao | 55555555555 | 80 | | 3 | zhen | 44444444444 | 60 | +----+------+--------------+-----+ 2 rows in set (0.00 sec) //排列后取前两个数据 mysql> select * from liu order by age desc limit 1,2; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 3 | zhen | 44444444444 | 60 | | 2 | jerry | 33333333333 | 30 | +----+-------+--------------+-----+ 2 rows in set (0.00 sec) //跳过第一个数据后取前两个 mysql> select * from liu where age >=30; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | +----+-------+--------------+-----+ 3 rows in set (0.00 sec) mysql> select * from liu where age >=30 and name = 'zhen'; +----+------+--------------+-----+ | id | name | phone_numble | age | +----+------+--------------+-----+ | 3 | zhen | 44444444444 | 60 | +----+------+--------------+-----+ 1 row in set (0.00 sec) mysql> select * from liu where age between 10 and 30; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 1 | tom | 13297040973 | 23 | | 2 | jerry | 33333333333 | 30 | | 5 | boss | 19045045021 | 12 | +----+-------+--------------+-----+ 3 rows in set (0.00 sec) mysql> insert into liu(id,phone_numble,age) value(6,13080619342,55); Query OK, 1 row affected (0.00 sec) //加入一行name为空的记录 mysql> select * from liu where name is not null; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 1 | tom | 13297040973 | 23 | | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | | 5 | boss | 19045045021 | 12 | +----+-------+--------------+-----+ 5 rows in set (0.00 sec) mysql> select * from liu where name is null; +----+------+--------------+-----+ | id | name | phone_numble | age | +----+------+--------------+-----+ | 6 | NULL | 13080619342 | 55 | +----+------+--------------+-----+ 1 row in set (0.00 sec)

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]; mysql> select * from liu; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 1 | tom | 13297040973 | 23 | | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | | 5 | boss | 19045045021 | 12 | | 6 | NULL | 13080619342 | 55 | +----+-------+--------------+-----+ 6 rows in set (0.01 sec) mysql> update liu set age =40 where id =1; Query OK, 1 row affected (0.00 sec) mysql> select * from liu where id =1; +----+------+--------------+-----+ | id | name | phone_numble | age | +----+------+--------------+-----+ | 1 | tom | 13297040973 | 40 | +----+------+--------------+-----+ 1 row in set (0.00 sec)

4.2.4 delete语句

//DML操作之删操作delete //语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; mysql> select * from liu; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 1 | tom | 13297040973 | 40 | | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | | 5 | boss | 19045045021 | 12 | | 6 | NULL | 13080619342 | 55 | +----+-------+--------------+-----+ 6 rows in set (0.00 sec) mysql> delete from liu where id = 6; //删除某条记录 Query OK, 1 row affected (0.00 sec) mysql> select * from liu; +----+-------+--------------+-----+ | id | name | phone_numble | age | +----+-------+--------------+-----+ | 1 | tom | 13297040973 | 40 | | 2 | jerry | 33333333333 | 30 | | 3 | zhen | 44444444444 | 60 | | 4 | chao | 55555555555 | 80 | | 5 | boss | 19045045021 | 12 | +----+-------+--------------+-----+ 5 rows in set (0.00 sec) mysql> delete from liu;//删除整张表的内容 Query OK, 5 rows affected (0.00 sec) mysql> select * from liu; Empty set (0.00 sec) mysql> desc liu; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | phone_numble | varchar(11) | YES | | NULL | | | age | tinyint(4) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

4.2.5 truncate语句

truncate与delete的区别：

语句类型特点deleteDELETE删除表内容时仅删除内容，但会保留表结构DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项可以通过回滚事务日志恢复数据非常占用空间truncate删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值执行速度比DELETE快，且使用的系统和事务日志资源少通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表 //语法：TRUNCATE table_name; mysql> select * from liu; +----+------+--------------+-----+ | id | name | phone_numble | age | +----+------+--------------+-----+ | 1 | liu | 34343434343 | 32 | | 2 | zhen | 13329232222 | 43 | | 3 | chao | 13086024562 | 60 | +----+------+--------------+-----+ 3 rows in set (0.00 sec) mysql> truncate liu; Query OK, 0 rows affected (0.00 sec) mysql> select * from liu; Empty set (0.00 sec) mysql> desc liu; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | phone_numble | varchar(11) | YES | | NULL | | | age | tinyint(4) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

4.3 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：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。 GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION]; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | liuzhenchao | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) //授权liuzhenchao用户在数据库本机上登录访问所有数据库 mysql> grant all on *.* to 'liuzhenchao'@'localhost' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> grant all on *.* to 'liuzhenchao'@'127.0.0.1' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) //授权liuzhenchao用户在192.168.56.20上远程登录访问liuzhenchao数据库 mysql> grant all on liuzhenchao.* to 'liuzhenchao'@'192.168.56.20' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) //授权liuzhenchao用户在所有位置上远程登录访问liuzhenchao数据库 mysql> grant all on *.* to 'liuzhenchao'@'%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)

4.3.2 查看授权

//查看当前登录用户的授权信息 mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) //查看指定用户liuzhenchao的授权信息 mysql> show grants for liuzhenchao; +--------------------------------------------------+ | Grants for liuzhenchao@% | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'liuzhenchao'@'%' | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for 'liuzhenchao'@'localhost'; +----------------------------------------------------------+ | Grants for liuzhenchao@localhost | +----------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'liuzhenchao'@'localhost' | +----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants for 'liuzhenchao'@'127.0.0.1'; +----------------------------------------------------------+ | Grants for liuzhenchao@127.0.0.1 | +----------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'liuzhenchao'@'127.0.0.1' | +----------------------------------------------------------+ 1 row in set (0.00 sec)

4.3.3 取消授权REVOKE

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host'; mysql> revoke all on *.* from 'liuzhenchao'@'192.168.56.20'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

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

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

转载于:https://www.cnblogs.com/liuzhenchao/p/10758557.html