article cover

mysql的进阶用法 mysql

视图

类似临时表取别名,可反复使用。不推荐使用

-- 创建
create view v1 as select * from student where sid > 10;

-- 使用
select * from v1; # 使用v1视图

-- 修改
alter view v1 as SQL;

-- 删除
drop view v1;

触发器(不推荐使用)

delimiter \\
create trigger t1 before/after insert/delete on table1 for each row
begin
SQL; -- insert 可使用 NEW 变量; delete 可使用 OLD 变量
end \\
delimiter ;

函数

---- 内置函数
select curdate(); -- 当前日期
current_timestamp(); -- 当前日期
length('asdf'); -- 字符串长度
concat('alex', '123'); -- 拼接
date_format('2020-02-02', "%Y-%m") -- 时间格式化

---- 自定义函数
delimiter \\
create function f1(
i1 int,
i2 int)
returns int
begin
-- 可以写 存储过程 语句
declare num int default 0;
set num = i1 + i2;
return(num); 
end \\
delimiter ;

select f1();

存储过程

(更重要,DBA与程序员之间的分工活)
保存在MySQL上的一个别名,它是一堆SQL语句,用于替代程序员写SQL语句

delimiter \\
create procedure p1(
in n1 int, -- in, out, inout
out n2 int)
begin
set n2 = 123123;
select * from user where id > n1;
end \\
delimiter ;

set @v1 = 0;
call p1(1, @v1); -- @v1只用于取值,无法传入值,如果为inout,则皆可
select @v1;

事务(性能不高)

delimiter \\
create procedure p2(
out return_code tinyint)
begin
-- 定义异常
declare exit handler for sqlexception
begin
set return_code = 1; # 异常状态
rollback; # 回滚操作
end;

-- 原子性事务操作
start transaction;
delete from tb1;
insert into tb2(name) values('seven');
commit;

set return_code = 0; # 正常状态

end \\
delimiter ;

游标(略)

动态执行SQL(防SQL注入)

delimiter \\
create procedure p2(
-- in tpl varchar(255),
in arg int)
begin
set @arg = arg; -- 创建session变量
prepare prod_name from 'select * from user where id = ?';
execute prod_name using @arg; -- 只能是session变量
deallocate prepare prod_name;
end \\
delimiter ;

索引(Index)

创建额外文件,某种格式存储。

  1. 普通索引: 加速查找
  2. 主键索引: 加速查找 + 不能重复 + 不能为空
  3. 唯一索引: 加速查找 + 不能重复
  4. 联合索引: (多列)
    • 联合主键索引
    • 联合唯一索引
    • 联合普通索引
create index id_name on user(name);
drop index id_name on user;

ORM操作 (SQLAlchemy)

标签: mysql

benojan 发布于  2022-11-2 23:56 
article cover

mysql的备份与还原 mysql

备份

>mysqldump -u root db1 > db1.sql -p      # 无-d参数,备份结构与数据
>mysqldump -u root -d db1 > db1.sql -p  # 有-d参数,只备份结构

导入

create database db2;    # 先建立数据库
>mysqldump -u用户 db2 < db1.sql -p
标签: mysql

benojan 发布于  2022-11-2 23:53 
article cover

mysql的增删改查 mysql

增 Insert

insert into t1(id, name) value(1, '张三');
insert into t1(name, age) value('张三', 17), ('李四', 18);
insert into t1(name, age) select name, age from t2;

删 Delete

delete from t1;
delete from t1 where id = 2;
delete from t1 where id < 6 and name = '张三';

改 Update

update t1 set age = 18 where age = 17;
update t1 set name = '张四', age = 19 where id = 1;

查 Select

select * from t1;
select id, name from t1;
select id, name as cname from t1;
select id, name, 11 from t1; -- 11为额外的列
select id, name, 11, (select num from t2) as number from t1; -- 子查询语句,必须单值
select id, name, 11, (select num from t1 as b where b.student_id = a.student_id) as number from t1 as a group by a.student_id; -- 子查询语句可以使用外层表
select case when min(num) < 10 then 0 else min(num) end from t1;
select if(min(num)<10, 0, min(num)) from t1; -- 同上
select distinct name from t1; -- 去重,但效率不高,可用group by去重

使用函数查询

if(,,)
isnull()

条件查询

where

select * from t1 where id != 1;
select * from t1 where id > 10 or name = '张三';
select * from t1 where id not in(1,5,12);
select * from t1 where id between 5 and 12; -- 包含 5, 12
select * from t1 where id in (select nid from t2);

like

select * from t1 where name like 'a%';
select * from t1 where name like 'a_';

limit

select * from t1 limit 5; -- 前5行
select * from t1 limit 5 offset 2 -- 从第3行开始的5行
select * from t1 limit 2, 5; -- 从第3行开始的5行

order by

select * from t1 order by id asc;
select * from t1 order by id desc;
select * from t1 order by id asc, name desc;

group by

select count(id), part_id from t1 group by part_id;
select max(id), part_id from t1 group by part_id;
select min(id), part_id from t1 group by part_id;

聚合函数 count、max、min、sum、avg

聚合函数二次筛选,必须使用 having

select count(id), part_id from t1 group by part_id having count(id) > 1;

连表查询

左右连表 left join, right join, inner join

  • where连表
select * from a1, b1 where a1.part_id = b1.id;
  • left join连表

    a1是左表, b1是右表,left join为左表数据全部显示

select * from a1 left join b1 on a1.part_id = b1.id;
  • right join连表

    a1是左表,b1是右表,right join为右表数据全部显示

select * from a1 right join b1 on a1.part_id = b1.id;
  • inner join连表

    a1是左表, b1是右表, inner join,把出现null的行隐藏

select * from a1 inner join b1 on a1.part_id = b1.id;

上下连表 union

  • union连表,去重
select id, name from tb1
union
select num, sname from tb2;
  • union all连表,不去重
select id, name from tb1
union all
select num, sname from tb2;

临时表查询

select * from (select * from score where num > 60) as B;
标签: mysql

benojan 发布于  2022-11-2 23:48 
article cover

mysql的数据类型 mysql

数字类型

  1. 整数
类型 范围
tinyint -128 ~ 127
tinyint unsigned 0 ~ 255
smallint -32768 ~ 32767
smallint unsigned 0 ~ 65535
int -2147483648 ~ 2147483647
int unsigned 0 ~ 4294967295
bigint -9223372036854775808 ~ 9223372036854775807
bigint unsigned 18446744073709551615
  1. 小数
类型 说明
float 单精度
double 双精度
decimal (总位数, 小数位数):十进制小数(字符串法存)

字符串类型

(定长往前放,变长往后放)

类型 长度、特点
char(10) 255,速度快 (定长)
varchar(10) 255,节省空间,速度慢 (变长)
text 65535
mediumtext 16777215
longtext 4294967295

时间类型

类型 格式
datetime YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59)
date YYYY-MM-DD(1000-01-01/9999-12-31)
time HH:MM:SS('-838:59:59'/'838:59:59')
year YYYY(1901-2155)
timestamp YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037年某时)

枚举、集合类型

类型 用例 说明
enum sex ENUM('male', 'female') 单选
set col SET('a', 'b', 'c', 'd') 多选,如 'a, b''c, b'
标签: mysql

benojan 发布于  2022-11-2 23:26 
article cover

mysql的索引与外键 mysql

主键索引

约束:不能重复且不能为空;
加速查找

id int unsigned auto_increment primary key,
primary key (id,name), # 多列主键

唯一索引

约束:不能重复,可以为空;
加速查找

unique 约束名 (id, name),

外键

约束:值必须是另一张表的主键

constraint fk_user_depar foreign key(department_id) references department(id),

外键的变种:

  1. 一对一

    blog表中,fk(user_id) + unique(user_id)

    如:一个博客属于一个用户,一个用户只能有一个博客

  2. 一对多

    student表中,fk(class_id)

    如:一个学生属于一个班级,一个班级可以有多个学生

  3. 多对多

    fk(aid) + fk(bid) + unique(aid,bid)

    如:关系表、组合配对

标签: mysql

benojan 发布于  2022-11-2 23:09 
article cover

mysql的数据库操作 mysql

show databases; -- 显示数据库
create database db1; -- 创建数据库
create database db1 default charset utf8mb4; -- 创建数据库之指定字符编码
drop database db1; -- 删除数据库
use db1; -- 进入数据库
标签: mysql

benojan 发布于  2022-11-2 23:03 
article cover

mysql的用户操作 mysql

  1. 创建用户

    create user '用户名'@'IP地址' identified by '密码';
    
    create user 'zhangsan'@'192.168.1.1' identified by '123123';
    create user 'zhangsan'@'192.168.1.%' identified by '123123';
    create user 'zhangsan'@'%' identified by '123123';
  2. 用户授权(权限、人)

    show grants for '用户'; -- 查询权限
    grant 权限类型 on 数据库.表 to '用户'@'IP地址'; -- 授权
    revoke 权限类型 on 数据库.表 from '用户'@'IP地址'; -- 取消权限
    
    grant select,insert,update on db1.t1 to 'zhangsan'@'%';
    grant all privileges on db1.* to 'zhangsan'@'%';

    常见的权限类型:

    权限类型 中文解释
    all privileges 所有权限
    select 读取
    delete 删除
    update 更新
    create 创建
    drop 删除数据库、数据表
  3. 修改用户名

    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
  4. 修改密码

    alter user '用户名'@'IP地址' identified by '新密码';
  5. 删除用户

    drop user '用户名'@'IP地址';
  6. 查看用户

    use mysql;
    select user, host from user;
标签: mysql

benojan 发布于  2022-11-2 23:00 
article cover

mysql的环境搭建 mysql

  1. 下载 mysql.zip

  2. 解压缩 mysql.zip 至 c:\mysql(注意目录不要有中文)

  3. 添加环境变量:配置Path,添加mysql下的bin目录

  4. 初始化

>mysqld --install # 安装服务
>mysqld --initialize-insecure # 初始化mysql
>net start MySQL # 开启mysql服务
  1. 登录并更改密码
>mysql -u root -p
# 输入生成的密码

mysql>alter user 'root'@'localhost' identified by '123' # 更改新密码为:123

mysql>exit

>mysql -u root -p
# 输入新密码
  1. 关闭mysql服务
>net stop MySQL
标签: mysql

benojan 发布于  2022-11-2 22:57