MySQL 必知必会

阿里云-轻量应用服务器

显示数据库

SHOW DATABASES;

注意:在MySQL中,每一条SQL语句都以“;”作为结束标志。

打开数据库

语法格式:USE  数据库名;

【例】将数据库test1设置为当前数据库。

Use test1;

创建数据库

【例】 创建一个名为Bookstore的数据库,采用字符集gb2312和校对规则gb2312_chinese_ci。
CREATE DATABASE Bookstore
    DEFAULT CHARACTER SET gb2312
    COLLATE gb2312_chinese_ci;

修改数据库

【例】 修改数据库Pet的默认字符集为latin1,校对规则为latin1_swedish_ci。
ALTER DATABASE Pet
DEFAULT CHARACTER SET latin1
DEFAULT COLLATE latin1_swedish_ci;

删除数据库

语法 :DROP DATABASE [IF EXISTS] 数据库名

【例】删除数据库test2:
DROP DATABASE test2;

数据类型

  • 数字类型
    • 整数: tinyint、smallint、mediumint、int、bigint
    • 浮点数: float、double、real、decimal
  • 日期和时间: date、time、datetime、timestamp、year
  • 字符串类型
    • 字符串: char、varchar
    • 文本: tinytext、text、mediumtext、longtext
  • 二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

创建表

CREATE TABLE book (
图书编号 char(20) NOT NULL PRIMARY KEY,
图书类别 varchar(20) NOT NULL DEFAULT   ‘计算机’,
书名 varchar(40) NOT NULL ,
作者 char(10) NOT NULL ,
出版社 varchar(20) NOT NULL ,
出版时间 date NOT NULL ,
单价 float(5,2) NOT NULL ,
数量 int(5),
折扣 float(3,2) ,
封面图片 blob
) ENGINE=InnoDB;

注:从MySQL-5.5.5开始,MySQL数据库选择的默认存储引擎是InnoDB。

显示数据表文件名

SHOW TABLES

显示数据表结构

语法格式:{DESCRIBE | DESC} 表名 [列名 | 通配符 ]
DESC是DESCRIBE的简写,二者用法相同。

【例】 用DESCRIBE语句查看 Book 表的列的信息。

USE Bookstore
DESCRIBE Book ;

修改表

语法格式:

ALTER TABLE 表名
ADD [COLUMN] 列定义 [FIRST | AFTER列名] /*添加列*/
| ALTER [COLUMN] 列名 {SET DEFAULT 默认值| DROP DEFAULT}
/*修改默认值*/
| CHANGE [COLUMN] 旧列名 列定义 /*对列重命名*/
[FIRST|AFTER列名]
| MODIFY [COLUMN] 列定义 [FIRST | AFTER 列名]
/*修改列类型*/
| DROP [COLUMN] 列名 /*删除列*/
| RENAME [TO] 新表名 /*重命名该表*/

【例】 假设已经在数据库Bookstore中创建了表book,表中存在“书名”列。在表book中增加“浏览次数”列并将表中的“书名”列删除。

ALTER TABLE book ADD 浏览次数 tinyint NULL ,
DROP COLUMN 书名 ;

【例】 假设数据库Bookstore中已经存在table1表,将table1表重命名为student。

ALTER TABLE table1 RENAME TO student;

【例】 假设数据库BookStore中已经存在table2表和table3表,将table2表重命名为orders,table3表重命名为orderlist。

RENAME TABLE table2 TO orders, Table3 TO orderlist;

删除表

语法格式:DROP TABLE [IF EXISTS] 表名1 [,表名2 ] …


数据插入

语法格式:

INSERT [IGNORE] [INTO] 表名[(列名,…)]
VALUES ({表达式| DEFAULT},..),(…),…
| SET列名={表达式| DEFAULT}, …

【例】 若表Book中图书类别的默认值为“计算机”,数量默认值为NULL,插入上例那行数据。

INSERT INTO Book
(图书编号,书名, 出版日期,单价)
VALUES (
‘6626’, ‘网站制作’, ‘2010-08-16’, 33.25
);

与下列命令效果相同:

INSERT INTO Book
SET 图书编号=‘6627’, 书名=‘网页设计’,
图书类别=DEFAULT,
出版日期= ‘2010-08-16’, 单价=33.25;

数据修改

语法格式:

UPDATE [IGNORE] 表名
SET 列名1=表达式1 [,列名2=表达式2 …]
[WHERE 条件]

【例】 订单号为16的客户因某种原因退回4本图书,请在sell表中修改订购册数,同时书退回后,book表中该图书的数量要增加4本。

UPDATE sell ,book
SET sell.订购册数=订购册数-4 , book.数量=数量+4
WHERE sell.图书编号=book.图书编号 and sell.订单号=’16’;

数据删除

【例】 将Bookstore数据库的Members表中姓名为“张三”的员工的记录删除。

USE Bookstore;
DELETE FROM Members
WHERE 姓名=’张三’;

清空表

语法格式:TRUNCATE  TABLE  表名


数据查询

单表查询

SELECT [ALL | DISTINCT] 输出列表达式, …
[FROM 表名1 [ , 表名2] …] /*FROM子句*/
[WHERE 条件] /*WHERE子句*/
[GROUP BY {列名 | 表达式 | 列编号}
[ASC | DESC], … /* GROUP BY 子句*/
[HAVING 条件] /* HAVING 子句*/
[ORDER BY {列名 | 表达式 | 列编号}
[ASC | DESC] , …] /*ORDER BY子句*/
[LIMIT {[偏移量,] 行数|行数OFFSET偏移量}] /*LIMIT子句*/

选择指定的列

SELECT  字段列表  FROM 表名

定义列别名

SELECT 字段列表  [AS]   别名

替换查询结果中的数据

【例】 查询Book表中图书编号、书名和数量,对其库存数量按以下规则进行替换:若数量为空值,替换为“尚未进货”;若数量小于5,替换为“需进货”;若数量在5-50之间,替换为“库存正常”;若总学分大于50,替换为“库存积压”。列标题更改为“库存”。

SELECT 图书编号, 书名,
CASE
WHEN 数量 IS NULL THEN ‘尚未进货’
WHEN 数量 < 5 THEN ‘需进货’
WHEN 数量 >=5 and 数量<=50 THEN ‘库存正常’
ELSE ‘库存积压’
END AS 库存
FROM Book;

计算列值

【例】 对Sell表已发货的记录计算订购金额(订购金额=订购册数*订购单价),并显示图书编号和订购金额。

SELECT 图书编号, 订购册数*订购单价 AS 订购金额
FROM Sell
WHERE 是否发货= ‘已发货’;

消除结果集中的重复行

SELECT DISTINCT  字段列表

Where 子句

【例】 查询Book表中清华大学出版社和北京大学出版社出版的价格大于25元的图书。

SELECT 书名,出版社,单价 FROM Book
WHERE (出版社=’清华大学出版社’ OR 出版社=’北京大学出版社’ )
AND 单价>=25;

模式匹配(LIKE运算符)

【例】 查询Members表中姓“张”的会员的身份证号、姓名及注册时间。

SELECT 用户号,会员姓名, 注册时间 FROM Members
WHERE 会员姓名 LIKE ‘张%’;

范围比较:BETWEEN和IN

BETWEEN 操作符选取介于两个值之间的数据范围内的值(包含这两个值)。这些值可以是数值、文本或者日期。

【例】 查询Book表中2010年出版的图书的情况。

SELECT * FROM Book
WHERE 出版时间 BETWEEN  ‘2010-1-1’  AND  ‘2010-12-31’;

【例】 查询Book表中“高等教育出版社”、“北京大学出版社”和“人民邮电出版社”出版的图书的情况。

SELECT * FROM Book WHERE 出版社 IN ( ‘高等教育出版社’, ‘北京大学出版社’,
‘人民邮电出版社’);

说明:IN关键字最主要的作用是表达子查询。

空值比较

当需要判定一个表达式的值是否为空值时,使用IS  NULL关键字,格式为:表达式  IS  [ NOT ]  NULL

【例】 查询Sell表中尚未发货的订单记录。

SELECT * FROM Sell
WHERE 是否发货 IS NULL;

多表查询

【例】查找Bookstore数据库中客户订购的图书书名,订购册数和订购时间。

SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book, Sell
WHERE Book.图书编号=Sell.图书编号;

内连接

【例】 查找Bookstore数据库中客户订购的图书书名,订购册数和订购时间。

SELECT Book.书名, Sell.订购册数, Sell.订购时间
FROM Book inner join Sell
ON Book.图书编号=Sell.图书编号;

【例】 用JOIN关键字表达下列查询:查找购买了“网页程序设计”且订购数量大于5本的图书和会员姓名和订购册数。

SELECT Book.图书编号, 会员姓名, 书名, 订购册数
FROM Sell JOIN Book ON Book. 图书编号= Sell.图书编号
JOIN Members ON Sell.身份证号 = Members.身份证号
WHERE 书名 = ‘网页程序设计’ AND 订购册数>5 ;

外连接

【例】 查找所有图书的图书编号、数量及订购了图书的会员身份证号,若从未订购过,也要包括其情况。

SELECT Book.图书编号,Book.数量,身份证号
FROM Book LEFT OUTER JOIN Sell
ON Book.图书编号= Sell.图书编号;

【例】 查找订购了图书的会员的订单号、图书编号和订购册数以及所有会员的会员姓名。

SELECT 订单号,图书编号,订购册数, Members.会员姓名
FROM Sell RIGHT JOIN Members
ON Members.用户号= Sell.用户号;

子查询

【例】 查找在Bookstore数据库中张三的订单信息。

SELECT *
FROM Sell
WHERE 用户号 IN
( SELECT 用户号 FROM Members  WHERE 会员姓名 = ‘张三’ );

比较子查询

这种子查询可以认为是IN子查询的扩展,它使表达式的值与子查询的结果进行比较运算。其格式如下:

表达式 { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY } ( 子查询 )

【例】 查找Book表中所有比“网页设计”类图书价格都高的图书基本信息。

SELECT 图书编号,图书类别,单价 FROM Book
WHERE 单价>ALL
(SELECT 单价 FROM Book WHERE 图书类别=’网页设计’ );

聚合函数

  • count()
  • max(),min()
  • sum()
  • avg()

分类汇总

【例】 按图书编号分类统计其订单数和订单的平均订购册数。

SELECT 图书编号, AVG(订购册数) AS ‘订购册数’ ,
COUNT(订单号) AS ‘订单数’
FROM Sell
GROUP BY 图书编号;

Having 子句

【例】 查找Sell表中会员订单数在2笔以上且每笔订购册数都在5本以上的会员。

SELECT 用户号, AVG(订购册数)
FROM Sell
WHERE 订购册数 >5
GROUP BY 用户号
HAVING COUNT(*) > 2;

ORDER BY子句

语法格式:ORDER BY {列名 | 表达式 | 列编号} [ASC | DESC] , …

说明:ORDER BY子句后可以是一个列、一个表达式或一个正整数。正整数表示按结果表中该位置上的列排序。例如,使用ORDER BY 3表示对SELECT的列清单上的第3列进行排序。

关键字ASC表示升序排列,DESC表示降序排列,系统默认值为ASC。

【例】 将Sell表中记录按订购册数从高到低排列。

SELECT *
FROM Sell
ORDER BY 订购册数 DESC;

LIMIT子句

LIMIT子句是SELECT语句的最后一个子句,主要用于限制被SELECT语句返回的行数。

语法格式:

LIMIT {[偏移量,] 行数|行数 OFFSET 偏移量}

语法格式中的偏移量和行数都必须是非负的整数常数,偏移量指定返回的第一行的偏移量,行数是返回的行数。例如,“LIMIT 5”表示返回SELECT语句的结果集中最前面5行,而“LIMIT 3,5”则表示从第4行开始返回5行。值得注意的是初始行的偏移量为0而不是1。

【例】 查找Members表中注册时间最靠前的5位会员的信息。

SELECT *
FROM Members
ORDER BY 注册时间
LIMIT 5;


视图简介

数据库视图(view)简介 – 码中人

创建视图

语法格式:

CREATE [OR REPLACE] VIEW 视图名 [(列名列表)]
AS select语句
[WITH CHECK OPTION]

【例】创建Bookstore数据库上的jsj_sell视图,包括计算机类图书的销售的订单号,图书编号,书名,订购册数等情况。要保证对该视图的订单修改都要符合计算机类这个条件。

CREATE VIEW jsj_sell
AS
SELECT 订单号,Sell.图书编号,书名,订购册数
FROM Book, Sell
WHERE Book.图书编号=Sell. 图书编号
AND Book.图书类别=convert(‘计算机’ using gb2312)
WITH CHECK OPTION;

注:where 子句中如果字符串中有汉字,将采用系统默认的字符集,如果和表中字符集不同,需用CONVERT函数转换。

删除视图

语法格式:DROP VIEW  [IF EXISTS] 视图名1 [,视图名2] …


索引简介

MySQL 索引简介 – 码中人

创建索引

语法格式:

CREATE [UNIQUE | FULLTEXT] INDEX 索引名
ON 表名(列名[(长度)] [ASC | DESC],…)

【例】 根据Book表的书名列上的前6个字符建立一个升序索引name­_book。

CREATE INDEX name­_book
ON Book(书名(6) ASC);

【例】 在Book表的书名列上创建一个普通索引。

ALTER TABLE Book
ADD INDEX sm_book (书名);

【例】 创建sell_copy表的语句如下,sell_copy表带有身份证号和图书编号的联合主键,并在订购册数列上创建索引。

CREATE TABLE sell_copy (
身份证号 CHAR(18) NOT NULL,
图书编号 CHAR(20) NOT NULL,
订购册数 INT(5),
订购时间 DATETIME,
PRIMARY KEY(身份证号, 图书编号),
INDEX dgcs(订购册数)
);

查看索引

如果想要查看表中创建的索引的情况,可以使用SHOW INDEX FROM tbl_name语句,例如:

SHOW INDEX FROM book;

删除索引

使用DROP INDEX语句删除索引。语法格式:

DROP INDEX索引名 ON 表名

【例】 删除Book表上的sm_book索引。

DROP INDEX sm_book ON Book;

使用ALTER TABLE语句删除索引。语法格式:

ALTER [IGNORE] TABLE 表名
| DROP PRIMARY KEY /*删除主键*/
| DROP INDEX 索引名 /*删除索引*/

【例】 删除Book表上的主键和mark索引。

ALTER TABLE Book
DROP PRIMARY KEY,
DROP INDEX mark;


内置函数

MySQL 内置函数 – 码中人

流程控制语句

IF 语句

【例】 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。

DELIMITER $$
CREATE PROCEDURE COMPAR
(IN K1 INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )
BEGIN
IF K1>K2 THEN
SET K3= ‘大于’;
ELSEIF K1=K2 THEN
SET K3= ‘等于’;
ELSE
SET K3= ‘小于’;
END IF;
END$$
DELIMITER ;

说明:存储过程中K1和K2是输入参数,K3是输出参数。

CASE 语句

【例】 创建一个存储过程,当给定参数为U时返回“上升”,给定参数为D时返回“下降”,给定其他参数时返回“不变”。

DELIMITER $$
CREATE PROCEDURE var_cp
(IN str VARCHAR(1), OUT direct VARCHAR(4) )
BEGIN
CASE str
WHEN ‘U’ THEN SET direct =’上升’;
WHEN ‘D’ THEN SET direct =’下降’;
ELSE SET direct =’不变’;
END CASE;
END$$
DELIMITER ;

WHILE循环

【例】 创建一个带WHILE循环的存储过程。

DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SET v1 = v1-1;
END WHILE;
END$$
DELIMITER ;

说明:当调用这个存储过程时,首先判断v1的值是否大于零,如果大于零则执行v1-1,否则结束循环。

创建存储过程

CREATE PROCEDURE sp_name ([proc_parameter[,…]])
routine_body

【例】 编写一个存储过程,实现的功能是删除一个指定用户姓名的用户信息。

DELIMITER $$
CREATE PROCEDURE del_member(IN xm CHAR(8))
BEGIN
DELETE FROM Members WHERE 姓名=xm;
END $$
DELIMITER ;

【例】 创建一个Bookstore数据库的存储过程,根据用户姓名和书名查询订单,如果订购册数小于5本不打折,订购册数在5-10本之间,订购单价打九折,订购册数大于10本,订购单价打八折。

DELIMITER $$
CREATE PROCEDURE
dj_update(IN c_name CHAR(8), IN b_name CHAR(20))
BEGIN
DECLARE bh CHAR(20);
DECLARE yhh CHAR(10);
DECLARE sl TINYINT;
SELECT 用户号 INTO yhh FROM Members
WHERE 姓名=c_name;
SELECT 图书编号 INTO bh FROM Book WHERE 书名=b_name;
SELECT 订购册数 INTO sl FROM Sell
WHERE 用户号=yhh AND 图书编号=bh;
IF sl>=5 AND sl<=10 THEN
UPDATE Sell SET 订购单价=订购单价*0.9
WHERE 用户号=yhh AND 图书编号=bh;
ELSE
IF sl>10 THEN
UPDATE Sell SET 订购单价=订购单价*0.8
WHERE 用户号=yhh AND 图书编号=bh;
END IF;
END IF;
END$$
DELIMITER ;

查看存储过程

SHOW PROCEDURE STATUS

查看某个存储过程的具体信息

SHOW CREATE PROCEDURE sp_name

调用存储过程

CALL sp_name([parameter[,…]])

删除存储过程

DROP PROCEDURE  [IF EXISTS] sp_name

创建存储函数

语法格式:

CREATE FUNCTION sp_name ([func_parameter[,…]])
RETURNS type value
routine_body

【例】 创建一个存储函数,它返回Book表中图书数目作为结果。

DELIMITER $$
CREATE FUNCTION num_book()
RETURNS INTEGER
BEGIN
RETURN (SELECT COUNT(*) FROM Book);
END$$
DELIMITER ;

【例】创建自定义函数“fun_sum”,通过传递整型参数n,用while循环语句得到1+2+3+……+n的和;执行fun_sum(99)得到1~99相加之和。

DELIMITER $$
create function fun_sum (n int)
returns float
BEGIN
declare t int;
declare c int;
set t=1,c=1;
while c<=n do
set t=t+c;
set c=c+1;
end  while;
return t;
END $$
DELIMITER ;
select fun_sum(99);

查看存储函数

SHOW FUNCTION STATUS

调用存储函数

存储函数创建完后,就如同系统提供的内置函数(如VERSION()),所以调用存储函数的方法也差不多,都是使用SELECT关键字。语法格式为:

SELECT sp_name ([func_parameter[,…]])

创建触发器

语法格式:

CREATE TRIGGER 触发器名,触发时间,触发事件
ON 表名 FOR EACH ROW 触发器动作

【例】 创建一个触发器,当修改Sell表中订购册数时,如果修改后的订购册数小于5本,则触发器将该对应的折扣修改为1,否则,折扣修改为0.8。

DELIMITER $$
CREATE TRIGGER sell_update BEFORE UPDATE
ON Sell FOR EACH ROW
BEGIN
IF NEW.订购册数<5 THEN
UPDATE Book SET 折扣=1 WHERE 图书编号=NEW.图书编号;
ELSE
UPDATE Book SET 折扣=0.8 WHERE 图书编号=NEW.图书编号;
END IF;
END$$
DELIMITER ;

查看触发器

SHOW TRIGGERS

删除触发器

DROP TRIGGER trigger_name


关注微信公众号

码中人 微信公众号

发表评论

电子邮件地址不会被公开。 必填项已用*标注