MySql 基础

1.认识Mysql

MySql 三层结构

DBMS 数据库管理系统
- DataBase1
  - 表
  - 表2…..
- DataBase2
  - 表1
  - 表2…..
- DataBase……
MySql 不区分大小写

我们所说的数据库包含上述三层结构，当然除了上述内容外还有些其他功能结构。

客户端——> 通过port 3306（默认） —–>数据库（数据存储的各种数据）

数据库分为；

关系型数据库

通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库

Redis，MongDB

2.创建一个数据库

CHARACTER SET：指定数据采用的字符集如果不指定默认为uft_8
COLLATE：指定数据可字符集的校对规则(常用utf_bin【严格区分大小写】、utf8_general_ci 【不区大小写默认】)

注意:

校对不区分大小写，如果表中数据存在相同字段但大小写不同，只会显示一条结果；

CREATE DATABASE [IF NOT EXISTS] name CHARACTER 字符集名称 COLLATE 校对规则

[IF NOT EXISTS] 如果DB存在不会执行

省略后如果创建书库存在则会报错，

创建数据库&删除数据库


#创建数据库  不指定字符集 和 校对规则 则默认 一般为utf8(mb3)  新版的utf8mb4
CREATE DATABASE hsp_db01

#删除数据库
DROP DATABASE hs_db01
DROP DATABASE hsp_da02

#创建一个使用utf8mb4 的字符集 的 hsp_da02数据库
CREATE DATABASE hsp_da02 CHARACTER SET utf8mb4

#创建一个使用utf8mb4  校对规则为utf8mb4_bin 不区分大小写
CREATE DATABASE hsp_da03 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

#显数DB
SHOW DATABASES
#显示差创建的数据库语句
SHOW CREATE DATABASE db_name
#删除 DB
DROP DATABASE [IF EXISTS] name

3.备份数据库

Dos命令行

备份数据库

mysqldump -u user_name -p -B DB1 DB2 … > file_name.sql

备份数据库中的表

mysqldump -u user_name -p DB1 tb1 tb2 … > file_name.sql

注意：

添加环境，每天加去bin目录启动
不要在sql控制台输入会报错
-p 后密码不要输入回车后输入

恢复数据库

Source file_name

4.创建表

CREATE TABLE (
	name datatype,
    name2 datatype,
	...
)character set 字符集 collate 校对规则 engine 引擎

#character set 默认与数据库一致
#collate 默认与数据库一致

-- 创建一张员工基本信息的表
CREATE TABLE `emp` (
  id INT,
  `name` VARCHAR (32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR (32),
  salary DECIMAL (9, 4),
  `resume` TEXT
) CHARSET utf8mb4 COLLATE utf8mb4_bin ENGINE INNODB ;

#添加数据
INSERT INTO `emp`
	VALUES(008, '姓名', '男', '2050-10-10', '2050-10-10', '工作', 10.2323, '描述');
	
#查询
select * from emp;

创建表示注意确认当前是在那个数据库

5.修改表

修改表的基本操作

#添加列
ALTER TABLE tablename
ADD (column datatype [DEFAULT expr]
		....		);
#修改列
ALTER TABLE tablename
MODIFY colum datatype [DEFAULT exper];

#删除列
ALTER TABLE tablename
DROP column;

#查看表的结构
desc tablename

#修改表名
Rename table tablename to New_tablename

#修改表的字符集
ALTER TABLE tablename character set 字符集;

#修改字段名称及其默认值

实例

-- 创建一张员工基本信息的表
CREATE TABLE `employee` (
  id INT,
  `name` VARCHAR (32),
  sex CHAR(1),
  birthday DATE,
  entry_date DATETIME,
  job VARCHAR (32),
  salary DECIMAL (9, 4),
  `resume` TEXT
) CHARSET utf8mb4 COLLATE utf8mb4_bin ENGINE INNODB ;

#增加一个字段 （列）iamge
ALTER TABLE employee
	ADD image VARCHAR(32) NOT NULL
	DEFAULT '';
#增加多个字段
#增加一个字段 （列）
ALTER TABLE employee 
  ADD (
    image2 VARCHAR (32) NOT NULL DEFAULT '',
    image3 VARCHAR (32) NOT NULL DEFAULT ''
  ) ;

-- 查询表中字段信息
DESC employee;


#修改列中的数据类型 默认改为not null 必须给定一个默认值
-- change 可以替换 modify
ALTER TABLE employee 
  MODIFY `name` VARCHAR (33)NULL,
  MODIFY `sex` VARCHAR(1) NOT NULL DEFAULT '';


#删除表中一列一个列
ALTER TABLE employee
	DROP image2,
	DROP image,
	DROP image3;

#修改表名称
RENAME TABLE employee TO emp;

#修改表的字符集和校对
ALTER TABLE emp CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

#修改列中的数据类型
ALTER TABLE emp
CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT '';

5.MySql 数据类型

也称作列类型

引入一下整形范围和正负的理解，我们都知道1byte等于8bit

char c; 这个字符c 能表示的范围为 2^8 也就是0-255

short int i; 短整形站2byte 2^16 也就是 -32768 — + 32767

unsigned short int；这个无符号类型则是 0-65535

在二进制中正负实现规则为下，任意一个非unsigned整形最高位是用来存放0 1 0表示没有符号也就是为正 1表示有符号也就为负

所以实际存放数值的只有15位二进制数，2^15 就为 0-32767了

数值类型

整形
- bit(M) [m指定位数默认1 范围1-64]是以二进制输出的比如 bit(8) =255 输出的 ‘11111111’
- tinyint [1byte]
- smallint [2byte]
- mediumint [3byte]
- int [4byte]
- bigint [8byte]
小数类型
- float [单精度 4byte]
- double [双精度 8byte]
- decimal(M,D) [大小不确定]
文本类型
- char [0-255 字符]
- varchar [0-65535 字节 utf8编码最大为21844字符 1-3个字节用于记录大小如果编码为gbk 2个字符用记录大小]
- text [0-65535]
- longtext[0-2^32-1]
二进制数据类型
- blob [0- 2^16-1]
- longblob [0-2^32-1]
时期类型
- date [日期年月日]
- year [年]
- time [时间时分秒]
- datetime [年月日时分秒]
- timestamp [时间戳]

使用规则：保小不保大

在满足需求的情况下，经理选择占用空间较小的类型

decimal(M,D)

定点数精度非常高

双精度，两个参数decimal(m,d) m <= 65 D <= 30 D默认为0 M默认为10

M为总小数位数 D为小数点后位数 D为0则内有小数点或者分数部分。

字符串

char(size ) size 表示的是字符数（不论是否是中文还是其他）而不是字节，注意不好搞混了

varchar(size) 同理

CREATE TABLE `tb`(
	`char` CHAR(),
    `varchar` VARCHAR()
);
INSERT INTO `tb` VALUES('四个字符', 'abcd');

1_CHAR 和 VARCHAR

char() 类型和数组一样是定长的不可改变其大小，如果超出就会报错

优点：适合定长的字符串并且执行效率很高

缺点：长度不定时容易产生错误

varchar() 类型是一种可边长数组不定长

优点：varchar 在对于不知道字符有多长时有用处

缺点：搜索没有char快速，并且空间利用也没有char高效

2_TEXT、MEDIUMTEXT、LONGTEXT

text 0-2^16

mediumtext 0-2^24

longtext [0-2^32-1]

文本类型，可替换varchar类型也是不定长字符串。适合用于非常长的字符串比如文章

CREATE TABLE [IF NOT EXISTS] `tb3` (
	`text` TEXT,
	`mediumtext` MEDIUMTEXT,
	`longtext` LONGTEXT
);

INSERT INTO  [IF NOT] `tb3` VALUE('这个类型很长', '可变长字符串', '汉子3字节1111这个一字节编码不同可能不一样');

—-注意后面的字节数变化

日期类型

DATE
DATETIME
TIEMSTAMP （时间戳可选项 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 没有指定时间会将当前时间加入 CURRENT_TIMESTAMP）

TIMESTAMP详解

自动更新时间为当期时间

TIMESTAMP 声明 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

insert updata 时候会将该字段的这一列所有timestamp 类型的变量更改为当期时间

CREATE TABLE t1 (
  birthday DATE,
  -- 年月 
  job_DATATIME DATETIME,
  -- 年月时间
  TIME_STAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- 更新这个表某条记录  会自动更新这一列的所有字段 以当前时间更新
  TIME_STAMP2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO t1 (birthday, job_DATATIME) VALUES ('2000-01-02','2022-01-01 10:12:30');
INSERT INTO t1 (birthday, job_DATATIME) VALUES ('2001-01-02','2020-01-01 10:12:30');
-- 插入两条记录

6.增删改查

INSERT语句

INSERT INTO table_name [column1,column2] VALUES [value1, value2]

CREATE TABLE goods(
	`id`  INT,
	`goods_name` VARCHAR(10),
	price DECIMAL(8,2)

)CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DESC goods;

INSERT INTO `goods` (`id`, `goods_name`, `price`) 
	Values (1001, 'rtx-4090', 20000.0);
INSERT INTO `goods` (`id`, `goods_name`, `price`) 
	VALUES (1002, 'rtx-4080', 10000.0);

SELECT * FROM `goods`;

INSERT INTO employe (id, user_name, brithday, job, salary)
VALUES (001, '张三', "2001-01-02", 'xxx', 10000.0);
INSERT INTO employe (id, user_name, brithday, job, salary)
VALUE (002, '李四', '2000-01-02', 'xxx', 0.0);

插入数据类型必须与字符串匹配
- 如果字段为 int 数据项为'字符串' mysql会尝试进行转换，字符串字符必须为数字
数据项的长度必须在规定范围中
列1，列2 VALUESdata1，data2 列出的字段必须与数据项一一对应
date datetime 时间类型本质是字符串
数据项可以null 前提是字段没有设置not null

添加多条数据

```mysql
INSERT INTO table_name (….)
VALUES (),(),()..;


- 给表中所有字段添加数据项可以 不用列出其`column`

- 字段中有默认值，可以不用添加，如果没有则会报错

  ```mysql
  CREATE TABLE test(
  	head CHAR(2),
  	`default` INT DEFAULT 1,
  	`end` CHAR(2)
  )CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
  
  INSERT INTO test (head, 'default', 'end')
  VALUES ('->', '<-'); # 列段有三个 实际添加了2个字段

PS， column 字段如果是关键字用[``] 用单引号不行

UPDATE语句

UPDATE table_name SET column = data .. WHERE 条件;

没有使用where语句表中所有字段值都会改变

示例：

1
2
3

UPDATE employe
SET salary = salary + 1000
WHERE user_name = '张三'

DELETE 语句

删除表中的某条记录

DELETE FROM 表明

WHERE 条件

WHERE 条件不存在即删除所有记录

SELECT 语句

SELECT 查询语句

SELECT [DISTINCT] (去除重复项) * | [列命1，列命2] FORM 表明

CREATE TABLE students( id INT NOT NULL DEFAULT 1, `name` VARCHAR(20) NOT NULL DEFAULT '', chinese FLOAT NOT NULL DEFAULT 0.0, english FLOAT NOT NULL DEFAULT 0.0, math FLOAT NOT NULL DEFAULT 0.0 )CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNOBASE; 
/*[15:17:14][16 ms]*/ ALTER TABLE students CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 
INSERT INTO students 
VALUES( 1, '张三', 50, 50, 50),
(2, '李四', 40,40,40),
(3, '李白', 100,0,0),
(4, '图灵', 0, 100, 100),
(5, '爱因斯坦', 0, 100, 100),
(6, '牛顿', 0, 100, 100),
(7, '我', 30, 10, 10);

查询表中所有数据元素
1
SELECT * FROM students;
按列查找
1
SELECT `name`, `math` FROM students;
查询中过滤重复数据
1
SELECT DISTINCT * FROM students;

SELECT 查询结果运算

使用表达式对查询的列进行运算

SELECT * | {column1 | expression , column2 | expression ..}

FROM table_name；

SLECT 查询中使用别名 as

1	SELECT column_name as new_name FROM table_name;

示例

1	SELECT `name`, (`math` + `english` + `chinese`) AS '总成绩' FROM students;

WHERE 子句

ORDER BY 子句

示例

1
2
3

SELECT `name`, (math + english + chinese) AS socre
FROM students
ORDER BY socre DESC;

Count 合计/统计函数

count(column) 统计单个列时会排除为null的记录而如果是查询表中全部有一条记录为空也会算做一条记录

与底层逻辑有关

统计表中有多少条记录
1
SELECT COUNT(*) FROM students;

统计总数学＞90的有多少人

1
2
3

SELECT COUNT(`name`) FROM students 
	WHERE math > 90
	ORDER BY `math`

SUM（）

只对数值有效，用法和count()一致

AVG（）

MAX（）& MIN（）

GROUP BY & HAVING（）

7.字符串常用操作

dual 系统自带亚原表没有实际意义用于测试

CHARSET(str) 返回字符串 ->字符集

1	SELECT CHARSET('张三') FROM dual;

CONCAT(str1, str2...) -> 拼接字符串

1	SELECT CONCAT('A'， 'B'， 'C') FROM DUAL;

STRCMP(str1, str2) -> 比较字符串比较的是二进制数大小不区分大小写

1	SELECT STRCMP('abc','ABC') FROM DUAL;

SUBSTRING(str, position, length) 截取字符串

1	SELECT SUBSTRING('abcdef', 2, 4) FROM DUAL;

LTRIM(str) & RTRIM(str) & TRIM(str) 去除多余空格

1	SELECT LTRIM(' a'), RTRIM('b '), TRIM(' ab ') FROM DUAL; -- ->a,b,ab

INSTR(str1, str2) ->查找子串在str1中的位置

1	SELECT INSTR('ABCD', 'C') FROM DUAL; -- ->3

转换大小写 UCASE(str) && LCASE(str)

1	SELECT UCASE('abcde'), LCASE('ABCDE') FROM DUAL;

返回字符串长度 **返回的是字节长度并非实际长度 LENGTH(str)

1	SELECT LENGTH('你好世界') FROM DUAL; -- 3 * 4 --- > 12

REPLACE(str1, serch_str, replace_str) 在原str字符串查找子串serch_str 并替换成replace_str

1	SELECT REPLACE('abcde', 'c', '第三个字符') FROM DUAL; -- ab第三个字符de

栗子： name 首字母大写其余小写

1	SELECT CONCAT( UCASE( SUBSTRING(`name`, 1, 1) ) , LCASE( SUBSTRING(`name`, 2) ) ) FROM students;

8.MATH常用函数

ABS(num) 返回绝对值

1	SELECT ABS(-233.333) FROM DUAL;

BIN() 十进制转二进制

1	SELECT BIN(10) FROM DUAL;

CONV(num, from_case, to_case) -》将num （from_case 当前进制）转换为 to_case 进制

1	SELECT CONV(16, 10, 16) FROM DUAL;

HEX(num) 转换为16进制

1	SELECT HEX(16) FROM DUAL;

上下取整 CEIL(float) && FLOOR(float)

1	SELECT CEIL(-11.111) FROM DUAL;

保留几位小数FROMAT(num, bit) 自动四舍五入

1	SELECT FORMAT(12.2225, 3) FROM DUAL;

取模mod(num, num)

1	SELECT MOD(10, 3) FROM DUAL;

返回随机数，默认种子时间真随机，自己参数种子自添加伪随机

rand(种子) 0 ≤ n ≤ 1.0

1	SELECT RAND() FROM DUAL;

9.时间日期相关函数

CURRENT() 返回当前日期

CURRENT_TIME() 返回当前时间

CURRENT_TIMESTAMP() 当前时间戳

NOW() 返回当期时间和日期

DATE(datetime) 返回 datetime的日期部分

DATE_ADD(date2, INTERVAL d_value d_type) date2基础上加日期或者时间 d_type 年月日时分秒周都可以

DATE_Sub(date2, INTERVAL d_value d_type) date2基础上减日期或者时间 d_type 年月日时分秒周都可以

DATEDIF(date1, date2) 两个日期差(结果是天) -> TIMEDIF() 时间差值

截取时间YEAR|DAY|MONTH(datetime)