3Python全栈之路系列之MySQL表内操作，,Python全栈之路

Python全栈之路系列之My

SQL表内操作

先创创建一个表用于测试

--创建数据库CREATEDATABASEdbnameDEFAULTCHARSETutf8COLLATEutf8_general_ci;--创建表CREATETABLE`tb`(`id`int(5)NOTNULLAUTO_INCREMENT,`name`char(15)NOTNULL,`alias`varchar(10)DEFAULTNULL,`email`varchar(30)DEFAULTNULL,`password`varchar(20)NOTNULL,`phone`char(11)DEFAULT‘13800138000‘,PRIMARYKEY(`id`,`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

增加表内数据

进入dbname数据库mysql>usedbnameDatabasechanged#查看当前库所有的表mysql>showtables;+------------------+|Tables_in_dbname|+------------------+|tb|+------------------+1rowinset(0.00sec)#查看tb表内的内容mysql>select*fromtb;Emptyset(0.00sec)

--插入单条数据insertintotb(name,email,password)values("ansheng","[email protected]","as");--同时插入多条数据insertintotb(name,email,password)values("as","[email protected]","pwd"),("info","[email protected]","i");

查看插入的数据

mysql>select*fromtb;+----+---------+-------+-------------------------+----------+-------------+|id|name|alias|email|password|phone|+----+---------+-------+-------------------------+----------+-------------+|2|ansheng|NULL|[email protected]|as|13800138000||3|as|NULL|[email protected]|pwd|13800138000||4|info|NULL|[email protected]|i|13800138000|+----+---------+-------+-------------------------+----------+-------------+3rowsinset(0.00sec)

把别的表的数据插入当前表

查看tb_copy表内的内容

mysql>select*fromtb_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|+----+--------+-------+-------+----------+-------------+3rowsinset(0.00sec)

把tb_copy表内的name,email,password列插入到tb表中

insertintotb(name,email,password)selectname,email,passwordfromtb_copy;

查询tb内的内容

mysql>select*fromtb;+----+---------+-------+-------------------------+----------+-------------+|id|name|alias|email|password|phone|+----+---------+-------+-------------------------+----------+-------------+|2|ansheng|NULL|[email protected]|as|13800138000||3|as|NULL|[email protected]|pwd|13800138000||4|info|NULL|[email protected]|i|13800138000||5|hello|NULL|NULL|1|13800138000||6|word|NULL|NULL|2|13800138000||7|python|NULL|NULL|3|13800138000|+----+---------+-------+-------------------------+----------+-------------+6rowsinset(0.00sec)

删除表内数据

--删除表内的所有内容deletefromtb_copy;

--删除表内某一条数据deletefromtbwhereid=2andname="ansheng";

更改表内数据

updatetbsetname="as"whereid="3";

查

--查询表内所有内容select*fromtb;--带条件的查询表内的内容select*fromtbwhereid>4;

查询的时候指定最后一列的名称

mysql>selectid,nameasusernamefromtbwhereid>4;+----+----------+|id|username|+----+----------+|5|hello||6|word||7|python|+----+----------+3rowsinset(0.00sec)

其他操作

条件

--多条件查询select*fromtbwhereid>3andname="hello"andpassword="1";--查询指定范围select*fromtbwhereidbetween4and6;--查询括号内存在的数据select*fromtbwhereidin(4,6);--查询括号内不存在的数据select*fromtbwhereidnotin(4,6);--以别的表的内容为查询条件select*fromtbwhereidin(selectidfromtb_copy);

通配符

--以p开头的所有（多个字符串）select*fromtbwherenamelike"p%";--以p开头的所有（一个字符）select*fromtbwherenamelike"p%";

限制

--前三行数据select*fromtblimit3;--从第2行开始的3行select*fromtblimit2,3;--从第4行开始的5行select*fromtblimit5offset4;

排序

--根据"name"列从小到大排列select*fromtborderbynameasc;--根据"name"列从大到小排列select*fromtborderbynamedesc;--根据“列1”从大到小排列，如果相同则按列2从小到大排序select*from表orderby列1desc,列2asc;

分组

selectidfromtbgroupbyid;selectid,namefromtbgroupbyid,name;selectnum,nidfrom表wherenid>10groupbynum,nidorderniddesc;selectnum,nid,count(*),sum(score),max(score),min(score)from表groupbynum,nid;selectnumfrom表groupbynumhavingmax(id)>10;

特别的：group by 必须在where之后，order by之前

连表

无对应关系则不显示

selectA.num,A.name,B.namefromA,BwhereA.nid=B.nid;

无对应关系则不显示

selectA.num,A.name,B.namefromAinnerjoinBonA.nid=B.nid;

A表所有显示，如果B中无对应关系，则值为null

selectA.num,A.name,B.namefromAleftjoinBonA.nid=B.nid;

B表所有显示，如果B中无对应关系，则值为null

selectA.num,A.name,B.namefromArightjoinBonA.nid=B.nid;

组合

组合，自动处理重合

selectnicknamefromAunionselectnamefromB;

组合，不处理重合

selectnicknamefromAunionallselectnamefromB;

#Python全栈之路

3Python全栈之路系列之MySQL表内操作