MySQL数据库-表操作-SQL语句(一)

2019-07-24 09:04:18来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

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系列类型,可以保存文章的纯文本,一般用于存储大量的字符串。

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 

 


原文链接:https://www.cnblogs.com/yif0118/p/11228727.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:数据库概念介绍

下一篇:【mysql】mysql中的锁机制