Mysql使用笔记

MYSQL基本使用

[toc]

0、select子句顺序

select、from、where、group by、having、order by、limit

1、连接与登录

1.1、登录

mysql -u root -p 
xxxxx

1.2、选择数据库

use courese;

1.3、显示所有的数据库

show databases;

1.4、显示特定的表

show tables;
show columns from person;

1.5、显示的其他命令

show status;
show grants;
show errors;
show warning;

2、检索数据

2.1、检索多列

select prod_id, prod_name, prod_price
from products;

2.2、检索唯一的不同行

select distinct vend_id
from products;

2.3、检索特定行的数据

select prod_name
from products
limit 3,4;

select prod_name
from products
limit 4 offset 3;

注意： limit 1，1 ： 检索出第二行而不是第一行。

2.4、检索排序的数据

select prod_id, prod_price, prod_name
from products
order by prod_price desc; # 降序

select prod_id, prod_price, prod_name
from products
order by prod_price desc， prod_name; # 先按prod_price降序排序, 再按prod_name排序

注意：默认升序

数据库的排序是按数据库默认的设置来的。

3、过滤数据

3.1、where操作符

= , <>(不等于), !=(不等于), <，>, <=, >=, between(指定在两者之间);

select prod_name, prod_price
from products
where prod_price between 5 and 10;

3.2、空值检查

select prod_name 
from products
where prod_price is null;

3.3、组合查询-and、or

select prod_id, prod_price, prod_name
from products
where vend_id = 1003 and prod_price <= 10;


select prod_id, prod_price, prod_name
from products
where vend_id = 1003 or prod_price <= 10;

注意： and 的优先级高于 or。

3.4、in查询

select prod_name, prod_price
from products
where vend_id in (1002, 1003)
order by prod_name;

注：in 后跟由逗号分隔的合法值清单，清单需要在圆括号内。

3.5、not 查询

select prod_nam， prod_price
from products
where vend_id not in (1002, 1003)
order by prod_name;

4、尽量少用like

5、创建字段

5.1、拼接字段

select concat(rtrim(vend_name), '(', rtrim(vend_country), ')') as vend_title
from vendors
order by vend_name;

rtrim， ltrim， trim 用于去掉左右两边空格。

5.2、利用字段执行算术运算

select prod_id, quantity, item_price, quantity*item_price as expanded_price
from orderitems
where order_num = 20005;

6、分组

6.1、创建分组

select order_num, sum(quantity * item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity * item_price) >= 50;

注意：where与having的区别：where在数据分组前进行过滤，having在数据分组后就那些过滤。

7、子查询

7.1、嵌套查询

select cust_id
from orders
where order_num in (select order_num
										from orderitems
										where prod_id = 'TN2');

注意：子查询总是从内向外处理。为了性能考虑，不能嵌套过多的子查询。

7.2、作为计算字段

select cust_name, cust_state, (select count(*)
																from orders
																where orders.cust_id = customers.cust_id) as orders
from customers
order by cust_name;

8、联结表

8.1、外键

外键：某个表中的一列，它包含另一个表的主键，定义两个表的关系。

8.2、创建联结表（基本）

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id
order by vend_name, prod_name;

注意：

1、一条select语句中联结几个表，相应的关系是在运行中构建的。

2、联结两个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对。

3、where子句作为过滤条件，它只包含那些匹配的给点条件的行。

4、没有联结条件的联结表返回的是笛卡尔积。

5、以上的联结方式属于内部联结，等价于：

select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

8.3、联结表时使用别名

select cust_name, cust_contact
from customers as c, orders as o, ordertimes as oi
where c.cust_id = o.cust_id
		and oi.order_num = o.order_num
		and prod_id = 'TN2';

8.4、高级联结——自联结

select p1.prod_id, p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id
		and p2.prod_id = 'DTNTR'

注意：

1、使用场景：当发现某一商品（例如为”DTNTR“）存在问题，因此想知道生产该物品的所有供应商的其他物品是否也存在问题。

2、此查询使用的两个表实际是相同的表，但是为了避免产生歧义，需要使用不同的别名。

8.5、高级联结——自然联结

select c.*, o.order_num, o.order_data, oi.prod_id, oi.quantity, oi.item_price
from customers as c, order as o, ordertime as oi
where c.cust_id = o.cust_id
		and oi.order_num = o.order_num 
		and prod_id = 'FB';

注意：

1、自然联结是这样一种联结，只是选择那些唯一的列。结合通配符和手动选择完成。

8.6、高级联结——外部联结

select customers.cust_id, orders.order_num
from customers left outer join orders
on customer.cust_id = orders.cust_id

注意：

1、使用外部联结有时候会需要包含没有关联行的那些行。

2、使用左联结还是右联结，主要看需要统计哪些没有关联的行。

9、组合查询

9.1、组合多条查询

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
union 
select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);

注意：

1、union将两条select语句组合输出，union会从查询的结构自动去除重复的行。

2、如果需要返回匹配的所有行，使用 union all。

3、union中每个查询必须包含相同的列、表达式或聚集函数。

4、使用union组合查询时，只能使用一条order by子句，它必须出现在最后一条select语句之后。

10、插入数据

10.1、插入完整数据

insert into customers values(
													null,
													'Pep E. LaPew');

注意：

1、insert语句一般不会产生输出。

2、存储到每个表中的数据在values子句中，对每个列都需要提供一个值。

3、如果某个列没有值，那么使用null值。

以上一般不会采用，使用时一般会给出列名。

insert into customers(cust_name, cust_city) 
			values('Pep E. LaPew', 
							Log Angeles);

注意：由于插入比较耗时，可以考虑降低insert语句的优先级。

insert low priority into customers(cust_name, cust_city) 
										values('Pep E. LaPew', 
                            Log Angeles);

10.2、插入多行

insert low priority into customers(cust_name, cust_city) 
										values('Pep E. LaPew', 
                           'Log Angeles')，
                    			('M. Martian',
                    			 'New York');

10.3、插入检索出的数据

insert into customers(cust_contact, cust_email)
select cust_contact, cust_email
from custnew;

注意：

1、避免插入主键 cust_id，让mysql自动填充。

11、更新数据与删除数据

11.1、注意

更新语句一定要与where一起使用，否则更新全表。

11.2、更新多列数据

update ignore customers
set cust_name = 'The Fudds',
		cust_email = 'elmer@fudd.com'
where cust_id = 1005;

注意：

1、需要关键字 set。

2、最后一个set的值不需要,

3、使用ignore关键字可以在update发生错误时依旧把之后的数据也更新了。

11.3、删除特定行

delete from customers
where cust_id = 10006;

1、删除所有行，使用 truncate table 速度更快。

12、创建表

12.1、创建表和相关字段

create table customers
(cust_id   int        not null auto_increment,
 cust_name char(50)   not null, 
 quantity  int        not null, default 1,
 cust_city char(50)   null,
 primary key(cust_id)
)engine=innodb;

注意：

1、创建表：表名，字段名，字段类型，字段要求，主键，使用的数据库引擎。

2、not null：组织插入没有值的列，常用与指定必须的列；null：允许没有值的列。

3、创建多个主键：primary key (order_num, order_item)。要求插入的主键的列是唯一的。

4、指定默认值， 使用关键字 default。不允许使用函数作为默认值。

5、引擎类型：

​ innodb：可靠的、支持事务处理的、不支持全文搜索的引擎；

​ myisam：性能高，支持全文搜索，不支持事务处理；

​ memory：类似myisam，数据存储在内存，速度快。

12.2、更新表结构

alter table vendors
add vend_phone char(20);
drop column vend_phone;

注意：

1、使用alter table 更新表结构。

12.3、定义外键

alter table ordertimes
add constraint fk_ordertimes_orders
foreign key (order_num) references orders (order_num);

12.4、删除表

drop table customers;

12.5、重命名表

rename table backup_customers to customers,
						 backup_vendors to vendors;