数据库指令复习（面视准备）-Mysql篇

创建表

create database test;

use test;
 
CREATE TABLE product_ (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(30) ,
  price float ,
  PRIMARY KEY (id)
) DEFAULT CHARSET=UTF8;

列的操作：

新增列
alter table product_ add column type int;
删除列
alter table product_ drop column type

删除表

drop table product_

插入数据

INSERT into product_ (id,name,price) values (1,'phone1',1000);
或者
INSERT into product_ values (1,'phone1',1000);

查找：

常用查找数据：

select * from product_;
或者
SELECT id,name,price from product_;

条件查找：

条件查询
select * from product_ where id=1;
且关系
select * from product_ where id=1 and price<2000;
或关系
select * from product_ where id=2 or price<2000;


in关键字查询：
select * from product_ where id in (1,2,3)
嵌套查询：
select * from product_ where id in (
    select id from product_ where price<2000
)


between关键字
select * from product_ where id between 1 and 3（查找id在1和3之间的product的信息）

模糊查询：

SELECT * FROM product_ WHERE name LIKE 'phone%'

通配符：

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或者 [!charlist]	不再字符列中的任何单一字符

查询不重复：

select distinct price FROM product_;

排序输出：

顺序输出
select * from product_ order by price;
select * from product_ order by price asc;
逆序输出
select * from product_ order by price desc;
先按照价格顺序，然后按照id逆序
select * from product_ order by price asc,id desc;

修改数据：

update  product_ set price=800 where id=5

删除数据：

delete from product_ where price=800

Alias（给查询的列取别名）：

select id,name as '产品名称',price from product_ where id between 1 and 3
其中的as省略了也没有问题
select id,name '产品名称',price from product_ where id between 1 and 3

 

多表操作：

创建一个新表：

create table phone(
	id int NOT NULL,
	t_name varchar(20),
	primary key(id)
)default charset=utf8;

插入数据：

INSERT into phone values(1,'小米');
INSERT into phone values(2,'华为');
INSERT into phone values(3,'苹果');

现在的表的数据：

product_表：

id	name	price	type
1	phone1	1000	1
2	phone2	1000	2
3	phone3	2000	1
4	phone4	1000	1
5	phone5	1000	4

phone表：

id	t_name
1	小米
2	华为
3	苹果

多表查询：

select product_.id,product_.name,phone.t_name 
from product_,phone 
where product_.type=phone.id

id	name	t_name
1	phone1	小米
2	phone2	华为
3	phone3	小米
4	phone4	小米

除了上面的方法，也可以使用连接的方式

• JOIN: 如果表中有至少一个匹配，则返回行
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

内连接：

select product_.id,product_.name,phone.t_name from product_
inner join phone
on product_.type=phone.id;

id	name	t_name
1	phone1	小米
2	phone2	华为
3	phone3	小米
4	phone4	小米

左连接：

select product_.id,product_.name,phone.t_name from product_
left join phone
on product_.type=phone.id;

id	name	t_name
1	phone1	小米
2	phone2	华为
3	phone3	小米
4	phone4	小米
5	phone5

右连接：

select product_.id,product_.name,phone.t_name from product_
right join phone
on product_.type=phone.id;

id	name	t_name
1	phone1	小米
2	phone2	华为
3	phone3	小米
4	phone4	小米
		苹果

全连接(Mysql不支持全连接)：

select product_.id,product_.name,phone.t_name from product_
full join phone
on product_.type=phone.id;

id	name	t_name
1	phone1	小米
2	phone2	华为
3	phone3	小米
4	phone4	小米
		苹果
5	phone5

UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

//不允许重复
SELECT type FROM product_
UNION
SELECT id FROM phone

//允许重复
SELECT type FROM product_
UNION All
SELECT id FROM phone

 

 

 

函数：

AVG 函数返回数值列的平均值

返回商品的平均值
SELECT AVG(price) FROM product_

返回价格大于平均值的商品
select * from product_ where price>(select avg(price) from product_)

COUNT() 函数返回匹配指定条件的行数。

返回product_表中的数据个数
SELECT count(price) FROM product_

返回小于价格平均值的商品个数
SELECT count(price) FROM product_ where price<(select avg(price) from product_)
或者
SELECT count(*) FROM product_ where price<(select avg(price) from product_)

FIRST() 函数返回指定的字段中第一个记录的值（Mysql不支持，如果要实现可以使用limit）。

返回第一个
SELECT FIRST(price) FROM product_
返回最后一个
SELECT last(price) FROM product_

MAX 函数返回一列中的最大值，MIN函数返回一列中的最小值。NULL 值不包括在计算中

返回最大值
SELECT MAX(price) FROM product_
返回最小值
SELECT min(price) FROM product_

SUM 函数返回数值列的总数（总额）

求和函数
SELECT sum(price) FROM product_

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

select type,sum(price) from product_ GROUP BY type
//在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
select * from product_ GROUP BY type having type>1

UCASE 函数把字段的值转换为大写。

LCASE 函数把字段的值转换为小写。

MID 函数用于从文本字段中提取字符。

LEN 函数返回文本字段中值的长度。

ROUND 函数用于把数值字段舍入为指定的小数位数。

NOW 函数返回当前的日期和时间。

FORMAT 函数用于对字段的显示进行格式化。