园龄：2年8个月粉丝：10 关注：1

mysql学习全部过程

MySQL

一.数据库分类

关系数据库：
- MySQL，Oracle，Sql Server，DB2，SQLlite
- 通过表与表之前，行与列之间的关系进行数据的存储，学院信息表，考勤表
非关系型数据库：（NoSql）
- Redis,MongDB
- 非关系型数据库，对象存储，通过对象的自身的属性来决定
DBMS（数据库管理系统）
- 数据库的管理软件，科学有效的管理我们的数据，维护和获取数据
- MySQL，数据库管理系统

二.MySQL简介

MySQL是一个关系型数据库管理系统
MySQL是一个关系型数据库管理系统**，**由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

三.安装MySQL

下载所需要的东西
1. Mysql数据库压缩包：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
2. 下载Navicat Premium 15：链接：https://pan.baidu.com/s/1HEXM1S5Ky9b-ZGi1O3JC-g 提取码：yyds
安装步骤
1. 下载后压缩包
2. 解压都自己想安装的目录，本人解压到D:\idea\Mysql\mysql-5.7.19
3. 添加环境变量：我的电脑右键->属性->高级系统设置->环境变量->系统变量->Path->编辑->新建（把你解压的MySQL路径复制进来）->选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹---D:\idea\Mysql\mysql-5.7.19\bin（这是我的）
4. 编辑 my.ini 文件 ,注意替换路径位置
```
[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables
```
5. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)
6. 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
7. 然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
8. 进入界面后更改root密码
```
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
```
9. 刷新权限
```
flush privileges;
```
10. 修改my.ini文件删除最后一句skip-grant-tables
11. 重启mysql即可正常使用
```
net stop mysql   //停止mysql
net start mysql  //开始MySQL
```
12. 连接上测试出现以下结果就安装好了

四.连接数据库

cmd连接数据库命名

show databases; --查看所有数据库
use school --切换数据库use 数据库名称
describe student; --查询这个student表信息
create database westors;--创建一个数据库
exit;--退出

SQL的分类
1. DDL：数据库定义语言，比如插入操纵insert、删除操纵delete、修改操纵update
2. DML：数据库操作语言，数据查询语言，用来对数据库表中的数据进行查询。select
3. DQL：数据库查询语言，建立数据库、删除数据库、建立表的结构、修改表结构等这就需要使用数据定义语言。
4. DCL：数据库控制语言，常常需要建立用户，用户有不同的角色和权限。所以我们要给用户去授权。

操作数据库

一. 操作数据库（了解）

创建数据库

-- 添加数据库
create database if not exists text1;

删除数据库
```
-- 删除数据库
drop database text1;
```
查询数据库
```
-- 删除数据库
drop database text1;
```

查询数据库表数据

-- 查看表中所有数据
select * from student;

二. MySQL的数据类型

数字类型
字符串类型
日期和时间类型

三. 数据库的字段属性（整数）

无符号：
- 声明了该列不能声明为负数
- 无符号的整数
0填充：
- 0填充的
- 不足的位数，使用0来填充，int(3) ---003
自增：
- 通常理解为自增，自动在上一条记录的基础上+1
- 通常用来设计唯一的主键，index，必须是整数类型
- 可以自定义设计主键自增的起源值和步长
默认：
- 设置默认的值
- sex默认为男，如果不指定该列的值，，则会默认的值

sql语句实现创建表

-- 创建students表
create table if not exists `students`(
 `id` int(4) not NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(30) not null default '匿名' comment '姓名',
 `pwd` varchar(20) not null default '123456' comment '密码',
 `sex` 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 AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

格式

create table [if not exists] 表名(
    字段名 列类型 [属性][索引][注释],
    字段名 列类型 [属性][索引][注释],
    ......
    字段名 列类型 [属性][索引][注释]
)[表类型][字符集设置][注解];

数据引擎

INNODB 默认使用
MYISAM 早些年使用的

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

常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多个用户操作
物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储。
MySQL引擎在物理文件上的区别
- innoDB：在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata1 文件
- MYISAM：对应文件
  1. *.frm 表结构的定义文件
  2. *.MYD 数据文件（data）
  3. *.MYI 索引文件（index）
设置数据库表的字符集编码
```
CHARSET=utf8
```
不设置的话，会是MySQL默认的字符集编码，（不支持中文）

MySQL的默认编码是Latin1，不支持中文
修改
1. 修改表名（了解）：
alter table 原来的表名 rename [to] 新表名;
1. 表结构中添加字段：
alter table 表名 add 字段名数据类型约束;
1. 修改字段的名称：
alter table 表名 change 原字段名新字段名数据类型 ;
1. 修改字段的类型：
  
  alter table 表名 modify 原字段名新的数据类型 ;
```
-- 修改表rename as重名为1
ALTER TABLE students1 RENAME AS students;
-- 给students加默认约束DEFAULT，默认值为123456把？
-- ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT '123456';
ALTER TABLE students MODIFY pwd VARCHAR(16) DEFAULT '123456';
```
删除
1. 删除字段：Alter table 表名 drop column 字段名;
```
-- 删除表
drop table if exists students;
```

MySQL数据库管理

一.外键（了解即可）

二.DML语言（全部记住）

三.添加：

insert into 表的名字(字段1，字段2,...,字段n) values(值1，值2,...,值n)

-- 创建hero表
CREATE TABLE `hero` (
  `id` int(11) NOT NULL AUTO_INCREMENT comment '学号',
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- 创建表插入数据insert into hero添加
INSERT INTO `hero`(`id`,`name`,`age`,`sex`,`price`,`type_id`) VALUES
(1,'李白',18,'男',18888,2),
(2,'孙尚香',17,'女',6888,4),
(3,'韩信',15,'男',NULL,2),
(4,'小乔',5,'女',NULL,3),
(5,'西施',5,'女',13888,3),
(7,'张飞',1,'男',13888,5),
(8,'花木兰',1,'女',NULL,1),
(9,'虞姬',16,'女',13888,4),
(10,'凯',NULL,NULL,13888,1),
(11,'孙策',NULL,NULL,NULL,NULL),
(12,'曹操',18,'男',NULL,2),
(13,'孙膑',5,NULL,NULL,5);
-- 添加数据到表里
INSERT INTO hero VALUES(14,'杨玉环',23,'女',18888,3);
insert into hero(name,price,type_id) VALUES('马克',18888,4);
insert into hero 
value(16,'露娜',18,'女',13888,2),
(17,'张良',18,'女',13888,3);

四.修改

update 表名 set 要修改的字段1=值1,字段2=值2，...，字段n=值n [where 条件表达式语句]

-- 修改数据把之前的马克修改成百里
--UPDATE 表名 SET 字段名='属性' WHERE 字段名='修改的属性';
UPDATE hero SET name='百里' WHERE name='马克';

五.删除

1）delete from tab_name [where 条件];//当不写where条件的时候会全部删除

2）truncate table tab_name;//删除表全部记录

1．规则：

1)要删除数据的时候要先删除子表中和外键关联的数据，然后才能删除主键表中的数据

2)删除的时候一般要写where条件，否则就会删除全部数据。

-- 删除数据到表里delete from 表名
delete from hero where `name`='张良';
-- 删除所以数据
delete from hero;
-- 删除所以数据
delete from hero where 1=1;
delete from hero where true;

delete和truncate区别：
- 本质上的区别就是truncate重新设置，自增列计数器会归零
- truncate 不会影响数据
- delete与之相反
了解delete删除的问题，重启数据库，现象
- InnoDB==自增列会重1开始（存在内存当中的，断电即失）
- MyISAM==继续从上一个自增量开始（存在文件中国的，不会丢失）

DQL数据的查询操作（最重点）

一.查询的语法

1）标准语法

select 字段1 [as 别名],字段2，...，字段n

from 表名 [as] 别名

where 条件语句

order by 排序的字段 asc|desc； //asc 升序 desc降序

2）模糊查询

通配符：_ 1个字符长度

%:任意长度

二.分组查询

分组统计的语法

select 字段1 [as 别名],字段2，...，字段n from 表名 [as] 别名

from 表名 as 别名

where 字句 //全表筛选

group by 分组字段

having 组内筛选字段

order by 排序字段

limit 指定查询的记录从那条到那条

聚合函数

一．常用函数函数介绍

1)max()求最大值

2)min()求最小值

3)count()求记录的条数

4)sum()求和

5)avg()平均值

-- ------------------六、聚合函数-------------------------
-- 创建表
CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
 
INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);

#查询emp表中记录数#
SELECT * FROM emp;
#查询emp表中有佣金的人数：
select * from emp WHERE comm is not null;
#查询emp表中月薪大于2500的人数#
select * from emp WHERE sal>2500;
#统计月薪与佣金之和大于2500元的人数：#
select COUNT(*) from emp where sal+IFNULL(comm,0)>2500;
#查询有佣金的人数，以及有领导的人数：#
select COUNT(comm) as 佣金人数,COUNT(mgr) as 领导人数 FROM emp;
#查询所有雇员月薪和#
select SUM(sal) from emp;
#查询所有雇员月薪和，以及所有雇员佣金和：#
select SUM(sal) as 月薪,SUM(comm) as 佣金 from emp;
#统计所有员工平均月薪：#
select AVG(sal) as 平均月薪 from emp;
#查询最高工资和低工资：#
select MAX(sal) as 最大工资,MIN(sal) as 最小工资 from emp;

事务

1. 事务原则

原子性(Atomicity):要么成功，要么失败
一致性（Consistency）：事务前后的数据完整性要保证一致
持久性（Durability）：事务一点提交则不可逆，被持久化到数据库中
隔离性（lsolation）:每个事务操作都会被隔离

2.隔离所导致的一些问题

脏读：指一个事务读取了另一个事务未提交的数据

不可重复读：在一个事务内读取表中的某一行数据，多次读取结构不同。

虚读：是指在一个事务内读取到别的事务插入的数据，导致前后读取不一致。

基本语法

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

测试

/*
课堂测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

索引

1.索引的作用

提高查询的速度
确保数据的唯一性
可以加速表与表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据索引时，可以显著减少和排序的时间
全文索引字段搜索优化

2.分类

主键索引（Primary key）
唯一索引（Unique）
常规索引（Index）
全文索引（FullText）

3.主键索引

主键：某一个属性组能唯一标识一条记录
特点：
- 最常见的索引类型
- 确保数据记录的唯一性
- 确钉特定数据记录在数据库中的位置

4.唯一索引

作用：避免同一个表中某数据列中的值重复

与主键索引的区别：

主键索引一个表中只有一个

唯一索引可能有多个

create table `Grade`(
-- 主键索引primary key
`GradeID` int(11) auto_increment primary key,
-- unique唯一索引
`Gradename` varchar(55) not null unique
)

5.常规索引

作用：快速定位特定数据
注意：
- index和key关键字都可以设置常规索引
- 应加在查询找到条件的字段
- 不宜添加太多常规索引，影响数据的插入，删除和修改操作

6.全文索引

作用：快速定位特定数据
注意：
- 只能用于MyLSAM类型的数据表
- 只能用于char,varchar,text 数据列类型
- 适应大型数据集

7.拓展：测试索引

创建app_user:

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

批量插入数据：100W

-- DROP FUNCTION IF EXISTS mock_data;
-- 创建100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO `app_user`(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';

索引测试

-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
SELECT * FROM app_user WHERE name = '用户9999';

MySQL备份

数据库备份必要性

保证重要数据不丢失
数据转移

MySQL数据库备份方法

mysqldump备份工具
数据库管理工具,如SQLyog
直接拷贝数据库文件和相关配置文件

规范数据库设计

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

为什么需要数据库设计

当数据库比较复杂时我们需要设计数据库

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

三大规范（面试有可能问）

第一范式
- 原子性：保证每一列不可分割
第二范式
- 前提：满足第一范式
- 每张表只描述一件事情
第三范式
- 前提满足第一范式和第二范式
- 第三范式需要确保数据表的每一列数据都和主键直接相关，不能间接相关
规范性和性能问题
- 关联表不得超过三张表，因为考虑到商业化的需求和目标，（成本，用户体验）
- 在规范性能的问题的时候，需要适应的考虑一下规范性
- 故意给某些表增加一些字段
- 故意增加一些计算机列

代码实现

-- 添加数据库
create database if not exists text1;
-- 删除数据库
drop database text1;
use shool;
-- 查看表中所有数据
select * from student;
-- 给students加默认约束DEFAULT，默认值为女把？
alter table students modify sex varchar(16) DEFAULT'女';
-- 查询表的结构
show columns from students;
-- 给id字段加自动编号
alter table students modify id int auto_increment;
-- 给name字段加唯一约束unique
alter table students add unique(name);
-- 给name添加非空约束not null
alter table students modify name varchar(13) not null;
-- 给表id加检查约束check
alter table students add constraint id check(id>10);
-- 创建hero表
CREATE TABLE `hero` (
  `id` int(11) NOT NULL AUTO_INCREMENT comment '学号',
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- 创建表插入数据insert into hero添加
INSERT INTO `hero`(`id`,`name`,`age`,`sex`,`price`,`type_id`) VALUES
(1,'李白',18,'男',18888,2),
(2,'孙尚香',17,'女',6888,4),
(3,'韩信',15,'男',NULL,2),
(4,'小乔',5,'女',NULL,3),
(5,'西施',5,'女',13888,3),
(7,'张飞',1,'男',13888,5),
(8,'花木兰',1,'女',NULL,1),
(9,'虞姬',16,'女',13888,4),
(10,'凯',NULL,NULL,13888,1),
(11,'孙策',NULL,NULL,NULL,NULL),
(12,'曹操',18,'男',NULL,2),
(13,'孙膑',5,NULL,NULL,5);
-- 添加数据到表里
INSERT INTO hero VALUES(14,'杨玉环',23,'女',18888,3);
insert into hero(name,price,type_id) VALUES('马克',18888,4);
insert into hero 
value(16,'露娜',18,'女',13888,2),
(17,'张良',18,'女',13888,3);
-- 删除数据到表里
-- delete from 表名 where 条件1;
delete from hero where `name`='张良';
-- 删除所以数据
delete from hero;
-- 删除所以数据
delete from hero where 1=1;
delete from hero where true;
-- 修改数据把之前的马克修改成百里
-- UPDATE 表名 SET 需要修改的条件1 WHERE 想修改成的条件;
UPDATE hero SET name='百里' WHERE name='马克';
-- 查
-- 查询所以信息
select * from hero;
-- 查询所以男性
SELECT * FROM hero WHERE sex='男';
-- 查询hero这个表中name,id,price,sex条件sex=男
SELECT name,id,price,sex FROM hero WHERE sex='男';
SELECT *FROM hero WHERE name='百里';
SELECT *FROM hero WHERE name='孙尚香';
-- ------------------常用查询---------------------
-- 条件查询
-- 查询年龄大于5，小于20
SELECT *FROM hero WHERE age>5 and age<20;
SELECT * FROM hero WHERE age BETWEEN 5 AND 20;
-- 查询年龄为5，16，17的数据
SELECT * FROM hero WHERE age IN(5,16,17);
-- 聚合函数
-- MAX与MIN最大最小
SELECT min(age) FROM hero;
SELECT max(age) FROM hero;
-- 查询年龄最小的所以信息
SELECT *FROM hero WHERE age=(SELECT min(age) FROM hero);
-- sum函数
-- 年龄总和
SELECT sum(age) FROM hero;
-- count函数
SELECT COUNT(*) FROM hero;
-- AVG函数平均数
SELECT AVG(age) FROM hero WHERE sex='男';
-- 排序查询
-- 根据年龄排序
SELECT * FROM hero ORDER BY age;
-- 根据type_id排序
SELECT * FROM hero order by type_id;
-- 根据年龄倒序
SELECT * from hero ORDER BY age DESC;
-- 模糊查询
-- 查询姓名为三个字的英雄(三个下划线)
SELECT * FROM hero WHERE name LIKE '___';
-- 查询姓李的英雄
SELECT * FROM hero WHERE name LIKE '李%';
-- 查询姓名至少为三个子的英雄
SELECT * FROM hero WHERE name LIKE '___%';
-- 分页查询
SELECT * FROM hero LIMIT 3,2;
-- 新建type表
CREATE TABLE `type` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(20) DEFAULT NULL,
  `route` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- 插入数据
INSERT INTO type(tid,tname,route) values
(1,'战士','上单'),
(2,'刺客','打野'),
(3,'法师','中路'),
(4,'射手','下路'),
(5,'辅助','下路');
-- ---------------多表操作-------------------
select * from hero h,type t;
-- h.type_id=t.tid第一个表id找第二个表id;
SELECT * FROM hero h,type t WHERE h.type_id=t.tid;
-- hero内部连接type
SELECT * FROM hero h INNER JOIN type t on h.type_id=t.tid;
-- 左外连接
SELECT * FROM hero h LEFT JOIN type t ON h.type_id=t.tid;
-- 右外连接
SELECT * FROM hero h RIGHT JOIN type t on h.type_id=t.tid;
-- 全连接UNION结合
SELECT * FROM hero h LEFT JOIN type t ON h.type_id=t.tid
UNION
SELECT * FROM hero h RIGHT JOIN type t on h.type_id=t.tid;
-- 用id排顺序连表查询
SELECT * FROM hero h LEFT JOIN type t ON h.type_id=t.tid ORDER BY id;
-- 建立表booktype
create table if not exists booktype(
typeid int(11) primary key auto_increment,
typename varchar(50)
);
-- 建立book表
create table if not exists `book`(
 `id` int(10) auto_increment,
 `bookname` varchar(50) not null,
 `author` varchar(50) null,
 `price` float,
 `pubname` varchar(50) default '出版不详',
 `pubtime` date,
 `typeid` int(11),
  primary key(id),
--  foregin key(外键字段名) references 主键表(主键)
    FOREIGN KEY(typeid) REFERENCES booktype(typeid)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 删除表
-- 要先删除子表，再删除主表，因为子表在参照主表，有外键约束。
drop table book;
drop table booktype;
#将book表的名字给出book1#
alter table book1 rename book;
#将book表添加新的字段remark varchar 20#
alter table book add remark varchar(20) null;
#将book表的字段remark 修改成remark1#
alter table book change remark remark1 varchar(20) null;
#将book表的字段remark1的类型改成int#
alter table book modify remark1 int;
#将表book的字段remark1删除#
alter table book drop column remark;
#将表book的字段remark添加#
alter table book add remark int;
INSERT INTO booktype(typeid,typename) VALUES(NULL,'少儿');
insert into booktype(typename) values('编程类');
insert into booktype values(null,'财经类');
insert into book(id,bookname,author,price,pubname,pubtime,typeid)
values(null,'一千零一页','无名',35.8,default,NOW(),1);
insert into book(bookname,author,price,pubtime,typeid)
values('西游记','无名',68.8,NOW(),1);
insert into book(bookname,author,price,pubtime,typeid)
values('三国演义','罗贯中',58.8,NOW(),NULL);
-- 45不在booktype表主键中，所以插入失败
INSERT INTO book(`bookname`,`author`,`price`,`pubtime`,`typeid`)
VALUES('水浒传','施耐庵',48.8,NOW(),45);
-- -------------三、数据的修改操作---------------
-- update 表名 set 要修改的字段1=值1,字段2=值2，...，字段n=值n [where 条件表达式语句]
#把表book中的id=1的记录的pubname的值修改为湖北少儿出版社#
update book set pubname='湖北少儿节目' WHERE id=1;
#把表book中的id=1的记录中的出版时间改成2015-09-18#
update book set pubtime='2015-09-18' WHERE id=1;
#把表中的书籍的价格增加10元，同时把现有书籍的出版社全部改成中国少年出版社#
update book set price=price+10,pubname='少年出版社' WHERE id=1;
#把book中的price<60的增加5块#
update book set price=price+5 WHERE price<60;
-- -----------------四、数据的删除操作----------------
-- 1）delete from tab_name  [where 条件];//当不写where条件的时候会全部删除 
-- 2）truncate table tab_name;//删除表全部记录
#删除子表中的typeid=1的记录，然后再删除主表typeid=1的记录，此时主外键约束存在#
-- 先删子表，在删主表，有约束
DELETE from book WHERE typeid=1;
delete from booktype WHERE typeid=1;
#删除book表中的全部记录,然后再插入一条数据，观察id的值#
DELETE FROM book;
INSERT INTO book(`id`,`bookname`,`author`,`price`,`pubname`,`pubtime`,`typeid`)
VALUES(NULL,'一千零一夜','无名',35.8,DEFAULT,NOW(),2);
#采用第2种方式删除表中全部记录，再插入一条记录，观察id自动增长列的值#
truncate book;
INSERT INTO book(`id`,`bookname`,`author`,`price`,`pubname`,`pubtime`,`typeid`)
VALUES(NULL,'一千零一夜','无名',35.8,DEFAULT,NOW(),2);
-- ------------------五、数据的查询操作------------------
-- 创建表，插入数据
CREATE TABLE tab_student( 
s_number CHAR(6), 
s_name VARCHAR(20), 
s_age INT, 
s_gender VARCHAR(10) 
) CHARACTER SET utf8 COLLATE utf8_general_ci; 
INSERT INTO tab_student VALUES('S_1001', 'liuYi', 35, 'male'); 
INSERT INTO tab_student VALUES('S_1002', 'chenEr', 15, 'female'); 
INSERT INTO tab_student VALUES('S_1003', 'zhangSan', 95, 'male'); 
INSERT INTO tab_student VALUES('S_1004', 'liSi', 65, 'female'); 
INSERT INTO tab_student VALUES('S_1005', 'wangWu', 55, 'male'); 
INSERT INTO tab_student VALUES('S_1006', 'zhaoLiu', 75, 'female'); 
INSERT INTO tab_student VALUES('S_1007', 'sunQi', 25, 'male'); 
INSERT INTO tab_student VALUES('S_1008', 'zhouBa', 45, 'female'); 
INSERT INTO tab_student VALUES('S_1009', 'wuJiu', 85, 'male'); 
INSERT INTO tab_student VALUES('S_1010', 'zhengShi', 5, 'female'); 
INSERT INTO tab_student VALUES('S_1011', 'xxx', NULL, NULL); 
#查询tab_student表中的全部记录#
select * from tab_student;
#查询tab_student表中的全部记录,把字段用中文名表示
select s_number as 学号,
s_name as 姓名,
s_age as 年龄,
s_gender 性别
from tab_student;
#说明：字段的别名可以用as也可以不写as，别名可以用单引号也可以不用。#
#查询性别为女，并且年龄小于50的记录#
SELECT * FROM tab_student WHERE s_gender='female' AND s_age<50;
#查询学号为S_1001，或者姓名为liSi的记录：#
SELECT * FROM tab_student WHERE s_number='S_1001' OR s_name='liSi';
#查询学号为S_1001，或者姓名为liSi且性别为female的记录：#
select * from tab_student WHERE s_number='S_1001' or s_name='liSi' and s_gender='female';
#查询学号为S_1001，S_1002，S_1003的记录：#
select * from tab_student where s_number='S_1001' OR s_number='S_1002' OR s_number='S_1003';
SELECT * FROM tab_student WHERE s_number IN('S_1001','S_1002','S_1003');
#查询学号不是S_1001，S_1002，S_1003的记录：#
SELECT * FROM tab_student WHERE s_number NOT IN('S_1001','S_1002','S_1003');
#查询年龄为null的记录：
select * from tab_student where s_age IS null;
#查询年龄不为null的学生记录#
select * from tab_student where s_age IS NOT null;
#查询年龄在20到40之间的学生记录：
select * from tab_student WHERE s_age between 20 AND 40;
#查询姓名由5个字母构成的学生记录：#
select * from tab_student WHERE s_name LIKE '_____';
#查询姓名由5个字母构成，并且第5个字母为“i”的学生记录 #
select * from tab_student WHERE s_name LIKE '____i';
#查询姓名以“z”开头的学生记i录：#
select * from tab_student WHERE s_name LIKE 'z%';
#查询姓名中第2个字母为“i”的学生记录：#
select * from tab_student WHERE s_name LIKE '_i%';
#查询姓名中包含“a”字母的学生记录： 
select * from tab_student where s_name like '%a%';
#查询所有学生记录，按年龄升序排序： 
select * from tab_student order by s_age asc;
#查询所有学生记录，按年龄降序排序：
select * from tab_student order by s_age DESC;

-- ------------------六、聚合函数-------------------------
-- 创建表
CREATE TABLE `dept`(
    `deptno` INT(2) NOT NULL, 
    `dname` VARCHAR(14),
    `loc` VARCHAR(13),
    CONSTRAINT pk_dept PRIMARY KEY(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS'); 
INSERT INTO dept VALUES (30,'SALES','CHICAGO');  
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

CREATE TABLE `emp` (
    `empno` int(4) NOT NULL PRIMARY KEY,
    `ename` VARCHAR(10),  
    `job` VARCHAR(9),  
    `mgr` int(4),  
    `hiredate` DATE,  
    `sal` float(7,2),  
    `comm` float(7,2),  
    `deptno` int(2),
    CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); 
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); 
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); 
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); 
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); 
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20); 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-07',5000,NULL,10); 
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); 
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20); 
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); 
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); 
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

CREATE TABLE `salgrade` (  
    `grade` int, 
    `losal` int,  
    `hisal` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT INTO SALGRADE VALUES (1,700,1200); 
INSERT INTO SALGRADE VALUES (2,1201,1400); 
INSERT INTO SALGRADE VALUES (3,1401,2000); 
INSERT INTO SALGRADE VALUES (4,2001,3000); 
INSERT INTO SALGRADE VALUES (5,3001,9999);

#查询emp表中记录数#
SELECT * FROM emp;
#查询emp表中有佣金的人数：
select * from emp WHERE comm is not null;
#查询emp表中月薪大于2500的人数#
select * from emp WHERE sal>2500;
#统计月薪与佣金之和大于2500元的人数：#
select COUNT(*) from emp where sal+IFNULL(comm,0)>2500;
#查询有佣金的人数，以及有领导的人数：#
select COUNT(comm) as 佣金人数,COUNT(mgr) as 领导人数 FROM emp;
#查询所有雇员月薪和#
select SUM(sal) from emp;
#查询所有雇员月薪和，以及所有雇员佣金和：#
select SUM(sal) as 月薪,SUM(comm) as 佣金 from emp;
#统计所有员工平均月薪：#
select AVG(sal) as 平均月薪 from emp;
#查询最高工资和低工资：#
select MAX(sal) as 最大工资,MIN(sal) as 最小工资 from emp;
-- -----------------复杂查询-----------------
-- -----------------分组查询-----------------
#查询每个部门的部门编号以及每个部门的人数#
select deptno as 部门编号,COUNT(empno) as 部门人数 from emp GROUP BY deptno;
#查询每个部门的部门编号以及每个部门工资大于1500的人数#

select deptno as 部门编号,COUNT(empno) as 部门人数 FROM emp
WHERE sal>1500 GROUP BY deptno;
-- 连表查询
SELECT * FROM emp e INNER JOIN dept d on e.deptno=d.deptno;



-- -----------------------网课学习-------------------------
-- 创建students表
create table if not exists `students`(
 `id` int(4) not NULL AUTO_INCREMENT COMMENT '学号',
 `name` varchar(30) not null default '匿名' comment '姓名',
 `pwd` varchar(20) not null default '123456' comment '密码',
 `sex` 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 AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- 查询创建数据库sql语句
SHOW CREATE DATABASE shool;
-- 查询students数据表的定义语句
SHOW CREATE TABLE students;
-- 查看表的结构
DESC students;
-- 修改表rename as重名为1
ALTER TABLE students1 RENAME AS students;
-- 给students加默认约束DEFAULT，默认值为123456把？
-- ALTER TABLE 表名 MODIFY 字段名 数据类型 DEFAULT '123456';
ALTER TABLE students MODIFY pwd VARCHAR(16) DEFAULT '123456';
-- 删除表
drop table if exists students;
-- TRUNCATE清空student表自动增量会清空
TRUNCATE student;
delete from student where name='李永辉';
-- ----------------测试MD5 加密---------------------
create table `testmd5`(
`id` int(4) not null,
`name` varchar(20) not null,
`pwd` varchar(20) not null,
primary key(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 明文加密
insert into testmd5 values(1,'张三','123456'),(2,'历史','123456'),(3,'王五','123456');
-- 加密
update testmd5 set pwd=MD5(pwd) WHERE id=1;
select * from testmd5;
-- 插入时加密
insert into testmd5 values(4,'李永辉',MD5('123456'));
select * from testmd5 where `name`='李永辉' AND `pwd`=MD5('123456');
create table `Grade`(
-- 主键索引primary key
`GradeID` int(11) auto_increment primary key,
-- unique唯一索引
`Gradename` varchar(55) not null unique
)

-- 索引
-- 显示所有索引信息
show INDEX FROM student;
create table `result`(
INDEX `ind`(`studentNo`,`subjectNo`) --创建表时添加
)
create table `result` add index `ind`(`studentNo`,`subjectNo`);
-- 增加一个全文索引（索引名） 列名
ALTER table shool.student ADD FULLTEXT index `name`(`name`);
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- DROP FUNCTION IF EXISTS mock_data;
-- 创建100万条数据
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO `app_user`(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';

-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
SELECT * FROM app_user WHERE name = '用户9999';

上一篇安装MySQL,破解Navicat Premium 15

下一篇JavaScript错误1

本文作者：菜鸡前来

本文链接：https://www.cnblogs.com/lyhidea/p/16837688.html

posted @ 2022-10-28 22:08 菜鸡前来阅读(74) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

菜鸡前来

mysql学习全部过程

MySQL

一.数据库分类

关系数据库：

非关系型数据库：（NoSql）

DBMS（数据库管理系统）

二.MySQL简介

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

三.安装MySQL

下载所需要的东西

安装步骤

下载后压缩包

解压都自己想安装的目录，本人解压到D:\idea\Mysql\mysql-5.7.19

添加环境变量：我的电脑右键->属性->高级系统设置->环境变量->系统变量->Path->编辑->新建（把你解压的MySQL路径复制进来）->选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹---D:\idea\Mysql\mysql-5.7.19\bin（这是我的）

编辑 my.ini 文件 ,注意替换路径位置

启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)

再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）

进入界面后更改root密码

刷新权限

修改my.ini文件删除最后一句skip-grant-tables

重启mysql即可正常使用

连接上测试出现以下结果就安装好了

四.连接数据库

cmd连接数据库命名

SQL的分类

操作数据库

一. 操作数据库（了解）

二. MySQL的数据类型

三. 数据库的字段属性（整数）

MySQL数据库管理

一.外键（了解即可）

二.DML语言（全部记住）

三.添加：

四.修改

五.删除

DQL数据的查询操作（最重点）

一.查询的语法

二.分组查询

聚合函数

一．常用函数函数介绍

事务

1. 事务原则

2.隔离所导致的一些问题

索引

1.索引的作用

2.分类

3.主键索引

4.唯一索引

5.常规索引

6.全文索引

7.拓展：测试索引

MySQL备份

规范数据库设计

为什么需要数据库设计

设计数据库步骤

为什么需要数据库设计

糟糕的数据库设计 :

良好的数据库设计 :

软件项目开发周期中数据库设计 :

三大规范（面试有可能问）

代码实现

公告

常用链接

随笔分类

随笔档案

阅读排行榜

推荐排行榜