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 (
->	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%')
->	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

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Post

  • 8 things you need to think about for work-life balance
    View on Youtube Image: who works as a design VP for Facebook, presented 8 ways for work and life balance in her article “The
  • 10 special abilities of Smartphone
    View on Youtube No matter where you go or what you do, your smartphone is always with us. We use the internet with a smartphone,
  • Java: abstract class, abstract method
    An abstract class in Java is a class that declares an abstract function to be implemented in a subclass as an abstract method. An abstract
  • Java: inheritance and constructors
    When an object is created from an inherited class, the constructor of the superclass is performed before the constructor of the subclass. That is, when
  • Java reserved words: super, final
    super is a Java reserved word that is used to access member variables or methods of a superclass that is obscured by a subclass. Access
  • Java: finalize method and garbage collection
    The Java Virtual Machine(JVM) automatically performs garbage collection for objects that are no longer needed for efficient system operation. At this point, the JVM calls
  • Java: Method overloading
    Method overloading is the same concept as constructor overloading. That is, you can use methods of the same name in the same class, but methods
  • Java final variable
    You can specify an end variable using the reserved word final. The final variable represents a constant value that can not be changed. Typically, final
  • Java class variables
    Class variables are declared with static and have the concept of global variables. Class variables are used for the following purposes. Object references and object
  • Member variables in Java
    Java’s member variables are all declared outside the methods in the class and represent the properties that the object can have. Member variables consist of