例子教程"/>
SQL安装,pymysql及详细例子教程
本文首先是在ubtunu系统上安装mysql,然后总结了一下基本的mysql命令,并通过小例子实践了一下,最后简单介绍了一下python的pymysql模块
安装:
首先安装mysql服务端
sudo apt-get install mysql-server
安装mysql客户端:
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient-dev
安装过程可能要求设置密码 ,自行设置即可。
接下来测试:
sudo netstat -tap | grep mysql
如果看到有mysql 的socket处于 listen 状态则表示安装成功。
登录:
mysql -u root -p
输入上面设置的密码即可登录
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
注意:在安装过程中可能没有要求设置密码,那么这时不知道密码该怎么办呢?
首先查看:
sudo vim /etc/mysql/debianf
就可以看到用户名和密码即字段:user和 password
然后依此登录:(查到用户名为debian-sys-maint)
mysql -u debian-sys-maint -p
然后输入查到的密码即可登录
进入到一个名叫mysql的数据库
use mysql;
select host,user,plugin,authentication_string from user;
可以看到user对应下就有root用户
接下来重置密码:
update user set plugin="mysql_native_password",authentication_string=password('密码') where user="root";
然后退出:
exit;
重启mysql服务:
sudo service mysql restart
再次以root登录即可登录:
mysql -u root -p
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
首先有一个数据库可视化软件Navicat,可以自行下载操作,但是工作中更多需要的是命令行,下面简单规划总结一下基本的mysql的命令行操作
导入导出数据库:
#导出数据库(数据+结构)
mysqldump -u 用户名 -p 数据库名称>导出文件路径 #不需要登录mysql
#导出数据库(结构)
mysqldump -u 用户名 -p -d 数据库名称>导出文件路径 #不需要登录mysql#导入数据库
mysqldump -u 用户名 -p 数据库名称<文件路径
source 路径 #需要登录mysql
用户管理:
mysql数据库下的user表
desc user可以看到有host和user列就是记录的用户信息
#用户管理:#创建用户
create user '用户名'@'ip' identified by '密码';#登录用户
mysql -u 用户名 -h ip -p;#修改用户
rename '用户名'@'ip' to '用户名'@'ip';#修改密码
set password for '用户名'@'ip' = Passward('密码');#退出登录
exit;#删除用户
drop user '用户名'@'ip';#授权
grant 某权限 on 某个数据库.某一张表 to '用户名'@'ip';
数据类型:
decimal #精确varchar #变长#时间:date #2018-8-27time #12:30:30datetime #2018-8-27 12:30:30timestamp #20180827 123030enum #枚举:单选set #集合:多选
条件关键字
where < = > !=
where and or between and
where in() not in()
where like 'tt%' like 'tt_' like '%tt_' limit 6 offset 3 #从第三个位置连续取6个数据order by ID desc ,name asc;select series as S count(id) as C from tb1 group by series having count(id)>2
数据库:
SHOW DATABASES;CREATE DATABASE 数据库名称;CREATE DATABASE DEFAULT CHARSET utf8 COLLATE utf8_general_ci; USE 数据库名称;drop database 数据库名称;
数据表:
create table tb1(ID int auto_increment not null primary key ,age int default 3) engine=innodb charset=utf8; show tables;selete * from tb1;desc tb1;alter table 表名 add 列名 类型;alter table 表名 drop column 列名;alter table 表名 modify column 列名 类型; #修改类型 alter table 表名 change 原列名 新列名 类型; #修改列名,类型drop table tb1; #删除表#主键:create table tb1(ID int not null auto_increment primary key ,name varchar(10)) engine=innodb charset=utf8;create table tb1(ID int not null ,name varchar(10),primary key (ID,name)) engine=innodb charset=utf8;#外键:alter table tb1 add contraint 键名 foreign key tb1(name_id) references tb2(name);create table tb1(ID int not null ,name_id int, contraint 键名 foreign key name_id references tb2(name) ) engine=innodb charset=utf8;
数据行:
#插入
insert into tb1(ID,name) values(1408,'tt'),(1409,'mm');
insert into tb1(ID_tb1,name_tb1) select ID_tb2,name_tb2 from tb2 where 条件;#修改
update tb1 set name=4 where 条件;#查
select ID from tb1 条件
select series as S count(id) as C from tb1 group by series having count(id)>2;select ID_tb1 from tb1 union select ID_tb2 from tb2; #上下组合 去重
select ID_tb1 from tb1 union all select ID_tb2 from tb2; #上下组合 不去重select * from tb1,tb2; #笛卡尔组合
select * from tb1,tb2 where tb1.ID_name=tb1.ID; #可视化外键select * from tb1 left join tb2 on tb1.ID_name=tb1.ID; #同上,注意tb1此时是主表(全显示,没有的列填充null))
select * from tb1 rignt join tb2 on tb1.ID_name=tb1.ID; #同上,注意tb2此时是主表(全显示,没有的列填充null)
select * from tb1 inner join tb2 on tb1.ID_name=tb1.ID; #同上,注意tb2此时是主表(全显示,没有的列不显示)#归结起来就是,想要什么select就选什么,然后和外键有关系的表有多少,后面就加多少left join就行
select tb1.id,tb1.name,tb2.name,tb3.gender from tb1
left join tb2 on tb1.id=tb2.id
left join tb3 on tb1.name=tb3.id#更复杂的可以:(...)可以是一次select的结果,其实就是储在内存的一张临时表,在此基础上作
#别的更复杂的查询
select t.xxx,t.oooo from (........) as t
left join tb2 on ......#删除
drop table tb1; #删除表
delete from tb1; #删除表内容但没有清回原点
truncate table tb1; #删除表内容但清回原点
下面举一个简单的例子
数据集准备:(在root用户下)
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
如果不想手动输入这个数据库,可以导入数据库,即先跳到(8)导入数据库部分,将文章结尾给出的out.sql导入,即可看到如下所描述的数据库
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
这是一个有关企业的数据库enterprise
里面维护着如下表:
具体看一下:
部门表(departments):
工资表:
部门主管:
进入公司年份表:
最后一个是员工表:
用户准备:
create user 'test'@'localhost' identified by 'test';
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
下面根据一些需求做一些简单的练习:
(1)把该数据库的所有操作权限都授权给test用户:
grant all on enterprise.* to 'test'@'localhost';
然后退出登录:
以test用户登录:
mysql -u test -p
查看数据库:
当然也可以看到具体的表:
(2)查一下每个员工在的具体部门是什么以及其主管。
第一步可以先现将部门和其主管查出:
select departments.ID,departments.department,managers.name as manager from departments left join managers on departments.manager=managers.ID;
接着再将该表和员工表链接即可:
select employees.name ,t.department,t.manager from employees left join (select departments.ID,departments.department,managers.name as manager from departments left join managers on departments.manager=managers.ID) as t on employees.department=t.ID;
(3)查一下高收入(7000及以上)员工
首先查一下高收入人:
select employees.name ,employees.department,wages.wage from employees left join wages on employees.wage=wages.ID where wages.wage>=7000;
再把部门显示出来就好了:
select t.name,t.wage ,departments.department from (select employees.name ,employees.department,wages.wage from employees left join wages on employees.wage=wages.ID where wages.wage>=7000) as t left join departments on t.department=departments.ID;
(4)查一下各部门员工数
通过group和聚合函数count可以查出:
select employees.department , count(name) from employees group by employees.department;
然后连接一下部门名即可:
select departments.department, t.number from (select employees.department , count(name) as number from employees group by employees.department) as t left join departments on t.department=departments.ID;
当然可以进一步进行其他需求例如:查看一下大部门(人数在3个及以上)主要就是看一下having
select departments.department, t.number from (select employees. , count(name) as number from employees group by employees.department having count(name)>=3) as t left join departments on t.department=departments.ID;
(5)查一下以A字母开头的部门,以及该部门的员工(like ,in)
首先查出符合条件的部门:
select departments.department from departments where department like 'A%';
为了说明in的用法,先找出每个员工的部门名
select employees.name,departments.department from employees left join departments on employees;
然后就可以利用in啦
select employees.name,departments.department from employees left join departments on employees.department=departments.ID where departments.department in (select departments.department from departments where department like 'A%');
当然也顺势可以参看没有在A字母开头的部门的员工
select employees.name,departments.department from employees left join departments on employees.department=departments.ID where departments.department not in (select departments.department from departments where department like 'A%');
(6)最后从倒数第六个位置开始,连续查三条记录
select * from employees order by ID desc limit 3 offset 5;
(7)导出数据库:
首先要退出sql命令行,在linux命令行下执行以下:
查看mysql位置:
可以看到Mysql的运行目录就在:/usr/bin/mysql
然后cd到/usr/bin/目录下ls可以看到有一个命令mysqldump:
然后导出数据库:
sudo mysqldump -u test -p enterprise >/usr/out.sql
############################################
如果遇到如下报错
bash: /usr/out.sql: Permission denied
就是说/usr/目录的权限不够可以先:
sudo chmod 777 /usr/
然后再导出即可,记得不要忘了回复/usr/目录原先的权限:
sudo chmod 755 /usr/
####################################################
导出后就会在usr/目录下多出一个名叫做out.sql,然后查看:
vim out.sql
可以看到它的数据结构和数据即先创建各张表,然后插入数据。
如果只想导出数据结构表,那可以这样:
sudo mysqldump -u test -p -d enterprise >/usr/out.sql
那么out.sql中就应该没有insert语句了。
(8)导入数据库:
首先以root用户进入mysql,然后创建一个空的数据库名为temp
mysql -u root -p
进入temp数据库:
use temp
导入数据库:
source /usr/out.sql
来查看一下结果:
(9)删除数据库:
drop database temp;
(10)删除用户:
drop user 'test'@'localhost';
可以看到没有test用户啦!!!!!!!!
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pymysql:
最后说一下python关于mysql的库即pymysql。
安装很简单
pip install pymysql
(1)先来看一下使用pymysql怎么查:
假如还是想实现上述(3)的需求
#!/usr/bin/python
import pymysqlCon=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')Cur=Con.cursor(cursor=pymysql.cursors.DictCursor)
Conmit()Cur.execute('select t.name,t.wage ,departments.department from (select employees.name ,employees.department,wages.wage from employees left join wages on employees.wage=wages.ID where wages.wage>=7000) as t left join departments on t.department=departments.ID')result=Cur.fetchall()
print(result)Cur.close()
Con.close()
可以看到只要将(3)的代码写到Cur.execute中即可,其他的都是一些比较固定的语句。
说明:
1 其中Cur.fecthall()就是取出所有结果
Cur.fecthone()就是取一条记录,但是这里有一个指针类似的概念,就是说这里有一个类似全局指针,在执行了Cur.execute中select后,其实符合要求的结果已经全部加载到内存,这不过上面是将结果全部取出,也可以选择取出一条Cur.fecthone(),指针下移,如果再次执行Cur.fecthone()那么会接着往后取。
当然也有可以控制指针位置的方法,那就是Cur.scroll(2,mode='relative'),代表当前位置下移2条记录,当然Cur.scroll(-2,mode='relative')代表当前位置下移2条记录,还有就是Cur.scroll(2,mode='absolute')就是回到整个记录的第三个位置。下面是具体例子:
#!/usr/bin/python
import pymysqlCon=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')Cur=Con.cursor()
Conmit()Cur.execute('select t.name,t.wage ,departments.department from (select employees.name ,employees.department,wages.wage from employees left join wages on employees.wage=wages.ID where wages.wage>=7000) as t left join departments on t.department=departments.ID')print(Cur.fetchone())
print('----------------------')
print(Cur.fetchone())
print('----------------------')
Cur.scroll(-1,mode='relative')
print(Cur.fetchone())
print('----------------------')
print(Cur.fetchall())
print('----------------------')
print(Cur.fetchone())
print('----------------------')
Cur.scroll(1,mode='absolute')
print(Cur.fetchall())Cur.close()
Con.close()
2 Con.cursor(cursor=pymysql.cursors.DictCursor)意思就是结果是字典,倘若是Con.cursor()即默认结果就是元祖
Cur=Con.cursor()
可以看到字段没有了即name,wage,department。所以如果sql语句要想利用字段,或者显示字段,重命名字段(as)那就还是使用字典把。
3 Conmit()就是提交的意思,当然对这里没什么影响,这里仅仅有select,但是对于后面的介绍的插入,删除,修改的操作,必须在最后提交后才能生效,对于查则没有什么用!!!!!!!!!!!
(2)看一下插入
#!/usr/bin/python
import pymysqlCon=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')Cur=Con.cursor()Cur.execute('select * from employees')
print(Cur.fetchall())
print('------------------------------------')l=(('狐妖',3,1,5),('女娲',3,6,4))
r=Cur.executemany('insert into employees(name,department,wage,year)values(%s,%s,%s,%s)',l)
Conmit()print(r)
Cur.execute('select * from employees')
print(Cur.fetchall())Cur.close()
Con.close()
说明
1 这里的返回值r代表操作了几条数据,因为这里是插入两条,所以是2。
2 这里使用了Cur.executemany,当然使用Cur.execute然后里面插入一条数据也是可以的
(3)看一下修改
这里将女娲的部门由3改为4
#!/usr/bin/python
import pymysqlCon=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')Cur=Con.cursor()Cur.execute('select * from employees')
print(Cur.fetchall())
print('------------------------------------')Cur.execute('update employees set department=%s where name=%s ',(4,'女娲'))
Conmit()Cur.execute('select * from employees')
print(Cur.fetchall())Cur.close()
Con.close()
~
(3)看一下删除
这里将ID 为14的记录删除了。
#!/usr/bin/python
import pymysqlCon=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')Cur=Con.cursor()Cur.execute('select * from employees')
print(Cur.fetchall())
print('------------------------------------')Cur.execute('delete from employees where ID=%s',(14,))
Conmit()Cur.execute('select * from employees')
print(Cur.fetchall())Cur.close()
Con.close()
~
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
到此pymysql简单的介绍完毕,可以看到通过python更容易实现动态化(%s),核心的东西还是不变,即Cur.execute中还是mysql,所以还是要先熟练掌握mysql语句的精髓,然后在其基础上才能应用python这一外壳即可。
最后把out.sql文件贴出来,方便直接导入数据库进行练习,省去手动创建和插入工作,当然也可以手动输入,进一步熟悉一些命令:
-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost Database: enterprise
-- ------------------------------------------------------
-- Server version 5.7.23-0ubuntu0.18.04.1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--
-- Table structure for table `departments`
--DROP TABLE IF EXISTS `departments`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `departments` (`ID` int(11) NOT NULL AUTO_INCREMENT,`department` varchar(20) DEFAULT NULL,`manager` int(11) DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `departments`
--LOCK TABLES `departments` WRITE;
/*!40000 ALTER TABLE `departments` DISABLE KEYS */;
INSERT INTO `departments` VALUES (1,'Affairs',5),(2,'Accounting',3),(3,'Sales',1),(4,'Advertising',4),(5,'Planning',2),(6,'Research',6);
/*!40000 ALTER TABLE `departments` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `employees`
--DROP TABLE IF EXISTS `employees`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `employees` (`ID` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`department` int(11) DEFAULT NULL,`wage` int(11) DEFAULT NULL,`year` int(11) DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `employees`
--LOCK TABLES `employees` WRITE;
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
INSERT INTO `employees` VALUES (1,'逍遥',2,3,1),(2,'月如',3,4,4),(3,'唐钰',1,6,6),(4,'酒剑仙',5,4,5),(5,'姜明',2,1,1),(6,'圣姑',6,2,3),(7,'拜月',4,5,2),(8,'石长老',5,5,4),(9,'巫王',3,6,5),(10,'巫后',1,3,6),(11,'剑圣',2,2,2),(12,'王小虎',6,4,3),(13,'阿七',4,1,4);
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `managers`
--DROP TABLE IF EXISTS `managers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `managers` (`ID` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `managers`
--LOCK TABLES `managers` WRITE;
/*!40000 ALTER TABLE `managers` DISABLE KEYS */;
INSERT INTO `managers` VALUES (1,'景天'),(2,'雪见'),(3,'龙葵'),(4,'长卿'),(5,'紫萱'),(6,'茂茂');
/*!40000 ALTER TABLE `managers` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `wages`
--DROP TABLE IF EXISTS `wages`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wages` (`ID` int(11) NOT NULL AUTO_INCREMENT,`wage` int(11) DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `wages`
--LOCK TABLES `wages` WRITE;
/*!40000 ALTER TABLE `wages` DISABLE KEYS */;
INSERT INTO `wages` VALUES (1,3000),(2,3500),(3,4000),(4,5000),(5,7000),(6,8000);
/*!40000 ALTER TABLE `wages` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `years`
--DROP TABLE IF EXISTS `years`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `years` (`ID` int(11) NOT NULL AUTO_INCREMENT,`year` date DEFAULT NULL,PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `years`
--LOCK TABLES `years` WRITE;
/*!40000 ALTER TABLE `years` DISABLE KEYS */;
INSERT INTO `years` VALUES (1,'2016-03-01'),(2,'2016-09-01'),(3,'2017-03-01'),(4,'2017-06-01'),(5,'2017-09-01'),(6,'2017-12-01');
/*!40000 ALTER TABLE `years` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2018-08-30 14:25:32
更多推荐
SQL安装,pymysql及详细例子教程
发布评论