Mysql学习
关系型数据库
- 主要是用来描述实体与实体之间的关系,是实实在在的事物
- E - R关系图: Entity Relationship diagram:实体关系图(要会画)
- 实体:方框
- 属性:椭圆
- 关系:菱形
MYSQL 的 SQL 语句
SQL:Structure Query Language 结构化查询语言
SQL 的分类
DDL:数据定义语言:定义数据库,数据表他们的结构:
- create 创建
- drop 删除
- alter 修改
DML:数据操纵语言:主要是用来操作数据的
- insert 插入
- update 修改
- delete 删除
DCL:数据控制语言:定义访问权限,取消访问权限,安全设置
DQL:数据查询语言:
- select 查询
- from 子句
- where 子句
数据库
创建数据库
create database 数据库的名字;
create database xcz;
create database 数据库的名字 character set 字符集;
create database xcz_01 character set utf8;
create database 数据库的名字 character set 字符集 collate 校对规则;
create database xcz_02 character set utf8 collate utf8_bin;
查看数据库
-- 查看数据库定义的语句
--- show create database 数据库的名字
--- show create database xcz;
-- 查看所有数据库
-- show databases;
修改数据库的操作
--修改数据的字符集
-- alter database 数据库的名字 character set 字符集;
-- alter database xcz character set gbk;
删除数据库
drop database 数据库的名字;
其他数据库操作命令
--切换数据库(选中数据库)
-- use 数据库名字;
-- use xcz;
-- 查看一下当前正在使用的数据库
-- select database();
表结构的操作
创建表
create table 表名(
列名 列的类型(长度) 约束,
列名2 列的类型(长度) 约束
);
列的类型:
Java SQL
int int
char/string char/varcahr(char:固定长度 varchar:可变长度),其中长度代表的是字符的个数
double double
float float
boolean boolean
date date:YYYY-MM-DD
time:hh:mm:ss
datetime:YYYY-MM-DD hh:mm:ss 默认值是null
timestamp:YYYY-MM-DD hh:mm:ss 默认使用当前时间
text:主要是用来存放文本
blob:存放的是二进制
列的约束:
主键约束:primary key
唯一约束:unique
非空约束:none
案例
- 分析实体:学生
- 学生ID
- 姓名
- 性别
- 年龄
create table student(
sid int primary key,
sname varchar(10),
sex int,
age int
);
查看表
--查看所有的表
--show tables;
--查看表的定义
-- show create table student;
--查看表结构
-- desc student;
修改表
添加列(add)
alter table 表名 add 列名 列的类型 列的约束
alter table student add chengji int not null;
修改列(modify)
alter table student modify sex varchar(2);
修改列名(change)
alter table student change sex gender varchar(2);
删除列(drop)
alter table student drop chengji;
修改表名(rename)
rename table student to mytab;
修改表的字符集
alter table mytab character set gbk;
删除表
drop table mytab;
SQL完成对表中数据的 CRUD 操作
插入数据
insert into 表名(列名1,列名2,列名3)values(值1,值2,值3);
insert into student(sid,sname,gender,age)values(1,'xucz',1,23);
--- 简单写法:如果插入的是全列名的数据,表名后面的列名可以省略
insert into student values(1,'xucz',1,23);
insert into student(sid,sname) values(3,'dai');
insert into student values(3,'dai'); //这种写法错误,插入部分的则不可以省
---批量插入
insert into student values(5,'xucz',1,23),(2,'xucz',1,23),(3,'xucz',1,23),(4,'xucz',1,23);
--- 查看表中数据
select * from student;
删除记录
delete from 表名 where 条件
delete from student where sid=5;
delete from student; //将数据全部删除
☆ ☆ ☆ delete 删除数据和 truncate 删除数据有什么区别
delete: 属于 DML,一条一条删除表中数据
truncate:属于 DDL,先删除表再重建表,
关于哪条执行效率高:具体要看表中的数据量
如果数据比较少,delete比较高效
如果数据比较多,truncate比较高效
更新表记录
update 表名 set 列名=列的值,列名2=列的值2 [where 条件];
---将 sid 为 3的名字改为'lu';
---如果参数是字符串,要加单引号
update student set sname='lu' where sid=3;
查询记录
select [distinct] [*] [列名,列名2] from 表名 [where 条件]
distinct : 去除重复数据
select : 选择显示哪些列的内容
--商品分类:手机数码,鞋靴箱包
1.分类的 ID
2.分类名称
3.分类描述
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
insert into category values(null,'手机数码','电子产品');
insert into category values(null,'鞋靴箱包','江南皮鞋厂');
insert into category values(null,'香烟酒水','过年送礼');
insert into category values(null,'馋嘴零食','卫龙');
insert into category values(null,'酸奶饼干','好吃');
---所有商品
1.商品ID
2.商品名称
3.商品价格
4.生产日期
5.商品分类ID
商品和商品分类:所属关系
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int
);
insert into product values(null,'小米',998,null,1);
insert into product values(null,'锤子',2888,null,1);
insert into product values(null,'Nike',1998,null,2);
insert into product values(null,'中华',98,null,3);
insert into product values(null,'劲酒',38,null,3);
insert into product values(null,'卫龙',2,null,4);
insert into product values(null,'安慕希',68,null,5);
insert into product values(null,'小熊饼干',3,null,5);
简单查询:
----查询所有的商品:
select * from product;
----查询商品名称和商品价格:
select pname,price from product;
别名查询.as 的关键字,as 关键字是可以忽略
--表别名:select p.pname,p.price from product p;(主要用在多表查询);
select p.pname,p.price from product as p;
--列别名 :select pname as 商品名称,price as 商品价格 from product;
select pname as 商品名称,price as 商品价格 from product;
去掉重复的值
--查询商品所有的价格
select price from product;
select distinct price from product;
select 运算查询:仅仅在查询结果上进行了运算
select *,price*1.5 as 折后价 from product;
条件查询 [where 关键字]
指定条件,确定要操作的记录
--查询商品价格>60元的商品
select * from product where price > 60;
----where 后面的条件写法
--关系运算符: > >= < <= = != <>
<>:不等于 :标准sql语法
!=: 不等于 :非标准sql语法
--查询商品价格不等于 88 的所有商品
select * from product where price <> 98;
--查询商品价格在10到100之间
select * from product where price >10 and price <100;
between...and...
select * from product where price between 10 and 100;
逻辑运算:and ,or ,not
--查询商品价格小于 100 或者商品价格大于 100
select * from product where price<100 or price> 100;
like:模糊查询
_ :代表的是一个字符
% :代表的是多个字符
--查询出名字中带有酒的所有商品 '%酒%'
select * from product where pname like '%酒%';
--查询第二名字是熊的商品
select * from product where pname like '_熊%';
in 在某个范围中获得值
--查询出商品分类 ID 在 1,4,5里的商品
select * from product where cno in (1,4,5);
排序查询:order by 关键字
asc (默认): ascend 升序
desc: descend 降序
--0.查询所有商品,按照价格进行排序
select * from product order by price;
--1.降序排序
select * from product order by price desc;
--2.查询名称有酒的商品,按价格降序排序
select * from product where pname like '%酒%' order by price desc;
聚合函数:
sum() :求和
avg() :求平均值
count() :统计数量
max() :最大值
min() :最小值
--1.获得所有商品的总和:
select sum (price) from product;
--2.获得所有商品的平均价格:
select avg (price) from product;
--3.获得所有商品个数:
select count(*) from product;
--注意: where 后面不能接聚合函数
----查出商品价格大于平均价格的所有商品
第一步:查出平均价格
select avg(price) from product;
第二步:用 where来查询
子查询
select * from product where price > (select avg(price) from product);
分组:group by
---1.根据 cno 字段分组,分组后统计商品个数
select cno,count(*) from product group by cno;
--2.根据 cno 分组,分组统计每组商品的平均价格,并且商品平均价格大于60
select cno,avg(price) from product group by cno
having avg(price) > 60;
--having 关键字 可以接聚合函数的 出现在分组之后
--where 关键字 它是不可以接聚合函数 , 出现在分组之前
执行顺序
编写顺序
S..F..W..G..H..O
执行顺序
F..W..G..H..S..O
mysql 执行逻辑
连接器
用来跟服务器建立连接,完成经典的 TCP 握手后,连接器就开始验证身份,这时候就输入账号和密码
- 如果账号密码错误,就会报错,客户端程序结束执行
- 如果连接成功,连接器读取你的权限,之后这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。
太长时间没动静的话,连接器会自动断开(一般为8小时)
长连接:客户端持续有请求,则一直使用同一个连接
短连接:每次执行完很少的几次查询就断开连接,下次查询再重新建立一个
- 建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。
但是全部使用长连接后,你可能会发现,有些时候 MySQL 占用内存涨得特别快,这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,解决办法:
- 定期断开长连接
- mysql 5.7及以上版本,可以通过执行 mysql_reset_connection恢复到刚连接的状态。
执行逻辑第二步:查询缓存,key 为sql语句, value为查询到的结果。一般不推荐查询缓存,因为只要有一个对表的更新,缓存就会清空,如果更新压力较大,则会多造成很多开销。
分析器
识别出 mysql 语句中的字符串分别是什么,代表什么(如果有查询不存在的列,则会报错)
- 词法分析
- 语法分析
优化器
在不影响执行结果的前提下,选择执行效率好的方案
执行器
执行之前,首先判断有无对表的查询权限,有的话继续执行(若之前就命中了缓存,则会在查询缓存返回结果的时候,做权限认证)。默认调用 InnoDB 的引擎,最终返回结果集