1.约束

# 约束
    # unsigned  设置某一个数字无符号
    # not null 某一个字段不能为空
    # default 给某个字段设置默认值
    # unique  设置某一个字段不能重复
        # 联合唯一
    # auto_increment 设置某一个int类型的字段 自动增加
        # auto_increment自带not null效果
        # 设置条件 int  unique
    # primary key    设置某一个字段非空且不能重复
        # 约束力相当于 not null + unique
        # 一张表只能由一个主键
    # foreign key    外键
        # references
        # 级联删除和更新
​
# not null
# default
create table t2(
  id int not null,
  name char(12) not null,
  age int default 18,
  gender enum('male','female') not null default 'male'
)
​
# unique 设置某一个字段不能重复
create table t3(
    id int unique,
    username char(12) unique,
    password char(18)
);
​
# 联合唯一
create table t4(
    id int,
    ip char(15),
    server char(10),
    port int,
    unique(ip,port)
);
​
# 自增
    # auto_increment
    # 自增字段 必须是数字 且 必须是唯一的
create table t5(
    id int unique auto_increment,
    username char(10),
    password char(18)
)
insert into t5(username,password) values('alex','alex3714')
​
# primary key  主键
    # 一张表只能设置一个主键
    # 一张表最好设置一个主键
    # 约束这个字段 非空（not null） 且 唯一（unique）
​
create table t6(
    id int not null unique,     # 你指定的第一个非空且唯一的字段会被定义成主键
    name char(12) not null unique
)
​
create table t7(
    id int primary key,     # 你指定的第一个非空且唯一的字段会被定义成主键
    name char(12) not null unique
)
​
# 联合主键
create table t4(
    id int,
    ip char(15),
    server char(10),
    port int,
    primary key(ip,port)
);
​
# 外键 foreign key 涉及到两张表
# 员工表
create table staff(
id  int primary key auto_increment,
age int,
gender  enum('male','female'),
salary  float(8,2),
hire_date date,
post_id int,
foreign key(post_id) references post(pid)
)
​
​
# 部门表
# pid postname post_comment post_phone
create table post(
    pid  int  primary key,
    postname  char(10) not null unique,
    comment   varchar(255),
    phone_num  char(11)
)
​
update post set pid=2 where pid = 1;
delete from post where pid = 1;
​
# 级联删除和级联更新
create table staff2(
id  int primary key auto_increment,
age int,
gender  enum('male','female'),
salary  float(8,2),
hire_date date,
post_id int,
foreign key(post_id) references post(pid) on update cascade on delete set null
)

2.修改表

# 创建项目之前
# 项目开发、运行过程中
​
# alter table 表名 add 添加字段
# alter table 表名 drop 删除字段
# alter table 表名 modify 修改已经存在的字段  的类型 宽度 约束
# alter table 表名 change 修改已经存在的字段  的类型 宽度 约束 和 字段名字
​
# alter table 表名 add 字段名 数据类型(宽度) 约束 first/after name
# alter table 表名 drop 字段名
# alter table 表名 modify name varchar(12) not null
# alter table 表名 change name new_name varchar(12) not null
​
# id name age
# alter table 表名 modify age int not null after id;
# alter table 表名 modify age int not null first;

3.表关系

# 两张表中的数据之间的关系
# 多对一   foreign key  永远是在多的那张表中设置外键
    # 多个学生都是同一个班级的
    # 学生表 关联 班级表
    # 学生是多    班级是一
# 一对一   foreign key +unique  # 后出现的后一张表中的数据 作为外键，并且要约束这个外键是唯一的
    # 客户关系表 ： 手机号码  招生老师  上次联系的时间  备注信息
    # 学生表 ：姓名 入学日期 缴费日期 结业
# 多对多  产生第三张表，把两个关联关系的字段作为第三张表的外键
    # 书
    # 作者
​
    # 出版社
    # 书

# book ：id name price author_id
# author:aid name birthday gender
​
# 作者与书 一对多
create table author(
aid primary key auto_increment,
name char(12) not null,
birthday date,
gender enum('male','female') default 'male'
)
​
create table book(
id  int  primary key,
name char(12) not null,
price float(5,2)
author_id int,
foreign key(author_id) references author(aid)
)
​
# 作者与书一对一
create table author(
aid primary key auto_increment,
name char(12) not null,
birthday date,
gender enum('male','female') default 'male'
)
​
create table book(
id  int  primary key,
name char(12) not null,
price float(5,2)
author_id int unique,
foreign key(author_id) references author(aid)
)
​
# 作者与书多对多
create table author(
aid primary key auto_increment,
name char(12) not null,
birthday date,
gender enum('male','female') default 'male'
)
​
create table book(
id  int  primary key,
name char(12) not null,
price float(5,2)
)
​
create table book_author(
id int primary key auto_increment,
book_id int not null,
author_id int not null,
foreign key(book_id) references book(id),
foreign key(author_id) references author(aid),
);
​

4.数据操作

# 增加 insert
# 删除 delete
# 修改 update
# 查询 select
​
# 增加 insert
# insert into 表名 values (值....)
    # 所有的在这个表中的字段都需要按照顺序被填写在这里
# insert into 表名(字段名，字段名。。。) values (值....)
    # 所有在字段位置填写了名字的字段和后面的值必须是一一对应
# insert into 表名(字段名，字段名。。。) values (值....),(值....),(值....)
    # 所有在字段位置填写了名字的字段和后面的值必须是一一对应
​
# value单数            values复数
# 一次性写入一行数据   一次性写入多行数据
​
# t1 id,name,age
# insert into t1 value (1,'alex',83)
# insert into t1 values (1,'alex',83),(2,'wusir',74)
​
# insert into t1(name,age) value ('alex',83)
# insert into t1(name,age) values ('alex',83),('wusir',74)
​
# 第一个角度
    # 写入一行内容还是写入多行
    # insert into 表名 values (值....)
    # insert into 表名 values (值....)，(值....)，(值....)
​
# 第二个角度
    # 是把这一行所有的内容都写入
    # insert into 表名 values (值....)
    # 指定字段写入
    # insert into 表名(字段1，字段2) values (值1，值2)
​
​
# 删除 delete
    # delete from 表 where 条件;
​
# 更新 update
    # update 表 set 字段=新的值 where 条件；
​
# 查询
    # select语句
        # select * from 表
        # select 字段,字段.. from 表
        # select distinct 字段,字段.. from 表  # 按照查出来的字段去重
        # select 字段*5 from 表  # 按照查出来的字段去重
        # select 字段  as 新名字,字段 as 新名字 from 表  # 按照查出来的字段去重
        # select 字段 新名字 from 表  # 按照查出来的字段去重

5.where语句

# 比较运算 > < = >= <= != <>
# 范围筛选
    # 多选一 字段名 in (值1,值2，值3)
        # 20000,30000,3000,19000,18000,17000
            # select * from employee where salary in (20000,30000,3000,19000,18000,17000)
    # 在一个模糊的范围里  between 10000 and 20000
        # 在一个数值区间  1w-2w之间的所有人的名字
            # select emp_name from employee where salary between 10000 and 20000;
        # 字符串的模糊查询 like
            # 通配符 % 匹配任意长度的任意内容
            # 通配符 _ 匹配一个字符长度的任意内容
        # 正则匹配 regexp  更加细粒度的匹配的时候
            # select * from 表 where 字段 regexp 正则表达式
            # select * from employee where emp_name regexp '^j[a-z]{5}'
# 逻辑运算 - 条件的拼接
    # 与 and
    # 或 or
    # 非 not
        # select * from employee where salary not in (20000,30000,3000,19000,18000,17000)
​
# 身份运算 - 关于null  is null /is not null
    # 查看岗位描述不为NULL的员工信息
    # select * from employee where post_comment is not null;
​
# 查看岗位是teacher且名字是jin开头的员工姓名、年薪
    #select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%'
    #select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'

6.group by

# 分组 group by
# select * from employee group by post
# 会把在group by后面的这个字段，也就是post字段中的每一个不同的项都保留下来
# 并且把值是这一项的的所有行归为一组
​
# 聚合  把很多行的同一个字段进行一些统计，最终的到一个结果
    # count(字段) 统计这个字段有多少项
    # sum(字段)   统计这个字段对应的数值的和
    # avg(字段)   统计这个字段对应的数值的平均值
    # min(字段)
    # max(字段)
​
# 分组聚合
    # 求各个部门的人数
    # select count(*) from employee group by post
    # 求公司里 男生 和女生的人数
    # select count(id) from employee group by sex
    # 求各部门的平均薪资
    # 求各部门的平均年龄
    # 求各部门年龄最小的
        # select post,min(age) from employee group by post
    # 求各部门年龄最大的
    # 求各部门薪资最高的
    # 求各部门薪资最低的
    # 求最晚入职的
    # 求最早入职的
    # 求各部门最晚入职的
    # 求各部门最早入职的
​
# 求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到
# 但是要得到对应的人，就必须通过多表查询
​
# 总是根据会重复的项来进行分组
# 分组总是和聚合函数一起用 最大 最小 平均 求和 有多少项

7.having、order by、limit

# having 条件  # 过滤 组
​
# 部门人数大于3的部门
# select post from employee group by post having count(*) > 3
​
# 1.执行顺序 总是先执行where 再执行group by分组
#   所以相关先分组 之后再根据分组做某些条件筛选的时候 where都用不上
# 2.只能用having来完成
​
# 平均薪资大于10000的部门
# select post from employee group by post having avg(salary) > 10000
​
​
# select * from employee having age>18
​
# order by
    # order by 某一个字段 asc;  默认是升序asc 从小到大
    # order by 某一个字段 desc;  指定降序排列desc 从大到小
    # order by 第一个字段 asc,第二个字段 desc;
        # 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列
​
# limit
    # 取前n个  limit n   ==  limit 0,n
        # 考试成绩的前三名
        # 入职时间最晚的前三个
    # 分页    limit m,n   从m+1开始取n个
    # 员工展示的网页
        # 18个员工
        # 每一页展示5个员工
    # limit n offset m == limit m,n  从m+1开始取n个

8.pymysql

import pymysql
#
conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
                 database='day40')
cur = conn.cursor()   # 数据库操作符 游标
# cur.execute('insert into employee(emp_name,sex,age,hire_date) '
#             'values ("郭凯丰","male",40,20190808)')
# cur.execute('delete from employee where id = 18')
conn.commit()
conn.close()
​
​
conn = pymysql.connect(host='127.0.0.1', user='root', password="123",
                 database='day40')
cur = conn.cursor(pymysql.cursors.DictCursor)   # 数据库操作符 游标
cur.execute('select * from employee '
            'where id > 10')
ret = cur.fetchone()
print(ret['emp_name'])
# ret = cur.fetchmany(5)
ret = cur.fetchall()
print(ret)
conn.close()
​
select * from employee where id > 10
​
​
# sql注入风险

9.多表查询

# 多表查询
    # 两张表是怎么连在一起的
    # select * from emp,department;
    # 连表查询
        # 把两张表连在一起查
        # 内链接 inner join   两张表条件不匹配的项不会出现再结果中
        # select * from emp inner join department on emp.dep_id = department.id;
        # 外连接
            # 左外连接 left join  永远显示全量的左表中的数据
            # select * from emp left join department on emp.dep_id = department.id;
            # 右外连接 right join 永远显示全量的右表中的数据
            # select * from emp right join department on emp.dep_id = department.id;
            # 全外连接
            # select * from emp left join department on emp.dep_id = department.id
            # union
            # select * from department right join emp  on emp.dep_id = department.id;
    # 子查询
        # 找技术部门的所有人的姓名
        # 先找到部门表技术部门的部门id
        # select id from department where name = '技术';
        # 再找emp表中部门id = 200
        # select name from emp where dep_id = (select id from department where name = '技术');
​
        # 找到技术部门和销售部门所有人的姓名
        # 先找到技术部门和销售部门的的部门id
        # select id from department where name = '技术' or name='销售'
        # 找到emp表中部门id = 200或者202的人名
        # select name from emp where dep_id in (select id from department where name = '技术' or name='销售');
        # select emp.name from emp inner join department on emp.dep_id = department.id where department.name in ('技术','销售');
​
# 连接的语法
# select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
    # 常用
    # 内链接
    # 左外链接
​
# 找技术部门的所有人的姓名
    # select * from emp inner join department on emp.dep_id = department.id;
# +----+-----------+--------+------+--------+------+--------------+
# | id | name      | sex    | age  | dep_id | id   | name         |
# +----+-----------+--------+------+--------+------+--------------+
# |  1 | egon      | male   |   18 |    200 |  200 | 技术         |
# |  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
# |  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
# |  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
# |  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
# +----+-----------+--------+------+--------+------+--------------+
# select * from emp inner join department on emp.dep_id = department.id where department.name = '技术'
# select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技术'
​
# 找出年龄大于25岁的员工以及员工所在的部门名称
# select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;
​
# 根据age的升序顺序来连表查询emp和department
# select * from emp inner join department as d on emp.dep_id = d.id order by age;
​
# 优先使用连表查询，因为连表查询的效率高
​
​
# 练习
# 查询平均年龄在25岁以上的部门名
# 部门名 department表
# select name from department where id in (
#   select dep_id from emp group by dep_id having avg(age)>25
# );
# 员工表
# select dep_id,avg(age) from emp group by dep_id;
# select dep_id from emp group by dep_id having avg(age)>25;
​
# 查看不足1人的部门名(子查询得到的是有人的部门id)
# 查emp表中有哪些部门id
# select dep_id from emp group by dep_id;
# 再看department表中
# select * from department where id not in (???)
# select * from department where id not in (select dep_id from emp group by dep_id);
​
# 查询大于所有人平均年龄的员工名与年龄
# select * from emp where age>(select avg(age) from emp);
​
# 查询大于部门内平均年龄的员工名、年龄
# select dep_id,avg(age) from emp group by dep_id;
# select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
# on emp.dep_id = d.dep_id where emp.age > d.avg_age;