- mysql环境
- mysql数据库命令
- mysql用户管理
- mysql权限管理
- mysql数据类型
- 数据库行级别增删改查
- 视图
- 触发器
- 存储过程
- 函数
- 事物
- 索引
- json类型(mysql 8新增)
- 外键联级
mysql分类
DDL:DDL语句表示数据定义语言,create、drop、alter
DML:数据操纵语句,select、insert、update、delete
DCL:数据控制语句,grant
mysql的标准安装(linux的centos7下安装mysql8)
cd /usr/local
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz mysql
groupadd mysql
useradd mysql -g mysql
cd mysql
mkdir data
chown -R mysql:mysql data #设置权限
bin/mysqld --initialize --user=mysql --datadir /usr/localmysql/data '#初始化mysql
#返回一段文字 记住最后的密码,是用来登录的,2020-01-22T07:14:55.343332Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: %aF&UozMf1%V
cp support-files/mysql.server /etc/init.d/mysql #启动文件设置
vi .bash_profile #设置环境变量
source .bash_profile
mysql -uroot -p
alter user user() identified by '你要输入的密码'; #把当前用户重置密码
扩展
自定义my2.cnf
datadir=/usr/local/mysql/data basedir=/usr/local/mysql port=3308 socket=/tmp/mysql3308.sock mysqlx_port=33070 #在5.6以后启动mysql后会启动mysqlx插件来监听mysql mysqlx_socket=/tmp/mysqlx33070.sock
之后启动数据库
bin/mysqld --defaults-file=/etc/my2.cnf --user=mysql& #指定my2.cnf启动mysql
alias mysql2='/usr/local/mysql/bin/mysql'
mysql2 -uroot -p -S /tmp/mysql3308.sock #启动另外一个mysql , -S为指定前面设置的sock文件
登录成功
mysql8无法连接navicat问题
CREATE USER 'rainbol'@'%' IDENTIFIED BY '123456'; -- 新增一个用户 grant all privileges on *.* to 'rainbol'@'%'; -- 将该用户的设置所有权限 alter user 'rainbol'@'%' identified with mysql_native_password by '123456'; -- 设置密码改为native方式 FLUSH PRIVILEGES; -- 是否是阿里云服务器的话还要看下是否控制台开启3306端口访问权限
-- 服务器端检查
netstat -nlpt
tcp6 0 0 :::6379 :::* LISTEN 10074/./redis-serve
tcp6 0 0 :::33060 :::* LISTEN 1702/mysqld
tcp6 0 0 :::3306 :::* LISTEN 1702/mysqld
发现3306端口没有对外访问，到my.cnf中添加bind-address=0.0.0.0,并重启mysql服务
-- vim /etc/sysconfig/iptables
-A INPUT -p tcp -m tcp --dport 3306 -j DROP
发现了不知道谁禁用了，-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT 修改变成开启
之后重启防火墙service iptables restart
彻底删除mysql
https://www.cnblogs.com/nicknailo/articles/8563456.html
mysql 初始化命令 执行后解压mysqlbin目录下一大堆的可执行文件(创建相关架构数据)
mysqld --initialize-insecure
执行mysql服务端命令
mysqld
执行mysql客户端命令
mysql -u root -p 初始化密码为空
mysql -u rainbol -h 127.0.0.1 -p123456 -u用户名,后台默认localhost 加参数-h表示指定主机登录,-p密码
将mysql服务制作成Windows服务(这样做方便省事)
服务制作 mysqld路径 --install
服务启动 window: net start mysql
linux: mysql.server start /etc/init.d/mysql start
服务终止 net stop mysql
服务撤销 mysql路径 --remove
创建数据库 CREATE DATABASE 库名;
CREATE DATABASE 库名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #utf-8
CREATE DATABASE 库名 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #gbk
create database rainbol_test default character set utf8mb4;
create database if not exists rainbol_test default character set utf8mb4; #如果不存在就创建，有警告但是不会报错
查看数据库 show databases;
其他
show version(); 查看当前版本
show user(); 查看当前用户
show now(); 显示当前时间
show create database rainbol_test; 查看数据库创建的语句
进入数据库 use 库名;
退出数据库 exit. 或则 q
删除库/表 drop database 库名; drop table 表名; drop database if exists 表名; #如果表名存的情况下就删了
修改表 alter table 老表名 rename 新表名;
清空表 delete from 表名; truncate table 表名; (truncate删除速度快,且初始化自增数据,delete不会初始化自增数据)
查看表 show tables;
查看表结构 desc 表名; show columns from 表名；
刷新 flush privileges; 如果权限表或者mysql内部表做修改时,必须手动刷新或者重启mysql
创建表结构 create table 表名(id int,username varchar(20),password varchar(10));
创建表
create table 表名 like new_table; #复制原表内容新增一个与其一模一样的表结构(不是数据)
create table 表名1 as select * from 表名2; #拼表,把select查询到的结构变成新创建表的表结构
create table if not exists stu( username varchar(10), sex enum('男','女','保密'), money decimal(8,2),content text)engine=myisam default charset=utf8mb4;
zerifill 该属性只能用于设置数值类型，效果是该字段前面会补0
unsigned 该属性只能用于设置数值类型，不允许数值出现负数的情况且被修饰的该字段最大存储长度增加一倍，如果不希望数值在插入中出现负数，可以用这个修饰
create table if not exists stu1( tinyint unsigned ) engine=myisam default charset=utf8mb4;
引擎 create table 表名(xxx int)ENGINE=InnoDB DEFAULT CHARSET=utf8create table if not exists stu2( username varchar(10), sex enum('男','女','保密'), money decimal(8,2) zerofill ,content text,num tinyint unsigned )engine=myisam default charset=utf8mb4; #设置引擎 原子操作,表示执行按照事务进行,出现异常情况会回滚
null 表示空,非字符串 not null 不可为空 例子: create table tp(id int(2) not null)
default 添加默认值 例子: create table tp(id int default 10)
设置默认值 alter table 表名 alter 列名 set default 设置值;
删除默认值 alter table 表名 alter 列名 drop default;
主键 一列或者一组列,其值能唯一区分表中的每一行,也就是唯一标示每一行(行组)成为主键,主键可以加索引来快速查找
表中每一列都可以为主键但是要满足条件1.任意两行都不具有相同的主键值,2.每个行都必须有主键值(不能为null)
内置约束/索引 --加速查找 约束查询行为
primary key 主键索引(一张表只能有一个主键,唯一且不能重复,不能为null,一般自增列设为主键)
create table tp(id int primary key,username varchar(20),password varchar(20))engine=innodb default charset=utf8;
联合主键约束 例子: create table tp(id int,username varchar(20),password varchar(20),primary key(id,username)) #把id和username联合多列组成约束主键
添加主键 alter table 表名 add primary key(列名);
删除主键 alter table 表名 drop primary key;
alter table 表名 modify 列名 int,drop primary key;
constraint 外键约束(从表与主表做关联,之后插入数据可以进行约束),要注意的是，主表和从表必须使用相同的存储引擎(innodb)，禁止使用临时表并且主键列和外键列必须具有相似的数据类型，其中数字的长度或是否有符号位必须相同，而字符的长度则可以不同
#外键实例-创建主表 CREATE TABLE `stu_primary` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL, `password` varchar(255) NOT NULL DEFAULT '123456', PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci #创建从表 create table if not exists stu_constraint (id int auto_increment primary key ,constraint_id int ,email varchar(20) null,foreign key (constraint_id) references stu_primary (id));
#查询
SELECT * from stu_primary as a join stu_constraint as b ON a.id = b.constraint_id
添加外键 alter table 表名 add constraint 外键名称 foreign key 从表(字段) references 主表(字段);
删除外键 alter table 表名drop foreign key 外键名称
外键参照操作 create table if not exists stu_constraint1 (id int auto_increment primary key ,constraint_id int ,email varchar(20) null,foreign key (constraint_id) references stu_primary1 (id) on delete cascade)
cascade 表示如果删除主键列后对应外键列也会删除，当然还有update
set null 主从表删除或者更新，并设置从表中的外键列位null，必须保证从表列不是not null
no action 标准sql的关键字，在mysql中与restrict相同
unique 唯一索引
增加唯一索引 alter table 表名 add unique(id);
删除唯一索引 alter table 表名 drop index id;
AUTO_INCREMENT 自增列 create table 表名(id int auto_increment,username varchar(20),password(20))engine=innodb default charset=utf8;
设置自增 alter table 表名 auto_increment = 1
删除自增 alter table 表名 change id id int auto_increment primary key;
如果列的属性还带有AUTO_INCREMENT，那么要先将这个列的自动增长属性去掉，才可以删除主键,alter table 表名 modify id int,drop primary key;
comment 备注 create table 表名(id int auto_increment primary key comment '字段id')
修改库
alter database rainbol_test character set gbk; #修改编码
修改表
alter table 表名 add 列名 类型
删除列
alter table stu_primary drop aa ,drop bb ,drop cc; -- 删除多列
修改/新增列
alter table 表名 add 新增字段名 before 字段 int not null; -- 在字段之前增加字段（after则在之后），alter table 表名 add 新增字段名 int not null first -- 添加在字段的第一列
注意在添加多列时需要用括号,并且排列按照顺序向后排，不能将多列插入在中间 alter table stu_primary add (aa int not null,bb varchar(255) null);
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名,类型
alter table stu_primary1 change password password1 char(10) default 100;
新增/修改数据表属性
alter table 表名 alter 字段名 set default 10;
删除数据表属性
alter table stu_primary alter password drop default;
删除主键索引，唯一索引，外键索引
alter table stu_primary drop index aa;
创建用户 create user '用户名'@'ip地址' identified by '密码';
例子: create user 'rainbol'@'192.168.1.1' IDENTIFIED BY '123456'; 踩坑如果创建好了用户,在navicat中test.user表中修改该用户的host变为'%'无效
删除用户 drop user '用户名'@'ip地址';
修改用户 rename user '用户名'@'ip地址' to '新用户名'@'ip地址';
修改密码 set password for '用户名'@'ip地址' =Password('新密码');
授权 grant 权限 on 数据库.表名 to '用户名'@'ip地址'
库名.* 库名中所有的表
库名.表名 库名中指定的表
*.* 所有库所有表
通配符
用户名@IP地址 用户只能在改IP下才能访问
用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
用户名@% 用户可以再任意IP下访问(默认IP地址为%)
权限明细
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
bit 二进制位,长度1-64位 默认1位
tinyint 默认有符号,4位 添加无符号 create table 表名(num int unsigned);
有符号 -128 ~ 127
无符号 0 ~ 255
smallint 默认6位
有符号 -32768 ~ 32767
无符号 0 ~ 65535
int 默认11位,整数
有符号 -2147483648 ~ 2147483647
无符号 0 ~ 4294967295
bigint 默认20位
有符号 -9223372036854775808 ~ 9223372036854775807
无符号 0 ~ 18446744073709551615
deciaml 精确十进制小数,底层内存是字符串存储
create table 表名(num decimal(10,2)) #10表示总共10位数,2表示十位中小数占2位,如果插入超过位数,自动四舍五入
float 浮点型数值越大不精确
double 浮点型数值越大不精确
字符串
char 255位,定长,开辟指定空间,浪费空间,但是查找速度快
varchar 255位,变长,节省空间,查找速度相对慢
text 保存变长的 大字符串,65535位
mediumtext 16777215位
longtext 4294967295位
二进制数据 用不着,一般都是字符串形式代替二进制
tinyblob blob mediumblob longblob
时间
date YYYY-MM-DD (1970-01-01/1998-12-31)
time HH:MM:SS ('231:23:42'/'231:55:50')
datetime YYYY-MM-DD HH:MM:SS
timestamp YYYYMMDD HHMMSS TIMESTAMP DEFAULT 'yyyy-mm-dd hh:mm:ss' COMMENT '注意加引号'
DEFAULT CURRENT_TIMESTAMP
表示当插入数据的时候，该字段默认值为当前时间
ON UPDATE CURRENT_TIMESTAMP
表示每次更新这条数据的时候，该字段都会更新成当前时间
这两个操作是mysql数据库本身在维护，所以可以根据这个特性来生成【创建时间】和【更新时间】两个字段，且不需要代码来维护
enum 表示枚举值 在内存中创建自定义的一些值,往后在插入时,只能在这些值中选择,插入时值只能选择一个,相当于单选
create table(id int,name varchar(20),sex ENUM('男','女'))
set 表示集合类型 集合可以选择多选
create table(id int,name varchar(20),set('a','b','c','d'))
增
插入数据 insert into 表名 values ( aa,bb,cc,dd,ee) -- -这个方式字段列必须写全,不然会报错
插入一条数据 insert into 表名(id,username,password) values(1,'rainbol','123456');
插入多条数据 insert into 表名(id,username,password) values(1,'rainbol','123456'),(2,'rainbol2','67890'),(3,'rainbol3','234567 ') ;
指定另外一张表的数据插入进表中 insert into 表名1(id,username) select (插入的列名1,插入的列名2) from 表名2
改
update 表名 set 值 where 条件 --单表修改
update 表名1,表名2 set 表名1.username = 表名2.username where 条件 --多表修改
update 表名1,表名2 set 表1.username=(select * from 表3 where 条件) where 条件
删 delete from 表名 where 条件 --单表删除
delete 表名1,表名2 from 表名1 inner join 表名2 inner join 表名3 where 条件 --多表删除是从一个表里删除满足条件的数据
查
select * from 表名;
select (1+1)/2; --甚至可以计算,也可以把算出来的结构当成字段显示select *,(1+1) from 表名
select count(*) , count(`id`) from user -- 要注意的是count（*）查询的行如果为空行也就是null的会被记录，而count字段名是不会记录的，一定要注意
查询扩展
SELECT * FROM information_schema.columns WHERE column_name='userId';
TABLE_SCHEMA字段为db的名称（所属的数据库），查询所有数据库中有userId的字段列出,这对于定位问题是一个神查询。
变量赋值
-- 局部变量,只能用在begin/end语句块中，比如存储过程中的begin/end语句块。其作用域仅限于该语句块 -- set aa = 18; -- @ 设置用户变量,不用提前声明直接用 select @bb:=123; -- 冒号加等号表示赋值,冒号一定要加,不然会拼在一起,这条语句表示赋值并查询出来 set @bb=345; -- 同理和上面类似,冒号可加可不加 -- @@ 会话变量,mysql每连接一个客户端都会建立一个会话变量,所以每个连接的会话变量是独立的,连接断开再创建一个连接变量就可能不一样了 show session variables; set @@auto_increment_increment = 1; show session variables; select @@auto_increment_increment; -- 查询方式一 select @@session.auto_increment_increment; -- 查询方式二 -- global 全局变量与设置,当mysql启动时,读取默认的配置变量,之后读取配置文件的变量,如果在mysql命令行设置了global但是mysql重启了,配置就会消失,可以在配置文件中设置,通常是my.cnf show global variables; set global sql_warnings=ON; -- global不能省略 set @@global.sql_warnings=OFF; select @@global.sql_warnings; show GLOBAL VARIABLES like '%sql_warnings%';
条件
where select * from 表名 where 条件
between select * from 表名 where id between 1 and 20; 1到20符合范围
in select * from 表名 where id in (1,2,3) 取id为1,2,3的数据 not in 不包含
select * from 表名 where id in (select * from 表名2) 取其他表中包含的数据
通配符 select * from 表名 where username like 'rain%' 通配符%表示取表中username是rain开头的后面跟多少字符都可以
select * from 表名 where username like 'rain_' 通配符_ 表示取表中username是rain开头后面跟一个字符
limit select * from 表名 limit 5 取前5行数据
select * from 表名 limit 2,3 2表示起始位置,从第二行开始取 3表示取三行
select * from 表名 limit 3 offset 2 和上面差不多
limit分页 select * from 表名 limit 10,10; 取第十页数据,取10行
limit表示分页,全表扫描用limit 1,则一行一行扫描的,效率很高
分页优化:取下一页:where nid >xx limit 10;查找表中nid当nid>xx的时候取10行数据,xx表示用户点击第二页,xx就是第一页最后的那条nid
取上一页:where nid <xx order by nid desc limit 10 拿到nid小于xx的所有数据,进行反转并取前十条
排序 select * from 表名 order by 列名1 asc 默认asc ,根据列名1从小到大排序
select * from 表名 order by 列名 1 desc 根据列名1从大到小排序
select * from 表名 order by 列名1 asc,列名2 desc 先按照列名1规则排序,如果相同则按照列名2规则排序
分组 select * from 表名 group by 列名 对指定列名中相同的字段合并成一条,
聚合函数 select count(id),min(username),max(password),sum(nid) from 表名 group by count; select后面聚合函数中的名字是自定义加上的,不一定是实际列名
min()最小 max()最大 sum()求和 count()个数 avg()平均
having 如果想对聚合的结果做筛选就用它 select sum(nid) from 表名 group by count where sum(nid)>2;
group by必须在where之后order by之前
联合 union
select * from 表名1 union select * from 表名2 把两张表的数据放在一个结果集中,union自动会把两张表中重复的数据去重,如果不想要去重,写union all
连表操作 首先要分清主表从表
select * from 表名1,表名2 where 表名1.uid =表名2.uid;
左连接 按照左边表关联
select * from 表名1 left join 表名2 on 表名2.uid=表名1.uid
右连接 按照右边表关联
select * from 表名1 right join 表名2 on 表名2.uid = 表名1.uid
内连接 左右关联,相当于过滤null
select * from 表名1 inner join 表名2 on 表名2.uid =表名1.uid
条件语句及判断(流程控制函数)
--三目运算
select case 字段名 when xxx>20 then 1 else 0 end from 表名 -- 相当于三元运算 当xxx>20,值的1 否则为0
select case 字段名 when 'a' then 'hhh' when 'b' then 555 when 'c' then 777 else 213 end from 表名 -- 类似代码的 if else if else
select when 字段名='a' then 'hhh' when 字段名='b' then 555 when 字段名='c' then 777 else 213 end from 表名 --和上面一样的结果
--if条件
select if(字段名=1,2,3) from 表名; -- 如果字段名=1成立走2.否则走3
delimiter \ create procedure tp() BEGIN if a = 0 THEN SELECT * from tp1;ELSEIF a = 2 THEN SELECT * from tp2; ELSE SELECT * from tp3; END IF; END\ delimiter;
--while循环
delimiter \ create procedure tp() BEGIN declare code int; set code = 0; while code <10 do select code;set code = code +1; end while; END\ delimiter;
--repeat循环
delimiter \ create procedure tp() BEGIN declare code int; set code = 0; repeat select code;set code = code +1; until code >10 end repeat; END\ delimiter;
--loop循环
delimiter \ create procedure tp() BEGIN declare code int; set code = 0; loop_lab:loop select code;set code = code +1; if code>10 then leave loop lab;end if; end loop; END\ delimiter;
--动态执行sql语句 通过参数传入的字符串执行sql语句,mysql内部执行自动防止sql注入
delimiter \ create procedure tp() BEGIN declare code int; set @code = 0; prepare fuc from 'select * from tp where username = ?'; --创建一个fuc变量代指后面的字符串 mysql问号表示占位符 execute fuc using @code; --执行字符串格式化把@code的值,赋值给占位符,注意变量@code必须有@符号 deallocate prepare fuc; --执行fuc END\ delimiter;
视图 视图的作用是创建一个存储的变量来代替sql语句,当执行变量时,mysql在内存查找到对应变量中的sql语句做临时的增删改查,视图表与原表绑定后一旦原表变化,查询到视图也会变化,也理解成视图表可以代替原表做查询,但是如果原表修改了表结构,如添加了一个字段,视图表是按照原来定义的原表结构查询的,所以如果查询就是null了,这点要注意,select *定义时会把*变成对应的字段,如果结构修改也只是对应原表结构的字段,不会自动添加新结构的字段,同理删除也是一样
创建视图
create view 视图名 as sql语句 #这样就创建好了一个视图
删除视图
drop view 视图名
修改视图
alter view 视图名 as sql语句
使用视图
select * from 视图名
视图总结:
1.视图表对于原表结构删除或新增必须重新定义视图表
2.视图的优先级,如果视图中定义了查询语句如 create view view_1 as select * from user order by uid ; 当查询视图时语句 select * from view_1 order by uid desc; 这样生效的是外层查询语句的order by
3.视图不推荐多表查询,可能会失败的情况
触发器 触发器是在我们执行sql前后时想要触发的某种事件
创建触发器
# 我们可以建立的6种触发器
格式:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
--tigger_name:触发器名称,自定义
--trigger_time:触发时机,可加before,after
--trigger_event:触发条件,可加insert,update,delete
--tb1_name:触发表名,即在哪张表连接触发器
--trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句
# 插入前 CREATE TRIGGER tp1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tp2 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tp3 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 CREATE TRIGGER tp4 AFTER DELETE ON tb1 FOR EACH ROW BEGIN ... END # 更新前 CREATE TRIGGER tp1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 CREATE TRIGGER tp2 AFTER UPDATE ON tb1 FOR EACH ROW BEGIN ... END
删除触发器
DROP TRIGGER 触发器名;
使用触发器
--触发器无法由用户调用触发,而是由用户行为[增删改]触发 insert into 表名(字段名) values(值)
存储过程 一个sql语句的集合,如果有很长的sql语句,执行语句会占用性能,而存储过程会指定函数,通过函数自动解析找到mysql中sql语句,而这是视图所做不到的
创建存储过程
delimiter // create procedure fuc() BEGIN sql语句 END // delimiter; fuc相当于我们调用函数的函数名称 delimiter后面跟自定义字符//,这个是可以自定义的,相当于前端的闭合标签,mysql执行语句中遇到分号会立即提交,所以我们临时修改终止符,再结尾再改回来就行了
procedure fuc() 存储过程中也指定三个参数,没有返回值,三个参数充当了特定返回值的效果
in 内部使用,不能返回值,只传值
例子: delimiter // create procedure fuc(in args int) BEGIN sql语句 END // delimiter; --in 表示执行in参数 args自定义形参 int 指定int数据类型
call fuc(100) --执行fuc方法并带实参传入,终端调用
out 外部使用,想要返回值时,用out设置关键字,只获取值
例子: delimiter // create procedure fuc(out args2 varchar(20)) BEGIN sql语句 END // delimiter; --out 表示不接受实参的值,接收实参的变量名,所以参入的变量为@a,abc会自动抹去
@a = 'abc'
call fuc(@a) --将变量名a为实参参入
select @a; --查看返回结果,当存储过程执行完成后,@a就sql语句的返回结果
inout 外内部同时使用,既可以传值也可以获取值
例子: delimiter // create procedure fuc(out args2 varchar(10),inout args3 varchar(20)) BEGIN sql语句 END // delimiter;
执行存储过程 call fuc() --call加上函数名()就相当于执行里面的sql语句
修改存储过程 直接删除,不推荐修改
删除存储过程 drop procedure 存储过程名
存储过程内置sql格式
DECLARE tep int; --DECLARE存储过程内部声明变量时必须使用,创建一个int类型的tep变量名
set tep = 'abc' --set存储过程内部声明赋值时必须使用,abc赋值给tep
内置函数
自定义函数
通过存储过程创建自定函数
--自定义函数 delimiter $$ create function 函数名(ia int, ib int) returns int begin return ia + ib; end $$ delimiter ;
函数的删除
drop fuction myfuc3;
函数的执行
show create function 函数名; --查询创建函数语句 select function 函数名;
md5加密
insert into stu_primary1(username,paswword) values('小r',md5('123456',1)); -- 直接md5加密
CONCAT 字符串拼接
select concat(username,'--',password,'--',id,'--') as aaa from user; -- 将所有字段拼接起来
CONCAT_WS 字符串拼接
select concat_ws('/','username','id','None') from user; -- 将第一个参数的字符串拼接到后面所有参数身上 最后结果为：username/id/None
FORMAT 数字格式化
select format(12323.1324,2); -- 参数一是数字，参数二是位数 返回12,323.13 保留两位小数
lower/upper 转化大小写
left/right 获取字符串左右字符
select left('1234sdfs',3); -- 截取左侧3位字符，返回123
length()获取长度
trim/ltrim/rtrim 删掉制定前后字符串 不加参数默认删空格
select trim(leading '?' from '???324sf???'); -- 删除前面的？
select trim(trailing '?' from '???324sf???'); -- 删除后面的？
select trim(both '?' from '???324sf???'); -- 删除前后的？
replace 替换
select replace('???324sf???','?',''); -- 参数一被替换字符串，参数二被替换字符，参数三被替换的字符串 返回324sf
update user set name = replace('name',',',''); -- 将user表中name字段为逗号的去除
substring 截取
select substring('lasdf',-1) -- 截取字符串正向截取也可以反向截取，返回f
now 当前精确时间
curdate查看当前时间
date_add 时间增减
select date_add('2020-11-02',interval -365 day);
datediff 时间差值
select datediff('2020-11-02','2020-12-03');
date_format时间格式化
last_insert_id 拿到最后自增id
group_concat 把结果输出到一行中
事物 一旦出现错误会回滚代码块,如果不出错就不会执行异常代码块
delimiter \ create PROCEDURE tp( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tp1; insert into tp2(name)values('12345'); COMMIT; -- SUCCESS set p_return_code = 0; END\ delimiter ;
set autocommit=0; --set autocommit=0;其实就是start transaction,写其一就不用写其二,事务不会自动提交,必须后面加commit,如果设置了set autocommit=1;就后面不用加commit了.如果当前有lock操作,执行,start transaction会触发解锁操作,即unlock table操作
rollback; --rollback;表示回滚到执行事务之前的数据
commit and chain表示重新开启一个新的事务,commit and release表示事务回滚断开和客户端的连接
索引 是由原数据文件和索引文件组成,它们之间存在关联,索引文件把原数据的索引列转化为一个数字,并按照b_tree算法放置,查找过程是由sql语句触发,当执行索引时,mysql通过索引表的算法高效计算出索引文件中的数字,而又因为数字的指针指向硬盘中的原数据文件,从而找到目标的行位置
索引的优点及代价
优点:约束和加速查找
代价:索引之所以能在查询时速度快,因为创建了另一张索引表,当做增删改操作时,会在原数据表和索引表同时增加数据,删除数据,修改数据,并占用数据库额外的资源,所以索引不能用于频繁修改数据库的操作
索引的种类
1.普通索引 -快速查找
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) )
create index index_name on table_name(column_name) --创建表时添加索引
drop index_name on table_name; --删除索引
show index from table_name; --查看索引
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。
create index ix_extra on in1(extra(32));
2.唯一索引 -加速查找,约束列数据不能重复,可以为null
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) create unique index 索引名 on 表名(列名) --创建索引 drop unique index 索引名 on 表名 --删除索引
3. 主键索引 -加速查找,约束列数据不能重复,不能为null
create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(nid), index ix_name (name) ) alter table 表名 add primary key(列名); --创建索引 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; --删除索引
4.组合索引(多列创建索引)
普通组合索引 无约束
支持联合唯一,快速查找,约束列数据不能重复,不能为null 两列数据同时不能相同,如果只有一列为同一数据是可以的
create table in3( nid int not null auto_increment primary key, username varchar(32) not null, password varchar(64) not null, extra text ) create index ix_name_email on in3(username,password); --创建组合索引 组合索引遵循最左匹配规则,如果单独搜索password直接报错,只能匹配最左边的 username and password -- 使用索引 username -- 使用索引 password -- 不使用索引
组合索引能把两个索引组成一个,所以依组合索引节省空间,但是遵循最左匹配,如果匹配右边就失败了,,根据业务需求,用户表校验用户名和密码可以用此方法
覆盖索引
select id from 表名 where id >10; 这种情况下直接就去索引表中查找到对应的值,不用得到数字去原数据表,这种方式成为覆盖索引
合并索引
select * from 表名 where username ='rainbol' or password = '123456'; 两个索引username和password 之间合并索引查询
执行计划 帮助我们知道当前sql语句执行的运行状况,相对准确但是还是有偏差(如limit就还是显示全表扫描)
explain select * from 表名;
ALL 当出现 type字段为ALL,表示全表扫描
INDEX 当出现type字段为INDEX,表示索引表全表扫描
range 当出现type字段为range,表示当前查找是对索引列进行范围查找
注意:!= 和 > 符号 会对range影响,也就是说用!=和>符号不走索引
INDEX_MERGE 当出现type字段为INDEX_MERGE,表示索引走合并索引
REF 当出现type字段为REF,表示根据索引查找一个或多个值(普通索引)
EQ_REF 当出现type字段为EQ_REF,表示当需要连接时使用primary key或者unique类型(唯一查找时出现)
CONST 当出现type字段为CONST,其效率最高,如select id from 表名 where id = 1;就是覆盖查询
SYSTEM 当出现type字段为SYSTEM,其效率最高
其他不走索引(采坑)
like '%xxx' 这样查询不会走索引 正确应该是 like 'xxx%'
函数() sql查询尽量不要用函数做计算,这样是不走索引的,因为如果改变了索引值就不可能找到了
or条件下 select * from 表名 where username ='rainbol' or password = '123456' 如果username设置了索引,password没有设置索引,那么这条语句不会走索引
数据类型不一致,不会走索引
!= 普通索引和主键是可以走索引的 ,其他不走索引 select * from 表名 where username != 'rainbol ' select * from 表名 where id != 'xxx';
> 主键或者索引是整数类型,可以走索引,其他不走索引
order by 如果select映射后面不跟索引值,不走索引,跟索引值就走索引 select id from 表名 order by id desc;走索引 select * from 表名 order by id desc;#不走索引
组合索引最左前缀 然后是设置了组合索引,注意如果单独查询右边是不走索引的
其他注意事项
避免使用 *
创建表时能使用char的尽量使用char,避免varchar
表字段顺序,固定 长度的字段优先(char int往前面放)
组合索引尽量多使用,代替单列索引
尽量使用短索引,如classmate_people,可以使用classmate做索引
索引列下的值重复多,基本都一样,性别,不适合做索引,可以但是没有必要
慢查询 通过配置文件或者mysql内存的慢查询设置,能将sql执行超过所设置的时间的语句记录下来放到xx-slow.log中(查询日志记录路径可以设置)
1.修改配置文件一般为my.ini
查看:show variables like '%longs%';
其他当前数据库状态的查看:show status like 'update' 或 show status like 'com_Select' 或 show status like 'connections'
2.方法一:
添加slow _query_log = ON -- 开启慢查询
log_queries_not_using_indexes = NO 开启sql命中索引写日志
long_query_time = 0.5 开启查询慢0.5,秒
slow_query_log_file = xx/xx.log 文件记录路径
方法二:
show variables like '%query%'; 表修改
3.启动服务加慢查询
mysqld --default-files=mysql配置ini路径
mysql性能优化
https://www.cnblogs.com/RainBol/p/9530012.html
mysqldump命令 官方自带的命令行工具
mysqldump -u用户名 -p密码 -P端口 -database 数据库名 > 文件名.sql
mysqldump -uroot -p123456 --socket=/opt/lampp/var/mysql/mysql.sock -P3306 --all-databases >223.sql 全数据 库备份
mysqldump -uroot -p123456 --databases test >321.sql 指定数据库备份
mysqldump -uroot -p123456 test pay >111.sql 指定表
mysqldump -uroot -p123456 -P3306 test pay |gzip>gzipsql.gz 直接备份并压缩
**进入mysql use test source /xx/xxx/xx.sql; 恢复表或数据库
mysql -uroot -pchenzuanyi64 <123.sql 命令行还原数据库
gunzip < gzipsql.gz | mysql -uroot -p123456 test 还原压缩的数据库
创建json类型
create table json_test(id int auto_increment primary key,content json not null);
json_array返回json数组
select json_array('a','b','c',1,3,4,5,6) --返回["a", "b", "c", 1, 3, 4, 5, 6]
json_object返回json对象
select json_object('username','password','xiaohong','1'); -- 返回{"username": "password", "xiaohong": "1"}
json_pretty漂亮的json打印
set @data_json = json_object('username','password','xiaohong','1');
select json_pretty(@data_json);
插入json数据
insert into json_test(content) values(json_object('username','password','xiaohong','1'));
json_array_insert数组指定插入数据
下标插入：
set @bb = json_array('a','b','d'); --["a", "b", "d"]
select json_array_insert(@bb,'$[2]','c'); --["a", "b", "c", "d"] 参数一是json数据，参数二是下标，从0开始的下标，参数三是插入的值
select json_array_insert(@bb,'$[2]','c','$[3]','dd'); --下标插入都可以插入多个值
json_array_append
set @bb = json_array('a','b','d'); --["a", "b", "d"]
select json_array_append(@bb,'$[1]','bb'); -- ["a", ["b", "bb"], "d"] 插入后将整体变成一个数组
set @aa = json_object('id','1','username','xiaoming','password','123456'); --{"id": "1", "password": "123456", "username": "xiaoming"}
select json_array_append(@aa,'$.password','654321'); -- {"id": "1", "password": ["123456", "654321"], "username": "xiaoming"}
json_insert
key插入：
set @aa = json_object('id','1','username','xiaoming','password','123456'); --{"id": "1", "password": "123456", "username": "xiaoming"}
select json_insert(@aa,'$.id','2'); -- 当想要插入相同当值时数据不会改变
select json_insert(@aa,'$.age','18'); --{"id": "1", "age": "18", "password": "123456", "username": "xiaoming"} 当json数据中没有时才会插入成功
json_set 替换现有都值，增加不存在都值
set @bb = json_array('a','b','d'); --["a", "b", "d"]
select json_set(@bb,'$[1]','bb'); -- ["a", "bb", "d"]
set @aa = json_object('id','1','username','xiaoming','password','123456'); -- {"id": "1", "password": "123456", "username": "xiaoming"}
select json_set(@aa,'$.password','654321'); -- {"id": "1", "password": "654321", "username": "xiaoming"}
json_replace 仅替换现有都值,如果不存在不会增加
set @aa = json_object('id','1','username','xiaoming','password','123456'); --{"id": "1", "password": "123456", "username": "xiaoming"}
select json_replace(@aa,'$.password','hhh','$.lll','6666'); --{"id": "1", "password": "hhh", "username": "xiaoming"} 可以看到新增都lll不会添加
json_remove 删除指定字段
set @aa = json_object('id','1','username','xiaoming','password','123456'); --{"id": "1", "password": "123456", "username": "xiaoming"}
select json_remove(@aa,'$.password'); -- {"id": "1", "username": "xiaoming"}
set @bb = json_array('a','b','d'); --["a", "b", "d"]
select json_remove(@bb,'$[1]'); -- ["a", "d"]
json_extract 获取表中json类型都指定值
mysql> select * from json_test; +----+-------------------------------------------+ | id | content | +----+-------------------------------------------+ | 1 | {"username": "password", "xiaohong": "1"} | +----+-------------------------------------------+ 1 row in set (0.03 sec)
select json_extract(content,'$.username') from json_test; -- "password"
select content->'$.username' from json_test; -- "password" 这种方式也行
json_length 获取json中都key总数量（长度)
select json_length(content) from json_test; -- 2
json_contains_path json文档是否包含指定路径，用于检查json文档 参数二是指定字符产'one' or 'all' ,one表示如果又多个参数只有一个找到了那就返回1，all表示必须所有都找到了才能返回1
select json_contains_path(@bb,'one','$[0]'); -- 1
select json_contains_path(@aa,'one','$.pass123'); -- 0 没有返回0
json_contains json文档中查找指定匹配内容
外键联级起到了约束的作用，保证数据的完整性，如student子表关联class父表时，当父表更新或者删除时，子表也随之更新或者删除，这个过程是数据库层面完成的。早期企业系统数据库设计里面比较多，虽说帮程序员节省了delete、update操作，实际上增加了潜规则，也增加了软件复杂度，也会会减弱性能。
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。
- RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；
- CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；
- SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。
所以InnoDB引擎才允许使用外键，所以，我们的数据表必须使用InnoDB引擎。
外键必须建立索引
案例：
CREATE TABLE `user` ( `id` int(4) NOT NULL, `sex` enum('f','m') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo` ( `sn` int(4) NOT NULL AUTO_INCREMENT, `userid` int(4) NOT NULL, `info` varchar(20) DEFAULT NULL, PRIMARY KEY (`sn`), index `userid` (`userid`), CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `user` (`id`,`sex`) VALUES ('1', 'f'), ('2', 'm'), ('3', 'f'); INSERT INTO `userinfo` (`sn`,`userid`,`info`) VALUES ('1', '1', '2005054dsf'), ('2', '1', 'fdsfewfdsfds'), ('3', '1', 'gdsgergergrtre'), ('4', '2', 'et34t5435435werwe'), ('5', '2', '435rtgtrhfghfg'), ('6', '2', 'ret345tr4345'), ('7', '3', 'fgbdfvbcbfdgr'), ('8', '3', '45r2343234were'), ('9', '3', 'wfyhtyjtyjyjy');
当修改user表的主键id时，从表userinfo表的外键id也会改变，当删除user表的主键id时，随之从表userinfo表的所有关联id会被删除
要注意的是当我们先删除主表时drop table user;需要先删除从表再删除主表，最后这是不是和触发器很像，没错，根据不同数据库架构来取舍是否要使用外键联级
3730 - Cannot drop table 'user' referenced by a foreign key constraint 'userinfo_ibfk_1' on table 'userinfo'., Time: 0.001000s
NULL、RESTRICT、NO ACTION
删除：从表记录不存在时，主表才可以删除。删除从表，主表不变更新：从表记录不存在时，主表才可以更新。更新从表，主表不变CASCADE删除：删除主表时自动删除从表。删除从表，主表不变更新：更新主表时自动更新从表。更新从表，主表不变 SET NULL删除：删除主表时自动更新从表值为NULL。删除从表，主表不变更新：更新主表时自动更新从表值为NULL。更新从表，主表不变
版权声明：本文原创发表于 博客园，作者为 RainBol 本文欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则视为侵权。