MySQL Initial learning

Creating User: Create user ' username ' [@ ' host name '] identified by ' Password ';
The hostname can be empty, and the default is the% permission, which means that all hosts can connect.
create USER ' jredu ' @ ' loaclhost ' identified by ' jredu ';
 
 
Assign permissions to users : GRANT permission name on database name. Table name to user @ host
all represents all permissions, and *. * represents all tables in the database
GRANT All on * * to ' jredu ' @ ' loaclhost ';
 
REVOKE from delete user rights
REVOKE all on *.*from ' jredu ' @ ' loaclhost ';
 
GRANT select,insert,update,delete on Pingjiao. ' admin ' to ' jredu ' @ ' loacalhost ';
 
Creating database: Create database [IF not EXISTS] DB name [CHARACTER SET [=] ' utf8 '];
If you omit if not EXISTS and then repeatedly create the database, you will get an error!
 
CREATE DATABASE IF not EXISTS mydb CHARACTER SET ' UTF8 ';
 
Delete database : Drop database [IF EXISTS] name;
DROP DATABASE IF EXISTS mydb;
 
querying all databases in this computer
SHOW DATABASES;
 
 
Use the MyDB database to indicate that the following query will be for the MyDB database.
Use mydb;

querying all data tables in the database
SHOW TABLES;

"Data Type"

First, character type:
①char (N): Fixed N-length string, if the length is not enough, automatic space is padded. Range of N (0-255)
②varchar (): Stores variable-length strings, the most commonly used string type. 0-2^16-1 *10;
③text: A variable-length string can be stored. (often used to publish articles and other large sections of content) 0-2^16-1 *10^2
④tinytext:0 ~ 2^8-1 *10
⑤mediumtext:0 ~ 2^24-1 *10^3
⑥longtext:0 ~ 2^32-1 *10^4
⑦enum ("Male", "female"): Enum type, field can only hold the type enumerated.

second, the integral type:
①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^32 ~ 2^32-1
⑤bigint: Unsigned 0~2^64-1 signed -2^63 ~ 2^63-1

third, floating-point type:
①float: Accurate to 7 digits after the decimal point
②double: A valid number 15~16 digits after the decimal point.
 
Iv. Date-time data type
Note: Because the time store uses a string or timestamp store, there are almost different date types in the database.
①date: Storing date and time data
②timestamp: More accurate than date.

"Three major paradigms of database design"

1. First paradigm (1NF): Each column (field) in a data table must be the smallest unit that cannot be split. Other words
To ensure the atomicity of each column.

2. Second Normal form (2NF): After 1NF is satisfied, all columns in the table must be dependent on the primary key, not any one column and primary key
It does not matter. In other words, a table describes only one thing.

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

3. Third paradigm (3NF): After 2NF is satisfied, the requirement: Each column in the table is directly related to the primary key, not indirectly.
(each column in the table is dependent on the primary key only)

For example: In the Order form, customer-related information is required, after the Customer table is detached.
In the order form, only one user ID is required. and cannot have other customer information. Because, the other
User information is directly associated with the user ID, not the associated with the order ID.

"The essential difference between the second paradigm and the third paradigm "
Is whether there are two tables, and the second paradigm is that a table contains the attributes of many different entities,
Then it has to be divided into multiple tables. The third paradigm requires that multiple tables have been divided, so there can only be another one in a single table.
The ID (primary key) in the table, and no other information (any other information, is queried in the other table with the primary key).

Use MyDB;

--Create a table:
--IF not EXISTS can be omitted, and the error will be created repeatedly after omitting.

--Definition column: Column name data type column definition keyword
--Common column definition keywords:
--UNSIGNED: Set column as unsigned. Unsigned can only restrict columns of type number type.
--Auto_increment: Sets the column for automatic growth.
--automatic growth must be the PRIMARY key!! The primary key is not necessarily auto-grow, but it must be unique

/* " primary key "
1. Primary KEY considerations: The primary key is not empty by default! PRIMARY KEY default Uniqueness constraint!
Auto-grow only with primary key (primary key not necessarily self-increment, self-increment must be primary key)

2. How do I set the primary key?
① at the time of column definition: ID int PRIMARY KEY
② set after the column definition is complete: PRIMARY KEY (ID)

PRIMARY Key: Sets the PRIMARY KEY constraint.
Not_null: Sets a non-null constraint.
Unique: Sets a uniqueness constraint, which cannot have duplicate values. The unique can be any field.
default: Defaults constraint. Heighe DOUBLE (3,2) default 1.2 Heigh if not entered defaults to 1.2
FOREIGN key: Setting foreign KEY constraints

" foreign key "
1. Considerations for setting foreign keys:
① only the InnoDB database engine supports foreign keys
Modify the My.ini file Settings Default-storge-engin=innodb
the ② plus data type must be the same as the reference column.
(The numeric type requires the same length and unsigned, the string requires the same type, and the length can be different.) )
③ The field that sets the foreign key must have an index. If there is no index, the index is automatically generated when the foreign key is set.

2. Set the syntax for the foreign key:
[CONSTRAINT foreign Key name] FOREIGN Key (foreign key field) REFERENCES reference table (reference field)

3. Referential actions for FOREIGN KEY constraints:
Referential actions: How foreign keys in the foreign key table should be addressed when deleting or updating reference fields for reference tables.
Reference operation Optional Value: RESTRICT reject the reference table to delete or update the Reference field. Default
No action is the same as RESTRICT, but this command only takes effect in MySQL
CASCADE Delete or update a reference field for a reference table, the record for the foreign key table is synchronized to delete the update
Set NULL when deleting or updating a reference field for a reference table, the foreign key of the foreign key table is set to NULL

*/

DROP TABLE IF EXISTS tbl;

--if not EXISTS when the table is created, the table is confirmed to exist, and there is no

id INT (1) UNSIGNED PRIMARY KEY auto_increment,
' name ' VARCHAR (255) not NULL,--name is a system keyword, so use the anti-quote wrapping
age TINYINT (3),
height DOUBLE (3,2) DEFAULT 1.2,
clsid INT UNSIGNED,
constraint tbl_fk_classes FOREIGN KEY (CLSID) REFERENCES

DROP TABLE IF EXISTS ' CLS ';
CREATE TABLE IF not EXISTS CLS (
ID INT UNSIGNED PRIMARY KEY auto_increment,
ClassName VARCHAR (255) Not NULL
);

--SHOW COLUMNS from CLS;

DROP TABLE IF EXISTS ' user ';

CREATE TABLE IF not EXISTS ' user ' (
ID INT UNSIGNED PRIMARY KEY auto_increment,
ClsId INT UNSIGNED,
' Name ' varchar (255) is not NULL,
CONSTRAINT AAA FOREIGN KEY (clsId) REFERENCES CLS (ID) on DELETE SET NULL on UPDATE CASCADE
);

SHOW TABLES;
--Display table structure COLUMNS
SHOW COLUMNS from TBL;

--Display the table statement
SHOW CREATE TABLE tbl;

--Delete the table (delete if present)
DROP TABLE IF EXISTS tbl;

--Modify Table name
--1.alter table name RENAME [to] new table name;
ALTER TABLE tbl RENAME tbl2;
--2. Modify multiple tables at the same time: Rename table name to new table name [, another old table name to another new table name];
RENAME TABLE tbl2 to TBL1;

--Modify field columns
--ALTER table name change old column name new column list definition [First | After a column column name]
--First the field is adjusted to the table one column, after which a column is placed behind a column.
ALTER TABLE tbl Change ' name ' ' username ' VARCHAR ($) not NULL after age
--Modify only modifies column definitions, cannot modify column names
ALTER TABLE tbl MODIFY ' name ' VARCHAR ($) not NULL after age

--Delete a column in a table
ALTER TABLE tbl DROP Height

--Add a column to a table
ALTER TABLE tbl ADD height DOUBLE (3,2) DEFAULT 1.2 after age
--Add multiple columns to the table, cannot adjust the position of the column, can only be inserted in the last.
ALTER TABLE tbl ADD (
Weight DOUBLE (3,2) UNSIGNED,
School VARCHAR (255)
);

--Increase the PRIMARY KEY constraint
ALTER TABLE tbl ADD PRIMARY KEY (age)
--Delete primary KEY constraint
ALTER TABLE tbl DROP PRIMARY KEY

--New Uniqueness Constraint
ALTER TABLE tbl ADD UNIQUE KEY (username)
--Delete Uniqueness constraint: Because creating a uniqueness constraint creates an index by default, you delete the index when you delete it.
ALTER TABLE tbl DROP INDEX username

--Set Default value constraint
Alter TABLE TBL ALTER age SET DEFAULT 20;
--Delete default value constraint
Alter TABLE TBL ALTER age DROP DEFAULT;

--Set FOREIGN KEY constraint (delete foreign key by foreign key name)
ALTER TABLE tbl Add constraint waijianming foreign key (CLSID)
REFERENCES CLS (id) on delete set NULL update cascade;

--Delete the foreign KEY constraint. Because the index is created by default when you create a foreign key, you need to remove the index after you delete the foreign key
ALTER TABLE tbl DROP FOREIGN KEY aaa;
ALTER TABLE tbl DROP INDEX AA

MySQL Initial learning