Database and data table | MySQL (a) basis

“This is the 11th day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

Related articles

MySQL: indicates the MySQL series

Preface:

MySQL is a Relational Database Management System, is currently one of the best RDBMS (Relational Database Management System) applications.

• Advantages:
  • ① Open source database software, the key is free!
  • ② Small size, fast speed, low total cost of ownership, low cost of recruitment.
  • (3) The general development of small and medium-sized websites choose MySQL as a website database.

1. Database

1.1 Basic Operations

1. Create a database

CREATE DATABASE IF NOT EXISTS testDataBase;
Copy the code

2. Delete the database

DROP DATABASE IF EXISTS testDataBase;
Copy the code

3. Use a database

USE school;
Copy the code

4. View the database

SHOW DATABASE;
Copy the code

1.2 Column types of the database

(1) value:

Tinyint Very small data 1 byte SmallInt Small data 2 bytes MediumInt Medium size 3 bytes int Standard integer 4 bytes (common) BigINT Large data 8 bytesfloatDecimal A floating point number in the form of a string; used in financial calculationsCopy the code

② String:

Char string fixed size 0-255 varchar variable string 0-65535 tinytext tinytext 2^8-1 text text string 2^16-1 (save large text)Copy the code

③ Time and date:

Date YYYY-MM-DD, datetime HH: MM: SS Time format DateTime YYYY-MM-DD HH: MM :ss Most commonly used time format Timestamp 1970.1.1 Number of milliseconds since date Year Indicates the yearCopy the code

(4) null:

No value, unknown note, do not use null for operation, result is NULLCopy the code

1.3 Database Field types

1) unsigened:

Unsigned integer declaration The column cannot declare negative numbersCopy the code

(2) zerofill:

The length of 10 filled with 0 is 1 to 0000000001. If the number of digits is less than 0, fill it with 0Copy the code

(3) on the:

+1 is usually used to design a unique primary key index, which must be an integer similar to the start value and step of the primary key incrementCopy the code

④ Non-null Not NULL

If it is set to not NULL, how can I get an error if I do not assign a value to itCopy the code

(5) the default:

Set the default values!Copy the code

1.4 create a table

1) create:

Goal: Create a schoo1 database
Create student table (columns, fields) using SQL
-- Student ID int Login password vARCHAR (20) Name, gender vARCHar (2), date of birth (datatime), home address, emAI1
-- auto_increment Indicates the INCREMENT
Strings are enclosed in single quotes!
All statements are followed by, (English), the last one is not added
A table has only one unique PRIMARY KEY.

CREATE DATABASE school
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'student id',
`name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'name',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'password',
`sex` VARCHAR(2) NOT NULL DEFAULT 'male' COMMENT 'gender',
`birthday` DATETIME DEFAULT NULL COMMENT Date of birth,
`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'email'.PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
Copy the code

(2) formats:

CREATE TABLE [IF NOT EXISTS] ` table name ` (` field name ` column type [properties] [index] [note], ` field name ` column type [properties] [index] [note],... 'Field name' column type [Attribute][Index][comment]) [table type][character set for table][comment]Copy the code

1.5 Types of data tables

1) MYISAM and INNODB:

Note that:

• INNODB also supports full text search after MySQL5.6, but only in English. Interested partners can go to understand the difference between the two types of table underlying storage, here I do not talk about.

General operation:

• MYISAM saves space and is fast,
• INNODB has high security, transaction processing, multi-table multi-user operation

② Position of existence in physical space:

All database files are stored in the data directory, each folder corresponds to a database

The essence is file storage

The MySQL engine differs in physical files

• InnoDB only has a *. FRM file and ibdata1 file in its parent directory in the database table
• MYISAM corresponding file
  • *.frm – Table structure definition file
  • *.myd – Data files
  • *.myi index file

③ Set database character set encoding:

CHARTSET=UTF8

12
Copy the code

If not set, it will be the mysql default character set encoding.

You can configure the default encoding in my.ini

character-set-server=utf8
Copy the code

1.5 Modifying the Delete table

(1) to modify:

ALTER TABLE old TABLE AS new TABLE name
ALTER TABLE student RENAME  AS student1
ALTER TABLE name ADD column name attribute
ALTER TABLE student1 ADD age INT(11)
Alter table fields (rename, modify constraints)
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- Modify constraints
ALTER TABLE student1 CHANGE age age1 INT(1)  -- Rename the field

Drop a table field
ALTER TABLE student1 DROP age1
Copy the code

(2) to delete:

- delete table
DROP TABLE IF EXISTS student1
Copy the code

All create and delete operations should be judged to avoid errors

③ Points to note:

• Field name, using this package
• Comments – / * * /
• The SQL keyword is case-insensitive. You are advised to write it in lowercase
• All symbols are in English

The road ahead is long, I see no end, I will search high and low

If you think I bloggers write well! Writing is not easy, please like, follow, comment and give encouragement to the blogger ~ Hahah