MySQL学习与实验笔记
心小雨
喵~
展开
-
12.redis
pipeline(流水线) 节约RTT(往返时间)解耦 耦合度博主翻找文档ing待修改原创 2019-11-06 21:01:53 · 100 阅读 · 0 评论 -
11.主从复制
主,生成证书:mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data (生成在data目录)重启mysql:systemctl restart mysqld.service查看日志:2019-07-01T01:36:45.351787Z 0 [Warning] Fai...原创 2019-11-06 20:47:46 · 242 阅读 · 0 评论 -
10.优化
禁用索引:alter table t1 DISABLE KEYS;禁用唯一性检查:set unique_checks=0;禁用外键检查:set foreign_key_checks=0;set autocommit=0;create table tb_member( id bigint primary key, name varchar(20), sex tinyint not null ...原创 2019-11-06 20:45:56 · 143 阅读 · 0 评论 -
9.mysql日志
二进制日志log-binexpire_logs_days=10 //清除日志的天数max_binlog_size=100M //单个二进制日志大小为100M,默认1Gbinlog-do-db=db_name //告诉mysql需要对某个库记录binlogbinlog-ignore-db=db_name //告诉mysql不记录某个库的binlogbinlog_cache_size //...原创 2019-11-06 20:45:26 · 89 阅读 · 0 评论 -
8.MySQL用户与权限
use information_schema;查看user表:use mysqldesc user;select * from user\G查看加密是否开启:show variables like ‘have_openssl’;查看数据库用户表:desc db;创建用户:create user ‘zhijun’@‘localhost’ identified by ‘123.com’;...原创 2019-11-06 20:45:01 · 171 阅读 · 0 评论 -
7.触发器
建表:create table account(acct_num int , amount decimal(10,2));创建触发器:create trigger ins_sum(触发器名) before(在之前) insert on account(表名) for each row set @sum=@sum+NEW.amount;给变量赋值:set @sum=1;创建多个触发器:crea...原创 2019-11-06 20:44:23 · 191 阅读 · 0 评论 -
6.事务
开启事务:begin;update bank set money=money-200 where name=‘kaikai’;update bank set money=money+200 where name=‘liangliang’;提交事务:commit;开启事务:start transaction;查看自动提交:show variables like ‘autocommit’;...原创 2019-11-06 20:43:48 · 119 阅读 · 0 评论 -
5.视图
创建视图:create view view_t as select quantity,price,quantity*price from t;create view stu_clas(id,name,class) as select student.d_id,student.name,sut_info.glass from student,sut_info where student.d_id ...原创 2019-11-06 20:42:59 · 124 阅读 · 0 评论 -
4.索引
create index 创建索引 alter table 添加索引create table 表名[]查看索引:show index from book\Gshow create table from book\Guse information_schemaselect * from INNODB_SYS_INDEXES\G建表时添加索引:普通索引:create table boo...原创 2019-11-06 20:42:37 · 113 阅读 · 0 评论 -
3.
去重:select distinct 字段名 from 表名排序:select f_name,f_price from fruits order by f_price,f_name; (desc(降序),asc(升序))单组分组:select s_id,count() from fruits group by s_id;多组分组:select * from fruits group by s...原创 2019-11-06 20:41:41 · 139 阅读 · 0 评论 -
2.数据类型
博主正在翻找文档ing待修改原创 2019-11-06 20:41:00 · 92 阅读 · 0 评论 -
1.数据库和表的基本操作
1.父表中必须存在主键2.父表和子表的数据类型必须相同3.两个表的引擎必须是innodb外键约束:constraint(约束) 键名 foreign key(id) references(关联) t1(id)(父表的键)alter table 表名 add constraint fk_C_D foreign key(shen_id) references C(id);非空约束:creat...原创 2019-11-06 20:39:09 · 157 阅读 · 0 评论