1. MySQL Library and Table Building
1. Create a database
Creating a database is a block area on the system disk used for data storage and management. If an administrator creates a database for users when setting permissions, it can be used directly. Otherwise, you need to create the database yourself.
Grammar Format:
CREATE DATABASE [IF NOT EXISTS] Database Name
Example:
IF NOT EXISTS: Make a judgment before creating the database and perform the operation only if the database does not currently exist.
This option can be used to avoid duplicate creation errors where the database already exists.
# Create myschool database create database myschool;
IF NOT EXISTS: Make a judgment before creating the database and perform the operation only if the database does not currently exist.
This option can be used to avoid duplicate creation errors where the database already exists.
2. Create tables
Grammar Format:
CREATE TABLE [IF NOT EXISTS] table name ( Field 1 data type [field attribute | constraint] [index] [comment], ...... Field n Data Type [Field Properties|Constraints][Index][Comment] (Table type][Table character set][Comment];
Example:
#Create Student Table CREATE TABLE `student`( `studentNo` INT(4) PRIMARY KEY, ` name` CHAR(10), ......);
Be careful:
Multiple fields separated by commas Keep words enclosed in apostrophes Single line comment: #... Multiline comment: /*...*/
(1) Field constraints and attributes
Primary key
CREATE TABLE student( `studentNo` INT(4) PRIMARY KEY, ......);
Notes
CREATE TABLE test ( `id` int(11) UNSIGNED COMMENT 'number' )COMMENT='Test Table' ;
Set Character Set Encoding
CREATE TABLE [IF NOT EXISTS] table name ( #Omit Code (CHARSET =character set name;
(2) Create a student table in the myschool database
Commands to be executed
create databases myschool;
use myschool;
create table student(
`studentNo` int(4) not null comment 'School Number' primary key,
`loginPwd` varchar(20) not null comment 'Password',
`studentName` varchar(50) not null comment 'Full name',
`sex` char(2) not null default 'male' comment 'Gender',
`gradeID` int(4) unsigned comment 'Grade Number',
`phone` varchar(50) comment 'Telephone',
`address` varchar(255) default 'Address unknown' comment 'address',
`bornDate` datetime comment 'Date of birth',
`email` varchar(50) comment 'Mail Account',
`identityCard` varchar(18) comment 'ID number' unique key
)charset='utf8' comment='Student table';Check the table structure
mysql> desc student; +--------------+-----------------+------+-----+----------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------+------+-----+----------+-------+ | studentNo | int(4) | NO | PRI | NULL | | | loginPwd | varchar(20) | NO | | NULL | | | studentName | varchar(50) | NO | | NULL | | | sex | char(2) | NO | | male | | | gradeID | int(4) unsigned | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(255) | YES | | Address unknown | | | bornDate | datetime | YES | | NULL | | | email | varchar(50) | YES | | NULL | | | identityCard | varchar(18) | YES | UNI | NULL | | +--------------+-----------------+------+-----+----------+-------+ 10 rows in set (0.00 sec)
3. View Table
(1) Check to see if the table exists
use myschool; show tables;
(2) View table definitions
Grammar Format:
use myschool; desc `student`;
Example:
use myschool; desc `student`;
4. Delete Table
Grammar Format:
drop table [if exists] Table Name;
Example:
use myschool; drop table if exists `student`;
Use the if exists statement to verify that the table exists before deleting it
5. Delete database
Deleting a database cleans up existing databases from disk space, and after that, all data in the database is also deleted.
Deleting a database statement is similar to creating a database. The basic syntax format for deleting a database in MySQL is:
drop database if exists database name;
Example:
drop database if exists myschool;
6. On-line Practice
(1) Create a subject in the myschool database
create table subject(
`subjectNo` int(4) comment 'Course Number' primary key auto_increment,
`subjectName` varchar(50) comment 'Course Name',
`classHour` int(4) comment 'Hours',
`gradeID` int(4) comment 'Grade Number'
);Check the table structure
mysql> desc subject; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | subjectNo | int(4) | NO | PRI | NULL | auto_increment | | subjectName | varchar(50) | YES | | NULL | | | classHour | int(4) | YES | | NULL | | | gradeID | int(4) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
(2) Create result tables in myschool database
create table result(
`studentNo` int(4) comment 'School Number' not null,
`subjectNo` int(4) comment 'Course Number' not null,
`examDate` datetime(0) comment 'Examination Date' not null,
`studentResult` int(4) comment 'Examination results' not null
);Check the table structure
mysql> desc result; +---------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+-------+ | studentNo | int(4) | NO | | NULL | | | subjectNo | int(4) | NO | | NULL | | | examDate | datetime | NO | | NULL | | | studentResult | int(4) | NO | | NULL | | +---------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
2. Storage Engine for MySQL
1. Introduction to Storage Engine
The database storage engine is the underlying software component of the database. The database management system (DBMS) uses the data engine to create, query, and update
New and delete data operations.Different storage engines provide different storage mechanisms, indexing techniques, locking levels, and so on.Use different memory
Storage engine, you can also get specific functionality.
Many different database management systems now support many different data engines.The core of MySQL is the storage engine.
2. Type of storage engine
mysql has several storage engines, which are:
MyISAM InnoDB MERGE MEMORY EXAMPLE FEDERATED ARCHIVE CSV BLACKHOLE
3. Main differences between storage engines
(1) MyISAM storage engine features
- MySQL uses the MyISAM engine before 5.5 and InnoDB engine after 5.5
- The MyISAM engine reads faster, consumes relatively few resources, does not support transactions, does not support foreign key constraints, but supports full-text indexing
- Reading and writing are blocked, that is, you cannot write data while reading it, and you cannot read data when writing it
- The MyISAM engine can only cache indexes, not data
(2) InnoDB storage engine features
- Preferred engine for transactional databases, transactional security tables, row locking and foreign keys, InnoDB as default storage engine after MySQL version 5.5.5
- Transaction Secure Storage Engine with commit, rollback, and crash recovery capabilities, capable of handling large amounts of data, high performance and efficiency, and fully supports foreign key integrity constraints
- Has very efficient caching features, can cache index can also cache data, hardware requirements are relatively high
- When InnoDB is used, a 10MB-size auto-expanding data file named ibdata1 and two files named ib_will be created in the MySQL data directoryLogfile0 and ib_5MB Large Small Logfile for logfile1
(3) Memory Storage Engine Features
- The Memory Storage Engine stores data in tables in memory, providing quick access to queries and references to other table data
- Memory Storage Engine performs HASH and BTREE indexing, does not support BLOB and TEXT columns, and supports AUTO_INCREMENT column and index of columns that can contain NULL values
-
To free memory used by a Memory table when the contents of the Memory table are no longer needed, DELETE FROM or
TRUNCATE TABLE, or delete the entire table
4. Storage Engine Application
(1) MyISAM suitable scene
- Businesses that do not require transaction support, such as transfers
- For businesses that read a lot of data, not for businesses that read and write frequently
- Business with relatively low concurrency and relatively few data modifications
- Machines with poor hardware resources might consider using the MyISAM engine
(2) InnoDB suitable scenarios
- Business Required Transaction Holding, Concurrent Business
- Scenes with frequent data updates, such as BBS, SNS, Weibo, etc.
- Data consistency requires more business than e.g. recharge transfer, silver card transfer
(3) Summary
Use MyISAM: no transaction, small space, query access as the main use of InnoDB: multiple delete, update operations, high security, transaction and concurrency control
5. View the current default storage engine
mysql> show variables like '%storage_engine'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 3 rows in set, 1 warning (0.02 sec)
6. Modify the default storage engine
(1)MySQL 5.5
modifyMy.iniconfiguration file
default_storage_engine=InnoDB
(2)MySQL 5.7
The easiest way is to modify the storage engine of the table directly by command, as follows:
alter table table name ENGINE =engine name;
Example:
ALTER TABLE student ENGINE = InnoDB;
7. Set up storage engine for tables
Grammar Format:
CREATE TABLE table name ( #Omit Code ) ENGINE = storage engine;
Example:
CREATE TABLE `myisam` ( id INT(4) )ENGINE=MyISAM;
3. Additional knowledge of MySQL
In mysql, you can create up to 2 billion tables per database, and a table allows you to define 1024 columns, with a maximum length of 8092 bytes per row (excluding the lengths of texts and image types).
When a table has varchar, nvarchar, or varbinary column defined, inserting more than 8092 bytes of data row into the table will cause the statement to fail with error information.
SQL Server does not have a direct limit on the number of rows in each table, but it is limited by the database storage space.The maximum space available for each database is 1048516TB, so the maximum space available for a table is 1048516TB minus the space occupied by database class system tables and other database objects.Theoretically infinite, depending on your hard disk is not big enough, in most cases your hard disk is not big enough first.