MySQL数据库-表操作-SQL语句（一）

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