My SQL basic command summary

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;

Create table

A table is a tabular structure in which information can be stored, as shown below.

ID Name Model Number Series
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.

table lookup

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;

Condition check

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___';

Data alignment

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

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

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;

Delete data

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;

Share This Post

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다

Latest Post