001MySQL5.6从零开始学

MySQL5.6从零开始学

第一章初始mysql

1.1数据库基础

数据库是由一批数据构成的有序的集合，这些数据被存放在结构化的数据表中。数据表之间相关联，反应客观事物之间的本质联系。数据库系统提供了对数据安全控制和完整性控制
数据库表是一系列二维数组的集合，用来存储数据、操作数据的逻辑结构。列被称为字段，每一列表示记录的一个属性；行被称为记录，是组织数据的单位
数据类型：整数数据类型、浮点数数据类型、定点小数数据类型、二进制数据类型、日期/时间数据类型和字符串数据类型
主键：主键内容唯一，又称主码

1.2数据库技术构成

1.数据库系统

数据库：存储数据
数据库管理程序：管理数据库的软件
数据库应用程序：管理数据库的软件补充
2.SQL语言
1数据定义语言2数据操作语言3数据查询语言4数据控制语言
3.数据库访问技术
使用访问技术可以实现数据库与应用程序之间的链接
ODBC(多种数据库)、GDBC(java)、PDO(PHP)、ADO.net(.net)

1.3什么是mysql

1.客户机-服务器软件
主从式架构结构，简称c/s结构，Client Server
2.mysql版本
社区版企业服务器版

价格速度可移植性容易学丰富的接口安全性连接性支持查询语言

1.4mysql工具

1.mysql命令行使用程序
2.数据恢复及备份工具
3.mysql workbench

1.5如何学习mysql

兴趣、基础、学习新知识、多实践操作

第二章 MySQL的安装与配置

与MySQL的零距离接触

难度：初级
视频链接
简介：本课程涵盖全部MySQL数据库的基础，主要学习MySQL数据库的基础知识、数据表的常用操作及各种约束的使用，以及综合的运用各种命令实现记录进行CURD等操作，目标“看得懂、学得会、做得出”，为后续学习打下基础。

1-1MySQL概述

MySQL由瑞典MySQL AB公司开发，目前属于Oracle公司
MySQL是一个开源的关系型数据库管理系统
MySQL分为社区版和企业版
（关系型数据库：具备数据管理能力、用户管理能力、数据的管理能力）

1-2MySQL的安装与配置

MySQL安装

MySQL安装方式（Windows环境）
1.MSI安装（Windows Installer）
2.ZIP安装
安装步骤
1.双击MSI安装文件
2.同意最终许可协议
3.选择安装类型

Typical:典型安装（服务器、客户端）
Custom:自定义安装
Complete:完全安装

4.准备安装
5.安装进度

配置MySQL

1.运行MySQL配置向导文件
/MySQL/MySQL server 5.5/bin/MySQLInstallerConfig.exe
2.配置向导欢迎界面
3.选择配置类型

Detailed Configuration,详细配置
Standard Configuration,标准配置
4.是否安装为Windows服务
5.设置root用户密码
6.准备执行设置选项
7.配置完成

MySQL目录结构

bin目录，存储可执行文件
data目录，存储数据文件
docs,文档
include目录，存储包含的头文件
lib目录,存储库文件
share，错误消息和字符集文件

MySQL的配置选项

修改编码方式

[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
[client]
post=3306

1-2MySQL的安装与配置（附）Linux环境安装mysql

原文链接

环境：
1.操作系统：CentOS release 6.8 (Final)
2.安装版本： mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
3.下载地址：http://dev.mysql.com/downloads/mysql/
4.下载说明：上边的下载地址是最新版的，如果想下载老版本可以点击页面中的超链接“Looking for previous GA versions?”
5.特殊说明：Linux下MySQL5.6与MySQL5.7安装方法略有不同，本文以5.6为主

安装步骤：

0.卸载老版本MySQL

查找并删除maysql有关的文件

find / -name mysql
rm -rf 上边查找到的路径，多个路径用空格隔开
或用下面的一条命令实现
find / -name mysql|xargs rm -rf

1.在安装包存放目录下执行命令解压文件

tar -zxvf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz

2.删除安装包，重命名解压后的文件

rm -rf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.6.31-linux-glibc2.5-x86_64/ mysql

3.添加mysql用户组和mysql用户

先检查是是否存在用户和组

groups mysql

若无，则添加

groupadd mysql
useradd -r -g mysql mysql  #-r建立系统账号
groups mysql

4.进入mysql目录更改权限

cd mysql/
chown -R mysql:mysql ./

5.执行安装脚本

./scripts/mysql_install_db --user=mysql

安装完后修改当前目录拥有者为root用户修改date目录拥有者为mysql

chown -R root:root ./
chown -R mysql:mysql data

6.更改mysql密码

上一步安装脚本执行输出的日志中告诉我们如何更改密码了

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

执行会报错，因为你还没有启动mysql服务

./support-files/mysql.server start

如果MySQL启动报错，则可能是已经存在MySQL进程，杀掉即可,然后再重启

ps aux | grep mysql
kill -9 上边的进程号
#或者下边一条命令即可杀掉所有MySQL进程
ps aux|grep mysql|awk '{print $2}'|xargs kill -9

MySQL启动之后再执行如下命令更改密码：

./bin/mysqladmin -u root -h localhost.localdomain password 'root'

密码更改后即可登录MySQL

./bin/mysql -h127.0.0.1 -uroot -proot

登录之后将其他用户的密码也改成root

update mysql.user set password=password('root') where user='root';
flush privileges;

7.增加远程登录权限

本地登录Mysql后执行如下命令

grant all privileges on *.* to root@'%' identified by '密码';
flush privileges;

8.将MySQL加入server系统服务

cp support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
service mysqld restart
service mysqld status

9.配置my.cnf

vim my.cnf
#添加以下两条语句并保存退出
default-character-set=utf8
lower_case_table_names=1
max_allowed_packet=100M
#保存退出重启

1-3启动和停止mysql服务

方式：
文件方式：找到服务里的mysql服务进行重新启动
命令行方式：cmd-->net start mysql-->net stop mysql-->net start mysql

1-4MySQL登录与退出

MySQL登录

mysql 参数
-D 打开指定数据库
mysql -V 输出mysql版本信息
mysql -uroot -p #回车 p密码
mysql -uroot -P -h127.0.0.1 #P端口号，默认是3306;h服务器名称
退出：
exit;
quit;
\q;

第三章数据库的基本操作

3.1创建数据库
3.2删除数据库
3.3数据库存储引擎
3.4综合案例——数据库的创建和删除

3.1创建数据库

mysql -h localhost -u root -p #登录
SHOW DATABASES;

Database
information_schema
mysql
performance_schema
test

mysql:系统数据库，描述了用户使用权限
test:测试数据库
剩下两个：系统的存储过程及函数；系统性能参数

创建数据库的SQL语法格式为：
CREATE DATABASE database-name;
SHOW DATABASES;
SHOW CREATE DATABASE test_db; #显示创建数据库时的信息

3.2删除数据库

删除数据库的SQL语法格式为：
DROP DATABASE database_name; #执行时需谨慎，删除后无法恢复

3.3数据库存储引擎

3.3.1 MySQL存储引擎简介
3.3.2 InnoDB存储引擎
3.3.3 MyISAM存储引擎
3.3.4 MEMORY存储引擎
3.3.5 存储引擎的选择

3.3.1 MySQL存储引擎简介

MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。
MySQL5.6支持的存储引擎有：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、Blackhole等
查看mysql支持的存储引擎
SHOW ENGINES;

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| 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)

Engine:引擎名称
Support:是否支持
Comment：注释信息
Transactions：是否支持事务
XA：
Savepoints：

3.3.2 InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）,支持行锁定和外键。默认的存储引擎

3.3.3 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在web、数据仓储和其它应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

3.3.4 MEMORY存储引擎

MEMORY存储引擎将表中的数据存储在内存中，为查询和引用其它表数据提供快速访问方式

3.3.5 存储引擎的选择

不同的存储引擎都有各自的特点，适应于不同的需求，为了做出选择，首先需要考虑每一个存储引擎提供了哪些不同的功能

3.4综合案例-数据库的创建于删除

操作步骤：

1.登录数据库
2.创建数据库zoo
3.选择当前数据库为zoo，并查看zoo数据库的信息
4.删除数据库zoo

[root@PC1 mysql]# mysql -u root -h 127.0.0.1 -p    **#登录数据库**
Enter password: 

mysql> CREATE DATABASE zoo;                **#创建数据库**
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;**#查看数据库**
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zoo                |
+--------------------+
5 rows in set (0.01 sec)

mysql> USE zoo;**#切换到use数据库**
Database changed
mysql> SHOW CREATE DATABASE zoo;**#查看use建库信息**
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| zoo      | CREATE DATABASE `zoo` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP DATABASE zoo;**#删除数据库**
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;**#查看有哪些数据库**
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

第四章数据表的基本操作

4.1 创建数据表

数据表：列的集合

4.1.1创建数据表的语法形式

创建表的语句为CREATE TABLE。

CREATE TABLE 表名（
列名1 数据类型[列级别约束条件][默认值],
列名2 数据类型[列级别约束条件][默认值],
,,,,,,
[表级别约束条件]
）;

例：创建员工表tb_emp1

mysql> CREATE TABLE tb_emp1
    -> (
    -> id int(11),
    -> name varchar(25),
    -> deptId int(11),
    -> salary float
    -> );
Query OK, 0 rows affected (0.06 sec)

4.1.2使用主键约束

主键又称主码，是表中一列或多列的组合。主键约束（Primary Key constraint）要求主键列的数据唯一，并且不允许为空
单字段主键
字段名数据类型 PRIMARY KEY
多字段联合主键
PRIMARY KEY [字段1，字段2，...,字段n]
例：定义数据表tb_emp2,其中主键为id

mysql> CREATE TABLE tb_emp2(
    -> id int(11) PRIMARY KEY,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float
    -> );

例：定义数据表tb_emp3,其主键为id

mysql> CREATE TABLE tb_emp3(
    -> id int(11),
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> PRIMARY KEY(id)
    -> );

例：定义数据表tb_emp4,创建多字段联合主键

mysql> CREATE TABLE tb_emp5(
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> PRIMARY KEY(name,deptId)
    -> );

4.1.3使用外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值
constraint 外键名 foreign key(字段名1[,字段名2,...]) references 主表名(主键列1[,主键列2，...]);

constraint 约束
foreign 外
references 引用
例：定义数据表tb_emp5,并在tb_emp5表上创建外键约束
创建一个部门表tb_dept1:

mysql> create table tb_dept1(
    -> id int(11) primary key,
    -> name varchar(22) not null,
    -> location varchar(50)
    -> );

定义数据表tb_mp5,让他的键deptId作为外键关联到tb_dept1的主键id

mysql> create table tb_emp6(
    -> 
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
    -> );

4.1.4使用非空约束

非空约束(not null constraint)指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时，没有指定值，数据库系统会报错。
字段名数据类型 not null
定义数据表tb_emp6，指定员工的姓名不能为空，sql语句为

mysql> create table tb_emp7(
    -> id int(11) primary key,
    -> name varchar(25) not null,
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept2 foreign key(deptId) references tb_dept1(id) 
    -> );

4.1.5使用唯一性约束

唯一性约束(Unique Constraint)要求该列值唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
字段名数据类型 unique
例：定义数据表tb_dept2,指定部门的名称唯一，SQL语句为：

mysql> create table tb_dept2(
    -> id int(11) primary key,
    -> name varchar(22) unique,
    -> location varchar(50)
    -> );

例：定义数据表tb_dept3,指定部门的名称唯一，SQL语句为：

mysql> create table tb_dept3(
    -> id int(11) primary key,
    -> name varchar(22),
    -> location varchar(50),
    -> constraint sth unique(name)   ##sth唯一性约束的名称
    -> );

4.1.6使用默认约束

默认约束（default constraint）指定某列的默认值
字段名数据类型 default 默认值
例：定义员工表tb_emp8，指定员工的部门编号默认为1111,SQL语句为：

mysql> create table tb_emp8(
    -> id int(11) primary key,
    -> name varchar(25) not null,
    -> deptId int(11) default 1111,
    -> salary float,
    -> constraint fk_emp_depts foreign key(deptId) references tb_dept1(id)
    -> );

4.1.7设置表的属性值自动增加

在数据库应用中，经常希望在每次插入新记录时，系统就会自动生成字段的主键值。可以通过为表
主键添加auto_increment关键字来实现。一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分。
字段名数据名 auto_increment
例：定义员工表tb_emp9，指定员工的编号自动递增，SQL语句为：

mysql> create table tb_emp9(
    -> id int(11) primary key auto_increment,
    -> name varchar(25) not null,
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept5 foreign key(deptId) references tb_dept1(id)
    -> );
mysql> insert into tb_emp9 (name,salary) values('Lucy',1000),('lura',2000),('marry',3000);
mysql> select * from tb_emp9;

4.2查看数据表结构

4.2.1 查看表基本结构语句describe
4.2.2 查看表详细结构语句show create table

4.2.1 查看表基本结构语句describe

该语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为主键、是否有默认值、其他属性等
describe 表名/desc 表名
例：分别使用describe和desc查看tb_dept和tb_emp1的表结构，sql语句为：

mysql> describe tb_dept1; 
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> desc tb_emp1;
Field   //该表中包含了哪些字段
Type    //某个字段的数据类型
Null    //某个字段是否可以为空值，no 表示不能为空值
Key //表示该表是否已标识索引，PRI该列是主键的一部分，UNI该列是唯一索引的一部分，MUL该列中某个给定的值允许出现多次
Default 默认值
Extra 其他属性 如自增属性

4.2.2 查看表详细结构语句show create table

语句可以显示数据表的创建语句
show create table 表名\G; //"\G"使显示易于查看
例：用show creat table查看表tb_emp1的详细信息，sql语句为：

mysql> show create table tb_emp1\G;
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

4.3修改数据表

4.3.1修改表名
4.3.2修改字段的数据类型
4.3.3修改字段名
4.3.4添加字段
4.3.5删除字段
4.3.6修改字段的排列位置
4.3.7更改表的存储引擎
4.3.8删除表的外键约束

4.3.1修改表名

修改表名语法：
alter table <表名> rename [to] <新表名>; //to为可选参数
例：将数据表tb_dept3改名为tb_department3

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept1       |
| tb_dept2       |
| tb_dept3       |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
| tb_emp9        |
+----------------+
11 rows in set (0.00 sec)

mysql> alter table tb_dept3 rename tb_department3;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_department3 |
| tb_dept1       |
| tb_dept2       |
| tb_emp1        |
| tb_emp2        |
| tb_emp3        |
| tb_emp5        |
| tb_emp6        |
| tb_emp7        |
| tb_emp8        |
| tb_emp9        |
+----------------+
11 rows in set (0.01 sec)

4.3.2修改字段的数据类型

把字段的数据类型转换成另一种数据类型
alter table <表名> modify <字段名> <数据类型>
例：将数据表tb_dept1中name字段的数据类型由varchar(22)修改为varchar(30)

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_dept1 modify name varchar(30);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.3.3修改字段名

语法：
alter table <表名> change <旧字段名> <新字段名> <新数据类型>;
例：将数据表tb_dept1中location字段名称改为loc,数据类型不变；将数据表tb_dept1中loc字段改为location，数据类型改为varchar

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_dept1 change location loc varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| loc   | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table tb_dept1 change loc location varchar(60);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(60) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.3.4添加字段

在已存在的表中添加新的字段
alter table <表名> add <新字段名> <数据类型> [约束条件] [first|after已存在字段名]； //first 表的第一个字段
例：
在数据表tb_dept1中添加一个没有完整性约束的int类型的字段managerId(部门经理编号)
在数据表tb_dept1中添加一个不能为空的varchar(12)类型的字段column1
在数据表tb_dept1中第一列添加一个int类型的column2字段
在数据表tb_dept1中name字段后添加一个int类型的字段column3字段

mysql> desc tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(30) | YES  |     | NULL    |       |
| location | varchar(60) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table tb_dept1 add managerId int(11);
mysql> alter table tb_dept1 add column1 varchar(12) not null;
mysql> alter table tb_dept1 add column2 int(11) first;
mysql> alter table tb_dept1 add column3 int(11) after name;
mysql> desc tb_dept1;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| column2   | int(11)     | YES  |     | NULL    |       |
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(30) | YES  |     | NULL    |       |
| column3   | int(11)     | YES  |     | NULL    |       |
| location  | varchar(60) | YES  |     | NULL    |       |
| managerId | int(11)     | YES  |     | NULL    |       |
| column1   | varchar(12) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

4.3.5删除字段

删除字段是将数据表中的某个字段从表中移除
alter table <表名> drop <字段名>；
例：删除数据表tb_dept1表中的column2字段

mysql> alter table tb_dept1 drop column2;

4.3.6修改字段的排列位置

alter table来修改字段的相对位置
alter table <表名> modify <字段1> <数据类型> first|after <字段2>；
例：将数据表tb_dept1中的column1字段修改为表的第一个字段
例：将数据表tb_dept1中的column1字段修改为在locatiion字段之后

mysql> alter table tb_dept1 modify column1 varchar(12) first;
mysql> alter table tb_dept1 modify column1 varchar(12) after location;

4.3.7更改表的存储引擎

MySQL中，存储引擎是指MySQL数据库中表的存储类型，可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎
alter table <表名> engine=<更改后的存储引擎名>;
例：将数据表tb_department3的存储引擎修改为MyISAM

mysql> show create table tb_department3\G;
*************************** 1. row ***************************
       Table: tb_department3
Create Table: CREATE TABLE `tb_department3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sth` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> alter table tb_department3 engine=myisam;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_department3\G;
*************************** 1. row ***************************
       Table: tb_department3
Create Table: CREATE TABLE `tb_department3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sth` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

4.3.8删除表的外键约束

对于数据库中定义的外键，如果不在需要，可以删除。外键一旦删除就回接触主表与从表之间关系
alter table <表名> drop foreign key <外键约束名>
删除数据表tb_emp9中的外键约束

mysql> create table tb_emp10(
    -> id int(10) primary key,
    -> name varchar(22),
    -> deptId int(11),
    -> salary float,
    -> constraint fk_emp_dept foreign key (deptId) references tb_dept1(id)
    -> );
mysql> show create table tb_emp10\G;
*************************** 1. row ***************************
       Table: tb_emp10
Create Table: CREATE TABLE `tb_emp10` (
  `id` int(10) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptId`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified
mysql> alter table tb_emp10 drop foreign key fk_emp_dept;

4.4删除数据表

4.4.1 删除没有被关联的表
4.4.2 删除被其它表关联的主表

4.4.1 删除没有被关联的表

MySQL中，使用drop table可一次删除一个或多个没有被关联的表
drop table [if exists]表1，表2，...表n;
删除数据表tb_dept2

mysql> drop table tb_dept2;

4.4.2 删除被其它表关联的主表

在数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除，将破坏表的参照完整性。如果必须要删除，可以先删除与之关联的子表，再删除父表。但是这样同时删除了两个表中的数据。但有的情况下可能要保留子表，这时如果要单独删除父表，只需要将关联的表的外键约束条件取消，然后就可以删除父表
例：删除被数据表tb_emp关联的数据表tb_dept2

mysql> create table tb_dept2(
    -> id int(11) primary key,
    -> name varchar(22),
    -> location varchar(22)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table tb_emp(
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptId int(11),
    -> salary float,
    -> constraint
    -> fk_emp_dept foreign key (deptId) references tb_dept2(id)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> drop table tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> alter table tb_emp drop foreign key fk_emp_dept;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table tb_dept2;
Query OK, 0 rows affected (0.04 sec)

4.5综合案例

1.登录数据库
mysql -h localhost -u root -p

2.创建数据库Company
create database Company;

3.创建表offices

mysql> create table offices(
    -> officeCode int(10) not null unique,
    -> city varchar(22) not null,
    -> address varchar(22) not null,
    -> country varchar(22) not null,
    -> postalCode varchar(15) not null,
    -> primary key (officeCode)
    -> );

4.创建表employees

mysql> create table employees(
    -> employeeNumber int(11) not null primary key auto_increment,
    -> lastName varchar(22) not null,
    -> firstName varchar(22) not null,
    -> mobile varchar(50) not null,
    -> officeCode int(10) not null,
    -> jobTitle varchar(12) not null,
    -> birth datetime,
    -> note varchar(255),
    -> sex varchar(5),
    -> constraint office_fk foreign key(officeCode) references offices(officeCode)
    -> );

5.将表employees的mobile字段修改到officeCode字段后面
alter table employees modify mobile varchar(50) after officeCode;
6.将birth字段改名为employee_birth
alter table employees change birth employee_birth datetime;
7.修改sex字段，数据类型为char(1),非空约束
alter table employees modify sex char(2) not null;
8.删除字段note
alter table employees drop note;
9.增加字段名favoriate_activity,数据类型为varchar(100)
alter table employees add favoriate_activity varchar(100);
10.删除表offices
1)删除employees表的外键约束
alter table officeCode drop foreign key office_fk;
2）删除表offices
drop table offices;
11.修改employees存储引擎为MyISAM
alter table employees engine=myisam;
12.将表employees改为employees_info
alter table employees rename employees_info;

第五章数据类型和运算符

5.1MySQL数据类型介绍
5.2如何选择数据类型
5.3常见运算符介绍
5.4综合案例——运算符的使用

5.1MySQL数据类型介绍

5.1.1整数类型
5.1.2浮点数类型和定点数类型
5.1.3日期与时间类型
5.1.4字符串类型
5.1.5二进制类型

5.1.1整数类型

tinyint //最小整型
smallint //小整型
mediumint //中间整型
int(integer) //整型
bigint //大整型
例：创建表tmp1，其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

mysql> create table tmp1(x tinyint, y smallint, z mediumint, m int, n nigint);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nigint)' at line 1
mysql> create table tmp1(x tinyint, y smallint, z mediumint, m int, n bigint);
Query OK, 0 rows affected (0.03 sec)

mysql> desc tmp1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | tinyint(4)   | YES  |     | NULL    |       |
| y     | smallint(6)  | YES  |     | NULL    |       |
| z     | mediumint(9) | YES  |     | NULL    |       |
| m     | int(11)      | YES  |     | NULL    |       |
| n     | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.1.2浮点数类型和定点数类型

mysql中使用浮点数和定点数来表示小数

浮点类型：float double
定点类型：decimal
例：创建表tmp2，其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，

mysql> create table tmp2 ( x float(5,1), y double(5,1), z decimal(5,1) );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tmp2 values(5.12,5.15,5.123);
mysql> desc tmp2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | float(5,1)   | YES  |     | NULL    |       |
| y     | double(5,1)  | YES  |     | NULL    |       |
| z     | decimal(5,1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
mysql> select * from tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.1 |
+------+------+------+
1 row in set (0.00 sec)

5.1.3日期与时间类型

mysql中有多种表示时间的数据类型

datetime
date
timestamp
time
year
例：创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，’2010’，’2166’

mysql> create table tmp3( y year );
mysql> insert into tmp3 values(2010),('2010');
mysql> insert into tmp3 values(2010),('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 2
select * from tmp3;

例：向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’

mysql> insert into tmp3 values('0'),('00'),('77'),('10');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp3;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
4 rows in set (0.00 sec)

例：向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、78和11，

mysql> insert into tmp3 values(0),(78),(11);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tmp3;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1977 |
| 2010 |
| 0000 |
| 1978 |
| 2011 |
+------+
7 rows in set (0.00 sec)

例：创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’

mysql> create table tmp4( t time );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp4 values('10:20:20'),('22:23'),('2 10:10'),('3 02'),('10');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from tmp4;
+----------+
| t        |
+----------+
| 10:20:20 |
| 22:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
5 rows in set (0.00 sec)
在使用’D HH’格式时，小时一定要使用双位数值，如果是小于10的小时数，应在前面加0。

例：向表tmp4中插入值’101112’，111213,’0’，107010

DELETE FROM tmp4;
INSERT INTO tmp4 values('101112'),(111213),( '0')
INSERT INTO tmp4 values ( 107010);
ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1
show warnings; 
+---------+------+--------------------------------------------------+
| Level   | Code | Message                         |
+---------+------+--------------------------------------------------+
| Error | 1292 | Incorrect time value: '107010' for column 't' at row 1
+---------+------+---------------------------------------------------+
可以看到，第二次在插入记录的时候，数据超出了范围，原因是107010的分钟部分超过了60，分钟部分是不会超过60的，查看结果：

例：创建数据表tmp5，定义数据类型为DATE的字段d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期

mysql> create table tmp5( d date );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tmp5 values('1998-08-08'),('19980808'),('99-09-09'),('090909');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tmp5;
+------------+
| d          |
+------------+
| 1998-08-08 |
| 1998-08-08 |
| 1999-09-09 |
| 2009-09-09 |
+------------+
4 rows in set (0.00 sec)

mysql>

例：向表中插入系统当前时间

mysql> delete from tmp5;
Query OK, 4 rows affected (0.02 sec)

mysql> insert into tmp5 values( current_date() ),( now() );
Query OK, 2 rows affected, 1 warning (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select * from tmp5;
+------------+
| d          |
+------------+
| 2016-10-20 |
| 2016-10-20 |
+------------+
2 rows in set (0.00 sec)
CURRENT_DATE只返回当前日期值，不包括时间部分；NOW()函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

例;当前日期时间值

CREATE TABLE tmp6(  dt DATETIME );
INSERT INTO tmp6 values('1998-08-08 08:08:08'),('19980808080808'),('20101010101010');   //字符串格式
INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');
INSERT INTO tmp6 values(19990909090909), (101010101010);  //数字格式
INSERT INTO tmp6 values( NOW() );
例：TIMESTAMP
CREATE TABLE tmp7( ts TIMESTAMP);
INSERT INTO tmp7 values ('19950101010101'),('950505050505'),('1996-02-02 02:02:02'),('97@03@03 03@03@03'),(121212121212),( NOW() );
SELECT * FROM tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2013-03-24 09:17:49 |
+-------------------------+
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的

例：更改时区

DELETE FROM tmp7;
INSERT INTO tmp7 values( NOW() );
SELECT * FROM tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 2013-03-24 10:02:42 |
+-------------------------+
set time_zone='+10:00';
SELECT * FROM tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 2013-03-24 12:02:42 |
+-------------------------+

【例 5.17】创建数据表tmp7，定义数据类型为TIMESTAMP的字段ts，向表中插入

create table tmp7 values ('19950101010101'),('950505050505'),('1996-02-02 02:02:02'),('97@03@03 03@03@03'),('121212121212'),( now() );
select *from tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2013-03-24 09:17:49 |
+-------------------------+

由结果可以看到，’19950101010101’被转换为1995-01-01 01:01:01；’950505050505’被转换为1995-05-05 05:05:05；’1996-02-02 02:02:02’被转换为1996-02-02 02:02:02；’97@03@03 03@03@03’被转换为1997-03-03 03:03:03；121212121212被转换为2012-12-12 12:12:12；NOW()被转换为系统当前日期时间2013-03-24 09:17:49。
TIMESTAMP与DATETIME除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而TIMESTAMP值的存储是以UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

【例5.18】向tmp7表中插入当前日期，查看插入值，更改时区为东10区，再次查看插入值，

delete from tmp7;
insert into tmp7 values( now() );
select * from tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 2013-03-24 10:02:42 |
+-------------------------+
set time_zone='+10:00';
select * from tmp7;
+-------------------------+
| ts                |
+-------------------------+
| 2013-03-24 12:02:42 |
+-------------------------+

5.1.4字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其他数据，比如图片和声音的二进制数据

char
varchar
binary
varbinary
blob
text
enum
set
【例5.20】创建tmp8表，定义字段ch和vch数据类型依次为CHAR(4)、VARCHAR(4)向表中插入数据“ab ”

create table tmp8(
ch char(4), vch varchar(4)
);
insert into tmp8 values('ab ', 'ab ');
select * from tmp8;
此时ab都存入了相应的表，但char将末尾的两个空格删除了，vch字段保留了末尾的两个空格

【例5.21】创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值

create table tmp9 ( enm enum('first', 'second', 'third') )；
insert into tmp9 values('first'),('second'),('third'), (NULL);
select enm, enm+0 from tmp9; 查看索引值

【例5.22】创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为('excellent','good', 'bad')，向表tmp10中插入数据’good’，1，2，3，’best’

create atble tmp10 (soc int, level enum('excellent', 'good', 'bad') );
insert into tmp10 values(70,'good'), (90,1),(75,2),(50,3);
insset into tmp10 values(100,'best'); 报错

【例5.23】创建表tmp11，定义SET类型的字段s，取值列表为('a', 'b', 'c', 'd')，插入数据('a')，('a,b,a')，('c,a,d')，('a,x,b,y')

create table tmp11(s set('a', 'b', 'c', 'd') );
insert into tmp11 values('a'), ('a,b,c'), ('b,c');
select * from table tmp11;

【例5.24】创建表tmp12，定义BIT(4)类型的字段b，向表中插入数据2、9、15、16

create table tmp12( b BIT(4) );
insert into tmp12 values(2), (9), (15);
select bin(b+0) from tmp12;
b+0表示将二进制的结果转换为对应的数字的值，BIN() 函数将数字转换为二进制。从结果可以看到，成功的将3个数插入到表中。

【例5.25】创建表tmp13，定义BINARY(3)类型的字段b和VARBINARY(3)类型的字段vb，并向表中插入数据’5’，比较两个字段的存储空间。

create table tmp13( b binary(3), vb varbinary(30));
insert into tmp13 values(5,5);
select length(b), length(vb) from tmp13;
可以看到，b字段的值数据长度为3，而vb字段的数据长度仅为插入的一个字符的长度1。
由执行结果可以看出，b字段和vb字段的长度是截然不同的，因为b字段不足的空间填充了’\0’，而vb字段则没有填充。

5.1.5二进制类型

存储二进制数据类型的数据类型

bit
binary
varbinary
tinyblob
blob
mediumblb
longblob
【例5.26】创建表tmp14，定义数据类型为INT的字段num，插入值64，对num值进行算术运算：

create table tmp14 ( num int );
insert into tmp14 value(64);
select num, num+10, num-3+5, nun+5-3, num+36.5 from tmp14;

对tmp14表中的num进行乘法、除法运算。

select num, num*2, num/2, num/3, num%3 from tmp14;

【例5.28】用0除num。

select num, num/0, num%0 from tmp14;

【例5.29】使用’=’进行相等判断，

select 1=0, '2'=2, 2=2, '0.02'=0, 'b'='b', (1+3) = (2+2), null =null;
+-----+-------+-----+----------+---------+------------------+-----------------+
| 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3) = (2+2) | NULL=NULL |
+-----+-------+-----+----------+---------+------------------+-------------------+
| 0 | 1 | 1 | 0 | 1 | 1 | NULL |
+-----+-------+-----+----------+---------+------------------+-------------------+
1 row in set (0.00 sec)

【例5.30】使用’<=>’进行相等的判断

SELECT 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+1),NULL<=>NULL;
由结果可以看到，’<=>’在执行比较操作时和’=’的作用是相似的，唯一的区别是’<=>’可以用来对NULL进行判断，两者都为NULL时返回值为1。
3．不等于运算符 <>或者 !=
‘<>’或者’!=’用于判断数字、字符串、表达式不相等的判断。如果不相等，返回值为1；否则返回值为0。这两个运算符不能用于判断空值NULL。

【例5.32】使用’<=’进行比较判断，SQL语句如下：
SELECT 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1),NULL<=NULL;
+---------------+-------+------+--------+--------------------+------------------------+
| 'good'<='god' | 1<=2 | 4<=4 | 5.5<=5 | (1+3) <= (2+1)| NULL<=NULL |
+---------------+-------+------+--------+-------------------+-------------------------+
| 0 | 1 | 1 | 0 | 0 | NULL |
+---------------+-------+------+--------+-------------------+-------------------------+
1 row in set (0.00 sec)
由结果可以看到，左边操作数小于或者等于右边时，返回值为1，例如：4<=4；当左边操作数大于右边时，返回值为0，例如：’good’第3个位置的’o’字符在字母表中的顺序大于’god’中的第3个位置的’d’字符，因此返回值为0；同样比较NULL值时返回NULL。
【例5.33】使用’<’进行比较判断，SQL语句如下：

SELECT 'good'<'god', 1<2, 4<4, 5.5<5, (1+3) < (2+1),NULL<NULL;

【例5.34】使用’>=’进行比较判断，SQL语句如下：

SELECT 'good'>='god', 1>=2, 4>=4, 5.5>=5, (1+3) >= (2+1),NULL>=NULL;

【例5.35】使用’>’进行比较判断，SQL语句如下：

SELECT 'good'>'god', 1>2, 4>4, 5.5>5, (1+3) > (2+1),NULL>NULL;

【例5.36】使用IS NULL、ISNULL和IS NOT NULL判断NULL值和非NULL值，SQL语句如下：

SELECT NULL IS NULL, ISNULL(NULL),ISNULL(10), 10 IS NOT NULL;

【例5.37】使用BETWEEN AND 进行值区间判断，输入SQL语句如下：

SELECT 4 BETWEEN 4 AND 6, 4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;

SELECT 'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c';

【例5.38】使用LEAST运算符进行大小判断，SQL语句如下：

SELECT least(2,0), least(20.0,3.0,100.5), least('a','c','b'),least(10,NULL);

【例5.39】使用GREATEST运算符进行大小判断，SQL语句如下：

SELECT greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'),greatest(10,NULL);

【例5.40】使用IN、NOT IN运算符进行判断，SQL语句如下：

SELECT 2 IN (1,3,5,'thks'), 'thks' IN (1,3,5,'thks');

【例5.41】存在NULL值时的IN查询，SQL语句如下：

SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');

【例5.42】使用运算符LIKE进行字符串匹配运算，SQL语句如下：

SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_','stud' LIKE '%d','stud' LIKE 't_ _ _', 's' LIKE NULL;

【例5.43】使用运算符REGEXP进行字符串匹配运算，SQL语句如下：

SELECT 'ssky' REGEXP '^s', 'ssky' REGEXP 'y$', 'ssky' REGEXP '.sky', 'ssky' REGEXP '[ab]';

【例5.44】分别使用非运算符“NOT”和“！”进行逻辑判断，SQL语句如下：

SELECT NOT 10, NOT (1-1), NOT -5, NOT NULL, NOT 1 + 1;

SELECT !10, !(1-1), !-5, ! NULL, ! 1 + 1;

【例5.45】分别使用与运算符“AND”和“&&”进行逻辑判断，SQL语句如下：

SELECT 1 AND -1,1 AND 0,1 AND NULL, 0 AND NULL;

SELECT 1 && -1,1 && 0,1 && NULL, 0 && NULL;

【例5.46】分别使用或运算符“OR”和“||”进行逻辑判断，SQL语句如下：

SELECT 1 OR -1 OR 0, 1 OR 2,1 OR NULL, 0 OR NULL, NULL OR NULL;
SELECT 1 || -1 || 0, 1 || 2,1 || NULL, 0 || NULL, NULL || NULL;

【例5.47】使用异或运算符“XOR”进行逻辑判断，SQL语句如下：

SELECT 1 XOR 1, 0 XOR 0, 1 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1;
执行上面的语句，结果如下。
SELECT 1 XOR 1, 0 XOR 0, 1 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1;

【例5.48】使用位或运算符进行运算，SQL语句如下：

SELECT 10 | 15, 9 | 4 | 2;

【例5.49】使用位与运算符进行运算，SQL语句如下：

SELECT 10 & 15, 9 &4& 2;

【例5.50】使用位异或运算符进行运算，SQL语句如下：

SELECT 10 ^ 15, 1 ^0, 1 ^ 1;

【例5.51】使用位左移运算符进行运算，SQL语句如下：

SELECT 1<<2, 4<<2;

【例5.52】使用位右移运算符进行运算，SQL语句如下：

SELECT 1>>1, 16>>2;

【例5.53】使用位取反运算符进行运算，SQL语句如下：

SELECT 5 & ~1;

1. 案例目的

创建数据表，并对表中的数据进行运算操作，掌握各种运算符的使用方法。
创建表tmp15，其中包含VARCHAR类型的字段note和INT类型的字段price，使用运算符对表tmp15中不同的字段进行运算；使用逻辑操作符对数据进行逻辑操作；使用位操作符对数据进行位操作。
本案例使用数据表tmp15，首先创建该表，SQL语句如下：

CREATE TABLE tmp15 (note VARCHAR(100), price INT);

向表中插入一条记录，note值为“Thisisgood”，price值为50，SQL语句如下：

INSERT INTO tmp15 VALUES(“Thisisgood”, 50);

2. 案例操作过程

对表tmp15中的整型数值字段price进行算术运算，执行过程如下：

SELECT price, price + 10, price -10, price * 2, price /2, price%3 FROM tmp15 ;
+--------+------------+-----------+-----------+-----------+---------+
| price | price + 10 | price -10 | price * 2 | price /2 | price%3 |
+--------+------------+-----------+-----------+-----------+---------+
| 50 | 60 | 40 | 100 | 25.0000 | 2 |
+--------+------------+-----------+-----------+-----------+---------+

对表tmp15中的整型数值字段price进行比较运算，执行过程如下：

SELECT price, price> 10, price<10, price != 10, price =10, price <=>10,price <>10 FROM tmp15 ;
+--------+-----------+-----------+--------------+------------+---------------+--------------+
| price | price> 10 | price<10 | price != 10 | price =10 | price <=>10 | price <>10 |
+--------+-----------+-----------+--------------+------------+---------------+--------------+
| 50 | 1 | 0 | 1 | 0 | 0 | 1 |
+------ -+-----------+-----------+--------------+------------+----------------+--------------+

判断price值是否落在30~80区间；返回与70，30相比最大的值，判断price是否为IN列表（10, 20, 50, 35）中的某个值，执行过程如下：
mysql> SELECT price, price BETWEEN 30 AND 80, GREATEST(price, 70,30), price IN (10, 20, 50,35) FROM tmp15 ;
+--------+------------------------------------+--------------------------------+----------------------------+
| price | price BETWEEN 30 AND 80 | GREATEST(price, 70,30) | price IN (10, 20, 50,35) |
+--------+------------------------------------+--------------------------------+----------------------------+
| 50 | 1 | 70 | 1 |
+--------+------------------------------------+--------------------------------+----------------------------+
对tmp15中的字符串数值字段note进行比较运算，判断表tmp15中note字段是否为空；使用LIKE判断是否以字母’t’开头；使用REGEXP判断是否以字母’y’结尾；判断是否包含字母’g’或者’m’，执行过程如下：
mysql> SELECT note, note IS NULL, note LIKE 't%', note REGEXP '$y' ,note REGEXP '[gm]' FROM tmp15 ;
+--------------+----------------+-------------------+-----------------------+--------------------------+
| note | note IS NULL | note LIKE 't%' | note REGEXP '$y' | note REGEXP '[gm]' |
+--------------+----------------+------------------+------------------------+--------------------------+
| Thisisgood | 0 | 1 | 0 | 1 |
+--------------+-----------------+------------------+----------------------+---------------------------+
将price字段值与NULL，0进行逻辑运算，执行过程如下：
mysql> SELECT price, price && 1, price && NULL, price||0, price AND 0, 0 AND NULL, price OR NULL FROM tmp15 ;
+--------+--------------+--------------------+----------+---------------+------------------+--------------------+
| price | price && 1 | price && NULL | price||0 | price AND 0 | 0 AND NULL | price OR NULL |
+--------+--------------+--------------------+----------+---------------+------------------+--------------------+
| 50 | 1 | NULL | 1 | 0 | 0 | 1 |
+--------+--------------+-------------------+-----------+---------------+------------------+--------------------+
1 row in set (0.00 sec)
mysql> SELECT price,!price,NOT NULL,price XOR 3, 0 XOR NULL, price XOR 0 FROM tmp15 ;
+--------+--------+--------------+----------------+-----------------+-----------------+
| price | !price | NOT NULL | price XOR 3 | 0 XOR NULL | price XOR 0 |
+--------+--------+--------------+----------------+-----------------+-----------------+
| 50 | 0 | NULL | 0 | NULL | 1 |
+--------+--------+--------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)
将price字段值与2、4进行按位与、按位或操作，并对price进行按位操作，执行过程如下：
mysql> SELECT price, price&2 , price|4, ~price FROM tmp15 ;
+--------+----------+---------+-----------------------------+
| price | price&2 | price|4 | ~price |
+--------+----------+---------+-----------------------------+
| 50 | 2 | 54 | 18446744073709551565 |
+--------+----------+---------+------------------------------+
将price字段值分别左移和右移两位，执行过程如下：
mysql> SELECT price, price<<2, price>>2 FROM tmp15 ;
+--------+------------+-----------+
| price | price <<2 | price>>2 |
+--------+------------+-----------+
| 50 | 200 | 12 |
+--------+------------+-----------+

最后编辑于：2017.12.04 05:57:17

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,230评论 5赞 476
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,261评论 2赞 380
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,089评论 0赞 336
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,542评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,542评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,544评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,922评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,578评论 0赞 257
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,816评论 1赞 296
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,576评论 2赞 320
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,658评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,359评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,937评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,920评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,156评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,859评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,381评论 2赞 342