基于MYSQL的数据库原理与应用学习心得 mysql数据库技术与应用

这里写目录标题

• 初识MySQL

• 什么是数据库
• 数据库分类
• MySQL简介
• 安装MYSQL

• win10安装
• 安装SQLyog
• 连接数据库
• linux安装
• 使用Navicat Premium连接数据库

• 操作数据库

• 操作数据库
• 数据库的数据类型
• 数据库的字段属性（重要）
• 创建数据库表
• 数据表的类型
• 修改和删除表
• 导入表操作

初识MySQL

JavaEE：企业级java开发 Web

前端（页面：展示，数据）

后台（连接点：连接数据库JDBC，链接前端（控制，控制试图跳转，和给前端传递数据））

数据库（存数据，Txt，Excel，word）

什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统之上！SQL，可以存储大量的数据。500万

作用：存储数据，管理数据

数据库分类

关系型数据库：

• MySQL，Oracle，Sql Server，DB2，SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储，学员信息表，考勤表

非关系型数据库：NoSQL Not Only SQL

• Redis，MongoDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定。

DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据
• MySQL，数据库管理系统！

MySQL简介

MySQL是一个关系型数据库管理系统

前生：瑞典MySQL AB 公司开发

今世：属于 Oracle 旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

开源的数据库软件

体积小、速度快、总体拥有成本低，

大中小型网站的开发都选择 MySQL 作为网站数据库

官网：https://www.mysql.com/

安装MYSQL

win10安装

新建mysql配置文件ini

[mysqld]
port=3306
basedir=D:\kgc200x\app\mysql-8.0.18-winx64\
datadir=D:\kgc200x\app\mysql-8.0.18-winx64\data
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8mb4
[client]
port=3306
default-character-set=utf8mb4

启动管理员模式下的CMD,运行所有的命令

安装SQLyog

连接数据库

新建一个数据库school

每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看

新建一张表 student

连接数据库

命令行连接！

mysql -uroot -p		连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改用户密码

改密码8.0的语句     
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;	刷新权限

-------------------------------------------
所有的语句都使用；结尾
show databases;		查看所有的数据库

use 数据库名		 	切换数据库

show tabels;		 查看数据库中所有的表

mysql> describe student;	 显示数据库中指定表的信息
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(3)       | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> create database westos;	 	创建一个数据库

exit;	 退出连接

-- 单行注释（SQL的注释）

/*	（sql的多行注释）
hello
world
maomao
*/

linux安装

我使用二进制安装，已经编译完成，只需要初始化，就可以使用。

tar xf mysql-5.7.25-el7-x86_64.tar.gz -C /usr/local

mv mysql-5.7.25-el7-x86_64 mysql

groupadd  mysql

useradd -M -s /sbin/nologin mysql -g mysql

chown -R mysql:mysql mysql/

vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server_id=1
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
prompt=mysql [\\d]>

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

最后一排会出现一个随机密码 我们把密码复制下来
如：[Note] A temporary password is generated for root@localhost: JFw#)%:(&3yl

将mysql服务添加进系统服务
cd support-files/
cp mysql.server /etc/init.d/mysqld
ls /etc/init.d/mysqld

添加mysqld进入系统服务
chkconfig --add mysqld
chkconfig --list mysqld

service mysqld start
ps -aux |grep mysql

添加环境变量
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

登录Mysql（密码为初始化密码 之前复制的密码）
/usr/local/mysql/bin/mysql -uroot -p

进入mysql之后 修改密码
ALTER USER 'root'@'localhost' identified by '你的密码';

使用Navicat Premium连接数据库

需要对用户授权

grant all on *.* to 'root'@'%' identified by '123';
grant all on *.* to 'root'@'localhost' identified by '123';
flush privileges;

Mysql里面有四种语言 CRUD 增删改查！

DDL 数据库定义语言

DML 数据库操作管理语言

DQL 数据库查询语言

DCL 数据库控制语言

操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不区分大小写

操作数据库

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] name;

2. 删除数据库

DROP DATABASE [IF EXISTS] name;

3. 使用数据库

tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``

USE `school`
SELECT `user` FROM student

4. 查看数据库

SHOW DATABASES;		查看所有数据库

数据库的数据类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

字符串

• char 字符串固定大小的 0 - 255
• varchar 可变字符串 0 - 65535 常用的变量 String
• tinytext 微型文本 2^8 - 1
• text 文本串 2^16 -1 保存大文本

时间日期

• date YYYY-MM-DD 日期格式
• time HH：mm：ss 时间格式
• datetime YYYY-MM-DD HH：mm：ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数！也较为常用
• year 年份表示

null

• 没有值，未知
• 注意！！不要使用NULL进行运算，结果为NULL

数据库的字段属性（重要）

Unsigned：

• 无符号的整数
• 声明了该列不能声明为负数

zerofill：

• 0填充的
• 不足的位数，使用0来填充，举例：如果int（3）5 — 005

自增： AUTO_INCREMENT

• 通常理解为自增，自动在上一条记录的基础上 +1（默认）
• 通常用来设计唯一的主键 index，必须是整数类型
• 可以自定义设计主键的起始值和步长

非空： NUll not null

• 假设设置为not null，如果不给它赋值，就会报错！
• NUll，如果不填写值，默认就是null！

默认： DEFAULT

• 设置默认的值！
• sex，默认值为男，如果不指定该列的值，则会有默认的值！

扩展：

未来做项目用的，表示一个记录存在的意义！

id 主键

version 乐观锁

id_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

创建数据库表

目标：创建一个school数据库
 创建学生表(列，字段)	使用SQL 创建
 学号int 登陆密码varchar(20) 姓名，性别varchar(2)，出生日期(datatime)，家庭住址，email

 注意点，使用英文()，表的名称 和 字段 尽量使用 `` 括起来
 AUTO_INCREMENT 自增
 字符串使用 单引号括起来
 所有的语句后面加,(英文的)，最后一个不用加
 PRIMARY KEY 主键，一般一个表只有一个唯一的主键！

CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `gender` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE school   查看创建数据库的语句 
SHOW CREATE TABLE student 	 查看student数据表的定义语句
DESC student 	 显示表的结构

数据表的类型

– 关于数据库引擎
INNODB 默认使用
MYISAM 早些年使用的

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作：

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事务的处理，多表多用户操作

MYISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）

INNODB采用两个文件组织一张表
xxx.frm（存储格式的文件）
xxx.ibd（存储表中数据和索引的文件）

在物理空间存在的位置
所有的数据库文件都存在 data 目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

MySQL 引擎在物理文件上的区别

• innoDB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
• MYISAM对应文件

• *.frm 表结构的对应文件
• *.MYD 数据文件(data)
• *.MYI 索引文件(index)

设置数据库表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码（不支持中文！）

可以修改编码

在my.ini中配置默认的编码

character-set-server=utf8

修改和删除表

修改

-- 修改表	ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段	ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)  -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1)  -- 字段重命名

-- 删除表的字段 
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1

所有的创建和删除操作尽量加上判断，以免报错

注意点：

• `` 字段名，使用这个包裹
• 注释 – /**/
• sql 关键字大小写不敏感，建议大家写小写
• 所有符号全部用英文的

导入表操作

准备一个sql脚本

然后在当前目录连接mysql

mysql [(none)]>source student.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)


mysql [school]>desc school.student;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| studentno    | int(4)       | NO   | PRI | NULL    |       |
| loginpwd     | varchar(20)  | YES  |     | NULL    |       |
| studentname  | varchar(20)  | YES  |     | NULL    |       |
| sex          | tinyint(1)   | YES  |     | NULL    |       |
| gradeid      | int(11)      | YES  |     | NULL    |       |
| phone        | varchar(50)  | NO   |     | NULL    |       |
| address      | varchar(255) | NO   |     | NULL    |       |
| borndate     | datetime     | YES  |     | NULL    |       |
| email        | varchar(50)  | NO   | MUL | NULL    |       |
| identitycard | varchar(18)  | YES  | UNI | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
10 rows in set (0.00 sec)