Writing Sql Statement
MySQL – Creating Databases and Tables
1. 2. Select Start -> MySQL -> MySQL Server 5.1 -> MySQL Command Line Client. Enter password to access the MySQL server. The password is specified when MySQL is installed. 3. Type CREATE DATABASE mydatabase to create the database.
4.
Type USE mydatabase; to inform the system that all SQL commands that follow is using mydatabase. If database is found, the system will respond with: Database changed
5. 6. 7.
Copy the create_table.sql script file to c:\script. Open the file in text editor to view the sql statement. Type source c:\script\create_table.sql to create the friends table. If you type the wrong pathname, an error will be displayed. Otherwise, the system responds with: Query ok, 0 rows affected (0.08sec)
8.
Type the following command to display the column details of the table: DESCRIBE friends;
MySQL – Inserting Data into Tables
1. The INSERT SQL statement populate table with data. The general form of INSERT: INSERT INTO table_name(column1, column2, column3, ...) values (value1, value2, value3, ...); 2. Type the following statement to insert the first record in friends table: INSERT INTO friends(ID, Fname, Phone) values (1, “Tan Mei Mei”, “91234123”);
3.
To insert more records, we need to type separate INSERT statements. In order to make life easy, we create a script file insert_friends.sql with as many INSERT statements as we like. Do you remember how to run the script file?
4.
Run the script file and type the following statements to check the INSERT operation: SELECT * from friends;
MySQL – Querying and Updating Tables
1. The SELECT SQL statement queries table to get data. The general form of SELECT: SELECT column1, column2, ... from table_name [WHERE conditions];
2.
Type the following statement to get all fields and all records in friends table: SELECT * from friends; This SELECT statement has no [where] condition. As such, all records are retrieved from the table.
3.
1. 2. Select Start -> MySQL -> MySQL Server 5.1 -> MySQL Command Line Client. Enter password to access the MySQL server. The password is specified when MySQL is installed. 3. Type CREATE DATABASE mydatabase to create the database.
4.
Type USE mydatabase; to inform the system that all SQL commands that follow is using mydatabase. If database is found, the system will respond with: Database changed
5. 6. 7.
Copy the create_table.sql script file to c:\script. Open the file in text editor to view the sql statement. Type source c:\script\create_table.sql to create the friends table. If you type the wrong pathname, an error will be displayed. Otherwise, the system responds with: Query ok, 0 rows affected (0.08sec)
8.
Type the following command to display the column details of the table: DESCRIBE friends;
MySQL – Inserting Data into Tables
1. The INSERT SQL statement populate table with data. The general form of INSERT: INSERT INTO table_name(column1, column2, column3, ...) values (value1, value2, value3, ...); 2. Type the following statement to insert the first record in friends table: INSERT INTO friends(ID, Fname, Phone) values (1, “Tan Mei Mei”, “91234123”);
3.
To insert more records, we need to type separate INSERT statements. In order to make life easy, we create a script file insert_friends.sql with as many INSERT statements as we like. Do you remember how to run the script file?
4.
Run the script file and type the following statements to check the INSERT operation: SELECT * from friends;
MySQL – Querying and Updating Tables
1. The SELECT SQL statement queries table to get data. The general form of SELECT: SELECT column1, column2, ... from table_name [WHERE conditions];
2.
Type the following statement to get all fields and all records in friends table: SELECT * from friends; This SELECT statement has no [where] condition. As such, all records are retrieved from the table.
3.