MySQL基础

MySQL基础

为什么用数据库

早期数组/对象/集合存储数据，数据存储在内存中，后来数据使用IO（字符流）将数据写入到文件中，做到持久保存。以字符串的形式存储在文件中，操作起来不方便

数据库—>数据库管理系统

存储数据：有自己特定的规则存储数据

管理数据：使用sql语言对数据进行管理

DB数据库 DBMS数据库管理系统 SQL结构化语言，只应用在数据库中

数据库分类：

关系型数据库（数据库中表与表之间的固定关系）

oracle oracle公司开发的 大型数据库 MySQL 早期是瑞典一家公司开发的，免费的，现在也被oracle收购了 sql server 微软的 收费的 DB2 IBM 收费的 大型数据库

非关系型数据库（没有固定关系，数据以key-value的形式存储即可）

redis 做缓存

#单行注释 --单行注释（要有空格）

/* */多行注释 MySQL不区分大小写

结构化查询语言（Structured Query Language） 简称 SQL 是一种对数据库进行操作的语言，对数据，管理。

关系型数据库都支持sql

SQL

DDL(Data Definition Language)数据定义语言 定义表结构 create,alter,drop,rename

DML 数据操纵语言DML（Data Manipulation Lanuage）对表数据操作（新增、修改、删除）（insert,update,delete）

DQL(Data Query Language)对数据查询

DCL（Data Controller Language）主要是设置用户，权限·····root 根用户权限最大

DDL

在MySQL中创建一个数据库出来 MySQL数据库名一旦创建不能修改

CREATE DATABASE[if not exists] 数据库名[CHARSET utf8];

DROP DATABASE 数据库名[IF EXISTS 数据库名];删除数据库

ALTER DATABASE 数据库名 CHARSET utf8; 修改字符集

约束

PRIMARY KEY 设置主键约束 可以表示表中的唯一一条记录，不能为定，不能重复，一张表中只能有一个主键

NOT NULL 不能为空约束，一张表中可以有多列不能为空

UNIQUE 唯一性约束 一张表中可以有多列不能重复

检查约束 设置条件

外键约束 （多表关联时）

varchar和char类型在Java中表示时都是字符串

VARCHAR(6) 表示字符串 可变长度的字符串 （6）最大上限是6个字符，实际存储了4个字符，那么就占4个字符空间 varchar类型长度是有上限的 65535个字节

CHAR(1) 表示字符串 定长字符串 （4）实际存储了32个字符，那么长度仍为4个字符

data 日期 年月日 blob大文本类型

FLOAT(3,2) (3表示总长，2保留两位小数)

DATETIME 年月日 时分秒

​ int 占4个字节 -2147483648 --2147483647

id1 INT, id2 INT(6) 无区别

id2 INT(6) 2EROFILL 无符号的整数6位

AUTO_INCREMENT 设置主键自动增长，从1开始 建议主键使用自增

COMMENT 注释备注

例：id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘学生主键’，

DROP TABLE [IF EXISTS] 表名–删除表结构，数据不存在了

RENAME TABLE 旧名 TO 新名 --修改表名

CREATE TABLE 表名 LIKE 新表名 --复制表

DML

(1)新增

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中的函数NOW()获得

(2)修改表数据 修改数据时需要注意条件

UPDATE 表名 SET 列名=’ ’ WHERE 条件

(3)删除 删除数据时也要注意条件

DELETE FROM stu --删除整张表数据 是dml语句

DELETE FROM student WHERE num=100

TRUNCATE TABLE student; --从表结构上对数据进行清空 属于ddl操作

DQL

查询语句 基本语法：select 查询列表 from 表名；

select 列 from 表名 where 条件 order by(排序) group by(分组) having limit

嵌套子查询 关联查询

查询出来的结果是虚拟的表格（备份），不会改变的，不会影响到数据库中数据

–查询特定列，实际中是建议的（会提高查询的效率）

select columnl ,columnl from 表名

–表示查询所有列，在需要查询所有列时使用，一般不建议

SELECT DISTINCT sname,gender FROM student

–算术运算 + - * / 只能用作算术运算

SELECT CONCAT (sname,‘:’,gender)sname FROM student;

字符串处理函数

–length（列名）以字节为单位 获取参数值的字节个数

SELECT num,sname,LENGTH(sname) FROM student;

–char_length（列名）以字符为单位

SELECT num,sname,CHAR_LENGTH(sname) FROM student;

–把字符串变成大写小写

SELECT num,sname,UPPER(sname),LOWER(sname) FROM student;

–substring(列名，开始位置从1开始，截取的长度)

SELECT num,sname,SUBSTRING(sname,2,2) FROM student;

–instr（列名，查找的子串） 类似于Java中的indexOF( );返回子串第一次出现的索引，找不到返回0

SELECT INSTR(sname,‘i’),INSTR(“abcdcc”,‘c’) FROM student;

–trim(列名) 去掉字符串前后的空格或子串

SELECT TRIM(“abc”) FROM student;

–去掉前后的指定字符串

SELECT TRIM(‘a’ FROM “abca”) FROM student；

–填充字符串 用指定字符实现左（右）填充，将列填充为指定长度

SELECT num,LPAD（左）(sname,5,“a”),RPAD（右）(sname，5，“b”) FROM student;

–replace(列，old,new) 替换所有子串

逻辑处理

–case when 条件 then 结果1 else 结果2 end; 可有多个when

SELECT num,sname,height,(CASE WHEN height)=1.70 THEN“高个子” ELSE “非高个子” END) height FROM student

–IFNULL(被检测值，默认值) 函数检测是否为null，是则返回指定值，否则返回原本的

SELECT num,sname,IFNULL(height,‘身高信息未录入’) FROM student

–IF函数 if(条件1，结果1，结果2)

SELECT num,sname,IF(height>=1.70，‘高个子’，‘非高个’)FROM student

数学函数

round(数值)：四舍五入 ceil(数值):向上取整 floor(数值):向下取整

truncate(数值，保留小数的位数)：截断，小数点后截断到几位

mod(被除数，除数):取余，被除数为正，则为正，被除数为负，则为负

round():获取随机数，返回0~1之间的小数

日期函数

now():返回当前日期+时间 curdate():返回当前系统日期，不包含时间

curtime():返回当前时间，不包含日期

YEAR(日期)，MONTH(日期)，DAY(日期)，HOUR(日期)，MINUTE(日期),SECOND(日期)

获取指定部分的年、月、日、小时、分钟、秒

–str_to_date:将日期格式转换为指定格式的日期

SELECT STR_TO_DATE(‘2022-1-7’,‘%Y-%m-%d’) FROM student

–datediff(big,small) 计算两个日期之间的相差天数

SELECT num,sname,DATEDIFF(CURDATE(),‘2022-1-1’) FROM student

%Y:年，4位 %m：月，数值（00-12） %d：月的天，数值（00-31）

%H:小时（00-23） %i:分钟，（00-59） %s:秒（00-59） %f：微秒

%T:时间，24-小时（hh：mm：ss） %j:年的天（001-366）%w：周的天（0=星期日，6=星期六）

分组函数

SELECT SUM(height) FROM student --计算某列值的总和

…… AVG（height）…… --某列的平均值

SELECT COUNT() …… --统计表中记录总数 COUNT()用作统计行数

–max(数值/字符串) 字符使用字符编码比较大小

SELECT MAX(height),MAX(sname),MIN(height),MIN(sname) FROM student

条件查询

= 、 != (<>)、>、<、>=、<=

where后= 是比较是否相等

ord与 or或 not非

in() is null 为空的

not in() 不满足int中的条件 is not null 不为空的

……between ……and……两者之间,包含两边的边界值

SELECT * FROM student WHERE height BETWEEN 1.70 AND 1.90

–like 模糊查询 %匹配任意个字符 _匹配一个字符

SELECT *FROM student WHERE sname LIKE ‘张%’ ‘%张%’ ‘张_’

–排序 order by 列名 ASC(升)/DESC(降) 不写默认为升序

–限制数量 索引从第0行开始 Mysql分页查询的基础

SELECT * FROM student LIMIT 0,3 3,3

–分组 group by 分组到（以哪列为条件分组）与分组函数联合使用

把值相同的数据分到同一组

SELECT COUNT(*),gender FROM student GROUP BY gender --统计男生和女生分别有多少人

–统计男生和女生分别有多少人，查询人数大于2的是哪个性别的 HAVING对分组后的数据进行条件筛选

SELECT COUNT(*)c,gender FROM student WHERE id>=1 GROUP BY gender HAVING c>2

–union 将两条查询结果合并为一个结果 去除两条中的重复数据（每列数据都是相同的）

SELECT num,sname,gender FROM student WHERE gender=‘男’

UNION

SELECT num,sname,gender FROM student

–union all 将两条查询结果合并为一个结果 不会去除两条查询中的重复数据

子查询：出现在其他语句中的select语句，称为子查询或内查询4

​ 外部的查询语句称为主查询或外查询

分类：按出现位置 select后：仅支持标量子查询

​ from后：仅支持表子查询

​ where或having后：支持标量子查询，列子查询，行子查询

按功能、结果集的行列数不同 标量子查询（结果只有一行一列） max min sum avg count

​ 列子查询 一列多行

​ 表子查询 多列多行（相当于一个表）

在MySQL中的 update,delete语句不支持子查询，在oracle中支持

/*子查询主要使用在查询语句中

一般牵扯到两张表以上的操作，都需要通过列名调用，可以为表名定义别名

*/

SELECT s.num,s.sname,(SELECT phone FROM student s1 WHERE s1.num=s.num)FROM students

–select语句后只支持标量子查询（一行一列）

–from后只支持表子查询（多行多列），就是把一个查询的结果当做一张表，被另一个查询当作数据源

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-7)

–行子查询 一行多列

SELECT *FROM student WHERE (height,num)=(SELECT MAX(height),MAX(num) FROM student)

为什么要多张表？ 一个系统有许多信息，一张表存储一类信息

数据冗余——拆分

​ 多表的关系设计遵循“数据库设计范式（规则）” 一般满足前3种即可

1.第一范式 （确保每列保持原子性，不可再分割）

2.第二范式 要有主键，其他列都依赖于主键列存在

3.第三范式 消除传递依赖 一张表存储一类信息，每一类信息依赖于自己的主键

​ 表与表之间的关联关系 根据强弱分为强关联和弱关联

1.弱关联关系 表与表之间并没有强制的约束关系，只是一个逻辑关系

2.强关联关系 表与表之间有约束关系（外键约束）

–修改表结构之添加外键约束 majorid 添加外键约束 外键与另一张表的主键关联

ALTER TABLE student ADD CONSTRAINT fk_student_magjor_majorid FOREIGN KEY(majorid)

REFERENCES major(id)

–笛卡尔积 将两张表中所有数据全部关联起来 可添加条件

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 FLDAT(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 t_area(

​ id INT,

​ NAME VARCHAR(20),

​ pid INT --parent 父级

)

SELECT ta.name,ta1.name,

FROM t_area ta

INNER JOIN t_area ta1 ON ta.pid=ta1.pid

WHERE ta.id=6101

–外连接

–左外连接查询 把左边表中的数据全部查询出来

SELECT *FROM student s LEFT JOIN major m ON s.majorid=m.id

–右外连接 把右边表中的数据全部查询出来

​ ···RIGHT JOIN···

–查询数计学院专业下所有学生

–in out in 如果主查询的数据多，子查询的数据少 适合用 in not in 大表驱动小表

SELECT num,sname,FROM student WHERE majorid IN (SELECT id FROM major)

​ ···NOT IN (···)

–exists not exists

–子查询 SELECT ‘a’(可以为任意值) 条件成立 返回true ,不成立返回false

–如果主查询中的数据量少，子查询中数据多，适用exists/not exists 小表驱动大表

SELECT * FROM student s WHERE EXISTS(SELECT ‘a’ FROM majorm WHERE s.majorid=m.id)

​ ……NOT EXISTS(……)

exists 语法可理解为：将主查询的数据放到子查询中做条件验证，根据验证结果（true/false）来决定查询的数据结果是否得以保留