directory

MySQL

MySQL installation

1. Basic commands

Two, database operation

Table operation

4. Data operation

Five,

Associated query:

Python connects to the MySQL database

Example: Get the MySQL version number using Python code

Example: Create a new table for the database

Example: Insert data into a table

Example: Query data in a table

MySQL

MySQL installation

MySQL installation:

Xinzhi.wenda.so.com/a/152325151…

www.csdn.net/gather_2a/M…

Navicat installation:

Blog.csdn.net/pdcfighting…

youzfx.cn/resource/1

 

 

1. Basic commands

1. Start the service

  • Run CMD as an administrator
  • Format: net start Service name
  • Example: net start mysq157

2. Stop the service

  • Run CMD as an administrator
  • Format: Netstop service name
  • Example: net stop mysq157

 

3. Connect data

  • Format: mysQ1 -u user name -p
  • Example: mysq1 -u root -p
  • Enter a password (set during installation)

4. Log out (disconnect)

  • The quit or exit

 

5. Check the version (can be executed after connection)

  • Example: select version();

6, display the current time (can be executed after connection)

  • Example: select now();

7. Remote connection

  • Format: mysQ1 -h IP address -u user name -p Enter the mysQ1 password of the other party

 

Two, database operation

1. Create a database

  • 3) Create DATABASE data name charset= UTF8;
  • Example: create database sunck charset=utf8;

2. Delete the database

  • Format: DROP database Data name;
  • Example: drop database sunck;

3. Switch the database

  • Format :use database name;
  • Example: use sunck;

4. View the selected database

  • select . database( ) ;

 

Table operation

 

1. View all tables in the current database

  • show tables ;

2, create table

  • Format :create table name (column and type);
  • Auto_ increment Primary key Not null Indicates that the primary key is not empty
  • Create table student(id int auto_ increment primary key, name vARCHar (20) not null);

3, drop table

  • Format: drop table Table name;
  • Example: drop table student;

4. View the table structure

  • Format: desc table name;
  • Example: desc student;

 

5. Look at the construction sentences

  • Format: show create table name;
  • Example: show create table student;

6, rename table name

  • Rename table original table name to new table name;
  • Example: reanme table car to newcar;

 

7, modify table

  • Format: the alter table table name add | change | drop column type;
  • Example: ALTER table newCAR add isDelete bit default 0

 

4. Data operation

 

1, add a, insert all columns

  • Insert into values(…)
  • Note: The primary key column grows automatically, but it needs to occupy space when the full column is inserted. Usually, the day is used. After the insertion, the actual data will prevail
  • Example: insert into student values(day, “Tom “, 19,1,” Beijing “,0)

B. Default insert

  • Insert into table name (table 1, table 2, values(numerical 1, numerical 2,……) ;
  • Example: insert into student (name, age, address) values(“1ilei”,19,”_ Shanghai “);

C. Insert multiple pieces of data simultaneously

  • Insert into values(.. :),.. ),…
  • Example: insert into student values(0, “hanmeimei”,18,0, “Beijing “,0)

2, delete

  • Delete from table name where condition;
  • Delete from student where id=4;
  • Note: there is no condition to delete all, use with caution

3, change

  • Update table name set column 1= value 1, column 2= value….. Where conditions;
  • Update student set age=16 where id=7;
  • Note: there is no condition for all columns to be modified, so use with caution

4,

 

  • Description: Query all data in the table
  • Select * from table name;
  • Select * from. Student;

 

 

Five,

1. Basic grammar

  • Format: SELECT * from table name

  • Description:

  • A, the from keyword is followed by the table name, indicating that the data is from the table

  • Select * from table_name where table_name = ‘*’; select * from table_name where table_name = ‘*’

  • C, in the column name section after select, you can use as to get the alias, which is displayed in the result set

  • D. If you want to query multiple columns, separate them with commas

  • Example:

          select * from student ;

          select  name ,  age from student ;

          select name as a, age from student;

2. Eliminate duplicate lines

  • Using DISTINCT before the column following select eliminates duplicate rows
  • Example:
  •       select gender from student ;
  •       select distinct gender from student ;

3, condition query

  • A, grammar

  • Select * from table name where condition

  • B. Comparison operators

  • Equal to =

  • More than >

  • Less than < Greater than or equal to >=

  • Less than or equal to <= not equal to! Select * from student where id>8; select * from student where id>8;

  • C. Logical operators

  • And and

  • The or or

  • Not the

  • D. fuzzy query

  •      like   

  • % represents any number of any characters

  • _ indicates an arbitrary character

  • E. Range query

  • In means in a non-continuous range

  • between… and… Represents within a continuous range

  • F, empty judgment

  • Note: NULL is different from “”

  • Null: Is not null: is not NU11

    Select * from student where address is null; Select * from student where address is not null;

  • G. Priority

  • The parentheses, not comparison operators, and logic operators have higher precedence than or. If they are both present and you want to select OR first, you need to combine them with ()

4, aggregation,

  • To quickly wait for statistics, five aggregate functions are provided
  • A. Count (*) indicates the total number of rows to be counted. * and column names can be written in parentheses
  • B, Max (column) means to find the maximum value of this column
  • C, min(column) means to find the minimum value of this column
  • D, sum(column
  • E, AVG (column) means to find the average value of this column
  • Demand: Query the total number of students
  • Select count(*) from student;

5, grouping

  • Grouping by field indicates that data of the same field will be put into a collection.
  • After grouping, only the same data columns can be queried. Different data columns cannot be displayed in the result set
  • The grouped data can be counted and aggregated
  • Syntax: select column 1, column 2, gather….. From table name group by 1, 2, 3…
  • Select * from * where * having * groupby * groupby * groupby * groupby * GROUPby * GROUPby * GROUPby * GROUPby * GROUPby

6, sorting,

Select * from table name order by asc desc, asC desc

Description:

  • A. Sort the data by column 1. If the values of some columns 1 are the same, sort by column 2
  • B. The default order is from smallest to largest
  • C, ASC descending order
  • D, desc

7, paging

 

Select * from table_name limit start, count;

Note: the start index starts from 0

Example:

  • Select * from student limit 0,3
  • Select * from student limit 3,3
  • Select * from student where gender=1 limit 0,3

 

 

Associated query:

select students. name, class.name from class inner join students on class. id=students. classid; select students. name,class.name from class left join students on class. id=students. classid; Inner join table B: rows matching table A and B will appear in the result set

Table A left join table B: rows from table A that match rows from table B will appear in the result set, plus rows unique to table A that do not match rows will be filled with null

Table B: Rows from table A that match rows from table B will appear in the result set, plus rows unique to table A that do not match rows will be filled with NULL

 

Python connects to the MySQL database

To connect to the MySQL database, Python needs to install the PyMySql package

Example: Get the MySQL version number using Python code

import pymysql

Connect to database
Parameter 1: IP address of the host where the mysql server resides
# parameter 2: user name
# parameter 3: password
Parameter 4: The name of the database to connect to
db = pymysql.connect('localhost'.'root'.'passwd'.'class')

Create a cursor object
cursor = db.cursor()
SQL statement print version number
sql = 'select version()'
Execute SQL statement
cursor.execute(sql)
Get the returned information
data = cursor.fetchone()
print(data)
# Disconnect
cursor.close()
db.close()
Copy the code

Running results:

 

Example: Create a new table for the database

import pymysql

db = pymysql.connect('localhost'.'root'.'passwd'.'class')
cursor = db.cursor()

Check whether the table exists and delete it if it does
cursor.execute('drop table if exists bandcard')
# building table
sql = 'create table bandcard(id int auto_increment primary key,money int not null)'
cursor.execute(sql)

cursor.close()
db.close()
Copy the code

Running results:

 

Example: Insert data into a table

import pymysql

db = pymysql.connect('localhost'.'root'.'passwd'.'class')
cursor = db.cursor()

sql = 'insert into bandcard values(0,100)'
try:
    cursor.execute(sql)
    db.commit()
except:
    If the commit fails, return to the previous data
    db.rollback()
cursor.close()
db.close()
Copy the code

Running results:

 

Fetchone (): Function: fetchAll (): function: receives all rows returned RowCount: is a read-only property that returns the number of rows affected by the execute() methodCopy the code

Example: Query data in a table

import pymysql

db = pymysql.connect('localhost'.'root'.'123456ddd'.'class')
cursor = db.cursor()

sql = 'select * from student'
try:
    cursor.execute(sql)
    reslist = cursor.fetchall()
    for row in reslist:
        print('%d--%s' %(row[0],row[1]))
except:
    print('failure')
    If the commit fails, return to the previous data
    db.rollback()
cursor.close()
db.close()
Copy the code

Running results:

 

 

 

 

Learn together and make progress together. If there are any mistakes, please comment