GRANTALLONdatabase.*
TO'login_name'@'192.168.%'
IDENTIFIED BY'password';
REVOKE ALL ON *.* FROM login_name
DML
聚合函数
聚合函数
含义
COUNT(expr)
返回记录数目,若指定列,则返回非NULL值的行数
COUNT(DISTINCT expr)
返回不重复的非NULL记录数目
SUM() AVG()
求和 平均,可使用DISTINCT
MAX() MIN()
最大 最小
-- 指定范围 并按要求分类SELECT * FROM employees
WHERE emp_no BETWEEN 10010AND10020ORDERBY birth_date DESC;-- 去重显示SELECTDISTINCT dept_no FROM dept_emp;-- 分组 使用聚合函数SELECT emp_no, SUM(salary), AVG(salary) as avg_sal, COUNT(emp_no)
from salaries
GROUPBY emp_no
HAVING avg_sal > 70000;-- 子查询SELECT * FROM employees
WHERE emp_no IN (SELECT emp_no FROM employees WHERE emp_no > 10015)
ORDERBY emp_no DESC;SELECT emp.emp_no,emp.first_name,gender
FROM (SELECT * FROM employees WHERE emp_no > 10015) AS emp
WHERE emp.emp_no < 10019ORDERBY emp_no DESC;
(CROSS/INNER)JOIN 内(交叉)连接
select * from salaries crossjoin employees;-- select * from a cross join b;-- 设a表有a行,b表有b行-- a cross join b有a * b行-- 一共分为a部分 每一部分有b行,左侧b行内容全相同,右侧为b表的b行完整内容-- 第一部分内容如下,左侧为a表的第一行
emp_no
salary
from_date
to_date
NULL
emp_no
birth_date
first_name
last_name
gender
hire_date
10001
60117
1986-06-26
1987-06-26
10001
1953-09-02
Georgi
Facello
M
1986-06-26
10001
60117
1986-06-26
1987-06-26
10002
1964-06-02
Bezalel
Simmel
F
1985-11-21
10001
60117
1986-06-26
1987-06-26
10003
1959-12-03
Parto
Bamford
M
1986-08-28
10001
60117
1986-06-26
1987-06-26
10004
1954-05-01
Chirstian
Koblick
M
1986-12-01
10001
60117
1986-06-26
1987-06-26
10005
1955-01-21
Kyoichi
Maliniak
M
1989-09-12
10001
60117
1986-06-26
1987-06-26
10006
1953-04-20
Anneke
Preusig
F
1989-06-02
10001
60117
1986-06-26
1987-06-26
10007
1957-05-23
Tzvetan
Zielinski
F
1989-02-10
10001
60117
1986-06-26
1987-06-26
10008
1958-02-19
Saniya
Kalloufi
M
1994-09-15
10001
60117
1986-06-26
1987-06-26
10009
1952-04-19
Sumant
Peac
F
1985-02-18
10001
60117
1986-06-26
1987-06-26
10010
1963-06-01
Duangkaew
Piveteau
F
1989-08-24
10001
60117
1986-06-26
1987-06-26
10011
1953-11-07
Mary
Sluis
F
1990-01-22
10001
60117
1986-06-26
1987-06-26
10012
1960-10-04
Patricio
Bridgland
M
1992-12-18
10001
60117
1986-06-26
1987-06-26
10013
1963-06-07
Eberhardt
Terkki
M
1985-10-20
10001
60117
1986-06-26
1987-06-26
10014
1956-02-12
Berni
Genin
M
1987-03-11
10001
60117
1986-06-26
1987-06-26
10015
1959-08-19
Guoxiang
Nooteboom
M
1987-07-02
10001
60117
1986-06-26
1987-06-26
10016
1961-05-02
Kazuhito
Cappelletti
M
1995-01-27
10001
60117
1986-06-26
1987-06-26
10017
1958-07-06
Cristinel
Bouloucos
F
1993-08-03
10001
60117
1986-06-26
1987-06-26
10018
1954-06-19
Kazuhide
Peha
F
1987-04-03
10001
60117
1986-06-26
1987-06-26
10019
1953-01-23
Lillian
Haddadi
M
1999-04-30
10001
60117
1986-06-26
1987-06-26
10020
1952-12-24
Mayuko
Warwick
M
1991-01-26
事务 Transaction
事务,简而言之就是一系列操作
关系型数据库中支持事务,必须具备ACID四大属性
属性
描述
atomicity 原子性
事务的一系列操作不可分割,若完成一部分突然中断,之前的操作需要回滚
contistency 一致性
一项事务完成后,数据库必须从一个一致性状态变至另一个一致性状态
isolation 隔离性
并发执行的各事务间不能相互干扰
durability 持久性
事务一旦提交,对数据库的影响就是永久性的.即数据不可丢失
游标 Cursor
操作查询结果集的一种方法
可将游标看做指针,指向结果集的某一行
pymysql
Python连接MySQL的库
MySQLdb : 最有名的库,但不支持Python3
pymysql : 语法兼容MySQL,支持Python3
pymysql安装 : pip install pymysql
使用 :
import pymysql
from pymysql.cursors import DictCursor
conn=pymysql.connect('127.0.0.1','root','passwd','test')
cursor=conn.cursor(DictCursor)
try:
SQL='SELECT * FROM tj1 where id=%(name)s'
row=cursor.execute(SQL,{'name':11})
print(cursor.fetchall())
conn.commit()
except:
conn.rollback()
finally:
if cursor:
cursor.close()
if conn:
conn.close()
#---------------------------------------------------import pymysql
conn=pymysql.connect('127.0.0.1','root','passwd','test')
try:
with conn.cursor() as cursor:
for i in range(3):
SQL="INSERT INTO tj1 (val) VALUES (%s)"
rows=cursor.execute(SQL,i)
print(cursor.fetchall())
conn.commit()
except Exception as e:
print(e)
conn.rollback()
finally:
conn.close()
#---------------------------------------------------------import pymysql
conn=pymysql.connect('127.0.0.1','root','passwd','test')
with conn as cursor:
with cursor:
for i in range(3):
SQL="INSERT INTO tj1 (val) VALUES (%s)"
rows=cursor.execute(SQL,i)
print(cursor.fetchall())
conn.close()