Few basic operation mysql after installation...

After installing mysql server and python client connector, I need to know how to set up a data base and read write it, I don't need something super fancy, just simply usable for adding rolls and readable for others.


http://www.mysqltutorial.org/  is a site seems nice to me to read...there is a basic tutorial

Manage Database in MySQL

Under windows mysql server, the is a MySQL command line client, open it, it would ask for the password, which is what we have entered during installation.

Once entered, try

SHOW DATABASES;

it would list out what are the databases available. capital or not is not an issue, but always end with ;

we can use

1	CREATE DATABASE [IF NOT EXISTS] database_name;

to create data base,and use show to see it.

as linux system, we can use up arrow to find previous used commands.
No matter you use capital letter for database name or not, it will always in 小寫

Tell mysql which database to be used:

1	USE database_name;

Removing Databases

examples as following: if exists statement can be used.

1 2 3

CREATE DATABASE IF NOT EXISTS temp_database; SHOW DATABASES; DROP DATABASE IF EXISTS temp_database;

Understanding MySQL Table Types, or Storage Engines

There are 7 types of tables(so called storage engines). after v5.5, it use inniDB as default when creating a table.

MySQL Data Types

After table types, there are also data types to know. basically there are numeric data and string data type, but both can be characteristic grouped in to few more types, such as date and time data type, spatial data type.

Creating Tables Using MySQL CREATE TABLE Statement

2 3	CREATE TABLE [IF NOT EXISTS] table_name(         column_list         ) engine=table_type

engine type is innoDB if not indicated.
each column list can be represented :

column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT]

explanation:

• The column_name specifies the name of the column. Each column always associates with  a specific data type and the size e.g.,   VARCHAR(255). 
• The  NOT NULL or NULL indicates that the column accepts NULL value or not.
• The DEFAULT value is used to specify the default value of the column.
• The AUTO_INCREMENT indicates that the value of column is increased by one whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

so, examples like this:

2 3 4 5 6 7 8

CREATE TABLE IF NOT EXISTS tasks (   task_id int(11) NOT NULL AUTO_INCREMENT,   subject varchar(45) DEFAULT NULL,   start_date DATE DEFAULT NULL,   end_date DATE DEFAULT NULL,   description varchar(200) DEFAULT NULL,   PRIMARY KEY (task_id) ) ENGINE=InnoDB

 will create a table like this:

there is a sample data base goes with this tutorial:
http://www.mysqltutorial.org/mysql-sample-database.aspx


there are ways to drop table, copy table or changing table structure,

• MySQL Copy Table
• MySQL DROP TABLE - Removing Existing Tables
• Changing Table Structure Using MySQL ALTER TABLE

what I have created, you still need a ";" at the end

To import a databases from external:

1. Login to your MYSQL console.
2. use <name_of_your_database>;
3. source <path_of_your_.sql_file>

Do not add a ; at the end of 3rd step.

Beside show databases; there is also show tables; to see what tables are in the database we are using.
also you can describe tablename; to see what are the field in this table.

to show what's in the table, we can use select * from tablename; my table "geenroom" is empty now.

we can use insert command to insert a row of data:

2	INSERT INTO table(column1,column2...) VALUES (value1,value2,...);

or multiple row is also possible, just add the values()

2 3 4

INSERT INTO table(column1,column2...) VALUES (value1,value2,...),        (value1,value2,...);

I added a row into my table"greenroom"(remember to use ' for strings, such as date and time)

we could check with select command:

and there is a command to delete

• Using MySQL DELETE to Remove Data from Tables

==========================================================
there is also a tutorial for python connector

• Python MySQL Tutorial