饮冰三年-人工智能-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
这里需要将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;
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函数类似于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);
索引
索引创建的三种方法
1：创建表时，建索引
create table 表名 (
字段名 数据类型 (完整性约束条件)，
[unique|fulltext|spatial] index|key
[索引名] (字段名[长度] [ASC|DESC])
)
例子：
create table emp( id int , name varchar(20), 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()
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文件：
