MySQL基础

1、mysql服务

net start mysql
net stop mysql

 1.1、进入mysql

mysql -u 用户名 -p密码

 1.2、退出mysql

exit

 
 

2、DDL（数据库定义语言）
 2.1、查询数据库

SHOW DATABASES;
-- 查询所有数据库

SHOW DATABASE();
-- 查询当前数据库

 2.2、创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

 2.3、删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

 2.4、选择数据库

USE 数据库名;

 2.5、查看数据库和表

SHOW TABLES;
-- 查看数据库内所有的表

SHOW COLUMNS FROM 表名;
DESCRIBE 表名;
-- 查看特定表详情

SHOW STATUS;
-- 显示服务器状态信息
SHOW CREATE DATABASE 和 SHOW CREATE TABLE
-- 创建特定数据库或表的MySQL语句
SHOW GRAMTS
-- 显示授权用户的安全权限
SHOW ERRORS 和 SHOW WARNINGS
-- 显示服务器错误或警告消息

 
 

3、DML（数据库操作语言）
 3.1、SELECT 语句

SELECT prod_id,prod_name,prod_price
FROM products;
-- SQL 不区分大小写，通常约定将SQL关键字使用大写，表名列名使用小写

SELECT DISTINCT vend_id
FROM products；
-- 使用 DISTINCT 关键字进行去重

SELECT prod_name
FROM products
LIMIT 5;
-- 限制输出行数
-- LIMIT 5,6; 从第五5行，开始的6行

SELECT products.prod_name
FROM crashcourse.products;
-- 假定 products 表位于 crashcourse 数据库中，则可以这么表示

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
-- 对结果首先按照 价格排序 ，再按照 名称排序 输出，排序方式默认为 升序
-- ORDER BY prod_price DESC, 逐一使用 DESC 修饰，即可 降序

SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
-- 使用 ORDER BY 和 LIMIT 的组合，可以找最值

 
 3.2、WHERE 子句

SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';
-- MySQL执行语句时默认不匹配大小写，所以 Fuses 也可匹配
-- WHERE 后加上 BINARY 关键字可以区分大小写

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
-- BETWEEN 匹配范围中所有的值，左右皆为闭区间

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;
-- 空值检查，注意结果一定要进行验证（未知具有特殊含义，数据库不知道他们是否匹配）

 
  3.3、组合 WHERE 子句

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 = 1002 OR prod_id = 1003;

SELECT prod_id, prod_price, prod_name
FROM products
WHERE (vend_id = 1002 OR prod_id = 1003) AND prod_price >= 10;
-- 在计算次序中 AND 优先级较高，可以使用圆括号明确分组

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
-- IN 操作符具有与 OR 操作符相同的功能

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
-- NOT 操作符否定跟在他后面的条件

 
  3.4、LIKE 操作符

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
-- % 表示任何字符出现任意次数，也就可以匹配到如：JetPack 1000、JetPack 2000
-- WHERE prod_name LIKE '%j%t%';
-- 注意 % 不能匹配到 NULL

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
-- _ 用途与 % 相同，但是只能匹配单个字符：1 ton anvil、2 ton anvil
-- 与 % 能匹配 0 个字符不同，_ 总是匹配一个字符，不多不少

 
  3.5、正则表达式

SELECT prod_name
FROM products
WHERE prod_name PEGEXP '.000'
ORDER BY prod_name;
-- . 是正则表达式中的一个匹配字符，与LIKE相比，正则可以搜索子集

SELECT prod_name
FROM products
WHERE prod_name PEGEXP '1000|2000'
ORDER BY prod_name;
-- | 为正则表达式的 OR 操作，表示匹配其中之一

SELECT prod_name
FROM products
WHERE prod_name PEGEXP '[123] ton'
ORDER BY prod_name;
-- [] 为另一种形式的 OR 语句，为 [1|2|3] ton 的缩写
-- 此操作可以区分与 '1|2|3 ton'
-- [] 内亦可写为 [1-3]（即等价[123]）、[a-z]

SELECT prod_name
FROM products
WHERE prod_name PEGEXP '\\.'
ORDER BY prod_name;
-- 若匹配具有特殊含义的字符，则需加上转义符 \\

-- * 0个或多个匹配
-- + 1个或多个匹配
-- ? 0个或1个匹配
-- {n} 指定数目匹配
-- {n,} 不少于指定数目的匹配
-- {n,m} 匹配数目内的范围（m不超过255）

SELECT prod_name
FROM products
WHERE prod_name PEGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
-- 匹配结果：TNT(1 stick) 、 TNT(5 sticks)

WHERE prod_name PEGEXP '[[:digit:]]{4}'
-- 等价于 '[0-9][0-9][0-9][0-9]' 表示任意四位数

 
​  3.6、定位符

-- 为匹配指定位置字符而引入
-- ^ 文本的开始
-- $ 文本的结尾
-- [[:<:]] 词的开始
-- [[:>:]] 词的结尾

-- 找一个以一个数（包括小数点），开始的所有产品
SELECT prod_name
FROM products
WHERE prod_name PEGEXP '^[0-9\\.]'
ORDER BY prod_name;

SELECT * 
FROM products 
WHERE account_id REGEXP '[[:<:]]12[[:>:]]';
-- 匹配account_id为"213 12 123123"这样的数据

 

​  3.7、计算字段

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM vendors
ORDER BY vend_name;
-- 通过使用 Concat() 函数可以拼接多个串

SELECT Concat(Rtrim(vend_name), ' (', Rtrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
-- 通过使用 RTrim() 函数可以去掉值右边的所有空格
-- 同理还有 LTrim() 、Trim()（去掉两边的空格）

SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title
FROM vendors
ORDER BY vend_name;
-- 通过 AS 关键字定义别名

-- 执行算术计算
SELECT prod_id,
       quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems
WHERE order_nem = 20005;
-- 汇总商品价格（数量 * 单价）

-- 测试计算
SELECT 3*2; -- 返回 6
SELECT Trim('abc'); -- 返回 abc
SELECT Now(); -- 返回当前日期和时间 

 

4、函数

 不同DBMS之间差异有时很大，函数的可移植性没有SQL那么强。

a. 处理文本串（删除或填充值，转换值为大小写）的文本函数

b. 在数值数据上进行算术操作（返回绝对值，进行代数运算）

c. 处理日期和时间值并从中提取特定成分（返回日期差，检查有效性）

d. 返回DBMS正使用的特殊信息（返回用户登录信息，检查版本细节）

 4.1、文本处理函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

-- Left() 返回串左边的字符
-- Length() 返回串长度
-- Locate() 找出串的一个子串
-- Lower() 将串转换为小写
-- LTrim() 去除左边空格
-- Right() 返回串右边的字符
-- RTrim() 去除右边空格
-- Soundex() 返回串的SOUNDEX值  （声音模糊匹配）
-- SubString() 返回子串的字符
-- Upper() 将串转为大写

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');
-- 可以匹配到 Coyote Inc.   Y Lee 这条数据，因为 Y Lee 和Y lie 发音相似

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;
-- 搜索日期在2005年9月份的数据

 4.2、聚集函数

SELECT AVG(prod_price) AS avg_price
FROM products;
-- 计算products表中所有产品的平均价格
-- AVG() 函数忽略列值为NULL的行

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;

 4.3、分组数据

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
-- 根据 vend_id 对数据进行分组
-- GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
-- WITH ROLLUP 额外进行一次求和

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
-- 在分组基础上进行过滤

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2
ORDER BY COUNT(*)
LIMIT 5;
-- 组合一下

5、查询嵌套

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'));
-- 获得订购物品为TNT2的所有客户信息

SELECT 	cust_name, 
		cust_state,
		(	SELECT COUNT(*)
			FROM orders
			WHERE orders.cust_id = customers.cust_id ) AS orders
FROM customers
ORDER BY cust_name;
-- 作为计算字段使用

6、联结表
 
 
 

CREATE TABLE 表名 (
  列名 类型 附加定义,
  id int(11) NOT NULL AUTO_INCREMENT, 
  ....
  PRIMARY KEY (`主键名`) 
)ENGINE=引擎 DEFAULT CHARSET=编码;

CREATE TABLE xml
(
  v1 TINYTEXT,
  v2 TINYTEXT
);