MySQL数据库查询

---

前言

一、单表查询

创建表

create table student(
id int,
name varchar(20),
chinese float,
english float,
math float
);
insert into student(id,name,chinese,english,math) values(1,'王帅',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'潘金莲',67,53,95);
insert into student(id,name,chinese,english,math) values(3,'凤姐',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'旺财',88,98,92);
insert into student(id,name,chinese,english,math) values(5,'白小黑',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'白小黄',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'范蹦蹦',75,65,30);

查询语句

-- 查询表中所有学生信息
select * from student

-- 查询表中所有学生对应的英语成绩
select name,english from student 

-- 过滤重复数据
select distinct * from student

-- 统计总分
select name as 名字 , chinese+english+math as 总分 from student

-- 查询英语成绩大于90的同学
select * from student where english>90

-- 查询总分大于200分的同学
select name,chinese+english+math as 总分 from student where chinese+english+math>200

-- 查询英语成绩在80 - 90 之间的同学
select name , english '英语' from student where english<90 and english>80

-- 对数学成绩排序后输出desc 降序 | asc升序  
select * from student order by math desc

-- 对姓白的学生成绩排序输出
select name,chinese+english+math as '总分' from student where name like '白%' order by chinese+english+math desc

二、多表查询

1.引入表

转自https://blog.youkuaiyun.com/qq_45964076/article/details/119742156

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');



-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
 


-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);



-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

2.查询语句

-- 1.查询所有员工所有信息（姓名，职位，部门,工资...）
select e.id,e.ename as '姓名',d.dname '部门',j.jname '职位',e.salary '工资' from dept as d ,emp as e , job as j where e.dept_id = d.id and e.job_id = j.id

-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select e.id as '员工编号' ,e.ename as '员工姓名' ,e.salary as '工资' ,j.jname as '职务名称' ,j.description as '职务描述' ,d.dname as '部门名称' , d.loc as 部门位置 from dept as d ,emp as e ,job as j where d.id=e.dept_id and e.job_id=j.id 

该处使用的url网络请求的数据。

---

三、功能函数

ps： 原来用着orm挺好用的， 但是有些不能使用没有django环境（不能用django项目），就把字典拼接成原生sql

1、添加

def sql_ins_data(*args):
    # insert
    """
    insert into 表名 （ks） values （v）
    -----params
    data ： 字典数据
    table_name ： 数据库表名
    """
    data, table_name = args
    keys = []
    values = []
    for k, v in zip(data.keys(), data.values()):
        if v :
	        keys.append(k)
	        values.append(v)
    sql_insert = "insert into {} {} values {}".format(table_name, str(tuple(keys)).replace("'", "").replace('"', ''),
                                                      tuple(values))
    # print(sql_insert)
    return sql_insert

2、 更新

def sql_data_upd(*args):
    """
    update info set name='chang' where age=18
    data, table_name, where_k, where_data

    -----params
    data : 数据（字典）
    table_name : 数据库表名
    condition_name : 条件字段名（where后的字段名）
    condition_value : 条件字段值（where后=的值）
    """
    data, table_name, condition_name, condition_value = args
    update_data = ''
    for k, v in zip(data.keys(), data.values()):
    	if v:
	        if type(v) is str:
	            v = "'{}'".format(v)
	        if k == list(data.keys())[-1]:
	            da = "{}={}".format(k, v)
	        else:
	            da = "{}={},".format(k, v)

        	update_data += da
    if type(condition_value) is str:
        condition_value = "'{}'".format(condition_value)
    sql_update = "update {} set {} where {}={}".format(table_name, update_data, condition_name, condition_value)
    # print(sql_update)
    return sql_update

3 pymysql操作

    def db_operation(sql_insert, sql_update):
    # 连接数据库
    	db = pymysql.connect(user="root", host="localhost", password="123456", port=3306, db="databank")
        cursor = db.cursor()
        try:
            # 执行sql语句
            count = cursor.execute(sql_update)  # 执行返回1，不执行0
            if count:
                # 提交到数据库执行
                db.commit()
                print("更新")
            else:
                print("新增")
                cursor.execute(sql_insert)
                db.commit()
        except Exception as e:
            print("报错:", e)
            # 如果发生错误则回滚
            db.rollback()
        finally:
      		db.close()