Python Learning Database operations

Basic database Operation database operation: View storage Engine: show engines; View database: show databases;  or show create  Database oldboy\g Creating a database: create database oldboy default charset=utf8; deleting a database:  drop database oldboy; entering database:  use oldboy; data table operations: Creating Data tables: CREATE&NBSP;TABLE&NBSP;TB_EMP1   (    -> id int),     -> name varchar (+),     -> deptid int (one),    -> salary  float    -> ) engine=innodb default charset=utf8;primary key:create  table tb_emp2  (                                                                             -> id int (one)  primary key,    -> name  varchar (+),     -> deptid int (one),     -> salary  float    -> ) engine=innodb default charset=utf8;      Multiple primary key:create table tb_emp3  ( id int (one),  name varchar (25 ),  deptid int (one),  salary float, primary key (Name, deptid)  ) engine= innodb default charset=utf8;    FOREIGN KEY constraint Foreign key: Main Table: Create table tb_ dept1  (    -> id int (one)  primary key,    - > name varchar ( not null,    -> location varchar) (50 )     -> ) engine=innodb default charset=utf8;       from table:create  table tb_emp5  (    -> id int)  primary key,     -> name varchar (+),     -> deptid int (one),     -> salary float,    -> constraint fk_emp_dept1  foreign key (DeptID)  references tb_dept1 (ID)     -> ) engine= innodb default charset=utf8;     non-null constraint NOT&NBSP;NULL:CREATE&NBSP;TABLE&NBSP;TB_EMP6   (    -> id int (one)  primary key,    ->  name varchar (+)  not null,     #非空     ->  Deptid int (one),    -> salary float,    ->  Constraint fk_emp_Dept2 foreign key (DeptID)  references tb_dept1 (ID)     -> ) engine=innodb default charset=utf8; uniqueness constraint unique, requires that the column is unique, allowed to be empty, but only one value is null: CREATE&NBSP;TABLE&NBSP;TB_DEPT2   (    -> id int (one)  primary key,    ->  name varchar ( unique,    -> location varchar)      -> ) engine=innodb default charset=utf8;     Default value: create table tb_emp7  (    -> id int (one)  primary key,     -> name varchar (+)  not null,    ->  deptid int (one)  default 1111,    -> salary float,     -> constraint fk_emp_dept3 foreign key (deptId)  references  TB_DEPT1 (ID)     -> ) engine=innodb default charset=utf8;     self-increment primary key auto_increment: create table tb_emp8  (    -> id int (one)  primary key  auto_increment,    -> name varchar (+)  not null,     -> deptid int (one),    -> salary float,     -> constraint fk_emp_dept5 foreign key (DeptID)  references tb_ DEPT1 (ID)     -> ) engine=innodb default charset=utf8; insert TB_EMP8 three data: Insert &NBSP;INTO&NBSP;TB_EMP8 (name,salary)  values (' Lucy ',  1000), (' Lura ',  1200), (' Kevin ', 1500); ID self-increment view table structure: desc tb_emp8;  or SHOW&NBSP;CREATE&NBSP;TABLE&NBSP;TB_EMP8\G modify data table ALTER TABLE name:  alter  table tb_dept2 rename tb_deptment3; modifying field Types: 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 a field with constraints:  alter table tb_ Dept1 add column1 varchar ( not null;) Add a field to a location:  alter table tb_dept1  add column2 int (one)  first; add a new field after a field:  alter table tb_dept1 add  column3 int (one)  after name; Delete field: alter table tb_dept1 drop  Column2; Modify the table's storage engine: alter table tb_deptment3 engine=myisam; delete foreign KEY constraint:  alter table  tb_emp9 drop foreign key fk_emp_dept; Delete Data sheet:  drop table if exists  tb_dept2; Delete a foreign key associated primary table, you need to first cancel the foreign Key Association, or delete the primary table failed data type shaping:  tinyint 1 bytes   2**8 - 1= 255 value shaping:  smallint 2 byte shaping:  int 4 byte Shaping:  bigint 8 bytes floating point number single precision:  float 4 bytes Double precision :  double 8 byte decimal:  Not fixed, generally used in financial system date Time type year: 1 bytes, 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      #浪费内存, but query fast varchar (n):  non-fixed-length string       #节省内存, but query slow text:  store text longtext:  store big data text between  &NBSP;AND&NBSP, ....   Keywords use select 4 between 4 and 6, 4 between 4 and 6,  12 between 9 and 10;in, not in  keywords using select 2 in (1,3,5, ' thks '),  ' thks '  in (1,3,5, ' thks '); Like to match the string '% ':  match any number of characters ' _ ':  can only match one character select query data create  table fruits  (    -> f_id char)  not null,     -> s_id int not null,    -> f_name char ( 255) &NBSP;NOT&NBSP;NULL,&NBSP;&NBSP;&NBSP;&NBSP;-&GT; f_price decimal (8,2)  not null,    -> primary key (f_id)     -> )  engine=innodb =  engine    -> ) utf8;     Insert field: Insert into fruits (f_id,s_id,f_name,f_price)  values     ->  (' A1 ', 101, ' Apple ', 5.2),    ->  (' B1 ', 102, ' BlackBerry ', 10.2),    ->  (' BS1 ', 102, ' orange ', 11.2),    ->  (' BS2 ', , ' melon ', 8.2),    ->  (' T1 ', 102, ' banana ', 10.3),    ->   (' T2 ', 102, ' grape ', 5.3),    ->  (' O2 ', 103, ' coconut ', 9.2),     ->  (' C0 ', 101, ' Cherry ', 3.2),    ->  (' A2 ', 103, ' apricot ', 2.2),     ->  (' L2 ', 104, ' Lemon ', 6.4),    ->  (' B2 ', 104, ' lemon ', 7.6),     ->  (' M1 ', 106, ' Mango ', 15.6),    ->  (' m2 ',, ' Xbabay ', 2.6),     ->  (' T4 ', 107, ' Xbababa ', 3.6),    ->  (' M3 ',, ' Xxtt ', 11.6),     ->  (' B5 ', 107, ' XXXX ', 3.6); single-table query: Enquiry form:  select f_id,f_name from  fruits; Query Conditions where: select f_id,f_name from fruits where f_price=10.2;      #等号  =select * from fruits where f_price < 10;          #小于select  * from fruits where s_id  in (101,102)  order by f_name  (DESC);     #in关键字, sorted by f_name, desc Descending, ASC Ascending select * from fruits where f_price between 2.00 and 10.20;      #between  andselect * from fruits where f_name like   ' b% ';    #like关键字匹配,  % matches any number of characters select * from fruits where f_name like  ' _____y ';      #_匹配任意一个字符select  * from fruits where s_id= ' 101 '  and  f_price >=5;     #and多条件匹配select  * from fruits where  s_id= ' 101 '  or s_id=102;     #or多条件匹配order  by query results sorted select *  From fruits order by f_name;select * from fruits order by f_ price desc;     #desc倒叙排列group  by Group Select s_id,count (*)  as total  from fruits group by s_id;  #根据s_id分组, s_id the same number select s_id,count (*)  as  total from fruits group by s_id having count (f_name)  > 1 ;     #having后面加上查询条件limit限制查询的数量select  * from fruits limit 4;      #查询四Strip select * from fruits limit 4,3;     #索引为4, starting from fifth to return 3 inner  join , returns the records in both tables create table suppliers  (    -> s_id  Int (one)  not null auto_increment primary key,    -> s_name  char ( not null,    -> s_city char),     -> s_zip char (Ten),     -> s_call char ( not ) null    -> ) engine=innodb default charset=utf8; The following operations are fruits and suppliers associated select suppliers.s_id, s_name, f_name, f_price from  fruits inner join suppliers on     -> fruits.s_id =  suppliers.s_id;     #on后面是条件查询,left join     Returns all records that include all the records in the left table and the right table join fields select s_name,f_price from fruits left join suppliers on fruits.s_id = suppliers.s_id; Sub-query:select  S_id, f_name from fruits where s_id= (select s1.s_id from suppliers  as s1 where s1.s_city= ' Tianjin '); Union merge query Results and go to union all merge query does not go to heavy select s_id  ,f_name,f_price from fruits where f_price < 9.0 union all  select s_id,f_name,f_price from fruits where s_id in (101,103);

This article is from the "Linux Technology" blog, so be sure to keep this source http://haoyonghui.blog.51cto.com/4278020/1945907

Python Learning Database operations