MySQL基本操作

作者: saltcc | 来源:发表于2019-04-14 23:25 被阅读0次

写在前面

最近在学习数据库，将学习概要记录于此，学习的知识主要来源于 <<MySQL必知必会>>，下文中涉及相关的数据表均来源于书中提供的样例表create.sql，populate.sql

创建数据库

create database study;

查看数据库

show databases;

使用数据库

use study;

导入数据表

source create.sql;
source populate.sql;

查看表结构信息

DESC tablename;

检索数据

- 检索单列
SELECT cust_name FROM customers;

- 检索多列
SELECT cust_id,cust_name, cust_address FROM customers;

- 检索所有列
SELECT * FROM customers;

- 检索不同的行
SELECT DISTINCT cust_country FROM customers;

说明:
DISTINCT关键字是作用于所有的列，而不仅仅是前置它的列，例如SELECT DISTINCT cust_country, cust_id FROM customers; 意思是customers表中cust_country与cust_id都不同的行有哪些

- 限制结果
-- 指示返回不多于2行
SELECT cust_name FROM customers LIMIT 2;
-- 指示返回行数从行1开始的2行结果
SELECT cust_name FROM customers LIMIT 1,2;

说明:
行的下标起始从0开始，行0是第一行，行1是第二行

- 限定表名/数据库名检索
SELECT customers.cust_name FROM customers;
SELECT customers.cust_name FROM study.customers;

- 排序数据检索
SELECT cust_id,cust_name FROM customers ORDER BY cust_id;

- 多列排序检索
SELECT cust_id, cust_name FROM customers ORDER BY cust_name, cust_id;

- 指定方向检索
SELECT cust_id, cust_name FROM customers ORDER BY cust_id DESC;

说明:
默认ASC升序

- 过滤数据检索
-- where子句操作符号
SELECT prod_name, prod_price FROM products where prod_price = 2.5;
SELECT prod_name, prod_price FROM products where prod_price BETWEEN 2.5 AND 10;
-- 空值检索
SELECT prod_name, prod_price FROM products where prod_price IS NULL;

MySQL子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

- where子句组合过滤
-- AND且操作
SELECT prod_name, prod_price FROM products where prod_price >2.5 and prod_id < 5
-- OR或操作
SELECT vend_id,prod_name FROM products WHERE vend_id = 1001 OR vend_id = 1002;

说明:
AND 和 OR 操作符组合建立一个WHERE子句,SQL在处理OR操作符前，优先处理AND操作符
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 and prod_price >= 10;等同于
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 and prod_price >= 10);

- IN操作符
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id IN (1002,1003);
-- 等同于：
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id = 1002 or vend_id = 1003;

- NOT操作符
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id NOT IN(1002,1003);

说明:
MySQL数据NOT可支持对IN, BETWEEN和EXISTS子句取反

- 通配符过滤
-- LIKE 操作符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

说明:
"%"匹配0/1/N多个字符，"_"匹配单个字符

- 正则检索
-- 基本字符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000';

-- '或'匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000|2000';

-- 几个字符之一匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[123] ton';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1|2|3 ton';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[^123] ton';

-- 范围匹配
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[1-5] ton';

--特殊字符匹配（特殊字符需要前置'\\'进行引导）
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '\\.';

-- 匹配字符类
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[:alnum:]';

匹配字符类

字符类	说明
`[:alnum:]`	任意字母和数字(`[a-zA-Z0-9]`)
`[:alpah:]`	任意字符(`[a-zA-Z]`)
`[:blank:]`	空格和制表(`[\\t]`)
`[:cntrl:]`	`ASCII`控制字符(`ASCII 0`到`31`和`127`)
`[:digit:]`	任意数字(`[0-9]`
`[:graph:]`	与`[:print:]`相同，但是不包括空格
`[:lower:]`	任意小写字母(`[a-z]`)
`[:print:]`	任意可打印字符
`[:punct:]`	既不在`[:alnum:]`又不在`[:cntrl:]`中的任意字符
`[:space:]`	包括空格在内的任意字符
`[:upper:]`	任意大写字母(`[A-Z]`)
`[:xdigit:]`	任意十六进制数字(`[a-fA-F0-9]`)

- 匹配多个实例
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '.anvil';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}';
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP "a*il";

正则表达式重复元字符

元字符	说明
`*`	`0`个或多个匹配
`+`	`1`个或多个匹配(`{1,}`)
`?`	`0`个或`1`个匹配(`{0,1}`)
`{n}`	指定数目匹配
`{n,}`	不少于指定数目的匹配
`{n,m}`	匹配数目的范围(`m`不超过`255`)

说明:
*在通配符和正则表达式中有其不一样的地方，在通配符中*可以匹配任意的0个或多个字符，而在正则表达式中他是重复之前的一个或者多个字符，不能独立使用的。比如通配符可以用*来匹配任意字符，而正则表达式不行，他只匹配任意长度的前面的字符,通过使用.*匹配任意字符。

- 定位符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP "^[0-9].*";

定位元字符

元字符	说明
`^`	文本开始
`$`	文本结尾
`[[:<:]]`	词的开始
`[[:>:]]`	词的结尾

- 计算别名
SELECT prod_id, quantity,item_price, quantity * item_price AS expanded_price FROM orderitems;

函数

- 拼接函数
SELECT CONCAT(quantity,' : ', item_price) AS result FROM orderitems;
- 文本处理函数
SELECT vend_name, UPPER(vend_name) FROM vendors;
SELECT vend_name, LEFT(vend_name,1) FROM vendors;
SELECT vend_name, LENGTH(vend_name) FROM vendors;
SELECT vend_name, LOCATE('LT',vend_name) FROM vendors;
SELECT vend_name, LTRIM(vend_name)FROM vendors;
SELECT vend_name, SUBSTRING(vend_name FROM 1 FOR 2)FROM vendors;
SELECT vend_name, SUBSTRING(vend_name,1,2)FROM vendors;substring

常用处理函数

文本函数	说明
`LEFT()`	返回串左边的字符
`RIGHT()`	返回串右边的字符
`LENGTH()`	返回串的长度
`LOCATE()`	返回串的一个子串
`LOWER()`	将串转换为小写
`UPPER()`	将串转换为大写
`LTRIM()`	将串左边的空格去掉
`RTRIM()`	将串右边的空格去掉
`SUBSTRING()`	返回子串的字符

日期和时间处理函数	说明
`ADDDATE()`	增加一个日期(天、周等)
`ADDTIME()`	增加一个时间(时、分等)
`CURDATE()`	返回当前日期
`CURRENT_TIME()`	返回当前时间
`DATE()`	返回日期时间的日期部分
`DATEDIFF()`	计算两个日期之差
`DATE_ADD()`	高度灵活的日期运算函数
`DATE_FORMAT()`	返回一个格式化的日期或时间串
`DAY()`	返回一个日期的天数部分
`DAYOFWEEK()`	返回一个日期对应的星期几
`HOUR()`	返回一个时间的小时部分
`MINUTE()`	返回一个时间的分钟部分
`MONTH()`	返回一个日期的月份部分
`NOW()`	返回当前日期和时间
`SECOND()`	返回一个时间的秒部分
`TIME()`	返回一个日期时间的时间部分
`YEAR()`	返回一个日期的年份部分

例： SELECT order_num FROM orders where DATE(order_date) = '2005-09-01';

说明:
数值处理函数
ABS()/COS()/EXP()/MOD()/PI()/RAND()/SIN()/SQRT()/TAN()
SELECT ABS(order_num) from orders ;
聚集函数
AVG()/COUNT()/MAX()/MIN()/SUM()

分组

- 创建分组
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id;
- 过滤分组
SELECT vend_id,COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;
- 分组和排序
SELECT vend_id,COUNT(*) AS num FROM products GROUP BY vend_id ORDER BY num;

说明:
1.SELECT 子句书写顺序总结(SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY->LIMIT)
2.执行顺序：FROM->WHERE->GROUP BY->聚集函数计算->HAVING->SELECT->ORDER BY

子查询

- 子查询
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
WHERE orders.cust_id = customers.cust_id) as od FROM customers ORDER BY cust_name;

联结表

- 创建联结表
SELECT vend_name, prod_name FROM vendors, products WHERE vendors.vend_id = products.vend_id;

- 内部联结
SELECT vend_name, prod_name FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

- 自联结
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id;

- 外部联结
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON orders.cust_id = customers.cust_id;

说明:
与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。左外部联结、右外部联结之间的差别是所关联的表的顺序不一样

组合查询

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);

说明:
UNION 从查询结果集中自动去除重复的行，如果想返回所有匹配行，可使用 UNION ALL.

插入数据

-- 插入完整的行
INSERT INTO
customers
(
    cust_name,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)
VALUES('Jack','100 street','Los Angeles','CA','9004','USA');

--插入多行
INSERT INTO
customers
(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)
VALUES('Jack','100 street','Los Angeles','CA','9004','USA')
VALUES('mao','100 street','Los Angeles','CA','9004','USA');

--插入检索出的数据
INSERT INTO
customers
(
    cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)SELECT cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country FROM customers;

更新与删除数据

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
UPDATE customers SET cust_email = 'elmer@fudd.com',cust_name = 'The Fudds' WHERE cust_id = 10005;
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
DELETE FROM customers WHERE cust_id = 10006;
TRUNCATE TABLE customers;

创建表

CREATE TABLE customers
(
  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;

说明:
1.NULL 不是空串, NULL 值是没有值，它不是空串， ' '空串是一个有效的值
2.AUTO_INCREMENT,本列每增加一行时自动增量
3.PRIMARY KEY 指定表的主键，用于唯一标识表中每个行的列，主键不允许NULL值得列
4.ENGINE：InnoDB(可靠的事务处理引擎)/MyISAM(性能高，但不支持事务处理)/MEMORY(数据存储在内存，不在磁盘中，速度快，一般适用于临时表)

更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除表

DROP TABLE customers;

重命名表

RENAME TABLE customers TO customers2;
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors;

视图

创建视图: CREATE VIEW
查看视图：SHOW CREATE VIEW viewname;
删除视图：DROP VIEW viewname;

具体事例：
- 创建视图
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;

- 查询视图
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

MySQL基本操作

写在前面

创建数据库

查看数据库

使用数据库

导入数据表

查看表结构信息

检索数据

函数

分组

子查询

联结表

组合查询

插入数据

更新与删除数据

创建表

更新表

删除表

重命名表

视图

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读