MySQL Study ( One )— The concept of databases / Basic operation command / Addition, deletion and modification of data table / Foreign keys

01 Concept

Database related concepts

• DB：DataBase database （ A data “ Warehouse ”, Ability to save and safely manage data . Data organized , Can share .）
  • SQL ： Relational database （ Relational database establishes the relationship between tables through foreign key Association ）
  • NOSQL ：Not Only SQL Non relational database （ Non relational databases usually refer to data stored in the database as objects , The relationship between objects is determined by the properties of each object ）
• DMMS : DataBase Management System Database management system （ Scientific organization and storage of data , Get and maintain data efficiently ）
• MySQL It’s a popular open source , Free relational database （ The Swedish MySQL AB Companies to develop , After being SUN Company purchase , Now belongs to ORACLE company ）
• SQLyog ： It can be operated manually , management MySQL A graphical software tool for databases
  • SQLyog Use ：
    • Connect local MySQL database （ open SQLyog–> file —> new connection —> newly build —> Fill in connection information –> Test connection –> Connect after the test is successful ）
    • New database （root@localhost Right click to create a database –> Right click the database to create a new database table –> Add fields ）
    • The corresponding database operation statements can be seen in the history records
    • In the query window, you can create a database by operating statements
• MySQL The types of datasheets are MyISAM , InnoDB, HEAP , BOB , CSV etc. , The common one is MyISAM , InnoDB.
  • MyISAM: Transaction not supported , Data row locking , Foreign key constraints . Full text index support , The table space is small .
  • InnoDB： Support transaction processing , Data row locking , Foreign key constraints . Full text indexing is not supported , The table space is large , about MyISAM Twice as many .

02 Basic operation command

• matters needing attention ：1. Case insensitive （ The database collation is set to utf8_general_ci Proofreading is not case sensitive ）

​ **2. Statement to ; ending **

​ 3. Database or table names are usually in back quotation marks “ extraction , Used to distinguish keywords in a database

• Basic operation command ( Command line )

mysql -uroot -p******;// land MySQL -u After the user name , Here we use root For example ****** For the user password
show databases;// View all databases
use database_name;// Select database
SELECT * FROM database_name.table_name;// Query the form information under the database
show tables;// Display database mysql All the tables in
flush privileges;// Refresh database
drop database database_name;// Delete database
exit;// sign out MySQL

• Create database operation command （SQLyog Under the tool ）

# Enter... In the inquiry window （MySQL With notes # Statement ）
# Create database
CREATE DATABASE `database_name`;
# Create table
CREATE TABLE IF NOT EXISTS `table_name`（
# Create column fields
` Field name 1` Column type Data field properties COMMENT ' Field 1 Notes ',
......
` Field name n` Column type Data field properties COMMENT ' Field n Notes ',
）ENGINE=InnoDB DEFAULT CHARSET=utf8;

• Common mistakes ：
  • create form filed use “ extraction , Note that the symbol here is not a single quotation mark , It is tab The symbol above the key .
  • default、comment And so on. Use single quotation marks .

03 The addition, deletion and modification of the database

• Column type ： Specify the data type of the column in the database
  • Common numerical types ：tinyint（ Very small data ,1 byte ）int( Standard integers ,4 byte ) double（ Double precision floating point ,8 byte ）
  • Common string types ：char（ Fixed string , Search fast , Fee space ）varchar（ Variable string ）text（ Text string ）
  • Common date and time data types ：DATETIME(YY-MM-DD hh:mm:ss)
  • NULL value ： No value （ Do not use NULL Perform arithmetic operations , The result is still the NULL）
• Data field properties
  • UnSigned: Declare that the data column does not allow negative numbers
  • ZEROFILL: A statement of insufficient digits 0 Fill in , Such as INT（5）,5 Then for 00005
  • Auto_InCrement： Declare that the column value is automatically growing , Every time you add a piece of data , Automatically add to the previous record number by default 1
  • NULL and NOT NULL :
  • The default is NULL , That is, the value of the column is not inserted
  • If set to NOT NULL , Then the column must have a value of
  • DEFAULT : Declare the default value , Such as , Gender field , The default is ” male ” , Otherwise “ Woman ” ; If the value of this column is not specified , The default value is ” male ” Value

• Add data

#INSERT INTO command
INSERT INTO Table_name( Field 1, Field 2, Field 3,...) VALUES(' value 1',' value 2',' value 3')
# When adding a row of data ' Field 1, Field 2...' This part can be omitted , But the added value must be consistent with the table structure , Data columns , The order corresponds to , And the quantity is the same .
# Example
# Single data insertion , The primary key does not automatically increase
INSERT INTO height(`studentNo`,`height`) VALUES (1000,178);
# You can omit the field name
INSERT INTO height VALUES (1000,178);
# Insert multiple data , The primary key does not automatically increase
INSERT INTO height(`studentNo`,`height`) VALUES (1001,188),(1002,170),(1003,178),(1004,158);
# Single data insertion , The primary key increases automatically
INSERT INTO height (`tall`) VALUES (178);
# Insert multiple data , The primary key increases automatically
INSERT INTO height (`tall`) VALUES (178),(180),(168),(190);

• Delete data table

# DROP TABLE [IF EXISTS] Table name
# IF EXISTS Determine whether the data table exists , If you delete a table that doesn't exist, it throws an error
# DELETE FROM tabel_name WHERE condition;
# condition Filter by , Delete all column data of the table if not specified
# TRUNCATE [TABLE] table_name;
# Used to completely empty the data table , But the table structure , Indexes , Constraints, etc., remain unchanged
# give an example
# Use delete Delete height In the table studentNo=1004 The data of , among studentNo The attribute is auto increment
DELETE FROM height WHERE studentNo=1004;
# After deleting , Use INSERT Add data
INSERT INTO height(`height`) VALUES (178);
# Execution results ： Of the data added studentNo No more 1004, It is 1005
# Conclusion ： Use delete Delete data , The current value of auto increment is still based on the original value , Will log .
# Use truncate Delete height Data sheet
TRUNCATE TABLE height;
# After deleting , Use INSERT Add data
INSERT INTO height(`height`) VALUES (178);
# Execution results ： Of the data added studentNo No more 1004, It is 1
# Conclusion ： Use truncate Delete data , The auto increment current value will return to the original value and start again ; No logging .

• Modify data sheet

# Modify the name of the table :ALTER TABLE The old name of the table RENAME AS The new name of the table
# Add fields :ALTER TABLE Table name ADD Field name Column properties [ attribute ]
# Modify fields :ALTER TABLE Table name MODIFY Field name Column type [ attribute ] （ It is applicable to the case that the column name is not changed but the column property is changed ）
# ALTER TABLE Table name CHANGE Old field name new field name Column properties [ attribute ]
# Delete field :ALTER TABLE Table name DROP Field name
# Example
# Modify column fields studenNo Properties of , Do not modify column field names
ALTER TABLE height MODIFY `studentNo` INT(4) NOT NULL AUTO_INCREMENT COMMENT ' Student number ';
# Modify column fields studenNo Properties of , Modify column field name
ALTER TABLE height CHANGE `height` `tall` INT(3) NOT NULL COMMENT ' Student number ';
# Modify a column value of a row in a data table
# UPDATE table_name SET column_name=value [,column_name2=value2,...] WHERE condition;
# summary ：column_name For the data column to change ,value Is the modified data , It can be a variable , Specifically, an expression or nested SELECT result .condition Filter by , Modify all column data of the table if not specified .
# Example
# modify studentNo=1002 The height of the students
UPDATE height SET `height`=190 WHERE studentNo=1002;

04 Foreign keys

• Concept ： Each associated table is associated with a common keyword . If a keyword is in a public relation table PRIMARY KEY, The primary key , Then this public key is called the foreign key in the relationship of another table (foreign key). A table with this foreign key as the primary key is called the primary table , The table with this foreign key is called the slave table of the master table .
• effect ： Keep the data Uniformity , integrity , The main purpose is to control the data stored in the foreign key table , constraint . Associate two tables , Foreign key can only refer to the value of the column in the appearance or use null value .
• Foreign key creation

# How to create a foreign key ： Create a foreign key while creating a slave table
# Example
# First create the main table to be associated with the foreign key
CREATE TABLE `grade` (
`gradeid` INT(2) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ' grade id',
`grade` varchar(20) NOT NULL COMMENT ' grade '
) ENGINE=INNODB DEFAULT CHARSET=utf8;
# Then create the slave table
CREATE TABLE `student` (
`studentNo` INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ' Student number '
`studentname` VARCHAR(20) NOT NULL DEFAULT ' anonymous ' COMMENT ' full name ',
`sex` TINYINT(1) DEFAULT '1' COMMENT ' Gender ',
`gradeid` INT(10) DEFAULT NULL COMMENT ' grade ',
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
# How to create a foreign key ： After the data table is created , Modify subordinate table properties , Add foreign keys , Associate the main table
ALTER TABLE student
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
# After associating foreign keys , Then we can make a joint query
SELECT a.StudentNo,a.StudentName,b.grade
FROM student AS a,grade AS b
WHERE a.gradeid=b.gradeid;

• Foreign key deletion

# When deleting a table with a primary foreign key relationship , First delete from the table , After deleting the main table
# Example
ALTER TABLE student DROP FOREIGN KEY FK_studentNo;
ALTER TABLE student DROP INDEX FK_gradeid;