Python昇格------pymysqlモジュールの使用、単表クエリー
19119 ワード
Python昇格------pymysqlモジュールの使用、単表クエリー
一丶pymysqlを使う
1.pymysqlパッケージのダウンロード:pip 3 install pymysql
2.コードの作成
二丶単表クエリ
単一クエリー構文:
キーワード実行の優先順位
テーブルの作成:
単純クエリーたんじゅんくえり:select select
Whereクエリー:
whereケース
GROUP BYグループクエリー:
特徴:
重複率の高いフィールドに基づいて
グループ化されると、特定のデータを操作できません.
group_concat():最も重い表示にのみ使用され、中間の結果として他のデータを操作できません.
グループ化は重量除去の効果があります
HAVINGグループフィルタ:
特徴:
1つのグループの条件付きフィルタ
優先度:
1. 優先度where>groupby>having
2.whereはグループグループグループbyの前に発生し、whereは任意のフィールドを持つことができ、whereは絶対に集約関数と一緒に使用しない.
3.havingはグループグループグループbyの後に発生するので、havingはグループのフィールドを使用することができ、他のフィールドに直接取ることはできません.
集約関数:
count():フィールドが表示された回数を統計します.
max():フィールドの最大値
min():フィールドの最小値
AVg():フィールドの平均値
sum():フィールドの合計
ORDER BYソートクエリー:
特徴:
1.単一フィールド、複数フィールドのソート
2.デフォルトの昇順は小さいから大きい
3.desc降順は大きいから小さいまで、asc昇順は小さいから大きいまで
LIMIT制限クエリー:
特徴:
1.limit(n,m):nデフォルトは0から、n+1から、mバーを取ります
2.limit n offset m:用法ずっと
3.limit(n):n本を取る
4.order byと組み合わせて使う
適用:
1.ページング
2.制限値
正規クエリー:
特徴:
mysqlは正規を使用してクエリーできます
転載先:https://www.cnblogs.com/dengl/p/11290953.html
一丶pymysqlを使う
1.pymysqlパッケージのダウンロード:pip 3 install pymysql
2.コードの作成
######
import pymysql # pymysql
# 1. conn
conn=pymysql.Connect(host='127.0.0.1', user='root', password="123",
database='day40', port=3306,)
# 2. ,
cur=conn.cursor()
with open('test','r',encoding='utf-8') as f:
for line in f:
# python | alex | | 50 | 2018 - 7 - 1
line=line.strip().split('|')
# 3 sql %s sql python
sql='insert into book(b_name,auth, press,price,p_date) values(%s,%s,%s,%s,%s)'
# 4. sql , execute , sql
cur.execute(sql,line)
# 5. , ,
conn.commit()
# 6 ,
cur.close()
conn.close()####
import pymysql # pymysql
# 1. conn
conn=pymysql.Connect(host='127.0.0.1', user='root', password="123",
database='day40', port=3306,)
# 2. ,
cur=conn.cursor()
# 3. sql
sql = 'select * from book'
# 4. sql
cur.execute(sql)
# 5.
res1 = cur.fetchone() #
res2 = cur.fetchmany(2) #
res3 = cur.fetchall() #
print(res1) #
print(res2)
print(res3)
# 6 ,
cur.close()
conn.close()
二丶単表クエリ
単一クエリー構文:
SELECT DISTINCT 1, 2... FROM
WHERE
GROUP BY field
HAVING
ORDER BY field
LIMIT キーワード実行の優先順位
#3## ?
from : :from
where : where ， /
group by : group by， group by，
select : select
distinct :
having : having
order by : ：order by desc acs
limit : テーブルの作成:
#
company.employee
id id int
emp_name varchar
sex enum
age int
hire_date date
post varchar
post_comment varchar
salary double
office int
depart_id int
#
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #
depart_id int
);
#
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
#
# ： ， ，
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301',' ',7300.33,401,1), #
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
(' ','male',48,'20101111','teacher',10000,401,1),
(' ','female',48,'20150311','sale',3000.13,402,2),#
(' ','female',38,'20101101','sale',2000.35,402,2),
(' ','female',18,'20110312','sale',1000.37,402,2),
(' ','female',18,'20160513','sale',3000.29,402,2),
(' ','female',28,'20170127','sale',4000.33,402,2),
(' ','male',28,'20160311','operation',10000.13,403,3), #
(' ','male',18,'19970312','operation',20000,403,3),
(' ','female',18,'20130311','operation',19000,403,3),
(' ','male',18,'20150411','operation',18000,403,3),
(' ','female',18,'20140512','operation',17000,403,3)
;
#ps： windows ， ，select ， gbk単純クエリーたんじゅんくえり:select select
###
select user(); #
select database(); #
select now(); #
###
#
SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
#
SELECT * FROM employee;
#
SELECT emp_name,salary FROM employee;
### distinct
#
select distinct post from employee;
###
#
select emp_name, salary*12 from emplpoyee;
###
# as
select emp_name, salary*12 AS Annual_salary from employee;
#
select emp_name, salary*12 Annual_salary from employee;
###
# CONCAT() :
select concat(' :',emp_name,' :',salary*12) from employee;
# CONCAT_WS() : ,
select concat_ws(':' , emp_name, salary*12) from employee;
# CASE :
# : ( case when 1 then when 2 then else end )
select
(
case
when emp_name='jingliyang' then
emp_name
when emp_name='alex' then
concat(emp_name,'_BIGSB')
else
concat(emp_name,'SB')
end
) as new_name
from
employee;
#### :
#1 ， ,
# select concat(''),concat('') from employee;
#2 （ ）
# select distinct post from employee;
#3 ， , annual_year
# select emp_name ,salary*12 from employee;Whereクエリー:
###
# : > , < , >= , <= , != , <>
# between 80 and 100 : 80 100 , 80 100
# in(80,90,100) : 80 90 100
#
# like : % , _
# 1. like 'a%' a .
# 2. like '%a' a
# 3. like '_a' Xa
# 4. like 'a_' aX
# regexp :
# 1. '^a' a
# 2. '\d+'
# 3. 'a$' a
# is is not
is null :
is not null :
# : and or not
whereケース
# 1.
select emp_name from employee where post='sale';
# 2.
select emp_name , salary from employee where port='teacher' and salary>10000;
# 3. between and
select emp_name ,salary from employee where salary beetween 10000 and 20000;
# 4. is null ,
select emp_name,post_comment from employee where post_comment is null;
select emp_name,post_comment from employee where post_comment is not null;
#
select emp_name,post_comment from employee where post_comment='' ;
# 5. IN
SELECT emp_name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
SELECT emp_name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT emp_name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
# 6. LIKE
'%'
# eg
select * from employee
where emp_name like 'eg%';
'_'
# select * from employee
where emp_name like 'al__';
# 7 . regexp
# jin
select emp_name ,salary*12 from employee where emp_name regexp '^jin'
###
1. teacher 、
# select emp_name , age from employee where post ='teacher';
2. teacher 30 、
# select emp_name , age from employee where post ='teacher' and age>30;
3. teacher 9000-10000 、 、
# select emp_name , age ,salary*12 from employee where post ='teacher' and salary between 9000 and 10000;
4. NULL
# select * from employee where post_comment is not null;
5. teacher 10000 9000 30000 、 、
# select emp_name , age ,salary from employee where post ='teacher' and salary in (9000,10000,30000);
# select emp_name , age ,salary from employee where post ='teacher' and salary=9000 or salary=10000 or salary=30000;
6. teacher 10000 9000 30000 、 、
# select emp_name , age ,salary from employee where post ='teacher' and salary not in (9000,10000,30000);
# select emp_name , age ,salary from employee where post ='teacher' and not (salary=9000 or salary=10000 or salary=30000);
7. teacher jin 、
# select emp_name ,salary*12 from employee where post='teacher' and emp_name like 'jin%';
# select emp_name ,salary*12 from employee where post='teacher' and emp_name regexp '^jin';GROUP BYグループクエリー:
特徴:
重複率の高いフィールドに基づいて
グループ化されると、特定のデータを操作できません.
group_concat():最も重い表示にのみ使用され、中間の結果として他のデータを操作できません.
グループ化は重量除去の効果があります
### group by .
# , ( )
select post from employee group by post;
### group by group_concat()
# group_concat()
# ,
select post,group_concat(emp_name) from employee group by post;
### group by
# ,
select post,count(id) as count for employee group by post;
#####
1. unique , ,
2. ,
HAVINGグループフィルタ:
特徴:
1つのグループの条件付きフィルタ
優先度:
1. 優先度where>groupby>having
2.whereはグループグループグループbyの前に発生し、whereは任意のフィールドを持つことができ、whereは絶対に集約関数と一緒に使用しない.
3.havingはグループグループグループbyの後に発生するので、havingはグループのフィールドを使用することができ、他のフィールドに直接取ることはできません.
## 1.
mysql> select post from employee where count(salary) group by post;
ERROR 1111 (HY000): Invalid use of group function
## 2.
# ， salary
mysql> select post,group_concat(emp_name) from employee group by post having salary>1000;
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
#
mysql> select post,group_concat(emp_name) from employee group by post having avg(salary)>10000;
+-----------+---------------------------------------------------------+
| post | group_concat(emp_name) |
+-----------+---------------------------------------------------------+
| operation | , , , , |
| teacher | ,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+---------------------------------------------------------+
###
1. 2 、 、
# select post, count(id),group_concat(emp_name) from employee group by post having count(id)<2;
3. 10000 、
# select post,avg(salary) from employee group by post having avg(salary)>10000;
4. 10000 20000 、
# select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000;
# select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
集約関数:
count():フィールドが表示された回数を統計します.
max():フィールドの最大値
min():フィールドの最小値
AVg():フィールドの平均値
sum():フィールドの合計
### ,
###
#
select count(*) from employee;
# id .( )
select count(id) from employee;
#
select max(salary) from employee;
#
select min(salary) from employee;
#
select avg(salary) from employee;
#
select sum(salary) from employee;
###
1.
# select post, group_concat(emp_name) from employee group by post;
2.
# select post, count(emp_name) from employee group by post;
3.
# select count(emp_name),sex from employee group by sex;
4.
# select post, avg(salary) from employee group by post ;
5.
# select post, max(salary) from employee group by post ;
6.
# select post, min(salary) from employee group by post ;
7. ，
# select sex, max(salary) from employee group by sex ;
# 1w
select post,count(id) from employee where salary >10000 group by post;
ORDER BYソートクエリー:
特徴:
1.単一フィールド、複数フィールドのソート
2.デフォルトの昇順は小さいから大きい
3.desc降順は大きいから小さいまで、asc昇順は小さいから大きいまで
###
#
select * from employee order by salary;
# asc
select * from employee order by salary asc;
# desc
select * from employee order by salary desc;
### :
# age ,
select * from employee order by age , salary ;
# age ,
select * from employee order by age desc , salary ;
###
1. ， age ， age hire_date
# select * from employee order by age , hire_date desc;
2. 10000 、 ,
# select post , avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary);
3. 10000 、 ,
# select post , avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc;
LIMIT制限クエリー:
特徴:
1.limit(n,m):nデフォルトは0から、n+1から、mバーを取ります
2.limit n offset m:用法ずっと
3.limit(n):n本を取る
4.order byと組み合わせて使う
適用:
1.ページング
2.制限値
##
# , 3
select * from employee order by salary desc limit(3);
# 1 , , 5
select * from employee order by salary desc limit 0,5;
# 6 , 6 , 5
select * from employee order by salary desc limit 5,5;
### ， 5
mysql> select * from employee limit 0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
mysql> select * from employee limit 5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
正規クエリー:
特徴:
mysqlは正規を使用してクエリーできます
##
# emp_name al
select * from employee where emp_name regexp '^al';
# emp_name on
select * from employee where emp_name regexp 'on$';
# emp_name al
select * from employee where emp_name regexp '^al';
転載先:https://www.cnblogs.com/dengl/p/11290953.html