Hello everyone, my name is Sun Bujian 1208, welcome to follow my blog.

Tables: A relational database consists of two – dimensional tables and the relationships between them. A two-dimensional table is called a table in a database.

Field: The data corresponding to an attribute is called a Field, also known as a data item. Fields are often named the same as properties. For example, student id, name, age, gender, department, etc.

Record: The data corresponding to each entity is called a Record.

Common Navicat shortcut keys

CTRL + Q Opens the query window CTRL +/ comment CTRL + Shift +/ Uncomment CTRL + R Runs the selected SQL statement CTRL + L Deletes the selected line CTRL + D Copies the current line and pastes it to the next line CTRL + W closes the current query window

To perform command line operations in Navicat, click tools and select Command Line interface.

Basic operation

— 01 Create a database

CREATE DATABASE study DEFAULT CHARACTER SET utf8mb4;

— 02 Using the database

USE study;

— 03 View tables in the database

SHOW TABLES;

Create table (course)

CREATE TABLE COURSE( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, teacher VARCHAR(10), start-date DATE, operation-time DATETIME)

ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

View tables in the current database

SHOW TABLES;

— 06 View the table structure

DESCRIBE course;

DESC course;

— 07

SHOW CREATE TABLE course;

— 08 View data

SELECT * FROM course;

— 09 Insert data

INSERT INTO course (id, name) VALUES (1, ‘first class’), (2, ‘second class’), (3, ‘the third class’);

— 10 Modify data (note where condition)

UPDATE course SET teacher =’ ‘;

UPDATE course SET teacher WHERE id=2;

UPDATE course SET teacher =’ my_student ‘, start-date=’2020-02-02’, operation-time= NOW() WHERE id=3;

— 11 Delete data (note where condition)

DELETE FROM course WHERE teacher LIKE ‘% n %’;

— This is a vague search that will remove “cow” from teacher

— 12 Delete table

DROP TABLE course;

— 13 Delete libraries

DROP DATABASE study;

Practical cases

Case requirements:

Realize the creation of a database named study, create a table named course, create ID, name, teacher fields, to achieve the basic operation of adding, deleting, changing and checking.

The code is as follows:

CREATE DATABASE study DEFAULT CHARACTER SET utf8mb4 ;

USE study;

SHOW TABLES;

CREATE TABLE course(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(20) NOT NULL,

teacher VARCHAR(10))

ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

SHOW TABLES;

DESC course;

SELECT*FROM course;

INSERT INTO course(id,name)VALUES

(1,’ Lesson 1 ‘),

(2,’ Lesson 2 ‘),

(3,’ Lesson 3 ‘);

UPDATE course SET teacher=’ ‘;

UPDATE course SET teacher WHERE id=2;

DELETE FROM course WHERE teacher LIKE ‘% not %’;

DROP TABLE course;

DROP DATABASE study;

Welcome to subscribe column to invite you to drink a cup of Java, hope to bring convenience to friends in need, but also hope to get everyone’s attention and support.