【WEEK5】 【DAY4】Database Operations【English Version】

2024.3.28 Thursday

2. Database Operations

2.1. Databases

2.1.1. Creating a New Database (Right-click method)

2.1.2.Query: Click “Query” -> “New Query Table” to enter the required statements, click “Run”, such as:

2.2. Classification of Structured Query Languages

DDL: Data Definition Language
DML: Data Manipulation Language
DQL: Data Query Language
DCL: Data Control Language

2.3. Database Operations

2.3.1. Command Line Database Operations

1. Create database: create database [if not exists] database_name;
2. Delete database: drop database [if exists] database_name;
3. View databases: show databases;
4. Use database: use database_name; Note: If the table name or field name is a special character, it needs to be enclosed with backquotes
   [ ] indicates optional content (to avoid error generation)
   
   
   After entering and running SHOW DATABASES, you can see an additional line called westos. Then run DROP DATABASE westos to delete the database. To check, enter SHOW DATABASES again, and you will find that the line westos has disappeared.
   The line USE school is used to select the database, but no difference was found in specific use (it seems the result is the same without running this line)
   

2.4. Database Data Types (Column Types)

2.4.1. Column Types: Specifies the data type stored in that column of the database

2.4.1.1. Numeric Types

int type is commonly used
Due to precision issues, decimal is used for financial calculations

2.4.1.2. String Types

varchar is commonly used, corresponding to the string in Java

The difference between tinytext and text (for saving large texts): storage size, still uses string in Java

2.4.1.3. Date and Time Types

The most commonly used time format: datetime (YYYY-MM-DD HH:mm:ss)
where date is the date format: YYYY-MM-DD
time is the time format: HH:mm:ss
timestamp (also commonly used) is the number of milliseconds from 1970.1.1 zero hour to now

2.4.1.4. Null

Understood as “no value” or “unknown value”
Do not perform arithmetic operations with NULL, the result remains NULL

2.5. Database Field Attributes (Key Points)

2.5.1. UnSigned

1. Unsigned
2. Declares that the data column does not allow negative numbers

2.5.2. ZEROFILL

1. Zero-filled
2. Missing digits are filled with zeros, e.g., int(3),5 becomes 005

2.5.3. Auto_Increment

2.5.3.1. Automatically increases, each addition of data automatically adds 1 (default) on top of the previous record

After setting “Auto Increment”, there is no need to manually input the value of the auto-incremented number when adding rows in the lower left corner of “Open Table”, just select “√”, and the automatically filled and incremented number will appear, such as:
=> after selecting √ =>

2.5.3.2. Often used to set the primary key, and must be an integer type

2.5.3.3. Can define start value and step size

• Set step size for the current table (AUTO_INCREMENT=100): Only affects the current table
• SET @@auto_increment_increment=5; Affects all tables using auto increment (global)

2.5.4. NULL and NOT NULL

• Default is NULL, i.e., no value inserted for that column
• If set to NOT NULL, then the column must have a value
  If checked, then it cannot be empty (not null)

2.5.5. DEFAULT

1. Default
2. Used to set default values
3. For example, for the gender field, default to “male”, otherwise “female”; if no value is specified for this column, the default value will be that of “male”.
   
   
   

2.6. Creating Tables with SQL Statements (Key Points)

2.6.1. Requirements:

• Goal: Create a school database
  • Create a student table (columns, fields)
  • Student ID int, Login Password varchar(20), Name, Gender varchar(2), Date of Birth (datetime), Home Address, Email
  • Before creating a table, you must select a database first

2.6.2. Points to Note & Code

# Student ID int, Login Password varchar(20), Name, Gender varchar(2), Date of Birth (datetime), Home Address, Email
# Note the difference between `backticks (for table names, column names)` and 'single quotes (for comments)'
# After creating a table, you should add (), enclosing the related statements within, and each statement ends with a comma (except the last line)

CREATE TABLE IF NOT EXISTS `student3`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'student id',
	`psw` VARCHAR(20) NOT NULL DEFAULT '111111' COMMENT 'password',
	`name` VARCHAR(20) NOT NULL DEFAULT 'LIHUA' COMMENT 'name',
	`gd` VARCHAR(6) NOT NULL DEFAULT 'MALE' COMMENT 'gender',
	`bd` DATETIME DEFAULT NULL COMMENT 'birthday',
	`add` VARCHAR(100) DEFAULT NULL COMMENT 'address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'email',
	
	# Set primary key (usually one table has only one primary key, and it is advised to declare it separately)
	PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

--Format--
CREATE TABLE [IF NOT EXISTS] `table_name`	(
	`column_name1` column type [attributes] [index] [comment],
	`column_name2` column type [attributes] [index] [comment],
	......
	`column_nameN` column type [attributes] [index] [comment]
)[table type] [charset settings] [comment]

2.7. Reverse Usage

2.7.1. View Database Definition

SHOW CREATE DATABASE school;

(Click run on a single line to see the explanation part, as below)

2.7.2. – View Data Table Definition

SHOW CREATE TABLE student;

2.7.3. Display Table Structure

DESC student;  -- Set strict check mode (no tolerance)

SET sql_mode='STRICT_TRANS_TABLES';

2.8. Types of Data Tables

2.8.1. Setting Data Table Type

CREATE TABLE table_name(
   -- Some code omitted
   -- Mysql comments
   -- 1. # Single-line comment
  -- 2. /*...*/ Multi-line comment
)ENGINE = MyISAM (or InnoDB)

-- View mysql supported engine types (table types)
SHOW ENGINES;

MySQL data table types: MyISAM, InnoDB, HEAP, BOB, CSV, etc…
Common types MyISAM and InnoDB:

Experience (Applicable Situations):

• Use MyISAM: To save space and response speed
• Use InnoDB: For security, transaction processing, and multi-user data table operations

2.8.2. Data Table Storage Location

2.8.2.1. MySQL Data Tables Are Stored on Disk as Files

• Includes table files, data files, and the database’s options file
• Location: Stored in the Mysql installation directory\data. (Essentially still file storage, one folder corresponds to one database) The directory name corresponds to the database name, and the files in this directory correspond to the data tables.

2.8.2.2. Physical File Differences of MySQL Engines

• InnoDB type data tables only have one *.frm file, and the ibdata1 file in the parent directory
• MyISAM type data tables correspond to three files:

* .frm -- Table structure definition file
* .MYD -- Data file (data)
* .MYI -- Index file (index)

2.8.3. Setting Data Table Character Set

We can set different character sets for databases, data tables, data columns. Setting methods:

1. Set during creation through command, e.g.: CREATE TABLE table_name()CHARSET = utf8;
2. If not set, it follows the MySQL database configuration file my.ini parameter setting: “character-set-server=utf8”

2.9. Modifying, Deleting Databases

2.9.1. Modify Table (ALTER TABLE)

2.9.1.1. Change Table Name: ALTER TABLE old_table_name RENAME AS new_table_name

2.9.1.2. Add Field: ALTER TABLE table_name ADD column_name column attribute[attribute]

2.9.1.3. Modifying Columns:

• Rename: ALTER TABLE table_name CHANGE old_column_name new_column_name column_attribute[attribute]
• Modify constraints: ALTER TABLE table_name MODIFY column_name column_type[attribute]
  
  

2.9.1.4. Deleting Columns: ALTER TABLE table_name DROP column_name

2.9.2. Deleting Tables

2.9.2.1. Syntax: DROP TABLE [IF EXISTS] table_name

• IF EXISTS is optional, used to check if the table exists
• Deleting a non-existent table will throw an error
  

2.9.3. Points to Note

1. Use backticks (`) to enclose identifiers (database names, table names, column names, indexes, aliases) to avoid naming conflicts with keywords! Chinese characters can also be used as identifiers!
2. Each database directory has an options file db.opt that stores the current database options.
3. Comments:
  Single-line comment # Comment content
  Multi-line comment /* Comment content */
  Single-line comment -- Comment content       (Standard SQL comment style, requires a space character (space, TAB, newline, etc.) after the double dash)
4. Pattern wildcards:
  _   Any single character
  %   Any number of characters, including zero characters
  Single quotes need to be escaped \'
5. CMD command line statements can end with ";", "\G", "\g", only affecting the display result. Elsewhere, use a semicolon to end. Delimiter can modify the statement terminator for the current session.
6. SQL is case-insensitive to keywords
7. To clear existing statements: \c