MySQL学习01

---

1、初识MySQL

• 前端：页面展示。
• 后台：连接数据库。连接前端，控制视图跳转，给前端传递数据。
• 数据库：存取数据。

1.1、数据库分类

关系型数据库（SQL）：

• MySQL，Oracle，SQLserver
• 通过表与表之间，行与列之间的关系存储数据

非关系型数据库（No SQL）：

• redis ，mongodb，

DBMS(data base manage system)，数据库管理系统

• MySQL，数据库管理系统
• 数据库管理系统，能够科学有效的管理和维护数据。

1.2、安装MySQL

下载MySQL5.7.19window64位版本zip包

安装MySQL

• 解压zip包。使用zip安装包的优点是解压即按装，不会往注册表里填东西，能够卸载干净。
• 我的电脑-属性-高级-环境变量，添加MySQL环境变量
• 配置MySQL配置文件，my.ini

• my.ini文件内容

[mysqld]
basedir=D:\mysql-5.7.19\
datadir=D:\mysql-5.7.19\data\
port=3306
skip-grant-tables

• 启动管理员模式下的CMD，将路径切换到MySQL的bin目录下，mysqld -install命令安装MySQL。

C:\Windows\system32>cd D:\mysql-5.7.19\bin
D:\mysql-5.7.19\bin>mysqld -install

• 初始化数据库文件

D:\mysql-5.7.19\bin>mysqld --initialize-insecure --user=mysql

• 重新启动MySQL

D:\mysql-5.7.19\bin>net start mysql

• 命令行登录MySQL，密码为空

D:\mysql-5.7.19\bin>mysql -uroot -p

• 修改root用户密码，刷新权限

 update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
 flush privileges;

• 修改my.ini配置文件，把skip-grant-tables（跳过密码）注释掉。
  
• 重启MySQL即可正常使用

D:\mysql-5.7.19\bin>net stop mysql
D:\mysql-5.7.19\bin>net start mysql

1.3、navicate工具新建数据库

• 字符集选择utf8
• 排序规则选择utf8_general_ci
• 每一个navicate工具的操作，都对应一个SQL语句，可以在历史记录中查看到。

1.4、MySQL数据库的基本操作命令

查看所有的数据库

 show databases; --查看所有的数据
 +--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

切换数据库

use school;

查看数据库中所有的表

show tables;
+------------------+
| Tables_in_school |
+------------------+
| students         |
+------------------+

查看表中的所有信息

mysql> describe students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| age   | int(3)       | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

创建数据库

mysql> create database westos;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| westos             |
+--------------------+
6 rows in set (0.00 sec)

2、操作数据库

2.1、数据库操作

• SQL语句不区分大小写

创建数据库

create database if not exists westos;

检查数据库，如果不存在，则创建。

删除数据库

drop database if exists westos;

如果数据库存在，删除数据库。

使用数据库

use school;

查看所有的数据库

show databases;

2.1、数据库的列类型

数值

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

字符串

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

日期时间

• data，YYYY-MM-DD，日期格式
• time，HH:MM:SS，时间格式
• datetime，YYYY-MM-DD HH:MM:SS，常用的时间格式
• timestamp，时间戳，1970.1.1到现在的毫秒数，常用
• year，年份表示

null

• 空值
• 注意，不要使用NULL值进行运算

2.2、数据库创建表

create table if not exists `test1`(

`id` int(4) not null auto_increment comment '学号',
`pwd` varchar(20) not null default '123456' comment '密码',
`gender` varchar(4) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(20) default null comment '家庭住址',
`email` varchar(20) default null comment '邮箱',
primary key (`id`)

)engine=innodb charset=utf8

• 字段名用反单引号括起来，可以和谐SQL中的关键字
• 每行SQL语句用英文逗号结束，最后一行不加英文逗号
• auto_increment，自增
• comment，注释
• default null，默认位空

create table if not exists `test1`()

• 创建表时，在英文小括号中填写表中的各个属性

查看数据库中表的结构

desc test1

查看创建数据库的原始命令

show create database school

查看创建表的原始命令

show create table test1

2.3、数据库引擎

MySQL数据库引擎常用的有两种，Inodb,myisam

• InnoDB数据库在数据表中只有一个*.frm文件，和上一级目录中的ibdata1文件。

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

charset=utf8

my.ini配置文件中设置默认编码

添加配置

character-set-server=utf8

2.4、修改删除表

修改表的名字

alter table test rename as test007

将表的名字从“test"修改为”test007“

给表添加字段

alter table test007  add age int(4)

给表“test007”添加新的字段“age”

修改表中字段的数据类型

alter table test007 modify age varchar(4)

将字段“age”的数据类型修改为“varchar”

给表中的字段重命名

alter table test007 change age age1 int(4)

将表“test007”中的字段名“age"更改为”age1“

删除表中的字段

alter table test007 drop age1

删除表中的字段“age1”

删除表

drop table if exists test007

如果表存在，则删除表

3、MySQL数据管理

3.1、DML语言

数据库的作用

• 数据存储
• 数据管理

DML语言

• insert
• update
• delete

添加

INSERT INTO `grade`(`name`) VALUES('大四')

在表“grade”的“name”字段，添加值“大四”

INSERT INTO `grade`(`name`) VALUES('大三'),('大二')

在表“grade”的“name”字段，同时添加多个字段，添加值“大三”，“大二”

INSERT INTO `students`(`name`,`age`) VALUES('张三',6)

在表“students”，“name”，“age”字段，插入值。该方法是一次插入1条记录。

INSERT INTO `students`(`name`,`age`) VALUES('李四',2),('王五',3)...

在表“students”，“name”，“age”字段，插入值。该方法是一次插入多条记录。

修改

UPDATE `students` SET `name`='狂神' WHERE `id`=1

修改表 students，id=1的记录，name值为’狂神’

UPDATE `students` SET `name`='狂神'

如果不用 WHERE 条件，则修改所有的记录。

UPDATE `students` SET `name`='狂神',`age`=100 WHERE `id`=1

修改多个属性，中间用逗号隔开。

UPDATE `students` SET `name`='666' WHERE `name`='狂神' AND age=100

“ WHERE”，用来定位。
“AND”，用来添加多个条件

删除

DELETE FROM `students` WHERE id=1

从数据库中删除记录

truncate

TRUNCATE `students`

删除表students中的所有数据

delete 与truncate的区别

• truncate删除表后，自增列会归0
• delete删除表后，自增列接着上次自增

4、DQL查询数据

4.1、DQL

• Data Query Language数据库查询语言

查询表中所有信息

SELECT * FROM students

查询表中指定字段的信息

SELECT id,gender FROM test1

查询表test1中，id,gender字段的信息

给字段起别名

SELECT id AS 序号 FROM test1

给字段id取一个别名，叫做“序号”

运算操作

SELECT id+1 AS id自加1 FROM test1

id字段加1后，重新命名为“id自加1”，展示在查询结果中

4.2、where条件子句

where的使用

SELECT pwd,gender FROM test1 WHERE id>=2 AND id<3

查询test1表中pwd,gender字段，id大于等于2，小于3

模糊查询：比较运算符

SELECT  `name`,id FROM students WHERE `name`LIKE '刘%'

查询表students中，name中姓刘的记录

SELECT id,`name` FROM students WHERE addr is NOT NULL

查询add字段不是null的记录。

联表查询INNER JOIN

/*INNER JOIN*/
#联表查询
SELECT s.studentNo,studentName,studentResult 
FROM students AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

studentNo字段，是students表和result表共有的字段。

• As：重命名别名
• WHERE：条件过滤

students表：

result表：

联表查询LEFT JOIN

/*LEFT JOIN
WHERE ：等值查询
ON ：联接查询，判断的条件
*/
SELECT s.studentNo,studentName,studentResult 
FROM students AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo

联表查询，三表查询

/*
三表查询
表students与表result的共有字段是studentNo
表result与表`subject`的共有字段是subjectNo
*/
SELECT s.studentNo,studentName,studentResult,subjectName
FROM students s
LEFT JOIN result r
ON s.studentNo=r.studentNo
INNER JOIN `subject` sub
ON sub.subjectNo=r.subjectNo

4.3、分页和排序

按升序排序

/*
升序：ASC
将`studentResult`字段按升序排序
*/
SELECT `studentNo`,`studentResult`
FROM result
ORDER BY `studentResult` ASC

按降序排序

/*
升序：DESC
将`studentResult`字段按降序排序
*/
SELECT `studentNo`,`studentResult`
FROM result
ORDER BY `studentResult` DESC

4.4、子查询

5、MySQL函数

5.1、常用函数

常用函数

SELECT ABS(-8)#绝对值
SELECT CEILING(9.4)#向上取整
SELECT FLOOR(9.4)#向下取整
SELECT RAND()#返回一个0~1之间的随机数

#字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')#返回字符串的长度
SELECT CONCAT('我','爱','你们')#字符串拼接
SELECT LOWER('KuangShen')#把全部的字符串转为小写
SELECT UPPER('KuangShen')#把全部的字符串转为大写
SELECT REPLACE('狂神说坚持就能成功','坚持','努力')#替换字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6)#截取字符串，（源字符串，截取位置，截取长度）
SELECT REVERSE('狂神说坚持就能成功')#字符串反转

/*
查询姓周的同学，并且把周姓改为邹姓
*/
SELECT REPLACE(studentName,'周','邹') FROM students 
WHERE studentName LIKE '周%'

时间日期函数

#时间日期函数
SELECT CURRENT_DATE#获取当前日期
SELECT NOW()#获取当前日期，时间，包括时分秒

系统

#系统
SELECT SYSTEM_USER()#系统当前用户
SELECT USER()#系统当前用户
SELECT VERSION()#查看系统版本号

5.2、聚合函数（常用）

count函数，计算表格中记录的行数

SELECT COUNT(studentName) FROM students#COUNT(字段)，计算所有的行数，会自动忽略null值
SELECT COUNT(*) FROM students#COUNT(*)，计算所有的行数，不忽略null值
SELECT COUNT(1) FROM students#COUNT(1)，计算所有的行数，不忽略null值

平均值，最大值，最小值

SELECT SUM(studentResult) AS 总和 FROM result#计算总分
SELECT AVG(studentResult) AS 平均分 FROM result#计算平均分
SELECT MAX(studentResult) AS 最高分 FROM result#计算最高分
SELECT MIN(studentResult) AS 最低分 FROM result#计算最低分

5.3、数据库基本的MD5加密

MD5加密算法不可逆

MD5加密算法示例

#创建testmd5表
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

#明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
#md5加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
#插入值的时间就加密
INSERT INTO testmd5 VALUE(4,'xiaoming',MD5('123456'))

SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

5.4、select小结

6、事务

事务原则（ACID原则）：原子性，一致性，隔离性，持久性

7、索引

• 索引是帮助MySQL高效获取数据的数据结构。
• 索引在数据量小的表里用处不大，常应用在数据量比较大的表里。

7.1、索引的分类

• 主键索引，PRIMARY KEY
• 唯一索引，UNIQUE KEY
• 常规索引，KEY
• 常规索引
• 全文索引

查看表的索引

SHOW INDEX FROM students

给表格添加索引

#给表添加索引。索引名（字段名）
ALTER TABLE school.students ADD FULLTEXT INDEX `studentName`(`studentName`)

#EXPLAIN分析SQL的执行情况
EXPLAIN SELECT * FROM students

#给表添加索引。
#CREATE INDEX 索引名 ON 表名（字段）
CREATE INDEX addr_ind ON students(`addr`)

7.2、索引的原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表，不需要加索引
• 索引加在常用来查询的字段上

7.2、索引的数据结构

• innodb默认的数据结构是Btree
• hash类型的索引

8、权限管理和备份

8.1、权限管理

SQL命令操作

用户表：mysql.user

创建用户

#创建一个用户
CREATE USER kuangshen IDENTIFIED by '123456'

修改用户密码

#更改当前用户密码
SET PASSWORD = PASSWORD('123456')

#更改指定用户密码
SET PASSWORD FOR kuangshen = PASSWORD('111111')

用户名重命名

RENAME USER kuangshen TO kuangshen2

给用户授予所有权限

GRANT ALL PRIVILEGES ON *.* TO kuangshen2

查看指定用户权限

SHOW GRANTS FOR kuangshen2

#查看root用户权限
SHOW GRANTS FOR root@localhost

撤销指定用户权限

REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2

删除指定用户

DROP USER kuangshen2

8.2、MySQL数据库备份

命令行备份数据库

C:\Users\Zhouhao>mysqldump -hlocalhost -uroot -p123456 school students > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导出多张表

C:\Users\Zhouhao>mysqldump -hlocalhost -uroot -p123456 school students result > D:/b.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导出整个数据库

C:\Users\Zhouhao>mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导入数据库

• 在cmd命令行操作
• 首先要登录数据库
• 再导入

mysql> use school;
Database changed
mysql> source d:/a.sql