Learning mysql syntax-basics (1), mysql syntax-Basics
Preface
Mysql
Mysql syntax-This article uses Navicat Premium (Database Management Tool) to connect to mysql data.
This article mainly consists of two parts:
1. Create a user, create a database, assign permissions to the user, and delete user permissions.
Ii. Common Data Types in MYSQL
Iii. Table-create a table, primary key, and foreign key
Iv. Three paradigms of Database Design
All the following code is edited using mysql syntax in the new query table.
1. Create a user, create a database, assign permissions to the user, and delete the user permissions.
|
/* SQL multi-line comment */
-- SQL single row comment
Create user: create user 'username' @ 'hostname 'identified BY 'Password ';
The host name can be blank. If it is null, the default permission is %, indicating that all hosts can be connected.
Assign permissions TO users: GRANT permission name ON database name. indicates TO user name @ host name
Delete user permission: REVOKE permission name ON database name. indicates FROM username @ host name;
Create database: create database [if not exists] DATABASE name [character set [=] 'utf8'];
<IF [if not exists] is omitted, an error is returned when you create a database repeatedly!
Query all DATABASES in the Local Machine: SHOW DATABASES
The consumer uses the database metadata of mydb, indicating that the following queries are targeted at the mydb database by default.
USE mydb;
Query all data TABLES in the database: show tables [FROM database]
2. Common Data Types in MYSQL
|
I. Balanced type:
① CHAR (N): A string with a fixed length of N characters. If it is not long enough, it will be automatically filled with spaces;
② VARCHAR (N): stores character segments with edge lengths. Commonly used 0 ~ 255;
③ TEXT: a variable-length string. (Often used to publish articles and other large sections) 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 );
7. enum ("male", "female"): Enumeration type. fields can only contain the data that is cited.
Ii. 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 is the most commonly used!
⑤ BIGINT: Unsigned 0 ~ 2 ^ 64-1 signed-2 ^ 63 ~ 2 ^ 63-1;
3. floating point:
① FLOAT: It can be precise to the seven digits after the decimal point;
② DOUBLE: It can be precise to 15 to 16 digits after the decimal point;
Iv. Date and Time Data Types
Note: Since time storage is stored using strings or timestamps, the date type is almost unnecessary in the database.
① DATE: store DATE and Time Data
② TIMESTAMP: more accurate than DATE
3. Create a table, primary key, and foreign key
|
[Create a table ]:
Create table [if not exists] form name (
If not exists can be omitted. IF it is omitted, an error is returned when the table is created again. IF it is NOT omitted, the system checks whether the table already EXISTS. IF the table EXISTS, the creation statement is no longer executed.
Define column: column name, data type list, keyword
)
Common column definition keywords:
① UNSIGNED: Set the list to an UNSIGNED column. Only numeric columns can be set.
② AUTO_INCREMENT primary key is set as the auto-increment column. Auto-increment columns must be primary keys.
③ NOT null: The column is NOT empty.
④ UNIQUE: sets the uniqueness constraint. This field cannot contain duplicate values.
⑤ DEFAULT: Set the DEFAULT value constraints.
[Primary key ]:
1. Primary Key considerations? The primary key is empty by default! The default uniqueness constraint of the primary key!
Only the primary key can be set to automatically increase (the primary key does not necessarily grow automatically, and the automatic growth must be a primary key)
2. How do I set the primary key?
① The column definition is set: age SMALLINT (3) primary key,
② Set after column definition: primary key (age ),
Foreign key]
1. What are precautions for setting foreign keys?
① Only the innodb Database engine supports foreign keys. Modify the mysql. ini file: default-storage-engine = INNODB
② The foreign key and the Data Type of the reference column must be the same. (The length and unsigned characters must be the same for the numeric type, and the string type must be the same. The length can be different .)
③ The field for setting the foreign key must have an index. If no index exists, an index is automatically generated when a foreign key is set.
2. Set the foreign key syntax?
[Constraint foreign key name] foreign key (foreign key field) REFERENCES reference table (reference field) [on delete set null on update CASCADE] -- SET reference integrity
3. What are the integrity-based operations for foreign key constraints?
Reference operation: when the reference fields of the reference table are deleted or updated, how does one cope with the foreign keys in the foreign key table.
Optional value for reference operation: restrict rejects the reference table to delete or update the reference field. (default)
No action is the same as restrict, but this command takes effect only in mysql;
When cascade deletes or updates the reference fields of the reference table, the records of the foreign key table are deleted or updated synchronously. (The foreign key table and the reference table are synchronized)
When set null deletes or updates the reference field of the reference table, the foreign key of the foreign key table is set to null;
Create table if not exists tb1 (-- if not exists can be omitted. if not exists is omitted, an error is returned after repeated creation. if it is NOT omitted, the system checks whether the table exists. If the table exists, the creation statement id INT (3), 'name' VARCHAR (255) NOT null is NOT executed, -- name is a system keyword, so use the backquotes ''To enclose age SMALLINT (3) AUTO_INCREMENT primary key, lalala int unique, height DOUBLE (1.2) DEFAULT -- set the DEFAULT value constraint: the default value is 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 -- display table structure show tables; -- display table content structure show columns from TB1; -- show create TABLE tb1; -- delete the drop table if exists TB1; DROP TABLE IF EXIST S classes; drop table if exists 'user'; -- modify the table name alter table tb1 rename tb2; -- Modify Field column -- alter table name change old column name new column name column Definition [first | after a column] -- first adjust this field to the first column of the table; after a column: put this field behind a column: alter table tb1 change height width VARCHAR (200) not null first; -- delete a column in the table alter table tb1 drop name; -- add a column: required: alter table tb1 add haha DOUBLE (1.2) alter table tb1 add haha DOUBLE () DEFAULT after age; -- add multiple columns: No The position of the column can only be inserted at the end. Alter table tb1 add (ha1 DOUBLE (3, 2) UNSIGNED, ha2 VARCHAR (255); -- If you modify this parameter, rename table to tb1 [, 'user' to user1] is displayed. rename table tb2 to tb1, 'user' to user1; -- add the primary key constraint alter table tb1 add primary key (id); -- delete the primary key constraint alter table tb1 drop primary key; -- add the unique constraint ALTER table tb1 add unique key (ha1); -- delete the unique constraint: an index is created by default because the uniqueness constraint is created, you need to delete the index ALTER table tb1 drop index ha1; -- set the default value constraint: the default attribute ALTER table t must be set. B1 alter ha1 set default 20; -- delete the default value constraint 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; -- DELETE foreign key constraints because indexes are generated when common foreign keys are used, therefore, after deleting the foreign key, you need to delete the index. Alter table tb1 drop foreign key waijianming; alter table tb1 drop INDEX waijianming; show columns from TB1;
4. Three major database paradigms
|
1. 1NF: each column (field) in a data table must be the smallest unit that cannot be split. That is, to ensure the atomicity of each column.
Example: userInfo: 'yantai City, Shandong Province 123'
Userads: 'yantai City, Shandong Province 'usertel: '123'
2. 2NF: After 1NF is met, the requirement is that all columns in the table must depend on the primary key, and no column is related to the primary key. That is to say, a table only describes one thing.
For example, the Order table can only describe order-related information, so all fields must be related to order ID;
The product table can only describe product-related information. Therefore, all fields must be related to the product ID;
Therefore, the order information and product information cannot appear in the same table.
3. 3NF: each column in the table must be directly related to the primary key, rather than indirectly related. (Each column in the table can only depend on the primary key ).
For example, the Order table requires the customer information, which is placed after the customer table of the branch office. In the order table, you only need a user ID. There cannot be other customer information. Because other user information is directly associated with the user ID, rather than the order ID.
[Essential differences between the second paradigm and the third paradigm]
Because two tables are not separated, the second paradigm is that a table contains multiple attributes of different entities, and must be divided into multiple tables.
The third paradigm requires that multiple tables have been partitioned. Therefore, one table can only have the ID (primary key) in another table ), no other information is available (all other information is queried using the primary key in another table)
Notes may be incorrect during study. Thank you for your criticism.
Reflection, replay, get a little bit every day ------------------- look forward to better yourself
