MySQL usage statement

MySQL Common terminal commands

--Link Database
Mysql-uroot-pmysql

--Don't show password * * * *
Mysql-uroot-p
Password

--Exit the database
Quit/exit/ctrl + D
Exit (Remember this) * * * * *
Quit/exit Ctrl+d

--The SQL statement needs a semicolon at the end;
--Show Database version versions
Select version ();

--Show Time now
Select Now ();


--View the currently used database
Select Database ();

--View all databases
show databases;

--Create a database
-- CREATE database name Charset=utf8; ***
Create database Python10 charset = UTF8;

--View the statement that created the database
-- show create database name;
Show CREATE Database python10;

--Using the database
- The name of the use database;
Use Python9; *******

--Delete Database
-- drop database name;
Drop database python10;

--02 operation of data tables

--View all tables in the current database
Show tables;

--Create a table
-- int unsigned : unsigned shaping
-- auto_increment : Indicates auto-growth
--not null : Indicates that cannot be empty
-- PRIMARY key : Represents the primary key
- -Default: Defaults
-- CREATE table data table name (field type constraint [, field type constraint]);
CREATE table user (id int unsigned auto_increment primary key,name varchar () not null,age int unsigned);

--View table structure
- The name of the DESC data sheet;
DESC user;


--Create classes table (ID, name)
CREATE TABLE classes (ID int unsigned auto_increment primary key,name varchar (255));



--Create a students table (ID, name, age, High (decimal),
--gender (enum), cls_id)

CREATE TABLE students (ID int unsigned auto_increment primary key,name varchar () not NULL, age int unsigned,
High Decimal (5,2), gender enum ("Male", "female", "Confidential", "demon") default "Confidential", cls_id int unsigned);

--View the creation statement for the table
-- show create table name;
Show create table students;

--Modify Table-add field mascot (mascot) * *
-- ALTER TABLE name add column name type;
ALTER TABLE classes add mascot varchar (30);

--Modify Table-Modify field: Do not rename version
-- ALTER TABLE name modify column name type and constraints;
ALTER TABLE classes modify mascot varchar (100);

--Modify Table-Modify field: Rename version * * *
-- ALTER TABLE name change formerly known as the new name type and constraints;
ALTER TABLE classes change mascot Jxw varchar (30);

--Modify Table-delete field *
-- ALTER TABLE name drop column name;
ALTER TABLE classes drop JXW;

--Delete Table
-- drop table name;
- drop database;
drop table classes; *
Drop database python10; Half

--03 additions and deletions (curd)

-Increase
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| ID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar (20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
--Full column insertion
-- Insert [into] table name values (...);
--The primary key field can be placeholder with 0 null default
--Insert a class into the classes table
Insert into classes (Id,name) VALUES (1, ' Oldyang ');

Insert into classes (name) VALUES ("Python10");

Insert into classes (name) values (' Python11 '), (' Python12 ');

-Insert a student message into the students table
+--------+-------------------------------------+------+-----+---------+-------- --------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+---------+----------------+
| id | int (10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar (20) | NO | | NULL | |
| age | int (ten) unsigned | YES | | NULL | |
| high | decimal (5,2) | YES | | NULL | |
| gender | enum (' Male ', ' female ', ' neutral ', ' secret ') | YES | | Confidentiality | |
| cls_id | int (11) | YES | | NULL | |
+--------+-------------------------------------+------+-----+---------+----------------+
--Insert All
Span style= "COLOR: #ff0000" > INSERT into students (ID,NAME,AGE,HIGH,GENDER,CLS_ID) VALUES (2, ' Yangyang ', 18,1.80890, ' secrecy ', 001);
--Partial insertion
--  insert into table name (column 1,...) VALUES (value 1,...)
INSERT into students (name) VALUES ("Yang3");

--Multi-line insertion
INSERT into students (name) VALUES ("Zhang San"), ("John Doe");

JN N INSERT into table name (field) VALUES (value) #一一对应

--Modification
-- Update table name set column 1= value 1, column 2= value 2 ... where condition;
--All modifications
Update students set name = "Harry";

--Modification by condition

Update students set name = ' John Doe ' where id = 4;

--Modify multiple values by condition
-- update students set gender = "", name = "xxx" where;
Update students set Name= ' Zhang San ', age = where id = 5;


--Query Basic use
--Query all columns
-- select * from table name;
SELECT * from students;

---set condition query
SELECT * FROM students where id = 2;

--Query the specified column
-- Select column 1, column 2,... from table name;
Select Name,age from students;

--You can use as to specify an alias for a column or table
-- Select field [As Alias], field [as Alias] from data table;
Select Name,age as ' age ' from students;

--Order of fields
Select Age,name from students;

--Delete
--Physical deletion
-- Delete from table name where condition;
Delete from students where id = 3;

--Logical deletion
--Use a field to indicate whether the message is no longer available.
--Add a is_delete field bit type to the students table
--ALTER TABLE name add field type default value;
ALTER TABLE students add Is_delete bit default 0;


--Database backup and recovery (learn)
--mysqldump–uroot–p database name > python.sql;
--Mysql-uroot–p New database name < Python.sql; # Note that you need to create a database before importing

Mysqldump-uroot-p pthon10 > Python10.sql;

Mysql-uroot-p python10[New database name] < Python10.sql

SELECT distinct gender from students; Distinct field eliminates duplicate rows (gender)

ORDER BY field ASC arranges from small to large, ascending
ORDER BY field Desc from large to small sort, i.e. descending
Count : Total

--Connection Query
--INNER join ... on
-- Select ... from table a INNER join table B;

--Left Join
-- Select ... from table a LEFT join table B;

--Right Join
-- Select ... from table a right join table B;

Note: You need to add a condition to avoid Cartesian product

Python Connection Database
1. Import the Pymysql module
2. Establish connection:conn=pymysql.connect (fill in the following parameters in parentheses)
Parameter host: The connected MySQL host, if the machine is ' localhost '
Parameter: Port of the connected MySQL host, default is 3306
Parameter: Database name
Parameter User: The username of the connection
Parameter password: the password for the connection
Parameter charset: The encoding method used in communication, it is recommended to use UTF8
3. Get the object:cs1=conn.cursor ()
4. Execute SQL statement:cs1.execute ()
Execute commit () commit in addition to the query statement
5. Close: Close objects and connections
6. Extension:fetchall () query at execution time

MySQL usage statement