目录
数据库表的创建说明:
一、数据库
crashcourse为数据库名
USE crashcourse;
2.显示数据库
SHOW DATABASES;
3.显示表
SHOW TABLES;
SHOW COLUMNS FROM user;
每个字段返回一行,包括字段名,数据类型,是否允许NULL,键信息,默认值以及其他信息。
SHOW STATUS;
6.显示授权用户(所有用户或特定用户)的安全权限
SHOW GRANTS;
二、检索数据
SELECT * FROM 表名;
2 .搜索特定列
单列:SELECT 列名 FROM 表名;
多列:SELECT 列名1,列名2,...... FROM 表名;
3.检索不同行
SELECT DISTINCT 列名 from 表名;
4.限制结果
select 列名 from 表名 limit 检索条数
select 列名 from 表名 limit 开始位置,检索条数
5.使用完全限定表名
select 表名.列名 from 表名;
三、排序检索数据
select 列名 from 表名 order by 列名;
按多个指定列排序
select 列名1,列名2,列名3 from 表名 order by 列名1,列名2;
按指定列降序排序(升序换ASC,不指定的话都为ASC)
select 列名1,列名2,列名3 from 表名 order by 列名1 DESC;
先将产品按价格降序排序再对产品名排序
select prod_id,prod_price,prod_name from products order by
prod_price DESC,prod_name;
综合,order语句使用必须在from后limit前
select prod_price from products order by prod_price desc limit 1;
四、过滤数据(order语句位于where语句之后)
select pro_name,prod_price from products where prod_price = 2.5;
1、where语句操作符
操作符
|
说明
|
=
|
等于
|
<>
|
不等于
|
!=
|
不等于
|
<
|
小于
|
<=
|
小于等于
|
>
|
大于
|
>=
|
大于等于
|
BETWEEN
|
在指定的两个值之间
|
select prod_name,pro_price from products where prod_price
between 5 and 10;
select prod_name,pro_price from products where prod_price
between 条件1 and 条件2;
3、空值检查:
select prod_name from products where pro_price is null;
注:null--无值,与字段包含0,空字符串或仅仅包含空格不同。
select prod_name,pro_price from products where prod_price
between 条件1 OR 条件2;
由于AND的优先级高于OR,所以采用以下方法连用:
select prod_name,pro_price from products where prod_price
between (条件1 OR 条件2)AND 条件3;
5、IN操作符:
select prod_name,prod_price from products where vend_id in(1002,1003) order by prod_name;
筛选vend_id 为1002或1003制造的所有产品
select prod_name,prod_price from products where vend_id not in(1002,1003) order by prod_name;
匹配1002和1003之外供应商的vend_id
五、用通配符进行过滤
select type,currency from exchange_asset_details where type like 'transfer%';
执行这条语句时,检索任意以transfer起头的词。%告诉mysql接受transfer之后的任意字符,不管他有多少个字符。
select type,currency from exchange_asset_details where type like '%transfer%';
'%transfer%'表示匹配任何包含transfer的字符,不管前后是什么字符。
select type,currency from exchange_asset_details where currency like 'BT_';
select prod_name from products where prod_name REGEXP '1000'
order by prod_name;
输出
select prod_name from products where prod_name REGEXP '.000'
order by prod_name;
输出:
select prod_name from products where prod_name REGEXP '1000 | 2000' order by prod_name;
4.匹配特定字符
select prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name;
输出:
select prod_name from products where prod_name REGEXP '.' order by prod_name;
匹配查找特殊字符:
select prod_name from products where prod_name REGEXP '\\.' order by prod_name;
输出:
元字符
|
说明
|
*
|
0个或多个匹配
|
+
|
一个或多个匹配(等于{1,}
|
?
|
0个或1个匹配(等于{0,1}
|
{n}
|
指定数目的匹配
|
{n,}
|
不少于指定数目的匹配
|
{n,m}
|
匹配数目范围(m不超过255)
|
select prod_name from products where prod_name REGEXP
'\\([0-9] sticks?\\)' order by prod_name;
输出:
select prod_name from products where prod_name REGEXP '[[:digit:]] {4}' order by prod_name;
输出:
元字符
|
说明
|
^
|
文本的开始
|
$
|
文本的结尾
|
[[:<:]]
|
词的开始
|
[[:>:]]
|
词的结尾
|
select prod_name from products where prod_name REGEXP
'^[0-9\\.]' order by prod_name;
输出:
七、创建计算字段
select Concat(user_id,'(',currency,')') from exchange_asset_details order by user_id;
输出:
select Concat(RTrim(user_id),'(',RTrim(currency),')') from exchange_asset_details order by user_id;
RTrim()函数去掉值右边的所有空格
select Concat(RTrim(user_id),'(',RTrim(currency),')') As user_title from exchange_asset_details order by user_id;
输出:
select user_id,quantity,fee,quantity*fee as total from exchange_asset_details where user_id = '1000000123';
八、使用数据处理函数
select vend_name,Upper(vend_name) as vend_name_upcase from vendors order by vend_name
输出:
函数
|
说明
|
Left()
|
返回串左边的字符
|
Length()
|
返回串长度
|
Locate()
|
找出串的一个子串
|
Lower()
|
将串转换为小写
|
LTrim()
|
去掉左边的空格
|
Ringt()
|
返回右边的字符
|
RTrim()
|
去掉右边的空格
|
Soundex()
|
返回串的SOUNDEX的值
|
SubString()
|
返回子串的字符
|
Upper()
|
将串转换为大写
|
select cust_id,order_num from orders where date(order_date) = '2005-09-01'
select cust_id,order_num from orders where date(order_date) between '2005-09-01' and '2005-09-30'
或
select cust_id,order_num from orders where year(order_date)=2005 and month(order_date)=9
函数
|
说明
|
Abs()
|
返回一个数的绝对值
|
Cos()
| |
Exp()
|
返回一个数的指数值
|
Mod()
|
返回除操作的余数
|
Pi()
|
返回圆周率
|
Rand()
|
返回一个随机数
|
Sin()
| |
Sqrt()
|
返回一个角度的正切
|
九、汇总数据
函数
|
说明
|
AVG()
|
返回某列平均值
|
COUNT()
|
返回某列行数
|
MAX()
|
返回某列最大值
|
MIN()
|
返回某列最小值
|
SUM()
|
返回某列值之和
|
select AVG(prod_price) As avg_price from products
select count(*) AS num_cust from customers
返回具有电子邮件地址的客户总数3
select count(cust_email) as num_cust from customers
distact只能用于count(),不能用于count(*)
十、分组数据
select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2
具有两个(含)以上,价格为10(含)以上的产品供应商:
select vend_id,count(*) as num_prods from products where prod_price >= 10 GROUP BY vend_id having count(*) >=2
检索总计订单价格大于等于50的订单的订单号和总订单价格且按总计订单价格排序:
select order_num,sum(quantity*item_price) as ordertotal from orderitems group by order_num having sum(quantity*item_price) >= 50 order by ordertotal
十一、子查询
select cust_name,cust_contact from customers where cust_id in (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 inner join products on vendors.vend_id = products.vend_id;
与子查询结果等同的用法:
select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num = orders.order_num and prod_id = 'TNT2'
十三、创建高级联结
select c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o,orderitems as oi where c.cust_id = o.cust_id and oi.order_num= o.order_num and prod_id = 'FB';
外部联结(left or right) outer join on与内部联结inner join on的区别在于外部联结包含了那些没有下单,也就是单量为0的客户。
十四、组合查询
select vend_id,prod_id,prod_price from products where prod_price <= 5;
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002);
组合上述查询:
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)
或者
select vend_id,prod_id,prod_price from products where prod_price <= 5 or vend_id in(1001,1002)
注:union从查询结果集中自动去除了重复的行,若要返回所有匹配行,则使用union all
十五、全文本搜索
select note_text, match(note_text) against('rabbit') as rank from productnotes
十六、插入数据
insert into customers values('10006','leiyuxing','100 Main Street','fujian','CA','90047','USA',NULL,NULL);
也可指定,顺序自定,值对应即可
insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email)
values('10006','leiyuxing','100 Main Street','fujian','CA','90047','USA',NULL,NULL);
注:可以用insert low_priority info来降低插入优先级
insert info 表名1(表参1)values(对应表参值1);
insert info 表名1(表参1)values(对应表参值2);
第二种方式:
insert info 表名1 (表参) values(对应表参值1),(对应表参值2);
3、插入检索出的数据
CREATE TABLE custnew
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
将 customers的数据导入新表操作:
insert into custnew (cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email)
select cust_id,cust_name,cust_address,cust_city,cust_state, cust_zip,cust_country,cust_contact,cust_email from customers;
插入某些查询结果
十七、更新或删除数据
update customers set cust_email = 'lyx@qq.com' where cust_id ='10005';
注意,没有where语句将更新所有行,更新多个列时用,隔开。
update customers set cust_mail = null where cust_id=10005
2、删除数据
delete from customers where cust_id = 10006
2.2、删除所有行
truncate table
十八、创建和操纵表
create table 表名(
列名1 数据类型 约束,
列名2 数据类型 约束,
列名3 数据类型 约束 default 1,
primary key (列名)
)enging=innoDB;
1.1、NULL值是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行,即在插入行或更新行时,该列必须有值。
select last_insert_id();
1.4、指定默认值default
alter table vendors add vend_phone CHAR(20);
3.2、删除列
alter table vendors drop column vend_phone ;
drop table 表名;
rename table a to b;
十九、使用视图
create view
例如:
create view productcustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderitems.order_num =orders.order_num;
show create view viewname
二十、管理用户
use mysql;
select user from user;
create user jack IDENTIFIED by '1234'
rename user jack to james;
drop user james
drop只能删除用户账号,若要删除相关权限,则要先使用revoke删除与账号相关的权限,然后再用drop user删除账号
show grants for jack
GRANT, USAGE ON *.* TO 'jack'@'%',其中 USAGE ON *.* 表示根本没有权限,如果不指定主机名,则使用默认主机名%
- 要授予的权限
- 被授予访问权限的数据库或表
- 用户名
grant select on crashcourse.* to jack;
说明允许用户在crashcourse.*(crashcourse数据库的所有表)上使用select。用户jack对crashcourse数据库中的所有数据具有只读访问权限。
show grants for jack:
GRANT SELECT ON `crashcourse`.* TO 'jack'@'%'
revoke select on crashcourse.* from jack;
权限
|
描述
|
ALL PRIVILEGES
|
影响除
WITH GRANT OPTION
之外的所有权限
|
ALTER
|
影响
ALTER TABLE
命令的使用
|
ALTER ROUTINE
|
影响创建存储例程的能力
|
CREATE
|
影响
CREATE TABLE
命令的使用
|
CREATE ROUTINE
|
影响更改和弃用存储例程的能力
|
CREATE TEMPORARY TABLES
|
影响
CREATE TEMPORARY TABLE
命令的使用
|
CREATE USER
|
影响创建、弃用;重命名和撤销用户权限的能力
|
CREATE VIEW
|
影响
CREATE VIEW
命令的使用
|
DELETE
|
影响
DELETE
命令的使用
|
DROP
|
影响
DROP TABLE
命令的使用
|
EXECUTE
|
影响用户运行存储过程的能力
|
EVENT
|
影响执行事件的能力(从
MySQL5.1.6
开始)
|
FILE
|
影响
SELECT INTO OUTFILE
和
LOAD DATA INFILE
的使用
|
GRANT OPTION
|
影响用户委派权限的能力
|
INDEX
|
影响
CREATE INDEX
和
DROP INDEX
命令的使用
|
INSERT
|
影响
INSERT
命令的使用
|
LOCK TABLES
|
影响
LOCK TABLES
命令的使用
|
PROCESS
|
影响
SHOW PROCESSLIST
命令的使用
|
REFERENCES
|
未来
MySQL
特性的占位符
|
RELOAD
|
影响
FLUSH
命令集的使用
|
REPLICATION CLIENT
|
影响用户查询从服务器和主服务器位置的能力
|
权限
|
描述
|
REPLICATION SLAVE
|
复制从服务器所需的权限
|
SELECT
|
影响
SELECT
命令的使用
|
SHOW DATABASES
|
影响
SHOW DATABASES
命令的使用
|
SHOW VIEW
|
影响
SHOW CREATE VIEW
命令的使用
|
SHUTDOWN
|
影响
SHUTDOWN
命令的使用
|
SUPER
|
影响管理员级命令的使用,如
CHANGE
、
MASTER
、
KILL thread
、
mysqladmin debug
、
PURGE MASTER LOGS
和
SET GLOBAL
|
TRIGGER
|
影响执行触发器的能力(从
MySQL5.1.6
开始)
|
UPDATE
|
影响
UPDATE
命令的使用
|
USAGE
|
只连接,不授予权限
|
set password for jack = password('12345')
二十一、数据库维护
analyze table 表名
check table 表名
show processlist
可以用kill命令终结某个特定进程(管理员登录权限)
show variables或show status
二十二、读后感
作为一本介绍MySQL基础知识的书籍,《MySQL必知必会》非常适合初学者学习使用MySQL数据库。
第一部分介绍了MySQL的基础知识,如安装和配置。对于新手来说,这些都是非常重要的基础知识,能够快速掌握这些知识对于后续的学习非常有帮助。
第二部分介绍了如何使用MySQL操作数据,包括增加、删除、修改和查询数据等操作。这些都是MySQL的最基本的操作,作者使用了简单易懂的语言和实际的实例来讲解,读者可以轻松理解并实践。
第三部分则介绍了如何利用MySQL建立和管理表。这部分知识对于前端和后端开发来说非常重要,尤其是前端开发,需要对数据库有一定的了解。
最后,作者还介绍了如何使用MySQL进行高级操作,如联结表、子查询和事务等。对于一些高级应用开发,这部分知识也是非常有用的。
总之,《MySQL必知必会》是一本非常好的MySQL入门书籍,通过本书的学习,能够掌握MySQL的基础和进阶知识,非常适合初学者进行实践和学习。