《MySQL入门》

MySQL

什么是数据库？

数据库是一种存储并管理数据的软件系统

存储：持久化

管理：增删改查

一、常用的存储数据的方式：

1、Java中的变量：生命周期短，不能实现持久化

2、序列化：管理数据时依赖于Java中的反序列化

3、txt，办公软件：没有统一的方式管理数据

4、数据库

二、数据库概述

DBMS：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。

DB：数据库（Database）

SQL：结构化查询语言，（Structure Query Language），专门用来操作/访问数据库的通用语言。

MySQL：其中的一款关系型数据库管理系统

以下是2019年DB-Engines Ranking 对各数据库受欢迎程度进行调查后的统计结果：（查看数据库最新排名:https://db-engines.com/en/ranking）

2.1、数据库类型：

关系型数据库： 采用关系模型来组织数据，简单来说，关系模型指的就是二维表格模型。类似于Excel工作表。 特点：所存储的数据有一定的关系、将数据存储在磁盘上、重在持久化。

非关系型数据库：可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，通过减少很少用的功能，来提高性能。特点：以key-value或json的方式存储数据、将数据存储在内存中、重在性能，读取速度快。

2.2、关系型数据库设计规则:

遵循ER模型 ：

• E entity 代表实体的意思 对应到数据库当中的一张表
• R relationship 代表关系的意思

具体体现：

• 将数据放到表中，表再放到库中。

• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

• 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。

• 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。

• 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。

三、MySQL概述

MySQL是一种开放源代码的关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年,SUN又被Oracle收购。目前 MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，使得很多互联网公司选择了MySQL作为网站数据库（Facebook, Twitter, YouTube，阿里的蚂蚁金服，去哪儿，魅族，百度外卖，腾讯）。

四、MySQL卸载

步骤一：软件的卸载准备

学习网络编程时，TCP/IP协议程序有服务器端和客户端。mysql这个数据库管理软件是使用TCP/IP协议。我们现在要卸载的是mysql的服务器端，它没有界面。

【计算】-->右键-->【管理】-->【服务】-->【mysql的服务】-->【停止】

 

步骤二：软件的卸载

方式一：通过控制面板卸载

方式二：通过360或电脑管家等软件卸载

方式三：通过安装包提供的卸载功能卸载

or

步骤三：清理残余文件

如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。

（1）服务目录：mysql服务的安装目录

（2）数据目录：默认在C:\ProgramData\MySQL

如果自己单独指定过数据目录，就找到自己的数据目录，例如：

注意：请在卸载前做好数据备份

在操作完以后，需要重启计算机，然后进行安装即可。如果仍然安装失败，需要继续操作如下步骤四。

 

步骤四：清理注册表（选做）

如何打开注册表编辑器：在系统的搜索框中输入regedit

• HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL服务 目录删除

• HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL服务 目录删除

• HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL服务 目录删除

• HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL服务 目录删除

• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL服务目录删除

• HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL服务删除

注册表中的ControlSet001,ControlSet002,不一定是001和002,可能是ControlSet005、006之类

步骤五：清理服务列表中的服务名

如果在windows操作系统，删除注册表后，在服务列表中仍然存在原来的服务名，可以通过如下命令删除。

sc  delete  服务名

步骤六：清理原来的环境变量

找到path环境变量，将其中关于mysql的环境变量删除，切记不要全部删除。

例如：删除 D:\ProgramFiles\mysql\MySQLServer5.7\bin; 这个部分

五、MySQL安装

双击mysql-installer-community-5.7.28.0.msi,进入安装界面后选择Custom自定义安装，点击Next

在左侧目录中选择MySQL Server 5.7.28 -X64，点击→加载到右侧，在选择一下要安装的产品名，在右下角会出现Advanced Options，单击它可以设置安装路径

例如将安装路径和数据路径修改到D:\develop\下，点击OK。注意，安装目录避免空格和中文

点击Next下一步

点击Execute执行安装

有的可能会出现提示需要先安装C++动态库等，那就先安装C++动态库，没有提示说明操作系统中有你需要的动态库。

安装完成，点击Next下一步

点击Next下一步

点击Next下一步

将MySQL配置类型选择Development Computer开发电脑设置，点击Next下一步

设置MySQL密码，因为管理员用户为root，学习初期建议将MySQL数据库密码也设置为root，设置完成后，点击Next下一步

设置Windows Service 系统服务，设置完毕后点击Next下一步

日志设置，不做任何修改，点击Next下一步

高级设置，不做任何修改，点击Next下一步

点击Execute执行设置

安装完成，点击Finish完成

六、MySQL数据库服务的启动和停止

MySQL软件的服务器端必须先启动，客户端才可以连接和使用使用数据库。

方式一：图形化方式

• 计算机（点击鼠标右键）==》管理（点击）==》服务和应用程序（点击）==》服务（点击）==》MySQL57（点击鼠标右键）==》启动或停止（点击）

• 控制面板（点击）==》系统和安全（点击）==》管理工具（点击）==》服务（点击）==》MySQL57（点击鼠标右键）==》启动或停止（点击）

• 任务栏（点击鼠标右键）==》启动任务管理器（点击）==》服务（点击）==》MySQL57（点击鼠标右键）==》启动或停止（点击）

方式二：命令行方式

启动 MySQL 服务命令：
net start MySQL57

停止 MySQL 服务命令：
net stop MySQL57

七、MySQL数据库环境变量的配置

 

八、MySQL数据库客户端的登录

方式一：MySQL自带客户端

开始菜单==》所有程序==》MySQL==》MySQL Server 5.7==》MySQL 5.7 Command Line Client

方式二：命令行

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

例如：mysql -h localhost -P 3306 -u root -proot

注意：

（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格

mysql -hlocalhost -P3306 -uroot -proot

（2）密码建议在下一行输入

mysql -h localhost -P 3306 -u root -pEnter password:****

（3）如果是连本机：-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略

简写成：

mysql -u root -p
Enter password:****

连接成功后，有关于MySQL Server服务版本的信息，还有第几次连接的id标识。

也可以在命令行通过以下方式获取MySQL Server服务版本的信息

或登录后，通过以下方式查看当前版本信息：

方式三：可视化工具

例如：Navicat Preminum，SQLyog 等工具

• SQLyog

• Navicat

九、MySQL数据库的编码配置

命令行操作sql乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');

show variables like 'character_%';
show variables like 'collation_%';

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

修改my.ini配置文件

在路径：D:\ProgramFiles\mysql\MySQLServer5.7Data 找到my.ini文件修改内容1：    找到[mysql]命令，大概在63行左右，在其下一行添加         default-character-set=utf8修改内容2:    找到[mysqld]命令，大概在76行左右，在其下一行添加        character-set-server=utf8        collation-server=utf8_general_ci修改完毕后，重启MySQL57服务

• 查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

• 如果是以上配置就说明对了

十、命令行操作sql仍然乱码问题

mysql> INSERT INTO t_stu VALUES(1,'张三','男');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

原因：按照刚才的配置，服务器端认为你的客户端的字符集是utf-8，而实际上你的客户端的字符集是GBK。

查看所有字符集：SHOW VARIABLES LIKE 'character_set_%';

解决方案，设置当前连接的客户端字符集 “SET NAMES GBK;”

set names gbk;是为了告诉服务器,客户端用的GBK编码,防止乱码。

有的时候，这样还不能解决，例如，某些win10操作系统环境下，那么修改命令行的属性-》选项-》勾选使用旧版控制台，然后重启电脑

十一、MySQL需要的数据库和表

CREATE DATABASE test;

USE `test`;

/*Table structure for table `t_department` */

DROP TABLE IF EXISTS `t_department`;

CREATE TABLE `t_department` (
  `did` INT(11) NOT NULL AUTO_INCREMENT,
  `dname` VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (`did`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `t_department` */

INSERT  INTO `t_department`(`did`,`dname`) VALUES (1,'财务部'),(2,'市场部'),(3,'开发部'),(4,'后勤部'),(5,'人事部');

/*Table structure for table `t_employee` */

DROP TABLE IF EXISTS `t_employee`;

CREATE TABLE `t_employee` (
  `eid` INT(11) NOT NULL AUTO_INCREMENT,
  `ename` VARCHAR(20) DEFAULT NULL,
  `tel` VARCHAR(20) DEFAULT NULL,
  `sex` CHAR(1) DEFAULT NULL,
  `salary` DOUBLE DEFAULT NULL,
  `kpi` DOUBLE DEFAULT NULL,
  `birthday` DATE DEFAULT NULL,
  `hiredate` DATE DEFAULT NULL,
  `did` INT(11) DEFAULT NULL,
  PRIMARY KEY (`eid`),
  KEY `did` (`did`),
  CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`did`) REFERENCES `t_department` (`did`)
) ENGINE=INNODB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

/*Data for the table `t_employee` */

INSERT  INTO `t_employee`(`eid`,`ename`,`tel`,`sex`,`salary`,`kpi`,`birthday`,`hiredate`,`did`)
 VALUES (1,'张三','18049273785','女',1227,NULL,'1991-05-14','2011-04-17',1),
 (2,'李四','13241781270','男',17496,0.45,'1987-08-21','2008-07-12',2),
 (3,'王五','18054724408','女',7765,0.44,'1996-09-16','2014-09-13',3),
 (4,'赵六','13280270847','女',18857,0.82,'1985-04-16','2006-07-18',4),
 (5,'田七','18025800687','男',5144,0.81,'1993-09-15','2019-08-14',1),
 (6,'崔八','13276010428','女',31833,0.16,'1976-06-11','1998-09-18',2),
 (7,'杨九','18064830214','女',2425,NULL,'1998-06-18','2017-01-14',5),
 (8,'杨迪','13239239602','男',2595,NULL,'1990-06-11','2017-04-18',3),
 (9,'李冰冰','18046749015','女',6695,0.59,'1999-01-10','2011-09-13',1),
 (10,'范冰冰','13268166729','女',17474,0.51,'1988-05-19','2000-04-12',3),
 (11,'刘德华','18039254435','男',6199,0.8,'1995-08-15','2018-01-12',2),
 (12,'佟丽娅','13223114843','女',15917,0.46,'1981-08-17','2006-07-16',5),
 (13,'赵敏','18056025135','女',911,NULL,'1993-07-19','2019-08-13',4),
 (14,'周杰伦','13211074033','男',45512,0.32,'1970-07-14','1996-07-18',3),
 (15,'周芷若','18062362372','女',5958,0.9,'1994-05-12','2011-06-17',2),
 (16,'陈平安','13286025640','女',17760,0.14,'1982-08-15','2002-01-11',4),
 (17,'沈腾','18043736457','男',7056,0.98,'1996-03-17','2019-05-16',2),
 (18,'杨娜','13296906104','女',7469,0.5,'1998-00-14','2019-05-18',3),
 (19,'杨颖','18031595176','女',7408,0.56,'1992-09-10','2014-00-16',4),
 (20,'邓超','13226453603','男',14188,0.3,'1980-06-12','2003-01-18',3),
 (21,'刘欣','18026766512','女',5871,0.83,'1993-04-13','2010-03-16',2),
 (22,'范童','13241549709','女',32306,0.7,'1972-09-18','1998-06-16',3),
 (23,'李晨','18039047033','男',7134,0.25,'1995-04-18','2012-01-10',4),
 (24,'崔丽娜','13228387737','女',48703,0.52,'1972-09-10','1992-02-16',1),
 (25,'邓丽丽','18014935635','女',8055,0.75,'1986-04-15','2006-09-14',2),
 (26,'赵光照','13217289561','男',46596,0.49,'1974-08-10','1993-00-12',4),
 (27,'张浮萍','18057537081','女',8873,0.04,'1982-08-13','2005-05-11',3),
 (28,'赵凯丽','13212846012','女',17374,0.93,'1982-03-10','2007-06-18',2),
 (29,'张明','18042878502','男',200,NULL,'1998-04-18','2017-05-15',2),
 (30,'朱明亮','13254554322','女',18916,0.54,'1982-08-18','2001-03-14',3);

十二、运算符和数据类型

 MySQL数据类型

• 整型系列：xxxInt

int(M)，必须和unsigned zerofill一起使用才有意义

unsigned：无符号字符，将整形的范围变为从0开始，同时所对应的范围也会发生变化

zerofill：0填充，当设置了整形的宽度时，若为该字段所设置的值不够宽度的要求，则会在数据之前补0，以达到宽度的要求

• 浮点型系列：float,double

double(M,D)：表示最长为M位，其中小数点后D位

例如：double(5,2)表示的数据范围[-999.99,999.99]，如果超过这个范围会报错。

• 定点型系列：decimal

decimal(M,D)：表示最长为M位，其中小数点后D位

• 字符串类型：char,varchar(M),text

char如果没有指定宽度，默认为1个字符

varchar(M)，必须指定宽度

• 日期时间类型：year, date, datetime, timestamp

注意一下每一种日期时间的表示范围

• 其他类型：bit（0或1）, xxBlob, 枚举，集合等

TinyBlob 最大 255B Blob 最大 65K  MediumBlob 最大 16M  LongBlob 最大 4G

+----------------+--------------+------+-----+---------+----------------+| Field          | Type         | Null | Key | Default | Extra          |+----------------+--------------+------+-----+---------+----------------+| eid            | int(11)      | NO   | PRI | NULL    | auto_increment || ename          | varchar(20)  | NO   |     | NULL    |                || tel            | char(11)     | NO   |     | NULL    |                || gender         | char(1)      | YES  |     | 男        |                || salary         | double       | YES  |     | NULL    |                || commission_pct | double(3,2)  | YES  |     | NULL    |                || birthday       | date         | YES  |     | NULL    |                || hiredate       | date         | YES  |     | NULL    |                || job_id         | int(11)      | YES  |     | NULL    |                || email          | varchar(32)  | YES  |     | NULL    |                || mid            | int(11)      | YES  |     | NULL    |                || address        | varchar(150) | YES  |     | NULL    |                || native_place   | varchar(10)  | YES  |     | NULL    |                || did            | int(11)      | YES  |     | NULL    |                |+----------------+--------------+------+-----+---------+----------------+

 MySQL运算符

• 算术运算符

加：+减：-乘：*除：/   div（只保留整数部分）模：%   mod

• 比较运算符

大于：>小于：<大于等于：>=小于等于：>=等于：=   不能用于null判断不等于：!=  或 <>安全等于：<=>  可以用于null值判断

• 逻辑运算符（建议用单词，可读性来说）

逻辑与：&& 或 and逻辑或：|| 或 or逻辑非：! 或 not逻辑异或：^ 或 xorxor：只能满足其中的一个条件，不能同时满足，也不能都不满足，结果A+B-A∩B

• 范围

区间范围：between x  and  y        not between x  and  y集合范围：in (x,x,x)        not  in(x,x,x)

• 模糊查询（只针对字符串类型，日期类型）

like 'xxx'如果想要表示0~n个字符，用%如果想要表示确定的1个字符，用_

• 位运算符（很少使用）

左移：<<右移：>>按位与：&按位或：|按位异或：^

• 特殊的null值处理

#（1）判断时
xx is null
xx is not null
xx <=> null

#(2)计算时
ifnull(xx,代替值)  当xx是null时，用代替值计算

十三、 MySQL的语法规范和要求

• MySQL的SQL语法不区分大小写， MySQL的关键字和函数名等不区分大小写，但是对于数据值是否区分大小写，和字符集与校对规则有关。 ci（大小写不敏感），cs（大小写敏感），_bin（二元，即比较是基于字符编码的值而与language无关，区分大小写）
• 命名时：尽量使用26个英文字母大小写，数字0-9，下划线，不要使用其他符号 。
• 建议不要使用mysql的关键字等来作为表名、字段名等，如果不小心使用，请在SQL语句中使用`（飘号）引起来
• 数据库和表名、字段名等对象名中间不要包含空格
• 同一个mysql软件中，数据库不能同名，同一个库中，表不能重名，同一个表中，字段不能重名
• 标点符号：必须成对、必须英文状态下半角输入方式、字符串和日期类型可以使用单引号''、列的别名可以使用双引号""，给表名取别名不要使用双引号。取别名时as可以省略、如果列的别名没有包含空格，可以省略双引号，如果有空格双引号不能省略。
• SQL脚本中如何加注释： 单行注释：#注释内容
•                                         单行注释：--空格注释内容 其中--后面的空格必须有
•                                         多行注释：/* 注释内容 */

十四、约束与索引

关系型数据库设计规则

• 遵循ER模型和三范式

  • E entity 代表实体的意思 对应到数据库当中的一张表

  • R relationship 代表关系的意思

• 三范式：1、列不能拆分 2、唯一标识 3、关系引用主键

• 具体体现

  • 将数据放到表中，表再放到库中。

  • 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

  • 表具有一些特性，这些特性定义了数据在表中如何存储，类似java和python中 “类”的设计。

  • 表由列组成，我们也称为字段。每个字段描述了它所含有的数据的意义，数据表的设计实际上就是对字段的设计。创建数据表时，为每个字段分配一个数据类型，定义它们的数据长度和字段名。每个字段类似java 或者python中的“实例属性”。

  • 表中的数据是按行存储的，一行即为一条记录。每一行类似于java或python中的“对象”。

约束与索引概念

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录

• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”

• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门

• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束

• Not NULL约束：非空约束

• Check约束：检查约束

• Default约束：默认值约束

• 自增约束

约束（CONSTRAINTS）与索引（INDEX）

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式，是逻辑约束，不会因为设置约束而额外占用空间。

索引是一个单独、物理的存储在数据页上的数据库结构，它是表中一列或若干列值的集合和相应的指向表中数据值的物理标识数据页的逻辑指针清单（类似于新华字典的目录索引页）。索引的存在会增加数据库的存储空间，也会使插入、修改数据的时间开销变多(因为插入和修改数据时，索引也要随之变动），但是可以大大提高查询速度。因此应该在键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

其中主键删除，对应的索引也会删除

删除唯一键的方式是通过删除对应的索引来实现的

删除外键，外键列上的索引还在，如果需要删除，需要单独删除索引

十五、 SQL分类

DDL：数据定义语言，定义库，表结构等，包括create,drop,alter等

DML：数据操作语言，增删改查数据，包括insert,delete,update,select等

DCL：数据控制语言，权限，事务等管理。

 

十六、 DDL

查看所有数据库

show databases;

指定使用某个数据库

use 数据库名;

创建数据库

create database 数据库名;create database 数据库名 charset 'utf8'; #在mysql中字符集名称不要使用utf-8create database 数据库名 charset 'gbk';

删除数据库

drop database 数据库名;

查看某个库下的所有表格

show tables ;  #前提是前面有use 数据库名;的语句show tables from 数据库名;

创建表格

create table 【数据名.】表名(    字段名1 数据类型,    字段名2 数据类型,    ....);

删除表格

drop table 【数据库名.]表名称;

查看某个表结构

describe 【数据库名.]表名称;desc 【数据库名.]表名称;

增加一列

alter table 【数据库名.]表名称 add 【column】 字段名 数据类型;alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 first;alter table 【数据库名.]表名称 add 【column】 字段名 数据类型 after 另一个字段;

删除一列

alter table 【数据库名.]表名称 drop 【column】 字段名;

修改列数据类型

alter table 【数据库名.]表名称 modify 【column】 字段名 新数据类型;

修改列名

alter table 【数据库名.]表名称 change【column】 旧字段名 新字段名 新数据类型;

修改列的位置

alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 first;alter table 【数据库名.]表名称 modify 【column】字段名 数据类型 after 另一个字段;

修改表名称

alter table 旧表名 rename 新表名;rename table 旧表名 to 新表名;

查看某个表的约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';或SHOW CREATE TABLE 表名;

查看某个表的索引

SHOW INDEX FROM 表名称;

主键约束：primary key

主键分为单列主键和复合主键：

主键的特点：（1）唯一并且非空（2）一个表只能有一个主键约束（3）主键约束名就叫做PRIMARY（4）创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除。

（1）如何在建表时指定主键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	....
);
或
create table 【数据名.】表名(
	字段名1 数据类型,
	....,
    primary key(字段名1)
);
或
create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表)#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);

（2）如何在建表后指定主键约束

alter table 表名称 add primary key (主键字段列表);

（3）如何删除主键约束

alter table 表名称 drop primary key;

唯一键约束：unique key

（1）唯一键约束特点

• 唯一键约束列允许为null。

• 同一个表可以有多个唯一约束。

• 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。

• MySQL会给唯一约束的列上默认创建一个唯一索引。

• 删除唯一键只能通过删除对应索引的方式删除，删除时需要指定唯一键索引名

（2）如何在建表时指定唯一键约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 unique key,
	....
);

create table 【数据名.】表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型,
    字段名3 数据类型,
	....,
    unique key(复合唯一字段列表)#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
);

（3）如何在建表后增加唯一键约束

alter table 表名称 add 【constraint 约束名】 unique 【key】 (字段名列表);
#如果没有指定约束名，(字段名列表)中只有一个字段的，默认是该字段名，如果是多个字段的默认是字段名列表的第1个字段名。也可以通过show index from 表名;来查看

（4）如何删除唯一键约束

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过“show index from 表名称;”查看

外键约束：foreign key

（1）外键特点

• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。

• 删除外键时，关于外键列上的普通索引需要单独删除。

（2）要求

• 在从表上建立外键，而且主表要先存在。

• 一个表可以建立多个外键约束

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键），推荐引用主表的主键。

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

（3）约束关系：约束是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束

• 添加了外键约束后，从表的添加和修改数据受约束

• 在从表上建立外键，要求主表必须存在

• 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

（4）5个约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• Restrict方式：同no action, 都是立即检查外键约束

• Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式

（5）如何在建表时指定外键约束

create table 【数据名.】从表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】,
	....,
    【constraint 外键约束名】 foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
    #如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk
);

create table 【数据名.】表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（6）如何在建表后指定外键约束

alter table 从表名称 add 【constraint 外键约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名) 【on update xx】[on delete xx];

（7）如何删除外键约束

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
#查看约束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;
#查看索引名 show index from 表名称;

非空约束：not null

NOT NULL 非空约束，规定某个字段不能为空

（1）如何在建表时给某个字段指定非空约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】,
    字段名2 数据类型 【not null】,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（2）如何在建表后指定某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL 【default 默认值】;
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

（3）如何在建表后取消某个字段非空

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【default 默认值】;
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

默认值约束：default

（1）如何在建表时给某个字段指定默认约束

create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....,
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

create table 【数据名.】表名(
    字段名1 数据类型 【not null】 【default 默认值】,
    字段名2 数据类型 【not null】 【default 默认值】,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) 【on update 外键约束等级】【on delete 外键约束等级【
    #外键只能在所有字段列表后面单独指定
);

（2）如何在建表后指定某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型  【default 默认值】 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

（3）如何在建表后取消某个字段的默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 【NOT NULL】;
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

检查约束：check

检查约束，mysql暂不支持

create table stu(
	sid int primary key,
	sname varchar(20),
	gender char check ('男'or'女')
);
insert into stu values(1,'张三','男');
insert into stu values(2,'李四','妖');

使用枚举类型解决如上问题：
create table stu(
	sid int primary key,
	sname varchar(20),
	gender enum ('男','女')
);

自增约束：auto_increment

（1）关于自增长auto_increment：

• 一个表最多只能有一个自增长列

• 自增长列必须是键列（主键列，唯一键列，外键列），并且要求非空。

• 自增列必须是整数类型

• InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

（2）如何在建表时指定自增长列

create table 【数据名.】表名(
	字段名1 数据类型  primary key auto_increment,
	字段名2 数据类型 【unique key】 【not null】 【default 默认值】,
	....
);

或
create table 【数据名.】表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 【unique key  not null】 auto_increment,
	....
);

（3）如何在建表后指定自增长列

alter table 【数据名.】表名 modify 自增字段名 数据类型 auto_increment;

（4）如何删除自增约束

alter table 【数据名.】表名 modify 自增字段名 数据类型;

 

十七、 DML

添加数据

insert into 【数据库名.]表名称 values(值列表)； #要求值列表的顺序、个数、类型，要与表格中的字段的顺序、个数、类型一一匹配insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#要求列表的顺序、个数、类型，要与前面的(部分字段列表)的顺序、个数、类型一一匹配insert into 【数据库名.]表名称 values(值列表1)，(值列表2)。。。;insert into 【数据库名.]表名称(部分字段列表) values(值列表1)，(值列表2)。。。;

（1）如果某列有自增约束，怎么添加该字段的值

添加数据时，对于自增列

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应自增列可以赋值为null和0

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#自增列在(部分字段列表)中不写就可以

（2）如果某列有默认值约束，怎么添加、修改该字段的值

添加数据时，对于有默认值列

insert into 【数据库名.]表名称 values(值列表)；#在值列表中，对应默认值列，如果想用默认值，用default

insert into 【数据库名.]表名称(部分字段列表) values(值列表)；#对应默认值列，如果想用默认值，在(部分字段列表)中不写就可以

 

修改数据

update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】;
update 【数据库名.]表名称 set 字段名1 = 值1, 字段名2 = 值2 。。。 【where 条件】; #对应默认值列，如果想用默认值，写字段名 = default就可以

如果没有加where条件，表示修改所有行，这个字段的值

删除数据

delete from 【数据库名.]表名称 【where 条件】;

如果没有where条件，表示删除整张表的数据;

truncate 【数据库名.]表名称;#删除整张表的数据，还可以使用这个语句，效率更高，但是它不能回滚

用delete删除整张表和用truncate删除整张表的数据的区别？

（1）truncate速度快

（2）truncate无法回滚

truncate因为底层是把表drop掉，然后新建了一张空表。

delete因为底层是一行一行删数据。

简单查询数据

select * from 【数据库名.]表名称; #查询整张表的所有数据select 字段列表  from 【数据库名.]表名称;  #查询部分列表select * from 【数据库名.]表名称 【where 条件】;select 字段列表  from 【数据库名.]表名称 【where 条件】;

使用distinct可以对查询结果进行去重

十八、数据库导入和导出

单个数据库备份（5.5版本）

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码 --database  数据名 > 文件路径/文件名.sql

例如：

C:\Windows\System32>mysqldump -hlocalhost -P3306 -uroot -p123456 --database  test > d:/test.sql

数据库单个备份（5.7版本）

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码  数据名 > 文件路径/文件名.sql

不要再写--database

导入执行备份的sql脚本

先登录mysql，然后执行如下命令：

mysql> source  sql脚本路径名.sql

例如：

mysql>source d:/test.sql;

十九、函数

两种SQL函数

单行函数

• 只对一行进行变换，每行返回一个结果

• 可以嵌套

• 参数可以是一字段或一个表达式或一个值

• 字符串函数

• 举例1：大小写控制函数

• 举例2：字符控制函数

• 数值函数/数学函数

• 举例1：ROUND:四舍五入

ROUND(45.926, 2)     -->  45.93

• 举例2：TRUNCATE:截断

TRUNCATE(45.926)      --> 45

• 举例3：MOD:求余

MOD(1600, 300)	 --> 100

• 日期函数

其中：

（1）DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

（2）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

• 流程函数

• 举例1：

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"FROM employees;

• 举例2：

SELECT last_name, job_id, salary,       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary                   WHEN 'ST_CLERK' THEN  1.15*salary                   WHEN 'SA_REP'   THEN  1.20*salary       ELSE      salary END     "实发工资"FROM   employees;

• 举例3

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees;

 

 

呜呜呜呜呜 

mysql> source  sql脚本路径名.sql

 

mysql>source d:/test.sql;