my python day8

一、数据库

数据是存放数据的仓库 ，是长期存在才计算机内的有组织，可共享的数据集合。

数据库管理系统是操作和管理数据库的大型软件。

如MySQL、Oracle、SQLite、Access、MS SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于大中型企业，如联想、方正等。

 

 

数据库特点：1.数据库结构化

　　　　　　2.数据共享，冗余度低，易扩散

　　　　　　3.数据独立性高

　　　　　　4.数据有DBMS统一管理和管理

　　　　　　　　a.数据的安全性保护

　　　　　　　　b.数据的完整性检查

　　　　　　　　c.并发控制

　　　　　　　　d.数据库恢复

二、mysql

mysql是一个关系型数据库管理系统。

数据库管理软件分类：1.关系型数据库。 如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用

　　　　　　　　　　2.非关系型数据库：mongodb，redis，memcache

SQL语句主要用于存取数据、查询数据、更新数据和管理数据库系统，SQL主要分为3种类型数据

1.DDL语句，数据库定义语句，数据库、表、视图、索引、存储过程，如CREATE ALTER DROP

2.DML语句，数据库操作语句，插入数据INSERT 删除数据DROP 更新数据UPDATE 查询数据SELECT 

3.DCL语句，数据控制语句，例如控制用户的访问权限GRANT、REVOKE

登入mysql  mysql -h 127.0.0.1 -P 3306 -u root -p 密码

1⃣️、创建数据库

CREATE DATABASE [IF NOT EXIST] 数据库名. [CHARACTER SET UTF-8];

2⃣️、数据库相关操作

SHOW  DATABASES; #显示所有的数据库
SHOW CREATE DATABASE 数据库名。#显示创建的一个数据库
SELECT DATABASE()  #
USE 数据库名  #选择数据库
DROP DATABASE 数据库名 #删除数据库
ALTER DATABASE 数据库名 CHARACTER SET UTF-8

3⃣️、表 

表相当于文件，表中的一条记录就相当于文件的一条内容，不同的是，表中的记录有对应的标题，称为表的字段。

id、name、age、sex 是字段，一行一行内容为记录。

4⃣️创建表

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

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

CREATE TABLE tab_name (field1 type [完整约束性条件],field2 type）；

CREATE TABLE student(id INT PRIMARY key AUTO_INCREMENT,  
                name VARCHAR(20),
                 age INT ,
                 result INT );

5⃣️ 表的相关操作

SHOW TABLES;  #显示所有的表
SHOW CREATE TABLE TABLE 表名  #查看表详细结构
DESC 表名   #查看表结构
DROP 表名 #删除表

6⃣️数据类型

1.整数类型

TINYINT SMALLINT MEDIUMINT INT BIGINT

2.浮点型

定点数类型  DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE。  

#FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] M为最大个数，D为小数点后几位

 

3.日期类型

DATE TIME DATETIME TIMESTAMP YEAR 

 YEAR
            YYYY（1901/2155）

        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

4.字符串类型

#char类型：定长，简单粗暴，浪费空间，存取速度快  char(10)

#varchar类型：变长，精准，节省空间，存取速度慢. 

5.枚举类型和集合类型

enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

7⃣️非限制性约束条件

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录。主键的特征 非空且唯一。= not null + unique 
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;

或在创建表之后 alter table employee add constraint fk_name foreign key(dpt_id)
references department(id)

 

8⃣️修改表字段

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：
1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

9⃣️表记录的操作

1. 使用INSERT实现数据的插入
2. UPDATE实现数据的更新
3. 使用DELETE实现数据的删除
4. 使用SELECT查询数据以及。

 

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

#更新表记录
语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

删除表记录
语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

练习：
    更新MySQL root用户密码为mysql123
    删除除从本地登录的root用户以外的所有用户

表记录查询
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，如果没有group by，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：order by

8.限制结果的显示条数

2⃣️.对于where约束
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3.in(10,20,30)在10、20、30之中
4.like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
3⃣️.groupby 分组
分组发生在wherw之后，
才 mysql 8.0中会出现

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 's444.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 出现该问题的解决方案：1.检查mysql的版本。select @@sql_mode;

　　　　　　　　　　　查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

　　　　　　　　　　2.去掉ONLY_FULL_GROUP_BY，重新设置值。

set @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

,NO_ENGINE_SUBSTITUTION';

　　　　　　　　　　3.上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

聚合函数 

SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

4⃣️ having 筛选

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

　#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

  #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

5⃣️ order by 排序   asc 升序。desc 降序

　　 SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

6⃣️ limit。限制显示几条

　　select * from student limit 1,2  ;   跳过1，再显示两条

正则表达式。

SELECT * FROM employee WHERE name REGEXP '^ale';

🔟两个表之间的关系

create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;


on delete cascade 级联删除。 
on delete set null 删除是设置为空
select * from table_a,table_b where table_a.id = table_b.idd; #笛卡尔积
select * from table_a inner join(table_b) on table_a.id = table_b.idd;  # 内连接
select name from table_a left join(table_b) on table_a.id = table_b.idd;  #左连接 ，以左边字段为主
select name from table-a right join(table_b) on table_a.id = table_b.idd; #右连接，以右边字段为主

 

三、使用pymysql操作数据库

pip install pymysql 

import pymysql
conn = pymysql.connect(host="localhost",port=3306,user="root",password="Zqq@0529",database="s444")
#建立链接
cursor = conn.cursor()
cursor = conn.cursor(pymysql.cursors.DictCursor)
#建立游标
sql = "CREATE TABLE t(id int primary key,name varchar(20),age int );"
sql2 = "insert into t values (4,'f',12),(5,'g',5)"
cursor.execute(sql)  #执行sql 语句
sql3 = "select * from t"
x = cursor.execute(sql3)    #获取总共的记录
print(x)
data = cursor.fetchone()  #获取一条内容
data2= cursor.fetchmany(2)  #获得多条记录
data3 = cursor.fetchall()  #获取所有内容
# print(data2)
# print(data)
# print(data3)
cursor.scroll(1,mode="relative")  #相对当前位置向前移动一位
cursor.scroll(2,mode="relative")
cursor.scroll(-1,mode="relative")  #相对当前向后移动一位
cursor.scroll(1,mode="absolute") #绝对位置移动到1位置
data = cursor.fetchall()
print(data)
conn.commit() #提交
cursor.close() #游标关闭
conn.close()#链接关闭

四、事务 

逻辑上的一些操作，组成这组操作的各个单元。

start transaction 开启事务

rollback 回滚事务。 回滚到开启事务的地方

commit 提交事务

savepoint 保留点 事务处理中设置临时占位符. savepoint + 名字

start transaction;   #开启事务

update student set reslut  = result -20;

savepoint u1;  设置保留点

delete from student where id = 3;

savepoint d1 ;

rollback u1;  回滚
 
commit;  提交

函数

delimiter //
create function f5(
    i int
)
returns int
begin
    declare res int default 0;
    if i = 10 then
        set res=100;
    elseif i = 20 then
        set res=200;
    elseif i = 30 then
        set res=300;
    else
        set res=400;
    end if;
    return res;
end //
delimiter ;

# 获取返回值
select UPPER('egon') into @res;
SELECT @res;


# 在查询中使用
select f1(11,nid) ,name from tb2;