Demo and introduction of common operation methods under MySQL database commands ~, Mysql database

Demo and introduction of common operation methods under MySQL database commands ~, Mysql database

Mysql database

To install mysql, go to the Baidu tutorial.

Start Database
The password set by mysql-u root-p under cmd
Command
Show databases; view the Database List of the mysql database management system
Use Database Name; select the mysql database to be operated, use this command, all mysql commands are only for this database
Show columns from data table; displays data table attributes, attribute type, primary key information, whether it is Null (Null), default value, and other information.
Create database testdb charset "utf8"; create a database named testdb and support Chinese characters.
Drop database testdb; delete database testdb
Show index from data table; displays detailed index information of the data table, including PRIMARY (PRIMARY key)

Create a data table
Create table table_name (column_name [column name] column_type [column type]); # The semicolon is very important in the database.

For example:
Mysql> create table student (
-> Id int auto_increment, # [auto_increment indicates auto-increment of 1 for each inserted data entry]
-> Name char (32) not null,
-> Age int not null,
-> Register_date date not null,
-> Primary key (id ));
Then a student data table is created.

Insert new data into the MySQL DATA table
Insert into table_name (field1, field2,... fieldN) values (value1, value2,... valueN)
Use the table example created above
Mysql> insert into student (name, age, register_date) values ("zhangyang", 3, "8-8-1-1 ")
Mysql> insert into student (name, age, register_date) values ('zhangyang', 3, '2014-5-5') # There is no difference between single quotes and double quotes.
This allows you to insert data into a data table.
Mysql query data
The select command can read one or more data records.
* Number instead of other fields
Select * from student; shows the details of all fields in the data table.

The running result is as follows:
+ ---- + ----------- + ----- + --------------- +
| Id | name | age | register_date |
+ ---- + ----------- + ----- + --------------- +
| 1 | zhangyang | 3 | 2011-01-01 |
| 2 | zhangyang | 3 | 2015-05-05 |
+ ---- + ----------- + ----- + --------------- +
General Syntax:
Select column_name, column_name from table_name;

You can use offset n to set the offset. In other words, the offset is read from the n + 1 data entry, which must be used with limit. The default value is 0.
Limit n indicates that only n data entries are read. For example, select * from student limit 2 offset 3; # offset must be after limit
Where clause:
You can use the where statement to include any conditions:
For example, select * from student where id> 3; the output is the data after the third entry.

Select * from student where id = 4; Output fourth data
You can also query multiple conditions:
Select * from student where id> 3 and age> 5; query and output data older than 5 after the third row.
Fuzzy query:
Select * from student where register_date like '2017-2018 '; remember to use like and %

Modify data in mysql

Update statement
Syntax: update table_name set field1 = new-value1, field2 = new-value2 where clause;
For example:
Update student set name = 'Han mei' where name = 'zhangyang'; # You can modify only the items in the column name.

Mysql deletes data
Delete statement
Delete from table_name where clause; precisely delete data after setting deletion Conditions
For example:
Delete from student where name = 'Han mei'; delete all data named Han Meimei.

Data Sorting:
Select from table_name order by fieldn;
Select from table_name order by fieldn desc;
Sort by column n. The default value is ascending. Adding a desc is descending.

Group by is used for grouping statistics.
Example
Select name, count (*) from student group by name;
Name is used to count
Mysql> select * from student; # I used delete to delete the first two items. Where does the id number increase or where?
+ ---- + ------ + ----- + --------------- +
| Id | name | age | register_date |
+ ---- + ------ + ----- + --------------- +
| 3 | Lu Yi | 31 | 2018-11-05 |
| 4 | x Yi | 31 | 2048-01-05 |
| 5 | x Yi | 31 |
+ ---- + ------ + ----- + --------------- +
3 rows in set (0.00 sec)

Mysql> select name, count (*) from student group by name;
+ ------ + ---------- +
| Name | count (*) | # count (*) can also be assigned to a new name, also using **.
+ ------ + ---------- +
| X Yi | 2 |
| Lu Yi | 1 |
+ ------ + ---------- +
2 rows in set (0.07 sec)

----------------------- I am the delimiter ------------------------------------------
Mysql> select name, count (*) as count_name from student group by name;
+ ------ + ------------ +
| Name | count_name |
+ ------ + ------------ +
| X Yi | 2 |
| Lu Yi | 1 |
+ ------ + ------------ +
2 rows in set (0.27 sec)
Note that the previous name is the name column, and the subsequent name is the result of displaying the [by] name count!

The aggregate function of group:
Mysql> select name, sum (age) from student group by name;
+ -------- + ---------- +
| Name | sum (age) |
+ -------- + ---------- +
| X Yi | 62 |
| Translated by Zhang jia | 45 |
| Cao Qinghua | 15 |
| Lu Yi | 31 |
+ -------- + ---------- +
4 rows in set (0.04 sec)

Finally, count the age of all people (here we can add other things to count, I am just an example), just add with rollup before the semicolon
Mysql> select name, sum (age) as age_count from student group by name with rollup;
+ -------- + ----------- +
| Name | age_count |
+ -------- + ----------- +
| X Yi | 62 |
| Translated by Zhang jia | 45 |
| Cao Qinghua | 15 |
| Lu Yi | 31 |
| NULL | 153 |
+ -------- + ----------- +
5 rows in set (0.00 sec)
If you do not want NULL, you can use the coalesce syntax to replace NULL with the desired name.
Mysql> select coalesce (name, 'Total'), sum (age) as age_count from student group by name with rollup;
+ ----------------------- + ----------- +
| Coalesce (name, 'Total') | age_count |
+ ----------------------- + ----------- +
| X Yi | 62 |
| Translated by Zhang jia | 45 |
| Cao Qinghua | 15 |
| Lu Yi | 31 |
| Total | 153 |
+ ----------------------- + ----------- +
5 rows in set (0.00 sec)

Modify the table itself rather than the data. For example, modify the table header name and age.
Mysql alter statement
Alter table student add sex enum ('M', 'w') not null; add the sex field and set the field type to enum ('M', 'w ')

Alter table student drop age; Delete the entire age field from student (the entire column corresponding to age)
To modify the field type and name, use the change Clause.
Mysql> alter table student change sex gender char (32) default 'X'; # change followed by the old field name, followed by the new field name, and then the new field type is, the default value depends on whether the situation needs to be set.
Query OK, 5 rows affected (0.84 sec)
Records: 5 Duplicates: 0 Warnings: 0

Query the intersection and set difference between two tables
Innerjoin intersection left join difference set (left to right) right join is also difference set (right to left)

Full join union: mysql does not directly support full join, and can be output together with left and right.
Syntax:
Create table A (a int not null );
Create table B (B int not null );
Insert into A (a) values (I); # abbreviated here, I is 1-5
Insert into B (B) values (I); # abbreviated here, I is 3-7
Select * from A inner join B on A. a = B. B; the intersection of A and B is output.
+ --- +
| A | B |
+ --- +
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+ --- +
Select * from A left join B on A. a = B. B. At this time, the difference set of output B in A is displayed first. The intersection is displayed, followed by the difference set.
+ --- + ------ +
| A | B |
+ --- + ------ +
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 1 | NULL |
| 2 | NULL |
+ --- + ------ +
According to the literal meaning, it is not difficult to see that right join is actually displaying the result.

Because mysql does not directly support the union statement, you can use the union statement to put left join and right join together and output the following output:
Select * from A left join B on A. a = B. B union select * from A right join B on A. a = B. B;
Mysql transaction:
In fact, it is the first time to enter begin; # Start the transaction,
Then various cool operations, if the program crashes at this time, no matter whether it is inserting data or modifying or doing anything, it will not be recorded, but if the id number is auto-incrementing, the serial number actually changes, just as the result of entering rollback after the operation is the same
If a commit is input, the operated data is retained.

Mysql Index
If the mysql with an index is an airplane, then the mysql with no index is a bicycle sharing ~, The speed varies greatly. Of course, if the data size is small, it cannot be reflected. However, if the data volume is large, there will be a big difference. However, the index also has disadvantages, and the Table query speed is improved, however, if you perform update, insert, and delete operations on a table, mysql not only needs to store data but also indexes. Creating indexes will occupy disk space.
Create an index
Create index index_name on table_name (x (length); # index_name index name (depending on what you like to write), table_name is the table to be indexed, and x is the index field, length: the length of the field. It cannot exceed the length of the field. If the length of the field is the same as that of the field

,,,
Note: The primary key is automatically an index.
,,,

Delete An index:
Drop index index_name on table_name; # Delete the index_name index in the table_name table.

 

Reading about how python connects to the database and various operations...