Database Operation of python Learning

Database basic operation: view storage engine: show engines; view database: show databases; or show create database oldboy\ G create database: create database oldboy default charset=utf8; delete database: drop database oldboy; enter database: use oldboy Datasheet operation: create datasheet: create table tb_emp1 (- > id int (11),-> name varchar (25),-> deptId int (11),-> salary float->) engine=innodb default charset=utf8 Primary key:create table tb_emp2 (- > id int (11) primary key,-> name varchar (25),-> deptId int (11),-> salary float->) engine=innodb default charset=utf8 Multiple primary key:create table tb_emp3 (id int (11), name varchar (25), deptId int (11), salary float, primary key (name, deptId)) engine=innodb default charset=utf8; foreign key constraints foreign key: main table: create table tb_dept1 (- > id int (11) primary key,-> name varchar (22) not null,-> location varchar (50)->) engine=innodb default charset=utf8 From table: create table tb_emp5 (- > id int (11) primary key,-> name varchar (25),-> deptId int (11),-> salary float,-> constraint fk_emp_dept1 foreign key (deptId) references tb_dept1 (id)->) engine=innodb default charset=utf8 Non-empty constraint not null:create table tb_emp6 (- > id int (11) primary key,-> name varchar (25) not null, # non-null-> deptId int (11),-> salary float,-> constraint fk_emp_dept2 foreign key (deptId) references tb_dept1 (id)->) engine=innodb default charset=utf8 The uniqueness constraint unique requires that the column is unique and is allowed to be empty, but only one value can be empty: create table tb_dept2 (- > id int (11) primary key,-> name varchar (22) unique,-> location varchar (50)->) engine=innodb default charset=utf8 Default value default:create table tb_emp7 (- > id int (11) primary key,-> name varchar (25) not null,-> deptId int (11) default 1111,-> salary float,-> constraint fk_emp_dept3 foreign key (deptId) references tb_dept1 (id)->) engine=innodb default charset=utf8 Self-increasing primary key auto_increment:create table tb_emp8 (- > id int (11) primary key auto_increment,-> name varchar (25) not null,-> deptId int (11),-> salary float,-> constraint fk_emp_dept5 foreign key (deptId) references tb_dept1 (id)->) engine=innodb default charset=utf8 Insert tb_ emp8 three pieces of data: insert into tb_emp8 (name,salary) values ('Lucy', 1000), (' lura', 1200), ('Kevin',1500); id increment view table structure: desc tb_emp8; or show create table tb_emp8\ G modify data table alter modify table name: alter table tb_dept2 rename tb_deptment3; modify field type: alter table tb_dept1 modify name varchar (30); modify field name: alter table tb_dept1 change location loc varchar (50) Add field: alter table tb_dept1 add managerId int (10); add constrained field: alter table tb_dept1 add column1 varchar (12) not null; add field in a location: alter table tb_dept1 add column2 int (11) first; add a new field after a field: alter table tb_dept1 add column3 int (11) after name; delete field: alter table tb_dept1 drop column2; modify the storage engine of the table: alter table tb_deptment3 engine=MyISAM Delete foreign key constraint: alter table tb_emp9 drop foreign key fk_emp_dept; delete data table: drop table if exists tb_dept2 To delete the primary table associated with a foreign key, you need to cancel the foreign key association first, otherwise delete the primary table failed data type × ×: TINYINT 1 byte 2 "8-1" 255 values × ×: smallint 2 bytes ×: int 4 bytes × ×: bigint 8 bytes floating point single precision: float 4 bytes double precision: double 8 bytes decimal: not fixed Generally used in financial system date time type year: 1 byte, format YYYYtime: 3 bytes, format HH:MM:SSdate: 3 bytes, format YYYY-MM-DDdatetime: 8 bytes, format YYYY-MM-DD HH:MM:SStimestamp: 4 bytes, format YYYY-MM-DD HH:MM:SS string type char (n): fixed length string # waste of memory But query speed is fast varchar (n): non-fixed length string # saves memory, but query speed is slow text: store text longtext: store big data text between.. And.. The keywords use select 4 between 4 and 6, 4 between 4 and 6, 12 between 9 and 10 in, and the not in keyword uses select 2 in. Like is used to match the string'%': match any number of characters'_': can match only one character select query data create table fruits (- > f_id char (10) not null,-> s_id int not null,-> f_name char (255) not null,-> f_price decimal (8 f_id) not null,-> primary key (f_id)->) engine->) engine=innodb = utf8 Insert fields: insert into fruits-> ('a1century pencyclophora' 101pjpl) values),-> ('b1jewelry page10.2),-> (' bs1',102,'orange',11.2),-> ('bs2',105,'melon',8.2),-> (' t1p102 minute bananas) -> ('t _ 2), ('t _ 2)),-> ('o _ 2), _ ()),-> ('c _ 0 _ (10)), (~ (3.2)), () ('a2), ()), > (()), > () > ('b _ 2), _ (), > (), > ('b _ 2), _ (), _ 'mango',15.6),-> (' m2century records105jewelry xbabayweights 2.6),-> ('t4jewelry mine107minutes xbababaweights 3.6),-> (' m3examples recalcitrals 105minutes xxttlemagen11.6),-> ('b5century records107minutes xxxexamples respectively 3.6) Single-table query: query table: select fanciidMagic name from fruits; query condition where: select fimidMagna fanciname fancipriceInquisition 10.2; # equal sign = select * from fruits where f_price

< 10; #小于select * from fruits where s_id in(101,102) order by f_name (desc); #in关键字,按f_name排序，desc降序，asc升序select * from fruits where f_price between 2.00 and 10.20; #between andselect * from fruits where f_name like 'b%'; #like关键字匹配, %匹配任何多个字符select * from fruits where f_name like '_____y'; #_匹配任意一个字符select * from fruits where s_id='101' and f_price >

= 5; # and multi-conditional matching select * from fruits where multiple conditions matching 101'or s matching 102; # or multi-conditional matching order by query results sorting select * from fruits order by fancinameistselect * from fruits order by f_price desc; # desc flashback arrangement group by grouping select scondiidJournal count (*) as Total from fruits group by s_id # according to s_id grouping, the same number of s_id: select account (*) as Total from fruits group by s_id having count (f_name) > 1; # having followed by query condition limit limits the number of queries select * from fruits limit 4; # query four select * from fruits limit 4prime3 # Index 4 returns 3 connections within inner join from Article 5, and returns records create table suppliers (- > s_id int (11) not null auto_increment primary key,-> s_name char (50) not null,-> s_city char (50),-> s_zip char (10),-> s_call char (50) not null->) engine=innodb default charset=utf8 in both tables The following action is the select suppliers.s_id associated with fruits and suppliers, fruits.s_id _ name,f_price from fruits inner join suppliers on-> fruits.s_id = suppliers.s_id; # on is followed by a conditional query, and left join returns all records including all records in the left table and table join fields on the right select Subquery: select scuriiddepartment fanciname from fruits where sliced price = (select s1.s_id from suppliers as S1 where s1.squarry citypotent quotation price from fruits where s_id in); union merges the query results and deduplicates the union all merge query does not repeat the select scuriiddepartment farewell price from fruits where f_price < 9.0 union all select scuridline flip price from fruits where s_id in (101103) 2. Example of pymysql module operating database: #! / usr/bin/python#-- *-- coding:utf-8-- *-- import pymysqlconn = pymysql.Connect (host='127.0.0.1',user='root',password='aixocm',port=3306,database='oldboy' Charset='utf8') cursor = conn.cursor () v = cursor.execute ('select * from student') print (v) # cursor.fetchone () # get a piece of data # cursor.fetchmany (2) # get multiple pieces of data result = cursor.fetchall () # get all data print (result) cursor.close () conn.close () #! / usr/bin/python#-- coding:utf-8-import pymysqlconn = pymysql. Connect (host='127.0.0.1' User='root',password='aixocm',port=3306,database='oldboy' Charset='utf8') cursor = conn.cursor () # v = cursor.execute ('insert into student (name) values ("test")) # print (v) v = cursor.execute (' delete from student where sid=7') conn.commit () # commit transaction cursor.close () conn.close () #! / usr/bin/python#-coding:utf-8-import pymysqlnum=8conn = pymysql.Connect (host='127.0.0.1',user='root',password='aixocm',port=3306 Database='oldboy',charset='utf8') cursor = conn.cursor () # v = cursor.execute ('insert into student (name) values ("test")') # print (v) v = cursor.execute ('delete from student where sid=%d'% (num)) # prevent sql from injecting conn.commit () cursor.close () conn.close ()