SQL concepts and basic operations of database tables

This is the second day of my participation in the November Gwen Challenge. Check out the details: the last Gwen Challenge 2021.

Before introducing SQL, let’s take a look at some database concepts.

What is a database

• Database (DB) : A collection of organized and centrally managed data. Simply put, it’s a place where data is stored.
• Database Management System (DBMS) : computer software used to manipulate and manage databases, such as MySQL, Oracle, SqlServer, etc., are Database Management systems.

A database system can contain many databases, and each database contains many tables. A column of a table is called a field, and a row is called a record. For example, here is a table named persons:

The table contains three records (each for a person) and five columns (ID, name, age, gender, and address).

With these basic concepts in hand, let’s take a look at SQL.

The SQL profile

What is the SQL

Structured Query Language (SQL) is a Structured Query Language used to interact with databases and provides various keywords and syntax.

SQL statements and their types

SQL statements that use keywords, table names, and column names combined with certain grammar rules are called SQL statements. SQL statements can be assigned different instructions to the database. According to the types of instructions, SQL statements can be divided into the following three types:

• Data Definition Language (DDL) : Used to create, delete, or modify objects such as databases and tables in databases.

  • Create: Creates objects such as databases and tables
  • Drop: Deletes objects such as databases and tables
  • Alter: Alters the structure of objects such as databases and tables

• Data Manipulation Language (DML) : Used to query or modify records in a table.

  • Select: queries the data in the table
  • Insert: Inserts data into a table
  • Update: Updates data in a table
  • Delete: deletes data from a table

• Data Control Language (DCL) : Used to confirm or cancel operations on Data changes in the database and set users’ permissions on objects in the database.

  • Commit: Commit, that is, confirm changes to data in the database
  • Rollback: Rollback is to cancel changes made to data in the database
  • Grant: grant operation rights to the user
  • Revoke: Revokes a user’s operation rights

Basic writing rules for SQL

• SQL statements are preceded by semicolons;At the end
• SQL keywords are case insensitive, but table names, field names, data, and so on are case sensitive
• String or date types must be enclosed in single quotation marks, such as ‘xyz’, ‘2021-10-29’
• Words need to be separated by Spaces or newlines
• The names of databases, tables, and fields can be lowercase letters, digits, and underscores (_)

The data type

List only common ones:

Basic database operations

Database correlation

• Creating a database

CREATE DATABASE <The database name>;
Copy the code

• Deleting a Database

DROP DATABASE <The database name>;
Copy the code

• List the database

SHOW DATABASES;
Copy the code

• Switching databases

USE <The database name>;
Copy the code

Data table correlation

• Create a table

CREATE TABLE <The name of the table> (
    <The column name1> <type> <The constraint>.<The column name2> <type> <The constraint>. . .<Table of the constraint1>.<Table of the constraint2>,...). ;Copy the code

• Delete table

DROP TABLE <The name of the table>;
Copy the code

• Update the table

  • The new column

    ALTER TABLE <The name of the table> ADD COLUMN <The column name> <type> <The constraint>;
    Copy the code

  • Delete the column

    ALTER TABLE <The name of the table> DROP COLUMN <The column name>;
    Copy the code

  • Modify the column

    ALTER TABLE <The name of the table> MODIFY COLUMN <The column name> <type> <The constraint>;
    Copy the code

• Insert data

INSERT INTO <The name of the table> VALUES(value1And the value2,...). ;Copy the code

Specify the column to insert data into:

INSERT INTO <The name of the table>(column1Column,2,...).VALUES(value1And the value2,...). ;Copy the code

The sample

• Creating a database

CREATE DATABASE test;
Copy the code

• Display database

SHOW DATABASES;
Copy the code

• Switching databases

use test;
Copy the code

• Create table

CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL DEFAULT ' ' COMMENT 'name',
  `age` int(11) COMMENT 'age',
  `gender` varchar(11) NOT NULL DEFAULT 'male' COMMENT 'gender',
  `address` varchar(64) COMMENT 'address',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Personnel Information Sheet';
Copy the code

• The new column

ALTER TABLE persons ADD COLUMN mobile varchar(64) COMMENT 'Mobile phone Number';
Copy the code

• Delete the column

ALTER TABLE persons DROP COLUMN mobile;
Copy the code

• Modify the column

ALTER TABLE persons MODIFY COLUMN age varchar(11) COMMENT 'age';
Copy the code

• Insert data

INSERT INTO persons VALUES (1.'Alice'.24.'female'.'Beijing'.'the 2021-10-29 12:00:00');
Copy the code

INSERT INTO persons(name,age,gender,address) VALUES ('Bob'.25.'male'.'Shanghai');
Copy the code

Original is not easy, if small partners feel helpful, please click a “like” and then go ~

Finally, thank my girlfriend for her tolerance, understanding and support in work and life!