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/debian.cnf

就可以看到用户名和密码即字段：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-27

     time        #12:30:30

     datetime    #2018-8-27 12:30:30

     timestamp   #20180827 123030


     enum        #枚举：单选

     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 pymysql

Con=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')

Cur=Con.cursor(cursor=pymysql.cursors.DictCursor)
Con.commit()

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 pymysql

Con=pymysql.connect(host='localhost',port=3306,user='root',passwd='123',db='enterprise',charset='utf8')

Cur=Con.cursor()
Con.commit()

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 Con.commit()就是提交的意思，当然对这里没什么影响，这里仅仅有select，但是对于后面的介绍的插入，删除，修改的操作，必须在最后提交后才能生效，对于查则没有什么用！！！！！！！！！！！

(2)看一下插入

#!/usr/bin/python
import pymysql

Con=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)
Con.commit()

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 pymysql

Con=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,'女娲'))
Con.commit()

Cur.execute('select * from employees')
print(Cur.fetchall())


Cur.close()
Con.close()
~                  

（3）看一下删除

这里将ID 为14的记录删除了。

#!/usr/bin/python
import pymysql

Con=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,))
Con.commit()

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