MySQL数据库知识点总结

DB：Database:数据库，指保存数据的文件集合。
DBMS：DataBaseManagementSystem:数据库管理系统（数据库软件），常见的数据库软件：Oracle,MySQL,SqlServer,DB2,sqlite等
SQL：StructuredQueryLanguage:结构化查询语言，用于程序员和数据库软件交互。
 

数据库相关SQL

  1. show databases;
  2. create database db1;
  3. show crate database db1;
  4. create database db1 character set utf8/gbk;
  5. drop database db1;
  6. use db1;

表相关SQL

  1. create table t_user(id int ,name varchar(10) ,email varchar(10));
  2. show tables;
  3. show create table t_user;
  4. create table t_user(id int,name varchar(10),age int) engine=innodb/myisam charset=utf8/gbk;
  5. desc t_user;
  6. drop table t_user;

例子：

DROP TABLE IF EXISTS user.t_user;
create table user.t_user(
    id bigint(18) NOT NULL AUTO_INCREMENT PRIMARY key comment '自增id',
    user_id bigint(18) NOT NULL COMMENT '用户id',
    user_name varchar(20) NOT NULL COMMENT '用户姓名',
    user_count int(11) DEFAULT NULL COMMENT '用户家人数',
    user_type tinyint(4) DEFAULT NULL COMMENT '1:会员 2:普通 3:管理员',
    is_show tinyint(4) DEFAULT 0 COMMENT '是否显示(0-默认显示，1-隐藏，2-删除)',
    trace_code varchar(50) DEFAULT NULL COMMENT '埋点码',
    create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 comment = '用户表';
//创建联合索引，最左匹配原则
create index id_user_id_type on user.t_user(user_id,is_show,user_type);
//初始化数据
INSERT INTO `t_user` VALUES (1, 88888888, 'grant', 2, 3, 0, 'supperUser', '2022-02-22 22:22:22', '2022-02-22 22:22:22');

 

修改表相关

  1. rename table t_user to t1;
  2. alter table t1 engine=innodb/myisam charset=utf8/gbk;
  3. alter table t1 add sal int null comment '薪水' ;alter table t1 add sal int default 10000 comment '薪水' first;alter table t1 add sal int after name;
  4. alter table user add user_type tinyint(4) default 0 after user_name; 
  5. alter table t1 drop name;
  6. alter table t1 change sal salary varchar(10);
  7. alter table t1 modify age int first;alter table t1 modify age varchar(10) after name;

数据相关SQL

  1. insert into t1 values(1,'Tom','1@123'); insert into t1 values(1,'Tom','1@123'),(2,'Jerry','2@123'); insert into t1 (id,name) values(1,'张飞'),(2,'刘备');
  2. select name,age from where id<5;
  3. update t1 set age=10 where id=2;
  4. delete from t1 where id=2;

主键约束：唯一且非空----自增:只增不减从历史最大值基础上+1

  1. primary key auto_increment
  2. create table t2(id int primary key auto_increment,name varchar(10));

非空约束-not null---唯一约束-unique---默认约束-default

  1. create table t1(id int ,age int not null unique);
  2. create table t2(id int ,age int default 25);->insert into t2(id) values(1);//只有这样才能触发默认值生效。

外键约束

  1. 外键：用来建立关系的字段称为外键
  2. 外键约束：保证数据的完整性，外键字段的值可以null，可以重复，但是不能是关系表中不存在的数据，被依赖的数据不能先删除，被依赖的表也不能被先删除。
  3. 格式：constraint 约束名称 foreign key(外键字段) references 表名(字段名)
  4. 先创建被依赖的部门表：create table t_dept(id int primary key auto_increment,name varchar(10));--create table t_emp(id int primary key auto_increment,name varchar(10),dept_id int ,constraint fkdept foreign key(dept_id) references t_dept(id))
  5.  

删除表并创建新表，自增数值清零-truncate

  1. truncate table t1;

注释-comment ------`和'的区别，`是修饰表名和字段名 可以省略 ' 修饰字符串

  1. create table `t1`(`id` int primary key auto_increment comment '这是主键',`name` varchar(10) comment '名字');

数据冗余------外键

  1. 如果表设计不够合理，当数据量增多时出现的大量重复数据，这种现象称为数据冗余，通过拆分表的方式解决冗余问题。
  2. 用于简历关系的字段称为外键（如创建表时的字段：id int,deptid int,parentid int）

事务

  1. 事务是数据库中执行同一业务多条sql语句的工作单元 可以保证多条sql语句要么全部执行成功，要么全部执行失败。
    1. begin;
    2. update t_user set money=money-200 where id=2 and state='正常';
    3. savepoint s1;
    4. update t_user set ......
    5. savepoint s2;
    6. rollback to s1;
    7. rollback;
    8. commit;
    9. show variables like '%autocommit%';//查看自动提交状态
    10. 开启事务的第二种方式：关闭自动提交 改成手动提交 作用和begin类似：set autocommit=0/1;
  2. 事务的ACID特性：是保证事务正确执行的四大基本要素
    1. 原子性：Atomicity,最小不可拆分，保证全部成功或全部失败
    2. 一致性：consistency，从一个一致状态到另一个一致状态
    3. 隔离性：Isolation，多个事务之间互不影响。
    4. 持久性：Durability,事务提交后数据保存到数据库文件中持久生效

SQL分类

  1. DDL：Data Definition Language 数据定义语言
    1. create ,drop,alter,truncate
    2. 不支持事务
  2. DML：Data Manipulation Language 数据操作语言
    1. insert ,update ,delete, select(DQL)
    2. 支持事务 select 和事务没有关系
  3. DQL：Data Query Language 数据查询语言
    1. select
  4. TCL：Transaction Control Language 事务控制语言
    1. begin , rollback .commit ,savepoint s1,rollback to s1;
  5. DCL：Data Control Language 数据控制语言
    1. 设计分配用户权限相关的SQL

数据类型

  1. 整数 tinyint(4),int(8); bigint(18) ; int(m)->create table t_int(id int,age int(10) zerofill);
  2. 浮点数 double(m,d);decimal(5,3) ->76.234 m=5 d=3
  3. 字符串 
    1. char(m) 不可变长度字符串 m=10  'abc' 所占长度为10，执行效率略高
    2. varchar(m) 可变长度字符串 m=10 'abc' 所占长度为3 好处是节省空间 最大长度65535,建议长度不要超过255，超过加以用text
    3. text(m) 可变长度字符串 m=10 'abc' 所占长度3 最大长度65535
  4. 日期
    1. date:只保存年月日
    2. time:只保存时分秒
    3. datetime:年月日时分秒 默认值null 最大值9999-12-31
    4. timestamp:年月日时分秒 默认值当前时间 最大值2038-1-19，以时间戳的形式保存时间
      1. create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
      2. insert into t_date values('2019-4-13','20:37:50','2019-4-2 13:12:12',null);

导入*.sql数据到数据库中

  1. windows->把*.sql文件保存到d盘的根目录
    1. 在命令行中执行  source d:/tables.sql
  2. linux->把 *.sql文件保存到桌面
    1. 在命令行中执行 source /home/soft01/桌面/talbes.sql

查询语句相关

  1. is null--is not null--and--or
    1. select ename,sal comm from emp where comm is null and sal<2000;
  2. 别名--去重 distinct
    1. select distinct job 职位 from emp;
  3. 模糊查询 like；_：代表单个未知字符 %：代表0或多个未知字符--not between x and y;
    1. select ename,distinct job from emp where job like '%s%';
    2. select ename from t_item where title like '%e_';
    3. select * from t_item where price between 50 and 200 and title like '%得力%';
  4. 比较运算符>,<,>=,<=,=,!=,<>；
  5. in--order by sal asc/desc;升序默认可不写/降序
    1. select * from emp where sal not in(5000,800,950);//where sal!=5000 or sal!=800 or sal!=950;
    2. order by多个字段排序，用逗号隔开，按先后顺序分别排序：...order by id asc, age desc;
  6. limit 跳过的条数 请求的页数；
    1. limit 16,8;//(3-1)*8,8//请求第三页每页8条
  7. 数值计算 + -  * / % mod(7,2)

日期相关函数

  1. select now();
  2. select curdate();
  3. select curtime();
  4. select date(now()),time(now());
  5. select extract(hour from now());//从完整年月日时分秒中提取时间分量
  6. select ename,extract(year from hiredate) year from emp;//查询每个员工的姓名和入职的年份
  7. date_format(日期，格式); str_to_date(自定义的字符串时间，格式); %Y %y %d %H %i %s 
    1. select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
    2. select str_to_date('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');
  8. ifnull(x,y)函数，如果x的值为null，则age=y,如果不为null则age=x;
    1. update emp set comm=ifnull(comm,0);
  9. 获取当前时间的前一小时/后一小时
    1. date_sub(now(), interval 1 hour);//后一小时
    2. date_add(now(), interval 1 hour);//前一小时

聚合函数

  1. avg()--max()--min()--sum()--count()
    1. select count(*) from emp where deptno=30 and sal>1500;

字符串相关函数

  1. concat(s1,s2)//拼接
    1. select ename,concat(sal,'元') from emp;
  2. char_length(str)//长度
    1. select ename,char_length(ename) from emp;
  3. instr(str,substr);locate(substr,str);//获取出现的位置
    1. select locate('d','abcdef');//4
  4. insert(str,start,length,newstr);
    1. select insert('abcdefg',3,2,'m');//abmefg
  5. upper(str);lower(str);
    1. select upper('nba'),lower('Abc');
  6. trim(str);
    1. select trim('     a    b   ');//a   b;
  7. left(str,length) ,right(str,length),substring(str,index,length);
    1. select left('abcdef',2);//ab
    2. select right('abcdef',2);//ef
    3. select substring('abcdefg'2,3);
  8. repeat(str,count);
  9. replace(str,old,new);
    1. select replace('abcefg','e','mmm');
  10. reverse(str);
    1. select reverse('abc')//cba

数学相关函数

  1. floor(num)//向下取整
    1. select floor(3.94)//3
  2. round(num); round(num,m)//四舍五入
    1. select round(23.8)//24
    2. select rount(23.897,2);//23.90
  3. truncate(num,m);//非四舍五入
    1. select truncate(23.897,2)//23.89
  4. rand();//0-1--3-5的随机整数
    1. select floor(rand()*3+3);

分组查询--(每个)group by 

  1. select category_id,avg(price) a from t_item group by category_id having a<100;

组连接-group_concat()

  1. 凡是在需求中提到显示到一行（条）时，用此关键字
  2. 查询每个部门的员工姓名，部门的员工姓名显示到一条数据中：select deptno,group_concat(ename) from emp group by deptno;
  3. 查询每个部门的员工姓名和对应的工资，要求每个部门的信息显示到一条数据内：select deptno,group_concat(ename,':',sal)from emp group by deptno;

子查询(可以嵌套n层)

  1. select extract(year from hiredate) year,count(*) from emp group by year;
  2. select * from emp where job=(select job from emp where ename='jones') and ename!='jones';
  3. 子查询总结：
    1. 写在where和having的后面，当做查询条件的值
    2. 写在创建表的时候-格式：create table 表名 as(子查询) 
      1. create table new_emp as(select * from emp where deptno=10);
    3. 写在from 后面当成一个虚拟表 必须有表名 
      1. select ename from (select * from emp where deptno=10) new_table;

关联查询(同时查询多张表的查询方式)

  1. 扩展：select deptno from emp group by deptno order by avg(sal) desc limit 0,1;//查询最高平局工资的部门编号（并列第一的问题不能解决）
  2. select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
  3. 等值连接：select * from A,B where A.x=B.x and A.age=18;
  4. 内连接：select * from A join B on A.x=B.x where A.age=18;
  5. select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';
  6. 外连接：select * from A left/right join B on A.x=B.x where A.age=18;
  7. 查询部门表的全部名称和对应的员工姓名  select d.dname ,e.ename from emp e right join dept d on e.deptno=d.deptno;
  8. 关联查询总结：
    1. 关联查询的查询方式包括三种：等值连接，内连接，外连接
    2. 如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接
    3. 如果查询一张表的全部数据和另一张表的交集数据使用外连接，外连接只需要掌握一种即可

笛卡尔积

  1. 关联查询必须写关联关系，如果不写则是两张表的乘积，这个乘积称为笛卡尔积
  2. 工作中不允许出现因为超级耗内存，有可能直接崩溃。

表设计之关联关系

  1. 一对一：
    1. 有A和B两张表，A表中的一条数据对B表中的一条数据，B中一条对A中一条
    2. 应用场景：用户表和用户信息扩展表，商品表和商品详情表
    3. 如何建立一对一的关系：在从表中添加外键指向主表的主键建立关系
    4. create table user(id int primary key auto_increment,username varchar(10));
    5. create table user_info(user_id int ,nick varchar(10));
  2. 一对多：
    1. 有A和B两张表，A表中的一条数据对B表中的多条数据，B中一条对A中一条
    2. 应用场景：商品表和分类表 员工表和部门表
    3. 如何建立关系：在两张表中多的表中添加外键指向另一张表的主键；
    4. create table emp(id int primary key auto_increment,name varchar(10),dept_id int);
    5. create table dept(id int primary key auto_increment,name varchar(10));
  3. 多堆多：
    1. 有A和B两张表，A表中的一条数据对B表中的多条数据，B中一条对A中多条
    2. 应用场景：老师表和学生表
    3. 如何建立关系：创建一张关系表，在关系表中添加两个外键指向另外两张表的主键
    4. create table teacher(id int primary key auto_increment,name varchar(10));
    5. create table student(id int primary key auto_increment,name varchar(10));
    6. create table t_s(tid int,sid int);
  4. 例题：
    1. 每个部门的人数，根据人数降序排序：select d.deptno,count(e.empno) c from emp e right join dept d on e.deptno=d.deptno group by d.deptno order by c desc;
    1. 每个部门中，每个主管的手下人数：select deptno,mgr,count(*) from emp mgr is not null group by deptno,mgr;
    2. 每年的入职人数:select extract(year from hiredate) year,count(*) from emp group by year;
    3. 少于等于3个人的部门信息：select d.* from emp e right join dept d on e.deptno=d.deptno group by d.deptno having count(e.empno)<=3;
    4. 只有一个下属的主管信息：MariaDB [newdb3]> select * from emp where empno in(select mgr from emp where mgr is not null group by mgr having count(*)=1);
    5. 下属最多的人，查询其个人信息：
      1. 得到最多的人数：select count(*) from emp group by mgr order by count(*) desc limit 0,1;
      2. 通过最大人数得到领导的编号:select mgr from emp group by mgr having count(*)= (↑);
      3. 通过领导编号得到其领导的个人信息：select * from emp where empno in(↑);
    6. 查询员工信息和员工对应的部门名称，所在城市：select e.* ,d.dname,d.loc from emp e join dept d on e.deptno=d.depno;
    7. (员工的编号=领导（领导也属于某个领导的员工）的编号)查询emp表中所有员工的姓名以及该员工上级领导的编号，姓名，职位，工资:select e.ename,m.empno,m.ename from emp e left join emp m on e.mgr = m.empno;

视图-- view

  1.  定义： 数据库中标和视图都是弃内部的对象视图可以理解成是一张虚拟的表，视图本质就是取代了一段sql查询语句。
  2. 为什么使用：因为有些数据的查询需要书写大量的sql语句，每次书写开发效率太低，使用视图可以重用sql语句，可以隐藏敏感信息。
  3. create view v_emp_10 as(select * from emp where deptno=10);
  4. 分类：
    1. 简单视图：创建视图的子查询中不包含去重、分组查询、聚合函数、关联查询的视图称为简单视图，可以对视图中的数据进行增删改查操作；
    2. 复杂视图：和简单视图相反，只能进行查询。
    3. 创建显示每个部门工资总和，平均工资，最大工资，最小工资的复杂视图:create view v_emp_info as(select deptno ,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
    4. 对视图进行增删改查和table一样。insert into v_emp_10(empno,ename) values(10011,'Tom');insert into v_emp_10(empno,ename,deptno) values(10012,'Jerry',20);//以上两行都为数据污染
    5. 如果插入一条数据在原表中显示但是在视图中不显示，则称为数据污染。
    6. 数据污染可以通过with check option 关键字解决： create view v_emp_20 as(select * from emp where deptno=20) with check option;//插入数据：insert into v_emp_20(empno,ename,deptno) values(10013,'Jack',30);//插入失败；
    7. 删除和修改数据只能操作视图中存在的数据
    8. 修改视图：
      1. 创建或替换，如果不存在则创建，如果存在则替换：create or replace view v_emp_20 as(select * from emp where deptno=20 and sal>2000);
      2. 如果创建视图时子查询使用了别名则视图操作时只能使用别名：create view v_emp_20 as(select ename name ,sal from emp where deptno=20);->selelct ename from v_emp_20;//不认识ename;

索引

  1. 索引是数据库中提高查询效率的技术，类似于字典的目录
  2. 用索引后，磁盘块会以树桩结构保存，查询数据时能够大大降低磁盘块的访问量从而提高查询效率。
  3. 因为索引会占存储空间，如果数据量小的话不适用，会浪费存储空间。
  4. 创建 create index 索引名 on 表名(字段名（字符长度）)：create index i_item_title on item2(title);
  5. 给表添加主键约束会自动根据主键字段创建索引:
  6. show index from item2; drop index i_item_title on item2;select * from item2 where title='100';
  7. 通过多个字段创建的索引称为复合索引：create index 索引名（字段1，字段2）;create index i_item_title_price on item2(title,price);
  8. 查询数据时频繁使用多个字段作为查询条件时，使用复合索引
  9. 删除索引：ALTER  TABLE  table_name   DROP  INDEX  index_name;
  10. 联合索引where a=1 and b = 2 and c = 3,只有①a;ab;abc时，②没有函数，计算,or条件，><，③没有null和is null,like时有效。
  11. 联合索引where a=1 and b > 2 and c = 3,只有①a;ab有效，因为b是范围索引，c无法使用索引。
 

JDBC

  1. Java DataBase Connectivity
  2. 使用步骤：创建与数据库的连接>获取Statement/PreParedStatement对象>执行SQL语句>获取结果>处理结果>释放资源

processlist

//root用户查看所有正在运行的线程；其他用户只能看到自己正在运行的线程，除非赋予process权限
show processlist;
show full processlist
//来自MySQL系统库 information_schema 中的 processlist 表
select * from information_schema.processlist


Id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。
User: 就是指启动这个线程的用户。
Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
Time: 表示该线程处于当前状态的时间。
State: 线程的状态，和 Command 对应，下面单独解释。
Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

下面我们单独看一下 Command 的值：

Binlog Dump: 主节点正在将二进制日志 ，同步到从节点
Change User: 正在执行一个 change-user 的操作
Close Stmt: 正在关闭一个Prepared Statement 对象
Connect: 一个从节点连上了主节点
Connect Out: 一个从节点正在连主节点
Create DB: 正在执行一个create-database 的操作
Daemon: 服务器内部线程，而不是来自客户端的链接
Debug: 线程正在生成调试信息
Delayed Insert: 该线程是一个延迟插入的处理程序
Drop DB: 正在执行一个 drop-database 的操作
Execute: 正在执行一个 Prepared Statement
Fetch: 正在从Prepared Statement 中获取执行结果
Field List: 正在获取表的列信息
Init DB: 该线程正在选取一个默认的数据库
Kill : 正在执行 kill 语句，杀死指定线程
Long Data: 正在从Prepared Statement 中检索 long data
Ping: 正在处理 server-ping 的请求
Prepare: 该线程正在准备一个 Prepared Statement
ProcessList: 该线程正在生成服务器线程相关信息
Query: 该线程正在执行一个语句
Quit: 该线程正在退出
Refresh：该线程正在刷表，日志或缓存；或者在重置状态变量，或者在复制服务器信息
Register Slave： 正在注册从节点
Reset Stmt: 正在重置 prepared statement
Set Option: 正在设置或重置客户端的 statement-execution 选项
Shutdown: 正在关闭服务器
Sleep: 正在等待客户端向它发送执行语句
Statistics: 该线程正在生成 server-status 信息
Table Dump: 正在发送表的内容到从服务器
Time: Unused

查看客户端的链接数量

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from processlist ) as connect_info group by client_ip order by client_num desc;

查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

 查看mysql最大连接数和修改最大连接数

show variables like 'max_connections';
//
set global max_connections=1000;

 

 
 
 
 
 
 
 
 
 
 
 
 
posted @ 2019-06-24 06:24  白玉神驹  阅读(198)  评论(0)  编辑  收藏  举报