12-MySQL

最新推荐文章于 2024-03-19 10:00:00 发布

我丶秦始皇丶打钱

最新推荐文章于 2024-03-19 10:00:00 发布

阅读量172

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/weixin_47520834/article/details/108903306

版权

Java 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL

数据库的基本概念

1. 数据库的英文单词： DataBase 简称 ： DB
2. 什么数据库？          ---->存储不同类型的数据
	* 用于存储和管理数据的仓库。

3. 数据库的特点：
	1. 持久化存储数据的。其实数据库就是一个文件系统
	2. 方便存储和管理数据
	3. 使用了统一的方式操作数据库
	
数据库管理系统(DBMS)

> 数据库管理系统是为管理数据库而设计的电脑软件。  Database Management System
> 一般具有存储、截取、安全保障、备份等基础功能.
> * Mysql   Oracle  Sqlserver	
	

数据库的分类
mysql数据库管理系统:   数据库--->表--->字段 类型  约束   数据

> 1. 从存储位置:
1. 基于磁盘:  ==Mysql==   Oracle  Sqlserver  (IO  效率低)
2. 基于缓存(内存): redis  mogodb  hbase  (数据不能完全保证持久化)

> 2. 从关系上划分:
1. 关系型数据库: 数据与数据有关系    ==Mysql==   Oracle  Sqlserver   RDBMS
2. 非关系型数据库: key---value (json)  redis  （读多）  NOSQL(not only sql)

redis(CAS)+mysql

Mysql

配置
	* MySQL服务启动
		1. 手动。
		2. cmd--> services.msc 打开服务的窗口
		3. 使用管理员打开cmd
			* net start mysql : 启动mysql的服务
			* net stop mysql:关闭mysql服务
	* MySQL登录
		1. mysql -uroot -p密码
		2. mysql -hip -uroot -p连接目标的密码
		3. mysql --host=ip --user=root --password=连接目标的密码
	* MySQL退出
		1. exit
		2. quit

SQL

SQL：结构化查询语言。 strtuctured query language

1.DDL: 数据定义语言  data definition language   create  drop  alter等
2.DML: 数据操作语言 insert  delete  update
3.DQL: 数据查询语言 select
4.DCL：数据控制语言 grant commit  rollback  等

mysql(数据库管理系统)---> 有很多数据库database
    数据库(database)---> 表(table)----> 字段(属性)  字段类型  约束   数据

1 指令

mysql服务端应用程序: cmd(客户端) —> 主机(端口) root/密码

-- 1.连接mysql服务
mysql -hip  -uroot -p密码   -- ip: localhost/127.0.0.1/192.168.12.75(root没有权限)
如果连接本机mysql服务。-hip 是可以省略。(打开了mysql的连接)---> max-connection:151
-- C:\Users\DELL>mysql -h127.0.0.1 -uroot -p
-- Enter password: ****
-- C:\Users\DELL>mysql -uroot -p

-- 2. 查看mysql所有的db
mysql> show databases;

-- 3. 选中指定的数据库
  use 数据库名称;
  mysql> use mysql;
  
-- 4. 查看指定数据库所有的table
mysql> show tables;

-- 5. 查看指定的表结构
mysql> desc user;

-- 6. 查看表数据  DQL
  select * from 表名;   select * from user;
  
  --- mysql> select database(); 查看当前使用的数据库
  
-- 7. 查看创建数据库的信息
mysql> show create database java;  -- latin1(只能存储数字和英文)
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| java     | CREATE DATABASE `java` /*!40100 DEFAULT CHARACTER SET latin1 */ |

2 DDL(了解)

-- 1. 创建数据库
create  database 数据库名称; -- mysql> create database java;  数据库名称不允许更改
-- 2. 删除数据库
drop database 数据库名称; --- 数据无法回滚

-- 3.创建表  tb_userinfo  t_userinfo  userinfo  (创建表结构---> 字段  类型  约束)
create  table 表名(
   字段1 类型 [约束],
   字段2 类型 [约束],
   字段3 类型 [约束],
   ....
   字段n 类型 [约束]
);  

整型: tinyint unsigned   int (id)  bigint(时间 ---> 毫秒数/id)
字符串： char(n)  varchar(n)    
   char(3)  varchar(3)  代表可以存储3个字符  char是个定长(查询 提前trim())
   "ab "        "ab"                            
-- 创建学生表(必不可少3个字段： id  createtime  updatetime)
create table tb_student(
   id int(3),
   stuname  varchar(20),
   gender char(1),
   score float(4,1),
   money decimal(4,1),
   birthday date,
   createtime datetime,
   updatetime datetime 
);

-- 修改表结构： alter
-- 1. 新增一个字段  age
   alter table  tb_student add age tinyint(2) unsigned;  //unsigned不能为负数
  -- mysql> alter table  tb_student add age tinyint(2) unsigned after gender;
-- 2.删除字段
  alter table  tb_student drop age;  
-- 3.修改字段名称/类型
  mysql> alter table  tb_student change age  stuage tinyint(2) unsigned;
  mysql> alter table  tb_student change stuage  age int(1) unsigned;
-- 4. 修改类型  
  mysql> alter table tb_student modify age tinyint(2) unsigned;
-- 5. 修改表名
   alter table tb_student rename student;
-- 6. 删除表
    drop table tb_student;

3 * DML

-- 1. 新增  insert(1行记录受影响)
1.1 语法: insert into 表名 values (数据1,数据2,...数据n),(); 对表的所有的字段赋值
insert into tb_student values (2, 'jim1', 'n',90,200,'2020-01-01','2020-01-01 12:00:00','2020-01-01 12:00:00');

1.2 语法: 指定字段新增(推荐)
insert into 表名 (字段1,字段2...字段n) values (数据1,数据2,...数据n);
mysql> insert into tb_student (id,stuname,score,createtime) values (4,'lucy',100,now());

1.3 添加多条数据
insert into 
items(name,city,price,number,picture) 
VALUES
('耐克运动鞋','广州',500,1000,'003.jpg'),
('耐克运动鞋2','广州2',500,1000,'002.jpg');
这样，就实现了一次性插入了2条数据

--2. 删除  delete(>=0条记录受影响的)
  语法: delete from 表名 [where (字段)条件 and/or]; 条件删除
  delete from tb_student;-- 清空表数据
  mysql> delete from tb_student where id =3;
  mysql> delete from tb_student where id =1 and stuname ='jim1';

-- 3. 修改  update(>=0条记录受影响的)
语法:
   1. update 表名 set 字段名1=新值,字段名2=新值...字段名n=新值 [where (字段)条件 and/or];
   update tb_student set gender ='f';
   mysql> update tb_student set money=1000,birthday='2020-01-01', updatetime=now() where id=4;

mysql> show variables like '%character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

mysql> alter database java character set utf8; -- 修改指定数据库的编码格式
-- 后续创建的数据库的编码还是latin。
-- 修改mysql的核心配置文件: my.ini
66 default-character-set=utf8
100 character-set-server=utf8
-- 重启mysql的服务。

4 * 约束

限定字段的数据。(新增/修改)

1. 空约束 null(行级约束)

-- (字段值可以为null)
create table a(
 id int null,
 `name` VARCHAR(10) null
);
-- insert into a (name) values ('abc');
-- update a set name = null where id = 123;

2. 非空约束 not null(行级约束)

-- 字段数据不允许为null(可以重复)
-- create table b(
--  id int not null,
--  `name` VARCHAR(10) not null,
--  age TINYINT(2)
-- );
-- insert into b values (1,'jim',20);
insert into b (id,name) values (2,'lucy');
--  Field 'id' doesn't have a default value

3. 唯一性约束 unique(行级约束)

-- 限定字段的数据唯一的不可重复的(排除null值)   (唯一性索引--->index): 提升查询的效率
-- create table c(
--  id int not null,
--  `name` VARCHAR(10) unique,
--  age TINYINT(2) NOT NULL UNIQUE
-- );    
-- insert into c values (1,'jim',20),(2,null,18);
-- > 1062 - Duplicate entry '20' for key 'age'
insert into c values (2,'tom',22);

4. 主键约束 primary key(行级/表级约束)

-- 等同于: not null + uinque  (既不能为null又是唯一的)
-- 修饰一个列(字段)： 一张表只有一个主键列(标识行记录的唯一性)
-- 任意类型的字段都可以充当主键列。常用: int/bigint/varchar   id为主键列
-- create table c(
--  id int primary key,
--  `name` VARCHAR(10) unique,
--   age TINYINT(2)
-- );
-- insert into c values (1,'jim',20),(2,'tom',20);
insert into c (name,age)values('lucy',20); 

-- 整型的字段充当主键列，主键列数据一般都是自增的。auto_increment
-- create table c(
--  id int primary key auto_increment,
--  `name` VARCHAR(10) unique,
--   age TINYINT(2)
-- );
-- insert into c values (101,'jim1',20),(102,'tom1',20);
-- insert into c (name,age)values('lucy8',20); 
-- auto_increment: 初始值1 每次自增+1(步长)
-- 修改自增的初始值
-- alter table c auto_increment=1000;
-- 修改步长
-- set GLOBAL auto_increment_increment=1;

-- 主键列是字符串类型的varchar
-- create table d(
--   id varchar(100) primary key,
--  `name` VARCHAR(10) unique,
--   age TINYINT(2)
-- );
-- insert into d values ('1','jim1',20),('2','tom1',20);
insert into d values (UUID(),'jim',20),(UUID(),'tom',20);
-- 随机生成的字符串   UUID
-- SELECT UUID();
-- SELECT UUID();

5. 默认约束default

-- 5. 默认约束  default
-- 给字段设定默认值
-- create table d(
--   id varchar(100) primary key,
--  `name` VARCHAR(10) DEFAULT '无名氏',
--   age TINYINT(2),
-- 	gender char(1) not null default '男'
-- );

-- insert into d (id,age) values (uuid(),20);

-- 注释
create table d(
  id varchar(100) primary key COMMENT '用户id',
 `name` VARCHAR(10) DEFAULT '无名氏'COMMENT '用户姓名',
  age TINYINT(2),
	gender char(1) not null default '男'
);

6. 外键约束 foreign key(表级约束)

表示表与表有关系。 (与2张表有关)

-- 1. 一对一  (一个用户有一个角色)
-- create table tb_role(
--   id int PRIMARY key auto_increment,
-- 	rolename varchar(10) not null unique,
-- 	`desc` varchar(100),
-- 	createtime  datetime,
-- 	updatetime datetime
-- );

-- alter TABLE tb_role auto_increment = 1000;    
    
create TABLE tb_userinfo(
 id int PRIMARY key auto_increment,
 username varchar(20) not null,
 age TINYINT(2) UNSIGNED,
 gender TINYINT(1) UNSIGNED COMMENT '0 false 男  1 true 女',
 createtime BIGINT(2),
 updatetime BIGINT(2)
);

-- 用户是哪一个角色是要严格参照角色表的信息。
-- 主表: 角色表
-- 从表(子表): 用户表
-- 从表的数据要严格参照主表的数据。
-- 外键列(有一个字段充当)在从表里面。(用户表有一个列充当外键列(外键列的数据要严格参照主表的数据))

alter table tb_userinfo add CONSTRAINT fk_user_roleid  
FOREIGN key(roleid) REFERENCES tb_role(id);

数据库的表设计:(3大范式)
1. 列的原子性(列不可再分) 
2. 遵循第一范式基础之上，保证每行记录的唯一性。(主键)
3. 遵循第二范式基础之上，避免出现数据的冗余(外键列的数据除外(参照主表的主键列的))
需求为准

--1. fk_user_roleid	roleid	java	tb_role	id	RESTRICT	RESTRICT
-- 修改/删除主表数据的时候，查看子表的记录是否关联主表的数据。
-- 2. fk_user_roleid	roleid	java	tb_role	id	CASCADE	CASCADE
-- 删除主表数据的时候，子表关联的记录会一起删除。(不推荐)
-- 3.fk_user_roleid	roleid	java	tb_role	id	SET NULL	SET NULL
-- 删除主表数据的时候，子表外键列数据会设置为null(外键列可以为null)

-- 使用外键效率很低，弱外键。(所有的业务逻辑(表与表的关系)都在业务代码层面上进行解决)

2.一对多/多对多
tb_teacher
tb_student

* DQL(查询)

--DCL: grant all privileges on *.* to 'root'@'%' identified by 'root';
-- 将mysql数据库里面的user表root用户的host字段的数据改成%。

select * from 表名;-- *: 通配的所有的字段名称
语法:(只要是查询一般后面都会where/order by/limit)
  select 
     字段名称1, 字段名称2,....
  from 表1,表2,.....
  [where 条件1 and/or 条件2]-- 过滤不符合条件的行记录
  [group by 字段1] -- 分组
  [having 条件]-- 对分组之后的数据进行过滤
  [order by 字段 asc/desc ]-- 对行记录进行排序
  [limit ?,?]-- 限定查询的行记录

1. 条件查询 where

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>=；
BETWEEN…AND；是否满足一个区间范围  >=  <=
IN(set)；条件的集合
IS NULL；
AND； 连接多个条件的查询
OR；or  满足其中一个条件就可以
NOT；

-- 查询学生性别为女，并且年龄15的记录
-- select * from stu where gender = 'female' and age = 15;
-- 查询学号为S_1001，S_1002，S_1003的记录
-- select * from stu where sid = 's_1001' or sid = 's_1002' or sid = 's_1003';
-- select * from stu where sid in ('s_1001','s_1002','s_1003');

-- 查询学号不是S_1001，S_1002，S_1003的记录
-- select * from stu where sid != 's_1001' and sid != 's_1002' and sid != 's_1003';
-- select * from stu where sid not in ('s_1001','s_1002','s_1003');

-- 查询年龄为null的记录(字段值null  is)
-- select * from stu where age is null;

-- 查询年龄不为null的学生记录
-- select * from stu where  age is not null;

-- 查询年龄在20到40之间的学生记录
-- select * from stu where  age>=20 and age <=40;
-- select * from stu where  age BETWEEN 20 and 40;

-- 查询性别非男的学生记录
-- select * from stu where gender !='male' or gender is null;

2. 模糊查询 like

-- show variables like '%character%'; 查看整个mysql所有字符编码格式
-- show variables like '%character%';

-- 查询姓名由5个字母构成的学生记录 (模糊通配字母: _ )
-- select * from stu where sname like '_____';

-- 查询姓名以“z”开头的学生记录(%: 通配任意量的内容)
-- select * from stu where sname like 'z%';

-- 查询姓名中第2个字母为“i”的学生记录
-- select * from stu where sname like '_i%';

-- 查询姓名中包含“a”字母的学生记录
-- select * from stu where sname like '%a%';

3.字段控制查询

-- 3.1 去重  distinct (单列)
-- 查询学生表里面所有的性别。
-- select DISTINCT gender from stu;
-- 3.2 null值运算
-- 把学生的年龄都+5(字段值null,算术运算 最后的结果都是null)
-- 需求: null-->0  ifnull(字段,新值)
-- select sid,sname,age, (ifnull(age,0)+5) from stu;
-- 查询员工的月薪和佣金之和
-- select empno,ename,sal,comm,sal+IFNULL(comm,0) from emp;

-- 3.3 别名查询 as(可以省略)  字段/表名 起别名查询(表名别名: 多表)
-- select e.empno,e.ename,e.sal,comm,sal+IFNULL(comm,0) as '月薪和佣金之和' from emp e;

4. 排序 order by

-- 按照多个字段值进行升序或者降序排列。 默认是升序  asc  降序: desc
-- 查询所有学生记录，按年龄降序排序
-- SELECT * FROM stu ORDER BY age desc;
-- 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号降序排序
-- SELECT * FROM emp ORDER BY sal desc,empno desc;

5. 分组查询

COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

-- count(参数)  统计表里面行记录(参数: 字段(一般都是主键列))
-- 查询emp表中记录数：
-- select count(empno) as total,count(*),count(1) from emp;
-- 查询emp表中有佣金的人数：注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。
-- select count(comm) from emp;
-- 查询emp表中月薪大于2500的人数：
-- select count(*) from emp where sal>2500;
-- 统计月薪与佣金之和大于2500元的人数：
-- select count(*) from emp where sal+IFNULL(comm,0)>2500;

-- 查询最高工资和最低工资
-- select max(sal)'最高工资',min(sal) '最低工资' from emp;
-- 查询有佣金的人数，以及有领导的人数：
-- select count(comm),count(mgr) from emp;
-- 查询所有雇员月薪和,所有雇员佣金和，所有雇员月薪+佣金和 统计所有员工平均工资： ：
-- select sum(sal),sum(IFNULL(comm,0)),sum(sal+IFNULL(comm,0)),avg(sal) from emp;

-- 查询每个部门的部门编号以及每个部门的人数：
-- select deptno,count(*) from emp GROUP BY deptno;

-- 查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
-- select deptno,count(*) from emp where sal>1500 GROUP BY deptno;
-- select deptno,count(*) from (select * from emp where sal>1500) temp  GROUP BY deptno ;

-- 查询工资总和大于9000的部门编号以及工资和：
-- where 不能与聚合函数使用  Invalid use of group function
-- 分组之后的数据进行顾虑  having
-- select deptno,sum(sal) sum from emp  GROUP BY deptno having sum>9000;

-- where  vs  having
-- select * from emp having sal>1500;
-- 1. 位置 
-- 2. 组函数运用  where  不可以  having 可以

6. 关联查询

-- 关联查询(多表之间数据查询)---> 推荐
-- 1.等值连接(连接的条件是=)
-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称 
-- 56= 14*4 (笛卡尔积数据)
-- 2张表  至少有1个条件  3张表  至少2个条件 
-- select e.empno,e.ename,e.sal,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;


-- 2.不等值连接(连接的条件不是=)
-- 查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
-- select e.empno,e.ename,e.sal,s.GRADE from emp e,salgrade s where e.sal BETWEEN s.LowSAL and s.HISAL;
-- select e.empno,e.ename,e.sal,d.dname,d.loc,s.GRADE from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal BETWEEN s.LowSAL and s.HISAL;

-- 3.外连接
-- 查询每个部门的部门编号和每个部门的工资和: 基表: dept
-- 因为员工表没有员工在40号部门
-- select deptno,count(*),sum(sal) from emp GROUP BY deptno;
-- select d.*,count(*),sum(sal) from dept d,emp e where e.deptno = d.deptno GROUP BY e.deptno;
-- 1.内连接  inner join on/where ==> 等值连接
-- select d.*,count(*),sum(sal) from dept d INNER JOIN emp e where e.deptno = d.deptno  GROUP BY e.deptno;

-- 2.左外连接 以左表为基准  右表没有的数据以null进行填充  LEFT JOIN  on
-- select d.*,count(e.empno),IFNULL(sum(e.sal),0) from dept d LEFT JOIN emp e on e.deptno = d.deptno GROUP BY e.deptno ORDER BY d.deptno;
-- 
-- 3.右外连接  以右表为基准  左表没有的数据以null进行填充 RIGHT JOIN  on
-- select d.*,count(e.empno),IFNULL(sum(e.sal),0) from emp e RIGHT JOIN dept d  on e.deptno = d.deptno GROUP BY e.deptno ORDER BY d.deptno;


-- 4. 自连接(自己与自己关联查询 把1张表看成多张表使用)
-- 查询员工姓名和员工的老板的名称  emp: 员工表 e1  老板表 e2
-- select e1.empno,e1.ename,e1.mgr,e2.empno,e2.ename from emp e1, emp e2 WHERE e1.mgr=e2.empno ORDER BY e1.empno;
-- 一共14个员工  13条记录
-- select e1.empno,e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 INNER JOIN emp e2 WHERE e1.mgr=e2.empno ORDER BY e1.empno;
-- select e1.empno,e1.ename,e1.mgr,e2.empno,e2.ename from emp e1 LEFT JOIN emp e2 on e1.mgr=e2.empno ORDER BY e1.empno; 

-- 5. 子查询(查询条件的数据是未知的  级别最高)
-- 查询工资为800的员工信息
-- select * from emp where sal = 800;
-- 查询工资为20号部门平均工资的员工信息.  使用1条sql
-- select * from emp where  sal = (select avg(sal) from emp where deptno = 20);

-- 6.集合查询  union vs union all
-- 分库分表 mycat: 用户表: tb_user1  tb_user2  DISTINCT(单列)  
-- 去除重复的行记录(多个列的数据)
-- select * from tb_user1
-- UNION 
-- select * from tb_user2
-- UNION 
-- select * from tb_user3;
-- 通过1条sql语句 查询3张表的数据。  union去除重复的行记录  union all 不去重

7. 分页查询 limit

-- 6.分页查询(查询量的限定)--->100   每页30条记录 4页
-- limit mysql独有关键字
-- Limit size; 代表从第1条记录开始查询 查询size
-- limit start,size;代表从第start条记录开始查询 查询size   行记录有索引 0
-- 查询表的总记录数: select count(id) from 表;
-- pageSize = 30/20/10;
-- totalPage = count/pageSize
-- select count(empno) from emp;-- 14
-- 每页展示5条数据
-- 3页
-- 1页: 5条
-- SELECT * FROM emp ORDER BY empno LIMIT 0,5;
-- 2页 5条
-- SELECT * FROM emp ORDER BY empno LIMIT 5,5;
-- 3页  4
-- SELECT * FROM emp ORDER BY empno LIMIT 10,5;

-- 通用sql:
-- int pageSize = 5;
-- int page = 1/2/3/totalPage 第一个参数的数据完全取决于用户要查看第几页的数据
-- SELECT * FROM emp ORDER BY empno LIMIT (page-1)*pageSize,pageSize;