饮冰三年-人工智能-Python-21 Python数据库MySql

一：下载、安装与卸载

1：下载地址：https://dev.mysql.com/downloads/mysql/

2：安装MySql

打开下载文件解压到指定文件目录。（我这里解压目录为D:\MySql\mysql-8.0.13-winx64）

打开解压后的MySql文件在根目录下创建my.ini (mysql配置文件)

my.ini文件内容如下：
（建议直接复制粘贴下面文件）

[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

#设置3306端口

port = 3306

# 设置mysql的安装目录

basedir=D:\MySql\mysql-8.0.13-winx64

# 设置mysql数据库的数据的存放目录

datadir=D:\MySql\mysql-8.0.13-winx64\data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

my.ini

这里需要将basedir 与 datadir 路径改成mysql的解压路径

安装:依次输入:mysqld --install (安装) mysqld --initialize （初始化）net start mysql（运行）

修改密码

先获取原始密码：

登录之后修改密码

alter user 'root'@'localhost'IDENTIFIED BY '12qwaszx!@QWASZX';

注意：需要重启mysql服务，密码要复杂一点，否则验证可能通不过

忘记密码，或者无法登录怎么办？

1：停掉当前服务器 net stop mysql

2：mysqld --console --skip-grant-tables --shared-memory 启动服务器。

3：再开启一个cmd。进入到bin目录下面。D:\MySql\mysql-8.0.13-winx64\bin

　　3.1 无密码登录mysql -uroot -p

　　3.2 选中数据库use mysql;

　　3.3 修改密码  alter user 'root'@'localhost'IDENTIFIED BY '12qwaszx!@QWASZX';

误删了root账户怎么办？

按照忘记密码的操作登录系统，选中数据库use mysql;

use mysql;
//添加用户
insert into user(User,authentication_string,ssl_cipher,x509_issuer,x509_subject) values('root','','','','');
//添加权限
update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';commit;

创建root账户

3：完全卸载

　　使用以下命令查看当前安装mysql情况，查找以前是否装有mysql

　　rpm -qa|grep -i mysql

　　停止mysql服务、删除之前安装的mysql

　　删除命令：rpm -e –nodeps 包名

　　注意删除顺序，如果提示依赖包错误，还可以  rpm -ev 包名 --nodeps

查找之前老版本mysql的目录、并且删除老版本mysql的文件和库

find / -name mysql

注意：卸载后/etc/my.cnf不会删除，需要进行手工删除

rm -rf /etc/my.cnf

rpm  -qa|grep -i mysql

二 DDL语言

1:展示所有的数据库
show databases;
1.1 展示某个数据库创建时的信息（编码）
show create database db_name;

1.2创建数据库
create database [if not exists] db_name [character set xxx]

1.3切换数据库

use 数据库名
1.4修改数据库
alter database db_name [character set xxx]

1.5删除数据库

drop database [if exists] db_name

2：展示该数据库下的数据表

show tables

2.1 创建一张表
create table tab_name(
field1 type[完整性约束条件],
field2 type,
...
fieldn type
)[character set xxx];
例：
create table Student(
id int primary key auto_increment,
name varchar(20) not null,
age int ,
birth_date date,
remark text
)

2.2：展示某个表下的列
show columns from table_name
desc table_name

2.3 展示某个表的创建语句
show create table table_name

2.4 修改表结构（增加列）
alter table table_name add column 列名 类型 [约束] [first|after 字段名]
例：添加一个班级名称（字符串，长度为10，不能为空，唯一，放到remark上面）
alter table student add column className varchar(10) not null unique after birth_date;

2.4 修改表结构（增加多个列）
alter table student add A varchar(10),add B varchar(20)

2.5 修改表结构（修改列属性）
alter table table_name modify 列名 类型 [约束] [first|after 字段名]
把备注字段设置成长度为5的decimal类型（不能为空）
alter table student modify remark decimal(5,2) not null;

2.6 修改列名

alter table table_name change colum 列名 新列名 类型 [约束] [first|after 字段名]
把B修改成成绩（decimal，不能为空，放到备注上面）
alter table student change B score decimal(5,2) not null after className

2.7 删除列
alter table student drop [colum] A;
2.8 修改表名称
rename table student to stu;

2.9 修改表的字符集
alter table stu character set gbk;

2.10 删除表
drop table table_name

3 展示某个数据表中的所有信息
select * from table_name;
3.1 新增
insert into tab_name (field1,filed2,.......) values (value1,value2,.......);
例：中规中矩插入
insert into student (id,name,age,birth_date) values
(1,'yang1',12,'1990-09-09');
例：省略列名，全部字段插入
insert into student values
(2,'yang2',12,'1990-09-09','');
例：插入部分字段
insert into student (name) values
('yang3');
例：通过set插入
insert into student set name="yang4";
例：插入多条
insert into student (name) values
('yang5'),
('yang6');

3.2 修改数据

update tab_name set field1=value1,field2=value2,......[where 语句]
例：
update student set birth_date="1994-10-24" WHERE id=1;
例：支持简单四则运算
update student set age=age+1 WHERE id=1;
3.3 删除表纪录
delete from tab_name [where ....]
例：删除表中id为3的记录。
delete from student where name='yang3';
-- 删除表中所有记录。
delete from student;

-- 使用truncate删除表中记录(删除，在创建)。
truncate table emp_new;

4：查询
--单表查询

   CREATE TABLE ExamResult(

   id INT PRIMARY KEY  auto_increment,
   name VARCHAR (20),
   JS DOUBLE ,
   Django DOUBLE ,
   OpenStack DOUBLE
);

INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),
                               (2,"xialv",35,98,67),
                               (3,"alex",59,59,62),
                               (4,"wusir",88,89,82),
                               (5,"alvin",88,98,67),
                               (6,"yuan",86,100,55);

---准备表

4.1 查询表达式
   SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

                     -- 查询表中所有学生的信息。
                    select * from ExamResult;
                    -- 查询表中所有学生的姓名和对应的英语成绩。
                    select name,JS from ExamResult;
                    -- 过滤表中重复数据。
                    select distinct JS ,name from ExamResult;                    

查询全部或部分字段和去重复

                -- 在所有学生分数上加10分特长分显示。
                select name,JS+10,Django+10,OpenStack+10 from ExamResult;
                -- 统计每个学生的总分。
                select name,JS+Django+OpenStack from ExamResult;
                -- 使用别名表示学生总分。
                select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;
                select name,JS+Django+OpenStack 总成绩 from ExamResult;

支持四则运算和别名

            -- 查询姓名为XXX的学生成绩
            select * from ExamResult where name='yuan';
            -- 查询英语成绩大于90分的同学
            select id,name,JS from ExamResult where JS>90;
            -- 查询总分大于200分的所有同学
            select name,JS+Django+OpenStack as 总成绩 from
                        ExamResult where JS+Django+OpenStack>200 ;

过滤查询

              select * from ExamResult order by JS;
              -- 对总分排序按从高到低的顺序输出
              select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
                   总成绩 from ExamResult order by 总成绩 desc;
              -- 对姓李的学生成绩排序输出
              select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))
                   总成绩 from ExamResult where name like 'a%'
                                         order by 总成绩 desc;

排序

对成绩表表按姓名分组后显示每个人js成绩总和超过150的同学
    select name,SUM(JS)from ExamResult group by name
                                    HAVING SUM(JS)>150;

分组

    聚合函数：  --   (一般和分组查询配合使用)

        --<1> 统计表中所有记录

            -- COUNT(列名)：统计行的个数
                    -- 统计一个班级共有多少学生？先查出所有的学生，再用count包上
                     select count(*) from ExamResult;
                    -- 统计JS成绩大于70的学生有多少个？
                     select count(JS) from ExamResult where JS>70;
                    -- 统计总分大于280的人数有多少？
                     select count(name) from ExamResult
                           where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
                    -- 注意:count(*)统计所有行;     count(字段)不统计null值.

            -- SUM(列名)：统计满足条件的行的内容和
                    -- 统计一个班级JS总成绩？先查出所有的JS成绩，再用sum包上
                        select JS as JS总成绩 from ExamResult;
                        select sum(JS) as JS总成绩 from ExamResult;
                    -- 统计一个班级各科分别的总成绩
                        select sum(JS) as JS总成绩,
                               sum(Django) as Django总成绩,
                               sum(OpenStack) as OpenStack from ExamResult;

                    -- 统计一个班级各科的成绩总和
                        select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
                                                    as 总成绩 from ExamResult;
                    -- 统计一个班级JS成绩平均分
                        select sum(JS)/count(*) from ExamResult ;
                    -- 注意：sum仅对数值起作用，否则会报错。

            -- AVG(列名)：
                            -- 求一个班级JS平均分？先查出所有的JS分，然后用avg包上。
                                select avg(ifnull(JS,0)) from ExamResult;
                            -- 求一个班级总分平均分
                                select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
                                                         from ExamResult ;
            -- Max、Min
                            -- 求班级最高分和最低分（数值范围在统计中特别有用）
                              select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
                              最高分 from ExamResult;
                              select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
                              最低分 from ExamResult;
                            -- 求购物表中单价最高的商品名称及价格
                              ---SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?

                              SELECT MAX(price) FROM order_menu; 

                            -- 注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！
                            --      -----ifnull(JS,0)

聚合函数

  limit
                SELECT * from ExamResult limit 1;
                SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录

limit

注意limit函数类似于sqlserver中的Top关键字，但是limit可用于分页，跳过多少条取多少条

        使用正则表达式查询
        SELECT * FROM employee WHERE emp_name REGEXP '^yu';

        SELECT * FROM employee WHERE emp_name REGEXP 'yun$';

        SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

正则表达式

--外键约束

---  每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任

----主表

CREATE TABLE ClassCharger(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean  -- show create table ClassCharger: tinyint(1)

);

INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
                                                       ("丹丹",14,0),
                                                       ("歪歪",22,0),
                                                       ("姗姗",20,0),
                                                       ("小雨",21,0);
CREATE TABLE Student(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     --切记:作为外键一定要和关联主键的数据类型保持一致
       -- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)

) ENGINE=INNODB;

INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
                                            ("alvin2",4),
                                            ("alvin3",1),
                                            ("alvin4",3),
                                            ("alvin5",1),
                                            ("alvin6",3),
                                            ("alvin7",2);

表结构

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);

ALTER TABLE student DROP FOREIGN KEY abc;

增加外键 删除外键

--外键约束对子表的含义:   如果在父表中找不到候选键,则不允许在子表上进行insert/update

--外键约束对父表的含义:    在父表上进行update/delete以更新或删除在子表中有一条或多条对
                    -- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的
                    -- on update/on delete子句

-----------------innodb支持的四种方式---------------------------------------

-----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE CASCADE

------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
   -- 要注意子表的外键列不能为not null

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE SET NULL

------Restrict方式 :拒绝对父表进行删除更新操作(了解)

------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
   -- 进行update/delete操作（了解）

级联更新

示例：修改外键规则，完成级联删除

删除失败原因：删除主表UserInfo中数据时，子表中UserGateTable中存在主表中的数据。

修改外键结构，使其可以级联删除：

select * from UserInfo where username="张三";
delete from UserInfo where username="张三"
##删除外键
alter table `UserGateTable` drop foreign key `UserGateTable_gate_id_cb624bc5_fk_GatewayDevice_id`
ALTER TABLE `UserGateTable` DROP FOREIGN KEY `UserGateTable_user_id_570b8751_fk_UserInfo_id`
##新增外键
ALTER TABLE  `UserGateTable`
ADD CONSTRAINT   `UserGateTable_user_id_570b8751_fk_UserInfo_id` FOREIGN KEY `UserGateTable`(`user_id`) REFERENCES `UserInfo`(`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
#再次删除
delete from UserInfo where username="张三"
select * from UserInfo where username="张三";

修改外键，使其级联删除

--多表查询

-- 准备两张表
-- company.employee
-- company.department

      create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

      insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);

    create table department(
       dept_id int,
       dept_name varchar(100)
      );

    insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财政部');

mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+
rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | 人事部    |
|     201 | 技术部    |
|     202 | 销售部    |
|     203 | 财政部    |
+---------+-----------+
rows in set (0.01 sec)

准备两张表

内连接

select * from employee,department where employee.dept_id = department.dept_id;

select * from employee inner join department on employee.dept_id = department.dept_id;

外连接

select * from employee left join department on employee.dept_id = department.dept_id;

select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

mysql不支持全外连接

子查询

select * from employee where dept_id IN (select dept_id from department);

-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

select * from employee  WHERE EXISTS   (SELECT dept_name from department where dept_id=203);

带EXISTS关键字的子查询

索引

索引创建的三种方法

1：创建表时，建索引
create table 表名 (
字段名 数据类型 (完整性约束条件)，
[unique|fulltext|spatial] index|key
[索引名] (字段名[长度] [ASC|DESC])
)
例子：

create table emp(
id int ,
name varchar(),
index index_name (name)
)

创建数据库时，创建索引

2: create方式

create [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
on 表名 (字段名[(长度)] [ASC|DESC])

3:ALTER
ALTER TABLE 表名 add [unique|fulltext|spatial] index 索引名 (字段名[(长度)] [ASC|DESC])

索引测试

create table t1 (id int ,name varchar(20));
通过存储过程创建基础数据（50万条）
delimiter $$
create procedure autoinsert()
begin
declare i int default 1;
while(i<500000) do
insert into t1 values(i,'yang');
set i=i+1;
end while;
End $$
delimiter ;
调用储存过程
call autoinsert();

数据准备

1：未添加索引前

添加索引后

删除索引
Drop index 索引名 on 表名

事务

--        start transaction 开启事务
--        Rollback 回滚事务,即撤销指定的sql语句(只能回退insert delete update语句)，回滚到上一次commit的位置
--        Commit 提交事务，提交未存储的事务
--
--        savepoint 保留点 ，事务处理中设置的临时占位符 你可以对它发布回退(与整个事务回退不同)  
1:建表
create table test2(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb;
2：创建事务与回滚

3：保留点与回滚

SQL语句在python中的使用

# 导包
import pymysql
# 创建连接对象
conn = pymysql.connect(host='127.0.0.1',port=3306,user="root",passwd="12qwaszx!@QWASZX",db='yk2012')
# 创建游标对象
cursor = conn.cursor()
# 创建SQL语句
sql = "create table test (id int,name VARCHAR (20))"
# 通过游标执行SQL语句
cursor.execute(sql)
# 插入语句,并返回受影响行数
ret=cursor.execute("insert into test (id,name)VALUES(1,'张三')")

# 导包
import pymysql
# 创建连接对象
conn = pymysql.connect(host='127.0.0.1',port=3306,user="root",passwd="12qwaszx!@QWASZX",db='yk2012')
# 创建游标对象
cursor = conn.cursor()
# 创建SQL语句
sql = "create table test (id int,name VARCHAR (20))"
# 通过游标执行SQL语句
cursor.execute(sql)
# 插入语句,并返回受影响行数
ret=cursor.execute("insert into test (id,name)VALUES(1,'张三')")
cursor.execute("select * from test")
print(cursor.fetchall())
conn.commit()
cursor.close()
conn.close()

ViewCode

import pymysql

#添加数据

conn = pymysql.connect(host='127.0.0.1',port=3306,user="root",passwd="12qwaszx!@QWASZX",db='yk2012')
cursor = conn.cursor()

try:
    createSQL="create table test3(id int PRIMARY KEY auto_increment,name VARCHAR(20)) engine=innodb"
    insertSQL0="insert into test3 (id,name)VALUES(2,'张三')"
    insertSQL1="insert into test3 (id,name)VALUES(22,'张三2')"
    insertSQL2="insert into test3 (id,name)VALUES(33,'张三3')"

    cursor = conn.cursor()
    cursor.execute(createSQL)
    print(cursor.execute(insertSQL0))
    conn.commit()

    cursor.execute(insertSQL1)
    raise Exception
    cursor.execute(insertSQL2)
    cursor.close()
    conn.commit()

except Exception as e:
    conn.rollback()
    conn.commit()

cursor.close()
conn.close()

事务

三、使用过程中常见的问题

问题一：数据库连接不上

　　现象：mysql数据库连接不上，服务重启失败提示PID文件不存在，root用户登录： Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

　　处理方法：

　　1，通过whereis my.cnf 数据库配置文件位置，通过编辑mysql.sock套接字文件位置：

注意：通过命令 mkdir -p /tmp/mysql，然后执行 chmod 777 /tmp/mysql 。

　　2. centos 8 通过 systemctl start mysqld，启动mysql服务端生成mysql.sock文件：