Database details Getting Started Tutorial

Create user: CREATE user ' username ' "@ ' hostname '" identified by ' password ' hostname can be empty, default to NULL is% permission, indicating all hosts can connect

' jredu '@'localhost'jredu';

Grant to assign permissions to the user; Grant permission name on database name table name to user @ host All indicates that all permissions *. * represents all tables in all dataGRANT all on * * to ' jredu ' @ ' localhost ';

' jredu '@'localhost';

REVOKE from Delete user rights

' jredu '@'localhost';

CREATE DATABASE [IF not EXISTS] database name [CHARACTER SET [=] ' UTF8 ']; If you omit the If not EXISTS error when you create the databaseCREATE DATABASE IF not EXISTS myDB3 CHARACTER SET ' UTF8 '; Delete the database (IF EXISTS) database name;

DROP DATABASE IF EXISTS mydb2;

query all the databases in this computer SHOW database;

SHOW DATABASE;

Use the MyDB database to represent the following query, which will default to the MyDB database

Use MyDB;

querying all data tables in the database SHOW TABLES [from database]

SHOW TABLES from MySQL;

data types commonly used in MySQL First, character type:①CHAR (n): fixed n characters length string, if the length is not enough, will automatically be padded with n return 0~255;②VARCHAR (): Stores variable-length strings, most commonly used. 0~65535 * 10;③TEXT: can store variable-length strings, often used to publish articles such as 0~65535*10^2;④tinytext:0~ (2^8-1) *10; ⑤Mediumtext:0~2^24-1 * 10^3;⑥longtext:0~2^32-1 * 10^4; second, 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; third, floating-point type①FLOAT: can be exactly 7 digits after the decimal point②DOUBLE: can be accurate to 15 to 16 digits after the decimal point number Iv. Date-time data type because the time store uses a string or timestamp store, there is little data type in the database①Date and time data stored②TIMESTAMP: more accurate than DATE the three main paradigms of database design; 1. The first paradigm1NF: Each column in the data table must be the smallest unit that cannot be split, that is, to ensure the atomicity of each column such as UserInfo: ' Shandong province Yantai 13181602111 '; userads: ' Shandong Yantai ' Usertel: ' 13181602111 ' 2. The second paradigm2NF: When 1NF is satisfied, all columns in the table must be dependent on the primary key, and no one column has no relation to the primary key. That is, a table only describes one thing. For example: Order form: Only the information related to the order can be described, so all 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, the product and order information cannot exist at the same time 3. The third Paradigm3NF: Satisfies 2NF, requires that each column in the table is directly related to the primary key, not indirectly, and that each column in the table can depend on the primary key only. For example: In the order form, you need to have customer-related information, after separating out the Customer table, in the order table, only need to have a customer ID, and no other customer information. Because, other user information is directly associated with the user ID rather than the order ID associated with the [The essential difference between the second paradigm and the third paradigm]Is that there is no two tables, the second paradigm is that if a table contains a number of different entities of the attributes, then must be divided into multiple tables The third paradigm requires that multiple tables have been divided, then, a table can only have another table ID (primary key) and can not have any other information, (any other information, Use primary key to query in another table) Create a tableIf not EXISTS can be omitted, omitted after repeated creation of an error, if not omitted, is created to detect whether the table already exists, if the table already exists, no longer execute the CREATE statement define column Column name data type column definition keywords Common column Definition keywordsUNSIGNED settings are listed as unsigned columns, only columns of type number type auto_increment: Set as auto-Grow column, auto-grow column must be primary key [PRIMARY KEY] 1, the primary key considerationsPrimary key default non-NULL, PRIMARY key default uniqueness constraint, only primary key to set autogrow (primary key not necessarily self-increment, self-increment must be primary key) 2. How to set the primary key①Set when the column is defined, ID INT UNSIGNED PRIMARY KEY,②Set PRIMARY key (ID) PRIMARY key (ID) when the column definition is complete: Set PRIMARY KEY constraint NOT NULL: Set column non-null the field cannot be null unique: Set a Uniqueness constraint, which cannot have duplicate values the default 1.2 setting defaults constraint, Height DOUBLE (3,2) default 1.2 height if not entered, the FOREIGN KEY constraint is set to 1.2FOREIGN key [foreign Key] 1. What are the precautions for setting foreign keys? ①Only the INNODB database engine supports foreign key modification My.ini file Settings Default-storage-engine=innodb②The data type of the foreign key and the reference column must be the same, the numeric type requires the same length as the unsigned, the string requires the same type, and the length can be different③The field that sets the foreign key must have an index, and if there is no index, an index is automatically generated when the foreign key is set 2. Setting 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 referential integrity 3. Referential integrity operation for FOREIGN KEY constraintsReference action when you delete or update a reference field in a reference table, the foreign key in the foreign key table should handle the reference action optional value RESTRICT deny reference table delete or update reference field (default) NO ACTION is the same as RESTRICT, but this instruction only takes effect in MySQL Cascade When you delete or update a reference field for a reference table, the foreign key table's foreign keys are set to NULL when you delete the record synchronization for an update set NULL to delete or update a reference field for a reference table Show Table Structure

SHOW COLUMNS from table1;

to display the table's Table statement

SHOW CREATE TABLE table1;

Delete a table

DROP TABLE IF EXISTS table1;

Modify table name ALTER TABLE old table name RENAME [to] new table name;

ALTER TABLE table1 RENAME to table2;

at the same time modify multiple tables RENAME table table2 to table1 ", ' user ' to User1 ...";

RENAME TABLE table2 to table1, ' user ' to User1;

Modify Field Columns ALTER table name change old column name new column list definition [First | After a column]; First, this field is adjusted to a column of table degrees, after which a column is placed after a column

ALTER TABLE table1 change ' name ' username ' VARCHAR ($) not NULL after age;

MODIFY only modify column definitions and cannot rename

ALTER TABLE table1 MODIFY ' name ' username ' VARCHAR ($) not NULL after age;

Delete a column in a table

ALTER TABLE table1 DROP height;

added a required section ALTER TABLE table1 ADD height DOUBLE (8,2)

ALTER TABLE table1 ADD height DOUBLE (8,21.2 after age;

Add multiple columns cannot adjust the position of the column, can only be inserted in the last

ALTER TABLE table1 ADD (weight DOUBLE (3,2) unsigned,school VARCHAR (255 ));

increase the PRIMARY KEY constraint

ALTER TABLE table1 ADD PRIMARY KEY (ID);

Delete a primary KEY constraint

ALTER TABLE table1 DROP PRIMARY KEY;

New Uniqueness Constraint

ALTER TABLE table1 UNIQUE KEY (username);

Remove Uniqueness constraint The index is created by default when creating a Uniqueness constraint, so delete the index when you delete it

ALTER TABLE table1 DROP INDEX username;

Set default value Constraints

;

Delete a default value constraint

Alter TABLE table1 alter age DROP DEFAULT;

set FOREIGN KEY constraint mandatory section ALTER TABLE table1 ADD CONSTRAINT waijianming FOREIGN KEY (CLSID) REFERENCES classes (ID)

ALTER TABLE table1 ADD CONSTRAINT waijianming FOREIGN KEY (CLSID) REFERENCES classes (ID) on DELETE SET NULL on UPDATE CASC ADE;

The foreign KEY constraint is deleted, because the index is created by default when the foreign key is created, so after you delete the foreign key, you need to delete the index

ALTER TABLE table1 DROP FOREIGN KEY table1_fk_classes;

ALTER TABLE table1 DROP INDEX table1_fk_classes;

ALTER TABLE table1 DROP INDEX table1_fk_classes;

Database details Getting Started Tutorial