MySQL in-table operation of 3Python full stack path series

Python Full Stack Road Series My

SQL table operations

Create a table for testing

--CREATE DATABASE dbname DEFAULT CHARSET utf8 COLATE utf8_general_ci;--CREATE TABLE `tb`( `id` int(5) NOT NULL AUTO_INCREMENT, `name` char(15) NOT NULL, `alias` varchar(10) DEFAULT NULL, `email` varchar(30) DEFAULT NULL, `password` varchar(20) NOT NULL,`phone` char(11) DEFAULT '13800138000', PRIMARY KEY (`id`,`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8; add table data into 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 tb table contents mysql> select * from tb;Empty set (0.00 sec)--Insert single data into tb(name,email,password) values("anshengme.com @ gmail.com","as");--Insert multiple data simultaneously into tb(name,email,password) values("as","i@anshengme.com","pwd"),("info","info@anshengme.com","i");

View inserted data

mysql> select * from tb;+----+---------+-------+-------------------------+----------+-------------+| id | name | alias | email | password | phone |+----+---------+-------+-------------------------+----------+-------------+| 2 | ansheng | NULL | anshengme.com@gmail.com | as | 13800138000 || 3 | as | NULL | i@anshengme.com | pwd | 13800138000 || 4 | info | NULL | info@anshengme.com | i | 13800138000 |+----+---------+-------+-------------------------+----------+-------------+3 rows in set (0.00 sec)

Insert data from another table into the current table

View the contents of 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, and password columns from tb_copy into tb

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

Search the contents of tb

mysql> select * from tb;+----+---------+-------+-------------------------+----------+-------------+| id | name | alias | email | password | phone |+----+---------+-------+-------------------------+----------+-------------+| 2 | ansheng | NULL | anshengme.com@gmail.com | as | 13800138000 || 3 | as | NULL | i@anshengme.com | pwd | 13800138000 || 4 | info | NULL | info@anshengme.com | 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 data in a table--Delete all contents in a table delete from tb_copy;--Delete an item of data in a table delete from tb where id=2 and name="ansheng"; Change data in a table update tb set name="as" where id="3"; Query--Query all contents in a table select * from tb;--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-condition 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 existing data in brackets select * from tb where id in (4,6);--Query non-existing data in brackets select * from tb where id not in (4,6);--select * from tb where id in (select id from tb_copy);

wildcard

--all strings starting with p select * from tb where name like "p%";--all strings starting with p select * from tb where name like "p%";

limit

--select * from tb limit 3;--select * from tb limit 2,3;--select * from tb limit 5 offset 4;

sort

--select * from tb order by name asc;--select * from tb order by name desc;--select * from tb order by name desc;--select * from table order by column 1 desc, column 2 asc;

packet

select id from tb group by id;select id,name from tb group by id,name;select num,nid from table where nid > 10 group by num,nid order nid desc;select num,nid,count(*),sum(score),max(score),min(score) from table group by num,nid;select num from table group by num having max(id) > 10;

Special: group by must come after where and before order by

continuous list

No correspondence is displayed

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

No correspondence is displayed

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

Table A shows all. If there is no corresponding relationship 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 handling of overlap

select nickname from A union select name from B;

Combination, no overlap handling

select nickname from A union all select name from B;

#Python full stack path