MySql 学习笔记（一）： MySql数据库

数据库

数据库概念

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修 改，删除及查询操作。

数据库排行：

数据库分类

• 关系型数据库
• 非关系型数据库 NoSOL

关系型数据库

数据库中的【记录是有行有列的数据库】就是关系型数据库(RDBMs,Relational Database Management system)与之相反的就是 NoSQL 数据库了。

数据库管理系统

广义上的数据库：数据管理系统RDBMS

狭义上额数据库：真正存储数据的地方

数据库管理系统(DataBase Managementsystem，DBMS):指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。DBMS 可以管理多个 数据库，我们建议每个项目系统，对应一个数据库，避免数据混乱。然后可以在数据库中，根据具体操作数据对象，对应创建多个表。比如，商城管理系统中，有商品表、订单表、用户表等等。

数据库与数据库管理系统的关系

数据库表

数据库中以表为组织单位存储数据。表中的每个字段都有对应的数据类型。每一列数据存储的同一类型的数据。

表数据

表中的一行一行的信息我们称之为记录。根据表字段所规定的数据类型，向其中填入一条条数据（符合数据类型和范围）。

常见的数据库管理系统

1. MySQL:开源免费的数据库，小型的数据库。已经被甲骨文收购了，MySQL6.x版本也开始收费。
2. 甲骨文:收费的大型数据库，甲骨文公司的产品。甲骨文收购SUN公司，收购MYSQL。
3. DB2:IBM公司的数据库产品，收费的。常应用在银行系统中。
4. SQL Server:微软(Microsoft)公司收费的中型的数据库。C#、net等语言常使用。
5. SyBase:已经淡出历史舞台。提供了一个非常专业数据建模的工具。
6. sQLite:嵌入式的小型数据库，应用在手机端。

MySql数据库

概念

MySQL 是最流行的【关系型数据库管理系统】，在 WEB 应用方面 MySQL是最好的RDBMS 应用软件之一。

安装

通过 brew 安装 MySQL

在 MacOS 上，我们可以通过 brew 很容易地安装 MySQL。

安装 MySQL

brew install mysql

配置 MySQL 服务器

我们需要运行以下脚本配置 MySQL 服务器的安全性：

mysql_secure_installation

在这个过程中，你可以设置 root 的密码，配置一些选项以增强 MySQL 服务器的安全性。你会看到如下类似的输出：

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done!

通过安装包安装 MySQL

通过安装包安装 MySQL 的过程有友好的 UI 界面，更加的方便快捷。

下载安装包

请点击这里去下载 MySQL 社区版的 dmg 文件，它包含了 MySQL 的安装器。

安装 MySQL

下载安装包后，请按照以下步骤进行安装：

1. 双击下载 dmg 文件。双击后，会看到 MySQL 安装器文件，比如： mysql-8.0.26-macos-10.13-x86_64.pkg。双击 MySQL 安装器文件。
2. 在介绍页面，直接点击 “继续” 按钮。
3. 在 “许可” 页面，直接点击 “继续” 按钮。
4. 在 “安装类型” 页面，可以直接点击 “安装” 按钮，也可以先点击 “自定义” 按钮选择要安装的组件后再点击 “安装” 按钮。
5. 在 “配置” 页面，密码加密方式选择 Use Strong Password Entryption。然后，输入 root 用户的密码。点击 “Finish” 按钮就完成安装了

SQL语句

SQL概述

介绍

结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL 语句，但都有特有内容。

扩展:SQL作为一种访问【关系型数据库的标准语言】，SQL 自问世以来得到了广泛的应用，不仅是著名的大型商用数据库产品Oracle、DB2、sybase、sQLserver 支持它，很多开源的数据库产品如 PostgreSQL、MySQL也支持它，甚至一些小型的产品如Access 也支持 SQL。近些年莲勃发展的 NOSQL 系统最初是宣称不再需要 SQL的，后来也不得不修正为 Not Only SQL，来拥抱SQL. 蓝色巨人 IBM 对关系数据库以及 SQL语言的形成和规范化产生了重大的影响，第一个版本的 SQL 标准 SQL86 就是基于 system R的手册而来的。 Oracle 在 1979 年率先推出了支持 SQL 的商用产品。随着数据库技术和应用的发展，为不同RDBMS提供一致的语言成了一种现实需要。 对 SQL 标准影响最大的机构自然是那些著名的数据库产商，而具体的制订者则是一些非营利机构，例如【国际标准化组织 10、美国国家标准委员会 ANSI】等。 各国通常会按照 IS0 标准和 ANSI 标准(这两个机构的很多标准是差不多等同的)制定自己的国家标准。

SQL作用

• 在数据库中检索信息。
• 对数据库的信息进行更新。
• 改变数据库的结构。
• 更改系统的安全设置。
• 增加或回收用户对数据库、表的许可权限.

SQL语句分类

• 数据定义语言:简称DDL(Data Definition Language)
  • 作用：用来定义数据库对象:数据库，表，列等。
  • 关键词:create，alter，drop等
• 数据查询语言:简称DQL(Data Query Language)
  • 作用：用来查询数据库中表的记录。
  • 关键词:select，from，where等
• 数据操作语言:简称DML(Data Manipulation Language)
  • 作用：用来对数据库中表的记录进行更新。
  • 关键词:insert,delete，update等。
• 数据控制语言:简称DCL(Data Control Language)
  • 作用：用来定义数据库的访问权限和安全级别，及创建用户。

SQL通用语法

• SQL语句可以单行或多行书写，以分号结尾
• 可使用空格和缩进来增强语句的可读性
• MYSQL数据库的SQL语句不区分大小写，关键字建议使用大写
  • 例如:SELECT * FROM user.
• 常用注释形式

--单行注释内容
/* 多行注释 */
#单行注释内容

• SQL常用数据类型

类型名称	说明
int (integer)	整数类型
double	小数类型
decimal (m,d)	指定整数位与小数位长度的小数类型 decimal(10,2)
date	日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒2019-05-06
datetime	日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒 2019-05-06 09:49:30
timestamp	日期类型，时间戳
varchar(M)	文本类型，M为0-65535之间的整数

DDL的数据库操作：database

1）创建数据库

create database 数据库名;
create database 数据库名 character set 字符集;
--【案例】
--1.创建一个叫 he11o 的数据库
create database he11o;
--2.如果不存在则创建
create database if not exists he11o;
--3.创建数据库并指定字符集
create database hello defauit character set gbk;

2）查看数据库

查看数据库服务器中的所有的数据库:show databases;
查看某个数据库的定义的信息:show create database 数据库名;
-- 【案例】
--查看数据库服务器中的所有的数据库
show databases;
--查看某个数据库的定义信息
show create database he11o;

3）删除数据库

drop database 数据库名称;
--【案例】
drop database he11o;

4）修改数据库

-- 修改数据库默认的字符集:
alter database 数据库名 default character set 字符集
-- 【案例】
alter database hello3 character set utf8;

5）其他操作数据库

切换数据库: use 数据库名;
在看正在使用的数据库:select database();

DDL的表操作：table

1）创建表

建立数据表，就是创建表结构，指定数据表中一共有多少列，每一列的数据类型

-- create 指的是【创建】，table 指的是【数据表】
-- 一张表中可以指定多个字段，用逗号隔开，最后的字段不需要逗号
create table 表名(
    --可以定义多个列字段名 类型(长度)约束，字段名 类型(长度)约東
    字段名 类型（长度）约東,
    字段名 类型（长度）约東
)
--【案例】
-- 创建用户表
create table user(
   uid int primary key auto_increment --用户id
   uname varchar(20) -- 用户名
)

字段类型

常用的类型有：

1. 数字型：int、integer、bigint、mediumint、smallint、 tinyint
2. 浮点型：double、float、decimal （精确小数类型）
3. 字符型：char（定长字符串）、varchar（可变长字符串）
4. 日期类型：date （只有年月日）、time （只有时分秒)、datetime（年月日，时分秒）、year （年）
5. 二进制字符串类型：binary（定长，以二进制形式保存字符串）、varbinary（可边长）

表单约束

主键约東：primary key
唯一约東：unique
|非容约味•not nuil

注意： 主键约束=唯一约束＋非空约束

2）查看表

查看数据库中的所有表：show tables;
査看表结构：desc 表名;  
査看创建表的 SQL 语句：show create table 表名;

-- 【案例】
use hello: -- 使用 hello 数据库
show tables -- 查看所有表
desc user -- 査看 user 表
show create tableuser;  -查看user表的创建语句

3） 快速创建一个表结构相同的表

create table 新的表名 Like 旧日的表名

-- 【案例】
create table tb_user like user;
desc tb_user:

4）删除表

drop table 表名
drop table if exists 表名;
-- [案例)
-- 删除用户表
drop table user:

5）修改表

-- 修改表添加列
alter table 表名 add 列名 类型（长度）约束;

-- 修改表，修改列的类型长度及约束
alter table 表名 modify 列名 类型（长度）约束；

-- 修改表，修改列名
alter table 表名 change 旧列名 新列名 类型（长度）约束；

-- 修改表删除列
alter table 表名 drop 列名：

-- 修改表名
rename table 表名 to 新表名：

--【案例】
-- 修改表添加列
alter table user add address varchar(50):

-- 修改表修改列的类型长度及约束
alter table user modity address int(30);

-- 修改表修改列名
alter table user change addressaddr varchar(50);

-- 修改表删除列
alter table user drop addr;

-- 修改表名
rename table user to tb_user;

DML数据操作语言

1）插入记录：insert

语法：

-- 向表中插入某些列
insert into 表(列名1，列名2，列名3）values（值1，值2，值3）；

-- 向表中插入所有列
insert into 表 values（值1，值2，值3）；

-- 从另外一张表查某些列的结果插入当前表
insert into 表（列名1，列名2，列名3〕 values select 〔列名1，列名2，列名3〕from 表

-- 从另外一张表查所有列的结果插入当前表
insert into 表 values select * from 表

--【案例】
-- 向表中插入某些列，必须写列名
insert into user （uid, uname) values (001, "cuihua"):

-- 向表中插入所有列
insert into user values (002, 'agiang");

注意：

1. 列名数与 values 后面的值的个数相等
2. 列的顺序与插入的值的顺序一致
3. 列名的类型与插入的值要一致.
4. 插入值得时候不能超过最大长度.
5. 值如果是字符串或者日期需要加引号〞（一般是单引号）

2）更新记录：update

语法格式：update 更新、set 修改的列值、where 指定条件。

-- 不指定条件，会修改表中当前列所有数据
update 表名 set 字段名二值，字段名二值；

-- 指定条件，符合条件的才会修改
update 表名 set 字段名=值，字段名=值 where 条件；

-- 【案例】
-- 更新所有字段的值
update user set uname='xiaodong ':

--根据指定的条件来更新
update user set uname='hashiqi' where uid = 2

注意：

1. 列名的类型与修改的值要一致
2. 修改值的时候不能超过最大长度
3. 值如果是字符串或者日期需要加’’引号

3） 删除记录：delete & truncate

语法：

delete from 表名 [where 条件]；

-- 【案例】
-- 删除表中所有数据
delete from user:

-- 删除 uid 为1的用户
delete from user where uid = 1;

truncate table 表名；

注意

删除表中所有记录使用 【delete from 表名】，还是用 【truncate table 表名】？

删除方式的区别：

delete:一条一条删除，不清空 auto_increment 记录数。
truncate：直接将表删除，重新建表，auto_increment 将置为零，从新开始

SQL 约束

约束类型：

• 主键约束 primary key
• 唯一性约束 unique
• 非空约束 not null
• 外键约束 foreign key

主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

• 主键必须包含唯一的值。
• 主键列不能包含 NULL值。
• 每个表都应该有一个主键，并且每个表只能有一个主键。

添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE persons（
    id_p int PRIMARY KEY,
    lastname varchar (255), 
    firstname varchar (255), 
    address varchar (255)
    city varchar (255)
）

• 方式二：创建表时，在constraint约束区域，声明指定字段为主键：
  • 格式：【constraint 名称】 primary key (字段列表〕
  • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
  • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键，

 CREATE TABLE persons ( 
     firstname varchar (255), 
     lastname varchar (255), 
     address varcharess, 
     city varchar (255),  
     CONSTRAINT pk_personID PRIMARY KEY (firstname, lastname)
 ）

• 方式三：创建表之后，通过修改表结构，声明指定字段为主键：

  格式: ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY （字段列表）

CREATE TABLE persons (  
    firstname varchar (255), 
    lastname varchar 255).  
    address varchar (255).  
    city varchar (255)
）
ALTER TABLE persons ADD PRIMARY KEY (firstname , lastname);

删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL:

ALTER TABLE persons DROP PRIMARY KEY

自动增长列

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用auto_increment（自动增长列） 关键字，自动增长列类型必须是整形，自动增长列必须为键（一般是主键）

• 下列 SQL 语句把"persons” 表中的"p_id"列定义为 auto_increment 主键

CREATE TABLE persons（
    p_id int PRIMARY KEY AUTO_INCREMENT,
    lastname varchar (255),
    firstname varchar (255),
    address varchar (255),
    city varchar （255)
）

• 向persons添加数据时，可以不为p_id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO persons (firstname, lastname) VALUES ('Bill', 'Gates")
INSERT INTO persons (p_id, firstname, lastname) VALUES (NULL, 'Bill', 'Gates")

• 默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列SQL语法：

ALTER TABLE persons AUTO_INCREMENT=100

非空约束

NOT NULL約束强制列不接受 NULL 値。

NOT NULL约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

•下面的SQL语句强制"id_p个列和"astname"列不接受 NULL值

CREATE TABLE persons（
    id_p int NOT NULL,
    lastname varchar （255) NOT NULL, 
    firstname varchar (255), 
    address varchar (255)  
    city varchar255
)

唯一约束

• UNIQUE约束唯一标识数据库表中的每条记录。
• UNIQUE和PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
• PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

注意：每个表可以有多个 UNIQUE约束，但是每个表只能有一个 PRIMARY KEY 约束。

添加唯一约束

与主键添加方式相同，共有3种

• 方式一：创建表时，在字段描述处，声明唯一：

CREATE TABLE persons (  
    id_p int UNIQUE,
    lastname varchar (255) NOT NULL,
    firstname varchar (255), 
    address varchar (255), 
    city varchar (255)
）

• 方式二：创建表时，在约束区域，声明唯一：

CREATE TABLE persons(
    id_p int.
    lastname varchar 255 NOT NULL,
    firstname varchar (255), 
    address varchar (255),
    city varchar (255), 
    CONSTRAINT 名称 UNIQUE (id_P)
)

• 方式三：创建表后，修改表结构，声明字段唯一：

ALTER TABLE persons ADD 「CONSTRAINT 名称］ UNIQUE (Id_P)

刪除唯一約束

• 如需撤销UNIQUE约束，请使用下面的SQL:

 ALTER TABLE persons DROP INDEX 名称

• 如果添加唯一約束时，没有设置约束名称，默认是当前字段的字段名。

常见问题

MysQL数据库密码重置

1. 停止mysql服务器运行输入services.msc 停止mysql服务
2. 在cmd下,输入mysqld-console-skip-grant-tables启动服务器,出现一下页面,不要关闭该窗口

3. 新打开cmd,输入mysal-uroot 不需要密码

use mysql;
update user set password=password('root') WHERE user='root'

4. 关闭两个cmd窗口

DOS操作数据乱码解決

Iinsert into category (cid,cname） values(‘c010’，’中文’)；
ERROR 1366 (HY000) : Incorrect string value: '\XB7\XFE\XD7\XBO' for column 'came' at row 1

错误原因：因为mysql的客户端设置编码是utf8,而系统的cmd窗口编码是gbk

1. 查看MySQL内部设置的编码

show variables like 'character%’；查看所有mysql的编码

2. 需要修改client、connection、results的编码一致 （GBK编码）

解决方案1：在cmd命令窗口中输入命令，此操作当前窗口有效，为临时方案。

set names gbk;

解决方案2：安装目录下修改my.ini文件，重启服务所有地方生效