1. 数据库操作与存储引擎
1.1 数据库和数据库对象
数据库对象:存储,管理和使用数据的不同结构形式,如:表、视图、存储过程、函数、触发器、事件等。
数据库:存储数据库对象的容器。
数据库分两种:
①系统数据库(系统自带的数据库):不能修改,不能删除
information_schema:存储数据库对象信息,如:用户表信息,列信息,权限,字符,分区等信息。
performance_schema:存储数据库服务器性能参数信息。
mysql:存储数据库用户权限信息。
test: 任何用户都可以使用的测试数据库。
②用户数据库(用户自定义的数据库):一般的,一个项目一个用户数据库。
1.2 查看和选择数据库
①查看数据库服务器存在哪些数据库:
show databases;
②操作具体的数据库:
use mysql;(注:mysql为具体的数据库名)
③查看某个数据库中所有的表:
Show tables;(注:查看的是当前使用的数据库中的所有表,即必须先进入这个数据库)
1.3 创建和删除数据库
①创建指定名称的数据库:
create database testdatabase;(注:testdatabase即为要创建的数据库的相对于名称)
②删除数据库:
drop database testdatabase;
1.4数据库存储引擎的介绍
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的都使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力。
通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。
事务:针对于一组操作,要么都成功 要么都失败。
MyISAM:拥有较高的插入,查询速度,但不支持事务,不支持外键。
InnoDB:支持事务,支持外键,支持行级锁定,性能较低。
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但对比MyISAM,处理效率差,且会占用更多的磁盘空间以保留数据和索引。
2. MySQL常用列类型
2.1 查看表结构分析列类型
desc db; (注:db指当前所进入的数据库中的想查看的表)
2.2 整型
最常用的整数类型:
INT/BIGINT
2.3 小数类型
最常用的小数类型:
FLOAT[(s,p)] 、DOUBLE[(s,p)]
小数类型,可存放实型和整型 ,精度(p)和范围(s),如:double(5,2): 整数和小数一共占5位.其中小数占2位.不够精确。
精确的小数类型:
DECIMAL(比如银行 对小数要求比较高情况下使用)
BIGDECIMAL 高精度类型,金额货币优先选择
2.4 字符类型
常用字符类型:
char(size)定长字符
0 - 255字节,size指N个字符数,若插入字符数超过设定长度,会被截取并警告。
varchar(size)变长字符
0 -255字节,从MySQL5开始支持65535个字节,若插入字符数超过设定长度,会被截取并警告。(不是越长越好,压缩很耗费时间)
char和varchar长度都是255,那char 和 varchar 的区别:
比如 定义char(100),若字符只是占了2个,其他98个也是占用;
char好处:大小固定,不用压缩空间,速度更快一些;
而定义varchar(100),如果只占2个,那它总共就占2个;
保存超长字符类型:
TEXT系列类型,可以保存文章的纯文本,一般用于存储大量的字符串。
TEXT列不能有默认值,检索的时候不存在大小写转换
char与varchar的简单比较说明:
①char的效率高于varchar,char相当于控件换时间,varchar拿时间换控件;
②CHAR默认存储数据的时候,后面会用空格填充到指定长度;而在检索的时候会去掉后面空格;
VARCHAR在保存的时候不进行填充,尾部的空格会留下
2.5 日期类型
常用的日期时间类型:
DATE(年月 日)
DATETIME(年月日,时分秒)
TIMESTAMP(时间戳--范围19700101080001 - 203801191111407 基本不会用它,了解)
TIME和YEAR
注意:在MySQL中,日期时间值使用单引号引起来。
2.6二进制类型
为什么存放二进制?
项目中可能会需要保存图片,保存电影,这就需要使用到二进制类型。因为数据库中没有图片类型和电影类型,需要使用二进制流的形式存进来,读取也通过流来;
开发中,我们一般存储二进制类型保存(图片、视频)文件路径。
常用的二进制类型:
BIT:我们一般存储0或1,相当于Java中的boolean/Boolean类型的值。
在mysql中没有boolean类型 ,可以使用Bit替代;
BLOB:保存的时候,根据自己的大小,选择使用的类型.一般使用BLOB就够用了。
建议:今后的项目,不要把大的文件存放到数据库,如果文件太大,会影响查询和修改的速度,还有备份数据库的时候,也很慢.
如果还是有这样的需求,还是要保存图片,保存电影,怎么办?
通过IO流写到磁盘上,然后再数据库上面保存路径;
BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB:
存放图形、声音和影像,二进制对象,0-4GB。
3. 表操作SQL语句
3.1 创建表
语法:
create table 表名 (
列名1 列的类型 [约束],
列名2 列的类型 [约束],
列名3 列的类型 [约束],
……
列名N 列的类型 [约束],
);
如使用以上语句即可创建对应如下4列的约束表单:
3.2 删除表
语法:
drop table 表名;
3.3 修改表
语法:
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
如需修改mytable表中的name列长度为40并设置默认为null:
对应执行SQL语句为:
alter table mytable modify name varchar(40) default NULL;
3.4 查看表结构
语法:
desc 表名;
3.5查看表的约束
语法:
show create table 表名;(可以查看表结构,对应创建表SQL语句)
具体可见表中可以有如下表约束:
3.5.1 非空约束
①非空约束-- NOT NULL,不允许某列的内容为空。
3.5.2 列默认值
②设置列默认值-- DEFAULT 默认值。
3.5.3 唯一约束
③唯一约束-- UNIQUE,在该表中,该列的内容必须唯一。
注意:
3.5.4 主键约束
④主键约束-- PRIMARY KEY, 非空且唯一;主键自增长:AUTO_INCREMENT,从1开始,步长为1。
主键设计需知:
1:单字段主键:单列作为主键,建议使用。(一般来说名字为id)
复合主键:使用多列充当主键,不建议。(比如用户名+身份证+电话号码(表示唯一) )
2:主键分为两种:
1)自然主键: 使用有业务含义的列作为主键(不推荐使用);(这个主键是有意义,比如身份证、用户名、电话号码、驾驶证)
2)代理主键: 使用没有业务含义的列作为主键(推荐使用); (这个主键是没有任何意义,只要这列非空且唯一就行)
注意: 一般做项目都是使用代理主键,如果你用自然主键,比如身份证,它必须非空且唯一,随着业务的发展,一个人可能出现多个号,那身份证可能重复。由于代理主键没有意义,完全不需要我们来维护 -- 让它自动生成即可。另外,创建一张表
,必须有主键,如果没有主键,可以认为这张表不合格。
3.5.5 外键约束
⑤外键约束—FOREIGN KEY
A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)
4. 表的查询SQL
4.1 简单查询
4.1.1 简单数据查询
语法:
select *(列名1, 列名2…) from 表名;
如查询product商品表中的信息需求:
结果分别如下:
4.1.2 避免重复数据-DISTINCT
语法:
select distinct 列名1, 列名2,...
from 表名;
如:#查询商品的分类编号
SELECT DISTINCT category_id FROM product;
4.1.3实现数学运算查询+别名设置
对NUMBER型数据可以使用算数操作符创建表达式(+ - * /)
对DATE型数据可以使用部分算数操作符创建表达式 (+ -)
对列进行别名设置使用as + 别名,或直接在列后空格+ 别名,别名使用’’;
如:
#查询所有货品的id,名称和批发价(批发价=卖价*折扣)
SELECT id, product_name, sale_price*cutoff as '批发价' FROM product;
#查询所有货品的id,名称,和各进50个的成本价(成本=cost_price)
SELECT id, product_name, cost_price*50 as '50个成本价' FROM product;
#查询所有货品的id,名称,各进50个,并且每个运费1元的成本
SELECT id, product_name, (cost_price+1)*50 as '50个成本价(包含每个运费1元)' FROM product;
注:其中as可以省略;
4.1.4 设置显示格式查询
为方便用户浏览查询的结果数据,有时需要设置显示格式,可以使用CONCAT函数来连接字符串。
如:
#查询商品的名字和零售价(sale_price)--(CONCAT)
#格式:xxx商品的零售价为:xxx
SELECT CONCAT(product_name,'商品的零售价为:',sale_price) FROM product;
4.2过滤查询
4.2.1比较运算符
在MySQL表中查询条件中数值列或字符列可以使用如下运算符作为限定条件:
如:
#查询货品零售价为119的所有货品信息
SELECT * FROM product WHERE sale_price = 119;
#查询货品名为罗技G9X的所有货品信息
SELECT * FROM product WHERE product_name = '罗技G9X';
#查询货品名 不为 罗技G9X的所有货品信息
SELECT * FROM product WHERE product_name != '罗技G9X';
#查询分类编号不等于2的货品信息
SELECT * FROM product WHERE category_id <> 2;
#查询货品名称,零售价小于等于200的货品
SELECT product_name, sale_price FROM product WHERE sale_price <= 200;
#查询id,货品名称,批发价(sale price*cutoff)大于350的货品,注:这里WHERE后不能使用'批发价'查询
SELECT id, product_name, sale_price*cutoff as '批发价' FROM product WHERE sale_price*cutoff > 350;
注意:因为这里执行顺序是先执行where后面的条件过滤,再执行查询select看那些列数据是符合条件的。Where --> select
4.2.2逻辑运算符
如:
#选择id,货品名称,批发价(saleprice*cutoff)在300-400之间的货品
SELECT id, product_name, sale_price*cutoff as '批发价' FROM product WHERE sale_price*cutoff > 300 AND sale_price*cutoff < 400;
SELECT id, product_name, sale_price*cutoff as '批发价' FROM product WHERE sale_price*cutoff > 300 && sale_price*cutoff < 400;
#选择id,货品名称,分类编号为2,4的所有货品
SELECT id, product_name, category_id FROM product WHERE category_id = 2 or category_id = 4;
SELECT id, product_name, category_id FROM product WHERE category_id = 2 || category_id = 4;
SELECT id, product_name, category_id FROM product WHERE category_id in (2, 4);
#选择id,货品名称,分类编号不为2的所有商品
SELECT id, product_name, category_id FROM product WHERE category_id != 2;
SELECT id, product_name, category_id FROM product WHERE NOT category_id = 2;
#选择id,货品名称,分类编号并且货品零售价(sale_price)大于等于250或者是成本(cost_price)大于等于200
SELECT id, product_name, category_id FROM product WHERE sale_price >= 250 OR cost_price >= 200;
SELECT id, product_name, category_id FROM product WHERE sale_price >= 250 || cost_price >= 200;
4.2.3优先级规则
注意:括号将跨越所有优先级规则
4.2.4范围查询-BETWEEN AND
使用BETWEEN运算符显示某一值域范围的记录,这个操作符最常见的使用在数字类型数据的范围上,
但对于字符类型数据和日期类型数据同样可用。
语法:
Where 列名 between minvalue
and maxvalue
注意:
between 的范围是包含两边的边界值
eg: id between 3 and 7 等价与 id >=3
and id<=7
not between 的范围是不包含边界值
eg:id not between 3 and
7 等价与 id <3 or id>7
如:
#选择id,货品名称,批发价在300-400之间的货品
SELECT id, product_name, sale_price*cutoff
as '批发价' FROM product WHERE sale_price*cutoff BETWEEN 300 AND 400;
#选择id,货品名称,批发价不在300-400之间的货品
SELECT id, product_name, sale_price*cutoff as '批发价' FROM product WHERE sale_price*cutoff NOT BETWEEN 300 AND 400;
4.2.5集合查询-in
使用IN运算符,判断列的值是否在指定的集合中。
语法:
where 列名 in (值1, 值2);
如:
#选择id,货品名称,分类编号为2,4的所有货品
SELECT id, product_name, category_id FROM product WHERE category_id IN (2, 4);
#选择id,货品名称,分类编号不为2,4的所有货品
SELECT id, product_name, category_id FROM product WHERE category_id NOT IN (2, 4);
4.2.6空值查询-IS NULL
IS NULL:判断列的值是否为空。
语法:
where 列名 is null
注意:查询是否为NULL不能用=;
如:
#查询商品名为不为NULL的所有商品
SELECT * FROM product WHERE product_name is NOT NULL;
4.2.7模糊查询-LIKE
使用LIKE运算符执行通配查询,查询条件可包含文字字符或数字:
通配符:用来实现匹配部分值的特殊字符。
%通配符:可表示零或多个字符。
_通配符:可表示一个字符。
__通配符:可表示两个字符。
语法:
where 列名 like ‘%’(或者_或者__)
如:
#查询id,货品名称,货品名称匹配'%罗技M9_'
SELECT id, product_name FROM product WHERE
product_name LIKE '%罗技M9_';
#查询id,货品名称,分类编号,零售价大于等于50并且货品名称匹配'%罗技M1__
SELECT id, product_name, category_id,
sale_price FROM product WHERE sale_price >= 50 AND product_name LIKE '%罗技M1__';
4.3结果排序
使用 ORDER BY 子句将记录排序
ASC : 升序,缺省。
DESC: 降序。
ORDER BY 子句出现在SELECT语句的最后。
语法:
select *(列1,列2,…列N) from 表名 where 条件 order by 列名1
[ASC/DESC], 列名2 [ASC/DESC]
如:
#查询M系列并按照批发价排序(加上别名),默认ASC升序,可以省略
SELECT id, product_name, sale_price*cutoff
as '批发价'
FROM product WHERE product_name LIKE '%M%'
ORDER BY sale_price*cutoff ASC;
#查询分类为2的商品并按照批发价排序(加上别名),可以使用中文别名排序,因为ORDER BY在最后
SELECT category_id, product_name, sale_price*cutoff as '批发价'
FROM product WHERE category_id = 2
ORDER BY '批发价' DESC;
4.4分页查询LIMIT
对select * from product得出的结果进行分页显示,每页显示固定条数。
语法:
select 列1, 列2...
from 表名
[where 条件]
limit beginIndex, pageSize;
beginIndex:是开始索引(从0开始):第一条记录:0,第二条记录:1
pageSize:每页显示多少条数据
beginIndex = (当前页数 - 1) * pageSize
如:
#每页6条数据,要查询前3页,LIMIT
pageIndex, pageSize
SELECT * FROM product LIMIT 0, 6;
SELECT * FROM product LIMIT 6, 6;
SELECT * FROM product LIMIT 12, 6;
注意:LIMIT后面没有()
4.5聚集函数
聚集函数作用于一组数据,并对一组数据返回一个值。
比如,2,4,6,7,8 一组数据中,最大数8,最小数2,一共多少数 5个,它们的总和27,平均数27/5 ,mysql中聚集函数就完成上面的功能
常见的聚集函数如下:
COUNT:统计结果记录数
MAX:
统计计算最大值
MIN:
统计计算最小值
SUM:
统计计算求和
AVG:
统计计算平均值
具体使用如下:
#查询所有商品平均零售价
SELECT
AVG(sale_price) FROM product;
#查询商品总记录数
SELECT COUNT(id) FROM product;
查询分类为2的商品总数
SELECT COUNT(category_id) FROM product WHERE category_id = 2;
#查询商品的最小零售价,最高零售价,以及所有商品零售价总和
SELECT MIN(sale_price), MAX(sale_price), SUM(sale_price) FROM product;
注意:使用聚集函数的时候,不要添加列名
4.6分组查询GROUP BY
Group by --- group表示组,by 依靠什么分组;
可以使用GROUP BY 子句将表中的数据分成若干组,再对分组之后的数据做统计计算,一般使用聚集函数才使用GROUP BY.
语法:
select 聚集函数 from 表名 where 条件 group by having 分组后的条件
注意:GROUP BY 后面的列名的值要有重复性出现,分组才有意义。
使用HAVING字句,对分组之后的结果作筛选。
不能在
WHERE 子句中使用组函数(注意)。
可以在
HAVING 子句中使用组函数。
如:
#查询每个商品分类编号和每个商品分类各自的平均零售价(求2,3,4每类商品的平均零售价)
SELECT category_id,AVG(sale_price) FROM product GROUP BY category_id;
#查询每个商品分类编号和每个商品分类各自的商品总数。(求2,3,4每类商品的总数)
SELECT category_id,COUNT(category_id) FROM product GROUP BY category_id;
#查询每个商品分类编号和每个商品分类中零售价大于100的商品总数:
SELECT category_id, COUNT(*) FROM product WHERE sale_price > 100 GROUP BY category_id;
#查询零售价总和大于1500的 商品分类编号以及总零售价和;(根据分类编号查零售价的总和,然后零售价总和大于1500)
SELECT SUM(sale_price), category_id FROM product GROUP BY category_id HAVING SUM(sale_price) > 1500;
什么时候用where,什么时候用having?
Where 是先过滤已有的数据(数据是已经存在的),在进行分组,在聚集计算
Having 先分组,在对每组进行计算,根据得到结果再过滤(分组把数据算出之后,再过滤)
4.7Select语句总体执行顺序
1.from语句----2.where语句----3.group by语句----4.having语句----5.select语句----6.order by语句,根据目前1-4章节总结。
更详细的总体执行顺序:
(1)from
(2) join
(3) on
(4) where
(5)group by(开始使用select中的别名,后面的语句中都可以使用)
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by
如果觉得《mysql查询主键sql语句_MySQL数据库-表操作-SQL语句(一)》对你有帮助,请点赞、收藏,并留下你的观点哦!