This is the 29th day of my participation in the August Wenwen Challenge.More challenges in August

MySQL > create MySQL

  • Command line mode

    MySQL Command Line Client: In the Command Line mode, enter SQL statements to add, delete, modify, and query the database

  • Graphical interface tools

    Similar to Excel, we can use dot to achieve add, delete, change, check and other database operations

SQL

  • SQL is a standard computer language for accessing and processing databases
  • Is a language designed specifically for communicating with databases, providing a simple and efficient way to read and write data from a database
  • SQL refers to structured query Language, database query and programming language, used to access data and query, update, and manage relational database systems

A, SQL language classification

Data query language DQL, data manipulation language DML, data definition language DDL, data control language DCL.

1. Data query language DQL

The basic structure consists of a SELECT clause, a FROM clause, and a WHERE clause

2. Data manipulation language DML

Data manipulation language DML has three main forms:

  1. INSERT: INSERT
  2. UPDATE: the UPDATE
  3. DELETE: DELETE

3. Data definition Language DDL

Data Definition Language (DDL) is used to create tables, views, indexes, synonyms, clusters, and so on in a database

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

4. Data control language DCL

Data control language (DCL) is used to grant or reclaim certain privileges to access the database, control the time and effect of database manipulation transactions, and monitor the database. Such as:

  1. GRANT: authorization

    Data control language (DCL) is used to grant or reclaim certain privileges to access the database, control the time and effect of database manipulation transactions, and monitor the database

  2. The ROLLBACK: rolled back

  3. COMMIT:

Pay attention to

Database insert, delete, and modify operations are not complete until the transaction is committed to the database. Until the transaction commits, only the person operating the database has the right to see what is being done; others can only see it after the final commit is completedCopy the code

Second, SQL language advantages

  • SQL is supported by almost all major DBMSS

    SQL is not a language specific to a particular database vendor. So, learning the language allows you to work with almost any database.

  • SQL is simple and easy to learn

    Its statements are all made up of very descriptive English words, and there are not many of them

  • Although SQL looks simple, it is actually a powerful and flexible language

With its language elements, very complex and advanced database operations can be performed.

Three, SQL syntax characteristics

  • SQL statements can be newlines and must end with a semicolon

  • Commands are case-insensitive keywords and functions are recommended in uppercase (official recommendation)

  • A command cannot be modified after a wrong line feed, but can be cancelled with \c

Four, the operation steps of the database

  • Connect the MySQL
  • Open the database
  • Operational data
  • Exit the connection

1, connect

Open the cli, enter the password, and press Enter. If mysql> is displayed, the connection is successful

2, open the

When you connect to MySQL initially, no database is open for you to use. Select a database before you perform data operations on any database. To do this, USE the USE keyword.

USE database name; USE python21;Copy the code

The Database changed display indicates that the Database was selected successfully and the data in all tables under the current Database can be used
The database must be opened with USE (select the specified database) before it can be read

Pay attention to

  • The keyword is a reserved word as part of the MySQL language. Never name a table or column with a keyword
  • When using USE to switch and select a database, select an existing database or report an error

3, the operation

1) Database operation

  • Viewing a Database

    SHOW DATABASES; Returns a list of available databases

  • Creating a database

    CREATE DATABASE DATABASE name DEFAULT CHARSET=UTF8; (Execution case insensitive)

  • View the library build statement

    SHOW CREATE DATABASE DATABASE name;

  • Deleting a Database

    DROP DATABASE DATABASE name;

2) Data table operation

In database management system, there can be many databases, and each database can contain more than one data table

  • Create a table

    SQL > create table namecreate tableTable name (field1Type constraints, fields2Type constraints)default charset=utf8; Such as:create table shop (
        id int not null primary key auto_increment,
        goods_name varchar(255),
        goods_id int not null,
        goods_prices varchar(255))default charset=utf8; Or # if the table does not exist, create it; if it does, do not execute this commandcreate table if not existsTable name (field1Type constraints, fields2Type constraints,.......)default charset=utf8;
Copy the code

    Such as:

    create table if not exists shop (id int not null primary key auto_increment, goods_name varchar(255),goods_id int not null,goods_prices varchar(255))default charset=utf8;
Copy the code

  • View building statements

        SHOW CREATE TABLEThe table name \ G orSHOW CREATE TABLEThe name of the table.Copy the code

  • View all tables in a database

        SHOWTABLES; Before viewing the table, you need to enter the specified database (that is, view the table under the current database).Copy the code

  • View the data in the data table

        SELECT * FROMThe name of the table.Copy the code

  1. Each item is called a field
  2. Each row is called a record

  • View table structure

        DESCThe name of the table.Copy the code

  • Delete table

        DROP TABLEThe name of the table.Copy the code

3) Data operation

  • increase
  • delete
  • change
  • check

  • Add data/insert data

    # Single insertINSERT INTOTable name (field1, the field2, the field3,...).values(value1And the value2And the value3,...). ; Such as:insert into class(class_name,teacher,stu_num) values ('python1'.'gaga'.19); # batch insertINSERT INTOTable name (field1, the field2, the field3,...).values(a value1And a value2And a value3), (b value1, b value2, b value3),... ; Such as:insert into class(class_name,teacher,stu_num) values ('python4'.'gaga'.67), ('python2'.' ' ' '.23), ('python3'.'gaga'.23);
Copy the code

  • The query

        SELECT * FROMThe name of the table.SELECTfield1, the field2, the field3 FROMThe name of the table.SELECT * FROMThe name of the tableWHEREfield=A value;Copy the code

  • Modify the

    UPDATE the table nameSETfield=A valueWHEREConditions;Copy the code

  • delete

        DELETE FROMThe name of the tableWHEREfield=Certain conditions;Copy the code

4, exit

    exit;
    quit;
Copy the code

5. Shortcut keys

  • \G Format the output and display the result vertically (vertical display)

  • \s Displays server information

    Using delimiter Connection ID Connection ID or Thread ID of the connection. For clients that have already established connections, there is a unique connection ID

  • \c Terminates the command input operation

  • \h View help

  • \q Exits the current command line mode

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news