MySQL基础

 1.解压安装包  到一个没有中文的路径中
 2.配置环境变量 D:\Program Files\mysql-8.0.22-winx64\mysql-8.0.22-winx64\bin
 3.以管理员身份运行cmd
 4.进入到 D:\Program Files\mysql-8.0.22-winx64\mysql-8.0.22-winx64\bin
 5.初始化 mysqld --initialize-insecure
 6.安装mysqld install
 7.启动net start mysql
 在命令行中连接mysql,为mysql root账号设置密码
     cmd
     mysql -uroot -p  回车
     输入密码  无需输入  直接回车;
     select version();（mysql命令）   查看mysql数据库的版本 
     show databases;  显示所有的数据库
     use mysql;  选中mysql数据库,接下来的操作都是对此库进行的.  
     ALTER USER  'root'@'localhost'  IDENTIFIED WITH mysql_native_password BY  '密码';
     刷新指令FLUSH PRIVILEGES;   
     安装一个可视化客户端( SQLyog / Navicat 这只是一个提供了操作界面的工具,实际上是对数据库中数据操作)

3、MySQL语法规范

MySQL语法规范

不区分大小写，建议关键字大写，表名、列名小写

每句话用;

注释

单行注释：#注释文字

单行注释： -- 注释文字（要有空格）

多行注释： /* 注释文字 */

4、SQL

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统.

SQL优点：

不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL

简单易学

实际上强有力的语言，灵活使用可以进行非常复杂和高级的数据库操作

三、SQL——DDL

1、DDL概念

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言。

常用的语句：create ,alter,drop,rename

CREATE DATABASE IF NOT EXISTS schooldb CHARSET utf8; #创建数据库
DROP DATABASE IF EXISTS schooldb; #删除数据库
ALTER DATABASE schooldb CHARSET utf8; #改变数据库的编码

2、数据库存储数据的特点

将数据放到表中，表再放到库中

一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。

表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的设计。

表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列类似java中的”属性”

表中的数据是按行存储的，每一行类似于Java中的“对象”。

3、数据库表的基本概念

（1）、数据表
        表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。表的最简单形式是由行和列组成，分别都包含着数据。每个表都有一个表头和表体，表头定义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。
（2）、记录
        记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。
（3）、字段
        字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。数据表的一列包含了特定字段的全部信息。

4、设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

表名(表信息)

表中的字段

字段的数据类型和长度

哪些约束

-- 创建基本的表
/*
VARCHAR和CHAR类型在java中表示都是字符串
VARCHAR(6) 表示字符串 可变长度的字符串 （6) 最大上限是6个字符，如果实际存储量4个字符，那么就占4个字符空间
       varchar类型长度是有上限的，65535个字节，utf8一个汉字占3个字节
CHAR(1) 表示字符串 (6) 如果实际存储了4个字符，那么长度仍为6个字符
date 日期  年月日
FLOAT(3,2)  3 表示总长度，2表示小数点后面的长度
datatime 年月日  时分秒`mysql`
*/
CREATE TABLE student(
    id INT,
    num INT,
    sname VARCHAR(6),
    gender CHAR(1),
    birthday DATE,
    height FLOAT(3,2),
    phone VARCHAR(11),
    address VARCHAR(30),
    register_time DATETIME
)

DROP TABLE student; #删除表
/*
    PRIMARY KEY 设置主键约束了，一个表中只能有一个主键列，主键不能重复，不能为空的
      AUTO_INCREMENT设置主键自动增长 从1开始，建议主键使用自增
      COMMENT 注释备注
      DEFAULT 默认 
    NOT NULL  不能为空约束,一个表中可以有多个列不能为空
    UNIQUE  唯一性约束，不能重复，一个表中可以有多个列不能重复
    检查约束 设置条件
    外键约束
*/
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生主键',
    num INT NOT NULL UNIQUE COMMENT '学号',
    sname VARCHAR(6) NOT NULL COMMENT '姓名',
    gender CHAR(1) NOT NULL DEFAULT '男',    #默认男
    birthday DATE,
    height FLOAT(3,2) CHECK(height>1.0),
    phone VARCHAR(11) UNIQUE,
    address VARCHAR(30),
    register_time DATETIME  
) 
DROP TABLE student;-- 删除表结构，表结构删除后，数据也就不存在了
RENAME TABLE student TO stu;-- 修改表名
RENAME TABLE stu TO student;
CREATE TABLE stu LIKE student; -- 复制表结构

四、SQL——DML

数据操纵语言DML（Data Manipulation Language）

-- DML 数据操作语言  新增 修改 删除
/*
方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);
方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;
方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2….., 值n);
方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)
*/
-- 主键自增不需要插入，自动生成  register_time当前时间，可以通过mysql中的函数获得
INSERT INTO student(num,sname,gender,birthday,height,phone,address,register_time)
    VALUES(102,'李四','女','2000-10-1',1.80,'15333333','西安',NOW())

INSERT INTO student SET num=103,sname='王五';
-- 批量插入数据
INSERT INTO student(num,sname) VALUES(104,'赵柳1'),(105,'赵柳2'),(106,'赵柳3');

INSERT INTO stu(num,sname) SELECT num,sname FROM student;//相当于备份表数据
-- 修改表数据，修改时需要注意条件
UPDATE student SET sname='lili',gender='女',birthday='2022-5-8' WHERE num=100
-- 删除表数据 删除数据也需要注意条件
DELETE FROM student WHERE num = 106

DELETE FROM stu; -- 也是删除整张表数据，是DML语句 ，只是删除数据，如果重新添加新的数据，会继续接着之前的数据进行（比如自增主键，之前6条数据，删除后，再添加数据，主键会从7开始）

-- 从表结构上对数据进行清空  属于DDL操作，如果再添加新的数据，重新开始（比如自增主键，之前6条数据，删除后，再添加数据，主键会从1开始）
TRUNCATE TABLE student;

五、SQL——DQL

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

/*
    DQL 查询数据
*/
-- 基本查询语法 select 查询列表 from 表名;
-- select 列 from 表名 where 条件 order by(排序) group by(分组) having limit
-- 嵌套子查询 关联查询
-- 查询出来的结果是虚拟的表格（备份），不会改变的，不会影响到数据库中的数据
SELECT id,num,sname,10,SUBSTR(sname,1,2) FROM student;

-- 查询特定列 实际中是建议的(会提高查询的效率)
SELECT num,sname,gender FROM student;
-- * 表示查询所有的列，在需要查询所有列时使用，一般不建议使用
SELECT * FROM student;
-- 去除重复行（指的是所有列的值都相同）实际多用在关联查询时，由于关联条件导致一些重复数据
SELECT DISTINCT sname,gender FROM student;
-- 算数运算 + - * / 只能用做算数运算
SELECT num,sname,height+0.5,height+num FROM student;
-- + 不能用作字符串连接
SELECT sname+gender FROM student;
-- 字符串连接函数 CONCAT（） 定义别名 as 别名 as可以省略
SELECT CONCAT(sname,':',gender) FROM student;

-- 单行函数 此函数会对查询结果的每行进行处理

/*
length()：获取参数值的字节个数
char_length()获取参数值的字符个数
concat(str1,str2,.....)：拼接字符串
upper()/lower()：将字符串变成大写/小写
substring(str,pos,length)：截取字符串 位置从1开始
instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0
trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)
lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
replace(str,old,new)：替换，替换所有的子串
*/
-- length(列名) 以字节为单位
SELECT num,sname,LENGTH(sname)  FROM student;
-- char_length(列名) 以字符为单位 
SELECT num,sname,CHAR_LENGTH(sname)  FROM student;
-- upper()/lower() 将字符串变成大写/小写
SELECT num,sname,UPPER(sname),LOWER(sname) FROM student;
-- substring(列名,开始位置从1开始,截取的长度)：截取字符串 位置从1开始
SELECT num,sname,SUBSTRING(sname,2,2) FROM student;
-- instr(列名,查找的子串) 类似于Java中的indexof：返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(sname,'i') FROM student;
-- trim(str) 去掉字符串前后的空格或子串
SELECT TRIM(" abc ") FROM student;
SELECT CHAR_LENGTH(TRIM(" abc ")) FROM student;
SELECT TRIM('a' FROM "abca") FROM student;
-- lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度
-- rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度
SELECT num,LPAD(sname,5,"a"),RPAD(sname,5,'b') FROM student;


/*
逻辑处理  数学函数 日期函数 分组函数（统计函数 聚合函数）
case when 条件 then 结果1 else 结果2 end; 可以有多个when
ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回
原本的值
if函数：if else的 效果 if(条件,结果1,结果2)
*/
SELECT num,
       sname,
       height,
       (CASE WHEN height>=1.70 THEN "高个子" ELSE '非高个子' END) height
       FROM student
SELECT num,
       sname,
       height,
       (CASE WHEN height>=1.80 THEN "大高个子" 
             WHEN height>=1.70 THEN "高个子"
             ELSE '非高个子' END) height
       FROM student
SELECT num,sname,IF(height>=1.70,'高个子','非高个子') FROM student
SELECT num,sname,IFNULL(height,'身高信息未录入') FROM student

-- 数学函数
/*
round(数值)：四舍五入
ceil(数值)：向上取整，返回>=该参数的最小整数
floor(数值)：向下取整，返回<=该参数的最大整数
truncate(数值,保留小数的位数)：截断，小数点后截断到几位
mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负
rand()：获取随机数，返回0-1之间的小数
*/
SELECT num,sname,TRUNCATE(height,1) FROM student
-- mod(被除数,除数)
SELECT num,sname,MOD(num,3) FROM student
-- rand()：获取随机数，返回0-1之间的小数
SELECT RAND() FROM student;


/*
日期函数
now()：返回当前系统日期+时间
curdate()：返回当前系统日期，不包含时间
curtime()：返回当前时间，不包含日期
可以获取指定的部分，年、月、日、小时、分钟、秒
YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期)
SECOND(日期)
str_to_date：将日期格式的字符转换成指定格式的日期
date_format：将日期转换成字符串
datediff(big,small)：返回两个日期相差的天数
*/
-- 获取系统时间
SELECT NOW(),CURDATE(),CURTIME() FROM student;

SELECT num,sname,YEAR(birthday),MONTH(birthday) FROM student;

SELECT STR_TO_DATE('2023-6-9','%Y-%m-%d') FROM student;
SELECT num,sname,DATE_FORMAT(birthday,'%Y-%m') FROM student;
-- datediff(big,small)：返回两个日期相差的天数
SELECT num,sname,DATEDIFF(CURDATE(),'2023-6-1') FROM student;


-- 分组函数
/*
sum 求和、avg 平均值、max 最大值、min 最小值、count 计数（非空）
1.sum，avg一般用于处理数值型max，min，count可以处理任何类型
2.以上分组函数都忽略null值
3.count函数的一般使用count（*）用作统计行数
4.和分组函数一同查询的字段要求是group by后的字段
*/
SELECT SUM(height) FROM student;
SELECT AVG(height) FROM student;
-- 统计表中记录的总数
SELECT COUNT(*) FROM student; -- 结果为6
SELECT COUNT(height) FROM student; -- 结果为5，结果为空不统计 
-- max(数值/字符串) 字符使用字符编码比较大小
SELECT MAX(height),MAX(sname),MIN(height),MIN(sname) FROM student;
--  查询条件
/*
使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
*/
-- where后面 = 是比较是否相等
SELECT * FROM student WHERE gender = '男';
SELECT * FROM student WHERE gender != '男'; 
-- <>   也是不等于
SELECT * FROM student WHERE gender <> '男';
SELECT * FROM student WHERE gender = '男' AND height > 1.60 AND address = '西安';
SELECT * FROM student WHERE gender = '男' OR height > 1.60 ;
-- 获取满足in条件的
SELECT * FROM student WHERE num IN(101,102,103);
-- 不满足in中的条件
SELECT * FROM student WHERE num NOT IN(101,102,103);
-- 找为空的
SELECT * FROM student WHERE phone IS NULL;
-- 找不为空的
SELECT * FROM student WHERE phone IS NOT NULL;
SELECT * FROM student WHERE height >= 1.70 AND height <= 1.90
-- 在两者之间  包含两边的边界值
SELECT * FROM student WHERE height BETWEEN 1.70 AND 1.90;
/*
模糊查询
LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值
或数值型. 通配符： % 任意多个字符，包含0个字符 _ 任意单个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）
*/
SELECT * FROM student WHERE sname LIKE '张%';
SELECT * FROM student WHERE sname LIKE '%张';
SELECT * FROM student WHERE sname LIKE '张_';
-- 排序
/*
排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序，desc代表的是降序，如果不写，默认是升序
order by子句中可以支持单个字段、多个字段、表达式、函数、别名
*/
SELECT  * FROM student ORDER BY height;
SELECT  * FROM student ORDER BY height DESC;
SELECT  * FROM student WHERE id>1 ORDER BY height DESC;
-- 数量限制 索引从0开始，MySQL分页查询的基础（从第几开始，查询几条数据）
/*
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
*/
SELECT * FROM student LIMIT 0,3; 
SELECT * FROM student LIMIT 3,3; 
SELECT * FROM student WHERE id>=1 ORDER BY register_time DESC LIMIT 0,3;
-- 分组  group by 分组列（以哪列为条件分组） 与分组函数联合使用
/*
select 分组函数，列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[having 分组后的筛选]
[order by 子句]
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段
分组查询中的筛选条件分为两类:
数据源 源位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by的后面 having
*/
-- 把值相同的数据分到同一组 统计男生和女生分别有多少人
SELECT COUNT(*),gender FROM student GROUP BY gender;
SELECT COUNT(*),gender FROM student WHERE id>1 GROUP BY gender;
-- 统计男生和女生分别有多少人，查询出人数大于2的是哪个性别的
SELECT COUNT(*)c,gender FROM student WHERE id>=1 GROUP BY gender HAVING c>2;
-- 子查询
SELECT * FROM (SELECT COUNT(*)c,gender FROM student WHERE id>=1 GROUP BY gender)t WHERE t.c>2;
-- union 将两条查询结果合并为一个结果，去除两条中的重复的数据（每列数据都是相同的）
SELECT num,sname,gender FROM student WHERE gender = '男'
UNION
SELECT num,sname,gender FROM student 
-- union all 将两条查询结果合并为一个结果，不会去除两条查询中的重复数据
SELECT num,sname,gender FROM student WHERE gender = '男'
UNION ALL
SELECT num,sname,gender FROM student 

-- 子查询
/*
含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或
外查询. 分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：支持标量子查询，列子查询，行子查询
按功能、结果集的行列数不同：
标量子查询（结果集只有一行一列） max min sum avg count
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）（较少）
表子查询（结果集一般为多行多列）相当于一个表

在mysql中的 update delete 语句中不支持子查询，在Oracle中支持，insert中支持查询
*/

/*
子查询主要使用在查询语句中
一般牵扯到两张表以上的操作，都需要通过表名调用列，可以为表名定义别名
*/
-- select语句后面只支持标量子查询（一行一列）
SELECT s.num,s.sname,(SELECT phone FROM student s1 WHERE s1.num=s.num) FROM student s;
-- from后面只支持表子查询（多行多列）
SELECT * FROM(SELECT * FROM student WHERE gender = '男')t WHERE t.height>1.5;
-- 就是把一个查询的结果当做一张表，被另一个查询语句当做数据源
SELECT * FROM (SELECT COUNT(*)c,gender FROM student WHERE id>=1 GROUP BY gender)t WHERE t.c>2;
-- where 后面使用标量子查询 查询身高最高的学生信息
SELECT * FROM student WHERE height = (SELECT MAX(height) FROM student)
-- 列子查询 一列多行
SELECT * FROM student WHERE height IN (SELECT height FROM student WHERE height>1.6)
-- 行子查询 一行多列
SELECT * FROM student WHERE (height,num) = (SELECT MAX(height),MAX(num) FROM student)

六、多表设计_关联查询

/*
为什么需要多张表？
    一个系统有很多信息
    一张表存储一类信息
    数据冗余——————拆分(不可能也不建议将许多的信息放在一张表中，例如学生的课程，年级.....)
多张表之间是有关系的，如何设计表与表之间的关系？
    多表的关系设计遵循“数据库设计范式（规则）”
    关系型数据库设计有五种设计范式要求，一般满足前三种即可
        目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第
        三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范
        式（5NF，又称完美范式）。
        1.第一范式(确保每列保持原子性，不可再分割)
        2.第二范式就是要有主键，要求其他字段都依赖于主键。
        3.第三范式就是要消除传递依赖，一张表存储一类信息，每一类信息依赖于自己的主键。        
*/

/*
 学生信息表
 专业表
 课程
*/
CREATE TABLE major(
    id INT PRIMARY KEY AUTO_INCREMENT,
    mname VARCHAR(10)
)
-- 修改表结构
-- 在学生表中添加了一个专业id，这样学生信息与专业信息就关联上了
-- 表与表之间的关联关系，根据强弱分为强关联和弱关联
/*

1. 弱关联关系，表与表之间并没有强制的约束关系，单独删专业是可以的

2. 强关联关系，表与表之间有约束关系（外键约束）
   */
     ALTER TABLE student ADD COLUMN majorid INT;
     -- 修改表结构之外键约束 majorid添加外键约束,外键与另一张表的主键关联
     ALTER TABLE student ADD CONSTRAINT fk_student_major_majorid FOREIGN KEY(majorid) REFERENCES major(id);
     /*
     添加外键约束后，与一定的要求：
     1、当主表中没有对应的记录时，不能将记录添加到从表
     2、不能更改主表中的值而导致从表中的记录孤立
     3、从表存在与主表对应的记录，不能从主表中删除该行
     4、删除主表前，先删从表
     */
     -- 关联查询
     /*
     按功能分类：
     内连接
     等值连接
     非等值连接
     自连接
     外连接
     左外连接
     右外连接
     */
     -- 查询学生信息 学号，姓名... 专业名称
     -- 笛卡尔乘积现象，将两张表中的所有数据全部关联起来，可以添加条件
     SELECT * FROM student s,major m
     -- 内连接查询
     SELECT * FROM student s,major m
         WHERE s.majorid = m.id
     -- 建议使用 inner  join 把满足两张表的关联条件的数据查询出来
     -- 内连接中的等值连接
     SELECT * FROM student s INNER JOIN major m ON s.majorid = m.id           
     -- 内链接中的非等值连接
     CREATE TABLE height_level(
   hlevel CHAR(1),
   hlower FLOAT(3,2),
   hupper FLOAT(3,2)
     )        
     SELECT s.* ,h1.hlevel
   FROM student s 
   INNER JOIN height_level h1 
   ON s.height BETWEEN h1.hlower AND h1.hupper

  --  自连接 设计表 省 市 县（区）1张表
  CREATE TABLE tarea(
    id INT,
    lname VARCHAR(20),
    pid INT   -- parent 父级
  )
  DROP TABLE t_area;
  SELECT * FROM tarea ta 
    INNER JOIN tarea ta1 
    ON ta.pid = ta1.id
    WHERE ta.id = 6101
  -- 外连接左外连接,把左边表中的数据全部查询出来
  SELECT * FROM student s LEFT JOIN major m ON s.majorid = m.id 
  --  右外连接 把右边表中的数据全部查询出来
  SELECT * FROM student s RIGHT JOIN major m ON s.majorid = m.id 
  /*
      exists  not exists 
      in      not in
      如果主查询中的数据多，子查询中的数据少，适合用in    not in  大表驱动小表
      如果主查询中的数据少，子查询中的数据多，适合用exists    not exists  小表驱动大表
  */
  -- 查询数计学院 专业下的所有学生  SELECT id FROM major 子查询，查询数计学院的所有专业
  SELECT num,sname FROM student WHERE majorid IN(SELECT id FROM major)
  SELECT num,sname FROM student WHERE majorid NOT IN(SELECT id FROM major)
  /*
  EXISTS
  SELECT...ROM table WHERE EXISTS（subquery）
  该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果
  （TRUE或FALSE）来决定主查询的数据结果是否得以保留。
  */
  -- 子查询 SELECT 'a'（a可以是任意值），当条件成立时返回true，不成立返回false
  SELECT * FROM student s WHERE EXISTS(SELECT 'a' FROM major m WHERE s.majorid = m.id)
  SELECT * FROM student s WHERE NOT EXISTS(SELECT 'a' FROM major m WHERE s.majorid = m.id)