navicat使用、pymysql连接数据库
内容回顾
select distinct 字段1，字段2，。。。 from 表名
where 分组之前的过滤条件
group by 分组条件
having 分组之后过滤条件
order by 排序字段1 asc，排序字段2 desc
limit 5,5
as语法中给某个查询结果起别名的时候需要把查询语句中的分号去除
(select name,salary*12 as '年薪' from emp) as t1;
# 一个字段展示用户名和年龄
select concat(name,':',age) as info from emp;
# 字段为NAME和AGE，值为‘NAME:jason’,'AGE:18'
select concat("NAME:",name) as NAME,concat("AGE:",age) as AGE from emp;
# 如果拼接的符号是统一的可以用
select concat_ws(':',name,age,sex) as info from emp;
# 1.子查询相关
# 查询平均年轻在25岁以上的部门名
select name from dep
where id in
(select dep_id from emp group by dep_id having avg(age)>25);
select dep.name from emp inner join dep on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25;
# exist(了解)
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from employee
where exists
(select id from department where id > 3);
select * from employee
where exists
(select id from department where id > 250);
Navicat使用
下载地址:https://pan.baidu.com/s/1bpo5mqj
掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 建立表模型
#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键
练习题
导出的sql语句代码
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),