MySQL database addition, deletion, modification, cloning, foreign keys and other operations

catalogue

1. SQL field data type

2. View database information statement

3. SQL statement

  • Create and delete database data tables
  • Add, delete and query records to the table
  • Modify the table name, add, modify, delete fields, and add unique constraints
  • View, delete and add indexes in tables
  • Extension function, field value self increment, etc

4. Data table advanced operations

  • Clone the table and generate the data records of the data table into a new table
  • After deleting a record, the header of the primary key record starts to increase automatically
  • Create temporary table

5. Create foreign key constraints to ensure data integrity and consistency

6. Six MySQL constraints

There are data tables in the database, and there are records one by one in the data table.

You can use remote connection tools such as Navicat to link the database, but the database needs to be authorized.

SQL field data type

  • int: integer. The default length is 11
  • float: single precision floating point, 4 bytes, 32 bits
  • Double: double precision floating point, 8 bytes, 64 bits
  • char: a character type with immutable length. It is fast to read and consumes space. Spaces will be filled if the length is insufficient.
  • varchar: variable length character type, but the read data is lower than char, which is prone to memory fragmentation
  • text: text
  • image: picture
  • decimal(6,2): defines 6 effective length digits with two digits after the decimal point

char supports 255 characters at most. If the actual length of the stored data is smaller than the specified length, a space will be filled to the specified length; If the actual length of the stored data is greater than the specified length, the first 255 characters of the lower version of Mysql will be intercepted, and the higher version will report an error.

Note: varchar type: under version 4.0, varchar(20) refers to 20 bytes; Above 5.0, varchar(20) refers to 20 characters.

View database information statement

touchdown MySQL，It can also be used SQL Tools such as Navicat Remote connection, provided that Linux Mysql of use grant Authorize to allow remote login.

#The root user is authorized to log in remotely at all terminals with the password of 123456
grant all privileges on *.* to 'root'@'%' identified by '123456';

#Operation permission for all databases and tables with grant option
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

Navicat connection parameters

In order to practice operating in Linux, the following are directly typed in the Linux command box.

Linux Login in Mysql
mysql -u root -p password

1.View databases in the current server  
SHOW DATABASES;

2.View the tables contained in the database
USE Database name    #Note: database names are case sensitive
SHOW TABLES;

3.View the structure of the table (design, field)
DESCRIBE [Database name.]Table name
 or
DESC Table name

4\. View commands for creating tables
SHOW CREATE TABLE Table name

5.Query result row to column view \G
SELECT * FROM USER_INFO\G;

6\. View the current database
select database()

SQL statement

SQL statements are used to maintain and manage the database, including data query, access control, data update, object management, backup and other functions.

Create and delete database data tables

create new database

CREATE DATABASE Database name;

For example: CREATE DATABASE SCHOOL;

Create a new table in the database

CREATE TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

#Primary keys generally select fields that can represent uniqueness. NULL values are not allowed. A table can only have one primary key.

Example: USE SCHOOL;
CREATE TABLE STUDENT (ID int(10) NOT NULL,NAME varchar(20),AGE int(3),SEX char(2),PRIMARY KEY (ID));

Delete table

DROP TABLE [Database name.]Table name;				#If you do not USE to enter the database, you need to add the database name

Delete database

DROP DATABASE Database name;   #The table in the library will be deleted together

Be careful when deleting. Remember to be backed up before deleting

Add, delete and query records to the table

#First create the STARBUCKS database, and then create USER_INFO table, add some fields

CREATE DATABASE STARBUCKS;
USE STARBUCKS;

DROP TABLE IF EXISTS `USER_INFO`;
CREATE TABLE `USER_INFO`  (
  `ID` int(32) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `TYPE` varchar(32) DEFAULT NULL,
  `ADD_TIME` varchar(20) DEFAULT NULL,
  `IS_USED` char(5) DEFAULT NULL,
  PRIMARY KEY (`ID`) 
)

Add record to table

INSERT INTO Table name(Field 1,Field 2[,...]) VALUES(Value of field 1,Value of field 2,...);

Example:
#Add a full field one by one
INSERT INTO USER_INFO(ID,NAME,TYPE,ADD_TIME,IS_USED) VALUES (1001, 'Coffee machine', 'The machine is complete', '2021-06-22', 'D0001');
INSERT INTO USER_INFO VALUES (1002, 'Milk Tea machine', 'equipment', '2021-06-23', 'D0001');

#Or add multiple with one command
INSERT INTO USER_INFO (ID,NAME,TYPE,ADD_TIME,IS_USED) VALUES (1003, 'milk', 'food', '2021-06-24', 'D0002'),
(1004, 'packer', 'equipment', '2021-06-20', 'D0001'),
(1005, 'Candy bar', 'food', '2021-06-25', 'D0001'),
(1006, 'straw', 'equipment', '2021-06-20', 'D0001'),
(1007, 'cream', 'food', '2021-06-25', 'D0002');

SELECT query statement

SELECT Field name 1,Field name 2[,...] FROM Table name [WHERE Conditional expression];

#How many lines from 0
SELECT * FROM Table name limit Number of rows 

#Retrieve the number of records according to the line number range
SELECT * FROM Table name limit Number of start lines (excluding),The total number of lines starting from the number of starting lines

UPDATE update: pay attention to the conditions of WHERE plus UPDATE, otherwise all will be updated

UPDATE Table name SET Field name 1=Field value 1[,Field name 2=Field value 2] [WHERE Conditional expression];

as UPDATE USER_INFO SET TYPE='instrument' WHERE ID=1001;

DELETE delete records. Note WHERE to add the conditions for deleting records, otherwise all records will be deleted

DELETE FROM Table name [WHERE Conditional expression];

Example: delete from class2 where id=4;

Modify the table name, add, modify, delete fields, and add unique constraints

Modify table name

ALTER TABLE Old table name RENAME New table name;

Example: ALTER TABLE USER_INFO RENAME USER_MESG;
SHOW TABLES;
1234

Add field to table

ALTER TABLE Table name ADD Field name varchar(50) default 'Default value';
#Default indicates that the default value is set for this field; It can be used with NOT NULL, and the default value takes effect immediately

Example:
ALTER TABLE USER_MESG ADD STATUS VARCHAR(20) DEFAULT 'In use';

Delete fields in table

ALTER TABLE Table name DROP Field name;

Modify the field (column) name and add a unique key constraint

ALTER TABLE Table name CHANGE Old column name new column name data type [unique key];

Example:
ALTER TABLE USER_MESG CHANGE NAME DEV_NAME VARCHAR(30) UNIQUE KEY;

#Verify the unique key constraint and add the constraint field with the same name
INSERT INTO USER_MESG VALUES (1008,'milk','Drinks','2021-06-28','D0001','Expired ');
#Trigger unique key constraint, failed to add

UNIQUE KEY Unique key: you can use a null value, no duplicate value, and no null value NULL，

View, delete and add indexes in tables

#See which indexes the table has
 SHOW INDEXES FROM Table name;

Add and remove unique key constraints separately

#Give field DEV_NAM adds unique key constraint
ALTER TABLE USER_MESG ADD UNIQUE INDEX (DEV_NAME);

#Give field DEV_NAM removes the unique key constraint
#View the field dev in the index table_ Index name key corresponding to name_ Name, delete the index according to the index name

ALTER TABLE USER_MESG DROP INDEX Index name;

Extension function, field value self increment, etc

use school;
CREATE TABLE IF NOT EXISTS AREA_INFO (                                                                                                         -> AREA_ID int(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT,    #Another way to specify a primary key
-> AREA_NAME VARCHAR(20) NOT NULL UNIQUE KEY,
->  IS_USED CHAR(5) NOT NULL);

#---------------Command interpretation--------------------------------
#if not exists: indicates whether the table to be created already exists. If it does not exist, continue to create it
#int(4) zerofill: if the value is less than 4 digits, fill it with "0" in front, for example 0001
#auto_increment: indicates that this field is a self increasing field, that is, each record is automatically incremented by 1, which starts from 1 by default; Self growth field data cannot be duplicate;
#The self growing field must be a primary key; The field type must be int. if the added record data does not specify the value of this field and the addition fails, it will be automatically incremented once
#Unique key: indicates the unique key constraint of this field, and the data in this field cannot be repeated; There can only be one primary key in a table, but there can be multiple unique keys in a table
#not null: indicates that this field is not allowed to be NULL

Clone the table and generate the data records of the data table into a new table

Method 1: use like

#Clone table structure first
#Insert all data in
CREATE TABLE AREA_CLONE1 LIKE AREA_INFO;     #Copy area through LIKE method_ Info table structure generate AREA_CLONE1 table
INSERT INTO AREA_CLONE1 SELECT * FROM AREA_INFO;


If two tables have the same structure, the query result set of one table can be returned to another table
INSERT INTO Table 1 SELECT * FROM Table 2

Method 2: direct cloning

#Directly the query results of the source table to the created new table

CREATE TABLE Clone table name(SELECT * FROM Source table name);

SHOW CREATE TABLE Clone table name\G;					#Get the table structure, index and other information of the data table

Note: direct cloning cannot copy the primary key, automatic growth, constraint, index and other configurations of the original table. You can use LIKE.

After deleting a record, the header of the primary key record starts to increase automatically

DELETE FROM Table name;
#After DELETE clears the table, the returned result contains deleted record entries; DELETE deletes record data line by line; If there is a self increment field in the table, after deleting all records using DELETE FROM, the newly added record will continue to be self increment written to the record from the original maximum record ID.

truncate table Table name;
#TRUNCATE does not return deleted entries after clearing the table; TRUNCATE rebuilds the table structure as it is, so TRUNCATE is faster than DELETE to clear the table; After using TRUNCATE TABLE to clear the data in the table, the ID will be re recorded from 1.
#It is equivalent to directly formatting the table

Create temporary table

A temporary table is a table in effect in the current connection.

  • After the temporary table is created successfully, the temporary table created cannot be seen by using the SHOW TABLES command;
  • The temporary table will be destroyed after the connection exits. Before exiting the connection, you can also perform operations such as adding, deleting, modifying, and querying. For example, use the DROP TABLE statement to manually and directly delete the temporary table.
CREATE TEMPORARY TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

Example:
CREATE TEMPORARY TABLE TEM_TABLE (ID int(4) PRIMARY KEY, REMARK VARCHAR(20));

INSERT INTO TEM_TABLE VALUES(1001,'cursor');
SELECT * from TEM_TABLE;


implement quit After exiting the connection and logging in again, you can't operate the temporary table

Create foreign key constraints to ensure data integrity and consistency

Definition of foreign key: if the same attribute field X If it is a primary key in Table 1 but not in Table 2, the field X Foreign keys called table 2.

Understanding of primary key table and foreign key table:
(1)Tables with public keywords as primary keys are primary key tables (parent tables and primary tables)
(2)Tables with public keywords as foreign keys are foreign key tables (from table and appearance)

be careful:
1\. The fields of the primary table associated with the foreign key must be set as the primary key.
2\. It is required that the slave table cannot be a temporary table, and the fields of the master-slave table have the same data type, character length and constraints.


#Create main table HOBBY 
CREATE TABLE HOBBY (HOBID int(4),HOBNAME VARCHAR(50));

#Create hobby from table_ DETAIL 
CREATE TABLE HOBBY_DETAIL (ID int(4) PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),AGE int(3),HOBID int(4));

#Add a primary key constraint to the primary table HOBBY. It is recommended to use "PK_" start.
ALTER TABLE HOBBY ADD CONSTRAINT PK_HOBID PRIMARY KEY (HOBID);

#For HOBBY from table_ Add a foreign key to the detail table and set HOBBY_ A foreign key association is established between the hobbid field of the detail table and the HOBBY table.
#"FK_" is recommended for foreign key names start.
ALTER TABLE HOBBY_DETAIL ADD CONSTRAINT FK_HOB FOREIGN KEY (HOBID) REFERENCES HOBBY (HOBID);

DESC HOBBY_DETAIL ;

#When inserting a new data record, you must first the master table and then the slave table
INSERT INTO HOBBY VALUES(1,'Football');
INSERT INTO HOBBY_DETAIL VALUES(1,'Messi',32,1);



#When deleting data records, you must first delete the primary table from the primary table, that is, when deleting the primary key table, you must first delete other tables associated with it.
DROP TABLES HOBBY_DETAIL ;
DROP TABLES HOBBY ;

#Viewing and deleting foreign key constraints
SHOW CREATE TABLE HOBBY_DETAIL ;
ALTER TABLES HOBBY_DETAIL DROP foreign key FK_HOB ;  #FK_HOB is the foreign key name added earlier
ALTER TABLES HOBBY_DETAIL DROP key FK_HOB ;

DESC HOBBY_DETAIL ;

After removing the foreign key, you can delete the table

DROP TABLES HOBBY_DETAIL ;

DROP TABLES HOBBY ;

Six MySQL constraints

  • primary key constraint
  • foreign key constraint
  • Non NULL constraint (not null)
  • Uniqueness constraint (unique [key|index])
  • Default value constraint (default)
  • Self increasing constraint (auto)_ increment)

Meaning of key column value:

  • If the key is PRI, the column is one of the primary key or multi column primary keys.
  • If the key is UNI, the column is the first column of the unique index( The unique index allows multiple NULL values, but you can check the NULL field to determine whether the column allows NULL.)
  • If the key is MUL, the column is the first column of the non unique index, where the given value is allowed to appear more than once in the column.

Keywords: Database MySQL Big Data

Added by Pyro4816 on Mon, 06 Sep 2021 02:22:08 +0300

Popular Keywords

©2024 Programming VIP