Python full stack Road series MySQL in-table operation

First create a table for testing

--Create a databaseCREATEDATABASEdbnameDEFAULTCHARSETUtf8COLLATEUtf8_general_ci;--Create a tableCREATETABLE' TB '(' ID 'Int(5)NotNullAuto_increment,' Name 'Char(15)NotNull,' Alias 'varchar(10)DEFAULTNull,' Email 'varchar(30) default null ' password ' varchar (20) not NULL< Span class= "P",  ' phone ' char (11 ) default  ' 13800138000 '  primary key  ( ' ID ' , ' name ' ) ) engine=innodb default charset=utf8    

Increase in-table data

# 进入dbname数据库mysql> use dbnameDatabase changed# 查看当前库所有的表mysql> show tables;+------------------+| Tables_in_dbname |+------------------+| tb               |+------------------+1 row in set (0.00 sec)# 查看tb表内的内容mysql> select * from tb;Empty set (0.00 sec)

--Inserting a single piece of dataInsertIntoTb(Name,Email,password) values (  "XXX" , "[email protected]"  "XX" ); --simultaneously inserting multiple data insert into tb (name,email,password) values ( "as" , "xxxx.com" , "pwd"  ( "info"   "xxx.com" , "I" );       

To view the inserted data

select * from tb;把别的表的数据插入当前表

See what's in the Tb_copy table

select * from tb_copy;+----+--------+-------+-------+----------+-------------+| id | name   | alias | email | password | phone       |+----+--------+-------+-------+----------+-------------+|  5 | hello  | NULL  | NULL  | 1        | 13800138000 ||  6 | word   | NULL  | NULL  | 2        | 13800138000 ||  7 | python | NULL  | NULL  | 3        | 13800138000 |+----+--------+-------+-------+----------+-------------+3 rows in set (0.00 sec)

Insert the Name,email,password column in the Tb_copy table into the TB table

insert into tb (name, email, password) select name,email,password from tb_copy;

Querying for content within TB

select * from tb;6 rows in set (0.00 sec)

Delete in-table data

-- 删除表内的所有内容delete from tb_copy;

-- 删除表内某一条数据delete from tb where id=2 and name="xxx";

Change in-table data

update tb set name="as" where id="3";

Check

-- 查询表内所有内容select * from tb;-- 带条件的查询表内的内容select * from tb where id > 4;

Specify the name of the last column when querying

Mysql>SelectId,NameAsUsernameFromTbwhereId>4+----+----------+| id | username | +----+----------+| 5 | hello || 6 | word || 7 | python | +----+----------+3 rows in set Span class= "P" > (0. XX sec)            

Other operations

Conditions

--Multi-criteria QuerySelect*FromTbwhereId>3andName="Hello"andPassword="1";--Query the specified rangeSelect*FromTbwhereIdBetween4and6;--Querying for data that exists in parenthesesSelect*Fromtb where id in  (4 ,6 --Query for data that does not exist in parentheses select * from tb  where id not in  (4 ,6 --Query criteria for the contents of other tables select * from tb  where id in  (select id from tb_copy);           

Wildcard characters

-- 以p开头的所有（多个字符串）select * from tb where name like "p%";-- 以p开头的所有（一个字符）select * from tb where name like "p%";

Limit

-- 前三行数据select * from tb limit 3;-- 从第2行开始的3行select * from tb limit 2,3;-- 从第4行开始的5行select * from tb limit 5 offset 4;

Sort

--according to "name" column from small to large arrangement select * from  TB order by name asc; --sort from large to small according to "name" column select * from tb  Order by name desc; --According to "column 1" from large to small arrangement, if the same is the same as column 2 from small to large sort select * from  table Span class= "K" >order by  column 1 desc ,  column 2 asc;        

Group

SelectIdFromTbGroupById;SelectId,NameFromTbGroupById,Name;SelectNum,NidFromTablewhereNid>10GroupByNum,NidOrderNidDesc;SelectNum,Nid,Count (*sum (score< Span class= "P" >), max (scoremin  (score) from  table  Group by num,nid; Select num from  table group by num having max (id) Span class= "o" >> 10;           

Special: Group by must precede the where, order by

Even table

No correspondence is not displayed

select A.num, A.name, B.name from A,B where A.nid = B.nid;

No correspondence is not displayed

select A.num, A.name, B.name from A inner join B on A.nid = B.nid;

A table all displays, if there is no correspondence in B, the value is null

select A.num, A.name, B.name from A left join B on A.nid = B.nid;

b table all display, if there is no corresponding relationship in B, the value is null

select A.num, A.name, B.name from A right join B on A.nid = B.nid;

Combination

combination, automatic processing of coincident

select nickname from A union select name from B;

Combine, do not handle coincident

select nickname from A union all select name from B;

Python full stack Road series MySQL in-table operation