1
Navicat是一套快速、可靠的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理人员、开发人员及中小企业的需要。Navicat是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。

1
-- 语法结构：SELECT 字段 FROM 表名
2
-- 检索所有的列，使用*
3
SELECT * FROM staff
4
-- 检索单列:检索员工表中的员工编号列
5
SELECT eno FROM staff
6
-- 检索多列（检索多列的时候用逗号隔开）：检索员工表中的员工编号、员工姓名两列
7
SELECT eno,ename FROM staff
8
-- 限制检索结果（限制检索使用limit，限制的是行数）：检索员工表中的前5行
9
SELECT * FROM staff LIMIT 5
10
-- 去重检索（去重使用distinct）：员工表中有哪些不同的岗位
11
SELECT DISTINCT job FROM staff

1
-- 语法结构：SELECT 字段 FROM 表名 WHERE 条件
2
-- 数值判断：检索出员工表中工资大于10000的员工信息
3
SELECT * FROM staff WHERE sal>10000
4
-- 逻辑判断：检索出员工表中工资大于10000且奖金不为零的员工信息
5
SELECT * FROM staff WHERE sal>10000 AND bonus>0
6
-- 模糊判断：检索出员工表中名字里带有”青“、结尾为”青“、开头为”青“的员工信息
7
SELECT * FROM staff WHERE ename LIKE "%青%"
8
SELECT * FROM staff WHERE ename LIKE "%青"
9
SELECT * FROM staff WHERE ename LIKE "青%"
10
-- 多条件判断：检索出员工表中来自于1、2、5三个部门、入职时间为2013-1-1到2015-12-31且工资大于5000的员工信息
11
SELECT * FROM staff
12
WHERE
13
        deptno in(1,2,5)
14
        AND hiredate BETWEEN "2013-1-1" AND "2015-12-31"
15
        AND sal>=5000

1
-- 1、计算字段（加减乘除：+-*/）
2
-- 语法结构：SELECT 字段1+字段2 AS "新字段名" FROM 表名
3
-- 计算员工收入
4
SELECT *,sal+bonus AS "员工收入" FROM staff
5

6
-- 2、拼接字段（concat）
7
-- 语法结构：SELECT CONCAT(字段1,字段2...) AS "新字段名" FROM 表名
8
-- 生成员工工牌信息，包含名字与岗位
9
SELECT *,CONCAT(ename,"-",job) AS "工牌信息" FROM staff

1
-- 语法结构：SELECT 字段,计算字段 FROM 表名 WHERE 条件 GROUP BY 字段
2
-- 计算各岗位工资总和、奖金总和
3
SELECT job,SUM(sal) AS "工资总额",SUM(bonus) AS "奖金总额" FROM staff GROUP BY job

1
-- 语法结构：SELECT 字段,计算字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件
2
-- 计算各岗位工资总和、奖金总和，只显示奖金总和超过2000的职位
3
SELECT job,SUM(sal) AS "工资总额",SUM(bonus) AS "奖金总额" FROM staff GROUP BY job HAVING SUM(bonus)>2000

1
-- 语法结构：SELECT 字段,计算字段 FROM 表名  ORDER BY 字段 DESC
2
--默认为升序，DESC为降序
3
-- 计算各岗位工资总和、奖金总和，按照奖金降序排列
4
SELECT job,SUM(sal) AS "工资总额",SUM(bonus) AS "奖金总额" FROM staff GROUP BY job ORDER BY SUM(bonus) DESC

1
-- 计算各岗位工资总支出、奖金总支出、平均工资
2
SELECT job,SUM(sal),SUM(bonus),AVG(sal) FROM staff GROUP BY job
3

4
-- 计算工资最高值与奖金最低值
5
SELECT max(sal),MIN(bonus) FROM staff
6

7
-- 计算员工表总行数、岗位总行数、部门个数
8
SELECT COUNT(*),COUNT(job),COUNT(DISTINCT deptno) FROM staff

1
-- 返回当前的日期、时间
2
SELECT NOW(),CURDATE(),CURTIME()
3
-- 使用不同格式显示日期时间
4
SELECT DATE_FORMAT(NOW(),"%Y-%m-%d")
5
SELECT DATE_FORMAT(NOW(),"%Y-%m")
6
SELECT DATE_FORMAT(NOW(),"%Y")
7
-- 返回当前时间加一天、减一天的结果
8
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY)
9
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY)
10
-- 返回两个日期相隔时间(大的日期放在前面，返回的值为正数)
11
SELECT DATEDIFF("2015-1-1","2013-12-31" )
12

13
-- 计算员工入职天数
14
SELECT *,DATEDIFF(CURDATE(),hiredate) AS "入职天数" FROM staff
15

16
-- 添加员工入职月份字段
17
SELECT *,DATE_FORMAT(hiredate,"%m") AS "入职月份" FROM staff
18

19
-- 检索员工表中入职超过4000天的员工姓名
20
SELECT ename FROM staff WHERE  DATEDIFF(CURDATE(),hiredate)>4000

1
-- 返回员工的姓
2
SELECT ename,LEFT(ename,1) FROM staff
3
-- 返回员工姓名最后一位
4
SELECT ename,RIGHT(ename,1) FROM staff
5
-- 返回员工姓名长度
6
SELECT ename,LENGTH(ename) FROM staff

1
-- 语法结构：if(条件，为True返回的结果，为False返回的结果)
2
-- 查询员工表，新增一列用于标识收入高低，工资大于1万为高收入，工资收入小于等于1万为低收入
3
SELECT ename,sal,if(sal>10000,"高收入","低收入") FROM staff

1
-- 语法结构：
2
--         case when 条件1 then 结果1
3
--                          when 条件2 then 结果2
4
--                          when 条件3 then 结果3
5
--                          。。。
6
--                          else 默认结果
7
--          end AS 新字段名
8

9
-- 查询员工表新增员工类型，入职时间超过4000天以上为创始人，入职2500天以上为老员工，其他为新员工
10
SELECT *,CASE
11
        WHEN DATEDIFF(CURDATE(),hiredate)>4000 THEN
12
                "创始人"
13
        WHEN DATEDIFF(CURDATE(),hiredate)>2500 THEN
14
                "老员工"
15
        ELSE
16
                "新员工"
17
END AS "员工类型" FROM staff

1
-- UNION用于把来自多个SELECT语句的结果组合到一个结果集合中，也叫联合查询，需要注意的是，字段要完全相同
2
-- 语法结构：
3
-- SELECT...
4
-- UNION [ALL|DISTINCT]
5
-- SELECT...
6
-- UNION [ALL|DISTINCT]
7
-- SELECT...
8

9
-- (1)连接所有：UNION ALL
10
-- 将员工表和雇员表连接成一张大表
11
SELECT * FROM staff
12
UNION ALL
13
SELECT * FROM employee
14

15
-- 将员工表中工资大于1万元的员工与雇员表中部门为4的雇员连接成一张大表
16
SELECT * FROM staff WHERE sal>10000
17
UNION ALL
18
SELECT * FROM employee WHERE deptno in(4)
19

20
-- (2)去重连接：UNION ALL
21
-- 将员工表和雇员表连接成一张大表
22
SELECT * FROM staff
23
UNION DISTINCT
24
SELECT * FROM employee

1
-- inner join内连接
2
-- 将员工表和部门表连接成一张大表，显示只有相同部门的数据
3
SELECT * FROM staff INNER JOIN department on staff.deptno=department.deptno
4
-- INNER JOIN 与 JOIN 一致
5
SELECT * FROM staff JOIN department on staff.deptno=department.deptno
6
-- 简写
7
SELECT * FROM staff JOIN department USING(deptno)
8
-- 使用on可以连接不同的字段
9
-- 查询员工及其上级信息
10
SELECT * FROM staff AS a JOIN staff AS b on a.mgno=b.eno

1
-- LEFT JOIN左连接
2
-- 将员工表和部门表连接成一张大表以左边表为准
3
SELECT * FROM staff LEFT JOIN department ON staff.deptno=department.deptno
4
-- 连接表的部分字段
5
SELECT a.*,b.dname FROM staff AS a LEFT JOIN department AS b ON a.deptno=b.deptno
6
-- 添加where条件，取左表与右表无交集内容
7
SELECT * FROM staff LEFT JOIN department ON staff.deptno=department.deptno WHERE department.deptno is null

1
-- 右连接
2
-- 将员工表和部门表连接成一张大表以右边表为准
3
SELECT * FROM staff RIGHT JOIN department ON staff.deptno=department.deptno
4
-- 连接表的部分字段
5
SELECT a.ename,a.job,b.dname FROM staff AS a RIGHT JOIN department AS b ON a.deptno=b.deptno
6
-- 添加where条件，取左表与右表无交集内容
7
SELECT * FROM staff RIGHT JOIN department ON staff.deptno=department.deptno WHERE staff.deptno is null

1
-- 全连接：mysql不支持全连接，可以通过左右连接间接实现全连接
2
-- 将员工表和部门表连接成一张大表，显示所有的数据
3
SELECT * FROM staff LEFT JOIN department ON staff.deptno=department.deptno
4
UNION DISTINCT
5
SELECT * FROM staff RIGHT JOIN department ON staff.deptno=department.deptno

1
-- where子查询：主查询的条件是子查询的结果
2
-- 语法结构：SELECT 字段 FROM 表名 where 条件（SELECT 字段 FROM 表名）
3

4
-- （1）使用运算符
5
-- 查询员工表工资比”芋头“高的员工
6
SELECT eno,ename,sal FROM staff WHERE sal>(SELECT sal FROM staff WHERE ename="芋头")
7
-- 查询员工表中奖金与职位与”杨青青“一样的员工
8
SELECT eno,ename,job,bonus FROM staff WHERE (job,bonus)=(SELECT job,bonus FROM staff WHERE ename="杨青青")
9

10
-- （2）使用IN关键字
11
-- 查询员工表中的数据，这些数据必须同时在雇员表中出现
12
SELECT * FROM staff WHERE eno in  (SELECT eno FROM employee)
13
-- 查询员工表中的数据，这些数据必须没有在雇员表中出现
14
SELECT * FROM staff WHERE eno NOT in  (SELECT eno FROM employee)
15

16
-- （3）使用any关键词
17
-- ANY用来标识主查询的条件为满足子查询返回结果中任意一条数据记录，any有三种匹配模式：
18
-- =ANY：这与in概念一致
19
-- >ANY、>=ANY：比子查询中返回数据中最小值还要大的数据
20
-- <ANY、<=ANY：比子查询中返回数据中最大值还要小的数据
21
-- 查询员工表中工资不低于销售经理岗位工资的员工信息
22
SELECT ename,job,sal FROM staff WHERE sal>ANY(SELECT sal FROM staff WHERE job="销售经理")
23
-- 查询员工表中奖金不高于销售专员的员工信息
24
SELECT ename,job,bonus FROM staff WHERE bonus<ANY(SELECT bonus FROM staff WHERE job="销售专员")
25

26
-- （4）使用all关键词
27
-- 表示主查询的条件为满足子查询返回结果的所有记录，all有两种匹配模式
28
-- >ALL：大于所有
29
-- <ALL：小于所有
30
-- 查询员工表中比所有销售经理工资高的员工信息
31
SELECT ename,job,sal FROM staff WHERE sal>ALL(SELECT sal FROM staff WHERE job="销售经理")
32
-- 查询员工表中奖金比所有销售专员低的员工信息
33
SELECT ename,job,bonus FROM staff WHERE bonus<ALL(SELECT bonus FROM staff WHERE job="销售专员")
34

35
-- （5）使用exists关键字
36
-- exists是一个布尔类型，当返回结果集的时候为True，不能返回结果集时为false
37
-- 查询exists对外表采用遍历方式逐条查询，每次查询都会比较exists语句，当为True时，返回当前遍历的记录；反之，当为false时，丢弃当前遍历的记录
38
-- 查询所有员工的部门信息
39
SELECT * FROM department WHERE EXISTS(SELECT * FROM staff WHERE deptno=department.deptno)

1
-- from子查询：主查询的表是子查询的结果
2
-- 语法结构：SELECT 表名1.字段 FROM （SELECT 字段 FROM 表名2）AS 表名1
3
-- 查询各部门部门号、部门名称、部门地址、员工数、平均工资
4
SELECT b.deptno,b.dname,b.location,a.num,a.avg FROM (SELECT deptno,count(*) AS num,AVG(sal) AS avg FROM staff GROUP BY deptno) AS a ,department AS b WHERE a.deptno=b.deptno
5

6
-- 员工表新增员工类型，入职时间超过4000天以上为创始人，入职2500天以上为老员工，其他为新员工;查询各类员工平均薪资
7
SELECT new_staff.员工类型,AVG(new_staff.sal) AS "平均工资" FROM
8
(SELECT *,CASE
9
        WHEN DATEDIFF(CURDATE(),hiredate)>4000 THEN
10
                "创始人"
11
        WHEN DATEDIFF(CURDATE(),hiredate)>2500 THEN
12
                "老员工"
13
        ELSE
14
                "新员工"
15
END AS "员工类型" FROM staff) AS new_staff
16
GROUP BY new_staff.员工类型

1
-- (1)Row_number()简单排序
2
-- 将员工工资进行排序，并且将排序结果进行显示
3
SELECT ename,sal,        Row_number() OVER(ORDER BY sal DESC)AS "排序" FROM staff
4

5
-- (2)DENSE_RANK()相同排位不占位
6
-- 将员工工资进行排序，并且将排序结果进行显示,处理并列情况，相同排位不占位
7
SELECT ename,sal,DENSE_RANK() OVER(ORDER BY sal DESC) AS '排序' FROM staff
8

9
-- (3)RANK()相同排位占位
10
-- 将员工工资进行排序，并且将排序结果进行显示,处理并列情况，相同排位占位
11
SELECT ename,sal,RANK() OVER(ORDER BY sal DESC) AS '排序' FROM staff
12

13
-- (4)PARTITION BY分组排序
14
-- 将员工工资进行按照部门分组排序，并且将排序结果进行显示,处理并列情况，相同排位不占位
15
SELECT deptno,ename,sal,DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS '排序' FROM staff
16

17
-- (5)ntile(n)平均分组
18
-- 将员工工资进行排序，分为三组，并且将排序结果进行显示
19
SELECT ename,sal,ntile(3) OVER(ORDER BY sal DESC)AS "排序" FROM staff

1
-- 1、创建数据库：可以使用sql创建也可以手动通过Navicat工具创建
2
-- 语法结构：CREATE DATABASE 数据库名 ;
3
-- 创建一个名字叫做case_test的数据库
4
CREATE DATABASE case_test;
5

6
-- 2、创建表：可以使用sql创建也可以手动通过Navicat工具创建
7
-- 语法结构：
8
-- CREATE TABLE 表名
9
-- (
10
-- 字段1 数据类型,
11
-- 字段2 数据类型,
12
-- 字段3 数据类型,
13
-- 字段4 数据类型,
14
-- ....
15
-- PRIMARY KEY (主键字段)
16
-- )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表里面的内容'
17

18
-- 创建一个空表
19
CREATE TABLE order_test
20
(
21
        #字段名  `order_id`
22
  #数据类型 varchar(20)
23
        #NOT NULL 非空
24
        #默认 DEFAULT ''
25
        # 注释 '唯一的订单编号'
26
  `order_id`varchar(20) NOT NULL DEFAULT '' '唯一的订单编号',
27
  `cust_id` varchar(20) NOT NULL DEFAULT '' COMMENT '顾客ID',
28
  `date` date DEFAULT NULL COMMENT '交易日期',
29
  `original_value` double(10,2) DEFAULT '0.00' COMMENT '订单原始金额',
30
  `discount` double(10,2) DEFAULT '0.00' COMMENT '订单折扣金额',
31
  `items` int(11) NOT NULL DEFAULT '0' COMMENT '订单购买的商品数量',
32
  PRIMARY KEY (`order_id`)
33
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试的订单表';
34

35
-- 3、删除表在：可以使用sql删除也可以手动通过Navicat工具删除
36
-- 语法结构：DROP TABLE 表名
37
-- 删除order_test数据表
38
DROP TABLE order_test
39

40
-- 4、删除数据库：可以使用sql删除也可以手动通过Navicat工具删除
41
-- 语法结构：DROP DATABASE 数据库名
42
-- 删除case_test数据库
43
DROP DATABASE case_test

1
-- 插入数据：可以使用sql插入也可以手动通过Navicat工具插入
2
-- （1）插入一行：VALUES中的值需要和表中的字段完全对应
3
-- 语法结构：INSERT INTO 表名 VALUES (值1,值2,....)
4
-- 向order_test 表中插入一行数据
5
INSERT INTO order_test VALUES ('0001','C1','2017/1/1','199','99','1')
6

7
-- (2)插入一行：VALUES中的值需要和标识出的字段对应
8
-- 语法结构：INSERT INTO 表名 (字段1,字段2,....) VALUES (值1,值2,....)
9
-- 向order_test 表中插入一行数据
10
INSERT INTO order_test (`order_id`,`cust_id`,`date`,`original_value`,`discount`,`items`) VALUES ('0002','C2','2017/1/1','30.7','0','3')
11

12
-- (3)插入多行：VALUES中的值需要和标识出的字段对应
13
-- 语法结构：INSERT INTO 表名 (字段1,字段2,....) VALUES (值1,值2,....),(值1,值2,....),(值1,值2,....)
14
-- 向order_test 表中插入多行数据
15
INSERT INTO order_test (`order_id`,`cust_id`,`date`,`original_value`,`discount`,`items`) VALUES
16
('0003','C3','2017/1/3','49.9','19','1'),
17
('0004','C4','2017/2/19','125','0','5'),
18
('0005','C2','2017/3/5','499','100','4'),
19
('0006','C4','2017/11/11','273.5','23.5','11')

1
-- （1）更新表数据
2
-- 语法结构:UPDATE 表名 SET 字段=新值 WHERE 字段=旧值(更新的条件)
3
-- 把order_test表中订单为0002的订单日期修改为"2018-1-1"
4
UPDATE order_test
5
set date = '2018/1/1'
6
where order_id = '0002'
7

8
-- （2）更新表结构：增加列
9
-- 语法结构:ALTER TABLE 表名 ADD 列名 数据类型
10
-- 在order_test表中增加订单实付金额“spend”列
11
ALTER TABLE order_test ADD spend DOUBLE(10,2)
12

13
-- (3)更新表结构：删除列
14
-- 语法结构:ALTER TABLE 表名 DROP 列名
15
ALTER TABLE order_test DROP spend
16

17
-- (4)更新表结构：删除行
18
-- 语法结构:DELETE FROM 表名 WHERE 条件
19
DELETE FROM order_test WHERE order_id="0001"

1
#1、python连接数据库案例
2
#引入pymysql
3
import pymysql
4
#打开数据库连接
5
#host: SQL主机地址
6
#port: 端口号
7
#user: 数据库用户名
8
# password :数据库密码
9
# db: 数据库名
10
# charset='utf8'
11
con = pymysql.connect(host="127.0.0.1",port=3306,user='root',password='19941104',db='case_sql',charset='utf8')
12
# 使用 cursor() 方法创建一个游标对象 cursor
13
cursor = con.cursor()
14
# 使用 execute() 方法执行 SQL语句
15
cursor.execute("select * from staff")
16
# 接收全部的返回结果行
17
res = cursor.fetchall()
18
print(res)
19
# 关闭连接
20
con.close()

1
#2、python连接数据库封装方法
2
import pymysql
3
import pandas as pd
4
#封装方法
5
#DB 连接数据库的参数,字典形式
6
#sql 传入进来sql语句
7
def get_mysql_data(DB,sql):
8
    conn = pymysql.connect(host=DB['host'],port=DB['port'],user=DB['user'],password=DB['password'],db=DB['dbname'],charset='utf8')
9
    # 创建一个游标
10
    cursor = conn.cursor()
11
    # 使用 execute() 方法执行 SQL语句
12
    cursor.execute(sql)
13
    # 接收全部的返回结果行
14
    data = cursor.fetchall()
15
    # cols 字段信息
16
    cols = cursor.description
17
    # 执行
18
    conn.commit()
19
    conn.close()
20

21
    #转变数据
22
    col = []
23
    for i in cols:
24
        col.append(i[0])
25
    #data转成list形式
26
    data = list(map(list,data))
27
    data = pd.DataFrame(data,columns = col)
28

29
    return data
30

31
DB = {'host':"127.0.0.1",
32
     'port': 3306,
33
     'user':'root',
34
     'password':'19941104',
35
     'dbname':'case_sql'}
36
SQL = "select * from staff"
37

38
res = get_mysql_data(DB,SQL)
39
display(res)