Python21期 day15 之mysql

一、mysql操作语句

SELECT 语句

select语句用于从数据库中选取数据，结果被存储在一个结果表中，称为结果集

SQL SELECT语法：

SELECT column_name,column_name FROM table_name;
与
SELECT * FROM table_name;

INSERT INTO 语句

insert into 语句用于向表中插入新记录

SQL INSERT INTO 语法：

INSERT INTO table_name VALUES(value1,value2,...);

UPDATE 语句

update 语句用于更新表中存在的记录。切记where语句不可省，否则所有记录都被更新！

SQL UPDATE语法：

UPDATE table_name SET column1=value1 WHERE column=value;

DELETE FROM语句

delete语句用于删除表中记录

SQL DELETE语句：

DELETE FROM table_name WHERE column=value;

ALTER TABLE 语句

alter table语句用来在已有的表中添加、删除或修改列

SQL ALTER TABLE语法：

1、在表中添加列，使用下面语法
ALTER TABLE table_name ADD column_name datatype

2、改变表中列的数据类型，使用下面语法
ALTER TABLE table_name MODIFY COLUMN column_name datatype

二、数据库相关操作

1、创建数据库语法

CREATE DATABASE db_name charset utf8;

2、创建表语法

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

#复制表结构：create table 新表 like 旧表
#复制表数据：create table 新表 select * from 旧表

3、数据类型

  3.1 字符类型

# char和varchar 括号内的参数指的是字符的长度

#char类型：定长，简单粗暴，浪费空间，存取速度快
    字符长度范围：0-255
#varchar类型：变长，精准，节省空间，存取速度慢
    字符长度方位：0-65535

  3.2 枚举和集合类型

字段的值只能在给定范围中选择，如单选、多选
enum 单选 只能在给定范围内选一个值，如性别sex 男/女
set 多选 在给定范围内可以选择一个或一个以上的值(兴趣1，兴趣2...)

例：
create table consumer(
    id int,
    name varchar(50),
    sex enum('male','female'),
    level enum('vip1','vip2','vip3'),    #指定范围内，多选一
    fav set('play','music','read','study')    #指定范围内，多选多
);

4、完整性约束

约束条件与数据类型的宽度一样，都是可选参数。

作用：用于保证数据的完整性和一致性

分为：
PRIMARY KEY     #主键，唯一的标识记录
FOREIGN KEY    #外键
NOT NULL        #字段不为空
UNIQUE KEY    #该字段的值是唯一的
AUTO_INCREMENT    #该字段值自动增长（整数类型，且为主键）
DEFAULT        #该字段设置默认值

UNSIGNED        #无符号
ZEROFILL        #使用0填充

使用说明：
#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'

#必须为正值（无符号） 不允许为空 默认是20
age int unsigned not null default 20

# 3. 是否是key
主键    primary key
外键    foreign key
索引    (index,unique...)

  4.1 UNIQUE

中文翻译：不同的，在mysql中称为 单列唯一

实例：
使用约束条件unique，对公司部门字段进行设置
#第一种创建unique方式
create table department(
    id int,
    name char(10) unique
);

#第二种创建unique方式
create table department(
    id int,
    name char(10),
    unique(id),
    unique(name)
);

  联合唯一：

#创建services 表

create table services(
    id int,
    ip char(15),
    port int,
    unique(id),
    unique(ip,port)
);
# 联合唯一，只要两列记录，有一列不同，即符合联合唯一的约束

  4.2 primary key

一个表中可以：

单列做主键

多列做主键（复合主键）

约束：等价于 not null unique ，字段的值不为空且唯一

存储引擎默认是（innodb）：对于innodb存储引擎来说，一张表必须有一个主键

  单列主键

#创建t14表，为id字段设置主键，唯一的不同记录.
#not null + unique = primary key
create table t14(
    id int primary key,
    name char(16)
);

  4.3 foreign key

一个表中的foreign key 指向另一表中的primary key

#下面的sql 在创建表时添加foreign key约束：

CREATE TABLE Person(
    P_Id    nt primary key auto_increment,
    Name    char(20),
    Address    char(50),
    City    char(20)
)

CREATE TABLE Orders(
    O_id int primary key auto_increment,
    OrderNo    int not null,
    P_Id    int,
    FOREIGN KEY(P_Id) REFERENCES Person(P_Id)
)

5、单表查询

一、单表查询语法：
SELECT 字段1,字段2 FROM 表名
                WHERE 条件
                GROUP BY field
                HAVING    筛选
                ORDER BY field
                LIMIT 限制条数

二、关键字的执行优先级（重点）
from
where
group by
having
select
distinct
order by
limit

1 找到表：from
2 拿着where指定的条件，去表中取出记录
3 将取出的记录进行分组group by，如果没有，则整体为一组
4 将分组结果进行having过滤
5 执行select
6 去重
7 将结果按条件排序order by
8 限制结果显示条数

  5.1 where约束

where子句中可以使用
1 比较运算符：> < >= <= <> !=
2 between 80 and 100 ：值在80到100之间
3 in(10,20,30) 值是10或20或30
4 like '字符'
5 逻辑运算符：多个条件之间可以使用and or not

  5.2 group by分组查询

#需明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#分组是：将所有记录按照某个相同字段进行归类，比如员工表的职位分组，性别分组等

#大前提
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需借助于聚合函数

  5.3 聚合函数

max()    求最大值
min()    求最小值
avg()    求平均值
sum()     求和
count()   求总个数

#强调：聚合函数聚合的是组的内容，若没有分组，则默认一组
#每个部门有多少个员工
select post,count(id) from employee group by post;
# 每个部门的最高薪水
select post,max(salary)  from employee group by post;
# 每个部门的最低薪水
select post,min(salary) from employee group by post;
# 每个部门的平均薪水
select post,avg(salary) from employee group by post;
# 每个部门的所有薪水
select post,sum(age) from employee group by post;

  5.4 having过滤

having 与 where不一样地方在于

#！！！执行优先级从高到低：where > group by > having

#1. where发生在分组group by之前
#2. having发生在分组group by之后，因而having可以使用分组的字段，可以使用聚合函数

6、多表查询

准备两张表，部门表（department）、员工表（employee）

create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('nvshen','male',18,200),
('xiaomage','female',18,204)
;

多表连接查询

外链接语法：

SELECT 字段列表
        FROM  表1  
        INNER|LEFT|RIGHT  JOIN 表2  
        ON  表1.字段=表2.字段

(1)内连接：值连接匹配的行
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配
select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;


(2)外链接之左连接：优先显示左表全部记录
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有，右边没有的结果
select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;


(3)外链接之右连接：优先显示右表全部记录
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有，左边没有的结果
select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

子查询

#1 子查询是将一个查询语句嵌套在另一个查询语句中
#2 内层查询语句的查询结果，可以为外层查询语句提供查询条件
#3 子查询中可包含：IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字
#4 还可包含运算符：=、!=、>、<等