3Python Full Stack Road series MySQL in-table operation

Python full stack Road series of my

SQL in-table operations

First create a table for testing

--  Creating a database Create database dbname default charset utf8 collate utf8_ general_ci;--  CREATE TABLE create table  ' TB '   (   ' id '  int (5)  NOT NULL  auto_increment,   ' name '  char  NOT NULL,   ' Alias '  varchar (Ten)   default null,   ' email '  varchar ( DEFAULT NULL,   ' password '   varchar  NOT NULL,   ' phone '  char (one)  DEFAULT  ' 13800138000 ',   primary key  (' id ', ' name '))  engine=innodb default charset=utf8; 

Increase in-table data

Enter dbname database mysql> use dbnamedatabase changed# View all tables in current library mysql> show tables;+------------------+| Tables_in_dbname |+------------------+| TB |+------------------+1 row in Set (0.00 sec) # View the contents of the TB table mysql> select * from TB; Empty Set (0.00 sec)

Insert a single piece of data into the insert into TB (Name,email,password) VALUES ("Ansheng", "[email protected]", "as");--insert multiple data inserts into TB (name , Email,password) VALUES ("as", "[email protected]", "pwd"), ("Info", "[email protected]", "I");

To view the inserted data

mysql> select * from tb;+----+---------+-------+-------------------------+----------+-- -----------+| 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 |+-- --+---------+-------+-------------------------+----------+-------------+3 rows in set  ( 0.00 SEC)

Insert data from another table into the current table

See what's in the Tb_copy table

mysql> 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, e-mail, password) select Name,email,password from Tb_copy;

Querying for content within TB

mysql> select * from tb;+----+---------+-------+-------------------------+----------+-- -----------+| 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 |+----+---------+-------+-------------------------+----------+-------------+6 rows in set  (0.00 sec) 

Delete in-table data

--Delete all contents of the table delete from Tb_copy;

--Delete a data in the table delete from TB where id=2 and name= "Ansheng";

Change in-table data

Update TB set name= "as" where id= "3";

Check

--Query all contents of the table select * from tb;--the contents of the query table with conditions SELECT * from TB where ID > 4;

Specify the name of the last column when querying

Mysql> Select Id,name as username from TB where ID > 4;+----+----------+| ID |  Username |+----+----------+| 5 |  Hello | | 6 |  Word | | 7 | Python |+----+----------+3 rows in Set (0.00 sec)

Other operations

Conditions

--Multi-criteria Query SELECT * FROM TB where id>3 and name= "Hello" and password= "1";--Query specified range SELECT * from TB where ID between 4 and 6;--query for data that exists in parentheses select * from TB where ID in (4,6);--query for data that does not exist in parentheses select * from TB where ID not in (4,6);--the content of the other table is the query condition Sele CT * FROM-TB where ID in (select-ID from tb_copy);

Wildcard characters

--All (multiple strings) with p starting with select * from TB where name is like "p%";--All (one character) starting with P SELECT * from TB where name is like "p%";

Limit

--first three rows of data select * from TB limit 3;--3 lines starting from line 2nd select * from TB limit 2,3;--5 rows starting from line 4th select * FROM TB limit 5 offset 4;

Sort

--sort from small to large according to the "Name" column SELECT * from TB ORDER by name asc;--the "name" column from large to small arrange select * from TB ORDER by name desc;--according to "column 1" from large to small Arrange, if same, by column 2 from small to large sort select * from table order BY column 1 desc, column 2 asc;

Group

Select ID from TB GROUP by Id;select Id,name to id,name;select num,nid from table where nid > Ten GROUP by Nu M,nid order nid desc;select Num,nid,count (*), SUM (score), Max (score), Min (score) from table GROUP by Num,nid;select Num from table gr OUP by NUM has max (ID) > 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, 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全栈之路

3Python Full Stack Road series MySQL in-table operation