A preliminary understanding of MySQL

First install MySQL:

1. Word part:

①network network②option selection③port port④firewall firewall⑤engine engine

⑥standard standard⑦character character⑧collation proofreading⑨stirage storage

create create drop delete comment comment variables

2. Preview part

1. Please write the sql statement to create and delete the database

CREATE DATABASE database name ;

DROP DATABASE database name;

2. Write the sql statement to create and delete the table

CREATE TABLE table name (

field data type constraint ,

...,

...

)

DROP TABLE name;

3.View table

SHOW TABLES;

4. Specify the storage engine of the table

CREATE TABLE table name (

.........

)ENEGINE=Storage Engine;

3. Exercise Part

1. Complete the configuration of the MySQL database (ask Du Niang...)

2. Use the command line Connect to MySQL and operate the database

mysql -h server address-u username-p password

3. Use SQL statements to create a chart of accounts

#Computer-based three course schedule
DROP DATABASE IF EXISTS `myschool`;

CREATE DATABASE myschool;

USE myschool;

DROP TABLE IF EXISTS `subject`;

CREATE TABLE `subject`(

`subjectNo` INT(4) NOT NULL COMMENT 'Course Number' AUTO_INCREMENT PRIMARY KEY,

`subjectName` VARCHAR(50) COMMENT 'Course Name',

`classHour` INT(4) COMMENT 'class hour',

`gradeID` INT(4) COMMENT 'grade number'
);

4. Computer 4 Create a score table using SQL statements

#Timestamp score table
DROP TABLE IF EXISTS `result`;

CREATE TABLE `result`(
`studentNo` INT( 4) NOT NULL,
`subjectNo` INT(4) NOT NULL,
`exameDate` TIMESTAMP NOT NULL DEFAULT NOW() ,
`studentResult` INT(4) NOT NULL
);

5. Create student table and grade table

#Student table and grade table on computer
DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
`studentNo` INT(4) NOT NULL PRIMARY KEY,
`loginPwd` VARCHAR(20) NOT NULL,
`studentName` VARCHAR(50) NOT NULL,
`sex` CHAR( 2) NOT NULL,
`gradeID` INT(4) UNSIGNED,
`phone` VARCHAR(50),
`address` VARCHAR(255),
`bornDate` DATETIME,
`eamil` VARCHAR(50),
`identityCard` VARCHAR(18)
);

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade`(
`gradeID` INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`gradeName` VARCHAR(50) NOT NULL
);

6. Use system help

HELP Query content;

4. Summary part

MySQL storage engine

Commonly used storage engines: InnoDB, MyISAM

InnoDB: supports transaction processing, external key. It takes up more space than MyISAM, and is suitable for scenarios that require frequent transaction processing, updates, and deletions.

MyISAM: does not support transactions and foreign keys, takes up less space, and has fast access speed. It is suitable for scenarios that do not require transaction processing and frequent queries. Application scenarios

The above is the detailed content of A preliminary understanding of MySQL. For more information, please follow other related articles on the PHP Chinese website!