mysql笔记

一、学习环境

windows 10或windows 11  非家庭版操作系统

vmware workstation 16.0版本

安装1台windows server 2019-----IIS   WEB服务器

Linux操作系统   Centos 或 RHEL 安装mysql数据库服务器

应用系统服务器-----JSP、PHP、Python、.net、C#……

二、数据库和Mysql简介

1、什么是数据库？

你可还没有意识到,每当每个同学从自己手机的联系人中查找想要通话的姓名时,

这就是在使用数据库。如果在某个搜索引擎查找资料,也是在使用数据库。每个同

学到银行的取款机上取钱，这其实也是在使用数据库。

数据库实质是一个数据的集合，数据的仓库。

2、与数据库有关的几个概念

①数据data：凡是可以被计算机处理的对象。如：字母、汉字、声音、符号、动画

等。

②信息information:计算机用户或程序员或数据库管理员将数据经过加工处理后对

人们的决策起到帮助作用的有用数据。

③数据处理：数据-->数据处理-->信息

3、数据库系统的组成

DBS: database system,由数据库管理系统DBMS、数据库Database、数据库管理

员DBA、计算机、数据库应用系统DBAS。

DBMS------MySQL, Oracle、 Access, VFP、DB2、Ms-SQL属于系统软件的范围

DBAS------数据库应用系统 如学生管理系统、教务管理系统

4、为什么要有数据库

程序设计 数据类型(整型、浮点型、双精度、字符型、数组、字符串、结构体）

计算机基础 excel电子表格

5、没有数据库会产生的问题

存在大量的冗余数据,浪费资源

数据共享程度低

无法及时更新

无适应业务的扩展、业务需求

小结:

1.数据库:数据的仓库

2.据库中存放数据库对象(数据库表、视图、存储过程、函数等)

3.用户的数据是存放在数据库表中的

6、常见的关系型数据库

Oracel

Mysql

Ms-SQL server

DB2

http://db.engines.com/en/ranking

 

7、关系型数据库的基本概念

关系:用二维表的方式来表示实体和实体集之间的关系,

实体:客观存在的并且可以相互区别的事物。如1个人、1本书

实体集:一本书 ----- 实体 100本书 ---- 实体集

关系型数据库特征:二维表的组成行和列,行列存储数据。

行表示一条记录,也称为元组、实体信息, ---- 除了第1行

列表示一个字段,也称为属性, …-- 只有第1行

 

姓名年龄性别张三19男李四20女王五21男赵六18女

8、库

理解为文件柜、房子、容器。

专业的理解:库理解为存储数据的最大的分类,一般在项目的开发过程中会根据项

目来创建相应的库(一个项目创建一个库)

9、表

将用户数据存储到数据库表中,数据库表存放到数掘库中。

救据库表就是数据库中的一个对象,一般在开发软件项目的过程中会根据功能模块

能业务需求来创建相应的表。

用户可以根据应用的需要选择如何存储和索引数据库，是否使用事务等。Mysql默认支持多种引擎。

常用引擎：

MyISAm:不支持事务，也不支持主键外键，访问速度快。

InnoDB:支持事务，支持主键，外键，访问速度相对于myisam来讲慢一点。

Memory

Merge

show emgines; --查看mysql支持的数据库引擎

InnoDB和MyISAM引擎的区别：

create table student(sid int,sname char(20))default charset utf8;

show create table student;--查看表的搜索引擎

插入数据：

insert into student values(4,'hector');

insert into student values(1,'hector');

insert into student values(2,'hector');

insert into student values(3,'hector');

查询可以看到，查询结果与输入顺序是一致的

在表student表中增加主键：

alter table student add primary key (sid);

再次执行selet  * from student,可以看到查询结果的记录顺序与插入记录的顺序不同

更改表student 使用的数据库引擎后，再次查看表记录，发现记录顺序与插入记录一致。

适用命令修改数据库的存储引擎：

alter table student engine=innodb;

更改数据库默认的存储引擎

1.查看数据库支持的存储引擎

show engines;

2.显示默认数据库的存储引擎

show variables like 'table_type';

3.提示：修改配置文件后必须重启mysql服务

4.更改表的存储引擎

alter table student engine=myisam;

5.创建表时指定存储引擎

create table student1(sid int,sname char(20))engine=myisam;

在mysql命令行客户端提示符下输入？可以查看到当前模式可用到的命令

输入？contents 列出可以查看哪些帮助信息

？account management;

?set password;

?create user;

使用show查看Mysql的信息

输入？show查看show命令帮助

show databases; --查看mysql中所有数据库的名称

show tables;--查看当前数据库下的所有表名称

show engines;--查看数据库存储引擎

show create database db;--查看创建数据库db的语句

show create table stu;

show character set;

 show table statues;--查看所有表的状态

Vmware虚拟机介绍与学习环境规划

 

1、什么是虚拟机？

 

虚拟机(virtual Machine)指通过软件模拟的具有完整硬件系统功能的、运行在一个完全隔离环境中的完整计算机系统。

 

虚拟机系统是一个操作系统镜像，它具有与真实windows系统完全一样的功能，进入虚拟系统后，所有操作都是在这个全新的独立环境上进行，可以独立安装应用软件、保存数据、拥有自己的独立桌面，不会对真正的系统产生影响，而且具有能够在现有系统与虚拟镜像之间灵活切换的一类操作系统。

 

流行的虚拟机厂家有Vmware、Oracle virtual box、微软virtual PC等，它们都能在windows系统上虚拟出多个计算机(windows/linux)。

 

2、使用虚拟机的好处有哪些？

 

演示环境，可以安装各种演示环境，便于做实验----实验、学习环境

 

保证主机的快速运行，减少不必要的垃圾安装程序，偶尔使用的程序或者测试的程序可以在虚拟机上运行

 

避免每次重新安装操作系统

 

体验不同版本的操作系统，如Mac-OS、Linux、windows server

 

3、Vmware workstation虚拟机介绍

 

Vmware workstation中文名威睿工作站。是一款功能强大的桌面虚拟化软件(KVM、hyper-V、cytrix思杰)，提供用户可以在单一桌面上运行不同的操作系统，和进行开发、测试、部署新的应用程序的最佳解决方案。

 

Vmware workstation收费，非开源(源代码不开放、收费的)

第二章 Mysql1045错误怎么解决

1、  停止服务：停止MySQL服务；

 

2、  跳过验证：修改MySQL安装目录下的my.ini配置文件，使登录时跳过权限检查；

 

3、  修改密码：启动MySQL服务，登录MySQL，此时提示输入密码，输入任意密码回车即可进入MySQL。然后通过SQL语句修改root用户的密码；

 

4、  重启服务：将my.ini文件中加入的跳过权限语句删除或加#号注释。重启服务，使用修改后的密码登录即可

第二章 设置回环网卡

1、在物理机中添加回环网卡

2、到Vm虚拟机中选择VMnet X

3、将VmnetX桥接到新建的回环网卡上

4、修改VM虚机与物理机的ip地址

1物理机回环网卡的ip地址:10.10.10.10 255.255.255.0

2虚拟机网卡ip地址:10.10.10.100 255.255.255.0 ---- windows虚拟机

 

 

 

 

 

 

 

 

 

 

 

主机：

 

 

 

 

 

 

 

 

第二章 windows server 2019安装

1、正常引导系统安装U盘或者光盘后，可能会提示”Press any key to continue…”，此时需要按键盘任意键以启动Windows安装程序；

随后进入”Windows is loading files”画面和”Starting Windows”画面；

2、然后进入Windows安装程序图形界面，一般默认不动，点击”下一步”；

3、安装Windows，点击”现在安装”；

4、输入产品密钥，没有密钥可跳过，点击我没有产品密钥；

5、许可协议，勾选”我接受许可条款”复选框，再点击”下一步”；

6、选择”自定义：仅安装Windows（高级）（C）”；

7.系统会在新建主分区之前额外建立2 (3)个分区，请勿删除或者格式化这些分区。

如果使用光盘加载驱动，在点击”下一步”之前必须将Windows安装光盘放回光驱，否则无法继续安装。

8、开始安装Windows，在”安装更新”阶段完成后，可能会有一次自动重启；

重启后，Windows安装程序继续”完成安装”阶段，该阶段完成后，还会自动重启一次；

9、完成安装重启后，进入图形界面后会提示您建立密码，点击”确定”；

注：输入新密码，密码有一定复杂度要求，请一定牢记密码，如果忘记密码可能难以恢复；

10、完成密码设置后按”Ctrl”+”Alt”+”Delete”解锁，然后输入密码即可登录系统，安装完成

11、将刚开始准备工作中下载好的服务器驱动进行安装，勿使用驱动精灵、驱动人生等驱动安装程序安装驱动，会造成不兼容的情况。

第三章 Mysql 数据库的安装

1、下载并打开安装包，能看到版本是8.0.36，双击运行或者右键选择打开，打开后是一个安装向导，这个安装向导会先帮我们安装一个 mysql-installer 的程序，再通过该程序安装MySQL

2、选择自定义custom，接着下一步

3、选择开发者模式安装

4、选择安装路径，可以选择在其它盘

5、开始执行安装

6、安装完成，点击下一步

7、下面这个页面为配置选项，红框中的要选择开发者电脑，端口之类的一般默认即可，绿框建议勾选上，以后编程可能有机会用上，后面的名字可以自己取，选默认

8、选择验证方法，建议使用老一点的，比较稳定，选择新版本可能导致某些开源代码不兼容

9、设置root用户的密码

10、服务器的配置，默认即可

11、点击开始执行

12、设置router配置，暂时来说用不到，可跳过

13、输入刚才设置的密码，检查连接情况

14、最后会启动图形界面和shell界面软件，这两个程序都可以用于操作数据库，至此安装完成！

第三章 Mysql组件的作用

各个组件的作用：

MySql Server：Mysql数据库的服务器，我们操作数据一般都是连接到该服务器进行操作

MySql Workbench：可视化操作MySql数据库中的数据，说人话就是你可以用鼠标点击操作数据库

MySql Shell：这个就是通过命令行操作MySql数据库中的数据，学习命令时，推荐用这个，可以快速熟悉各种命令

MySql for visual studio：VS默认只显示自己的SQL Server源，所以要想MySql连接VS，就需要这个工具

Connector/NET :MySQL官方的.NET驱动程序，用于结合MySql for visual studio工具连接VS的

Connector/ODBC：该组件使用户可以用ODBC (Open Database Connectivity，开放数据库互联)数据库连接Mysql的服务器。比如说，用户可以使用Windows中的应用程序，如微软的 Access，Excel去连接数据库服务器。

Connector/J:连接java的包

Connector/C++：c++接口组件

Connector/Python：phthon接口组件

MySql Router：连接数据库与应用程序的中间组件

MySql Documentation：MySql数据库的使用文档

Samples and Examples：例子

第四章 数据库创建与常用操作

.Mysq18.0本地登录访问数据库

Mysql用户存储在mysql数据库的user表中,该表在mysql服务启动时自动加载到内存,控制用户的登录。

查看当前连接的mysql的用户

select user();

use mysql;

select user,host from user;

·创建mysql用户账户

create user hector@'localhost';

为新建本地用户修改密码

alter user hector@'localhost' identified by 'abc..123';修改hector用户的密码

为新建本地登录账户赋权

grant all privileges on*.* to hectar@'lacalhost' with grant option;

create user hector;

select host,user from user;

主机可以使用通配符,规则和标准的SQL语法中定义的完全相同

%表示任意长度的字符

_ 表示1位的任意字符

设置hector@'localhost'的密码为'abc .. 123'

创建用户时指定密码

create user hector@'localhost' identified by 'abc .. 123';

练习:

创建1个新用户设置密码并实现本地登录,注意授权。

creat user lwb@'localhost' identified by 'abc..123';

grant all privileges on . to lwb@'lacalhost' with grant option;

2.Mysql8.0远程访问数据库的设置

修改root密码

mysql -u root -p

use mysql;

alter user 'root'@'localhost' identified by 'abc .. 123';

exit;

alter user 'root'localhost' identified with mysql_native_password by 'abc .. 123';

允许root远程登录

二、数据库命名规范

数据库命名不能超过30个字符,推荐使用下划线命名方式

数据库命名必须为项目英文名称或有意义的缩写

数据库创建时必须添加默认字符集和字符序

utf8:utf8_general_ci不区分大小写 utf8_general_cs区分大小写;utf8_bin二进制

命名应使用小写

unicode 万国码,统一码、单一码:ucs2,utf16、utf161e、utf8、utf8mb4、utf32

utf8:1字符占1-3个字节 升级,老系统

utf8mb4:1字符占1-4个字节,新系统

第五章 CentOS Linux 7.6系统安装

1.要求

操作系统:CentOS 7.6 x86_64

数据库版本：mysql 8.0.39

字符集：utf8mb4

字符序：utf8mb4_general_ci，不区分大小写

2.环境准备

准备一台主机（ip：10.10.10.200 255.255.255.0）

安装CentOS Linux 7.6（桌面）

下载Mysql8.0并上传Mysql8.0至服务器

官网：https://dev.mysql.com/downloads

3.安装步骤：

配置hosts文件

echo "10.10.10.200 linux.test.com" >>/etc/hosts

cat /etc/hosts

vim /etc/hosts

10.10.10.200 linux.test.com

卸载主机自带的MySQL

rpm -qa | grep mysql

rpm -e --nodeps *mysql*

rpm -qa | grep mariadb

rpm -e --nodeps mariadb*

安装MySQL8.0

cd /mysql/

tar -xvf mysql-8.0.39-linux-glibc2.17-x86_64.tar

rm -f mysql-8.0.39-linux-glibc2.17-x86_64.tar

xz -d mysql-8.0.39-linux-glibc2.17-x86_64.tar.xz

tar -xvf mysql-8.0.39-linux-glibc2.17-x86_64.tar

mv mysql-8.0.39-linux-glibc2.17-x86_64 /usr/local/

mv /usr/local/mysql-8.0.39-linux-glibc2.17-x86_64  /usr/local/mysql

mkdir /var/lib/mysql

mkdir -p /usr/local/mysql/log

mkdir /usr/local/mysql/data

设置环境变量

vim ~/.bash_profile： PATH=$PATH:/usr/local/mysql/bin

验证：环境变量

[root@Linux ~]# which mysql

/usr/local/mysql/bin/mysql

创建MySQL用户和组，目录授权

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /var/lib/mysql

配置参数文件

vim /etc/my.cnf

[mysql] 

default-character-set=utf8mb4 

socket=/var/lib/mysql/mysql.sock

[mysqld] 

port = 3306

socket=/var/lib/mysql/mysql.sock

basedir=/usr/local/mysql 

character-set-server=utf8mb4 

default-storage-engine=INNODB

innodb_buffer_pool_size = 200M

max_allowed_packet=16M 

explicit_defaults_for_timestamp=1

log-output=FILE

general_log = 0

general_log_file=/usr/local/mysql/log/Linux.test.com.err

slow_query_log = ON

slow_query_log_file=/usr/local/mysql/log/Linux.test.com-query.err

long_query_time=10

log-error=/usr/local/mysql/log/Linux.test.com-error.err

default-authentication-plugin=mysql_native_password

MySQL数据库初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

cd /usr/local/mysql/log

tail -100f Linux.test.com-error.err

配置CentOS linux启动和停止脚本

vim /usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

LimitNOFILE = 65536

LimitNPROC = 65536

配置systemctl启动和停止方法

systemctl daemon-reload 

systemctl stop mysqld 

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

修改root密码

alter user 'root'@'localhost' identified by 'abc..123';

grant all privileges on *.* to 'root'@'localhost' with grant option;

flush privileges;

设置允许root用户远程登录

mysql> create user 'root'@'10.10.10.%' identified by 'abc..123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'10.10.10.%' with grant option;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00

第五章 Centos 7.6 安装 mysql8.0

配置hosts文件

echo "10.10.10.200 linux.test.com" >>/etc/hosts

cat /etc/hosts

vim /etc/hosts

10.10.10.200 linux.test.com

卸载主机自带的mysql

rpm -qa | grep mysql

rpm -e --nodeps mysql

rpm -qa | grep mariadb

rpm -e --nodeps mariadb*

安装mysql8.0

cd /mysql/

tar -xvf mysql-8.0.39-linux-glibc2.17-x86_64.tar

rm -f mysql-8.0.39-linux-glibc2.17-x86_64.tar

xz -d mysql-8.0.39-linux-glibc2.17-x86_64.tar.xz

tar -xvf mysql-8.0.39-linux-glibc2.17-x86_64.tar

mv mysql-8.0.39-linux-glibc2.17-x86_64 /usr/local/

mv /usr/local/mysql-8.0.39-linux-glibc2.17-x86_64 /usr/local/mysql

mkdir /var/lib/mysql

mkdir -p /usr/local/mysql/log

mkdir /usr/local/mysql/data

设置环境变量

vim ~/.bash_profile： PATH=$PATH:/usr/local/mysql/bin

验证：环境变量

[root@Linux ~]# which mysql

/usr/local/mysql/bin/mysql

创建mysql用户和组，目录授权

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /var/lib/mysql

配置参数文件

vim /etc/my.cnf

[mysql]

default-character-set=utf8mb4

socket=/var/lib/mysql/mysql.sock

[mysqld]

port = 3306

socket=/var/lib/mysql/mysql.sock

basedir=/usr/local/mysql

character-set-server=utf8mb4

default-storage-engine=INNODB

innodb_buffer_pool_size = 200M

max_allowed_packet=16M

explicit_defaults_for_timestamp=1

log-output=FILE

general_log = 0

general_log_file=/usr/local/mysql/log/Linux.test.com.err

slow_query_log = ON

slow_query_log_file=/usr/local/mysql/log/Linux.test.com-query.err

long_query_time=10

log-error=/usr/local/mysql/log/Linux.test.com-error.err

default-authentication-plugin=mysql_native_password

Mysql数据库初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

cd/usr/local/mysql/log

tail -100f Linux.test.com-error.err

配置CentOS linux启动和停止脚本

vim /usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

Documentation=man:mysqld(8)

Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

[Service]

User=mysql

Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

LimitNOFILE = 65536

LimitNPROC = 65536

配置systemctl启动和停止方法

systemctl daemon-reload

systemctl stop mysqld

systemctl start mysqld

systemctl enable mysqld

systemctl status mysqld

修改root密码

alter user 'root'@'localhost' identified by 'abc..123';

grant all privileges on . to 'root'@'localhost' with grant option;

flush privileges;

设置允许root用户远程登录

mysql> create user 'root'@'10.10.10.%' identified by 'abc..123';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on . to 'root'@'10.10.10.%' with grant option;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

远程连接需要关闭防火墙

firewall-cmd --state

systemctl stop firewalld.service

systemctl disable firewalld.service

第六章 表和库的管理

1.SQL语言简介

SQL：Structured Query Language结构化的查询语言，用来对数据库进行查询、更新和管理的一种特殊的语言。DML是SQL语言的核心。

包含三个部分：

1.DML

Data Manipulation language数据操纵语言

用于检索或更新数据库表：insert、delete、update、select增删改查

2.DDL

Data Defination language数据定义语言

用于定义的数据的结构：create alter drop

3.DCL

Data Control language数据控制语言

用于定义数据库用户的权限：grant revoke

2.数据类型

整数型：smallint、int、bigint

小数型：float、double

日期时间：date、time、datetime、timestamp

字符串：varchar、char，text

其他：clob存储文本大数据

           blod存储二进制大数据

3.创建表

1.语法

create table 表名

(

   列名 数据类型 特征, -- 字段名 属性名

   列名 数据类型 特征,

   ……

   列名 数据类型 特征

)charset=utf8;

2.示例

create table t_user

(

   id int,

   username varchar(20),

   password varchar(50)

);

create table t_student

(

   id int primary key auto_increment, -- 将id设为主键 自动增长 默认从1开始，每次递增

   name varchar(10) not null, -- 不允许为空

   age int,

   sex varchar(8) not null default '男', -- 指定默认值

   address varchar(100),

   height double,

   birthday date

)charset=utf8;

insertinto t_student(name,age,sex,birthday,height) values('张无忌',21,'男','2002-10-9',176.3);insertinto t_student(name,age,birthday,height) values('张三丰',21,'2002-10-9',176.3);insertinto t_student(name,age,sex) values(null,22,'女');insertinto t_student values('钱多多',20,'女','南京',172.1,now());

4.修改表

添加列

  语法：

      alter table 表名 add 列名 数据类型;

  示例：

  在t_student表中增加1个名为weight体重字段，类型为双精度。

      alter table t_student add weight double;

- 修改列的类型

  语法：

      alter table 表名 modify 列名 新数据类型;

  示例：

  将t_student表中name姓名字段的宽度修改为250。

      alter table t_student modify name varchar(250);

- 修改列名

      alter table 表名 change 原列名 新列名 数据类型;

  示例：

  将t_student表中sex字段名称修改为gender。

      alter table t_student change sex gender varchar(8);

- 删除列

  语法：

      alter table 表名 drop 列名;

  示例：

  删除t_student表中weight列（字段）。

      alter table t_student drop weight;

- 修改表名

  语法：

      alter table 原表名 rename 新表名;

      或

      rename table 原表名 to 新表名;

  示例：

  将t_student表名修改为student。

      alter table t_student rename student;

      rename table student to t_student;

5.删除表

语法：drop table 表名；

           drop table if exists 表名；

示例:

伤处test数据库中的t_user;

drop table t_user;

drop table if exists t_user;

6.截断表

清空表中的数据，作法类似于无条件的delete语句

语法：

    truncate table 表名;

示例：

    truncate table t_student;

    select * from t_student;

    insert into t_student(name,gender) values('tom','男');

    insert into t_student(name,gender) values('mike','女');

    select * from t_student;

delete与truncate的区别：

- delete会记录日志，所以速度慢，而truncate不记录日志，清空表并释放资源，速度快

- delete可以指定条件只删除部分数据，而truncate只能用来清空表中所有数据

- delete不会将自动增长列归零，而truncate会使自动增长的列如id列归零

7.创建库

语法：

    create database 数据库名 charset utf8;

    create database if not exists 数据库名 charset utf8;

示例：

     create database if not exists shop charset utf8;

8.删除库

语法：

    drop database 数据库名;

    drop database if exists 数据库名;

示例：

    drop database if exists shop;

第七章 字符集和排序规则

1.什么是乱码

1.select *from +表名

2.预备知识

1.字符： character eg：abcd 1234 , . ( )

2.字符集合：charset 一组字符

Ascii字符集合(7bit，最高位是0),标准的----可打印的

扩展Ascii字符集合（8bit） 拉丁语系，扩展----不可打印的字符

GB2312 简体中文

BIG5 繁体中文

GBK 包含简体中文和繁体中文

3.字符编码：给字符集合中的每1个字符指定1个二进制数字来标识。

4.字符集：字符集合+编码=字符集

5.字符序：字符的排序规则 1个字符集，可以有多个排序规则即多个字符序

以_ci结尾，大小写不敏感，不区分大小写

以_cs结尾,大小写敏感，区分大小写

3.常见的字符集

1.Ascii字符集

2.扩展Ascii字符集 latin1 8位二进制 包括Ascii字符集中的全部字符

3.GB2312 BIG5 GBK 16位二进制

4.unicode 字符集 全球语言 16位二进制

5.扩展Ascii字符集 latin1 latin2

6.unicode字符集 unicode编码 一个字符2个字节

utf8 一个英文字符 占一个字节

一个中文字符使3个字节

7.utf8mb3 utf8mb4 more bit

8.utf-8是一种变长的字节编码方式，对于某一个字符的utf-8编码，如果只有一个字节则其最高位二进制位为0；如果是多字节，其第一个字符节从最高位开始，连续的二进制位值为1的个数决定了其编码的位数，其余各字节均以10开头，utf8最多可以用到6个字节。

示例：

1字节 0xxx xxxx

2字节 110x xxxx 10xx xxxx

3字节 1110 xxxx 10xx xxxx 10xx xxxx

4字节 1111 0xxx 10xx xxxx 10xx xxxx 10xx xxxx

5字节 1111 10xx 10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx

6字节 1111 110x 10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx 10xx xxxx

A 0100 0001 65

B0100 0010 66

B>A

0 0011 0000 48

A a

a0110 0001 97

刘

1110 0101 1000 1000 1001 1010

E58896

A

0100 0001

41

示例：

新建文本文件，使用utf-8编码来保存，验证字符的字节数。

GBK字符集，一个英文占1个字节，一个汉字占2个字节。

 

 

 

 

 

 

 

 

第七章 字符，字符集，字符序

1、mysql8.0卸载

主要针对配置方式安装mysql8.0后的数据库的卸载。

1.停止mysql服务

命令行 net stop mysql(根据各自计算机系统的实际情况选择)

win+r-->serivices.msc-->选择mysql-->右键-->停止

2. 删除mysql服务

win+r-->cmd-->sc delete mysql

3.清理注册表

windows+r-->regedit-->注册表管理器

在这个文件路径下：

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\找到MySQLD Service以及MYSQL进行删除（如果没有就可以忽略），另外在你的电脑可能会有多个或者不同的ControlSet00x，都要将其中的MySQL删掉。

4.mysql 数据库连接方式

案例环境：

服务器：使用vmware虚拟机来实现

windows server 2019 10.10.10.100 255.255.255.0

linux server centos 7.6或8.4 10.10.10.200 255.255.255.0

2.1 使用crt客户端工具

2.2 在linux上通过命令行访问windows上的mysql

2.3 在windows上通过命令行访问linux上的mysql

本节中主要包含以下几个重点内容：

1.MySQL 8.0 卸载步骤

- 停止 MySQL 服务

- 删除 MySQL 服务

- 删除 MySQL 8.0 安装文件

- 清理注册表

- 使用 CRT 客户端工具

- 在 Linux 上通过命令行访问 Windows 上的 MySQL

- 在 Windows 上通过命令行访问 Linux 上的 MySQL

- 创建数据库 schoolDB

- 创建学生表 TStudent

- 创建课程表 TSubject

- 创建分数表 TScore

- 插入课程信息

- 创建函数 create_name 实现自动生成学生姓名

- 创建函数实现汉字转换为拼音

- 创建表 t_base_pinyin

- 插入对照数据

- 存储过程 addStudent 用于添加学生

- 调用存储过程 addStudent 添加 2000 名学生

- 存储过程 fillScore 用于插入学生成绩

- 连接 TStudent、TScore 和 TSubject 表查询结果

 

 

第七章 指定数据库和表及列的字符集

1.创建数据库时，指定数据库的默认字符集

create database db default character latin1;

2.创建表时，指定表的默认字符集

create table student(sid int,sname char(30))default charset utf8;

3.创建列（字段）时，指定列（字段）的字符集

create table student1(sid int,sname char(30),address char(20) character set latin1)default charset utf8;

回忆：

创建数据库时，如果不指定默认字符集，就会使用服务器字符集

character_set_server

创建表时，如果不指定表的默认字符集，就会使用数据库字符集

character_set_database

创建列时，如果不指定列的默认字符集，就会使用表的默认字符集

 

 

第七章 设置mysql的字符集

1.查看服务器和客户端字符集

show variables like 'character_set_%'; --查看默认字符集

2.修改默认字符集

临时修改

set names 字符集名称 ；--临时修改

注：此方法可临时修改三个字符集：

character_set_client

character_set_connection

character_set_results

set character_set_database=utf8; --使用此方法临时修改也可以

永久修改——修改配置文件

windows server —— my.ini

linux —— my.cnf

注：修改配置文件后必须重启mysql服务

net start mysql;

net stop mysql;

 

 

 

 

第八章 SQL介绍

SQL是访问和操作数据库的标准语言。

Structure query language

可以进行的操作:

SQL可以对数据库进行查询

SQL可以从数据库中检索数据

SQL可以给数据库表插入记录

SQL可以更新数据库表的记录

SQL可以从数据库表中删除记录

SQL可以创建新的数据

SQL可以从数据表中创建新表

SQL可以在数据库创建存储过程

SQL可以在数据库中创建视图

SQL可以设置表，过程和视图的权限

数据的元操作:

CRUD操作:

C: create 创建数据

R: retrieve/read获取数据/读取数据

U: update/alter 更新数据/修改数据

D:delete/drop 删除数据

第八章 表命令

1、查看所有表

show tables;

 

2、 创建表

前提：切换数据库，需要明确在哪个数据库中新建表

CREATE TABLE table_name ( -- table_name 表名 特别说明：数据库名、表名都应该有一意义

 

    column1_name data_type constraints, -- 列名 类型 属性

 

    column2_name data_type constraints,

 

    ....

 

)ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 存储引擎 字符集

 

3、示例：

创建一个用户表

 

CREATE TABLE users ( -- 在DB数据库下新建一个user表

 

    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

 

    name VARCHAR(50) NOT NULL, -- null值 表示是一个不确定的值

 

    birth_date DATE,

 

    phone VARCHAR(11) NOT NULL UNIQUE

 

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

主键：primary key 在一个表中至少应该有这样一个字段，这个字段所对应的值一不能有重复，二不能为空，这个字段所对应的值能够起到唯一标识表中每一条记录的作用。

上面的语句创建了一个名为users的表，其中包含5个字段id、name、birth_date和phone。注意，每个字段后面都有一个数据类型声明，表示该字段将存储何种类型的数据，例如：整数、字符串、日期等。

第八章 插入数据

语法:

insert into 表名(字段名1，字段名2，……) values(值1，值2，……);

缺省插入

实例:

给user表新增一条记录

insert into user(name,birthday,phone,age) values('老王'，'1954-10-5'，'13012345'，70);

全列插入

给user表再新增一条记录

insert into user values(1000,'老张'，'1964-10-7，13112345678，60);

insert into user values(0,'老李'，'1974-10-7'，13212345678，50);

说明:

给表中所有字段插入记录的时候，id自动增长型主键的值从0开始。

不同:

主键列是自动增长，但是在全列插入时需要占位，通常使用0，插入成功后以实际数据为准

查询数据

语法:

select * from 表名;--查看表记录

第八章 float精度测试

验证float double decimal误差

--新建测试表 tbl_test

create table tbl_test(

id int not null primary key auto_increment,

price1 float（9,3）not null,

price2 double (9,3) not null,

price3 decimal(9,3) not null

)engine=innodb default charset=utf8

 

 

 

 

第八章 更新约束

1.先创建一个表，除了主键，不加其它约束

CREATE TABLE users1 (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50),

birth_date DATE,

phone VARCHAR(11)

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

2.给手机号添加唯一约束

alter table users1 ADD UNIQUE (phone);

3.删除唯一约束

alter table users1 DROP INDEX phone;

4.给名字添加非空约束

alter table users1 modify name VARCHAR(50) not null;

5.删除非空约束

alter table users1

CHANGE COLUMN "name＂ "name" VARCHAR(50) NULL;

6.给生日添加默认约束

alter table users1 ALTER birth_date SET DEFAULT ＇1992-05-11＇;

7.删除默认约束

alter table users1 ALTER birth_date DROP DEFAULT;

第八章 数据库的命令

介绍数据库的CRUD操作

查看所有数据库

语法:

show databases;

创建数据库-C操作

语法:

create database 数据库名 charset 字符集;

切换数据库-R操作

语法:

use 数据库名;

查看当前所有数据库-R操作

语法:

select database();

修改数据库-U操作

语法:

alter database 数据库名[default] character set 字符集 [default] collate 字符序;

示例:

alter database db_test default character set gb2312 default collate gb2312_chinese_ci;

查看数据库编码

show create database 数据库名;

查看mysql支持的所有编码类型

show character set;

删除数据库-D操作

语法:

drop database 数据库名;

第八章 数据类型--浮点类型

1.float 实例:float (5,2) 范围:(-999.99 999 90)

桌子的长度 物理测理的度量值

2.double 实例: 误差比float低 ，科学计算，物理测量的值

3.decimal 实例:decimal(7,2) M = 7(包含整数部分和小数部分，小数点也要算作1位) D=2

小数点后数字的位数 如某一商品的价格是¥1234.56

 

 

第八章 数据类型--文本型和日期型

1.char(50) 宽度是固定。若占不完，空格补齐 查询效率高，浪费存储空间

2.varchar(50) 宽度是自动，用多少宽度分配多少宽度，查询效率低，节约空间

3.text 长文本类型 实例:简历 text类型

 

 

 

 

第八章 数据类型--整数型

1.tinyint 实例:性别 男1 女0 数据类型选择 tinyint

2.decimal 实例:价格 与财务有关的字段

3.mediumint 实例:博客访问数设置为该类型

4.int 实例:员工的编号，学生学号设置为该类型

5.bigint 实例:网站访问数设置为该类型

 

 

第八章 约束和引擎

主键:学生表中的学号，身份证号;课程表中的课程号;在一个表中至少应该有这样一个字段，这个字段所对应的值---不能有重复，不能有空间，它能够起到唯一标识表中记录的作用

not null 不能为空

实例:某个商品的价格是null，只能说这个商品价格尚未确定，不是免费

unique:满足主键条件的字段

设置为unique的字段，此字段所对应的值不能有重复

foreign key外键:在一个表中某一个字段 它不是当前这个表的主关键字，但是它却是另外一个表的关键字，我们在当前这个表中，把此字段称为是当前表的外部关键字

存储引擎:就是我们对数据进行增 删 改 查或CRUD操作的一种技术方式

 

 

第九章 SQL语句

准备数据

说明：

在schoolDB数据库下，在3张表：学生表 tstudent

分数表 tscore

课程表 tstudent

 

 

第九章 简单查询

1.查询所有字段

*代表所有字段

示例

查询学生表中所有学生信息

select * from tstudent;

回忆：

投影：从源表中选择符合条件的列（字段，属性）

选择：从源表中选择符合条件的行（记录，元组）

2.查找指定字段

查询单个字段

示例

查找学生的姓名，性别，邮箱

select sname,sex,email from tstudent;--投影

3.查询指定记录

查询满足条件的元组，使用where子句

示例

查询班级是java开发的学生的姓名，性别，邮箱，专业班级

select sname,sex,email,class from tstudent where class = 'java开发';

第九章 简单查询——关键字

1.带in关键字的查询

示例

查询专业班级为java开发，大数据和网络与网站开发的学生信息

select * from tstudent where class in("java开发"，"大数据"，"网络与网站开发");

2.带between…and…关键字的查询

示例

查询1983年至1987年之间出生的所有学生的信息

select * from tstudent where birthday between "1983-1-1" and "1987-12-31";

3.带like关键字的查询

通配符

% 代表任意多个字符

_代表任意多个字符

示例

查询学生表中所有姓王的学生信息

select * from tstudent where sname like '王%';

4.空值查询

空值：null,不确定的值。

查询奖金为空值的雇员信息

select * from emp where comm is null;

5.and并且

示例

查询学号小于18，性别为男，并且专业班级为java开发的学生信息

select * from tstudent where studentid<18 and sex='男' and class = 'java开发';

6.or或者

示例

查询姓名中含有'生'字，或专业班级为大数据的学生信息

select * from tstudent where like '%生%' or '大数据';

7.去掉重复值

示例

查询学生表的专业班级

select distinct class from tstudent;

8.使用limit限制查询结果的数量

功能：返回指定位置的指定条数

查询学生表中的前五名学生的信息

select * from tstudent limit 5;

9.union合并查询结果

union 集合的合并

select student,sname,sex,from tstudent where studentid='00013'

union

select studentid,sname,sex from tstudent where sname like '雷%';

10.as 为表和字段取别名

select studentid as 学号，sname as 姓名，birthday as 出生日期 from tstudent;

第九章 连接查询

1.内连接

表1 inner join 表2 on 条件

父表：主关键字所在的表

子表：外部关键字所在的表

select s.studentid,s.sname,sub.subjectname,sub.subjectid,sc.mark from

tstudent s inner join tscore sc on s.studentid=sc.studentid

inner join tsubject sub on sc.subjectid=sub.subjectid;

连接查询：去掉重复属性的等值相连

2.外连接

左连接(以左边的表为主)

select s.*,sc.from tstudent s left join tscore sc on s.studentid=sc.studentid;右连接(以右边的表为主)select s.,sc.*from tstudent s right join tscore sc on s.studentid=sc.studentid;

3.自连接

select a.studentid,a.sname,b.studentid,b.sname

from tstudent a inner join tstudent b

on a.sname=b.sname

where a.studentid<>b.studentid;

更新操作

DML数据操作语言

1.insert

语法：

insert into 表名(列名1，列名2，…) values(值1，值2，…);

示例：

insert into dept(deptno,dname,loc) values(50,"市场部"，"南京");

insert into dept(deptno,dname) values(60,"开发部");

如果依次插入表中的所有列，可以省略列名。

insert into dept values(11,"aaa","aaa"),(22,"bbb","bbb");

2.delete

语法：

delete from 表名 where 条件;

示例：

删除部门编号为60的部门信息？

delete from dept where deptno=60;

删除市场部所有员工工资高于5000的员工？

delete from emp where deptno = (select deptno from dept where dname="市场部")

and sal>5000;

注意：delete from emp 会将emp表中的所有数据全部删除

3.update

语法：

update 表名 set 列名1=值1，列名2=值2 where 条件;

示例：

update dept set dname = "market" where dname="市场部";

常量和变量

1.常量

定义：不变的量，常数项

数值常量

字符串常量：" "或' '

转义字符：\字符

\n 换行

\' 一个单撇号

\""一个双撇号

\\一个\如果没有转义字符，则认为\是一个转义字符的前导符

\% 一个%如何没有转义字符 则认为是通配符

\_一个_如果没有转义字符 则认为是通配符

示例：

select 'qian duo duo \'s' from dual;

布尔常量

在mysql中 true 是1 false 是0.

2.变量

用户自定义的变量 使用@开始 使用 set 可以给变量赋值

set @name='阿花'；-- name就是用户自定义的变量

select @name from dual;

insert into emp(empno, ename)

values(9900，'张三疯'）；

insert into emp(empno, ename) values(9901, @name);

set @eno=1001, @name='阿狗；

insert into emp(empno, ename) values(@eno, @name);

set @sid=10;

select @sid;

set @sid=@sid+1;

select @sid;

set @name=(select ename from emp where ename='smith');

select @name;

系统变量

 使用@@开始也可以使用set进行赋值，用来存储mysql当前

1.全局系统变量 针对所有用户默认配置

2.会话系统变量 针对当前用户生效 用户登录mysql会使用全局系统变量，如果

话系统变量，就使用会话系统变量的值，不过只针对当前用户生效。

示例：

show variables；显示会话系统变量

show global variables；显示全局系统变量

show session variables；显示会话系统变量

视图

简单 隐藏数据复杂性

安全 可以对视图授权

数据独立 可以屏蔽表结构变化对用户的影响

一、创建视图的目的

，比如增加列，更改列名

1. 创建单表视图

以下视图显示 JAVA 班的学生姓名、身份证号和班级。

二、创建视图

CREATE VIEW vJAVAstudent

as

SELECT sname 姓名,cardID 身份证号,class 班级 from `TStudent` where class='JAVA'

查询视图

select * from vJAVAstudent;

 

 

select 姓名,身份证号 from vJAVAstudent where 姓名='白安'

 

2. 在多表上创建视图

创建一个视图，展示学生学号 姓名 身份证号 班级 科目 成绩

create VIEW vStudentInfo

as

select a.studentid 学号,sname 姓名,cardID 身份证号,class 班级,subjectName 科

目,mark 成绩 from `TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject`

c on b.`subJectID`=c.`subJectID`

查询视图

select * from `vStudentInfo`

 

 

查找各科最高分

select 科目,MAX(成绩) 最高分 from `vStudentInfo` group by 科目

 

 

3. 创建视图

该视图显示学生学号 姓名 分数 和学科

CREATE VIEW `studentMark`

AS

(

SELECT a.studentid 学号,sname 姓名,mark 分数,subJectName 学科

 FROM TStudent a JOIN TScore b ON a.studentid=b.studentid

 JOIN TSubject c ON b.subjectid=c.subjectid

);

查询该视图

select * from studentMark

 

 

查看学生的学号 姓名 身份证 总分 平均分

select 学号,姓名,班级,AVG(成绩) 平均分,sum(成绩) 总分 from vStudentInfo group by

学号,姓名,班级 order by 总分 desc

4. 行列转换视图

以上视图为表，创建行列转换视图

create view chengji

as

SELECT 学号,姓名,

AVG(CASE 科目 WHEN '大数据' THEN 成绩 END) 大数据,

 AVG(CASE 科目 WHEN '网络管理' THEN 成绩 END) 网络管理,

AVG(CASE 科目 WHEN 'JAVA 开发' THEN 成绩 END) JAVA 开发,

 AVG(CASE 科目 WHEN '人工智能' THEN 成绩 END) 人工智能

FROM vStudentInfo group by 学号,姓名

查询 chengji 视图

 select * from chengji

 

 

5. 查看视图基本信息

三、查看视图

DESCRIBE `vStudentInfo'

 

 

 

如图，双击 vStudentInfo 视图，可以查看创建的视图

 

6. 在 views 表中查看视图详细信息

使用 SQL admin 连接到 Information_schema 数据库

输入 SELECT * FROM information_schema.views;

 

 

1. 使用 create or replace 修改视图

给现有视图添加一列 email

create OR REPLACE

四、修改视图

VIEW vStudentInfo

as

select a.studentid 学 号 ,sname 姓 名 ,cardID 身 份 证 号 ,email 邮 箱 ,class 班

级 ,subjectName 科 目 ,mark 成 绩 from `TStudent` a join `TScore` b on

a.`StudentID`=b.`StudentID` join `TSubject` c on b.`subJectID`=c.`subJectID`

2. 使用 ALTER 修改视图

给现有视图去掉身份证号列

ALTER VIEW vStudentInfo

as

select a.studentid 学号,sname 姓名,email 邮箱,class 班级,subjectName 科目,mark 成

绩 from

`TStudent` a join `TScore` b on a.`StudentID`=b.`StudentID` join `TSubject` c on

b.`subJectID`=c.`subJectID`

五、通过视图更改数据

通过视图来插入、更新、删除表中的数据，都是修改的基本表上进行更新

3. 通过视图更改数据

select * from `vStudentInfo` 

 

 

给韩旭的数据结构加 10 分

update vStudentInfo set 成绩=成绩+10 where 姓名='易祥辉' and 科目='.net 开发'

update vStudentInfo set 姓名='韩立刚' where 姓名='易祥辉' and 科目='.net 开发'

4. 通过视图插入数据

下面视图能够插入数据，视图来自一张表，且视图必须包含表中不允许为空的那些列。

alter VIEW vJAVAstudent1

as

SELECT studentID 学号,sname 姓名,cardID 身份证号,class 班级 from `TStudent` where

学号='10006'

通过视图插入记录

insert into vJAVAstudent1 values ('10006','章玉华','111111111','net')

select * from vJAVA

 

5. 通过视图删除数据

视图中的数据只能来源于一张表。

delete from Vjavastudent where 身份证号='3846152307322401'

 

索引

一.索引定义

索引是一种独立存在的，对数据库表中一列或多列的值进行排序(目的:加快查询速度，提高效率)

数据库引擎：innodb支持索引.

show create table 表名——查看数据库索引

二.案例演示

索引是如何提高查询速度?

实例:

set profiling=1——初始化计时器

由于name没有建索引，所以使用以下sql语句执行后观察所用时间

show profiles;--查看查询的执行时间

select * from test_index where name='我的年龄12999';

create index name_index on test _index(name);

结论:索引可以提高查询速度。

三.建立索引

索引的分类

1.普通索引index:简单排序 加速查询

2.唯一索引 unique

    主键索引:primary key 加速查询+实现实体完整性约束(不能有重复，也不能是null值)

  唯一索引:unique加快查询 不允许有重复值 可以空值(null值)

create unique index 索引名字 on 表名(字段(长度));

3.联合索引

primary key(id name):联合主键索引

unique(id name):联合唯一索引

index(id name):联合普通索引

4.全文索引 功能:分词

create fulltext index 索引名字 on 表名 (字段1(长度));

create fulltext index 索引名字 on 表名(字段1(长度)，字段2(长度));

5.空间索引 了解 数据库索引

索引建立方式

普通索引

语法:

方法一:

create index 索引名 on 表名(字段名(长度));

方法二:

通过修改表结构来建索引

alter table 表名 add index 索引名(字段名(长度));

方法三:

create table 表名(字段名1 类型1(宽度))

                            ……，

                           index 索引名(字段名(宽 度)));

唯一索引

创建唯一索引

方法一:

create unique index 索引名 on 表名(字段名(宽度));

方法二:

alter table 表名 add unique 索引名(字段名(长度));

方法三:

create table 表名(字段名1 类型1(宽度))

                            ……，

                           unique 索引名(字段名(宽度)));

示例:

create table mytable(

id int not null,

username varchar(16) not null,

primary key(id)

);

删除索引

语法:

drop index 索引名 on 表名;

示例:

drop index xxhh on test_index;

索引的作用

索引可以提高我们的查询效率

索引的缺点

会占用一定磁盘空间

会影响更新，插入，删除效率

第一范式——字段的原子性，字段的不可分割性

第一范式是属性不可分割，每个字段都应该是不可再拆分的

 

第二范式——依赖主键

第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记任主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思,，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

第三范式——引用主键

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。