MySQL BASIC Syntax structure

increase, delete, change, check LibraryDATABASE

1, CREATE DATABASE "add"

Create Database dbname charset UTF8; #创建    database   library name   setting encoding  UTF8

2, Query the library "check"

Create Database dbname; #查询数据库的 Create information  show databases; #列出所有存在的数据库名

3, change the database "change"

Alter Database db1 charset GBK; #更改   Database   name  encoding format gbk# change the database code named DB1 to GBK. 

4, delete the database "delete"

Drop Database db1; #删    database   name

TableTABLE

Switch library: Use DataBaseName; (library is equivalent to folder, switch library is like Switch folder)

View Current Library: Select database (View the current name of the library, which is equivalent to viewing the current folder name)

The operation of the table is to operate within the Library (folder), each table is equivalent to the file in the library, the table name is the file name, the table is the contents of the document. The created table generates two files, one is *.frm and the other is *.ibd.

1, CREATE TABLE "add"

#语法形式Create TableTable name (field name 1 type[(width) constraint conditions], field name 2 type[(width) constraint conditions], field name 3 type[(width) constraint conditions]); #字段间用','Comma separate, last one don't have','comma. #语句样式Create TableT1 (IDint, nameChar); #创建 table name (column Name property)

2, inquiry form "Check"

Create Table T1. #查询 The property information for the created table "\g" is formatted and displayed after it is used.  desc  t1; #与上一条一样, query the property information for the created table. Show tables; #查询当前库中所有表的名字. 

3, modify the table structure "change"

#修改表名Alter TableT1 rename newt1# change table name rename new table name # Add FieldAlter TableT1AddRemarksChar(Ten); #修改 table table name Add field name fields TypeAlter TableT1AddRemarksChar(Ten),               AddRemarks2Char( One); #注: Add multiple fields with','Comma separated,';'The semicolon ends, but both requireADDThe field name data type format. Alter TableT1AddNewfieldintfirst; #修改 table table name increase field name fields type Add the top # Note: This field is added to the front of the table. Alter TableT1AddNewfield1intafter name; #修改 table table name Add field name fields type in ... Next Field name # Note: After+The field name means that the new field name is added to the field name. #删除字段Alter TableT1Dropnewfield1; #修改 table Name drop field name # Modify fieldAlter TableT1 Modify NameChar(Ten#修改 Table table Name property Column Name property valueAlter TableT1 Change name NameChar(Ten); #修改 table table name change column name (old) column name (new) property value

4, delete the table "delete"

Drop Table T1, #删   table    name

Copying tables

1. Create an empty library first:Create Databasedb3 charset UTF8;2. Using the new library: Usedb3;3. "Method One" copies the fields from the Target library table:Create TableT1SelectHostUser  fromMysql.User; The new table table name looks for the field name from the target library. target table; #注: The structure properties of the table and the field data in the table are copied together. If you want to copy only the table structure, you can use the Where condition or let the conditional formula in the where is not true, so that only the table structure is copied. #例:Create TableT1SelectHostUser  fromMysql.User where 1>5; # "Method Two" duplicate table structure:Create TableT1 likeMysql.User; #新建 table names are like Target libraries. Target Table # Note: Copy only table structure without field content

RecordRecord

1, add record "increase"

Insert  into T1 (id,name) value (id,name), #插入   to the   table name (column name)  value (column name)  #into可以不写, but it is recommended to write, make the statement structure is complete and readable, and even a column name must be enclosed in parentheses. Example: T1 (name) value (name)insert into T1 value (id,name), (id,name); Insert  into T1 (name) value (name) , #插入 to the T1 value (column name), and if a column in the table is specified, the value is also assigned in the specified format, as shown in two examples. 

2, check the table record "check"

Select  from t1; #查询 column name      from the   table name Select from db1.t1; #查询   column name     from the   database name. Table name  Select*  from T1; #查询  all Columns  from the  table name

3, change the table information "Change"

UpdateDb1.t1SetName='Sly'; #更新 database name. Table name set column name=The new value # Changes the contents of the Name column in the T1 table in database db1 to'Sly'. UpdateT1SetName='Sly' whereId=2; #更新 table name set column name=new Value condition condition Value # Update the contents of the Name column in the T1 table to'Sly, the condition is a record with ID 2. 

4, delete the record "deleted"

Delete  from t1; #删除  Remove all records from the T1 table from the table name #.  Deletefromwhere id=2, #删除   from the  table name  condition  Condition Value

Basic concepts of SQL statements

The SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL languages:

1. DDL statement Database Definition Language: Database, table, view, index, stored procedure, such as Create DROP ALTER

2. DML statement Database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select

3. DCL Statement Database Control Language: for example, to control user access rights grant, REVOKE

Naming rules for libraries

Can consist of letters, numbers, underscores, @, #, $

Case sensitive

Uniqueness

You cannot use keywords, such as create select

Numbers cannot be used alone

Maximum 128 bits

Use the Help + command to view the use of SQL statements.

Table

Types of Tables is the storage engine .

First, check the MySQL supported storage engine

#命令show engines;

Note: MySQL supports a variety of storage engines and is the default engine that needs to focus on innodb,mysql.

Ii. Specifying a table type (storage engine)

#例: Create Table int) Engines=InnoDB;

InnoDB is two tables in the library.

MySQL BASIC Syntax structure