mysql 增删改查

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;
上一篇:Filter Lookup Editor Data Source 筛选器查找编辑器数据源


下一篇:LeetCode——Department Highest Salary(花式使用IN以及GROUP BY)