本文详细介绍了MySQL数据库的基本操作，包括查看和使用数据库、创建和管理表、数据的增删改查以及各种查询技巧。涉及的关键概念有： AUTO_INCREMENT、DEFAULT、主键和外键、引擎类型、事务处理、视图和存储过程。此外，还讲解了如何进行全限定表名查询、排序、WHERE子句的使用、函数操作以及全文本搜索。
注意
- MySQL必知必会附录-表数据 http://www.forta.com/books/0672327120/
- 我自己把六张表放在数据库sty_test数据库中
- customers orderitems orders productnotes products vendors
- mysql不区分大小写，包括where中查询的列值jackpack和JackPack是不区分的
数据库操作
-
查看所有数据库
show databases; -
使用数据库
use sty_test； -
查看数据库创建时的语句
show create database sty_test;
其他操作
-
查看授权
show grants;
表操作
-
查看所有表
show tables; -
查看表的列信息
show columns from customers; 或者：describe customers; -
查看表创建时的语句
show create table customers;
创建表
CREATE TABLE `customers` (
`cust_id` int NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10012 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
注意：最好加上：if not exists
- AUTO_INCREMENT
自动增加主键，若自己赋值，则之后的规则根据自己的赋值来增加
查看最后一个AUTO_INCREMENT的值：select last_insert_id()
-
DEFAULT
可以给列赋值默认值 比如： `cust_city` char(50) DEFAULT 'BEIJING', -
主键和外键
PRIMARY KEY (`prod_id`), KEY `fk_products_vendors` (`vend_id`), CONSTRAINT `fk_products_vendors` FOREIGN KEY (`vend_id`) REFERENCES `vendors` (`vend_id`)
引擎类型
-
innoDB
事务型引擎，不支持全文本搜索 -
MyISAM
支持全文本搜索，没有事务 -
注意-外键不能跨引擎
操纵表
-
修改表
-添加列 alter table vendors add vend_phone char(20); -删除列 alter table vendors drop column vend_phone; -定义外键 alter table products add CONSTRAINT `fk_products_vendors` FOREIGN KEY (`vend_id`) REFERENCES `vendors` (`vend_id`) -
删除表
drop table customer2; -
重命名表
rename table customer1 to customer2;
增删改查（表的数据操作）
添加
insert into customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) values
('Pep E.LaPew','100 Main Street','Los Angeles','CA','90046', 'USA'),
('M. Martian','42 Galaxy Way','New York','NY','11213','USA');
添加多行写成一条语句比写成多条在执行上效率要高
在这利要注意一下，增删改执行需要的时间比较久，可能会影响select的效率，所以可以用low_priority降低增删改的优先级
如 insert low_priority into
-
插入检索数据
insert into customers (cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) select cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from customers where cust_id = 10001; 我们在这里用了相同的列名，但其实，只要select返回的数据类型和数目能对应上就可以，列名不用一致
删除
注意，删除时一定要加筛选条件，否则会删除整张表的数据
delete from customers where cust_id =10007;
-如果想删除整张表的数据，可以用truncate table 它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）
修改
注意，更新时一定要加筛选条件，否则会更改整张表的数据
update customers set cust_name='The Fudds',cust_email = 'elmer@fudd.com' where cust_id = 10005;
-可以用子查询select
-因为在更新多个数据时，有一个错了，整条语句都会失败，所以可以用关键字ignore来忽视错误，更新其他的
update ignore customers ...
-可以更改为NULL : update customers set cust_name=NULL where cust_id = 10005;
查询
- select prod_id,prod_name from products where prod_id = 'ANV01';
-
查询全部 select * from products;
-
去重 select distinct vend_id,prod_price from products;
- 注意：去重多个列时不仅仅是使vend_id一项唯一，而是把vend_id和prod_price看作一个整体来去重（简单说就是这样写，只有两项都想同时才会去重） -
限制 select vend_id from products limit 5;
- 注意：和 limit 0,5效果一样，0为起始行，默认第一行为0，可以不写 - 同select vend_id from products limit 5 offset 0; -
全限定表名 select products.vend_id from sty_test.products;
-
排序
- 升序 select prod_price,prod_name from products order by prod_price asc,prod_name asc;（默认升序，可以不写asc） - 降序 select prod_price,prod_name from products order by prod_price desc,prod_name desc; - 注意：如果要指定区分大小写排序，即a是在B前还是Z后，需要数据库管理员来处理，order by做不到，order by不区分大小写 -
where的用法
-
等于=，不等于<>/!=,两个值之间between
-
注意，字符要用‘’引起来
-
between包括初始值和末尾值
- select prod_price from products where prod_price between 5 and 10; -
空值检查 select prod_price from products where prod_price is null;
-
and和or(and优先级高)
- select vend_id,prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price>=10;因为优先级问题，这个的意思变为了查询vend_id为1002的和vend_id=1003且prod_price大于等于10的 - 解决这个问题用括号 select vend_id,prod_name,prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price>=10; -
in和not的用法
- select prod_name,prod_price from products where vend_id in (1002,1003); - select prod_name,prod_price from products where vend_id not in (1002,1003); -
通配符
- select prod_name from products where prod_name like '%jet%';（%可匹配任何数目的字符，包括0个） - select prod_name from products where prod_name like '_ ton anvil'; (_只匹配一个，不能多也不能少） -
正则匹配regexp
- 匹配一个字符 select prod_name from products where prod_name regexp 'JetPack .000'; - 太多了，详细的去看第九章。
-
函数操作
创建计算字段
-
拼接concat()
select concat(cust_name,'(',cust_country,')') from customers; -
去掉空格
RTrim()和LTrim() 用法: select RTrim(cust_name)... -
别名as
select concat(cust_name,'(',cust_country,')') as title from customers; -
加减乘除计算
select prod_price*10 as newPrice from products; -
其他玩法
select 3*2; select now();//显示当前时间
数据处理函数
-
文本
常用的列出几个 Upper(),Lower(),RTrim()和LTrim() 其余的见书p69的表11-1 普及一个有趣的----select cust_name from customers where soundex(lcust_name)=soundex('Lee') 意思是查找名字发音和lee类似的 -
时间
-注意格式为yyyy-mm-dd 如：2000-01-01 -注意因为可能数据库中存的不仅仅是日期，还有时间，所以where date = 'yyyy-mm-dd'可能匹配不到 所以我们最好用Date()函数处理一下，只保留日期 即：select cust_id from orders where Date(order_date)='2005-09-01'; -当然除了Date(),还有Time() -筛选范围用where Date(x) between yyyy-mm-dd and yyyy-mm-dd -当然也可以用Year()和Month(); select cust_id from orders where Year(order_date)=2005; 其他的见p71的表11-2 -
数值
常见的：绝对值abs(),随机数rand() 其余的见p74的表11-3
汇总数据
-
聚集函数
-
平均值：AVG()
select avg(prod_price) as avgPrice from products where vend_id=1003; avg()忽略不计算null -
计数：count()
select count(*) as num_email from customers;//返回数据(行)的数目，自然也包括null select count(cust_email) as num_email from customers;//返回有邮箱的数目,忽略null -
大小:max(),min()
都忽略null,可以算字符串 -
总和：sun()
-
-
聚集不同值
用不重复的值计算：distinct select avg(distinct prod_price) as avgPrice from products where vend_id=1003; -
组合聚集函数
select count(*) as num_items,avg(prod_price) as price_avg from products;
其余操作
分组
-
创建
select vend_id,count(*) as vend_num from products group by vend_id; 想再加上一个总数，可以在末尾加个with rollup 例如：... group by vend_id with rollup; -
过滤
select vend_id,count(*) as vend_num from products group by vend_id having count(*)>2; where是在分组前进行过滤，having在分组后再进行过滤，可以一起使用 例如：select ... where ... group by ... having ... -
排序
在group by 中可以用order by -
select子句顺序
select,from,where,group by,having,order by,limit
子查询
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
连结表
select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_name,prod_name;
一张表的外键是其他表的主键
创建高级连结
-
自联结
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'; 等价于 select prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id = 'DTNTR'); -
内/外联结
内联结：select customers.cust_id,orders.order_num from customers inner JOIN orders on customers.cust_id = orders.cust_id; 外联结：select customers.cust_id,orders.order_num from customers left outer JOIN orders on customers.cust_id = orders.cust_id; 外联结分左右，左联结指的是以左边表为基准，右边数据没有的用null显示，反之右联结以右表为基准，左边没数据的用null表示，内联结则是只显示有数据的
组合查询
select vend_id,prod_price from products where prod_price<=5 union select vend_id,prod_price from products where vend_id in (1001,1002);
自带去重
全文本查询
高级操作
视图
1
存储过程
-
创建存储过程
create procedure productpricing() begin select avg(prod_price) as priceaverage from products; end; -
使用存储过程
call productpricing(); -
删除存储过程
drop procedure productpricing; 可以判断是否存在：drop procedure if exists productpricing; -
使用参数
创建存储函数，使他接收三个参数 create procedure productpricing( out p1 decimal (8,2), out ph decimal (8,2), out pa decimal (8,2) ) begin select min(prod_price) into p1 from products; select max(prod_price) into p1 from products; select avg(prod_price) into p1 from products; end; 为了使用此存储函数，需要三个变量名，在这里，我们给他三个变量名 call productpricing(@pricelow,@pricehigh,@priceaverage); 可以调用了 select @pricelow; in的用法 create procedure ordertotal( in onumber int, out ototal decimal(8,2) ) begin select sum(item_price*quantity) from orderitems where order_num = onumber into ototal; end; 赋值 call ordertotal(20005,@total); 调用 select @total; -
建立智能存储过程
待更新 -
检查存储过程
show create procedure ordertotal; show procedure status like 'ordertotal';
游标
敬请期待
触发器
敬请期待
数据库维护
- 待更新
改善性能
敬请期待
待更新
敬请期待
navicat快捷键
- 快速运行 ctrl+r
- 运行选择的 ctrl+shift+r
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
