Creating the database
The database is created with a query statement of the form “CREATE DATABASE name”.
mysql> CREATE DATABASE Company;
When the DB is created, enter the command in the form of “USE name” to use the DB.
mysql> USE Company;
A table is a tabular structure in which information can be stored, as shown below.
|1||name 1||Model 1||Number 1||Series 1|
|2||name 2||Model 2||Number 2||Series 2|
The data types that can be used for tables are shown below.
- VARCHAR: String
- INT: integer number
- DOUBLE: real number
The table is created with “CREATE TABLE table name ()”. For example, enter the query command below to create the products table and specify each field. When specifying a field, enter it in the order of “field name, type, property”.
mysql> CREATE TABLE products ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(50) NOT NULL, -> modelnumber VARCHAR(15) NOT NULL, -> series VARCHAR(30) NOT NULL);
The field properties can be specified as shown below.
- NOT NULL: Fields must be entered
- AUTO_INCREMENT: Automatically increases the number
- PRIMARY: Specify as primary key
Check table information
The generated table can be identified as “DESCRIBE table name” with information related to the table field.
mysql> DESCRIBE products;
Save the data
A database can own a table and store data in table. The command to add data to the table is “INSERT INTO table name (field, field) VALUE (data, data)”.
mysql> INSERT INTO products (name, modelnumber, series) VALUE -> ('Eric Clapton Stratocaster', '0117602806', 'Artist'), -> ('Jeff Back Stratocaster', '0119600805', 'Artist');
The ID field is generated automatically by applying the AUTO_INCREMENT attribute.
When retrieving the data of a table, used it in the form of “SELECT field, field FROM table”.
mysql> SELECT * FROM products;
mysql> SELECT id, name, series FROM products;
The command to select the desired data in the database is the “WHERE” command and can be used in the form of “SELECT field, field FROM table WHERE condition”.
mysql> SELECT * FROM products -> WHERE series="Artist";
When you use the WHERE command, you can further refine the condition by using comparison operators.
- =: Left side equals right side
- ! = Or <>: left and right are different
- <: The right side is larger than the left side
- >: Left side is bigger than right side
- <=: Right side is greater than or equal to left side
- >=: Left side is greater than or equal to right side
- OR: logical sum operator
- AND: logical multiplication operator
mysql> SELECT * FROM products -> WHERE (series="Artist") OR (series="Road Worn");
View data that matches a specific part
Use the “LIKE” command if you want to see data that matches a specific part of the text. For example, if you want to retrieve all the data whose modelnumber field begins with 011, use:
mysql> SELECT * FROM products -> WHERE modelnumber LIKE '011%';
You can also specify up to the number of characters. The following command queries all data whose modelnumber field begins with 011 and is followed by three letters.
mysql> SELECT * FROM products -> WHERE modelnumber LIKE '011___';
The collation uses the “ORDER BY” command. For example, if you want to sort all the data in the products table in ascending order by name field, use the following.
mysql> SELECT id, name, series FROM products -> ORDER BY name;
To distinguish between ascending and descending order, use “ASC” or “DESC” command.
mysql> SELECT id, name, series FROM products -> ORDER BY name DESC;
Selecting data for a specific location
Use the “LIMIT” command to select data at a specific location. This “LIMIT” command is used to display a list of bulletin boards, etc., and is a basic table command to implement paging.
If you select two parent data in the products table, you can use it as shown below.
mysql> SELECT * FROM products LIMIT 2;
If you add 2 after the LIMIT command, you can select a specific number of data at a specific location.
mysql> SELECT * FROM products LIMIT 2, 2;
If you want to use other commands together with the SELECT command, you must enter them in the order of SELECT, FROM, WHERE, ORDER BY, and LIMIT as shown below.
mysql> SELECT id, name, modelnumber FROM products -> WHERE (id < 7) AND (modelnumber LIKE '011%') -> ORDER BY name DESC -> LIMIT 3;
Data grouping is the most challenging part of the database. The grouping command is "GROUP BY". For example, to group series fields, use:
mysql> SELECT series FROM products -> GROUP BY series;
Data modification uses the "UPDATE" command. For example, to change the name field of data with an id field of 4, use:
Note that you must use the "WHERE" command together with the "UPDATE" command. Otherwise, be careful because the name attribute of all data changes.
mysql> UPDATE products -> SET name='American Deluxe Telecaster', modelnumber = '0' -> WHERE id = 4;
Data deletion uses the "DELETE" command. As with "UPDATE", all data is deleted unless "DELETE" is used with the "WHERE" command.
mysql> DELETE FROM products -> WHERE id = 10;
Deleting tables and databases
To delete a table, use the "DROP" command.
mysql> DROP TABLE products;
To delete a database, use the "DROP DATABASE" command.
mysql> DROP DATABASE Company;