mysql

安装

dev.mysql.com/downloads/i…

连接方法：

1、

2、配置环境变量 cmd 中 mysql -u root -p 然后输入密码就行了

系统变量path 中配置 C:\Program Files\MySQL\MySQL Server 8.0\bin

mysql -u root -p

数据类型

整数类型

1. TINYINT：占用1个字节，范围为-128到127。
2. SMALLINT：占用2个字节，范围为-32768到32767。
3. MEDIUMINT：占用3个字节，范围为-8388608到8388607。
4. INT：占用4个字节，范围为-2147483648到2147483647。
5. BIGINT：占用8个字节，范围为-9223372036854775808到9223372036854775807。

浮点数类型

1. FLOAT：占用4个字节，表示单精度浮点数，精度为7位。
2. DOUBLE：占用8个字节，表示双精度浮点数，精度为15位。
3. DECIMAL：用于精确计算，根据用户定义的精度来存储数字。DECIMAL的精度是以M和D来定义的，其中M表示数字的总位数，D表示小数部分的位数。

tips:

1. 浮点数类型在存储和计算时可能存在精度损失，因此不适合用于涉及到精确计算的场景。
2. 使用DECIMAL类型时需要注意其精度设置，避免精度丢失或误差。

日期和时间类型

1. DATE：用于存储日期，格式为YYYY-MM-DD。
2. TIME：用于存储时间，格式为HH:MM:SS。
3. YEAR：用于存储年份，格式为YYYY。
4. DATETIME：用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS。
5. TIMESTAMP：用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS，可以自动更新。

tips:

1. DATE、TIME和DATETIME类型可以设置默认值和约束条件，以便在插入数据时保证数据的完整性和正确性。
2. TIMESTAMP类型在插入数据时可以自动更新，这样可以避免手动更新时的繁琐操作。但需要注意的是，如果需要保存精确时间戳，应该使用DATETIME类型。

字符串类型

1. CHAR：定长字符串，最大长度为255个字符。
2. VARCHAR：变长字符串，最大长度为65535个字符。
3. TINYTEXT：最大长度为255个字符的文本。
4. TEXT：最大长度为65535个字符的文本。
5. MEDIUMTEXT：最大长度为16777215个字符的文本。
6. LONGTEXT：最大长度为4294967295个字符的文本。

tips:

1. 在选择字符串类型时应该根据实际需要选择不同的类型。如果字符串长度固定，可以使用CHAR类型，如果长度不确定，可以使用VARCHAR类型。
2. VARCHAR类型的存储效率比CHAR类型高，但在查询和排序时效率较低。
3. 如果需要存储较大的文本，应该使用TEXT类型而不是VARCHAR类型。

二进制数据类型

1. BINARY：定长二进制数据，最大长度为255个字节。
2. VARBINARY：变长二进制数据，最大长度为65535个字节。
3. TINYBLOB：最大长度为255个字节的二进制数据。
4. BLOB：最大长度为65535个字节的二进制数据。
5. MEDIUMBLOB：最大长度为16777215个字节的二进制数据。
6. LONGBLOB：最大长度为4294967295个字节的二进制数据。

tips:

1. 在设计表结构时应该根据实际需要选择不同的二进制数据类型。如果数据量很小，可以使用TINYBLOB或BINARY，如果数据量很大，可以使用LONGBLOB或VARBINARY。
2. 二进制数据类型在存储和查询时效率较高，但在排序和比较时效率较低。

DDL

数据库操作

//创建数据库

create database db_test1

create database db_test1 default charset utf8mb4

create database if not exists db_test default charset utf8 default collate utf8_general_ci;

//查看数据库

show databases;

select database(); //查看当前使用数据库

//使用数据库

use db_test;

//删除数据库

drop database db_test1;

drop database if exists db_test; //如果存在才删除

表操作

show tables; //查看当前数据库所有表

desc t_user; //查看表结构

show create table t_user; //查看创建表语句

//创建表

create table if not exists tb_user(
    id int comment '主键ID',
    name varchar(20) comment '用户名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表'; 

create table if not exists t_user(
    id int(11) primary key auto_increment,
    name varchar(20) not null,
    age tinyint unsigned not null, //无符号整数
    grade double(5,2) not null, //浮点数,5表示总位数，2表示小数位数
    birthday date not null //日期
)engine=innodb default charset=utf8; 

create table if not exists t_emp(
    id int(11) primary key auto_increment,
    workNo varchar(20) not null,
    name varchar(10) not null,
    gender char(1) not null,
    age tinyint unsigned not null,
    idCard char(18) not null,
    enrrydate date not null comment '入职日期'
) comment '员工表';

//添加字段
alter table t_user add phone varchar(20) not null; //在表最后面添加字段

alter table t_user add email varchar(20) not null after name; //在指定字段后面添加字段

alter table t_user add idCard char(18) not null first; //在表最前面添加字段


//修改字段
alter table t_user modify phone varchar(25) not null; //修改字段类型和约束
alter table t_user change phone phoneNo varchar(25) not null; //修改字段名和类型和约束
alter table t_user drop phoneNo; //删除字段
alter table t_user rename to t_userinfo; //修改表名
alter table t_userinfo comment '用户信息表'; //修改表注释
alter table t_userinfo modify phone varchar(25) not null comment '电话号码'; //修改字段注释
alter table t_userinfo change phone phoneNo varchar(25) not null comment '电话号码'; //修改字段名和注释

//删除表
drop table if exists t_userinfo;
drop table if exists t_userinfo,t_emp;//删除多个表

//清空表
truncate table t_userinfo; //清空表数据，保留表结构
delete from t_userinfo;//清空表数据，保留表结构，保留自增
delete from t_userinfo where id=1;//清空表数据，保留表结构，保留自增
delete from t_userinfo where id>1;//清空表数据，保留表结构，保留自增
delete from t_userinfo where id in(1,2);//清空表数据，保留表结构，保留自增
delete from t_userinfo where id not in(1,2);//清空表数据，保留表结构，保留自增
delete from t_userinfo where id between 1 and 2; // 

DML

对数据库中表的数据记录进行增删改操作

insert into t_user(id,name,age) values("1",'张三',18); --插入数据
insert into t_user values("2",'李四',20); --插入数据
insert into t_user values("3",'李四',20),("4",'王五',30); --插入数据 

select * from t_user; --查询数据 

update t_user set age=20  where id=1; --更新id=1数据
update t_user set age=20,name="张三1" where id=1; --更新id=1数据
update t_user set age=20  where id=1; --更新id=1数据
update t_user set age=20,name="张三1"; --更新所有数据


delete from t_user where id=1; --删除数据
delete from t_user where id=1 or id=2; 
delete from t_user where id in(1,2); --删除id=1或id=2的数据
delete from t_user where id>1; --删除id>1的数据


delete from t_user; --删除所有数据

DQL

数据查询语言，用来查询数据库中表的记录

select * from t_user;
select name from t_user; --查询name字段
select name,age from t_user; --查询name和age字段
select name,age from t_user where id=1; --查询id=1的数据
select name,age from t_user where id>1; --查询id>1的数据
select name,age from t_user where id>1 and age<20; --查询id>1且age<20的数据
select name,age from t_user where id>1 or age<20; --查询id>1或age<20的数据
select name,age from t_user where id between 1 and 3; --查询id在1到3之间的数据

select name as '姓名' from tb_user; --给表头起别名
select name '姓名' from tb_user; --给表头起别名 as 可以去掉

select distinct name from tb_user; --去重查询

聚合函数

聚合函数 所有的null值都会被忽略

select count(*) from tb_user; --查询总记录数（总共有几条数据）
select count(age) from tb_user; --查询age有值的有几条
select count(*) from tb_user where age>20; --查询年龄大于20的总记录数
select max(age) from tb_user; --查询最大年龄
select min(age) from tb_user; --查询最小年龄
select avg(age) from tb_user; --查询平均年龄
select sum(age) from tb_user; --查询年龄总和
select count(*) as '总记录数',max(age) as '最大年龄',min(age) as '最小年龄',
avg(age) as '平均年龄',sum(age) as '年龄总和' from tb_user; 
--查询总记录数、最大年龄、最小年龄、平均年龄、年龄总和

分组查询

• where 和 having的区别
• where 是在分组之前进行筛选的，having 是在分组之后进行筛选的
• where 后面不能跟聚合函数，having 后面可以跟聚合函数
• having 后面可以跟别名，where 后面不能跟别名

//按照age分组查询，查询每个年龄的人数
select age,count(*) from tb_user group by age; 
//按照age分组查询，查询年龄大于20的人数
select age,count(*) from tb_user where age>20 group by age; 
//--选择年龄大于20的用户，并按年龄分组，同时过滤出每个年龄组中用户数量大于1的组。
//按照age分组查询，查询年龄大于20且人数大于1的人数 having是分组查询后的筛选条件 不能用where
select age,count(*) from tb_user where age>20 group by age having count(*)>1; 

//查询年龄大于20的用户，并按年龄分组，同时过滤出每个年龄组中用户数量大于1的组，并按年龄降序排序。
select age,count(*) from tb_user where age>20 group by age having count(*)>1 order by age desc;
//按照gender分组查询，查询每个性别的人数
select gender,count(*) from tb_user group by gender;
//按照gender分组查询，查询每个性别的平均年龄
select gender,avg(age) from tb_user group by gender;
//按照workAddress分组查询，查询年龄小于45且人数大于2的工作地点
select workAdress,count(*) from tb_user where age < 45 group by workAddress having count(*)>2; 
 

• --tips: group by 后面可以跟多个字段，表示按照多个字段分组查询,
• --执行顺序：where -> 聚合函数 -> group by -> having -> select -> order by
• --分组之后只能查询分组字段和聚合函数，不能查询其他字段，除非该字段在group by 后面

排序

//按照age降序排序
select * from tb_user order by age desc; 
//按照age升序排序
select * from tb_user order by age asc; 
//按照age降序排序，如果age相同，则按照gender升序排序
select * from tb_user order by age desc,gender asc; 

分页查询

select * from tb_user limit 0,10; //查询前10条数据
select * from tb_user limit 10,10;//查询第11到20条数据
select * from tb_user limit 20,10;//查询第21到30条数据
//limit 后面可以跟两个参数，第一个参数表示起始位置，第二个参数表示查询的条数
//limit 后面可以跟一个参数，表示查询的条数，默认起始位置为0

执行顺序

from -> where -> 聚合函数 -> group by -> having -> select -> order by -> limit

DCL

数据库控制语言，用来管理数据库用户，控制数据库的访问权限。

--查询用户
select * from mysql.user; --查询所有用户
show grants for 'root'@'localhost'; --查询root用户的权限
 
--创建用户
create user 'username'@'host' identified by 'password';
--创建用户，用户名为zjz，密码为1234，主机为localhost
create user 'zjz'@'localhost' identified by '1234';
--登录数据库 用户名root 
-- mysql -u zjz -p

-- 主机设置为 % 表示允许从任意主机登录
create user 'zjz'@'%' identified by '1234';

 
--修改用户密码
alter user 'username'@'host' identified by 'newpassword';

--删除用户
drop user 'username'@'host';


--授权
grant all privileges on *.* to 'username'@'host'; -- 所有权限 所有数据库 给用户

grant all privileges on database.* to 'username'@'host'; -- 所有权限 所有数据库 给用户
grant select,insert on database.* to 'username'@'host'; -- 查询和插入权限 给用户
grant select,insert on database.* to 'username'@'host' with grant option; -- 查询和插入权限 给用户，并且允许用户授权给其他用户
flush privileges; -- 刷新权限
show grants for 'username'@'host'; -- 查看用户权限
revoke select on database.* from 'username'@'host'; -- 回收查询权限
revoke all privileges on database.* from 'username'@'host'; -- 回收所有权限

show grants; -- 查看当前用户权限


--回收权限
revoke all privileges on database.* from 'username'@'host';

函数

符串函数

select concat ('hello', 'world'); -- 拼接字符串
select length('hello world'); -- 获取字符串长度
select substring('hello world', 1, 5); -- 截取字符串
select reverse('hello world'); -- 反转字符串
select upper('hello world'); -- 转换为大写
select lower('HELLO WORLD'); -- 转换为小写
select trim('  hello world  '); -- 去除字符串两端的空格
select replace('hello world', 'world', 'mysql'); -- 替换字符串
select locate('world', 'hello world'); -- 查找字符串位置
select instr('hello world', 'world'); -- 查找字符串位置
select lpad('hello', 10, ' '); -- 左填充
select rpad('hello', 10, ' '); -- 右填充   

update tb_user set id = lpad(id,5,'0'); -- 更新id为5位，不足前面补0

数值函数

select abs(-3.14); -- 绝对值
select ceil(3.14); -- 向上取整
select floor(3.14); -- 向下取整
select round(3.14); -- 四舍五入
select truncate(3.1415926, 2); -- 截断小数点后两位
select mod(10, 3); -- 取余
select pow(2, 3); -- 幂运算
select sqrt(4); -- 平方根
select rand(); -- 生成随机数
select sign(-3.14); -- 判断正负，正数返回1，负数返回-1，0返回0
select ascii('a'); -- 获取字符的ASCII码

日期函数

select curdate();-- 获取当前日期
select curtime();-- 获取当前时间
select now();-- 获取当前日期和时间
select year(now());-- 获取当前年份
select month(now());-- 获取当前月份
select day(now());-- 获取当前日期中的天数
select hour(now());-- 获取当前时间中的小时数
select minute(now());-- 获取当前时间中的分钟数
select second(now());-- 获取当前时间中的秒数
select date_format(now(), '%Y-%m-%d %H:%i:%s');-- 格式化日期时间
select datediff('2022-01-01', '2022-01-02');-- 计算两个日期之间的天数差
select date_add(now(), interval 1 day);-- 在当前日期上加上一天
select date_sub(now(), interval 1 day);-- 在当前日期上减去一天
select timestampdiff(hour, '2022-01-01 00:00:00', '2022-01-02 00:00:00');-- 计算两个日期时间之间的时间差

流程函数

select if(1=1, '是', '否');-- if else 简写
select ifnull(null, '默认值');-- ifnull 第一个是null返回第二个值 否则返回第一个值
select coalesce(null, null, '默认值');-- coalesce 返回第一个非null的值
select nullif(1, 1);-- nullif 如果两个值相等返回null，否则返回第一个值
select case when 1=1 then '是' else '否' end;-- if else 函数    
select case when 1=1 then '是' when 2=2 then '否' else '其他' end;// 多重if else 
select locate('a', 'abc');// 查找子字符串在字符串中的位置


// 查询用户工作地点，如果工作地点是北京或上海，则显示为一线城市，否则显示为二线城市
select  name (case workaddress when '北京' then '一线城市' 
when '上海' then '一线城市' else '二线城市' end) as '工作地点' from tb_user;


// 查询学生成绩，如果成绩大于等于85分，则显示为优秀，如果成绩大于等于60分，则显示为及格，否则显示为不及格
select 
id,
name,
(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学成绩',
(case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) as '英语成绩',
(case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) as '语文成绩'
from tb_score; 

约束

约束是作用在表中字段上的规则，用于限制存储在表中的数据。保证数据库中数据的正确性，有效性，完成性。

• 主键约束 primary key
• 外键约束 foreign key references
• 唯一约束 unique
• 非空约束 not null
• 默认值约束 default
• 检查约束 check
• 自增约束 auto_increment

--非空约束 not null  
create table tb_user(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int,
	address varchar(50)
);-- 创建用户表，id为主键自增，name不能为空，age可以为空，address可以为空

--唯一约束 unique
create table tb_user(
	id int primary key auto_increment,
	name varchar(20) unique,
	age int,
	address varchar(50)
);-- 创建用户表，id为主键自增，name唯一，age可以为空，address可以为空
--主键约束 primary key
create table tb_user(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	address varchar(50),
	constraint uq_name unique(name)
);-- 创建用户表，id为主键自增，name可以为空，age可以为空，address可以为空，name唯一
--外键约束 foreign key
create table tb_score(
	id int primary key auto_increment,
	score int,
	student_id int,
	foreign key(student_id) references tb_student(id)
);-- 创建成绩表，id为主键自增，score为成绩，student_id为外键，关联学生表中的id
--默认约束 default
create table tb_order(
	id int primary key auto_increment,
	money float,
	status int default 0
);-- 创建订单表，id为主键自增，money为金额，status为状态，默认为0
--检查约束 check
create table tb_worker(
	id int primary key auto_increment,
	salary float,
	check(salary>=5000)
);-- 创建员工表，id为主键自增，salary为工资，要求工资大于等于5000

create table tb_student(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int check(age>=18)
);-- 创建学生表，id为主键自增，name不能为空，age必须大于等于18

--外键约束
create table tb_score(
	id int primary key auto_increment,
	score int,
	student_id int,
	foreign key(student_id) references tb_student(id)
);-- 创建成绩表，id为主键自增，score为成绩，student_id为外键，关联学生表中的id


--触发器
create trigger tri_before_insert before insert on tb_student for each row
begin
	if new.age < 18 then
		signal sqlstate '45000' set message_text = '年龄必须大于等于18';
	end if;
end;-- 创建触发器，在插入数据之前，如果年龄小于18，则抛出异常

--存储过程 
create procedure pro_insert_student(in name varchar(20), in age int)
begin
	insert into tb_student(name, age) values(name, age);
end;-- 创建存储过程，插入学生数据

call pro_insert_student('张三', 19);-- 调用存储过程，插入学生数据

--函数
create function fun_get_age(id int) returns int
begin
	declare age int;
	select age into age from tb_student where id = id;
	return age;
end;-- 创建函数，根据id获取学生年龄

select fun_get_age(1);-- 调用函数，获取学生年龄