MySQL Common commands

First, the cursor

A cursor is a way of working with data, in order to view or manipulate the data in the result set, the cursor provides the ability to move forward or backward through the data in the result set one line at a time or in multiple rows. You can use a cursor as a pointer, which can specify any location in the result, and then allow the user to manipulate the data at the specified location.

Template Case One,

Import .......
if __name__ = = ' __main__ ':
    CNX = Connect_mysql ()
    cus = Cnx.cursor ()
    SQL  = "CREATE TABLE test (ID int not null); INSERT into test (ID) values (100);
    Try
        Cus.execute (SQL)
        Cus.close ()
        Cnx.commit ()
    Except Exception as E:
        Cnx.rollback ()
        Print (' Error ')
        # Raise E
    Finally
        Cnx.close ()

Case two is as follows:

 Import pymysql 
 
 # Create connection 
 conn = Pymysql.connect (host= "192.168.48.136", port=3306, user= "Xiang", passwd= " Xiang ", db=" test ") 
 # Create cursor 
 cus = conn.cursor () 
 # define SQL 
 sql =" SELECT * from Test2; " 
 
 # Execute 
 # cus.execute (SQL) 
 # Take all the results, before you take the results, be sure to execute SQL 
 # Cus.fetchall () 
 # to take a result 
 # Cus.fetchone () 
 # Fetch 10 rows of data 
 # Cus.fetchmany (size=10) 
 # Close Cursor 
 # cus.close () 
 # cus.executemany () 
 Try: 
 Cus.execute ( SQL) 
 result = Cus.fetchone () 
 Print (result) 
 except Exception as E: 
 Raise e 
 finally: 
 CUS.C Lose () 
 Conn.close () 
 
 Second, create TABLE, common commands. 
 
 1. Create a table command as follows. 
 
 Case Three 

The 
 CREATE TABLE table name (
 Column name data type is NOT NULL 
 .....). 
); 
 
 CREATE TABLE stdunet (
 StdId int not NULL, 
 Stdname varchar (+), 
 Age int, 
 Sex enum (' M ', ' F '), 
 score int); 
 
 
 ' 123 ' varchar (TEN) 
 ' 123 ' char (TEN) 
 
 Class error correction case four, 
 grant all privileges on * * t O ' user1 ' @ '% ' identified by ' 123456 ' with GRANT option; 
 You this User1 user, only to all the library, all the table to make the deletion and check, and so on, no authorization to other users user2 can not authorize 
 
 2. Common commands. 

1>.select column name from table name where condition to judge

SELECT * from Sutdent where GROUP by stdname

               A, c where a.id = C. Group ID

Select a.ID a.name, c.id from a join C on C. Group ID = a.id and ^^ ^^
SELECT * FROM table_name limiet 10;
Show Create table_name
DESC table_name

2>.insert into table_name (ID, name, age) VALUES (1, ' ling ', 18), (), (), ();

3>.delete from table where condition is judged

4>truncate  only clear data, do not delete table structure
  Drop    table results are all yours.

5>.UPDATE table_name Set column name =xxx, where condition

6>.create Index Library Name _ Table name _ Column name 1_ column name 2 (column name 1, column name 2)

MySQL Common commands