MySQL语言学习（一）

SQL语言基础

SQL概述

• SQL全称： Structured Query Language，是结构化查询语言，用于访问和处理数据库的标准的计算机语言。SQL语言1974年由Boyce和Chamberlin提出，并首先在IBM公司研制的关系数据库系统SystemR上实现。
• 美国国家标准局(ANSI)开始着手制定SQL标准，并在1986年10月公布了最早的SQL标准，扩展的标准版本是1989年发表的SQL-89，之后还有1992年制定的版本SQL-92和1999年ISO发布的版本SQL-99。
• SQL标准几经修改和完善，其功能更加强大，但目前很多数据库系统只支持SQL-99的部分特征，而大部分数据库系统都能支持1992年制定的SQL-92。

SQL的特点

• 具有综合统一性，不同数据库的支持的SQL稍有不同
• 非过程化语言
• 语言简捷，用户容易接受
• 以一种语法结构提供两种使用方式

SQL语言的组成

• 数据定义语言-DDL
• 数据操纵语言-DML
• 数据控制语言-DCL
• 数据查询语言-DQL

语法特点

• SQL 对关键字的大小写不敏感
• SQL语句可以以单行或者多行书写，以分行结束

SQL的注释：

-- 单行        -- 后面一定要加空格
# 单行注释 # 后面可加可不加空格
SELECT * FROM emp; --这里是注释
/*
多行注释
*/

数据库系统

1. 数据库

数据库（DB）是一个以某种组织方式存储在磁盘上的数据的集合。

2. 数据库应用

数据库应用系统是指基于数据库的应用软件

3. 数据库管理系统分类

• 关系型数据库（RDBMS）

  1. Oracle数据库 （老大，最挣钱的数据库）
  2. MySQL数据库 （最流行中型数据库））
  3. SQL server数据库 （Windows上最好的数据库）
  4. PostgreSQL（功能最强大的开源数据库）
  5. SQLite（最流行的嵌入式数据库）

• 非关系型数据库(NoSQL)

  1. Redis（最好的缓存数据库）
  2. MongoDB（最好的文档型数据库）
  3. Elasticsearch（最好的搜索服务）
  4. Cassandra（最好的列式数据库）
  5. HBase（优秀的分布式、列式数据库）

3. SQL和数据库管理系统的关系

• SQL是一种用于操作数据库的语言，SQL适用于所有关系型数据库。
• MySQL、Oracle、SQLServer是一个数据库软件，这些数据库软件支持标准SQL，也就是通过SQL可以使用这些软件，不过每一个数据库系统会在标准SQL的基础上扩展自己的SQL语法。
• 大部分的NoSQL数据库有自己的操作语言，对SQL支持的并不好。

4. 数据库管理系统（数据库软件）

数据库管理系统（DBMS）用来创建和维护数据库。例如，SQL Server、Oracle、MySQL等等都是数据库管理系统

MySQL

MySQL简介

1. MySQL的介绍

MySQL数据库管理系统由瑞典的DataKonsultAB公司研发，该公司被Sun公司收购，现在Sun公司又被Oracle公司收购，因此MySQL目前属于 Oracle 旗下产品。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，一般中小型网站的开发都选择 MySQL 作为网站数据库。

2. MySQL的特点

1. MySQL数据库是用C和C++语言编写的，以保证源码的可移植性
2. 支持多个操作系统例如：Windows、Linux、Mac OS等等
3. 支持多线程，可以充分的利用CPU资源
4. 为多种编程语言提供API，包括C语言，Java，PHP。Python语言等
5. MySQL优化了SQL算法，有效的提高了查询速度
6. MySQL开放源代码且无版权制约，自主性强、使用成本低。
7. MySQL历史悠久、社区及用户非常活跃，遇到问题，可以很快获取到帮助。

3. MySQL版本

针对不同的用户，MySQL不同的版本：

• MySQL Community Server

社区版本，免费，但是Mysql不提供官方技术支持。

• MySQL Enterprise Edition

商业版，该版本是收费版本，可以试用30天，官方提供技术支持

• MySQL Cluster

集群版，开源免费，可将几个MySQL Server封装成一个Server。

• MySQL Cluster CGE

高级集群版，需付费。

• MySQL Workbench（GUI TOOL）

一款专为MySQL设计的ER/数据库建模工具。MySQL Workbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、商用版（MySQL Workbench SE）。

MySQL的命名机制使用由3个数字和一个后缀组成的版本号。例如，像mysql-8.0.26的版本号说明：

• 第1个数字(8)是主版本号，描述了文件格式。所有版本5的发行都有相同的文件格式。
• 第2个数字(0)是发行级别。主版本号和发行级别组合到一起便构成了发行序列号。
• 第3个数字(26)是在此发行系列的版本号，随每个新分发版递增。

MySQL的安装和使用

1. 安装方式

MySQL的安装有两种方式：

• 使用压缩包解压配置方式
• 使用安装包步骤安装方式（下载后根据引导提示安装即可）

2. 安装包下载

解压版

根据相应的操作系统和版本进行下载

下载地址：https://downloads.mysql.com/archives/community/

安装板

下载地址：https://downloads.mysql.com/archives/installer/

将下载的MySQL软件包解压在没有中文和空格的目录下

下面在Windows平台进行安装

3. 设置配置文件

在解压后文件夹目录创建my.ini文件并添加内容如下：

[mysqld]
# 设置3306端口
port=3306    
# 服务端默认编码（数据库级别）
character_set_server = utf8mb4
# 在创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置MySQL客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

4. 配置系统环境

1. 在【我的电脑】右键
2. 选择【高级系统设置】
3. 选择【高级】->【环境变量

将MYSQL_HOME添加到PATH环境变量

5. 服务操作

使用管理员权限进入DOS，在cmd中，进入解压目录下的bin目录依次执行以下命令：

# 1. 对mysql进行初始化，请注意，这里会生产一个临时密码，后边要使用这个临时密码
mysqld --initialize --user=mysql --console
# 2. 安装mysql服务
mysqld --install 
# 3. 启动mysql服务
net start mysql
# 4. 登录mysql，这里需要使用之前生产的临时密码
mysql -uroot –p   
# 5. 修改root用户密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ‘123456’;
# 6. 修改root用户权限
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY ‘123456';

MySQL常用图形管理工具

简介

日常的开发和维护均在类似dos窗口中进行，对于编程初学者来说，上手就略微有点困难，增加了学习成本。我们一般使用mysql图形管理工具来连接Mysql，然后在图形化界面上操作Mysql。

MySQL的管理维护工具非常多，除了系统自带的命令行管理工具之外，还有许多其他的图形化管理工具。

常用工具介绍

• Navicat

  Navicat是一套快速、可靠的数据库管理工具，Navicat 是以直觉化的图形用户界面而建的，可以兼容多种数据库,支持多种操作系统。

• SQLyog

  SQLyog 是一个快速而简洁的图形化管理MySQL数据库的工具，它能够在任何地点有效地管理你的数据库，由业界著名的Webyog公司出品。

  使用SQLyog可以快速直观地让您从世界的任何角落通过网络来维护远端的MySQL数据库。

• MySQL Workbench

  MySQL Workbench MySQL 是官方提供的图形化管理工具，分为社区版和商业版，社区版完全免费，而商业版则是按年收费。支持数据库的创建、设计、迁移、备份、导出和导入等功能，并且支持 Windows、Linux 和 mac 等主流操作系统。

• DataGrip

  DataGrip，是大名鼎鼎的JetBrains公司出品的，就是那个出品Intellij IDEA的公司。DataGrip是一款数据库管理客户端工具，方便连接到数据库服务器，执行sql、创建表、创建索引以及导出数据等

• 其它工具

  phpMyAdmin

  MySQLDumper

  MySQL GUI Tools

  MySQL ODBC Connector

图形化工具连接MySQL

• Navicat连接

  

• SQLYog连接

  

MySQL数据库基本操作-DDL

1. DDL解释

DDL(Data Definition Language)，数据定义语言，该语言部分包括以下内容：

• 对数据库的常用操作
• 对表结构的常用操作
• 修改表结构

不涉及数据

2. 对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database [if not exists] mydb1 [charset=utf8]
切换 (选择要操作的) 数据库	use mydb1;
删除数据库	drop database [if exists] mydb1；
修改数据库编码	alter database mydb1 character set utf8;

3. 对表结构的常用操作-创建表

• 创建表格式

  create table [if not exists]表名(
    字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
  )[表的一些设置];

  创建表是构建一张空表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型

  示例

  use mydb1;
  create  table if not exists student(
      sid int,
      name varchar(20),
      gender varchar(20),
      age int,
      birth date, 
      address varchar(20),
      score double
  );

• 数据类型

  数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

  • 数值类型
  • 日期和时间类型
  • 字符串类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
**BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL		依赖于M和D的值	依赖于M和D的值	小数值

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

日期类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

加粗部分为经常使用的

4. 对表结构的常用操作-其它操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名；
查看表结构	desc 表名
删除表	drop table 表名

5. 对表结构的常用操作-修改表结构格式

修改表添加列

语法：alter table 表名 add 列名 类型(长度) [约束];

示例

# 为student表添加一个新的字段为：系别 dept 类型为 varchar(20)
ALTER TABLE student ADD `dept` VARCHAR(20); 

修改列名和类型

语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;

示例

# 为student表的dept字段更换为department varchar(30)
ALTER TABLE student change `dept` department VARCHAR(30); 

修改表删除列

语法：alter table 表名 drop 列名;

示例

# 删除student表中department这列
ALTER TABLE student DROP department;

修改表名

语法：rename table 表名 to 新表名;

示例

# 将表student改名成 stu
rename table `student` to stu;

MySQL数据库基本操作-DML

基本介绍

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。

关键字：

• 插入insert
• 删除delete
• 更新update

数据插入

语法：

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...);     //向表中插入所有列

示例

insert into student(sid,name,gender,age,birth,address,score)
                       values(1001,'男',18,'1996-12-23','北京',83.5);
insert into student values(1001,'男',18,'1996-12-23','北京',83.5);

数据修改

语法：

update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

示例

-- 将所有学生的地址修改为重庆 
update student set address = '重庆’; 

-- 讲id为1004的学生的地址修改为北京 
update student set address = '北京' where id = 1004; 

-- 讲id为1005的学生的地址修改为北京，成绩修成绩修改为100 
update student set address = '广州',score=100 where id = 1005

数据删除

语法：

delete from 表名 [where 条件];
truncate table  表名 或者 truncate 表名

示例

-- 1.删除sid为1004的学生数据
delete from student where sid  = 1004;
-- 2.删除表所有数据
delete from student;
-- 3.清空表数据
truncate table student;
truncate student;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table ，可以理解为是将整个表删除，然后再创建该表

MySQL约束

概念

约束英文：constraint。约束实际上就是表中数据的限制条件

作用

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类

• 主键约束(primary key) PK
• 自增长约束(auto_increment)
• 非空约束(not null)
• 唯一性约束(unique)
• 默认约束(default)
• 零填充约束(zerofill)
• 外键约束(foreign key) FK

主键约束

概念

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
• 主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作

• 添加单列主键
• 添加多列联合主键
• 删除主键

操作-添加单列主键

创建单列主键有两种方式：

• 在定义字段的同时指定主键

  语法：

  -- 在 create table 语句中，通过 PRIMARY KEY 关键字来指定主键。
  -- 在定义字段的同时指定主键，语法格式如下：
  create table 表名(
     ...
     <字段名> <数据类型> primary key 
     ...
  )

  示例

  create table emp1(
      eid int primay key,
      name VARCHAR(20),
      deptId int,
      salary double
  );

• 是定义完字段之后指定主键

  语法：

  -- 在定义字段之后再指定主键，语法格式如下：
  create table 表名(
     ...
     [constraint <约束名>] primary key [字段名]
  );

  示例

  create table emp2(
      eid INT,
      name VARCHAR(20),
      deptId INT,
      salary double,
      constraint  pk1 primary key(id)
   );

操作-添加多列主键(联合主键）

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

注意：

1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
2. 一张表只能有一个主键，联合主键也是一个主键

语法：

create table 表名(
   ...
   primary key （字段1，字段2，…,字段n)
);

示例

create table emp3( 
  name varchar(20), 
  deptId int, 
  salary double, 
  primary key(name,deptId) 
);

通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加。

语法：

create table 表名(
   ...
);
alter table <表名> add primary key（字段列表);

示例

-- 添加单列主键
create table emp4(
  eid int, 
  name varchar(20), 
  deptId int, 
  salary double, 
);
alter table emp4 add primary key（eid);

删除主键约束

一个表中不需要主键约束时，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

语法：

alter table <数据表名> drop primary key;

示例

-- 删除单列主键 
alter table emp1 drop primary key;

-- 删除联合主键 
alter table emp5 drop primary key;

自增长约束(auto_increment)

概念

在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加auto_increment属性来实现主键自增长

语法

字段名 数据类型 auto_increment

操作

create table t_user1( 
  id int primary key auto_increment, 
  name varchar(20) 
);

特点

• 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
• 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
• auto_increment约束的字段必须具备 NOT NULL 属性。
• auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等。
• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。

指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。

例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

-- 方式1，创建表时指定
create table t_user2 ( 
  id int primary key auto_increment, 
  name varchar(20)
)auto_increment=20;

-- 方式2，创建表之后指定
create table t_user3 ( 
  id int primary key auto_increment, 
  name varchar(20)
);
alter table t_user2 auto_increment=20;

delete和truncate在删除后自增列的变化

• delete数据之后自动增长从断点开始
• truncate数据之后自动增长从默认起始值开始

非空约束(not null)

概念

MySQL 非空约束（not null）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

语法

-- 方式1：
<字段名> <数据类型> not null;

-- 方式2：
alter table 表名 modify 字段 类型 not null;

操作

-- 方式1，创建表时指定
create table t_user6 ( 
  id int , 
  name varchar(20) not null, 
  address varchar(20) not null 
);

-- 方式二 
create table t_user7 ( 
  id int , 
  name varchar(20) , -- 指定非空约束 
  address varchar(20) -- 指定非空约束 
); 
alter table t_user7 modify name varchar(20) not null; 
alter table t_user7 modify address varchar(20) not null;

删除非空约束

-- alter table 表名 modify 字段 类型 
alter table t_user7 modify name varchar(20) ; 
alter table t_user7 modify address varchar(20) ;

唯一约束(unique)

概念

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。

例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

语法

-- 方式1：
<字段名> <数据类型> unique
-- 方式2： 
alter table 表名 add constraint 约束名 unique(列);

操作

-- 方式一 创建表时指定
create table t_user8 ( 
 id int , 
 name varchar(20) , 
 phone_number varchar(20) unique -- 指定唯一约束 
);

-- 方式二
create table t_user9 ( 
  id int , 
  name varchar(20) , 
  phone_number varchar(20) -- 指定唯一约束 
); 
alter table t_user9 add constraint unique_ph unique(phone_number);

删除唯一约束

-- alter table <表名> drop index <唯一约束名>;
alter table t_user9 drop index unique_ph;

默认约束（default）

概念

MySQL 默认值约束用来指定某列的默认值。

语法

-- 方式1： 
<字段名> <数据类型> default <默认值>;
-- 方式2: 
alter table 表名 modify 列名 类型 default 默认值; 

操作

-- 方式一
create table t_user10 ( 
  id int , 
  name varchar(20) , 
  address varchar(20) default ‘北京’ -- 指定默认约束 
);

-- 方式二 alter table 表名 modify 列名 类型 default 默认值; 
create table t_user11 ( 
  id int , 
  name varchar(20) , 
  address varchar(20)  
);
alter table t_user11 modify address varchar(20) default  ‘北京’;

删除默认约束

-- alter table <表名> modify column <字段名> <类型> default null; 
alter table t_user11 modify column address varchar(20) default null;

零填充约束(zerofill)

概念

1. 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
2. zerofill默认为int(10)
3. 当使用zerofill 时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为-128+127，无符号为0256。

操作

create table t_user12 ( 
  id int zerofill , -- 零填充约束
  name varchar(20)   
);

删除

alter table t_user12 modify id int;