Learn basic mysql syntax-Mysql Tutorial-php.cn

Preface

## mysql

Mysql syntax--This article is learned by using Navicat Premium (database management tool) to connect mysql data.

This study mainly has two parts:

1. Create users, create databases, assign permissions to users, and delete user permissions.

2. Common data types in MYSQL

3. Table-create table, primary key, foreign key

4. Three major paradigms of database design

All the following codes are edited using mysql syntax in the new query table.

/* SQL multi-line comment*/
--SQL single-line comment

Create user: CREATE USER 'Username'@'Hostname' IDENTIFIED BY 'Password';
The hostname can be empty. If it is empty, it defaults to % permission, indicating that all hosts can connect.
Assign permissions to users: GRANT permission name ON database name. Indicates TO username@hostname
Delete user permissions: REVOKE permission name ON database name. Indicates FROM user name@host name;
##Create database : CREATE DATABASE [IF NOT EXISTS] database name [CHARACTER SET[= ] 'UTF8'];
　　<<
Query all databases in this machine: SHOW DATABASES

↓Use the mydb database↓, Indicates that the following queries will target the mydb database by default
USE mydb;
##Query all data tables in the database: SHOW TABLES [FROM database]

1. Create a user, create a database, assign permissions to the user, and delete User rights.

　　
1. Character type:

① CHAR(N): A string with a fixed length of N characters. If the length is not enough, spaces will be automatically filled;
② VARCHAR(N): Stores characters of variable length. Commonly used 0~255;
③ TEXT: stores variable-length strings. (Commonly used phrases to publish articles and other large paragraphs of content) 0~((2^16-1)*10^2);
　　 ④ TINYTEXT:0~((2^8-1)*10);
　　 ⑤ MEDIUMTEXT: 0~((2^24-1)*10^3);
⑥ LINGTEXT:0~((2^32-1)*10^4);
⑦ enum("male", "Female"): enumeration type, the field can only accommodate enumerated data.

2. Shaping:

① TINYINT: Unsigned 0~2^8-1 Signed -2^7~2^7-1;
② SMALLINT: Unsigned 0~2^16-1 Signed-2^15~2^15-1;
③ MEDIUMINT: Unsigned 0~2^24-1 Signed-2^23~2^23 -1；
④ INT: Unsigned 0~2^32-1 Signed -2^31~2^31-1 Most commonly used!
⑤ BIGINT: Unsigned 0~2^64-1 Signed -2^63~2^63-1;

3. Floating point type:

① FLOAT: Can be accurate to 7 significant digits after the decimal point;
② DOUBLE: Can be accurate to 15 to 16 significant digits after the decimal point;

4. Date and time data type

Note: Since time storage uses string or timestamp storage, the date type is rarely used in the database.
① DATE: Stores date and time data
② TIMESTAMP: More accurate than DATE

##2 Common data types in MYSQL

3. Table-Create table, primary key, foreign key

　　
【Create table】：

CREATE TABLE [IF NOT EXISTS] form name (
IF NOT EXISTS can be omitted, after omitting Repeated creation error. If not omitted, it will detect whether the table already exists during creation. If the table exists, the creation statement will not be executed
Defining columns: column name data type list keywords
　)
Commonly used columns Definition keyword:
① UNSIGNED: Set the list to an unsigned column. Only columns of numeric type can be set
② AUTO_INCREMENT PRIMARY KEY sets the column to an automatic growth column. Autogrow columns must be primary keys.
③ Not NULL: Settings are listed as non -empty constraints
④ unique: Set the unique constraint. Duplicate values cannot appear in this field.
⑤ DEFAULT: Set default value constraints.

[Primary key]：

1. What are the precautions for primary key? The primary key is empty by default! Primary key default uniqueness constraint!
2. How to set the primary key?
① Define in the column is settings: Age Smallint (3) Primary Key,
② Set: Primary Key (Age),

[outer key]
# [外)
# 1. What are the precautions for setting foreign keys? The types must be the same. (Numeric types require the same length and unsigned, strings require the same type, and the lengths can be different.) 　　 ③ The field where the foreign key is set must have an index. If there is no index, an index will be automatically generated when setting the foreign key.
2. What is the syntax for setting foreign keys?
[CONSTRAINT foreign key name] FOREIGN KEY (foreign key field) REFERENCES reference table (reference field) [ON DELETE SET NULL on update CASCADE]--Set referential integrity

3. Foreign Referential integrity operations for key constraints?
Reference operation: How to deal with the foreign key in the foreign key table when the reference field of the reference table is deleted or updated.
Refer to the optional value: RESTRICT refuses to delete or update the reference field; (default)
no action is the same as RESTRICT, but this instruction only takes effect in MySQL; When a field is deleted or updated simultaneously; (Foreign key table and reference table are synchronized)
　　　　　　　　　　　　set null When a reference field in the reference table is deleted or updated, the foreign key of the foreign key table is set to null;

CREATE TABLE IF NOT EXISTS tb1( 
-- IF NOT EXISTS 可以省略，省略后重复创建报错.如果不省略，则创建时会检测表是否已存在，如果表存在则不再执行创建语句id INT(3),
    `name` VARCHAR(255) NOT null, -- name是系统关键字，所以使用反引号``包裹age SMALLINT(3) AUTO_INCREMENT PRIMARY KEY,
    lalala INT UNIQUE,
 height DOUBLE(3,2) DEFAULT 1.2 -- 设置默认值约束：默认值为1.2-- PRIMARY KEY(age));create table if not exists classes(
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  classname VARCHAR(255) NOT NULL );CREATE table if not EXISTS `user`(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    clsId INT UNSIGNED,
    `name` VARCHAR(255) NOT NULL, CONSTRAINT user_fk_classes FOREIGN KEY (clsid) REFERENCES classes(id) ON DELETE SET NULL on update CASCADE)-- auto_increment-- 显示表结构SHOW TABLES;-- 显示表内容结构SHOW COLUMNS FROM TB1;-- 现实表的建表语句show create TABLE tb1;-- 删除表DROP TABLE IF EXISTS TB1;DROP TABLE IF EXISTS classes;DROP TABLE IF EXISTS `user`;-- 修改表名 ALTER table tb1 rename tb2;-- 修改字段 列-- alter table 表名 change 旧列名 新列名 列定义 [first|after某一列]-- first 将这个字段调整为表格第一列； after某一列： 将这个字段放到某一列后面alter table tb1 change height width VARCHAR(200) not NULL FIRST; 



-- 删除表中某一列alter table tb1 drop name;-- 新增一列：必选部分：alter table tb1 add haha DOUBLE(8,2)alter table tb1 add haha DOUBLE(8,2) DEFAULT 1.2 after age;-- 新增多列：不能调整列的位置，只能插在最后。alter table tb1 add (
    ha1 DOUBLE(3,2) UNSIGNED,
    ha2 VARCHAR(255)
);-- 同时修改多表明  rename table tb3 to tb1[,`USER`to user1];rename table tb2 to tb1,`USER`to user1;-- 增加主键约束alter table tb1 add PRIMARY KEY(id);-- 删除主键约束alter table tb1 drop PRIMARY KEY;-- 新增唯一性约束ALTER table tb1 add unique key(ha1);-- 删除唯一性约束：由于创建唯一性约束会默认创建索引，所以删除时，需删除索引ALTER table tb1 drop index ha1;    
-- 设置默认值约束：前提必须设置default 属性ALTER table tb1 alter ha1 set default 20;-- 删除默认值约束ALTER table tb1 alter haha drop default;-- 设置外键约束 必选部分  alter table tb1 add  foreign key (clsid)REFERENCES classes(id)alter table tb1 add constraint waijianming foreign key (clsid)REFERENCES classes(id) ON DELETE SET NULL on update CASCADE;-- 删除外键约束，由于常见外键时会生成索引，所以删除外键后，需要删索引。alter table tb1 drop foreign key waijianming;alter table tb1 drop INDEX waijianming;

SHOW COLUMNS FROM TB1;

Copy after login

4 The three major paradigms of database

　
1. First normal form (1NF): Each column (field) in the data table must be the smallest unit that cannot be split. That is to ensure the atomicity of each column.
For example: userInfo: 'Yantai City, Shandong Province 13181621008'
userads: 'Yantai City, Shandong Province' userTel: '13181621008'
2. Second Normal Form (2NF): After satisfying 1NF, the requirements : All columns in the table must depend on the primary key, and there cannot be any column that has no relationship with the primary key. In other words, a table only describes one thing.

For example: The order table can only describe order-related information, so all fields must be related to the order ID;
Related to product ID;
Therefore: order information and product information cannot appear in the same table.

3. Third Normal Form (3NF): Each column in the table must be directly related to the primary key, rather than indirectly. (Each column in the table can only depend on the primary key).
For example: In the order table, customer related information is required, after the branch customer table. In the order table, you only need to have a user ID. There cannot be other customer information. Because other user information is directly related to the user ID, not to the order ID.

[The essential difference between the second normal form and the third normal form]
is that there are no two tables. The second normal form means that one table contains the attributes of multiple different entities, so it must To be divided into multiple tables.
The third normal form requires that multiple tables have been divided. Then, one table can only have the ID (primary key) of another table, but not any other information (any other information must be used The primary key is queried in another table)