mysql学习全部过程

MySQL

一.数据库分类

1. 关系数据库：

   • MySQL，Oracle，Sql Server，DB2，SQLlite

   • 通过表与表之前，行与列之间的关系进行数据的存储，学院信息表，考勤表

2. 非关系型数据库：（NoSql）

   • Redis,MongDB

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

3. DBMS（数据库管理系统）

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

   • MySQL，数据库管理系统

二.MySQL简介

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

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

三.安装MySQL

1. 下载所需要的东西

   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

2. 安装步骤

   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. 连接上测试出现以下结果就安装好了

四.连接数据库

1. cmd连接数据库命名

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

2. SQL的分类

   1. DDL：数据库定义语言，比如插入操纵insert、删除操纵delete、修改操纵update

   2. DML：数据库操作语言，数据查询语言，用来对数据库表中的数据进行查询。select

   3. DQL：数据库查询语言，建立数据库、删除数据库、建立表的结构、修改表结构等这就需要使用数据定义语言。

   4. DCL：数据库控制语言，常常需要建立用户，用户有不同的角色和权限。所以我们要给用户去授权。

操作数据库

一. 操作数据库（了解）

1. 创建数据库

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

2. 删除数据库

   -- 删除数据库
   drop database text1;

3. 查询数据库

   -- 删除数据库
   drop database text1;

4. 查询数据库表数据

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

二. MySQL的数据类型

1. 数字类型

   

2. 字符串类型

   

3. 日期和时间类型

   

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

1. 无符号：

   • 声明了该列不能声明为负数

   • 无符号的整数

2. 0填充：

   • 0填充的

   • 不足的位数，使用0来填充，int(3) ---003

3. 自增：

   • 通常理解为自增，自动在上一条记录的基础上+1

   • 通常用来设计唯一的主键，index，必须是整数类型

   • 可以自定义设计主键自增的起源值和步长

4. 默认：

   • 设置默认的值

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

5. 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;

6. 格式

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

7. 数据引擎

   1. INNODB 默认使用

   2. MYISAM 早些年使用的

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

   3. 常规使用操作：

      • MYISAM 节约空间，速度较快

      • INNODB 安全性高，事务的处理，多表多个用户操作

   4. 物理空间存在的位置

      所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，本质还是文件的存储。

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

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

      • MYISAM：对应文件

        1. *.frm 表结构的定义文件

        2. *.MYD 数据文件（data）

        3. *.MYI 索引文件（index）

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

      CHARSET=utf8

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

      MySQL的默认编码是Latin1，不支持中文

   7. 修改

      1. 修改表名（了解）：

      alter table 原来的表名 rename [to] 新表名;

      2. 表结构中添加字段 ：

      alter table 表名 add 字段名 数据类型 约束;

      3. 修改字段的名称：

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

      4. 修改字段的类型：

         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';

   8. 删除

      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;

1. delete和truncate区别：

   • 本质上的区别就是truncate重新设置，自增列 计数器会归零

   • truncate 不会影响数据

   • delete与之相反

2. 了解delete删除的问题，重启数据库，现象

   • InnoDB==自增列会重1开始（存在内存当中的，断电即失）

   • MyISAM==继续从上一个自增量开始（存在文件中国的，不会丢失）

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

一.查询的语法

1）标准语法

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

from 表名 [as] 别名

where 条件语句

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

2）模糊查询

通配符：_ 1个字符长度

%:任意长度

二.分组查询

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. 事务原则

1. 原子性(Atomicity):要么成功，要么失败

2. 一致性（Consistency）：事务前后的数据完整性要保证一致

3. 持久性（Durability）：事务一点提交则不可逆，被持久化到数据库中

4. 隔离性（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.主键索引

1. 主键：某一个属性组能唯一标识一条记录

2. 特点：

   • 最常见的索引类型

   • 确保数据记录的唯一性

   • 确钉特定数据记录在数据库中的位置

4.唯一索引

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

2. 与主键索引的区别：

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

   • 唯一索引可能有多个

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

5.常规索引

1. 作用：快速定位特定数据

2. 注意：

   • index和key关键字都可以设置常规索引

   • 应加在查询找到条件的字段

   • 不宜添加太多常规索引，影响数据的插入，删除和修改操作

6.全文索引

1. 作用：快速定位特定数据

2. 注意：

   • 只能用于MyLSAM类型的数据表

   • 只能用于char,varchar,text 数据列类型

   • 适应大型数据集

7.拓展：测试索引

1. 创建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用户表'

    

2. 批量插入数据：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';

3. 索引测试

   -- 创建索引
   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模型图 , 确认需求信息的正确和完整.

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

1. 第一范式

   • 原子性：保证每一列不可分割

2. 第二范式

   • 前提：满足第一范式

   • 每张表只描述一件事情

3. 第三范式

   • 前提满足第一范式和第二范式

   • 第三范式需要确保数据表的每一列数据都和主键直接相关，不能间接相关

4. 规范性和性能问题

   • 关联表不得超过三张表，因为考虑到商业化的需求和目标，（成本，用户体验）

   • 在规范性能的问题的时候，需要适应的考虑一下规范性

   • 故意给某些表增加一些字段

   • 故意增加一些计算机列

代码实现

-- 添加数据库
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';