MySQL_note

MySQL基础

一、 了解数据库

1.1. 相关概念

• 数据库（database，简称DB）：保存有组织的数据的容器（通常是一个文件或一组文件）。

  误用导致混淆 人们通常用数据库这个术语来代表他们使用的数据库软件。这是不正确的，确切地说,数据库软件应当称为DBMS（数据库管理系统）。数据库是通过DBMS创建和操纵的容器。数据库可以是保存在硬设备上的文件，但也可以不是。

• 数据库管理系统（Database Management System，简称DBMS）：是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。

1.2. 关系型数据库

​ 关系型数据库（Relational database）：是创建在关系模型上的数据库，以行列的形式来存储数据。方便用户的理解，在关系型数据库中一系列的行和列称为表，一组表组成数据库。（单库的表容量是固定：可以进行分库分表的操作），可以将关系型数据库理解为二维数据表格模型，而一个关系型数据库是由二维表及其之间的关系组成的数据的组织。

1.2.1 常用的关系型数据库管理系统

• MySQL

  最受欢迎的开源的SQL数据库管理系统

  2003年Mysql5.0：支持SQL特性，事务，视图、存储过程、触发器等功能。

  2010年MySQL5.5，InnoDB存储引擎变为MySQL的默认存储引擎。

  优势：

  • MySQL是开放源代码的，可以免费使用（甚至可以修改源码）
  • MySQL服务器是一个快速的、易于使用的数据库服务器
  • MySQL可以在不同的操作系统中使用

• MariaDB

  是由MySQL的创始人主导开发的。担心Oracle将MySQL闭源。目前大型的互联网公司纷纷抛弃MySQL

  转入到MariaDB。

• PostgreSQL

  完整的支持了SQL标准，开源，可以在不同的操作系统中运行。

• Oracle数据库

  最先将关系型数据库转到桌面计算机上。客户/服务器结构的概念。

  Oracle数据库的优势：

  • 兼容性（采用SQL标准）
  • 可移植性（window,linux,unix,dos）
  • 可连接性（支持各种网络传输协议：TCP/IP,、DECnet,LU6.2)
  • 高生产率（提供了多种开发工具，可以方便用户快速的开发）
  • 开放性（oracle良好的兼容性、可以移植性、可连接性和高生产率使用oracle具有良好的开放性)

• SQL Server

  微软旗下，和.net，在国内广泛用于电力，保险等行业。2017版之前的SQL Server只支持windows操作

  系统。2017年后SQL Server可以运行在windows,linux,docker等平台。

• SQLite

  广泛应用与嵌入式开发中。

• Sybase

  PowerDesginer数据库建模工具。

1.2.2 SQL语言

​ SQL（Structured Query Language：结构化查询语言）。

​ SQL是数据库查询和设计语言，用于存取数据、查询、更新、管理关系数据库。与其他程序设计语言的差别是，SQL由很少的关键字组成，每个SQL语言通过一个或多个关键字构成。

​ SQL基于关系代数和元组关系演算，包括一个数据定义语言和数据操纵语言。SQL的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。尽管SQL经常被描述为，而且很大程度上是一种声明式编程（4GL），但是其也含有过程式编程的元素。

​ SQL的优点：

• 几乎所有的RDBMS都支持SQL。

• SQL简单易学。

• 使用方式灵活：SQL2种使用方式，可以直接以命令方式交互使用；也可以嵌入到其他程序设计语

  言中使用(jdbc)

• 非过程化：“做什么",不需要使用sql告诉计算机"怎么做"

注意：

SQL语句不区分大小写，对SQL中的关键字进行大写，而对表名、列名、数据库名称使用小写。可

以提高代码的阅读星和可维护性。

SQL包含四个部分：

• DDL(Data Defifinition Language)：数据定义语言

  CREATE,DROP,ALTER
  

• DML(Data Manipulate Language)：数据操纵语言

  INSERT,UPDATE,DELETE
  

• DQL(Data Query Lanaguage)：数据查询语言

  SELECT
  

• DCL(Data Control Language)：数据控制语言

  commit,rollback
  

1.2.3 相关概念

• 表

  表（table） 某种特定类型数据的结构化清单

  模式（schema） 关于数据库和表的布局及特性的信息

• 列和数据类型**

  列（Colum） 表中的一个字段。所有的表都是由一个或多个列组成的。

  数据类型（datatype） 所允许的数据的类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。

• 行

  行（row） 表中的一个记录

• 主键

  主键（primary key） 一列（或一组列），其值能够唯一区分表中每个行

• 外键

  外键（foreign key） 父数据表（Parent Entity）的主键（primary key）会放在另一个数据表，当做属性以创建彼此的关系，而这个属性就是外键。

1.3. 非关系型数据库

NOSQL(Not Only SQL)是对不同于传统的关系型数据库的数据库管理系统的统称。

随着web2.0的兴起，传统的关系型数据库在处理海量数据时，会显得力不从心，从而产生了NOSQL，主流NOSQL，都才用KEY-VALUE的形式。

常见的非关系型数据库：Redis ,HBase,MongoDb,CouchDB

二、 MySQL基础

2.1. MySQL的安装与配置

2.1.1 下载MySQL

在官网，现在最新版的MySQL：https://dev./downloads/mysql/

将下载好的压缩文件，解压至【安装目录】, D:/DataBase/mysql。

如果提示缺少dll文件，去 [https://www.microsoft.com/zh-cn/download/confirmation.aspx? id=48145](https://www.microsoft.com/zh-cn/download/confirmation.aspx? id=48145 ) 下载组件并安装。

2.1.2 初始化

在初始化时，需要使用已管理员身份启动的cmd,在 windows/system32/cmd.exe ，右键以管理员身份运行

#使用dos命令，进入到mysql的bin目录中 
cd d:#进入 
cd d:/DataBase/mysql/bin 
#使用mysqld命令进行初始化 
##进行初始化，同时创建随机的密码，并显示在控制塔中 
mysqld --basedir=d:/DataBase/mysql --datadir=d:/DataBase/mysql/mysql_data --initialize --console 
#随机生成的密码iiowpr<2felX

2.1.3 配置文件

将配置文件存储在 d:/DataBase/mysql/mysql_data/my.ini

[client]
default-character-set=utf8mb4
port            = 3306

[mysqld]
port            = 3306
basedir         = D:/DataBase/mysql
datadir         = D:/DataBase/mysql/mysql_data

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

default-time_zone='+8:00'
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

max_allowed_packet = 256M
max_connections=20

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
default-character-set=utf8mb4
auto-rehash

2.1.4 安装服务

#在install后面可以添加安装的服务名称，默认使用MySQL作为服务名称 
mysqld --install MySQL --defaults-file=D:/DataBase/mysql/mysql_data/my.ini

2.1.5 启动MySQL服务

• 使用命令方式启动

  net start mysql
  

• 使用服务方式启动

  在运行中输入services.msc，在【服务】中右键MySQL，启动

2.1.6 登录MySQL

#客户端登录命令 
mysql -h 主机地址 -u 用户名 -p 用户密码
mysql -uroot -p;

2.1.7 修改密码

通过命令修改root用户的密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; 
exit;
#退出 
mysql -uroot -p123456;

2.1.8 删除服务

• 删除注册表（不推荐）

• 使用命令删除服务

  SC DELETE mysql -- 服务的名称
  

2.2. 认识MySQL

2.2.1 MySQL介绍

MySQL是一个关系型的数据库管理系统，由MySQL AB公司开发，目前属于Oracle公司。

MySQL体积较小、速度快、成本较低、开放源代码。同时支持跨平台。MySQL集群搭建及分库分表。

MySQL特性：

• MySQL使用C和C++编写，保证了源代码的可移植性。
• 跨平台：支持主流的操作系统（Windows,Linux,Mac os ,HP-UX)
• 对编程语言的支持，对多种编程语言提供了API(C,C++,JAVA,PYTHON,PHP,Perl,Ruby)
• 支持多线程，充分利用CPU资源。（服务器单核（A,B）
• 优化的SQL查询算法，能提高查询效率。
• 提供了TCP/IP,ODBC和JDBC等多种数据库连接途径
• 支持多种存储引擎
• 提供了用于管理，检查，优化数据库操作的管理工具。

2.2.2 MySQL服务端常用程序

• mysqld

  mysqld是MySQL的后台程序（进程),只有该程序运行后，客户端才可以连接访问数据库。

• mysqld_safe

  也是服务器启动脚本。在Unix中使用mysqld_safe

• mysql.server

  服务器启动脚本。是通过调用mysqld_safe来启动mysql服务器的

• mysqld_multi

  服务器启动脚本，可以启动或停止系统中安装的多个mysql服务器

• mysql_install_db

  用于默认权限创建Mysql授权表，只能在系统首次安装mysql时执行，并且只执行一次。

2.2.3 MySQL客户端常用程序

• mysql

  通过交互式SQL语句输入来执行的命令行工具。（Index)

• mysqldump

  用户备份数据的。（将MYSQL数据库转存至一个文件中.sql,.csv)

• mysqlshow

• error

• mysqlcheck

• mysqlhotcopy(MyISAM)

三、 MySQL架构

逻辑架构：

• 第一层为客户端的连接认证，C/S都有此架构
• 第二层为服务器层，包含MySQL的大多数核心服务功能
• 第三层包含了存储引擎，服务器通过API与其通信，API规避了不同存储引擎的差异，不同存储引擎也不会互相通信，另外存储引擎不会去解析SQL(InnoDB是例外，它会解析外键定义，因为服务器本身没有实现该功能)

四、 存储引擎

4.1. InnoDB

MySQ5.5及更高版本，默认存储引擎使用InnoDB，它提供了事务安全表（兼容ACID)，支持外键引用的

完整性约束。支持事务的提交，回滚和紧急数据恢复。它支持行级锁定。可以将数据存储在集群索引

中，从而减少了基于主键查询的I/O次数

4.2 MyISAM

管理非事务性表，提高了存储和检索的效率，支持全文搜索。

4.3 MEMORY*

将数据存储在RAM中，数据的存储、查询更快

4.4 MERGE

将多个类似的MYISAM表分组为一个表，可以处理非事务性表。

4.5 EXAMPLE

开发人员学习如何变成存储过程，不能存储和查询数据

4.6 ARCHIVE

用于存储海量数据，单不支持索引

4.7 CSV

以,来分割数据并存储

4.8 BLACKHOLE

只接受数据，不存储数据

4.9 FEDERATED

将数据存储到远程数据库中

mysql数据库中分为行和列。数据在计算机上存储是以页为单位存储的。

五、 数据类型

数据类型是定义列中可以存储什么数据以及该数据实际怎样存储的基本规则

5.1 串数据类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

数据类型	大小	说明
CHAR	0~255 bytes	定长字符串,长度必须在创建时指定
VARCHAR	0~65535 bytes	变长字符串
TEXT	0~64K	长文本数据
TINYTEXT	0~255 bytes	短文本字符串
MEDIUMTEXT	0~16K	中等长度文本数据
LONGTEXT	0~4GB	极大文本数据
BLOB	0~64KB	二进制形式的长文本数据
TINYBLOB	0~255 bytes	不超过 255 个字符的二进制字符串
MEDIUMBLOB	0~16MB	二进制形式的中等长度文本数据
LONGBLOB	0~4GB	二进制形式的极大文本数据
ENUM		接受最多64K个串组成的一个预定义集合的某个串
SET		接受最多64个串组成的一个预定义集合的零个或多个串

/*	
	TEXT：存放富文本编辑器中的数据 
	BLOB:用于存放二进制文本数据 
*/
#1. 枚举 
CREATE TABLE test_enum( 
    n1 ENUM('a','b','c') 
);
INSERT INTO test_enum(n1) values('a'); 
INSERT INTO test_enum(n1) values('b'); 
INSERT INTO test_enum(n1) values('c'); 
INSERT INTO test_enum(n1) values('d'); -- 超出枚举范围的会报错 

#2. 集合类型 SET关键字声明 
CREATE TABLE test_set( 
    n1 SET('a','b','c') 
);
insert into test_set(n1) values('a'); 
insert into test_set(n1) values('a,b'); 
insert into test_set(n1) values('a,b,c'); 
insert into test_set(n1) values('a,b,c,d'); -- 超出范围会报错

5.2 数值数据类型

5.2.1 整数类型（精确值）

类型	大小	范围（有符号）	范围（无符号）	用途
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)	极大整数值

#默认情况下，整型时有符号 
#以前版本超出范围只会保留临界值，8.0版本超出范围会报错 
#在创建表时，可以通过unsigned来指定该整型为无符号的 
CREATE TABLE test_int( 
    n1 INT,-- id的范围-2 147 483 648，2 147 483 647 
    n2 INT UNSIGNED -- 用unsigned关键字来指名该字段时无符号的 
);

#整型数据的长度（大小）是由类型决定，整型后加括号，用于指定数据的显示宽度，一般与ZEROFILL关键字 一起使用，作用是在左侧自动填充0已达到自定宽度,一旦使用ZEROFILL，有符号就会变成无符号 

CREATE TABLE test_int2( 
    n1 INT(8) ZEROFILL, 
    n2 INT UNSIGNED 
);

5.2.2 定点类型（精确值）

类型	大小	范围（有符号）	范围（无符号）	用途
DECIMAL/NUMERIC	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

5.2.3 浮点类型（近似值）

类型	大小	范围（有符号）	范围（无符号）	用途
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值

/*浮点型：
    float(M,D) 
    double(M,D) 
定点型
    decimal(M,D) 
    numeric(M,D) 
    M:代表小数位+整数位的总长度 D:小数位的长度 
    默认情况下： float,double,numeric,decimal会根据插入的数据来自动调整精度(float(10,3),10.000) 
    decimal默认MD为(10,0) 和整形一样，超出范围时，报错 
    
    定点型的精度比浮点型的精度高，设计到货币金额的字段一般都用定点型（decimal,numeric） 
*/
CREATE TABLE test_float( n1 float(10,3), n2 numeric(10,2) );
insert into test_float(n1,n2) values(11.1,12.2); 
insert into test_float(n1,n2) values(11.11111,12.2); 
insert into test_float(n1,n2) values(11.1,222.2222222); 
insert into test_float(n1,n2) values(11.1,22222222221111111111111111111111111111111.0);

5.2.4 其他数值类型

数据类型	说明
BIT	位字段，1~64位
REAL	4字节的浮点值
BOOLEAN	布尔标志，或者为0或者为1，主要用于开/关标志

5.3 日期时间数据类型

数据类型	说明
DATE	表示1000-01-01~9999-12-31的日期，格式为YYYY-MM-DD
TIME	格式为HH:MM:SS
DATETIME	DATE和TIME的组合
TIMEDTAMP	功能和DATETIME相同（但范围较小）
YEAR	用2位数字表示，范围是70（1970年）69（2069年），用4位数字表示，范围是1901年2155年

timestamp时间戳，微信公总号、支付宝开发过程中，参数是时间戳。timestamp能更好的体现时区，

如果项目对时区比较敏感，选择日期类型时推荐使用timestamp

CREATE TABLE test_date( 
    n1 DATETIME 
);
INSERT INTO test_date values('2020-01-01');

六、 SQL语句

6.1 基础语句

6.1.1 操作数据库

#查看mysql版本信息 
SELECT VERSION();-- 系统函数，常量 
#查看mysql中有哪些数据库 
SHOW DATABASES; 
#创建数据库 
CREATE DATABASE t2;-- t2数据库名称 
#切换数据库（默认登录后没有使用任何数据库，需要操作t2数据库 
USE t2；-- t2需要切换的数据库 
#删除数据库 
DROP DATABASE t2;-- t2就是需要删除的数据库的名称

6.1.2 操作表

#创建表 
/*语法:
	CREATE TABLE 表名( 
	字段名 类型 【约束】， 
	字段名 类型 【约束】, ...
	字段名 类型 【约束】 
	);
*/
#查看有哪些表 
SHOW TABLES; 
#查看表结构 
DESC test_int;-- test_int是表名 
#修改表 
ALTER TABLE test_int CHANGE n2 n3 varchar(10); -- 将n2字段重命名为n3
#删除表 
DROP TABLE test_int;

6.1.3 操作数据

#C 插入 
-- INSERT INTO 表名(字段列表) VALUES(值列表) 
INSERT INTO test_int2(n1,n2) VALUES(1,2); 
#R 查询 
SELECT * FROM test_int2; 
#U 更新 
-- UPDATE 表名 set 字段1=值1，字段2=值2 【WHERE 筛选】 
UPDATE test_int2 set n2=100; 
#D 删除
--  DELETE FROM 表名
DELETE FROM 表名 DELETE FROM test_int2;

6.1.4 导入导出

#项目开发环境和生产环境
##数据库的导出 
mysqldump -uroot -p123456 java1908z > d:\java1908z.sql 
##将生产环境中的数据库导入到开发环境中来 
#1.创建数据库 
CREATE DATABASE java1908z; 
#2.切换数据库 
USE java1908z; 
#3.导入数据库 
SOURCE D:\java1908z.sql;

6.2 DDL(Data Defifinition Language)：数据定义语言

6.2.1 关键字

• CREATE

  CREATE在数据库中创建一个对象，凡是数据库、数据表、数据库索引、存储程序、用户函数、触发程序或是用户自定义类型等对象，都可以使用CREATE指令来创建。

  CREATE DATABASE d1;-- 创建数据库--
  CREATE TABLE　t1;-- 创建数据表--
  CREATE INDEX i1;-- 创建数据表索引--
  CREATE PROCEDURE p1;-- 创建存储程序--
  CREATE FUNCTION f1;-- 创建用户函数--
  CREATE　TRIGGER　tr1;-- 创建触发程序--
  

• ALTER

  ALERT以不同方式修改现有对象的结构，相较于CREATE需要完整的数据对象参数，ALERT则是可以按照要修改的幅度来决定使用的参数。

  ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL;-- 在表doc_exa中加入列,名称为cilumn_b,类型为varchar(20),允许为NULL--
  ALTER TABLE doc_exb DROP COLUMN column_b ; -- 在表doc_exb中移出column_b列--
  alter table scm.scm_d_pp_detail_1h  change  column PP_ID_2H PP_ID_1H varchar(50)  -- 列改名
  alter table test modify address char(10) -- 修改表列类型--
  

• DROP

  DROP则是删除数据库对象的指令，并且只需要指定删除的数据库对象名称即可，在DDL语法中是最简单的。

  DROP TABLE myTable;--删除myTable表--
  DROP VIEW myView;--删除myView视图--
  

6.2.2 数据库的管理

#1. 创建数据库 
#语法1：CREATE DATABASE 库名; 
CREATE DATABASE t1; 
#语法2：CREATE DATABASE IF NOT EXISTS 库名 -- 当库名不存在时，创建他，如果存在，不执行 
CREATE DATABASE IF NOT EXISTS t1; 
#2. 修改数据库 
##一般不去修改数据库名称 
##修改数据的编码格式 
ALTER DATABASE t1 CHARACTER SET utf8;-- 修改t1的字符编码格式 
#3. 删除数据库 
#语法1：DROP DATABASE 库名; 
DROP DATABASE t2; 
#语法2： DROP DATABASE IF EXISTS 库名 -- 当库名存在时删除，否则不执行 
DROP DATABASE IF EXISTS t2;

6.2.3 数据表的管理

/*创建表
	CREATE TABLE 表名( 
		列名 类型【(长度) 约束】, ... 
		列名 类型【(长度) 约束】, 
		【表约束】, 
		【表约束】 )	
  修改表： 
	ALTER TABLE 表名 ADD|DROP|MODIFY|CHANGE COLUMN 列名【列的类型(长度) 约束】 
  删除表： 
  	DROP TABLE 表名 
 */
#1.创建表 
CREATE TABLE b_company( 
    id INT NOT NULL AUTO_INCREMENT COMMENT '主键ID', 
    name VARCHAR(20) NOT NULL COMMENT '公司名称', 
    record_date DATE COMMENT '注册时间', 
    PRIMARY KEY(id) 
);
#2.修改表名称 
ALTER TABLE b_company RENAME TO bcompany; 
#3.修改name字段的名称 
company_name ALTER TABLE bcompany CHANGE COLUMN name company_name VARCHAR(30) COMMENT '公司注 册名称'; 
#4.修改company_name字段的类型和约束 
ALTER TABLE bcompany MODIFY COLUMN company_name VARCHAR(20) NOT NULL;
#5.添加列 
ALTER TABLE bcompany ADD COLUMN fr_name VARCHAR(20) NOT NULL; 
#6.删除列 
ALTER TABLE bcompany DROP COLUMN fr_name; 
#7.删除表 
DROP TABLE bcompany; DROP TABLE IF EXISTS bcompany;-- 加入bcompany存在，将其删除，否则不执行 
#8.截断表（清空表，与DELETE的差别，truncate表时，自增主键重新开始计算，而删除时，不会从1开始 TRUNCATE TABLE bcompany; 
#9.复制表结构 
CREATE TABLE b_company LIKE bcompany; 
#10.复制表结构并赋值数据 
CREATE TABLE b_comapny2 SELECT * FROM bcompany; 
#11.如果赋值部分表结构，不复制数据 
CREATE TABLE b_company3 SELECT id,company_name FROM bcompany WHERE 0; 
#12.复制部分表结构，并复制数据 
CREATE TABLE b_company4 SELECT id,company_name FROM bcompany;

6.2.4 约束的管理

数据库管理系统不仅提供了数据的保存，数据的完整性，合法性进行限制，这种限制就叫约束。

常见约束

• PRIMARY KEY 主键约束，每张表中只能有一个主键约束。表中唯一一列可以确定一行的列称为主

  键。主键不能为空，表中可以没有主键。mysql中如果没有显示的定义主键，mysql InnoDB存储

  引擎会自动生成一个隐藏的自增主键。

• NOT NULL 非空约束，标识该列的数据不允许为空

• DEFAULT 默认约束

• UNIQUE 唯一约束。唯一约束的列可以为空

• CHECK 检查约束（检查你输入的数据是否满足我自定义的约束条件）

• FOREIGN KEY（外键约束）：外键约束是用于限制2张表之间的关系的，保证了外键字段中的数据

  来源必须源于另一张表。主表中的非主键字段指向另一个表的主键字段。主表中的该非主键字段是一个外键。

约束的添加时机

• 创建表时添加约束
• 修改表时添加约束

约束分类

• 列级约束：支持默认、非空、主键，唯一约束、检查约束，不支持外键约束
• 表级约束：CONSTRAINT 约束名 约束类型(字段名)，一般不添加非空约束，默认约束

#创建表时添加约束 使用列级约束 
#用于存储王者荣耀（吃鸡）用户信息 
CREATE TABLE b_user( 
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    age INT DEFAULT 18,-- 默认值 
    sex CHAR(1) CHECK(sex='男' OR sex='女'), 
    mobile CHAR(11) UNIQUE, ipaddr INT 
);
#在创建表时添加表级约束 
CREATE TABLE b_user2(
    id INT NOT NULL AUTO_INCREMENT,-- 自增策略 
    age INT DEFAULT 18, sex CHAR(1), 
    mobile CHAR(11), 
    ipaddr INT, 
    -- 表级约束 
    PRIMARY KEY(id),-- 增加了一个名为p的主键约束，但是主键约束默认的名称
    PRIMARY CONSTRAINT uq UNIQUE(mobile), 
    CONSTRAINT fk FOREIGN KEY(ipaddr) REFERENCES s_ipaddr(id)-- 添加外键约束 
);
INSERT INTO b_user(sex,mobile,ipaddr) values('男','1111',1); 
INSERT INTO b_user(sex,mobile,ipaddr) values('未知','1111',2);
#服务器列表 
CREATE TABLE s_ipaddr( 
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(20) 
);

#修改表时添加约束
CREATE TABLE b_user3( 
    id INT, 
    age INT,-- 默认值 
    sex CHAR(1),
    mobile CHAR(11),
    ipaddr INT 
);
#添加主键约束 
ALTER TABLE b_user3 MODIFY COLUMN id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;-- 修改的列级约束
ALTER TABLE b_user3 ADD PRIMARY KEY(id);-- 表级约束 
#添加默认约束
ALTER TABLE b_user3 MODIFY COLUMN age INT DEFAULT 18; 
#添加检查约束【8.0不能修改检查约束】 
ALTER TABLE b_user3 MODIFY COLUMN sex CHAR(1) CHECK(sex='男' or sex='女'); 
#添加唯一约束 
ALTER TABLE b_user3 MODIFY COLUMN mobile CHAR(11) UNIQUE;-- 添加列级唯一约束 
ALTER TABLE b_user3 ADD UNIQUE(mobile);-- 添加表级唯一约束 
#添加外加约束 
ALTER TABLE b_user3 ADD FOREIGN KEY(ipaddr) REFERENCES s_ipaddr(id);

#删除约束
/*可以使用列级约束的 
	ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 
  可以使用表级约束的，并且有约束名称（PRIMARY KEY)， 	
  	ALTER TABLE 表名 DROP 约束类型 约束名 
*/
#删除外键 
ALTER TABLE b_user2 DROP FOREIGN KEY fk; 
#删除主键 
ALTER TABLE b_user3 DROP PRIMARY KEY; 
#删除唯一约束 
ALTER TABLE b_user3 MODIFY COLUMN mobile VARCHAR(11); 
##DROP INDEX 唯一键的名称 
ALTER TABLE b_user3 DROP INDEX mobile; 

##查询b_users表中的index 
SHOW INDEX FROM b_user3;

6.3 DML(Data Manipulation Language)：数据操纵语言

6.3.1 关键字

• INSERT

  #单行插入 
  INSERT INTO 表名【(字段1，字段2，字段3)】 VALUES (值1,值2，值3) 
  ##注意 值列表的个数和字段列表的个数完全一致，并且类型一致 
  INSERT INTO b_user(name) VALUES('张三');
  INSERT INTO b_user VALUES(1,'李四','男','2020-01-01'); 
  -- 如果表名后没有字段列表，VALUES后的值列表中的个数和表字段个数一致，并且值列表的顺序和字段列表的 顺序一致。一般如果主键列自增，不显式的给自增列赋值 
  -- 在值列表中字符、日期字段都应该使用单引号括起来 
  -- 如果表中有NOT NULL字段，该字段必须赋值 
  -- 如果表中没有NOT NULL，可以赋值，也可以不赋值（NULL) 
  #多行插入 
  INSERT INTO 表名【(字段1，字段2，字段3..)】 VALUES (值1，值2，值3...),(值1，值2，值 3...),(值1，值2，值3...) 
  INSERT INTO b_user(name,sex,birthday) VALUES('王宝强','男','2020-01-01'), ('王','男','2020-01-01'),('王1','男','2020-01-01'); 
  #批量插入
  ##将一个查询结果全部插入到数据库中 INSERT INTO 表名【(字段1，字段2，字段3...)】(SELECT c1,c2,c3 FROM 表名 【WHERE 筛选条 件】) 
  CREATE TABLE temp_user LIKE b_user; INSERT INTO temp_user(name,sex,birthday) (SELECT name,sex,birthday FROM b_user WHERE user_id>2); 
  
  ##注意:字段列表应该和SELECT后的显示字段列表个数，类型一致
  

  了解

  INSERT INTO 表名 SET 字段名=值，字段名=值 
  INSERT INTO temp_user SET name='kobe',sex='男',birthday='1976-01-01';
  

• UPDATE

  /*语法:
  	UPDATE 表名 SET 字段名=值,字段名=值 注意：最后一个没有逗号 WHERE 筛选条件*/
  #将temp_user表中的所有性别都改为男 
  UPDATE temp_user SET sex='女'; 
  UPDATE temp_user SET sex='男' WHERE user_id=1 OR user_id=4;
  

• DELETE

  /*语法:
  	DELETE FROM 表名 WHERE 筛选条件 注意：如果没有筛选条件，等于清空整张表*/
  #删除ID=3的用户信息 
  DELETE FROM temp_user WHERE user_id=3; 
  ##企业开发过程中谨慎的去使用delete语句 
  #1.先用查询语句 
  SELECT * FROM temp_user WHERE user_id=2; 
  #2.将SELECT *改为DELETE 
  DELETE FROM temp_user WHERE user_id=2; 
  ##TRUNCATE（截断表)和DELETE的差别 
  #1. TRUNCATE在截断表时，自增列重新从1开始自增，DELETE不影响自增列 
  #2. DELETE支持事务回滚，而TRUNCATE不支持事务回滚
  

6.4 DQL(Data Query Language)：数据查询语言

6.4.1 关键字

• select

6.4.2 基础查询

/*#基础语法 
	SELECT查询字段列表（最后一个字段不加逗号） FROM表名 
  #执行顺序 
	先执行 FROM 再执行SELECT 
	注意：
	查询字段列表中：字段，函数，表达式，常量 查询结果是一张虚拟的二维表，不能对查询结果进行更新和删除。 
*/
#查询常量 
SELECT 1; 
#查询函数 
SELECT VERSION(); 
#表达式 
SELECT 100+200; 
SELECT 1>2;-- mysql中没有boolean类型，0代表false,1代表true 
#查询单个字段 
SELECT name FROM temp_user; 
#多字段查询 
SELECT * FROM temp_user; SELECT name,sex FROM temp_user;-- 在企业开发过程中推荐使用 
#多个字段+常量+函数+表达式 
SELECT name,sex,2,VERSION(),100+200,user_id*2 FROM temp_user; 
#IFNULL(参数1，参数2)，参数1放字段名，参数2放参数1字段为空时，你需要替换的值 
SELECT name,IFNULL(birthday,'未知') FROM temp_user; 
#别名设置 AS 关键字设置，别名可以用单引号括起来，也可以不加单引号 
SELECT name,IFNULL(birthday,'未知') AS 'birthday'FROM temp_user; 
#别名设置，省去AS关键字，别名可以用单引号括起来，也可以不加单引号 
SELECT name,IFNULL(birthday,'未知') birthday FROM temp_user; 
#去重 
DISTINCT SELECT DISTINCT name FROM temp_user; 
#字符拼接(在oracle和sqlserver中字符拼接直接用+可以拼接) 
##语法:CONCAT(字符1，字符2)，将字符1和字符2拼接
SELECT CONCAT('篮球',name) AS name FROM temp_user;

6.4.3 条件查询

/*语法结构： 
	SELECT 查询字段列表 FROM表名 WHERE筛选条件 
	筛选条件： 
        1.条件表达式：>,>=,<,<=,=,!=,<>,IS NULL,IS NOT NULL 
        2.逻辑表达式:AND,OR,NOT 
        3.模糊查询:LIKE,BETWEEN AND,IN 
	条件查询的执行顺序:
        1.FROM 
        2.WHERE 
        3.SELECT 
*/
#条件表达式 
##查询年龄>18的运动员 
SELECT * FROM temp_user WHERE age>18; 
##查询年龄不等于18 
SELECT * FROM temp_user WHERE age!=18; 
SELECT * FROM temp_user WHERE age<>18; 
#注意：对于NULL值的条件表达式，只能使用IS NULL或IS NOT NULL 
#查询生日不为空的 
SELECT * FROM temp_user WHERE birthday IS NOT NULL; 
SELECT * FROM temp_user WHERE birthday IS NULL; 
##如果筛选条件中包含2个或以上个条件表达式，我们应该在条件表达式中间使用逻辑表达式 
#查询年龄>18的并且生日不能为空 
SELECT * FROM temp_user WHERE age>18 AND birthday IS NOT NULL; 
#查询年龄<=18 或生日为空的 
SELECT * FROM temp_user WHERE age<=18 OR birthday is null; 
SELECT * FROM temp_user WHERE NOT(age>18 AND birthday IS NOT NULL); 
#模糊查询 
##模糊查询的语法:SELECT 查询字段列表 FROM 表名 WHERE 字段名 LIKE '通配符+字符' 
###2种通配符 
####% 代表任意（0或多个）个任意字符 
SELECT * FROM temp_user WHERE name LIKE 'kobe%';-- 查询已kobe开头的用户 
SELECT * FROM temp_user WHERE name like '%kobe';-- 以kobe结尾的用户 
SELECT * FROM temp_user WHERE name like '%kobe%';-- 包含kobe的用户 
####_ 代表1个任意字符，如果要表示_字符，使用\_ 
SELECT * FROM temp_user WHERE name like 'kobe_';-- 是以kobe开头，但是应该是5个字符 
kobe_ SELECT * FROM temp_user WHERE name like '_kobe';-- 以kobe结尾，前面只能有一个任意字符 
SELECT * FROM temp_user WHERE name like '_kobe_';-- 包含kobe，前后只能有一个字符 
##BETWEEN AND 闭合区间查询 
###语法： BETWEEN 值1 AND 值2 == 字段>=值1 AND 字段<=值2 值1必须小于值2
SELECT * FROM temp_user WHERE user_id BETWEEN 1 AND 2; 
SELECT * FROM temp_user WHERE user_id>=1 AND user_id <=2; 
#IN子句可以使用or来替换 
##查询user_id 在1,3,4中的数据 
SELECT * FROM temp_user where user_id=1 OR user_id=3 OR user_id=4; 
SELECT * FROM temp_user WHERE user_id in(1,3,4); 
##注意：在in的集合中不允许出现通配符 
SELECT * FROM temp_user WHERE user_id in(1,3,%); -- 错误写法

注意

在实际开发中筛选条件中的条件表达式，逻辑表达式，模糊查询经常会混合使用，在混合使用

时，使用()来提升优先级

#查询年龄>18并且name是已kobe开头的,或者年龄<18 并且name是包含kobe的，或者年龄=18 并且 name=kobe 
SELECT * FROM temp_user WHERE (age>18 AND name like 'kobe%') OR (age<18 AND name like '%kobe%') OR (age=18 AND name='kobe');

6.4.4 排序

对查询结果进行顺序调整

/*基本语法: 
	SELECT查询字段列表 FROM表名 【WHERE筛选条件 】ORDER BY 排序字段列表 
	注意：
        1.默认排序使用ASC升序排列（不加ASC|DESC),DESC降序（数字（1,2,3），字符（根据各个国家的 排序情			况）） 	   
        2.多个字段，按出现的先后顺序排列 
        3.在排序字段列表中支持：单个字段，多个字段，表达式，函数，别名 
        4.ORDER BY 放在查询语句的最后（除了LIMIT) 
	执行顺序
		1. FROM 2. WHERE 3. SELECT 4. ORDER BY 
*/
CREATE TABLE n1( 
    n1 INT PRIMARY KEY, 
    n2 INT,
    n3 INT, 
    n4 INT, 
    n5 VARCHAR(20) 
);
INSERT INTO n1 values(1,2,3,4,'a'); 
INSERT INTO n1 values(5,2,3,4,'a'); 
INSERT INTO n1 values(2,1,3,4,'b'); 
INSERT INTO n1 values(3,5,3,4,'c'); 
INSERT INTO n1 values(4,3,2,4,'d'); 
# 按n1进行升序排列 
SELECT * FROM n1 ORDER BY n1+1 ASC;
# 按n1进行降序排列 
SELECT n1 as tn1,n2,n3,n4,n5 FROM n1 ORDER BY n1 DESC; 
# 按n2,n3升序排列 
SELECT * FROM n1 ORDER BY n2,n3;

6.4.5 单行函数

6.4.6 分组函数

6.4.7 分组查询

6.4.8 连接查询

6.4.9 联合查询

6.4.10 子查询

6.4.11 分页查询

6.5 DCL(Data Control Language)：数据控制语言

6.5.1 关键字

• commit
• rollback