MySQL学习小记
数据库 创建与删除
create database 数据库名
drop database 数据库名
数据表 创建与删除
CREATE TABLE table_name (
column_name column_type,
column_name column_type,
column_name column_type);
DROP TABLE table_name ;
INSERT INTO
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
即field与value一一对应
注: 在插入数据时 首先原表得有对应的字段
INSERT INTO runoob_tbl
-> (runoob_title, runoob_author, submission_date)
-> VALUES
-> ("学习 PHP", "菜鸟教程", NOW());
如该例 若原表无runoob_title, runoob_author，则加不进去 报错
SELECT 查询数据
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
可以使用一个或者多个表，表之间使用逗号(,)分割
SELECT 命令可以读取一条或者多条记录
可以使用 LIMIT 属性来设定返回的记录数
WHERE 选取数据
SELECT field1, field2,...fieldN
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
eg: select *
from runoob_tbl
where runoob_author="菜鸟教程";
UPDATE 更新数据
UPDATE table_name
SET field1=new-value1, field2=new-value2
[WHERE Clause]
可以在 WHERE 子句中指定任何条件 可以同时更新一个或多个字段
使用 WHERE 子句来更新 runoob_tbl 表中指定的数据
Eg:
UPDATE runoob_tbl
SET runoob_title='学习 C++' 注:多个条件之间逗号隔开
WHERE runoob_id=3;
DELETE 删除数据
DELETE FROM table_name [WHERE Clause]
eg:
删除 表中 runoob_id 为3 的记录
DELETE FROM runoob_tbl
WHERE runoob_id=3;
DML思维导图
LIKE 子句
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
可以在 WHERE 子句中使用LIKE子句
LIKE 通常与 % 一同使用，类似于一个元字符的搜索
使用 AND 或者 OR 指定一个或多个条件
可以在 DELETE 或 UPDATE 命令使用 WHERE...LIKE 子句来指定条件
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符
UNION 操作符
用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
注：
-
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
-
ALL: 可选，返回所有结果集，包含重复数据。
ORDER BY 排序
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
asc 为升序 desc为降序
注：
可以使用任何字段来作为排序的条件，从而返回排序后的查询结果
可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。可以添加 WHERE...LIKE 子句来设置条件。
SELECT *
from runoob_tbl
ORDER BY submission_date ASC;
GROUP BY 语句
根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
WITH ROLLUP
可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
即就是在group by分组之后，再次对聚合函数进行求和
MySQL 连接
-
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
-
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
-
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a INNER JOIN tcount_tbl b
ON a.runoob_author = b.runoob_author;
等价于
SELECT a.runoob_id, a.runoob_author, b.runoob_count
FROM runoob_tbl a
INNER JOIN tcount_tbl b
ON a.runoob_author = b.runoob_author;
NULL 值处理
IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
MySQL 事务
主要用于处理操作量大，复杂度高的数据
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
-
BEGIN 或 START TRANSACTION 显式地开启一个事务
-
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的
-
ROLLBACK 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；可以执行多次
-
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
-
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
-
ROLLBACK TO identifier 把事务回滚到标记点
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
[注] 一句一句的使用和执行
ALTER命令
修改数据
需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令
ADD 子句来向数据表中添加列
DROP 子句来向数据表中删除列
注： 如果你需要指定新增字段的位置 使用关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）
修改字段类型名称
如果需要修改字段类型及名称, 你可以在ALTER命令中
使用 MODIFY 或 CHANGE 子句 。
eg:
把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
修改表名
需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现
eg:
将数据表 testalter_tbl 重命名为 alter_tbl：
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
MySQL索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
创建索引
CREATE INDEX indexName ON table_name (column_name)
修改表结构 添加索引
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引
DROP INDEX [indexName] ON mytable;
唯一索引
不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
MySQL 临时表
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间
MySQL约束
表中数据的限制条件
比如有些列的值不能为空 有些值(身份证号)不能重复
主键约束
是一个列或者多个列的组合
相当于 唯一约束和非空约束的组合
每个表最多一个主键 primary key
添加单列主键
create table ep1(
eid int primary key,
name varchar(10),
salary double
);
添加多列主键
即由一个表中的多个字段组成的
注 ：当主键由多个字段组成时,不能直接在字段后面声明主键
一张表只能有一个主键,联合主键也是一个主键
create table ep1(
eid int ,
name varchar(10),
salary double
primary key(name,salary)
);
修改表结构添加主键
create table 表名(
...
);
alter table 表名 add primary key(字段列表)
删除主键约束
alter table 数据表名 drop primary key
自增长约束
在MySQL中 当主键定义为自增长后 该主键值就不需要用户输入数据 而是由数据库系统根据定义自动复制
每增加一条记录 主键就会自动以相同的步长进行增长
关键字 auto_increment
语法
字段名 数据类型 auto_increment
操作
create table ep1(
eid int primary key auto_increment,
name varchar(10),
);
指定自增长初始值
create table ep1(
eid int primary key auto_increment,
name varchar(10),
) auto_increment=100;
MySQL DQL基本查询
简化版
select * |列名
from 表
where 条件
运算符
eg:
-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price
from product;
-- 查询商品价格是200或800的所有商品
select * from product where price = 200 or price = 800;
select * from product where price in (200,800);
-- 查询含有‘裤'字的所有商品
select * from product where pname like ‘%裤%';
-- 查询以'海'开头的所有商品
select * from product where pname like '海%';
-- 查询第二个字为'蔻'的所有商品
select * from product where pname like '_蔻%';
-- 查询category_id为null的商品
select * from product where category_id is null;
-- 查询category_id不为null分类的商品
select * from product where category_id is not null;
排序查询
select
字段名1，字段名2，……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……
注：
1.asc代表升序，desc代表降序，如果不写默认升序
2.order by用于子句中可以支持单个字段,多个字段,表达式,函数，别名
3.order by子句，放在查询语句的最后面。LIMIT子句除外
-- 1.使用价格排序(降序)
select *
from product
order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select *
from product
order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price
from product order by price desc;
聚合查询
| 聚合函数 | 作用 |
|---|---|
| count() | 统计指定列不为NULL的记录行数； |
| sum() | 计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0 |
| max() | 计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； |
| min() | 计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算； |
| avg() | 计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0 |
-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price > 200;
-- 3 查询分类为'c001'的所有商品的总和
select sum(price)
from product
where category_id = 'c001';
-- 4 查询商品的最大价格
select max(price)
from product;
-- 5 查询商品的最小价格
select min(price)
from product;
-- 6 查询分类为'c002'所有商品的平均价格
select avg(price)
from product
where category_id = 'c002';
注：count函数对null值的处理
如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。其他基本都忽略null的存在
分组查询
select 字段1,字段2…
from 表名
group by 分组字段 having 分组条件
统计各个分类商品的个数,且只显示个数大于4的信息
select category_id ,count(*)
from product
group by category_id
having count(*) > 4;
分页查询
由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示1-5条，第二页显示6-10条。
-- 查询product表的前5条记录
select * from product limit 5
-- 从第4条开始显示，显示5条
select * from product limit 3,5
INSERT INTO SELECT
将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句
insert into Table2(field1,field2,…)
select value1,value2,… from Table1 或者：
insert into Table2 select * from Table1
注：要求目标表Table2必须存在
SELECT INTO FROM
将一张表的数据导入到另一张表中，有两种选择 SELECT INTO 和 INSERT INTO SELECT
SELECT vale1, value2 into Table2 from Table1
注： 要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
正则表达式
使用 REGEXP 操作符来进行正则表达式匹配
eg:
1.SELECT name 查找name字段中以'st'为开头的所有数据
FROM person_tbl
WHERE name REGEXP '^st';
2.SELECT name 查找name字段中以'ok'为结尾的所有数据
FROM person_tbl
WHERE name REGEXP 'ok$';
3.SELECT name 查找name字段中包含'mar'字符串的所有数据
FROM person_tbl
WHERE name REGEXP 'mar';
4.查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据
SELECT name
FROM person_tbl
WHERE name REGEXP '^[aeiou]|ok$';
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 |
|---|---|
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， 'abc' 可以匹配 "plain" 中的'p'。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
MySQL的多表操作
MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系，多对多
交叉连接查询
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选
内连接查询
求多张表的交集
select * from A inner join B on 条件;
外连接查询
左外连接：left outer join
select * from A left outer join B on 条件;
右外连接：right outer join
select * from A right outer join B on 条件;
满外连接: full outer join
select * from A full outer join B on 条件;
子查询
在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。
查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age
from emp3
where age = (select max(age) from emp3);
select …from …where exists(查询语句)
-- 查询公司是否有大于60岁的员工，有则输出
select *
from emp3 a
where exists(select * from emp3 b where a.age > 60);
MySQL函数
聚合函数
group_concat()
函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp
-- 指定分隔符合并
select department,group_concat
(emp_name separator ';' )
from emp group by department;
控制流函数
select
case 100
when 50 then 'tom'
when 100 then 'mary'
else 'tim'
end ;
CASE
表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了
窗口函数
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
注:window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数
分区（PARTITION BY）
PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
排序（ORDER BY）
OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
以及窗口大小（frame_clause）。
frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集
序号函数
格式：
row_number()|rank()|dense_rank() over (
partition by ...
order by ...
)
序号函数有三个：
ROW_NUMBER()、 RANK()、 DENSE_RANK()，
可以用来实现分组排序，并添加序号。
eg:
-- 对每个部门的员工按照薪资排序，并给出排名
select dname,ename,salary,
row_number() over(partition by dname order by salary desc) as rn
from employee;
前后函数
LAG和LEAD
返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
MySQL视图
视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
MySQL的JDBC
pymysql
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='root',password='333666',database='mydb4', charset='utf8')
# 获取游标
cursor = conn.cursor()
# 执行ＳＱＬ语句 返回值就是ＳＱＬ语句在执行过程中影响的行数
sql = "select * from employee;"
row_count = cursor.execute(sql)
print("ＳＱＬ语句执行影响的行数%d" % row_count)
for line in cursor.fetchall():
print(line)
cursor.close()
conn.close()
