First Knowledge mysql--life if ever meet

CREATE TABLE ' Student ' (
' Studentno ' INT (4) Not NULL PRIMARY KEY COMMENT ' study number ',
' Loginpwd ' VARCHAR (not NULL COMMENT ' password '),
' Studentname ' VARCHAR (a) not NULL COMMENT ' name ',
' Sex ' CHAR (2) Not NULL DEFAULT ' male ' COMMENT ' sex ',
' Gradeid ' INT (4) UNSIGNED COMMENT ' Grade number ',
' Phone ' VARCHAR (COMMENT ' telephone '),
' Address ' VARCHAR (255) COMMENT ' addresses ',
' Borndate ' datetime COMMENT ' date of birth ',
' Email ' VARCHAR (COMMENT ' email account '),
' Identitycard ' VARCHAR UNIQUE KEY COMMENT ' ID number '
);
DROP TABLE ' student '
CREATE TABLE ' Subject ' (
' Subjectno ' INT (4) PRIMARY KEY auto_increment COMMENT ' Course number ',
' Subjectname ' VARCHAR (COMMENT ' course name '),
' Classhour ' INT (4) COMMENT ' hours ',
' Gradeid ' INT (4) COMMENT ' Grade number '
)
CREATE TABLE ' Result ' (
' Studentno ' INT (4) Not NULL COMMENT ' study number ',
' Subjectno ' INT (4) Not NULL COMMENT ' course number ',
' Examdate ' DATETIME not NULL COMMENT ' test Date ',
' Studentresult ' INT (4) Not NULL COMMENT ' exam results '
)
CREATE TABLE ' Grade ' (
' Gradeid ' INT (4) Not NULL COMMENT ' grade number ',
' Gradename ' VARCHAR (not NULL COMMENT ' grade name ')

) comment= ' Grade table ';
#创建主键
ALTER TABLE ' Grade ' ADD CONSTRAINT pk_grade PRIMARY KEY ' Grade ' (Gradeid);
ALTER TABLE ' grade ' MODIFY ' Gradeid ' INT (4) Not NULL COMMENT ' grade number ';
#创建外键
ALTER TABLE ' student ' ADD CONSTRAINT
Fk_student_grade FOREIGN KEY (' Gradeid ')
REFERENCES ' Grade ' (' Gradeid ');
ALTER TABLE result ADD PRIMARY KEY pk_result (' Studentno ', ' subjectno ', ' examdate ');

#test数据库创建person表

CREATE DATABASE ' text ';
CREATE TABLE ' person ' (
' Number ' INT (4) auto_increment PRIMARY KEY COMMENT ' ordinal ',
' Name ' VARCHAR (a) not NULL COMMENT ' name ',
' Sex ' CHAR (2) COMMENT ' gender ',
' Borndate ' datetime COMMENT ' date of birth '
) comment= ' Person table ';
ALTER TABLE ' person ' RENAME ' Tb_person ';
ALTER TABLE ' Tb_person ' DROP ' borndate ';
ALTER TABLE ' Tb_person ' ADD ' borndate ' DATE;
ALTER TABLE ' Tb_person ' change ' number ' id ' BIGINT;

Mysql:
DBS Database
DBMS Database management System
DBA Database administrator
DB database
DBA operates DB through DBMS
relational databases and non-relational databases
Log in to MySQL
Mysql-h host Address-u user name-p password
Querying all the Databases
show databases;
Create a database
Create DATABASE [if not EXISTS] DB name;
Deleting a database
Drop DATABASE[IF exists] database name;

Structure language classification
DDL (data definition Language) Create drop alter creates deletes and modifies databases, tables, stored procedures, triggers, indexes ...

DML (Data manipulation language) Insert Delete update to manipulate data in the database

DQL (data Query language) Select is used to query data in the database

DCL (Data Control Language) grant (AUTHORIZATION) revoke (revocation)

TCL (Object Control Language) begin SavePoint (set rollback point) rollback commit
Grant query to User name
Revoke Query from user name
CRUD (Increase and deletion)

Create user
Create user username @ ' address '
Identified by ' password ';

Authorization to the user
"Grant all" on the * * to user name;
Grant all on * * to username @ ' address ';
Refresh System Permissions
Flush privileges;
Delete User
All users are stored in the user table in the MySQL database
Delete from Mysql.user where user= ' 14 ';
Delete from Mysql.user where user= ' and host= ' localhost ';

logical operators
&& and
or | | Or
Not! Non -

User switches to the specified database
If you do not switch to the specified database, you need to precede the table with the name of the database
Use MySQL;
Select ' Host ', ' User ' from ' user ';

Data type
int double
Decimal (A, b) is used in the MySQL database
A specifies the maximum number of decimal digits that can be stored to the right of the left of the decimal point, with a maximum precision of 38.
b Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. The number of decimal digits must be a value from 0 to a. The default number of decimal digits must be a value from 0 to a. The default number of decimal digits is 0

Frequent use of *****number***** in Oracle

Char fixed length For example, gender is char type length is 10 but our actual input length is 2; then storage is 10 space, which wastes resources.

VarChar variable length example: Sex is char type length is 10
But his actual length is 2.
Then the storage is 2 spaces.

Date format
Date Yyyy-mm-dd
DateTime YY-MM-DD HH:MM:SS
Time Hh:MM:ss
Timestamp 1970 to now y Yyymmddhhmmss
Year YYYY 1901

Constraint type

PRIMARY KEY constraint primary key (PK)
Used to set the primary key of the table to ensure the uniqueness of the row
FOREIGN key foreign key (FK)
Used to establish a relationship between tables
nonempty constraint NOT NULL
field is not empty
Default constraint default value for field
Uniquely constrained unique key (UK)
The value of the field is unique and can be null, but there can be only one
Autogrow auto_increment Set the automatic growth of columns (identity columns), which is typically used to set the primary key

Create a table
If it is a keyword then use the anti-quote '

The CREATE table [if not EXISTS] table name (
Field 1 data type [constraint, index, comment],
Field 2 data type [constraint, index, comment],
Field 3 data type [constraint, index, comment]
) [Table type] [table character set] [comment]

Signed types and unsigned types

A signed type? : Negative values can be taken
Unsigned type: default is 0! 0---Type of length

Zerofill attribute: If the number of digits is not enough, the front is 0 padded

If a bit value field specifies the Zrofill property, the unsigned property is added automatically!

Create student Tables

CREATE table if not EXISTS student (
Studentno Int (4)
Not null primary key comment ' password ',
Studentname varchar (a) NOT null comment ' student name ',
Sex char (2) NOT null default ' male ' comment ' sex ',
Gradeid Int (4) unsigned comment ' Grade number ',
Phone varchar (comment) ' Telephone '
Address VARCHAR (255) DEFAULT ' addresses unknown ' COMMENT ' address ',
borndate datetime COMMENT ' date of birth ',
Email VARCHAR (COMMENT) ' Email account ',
Identitycard VARCHAR UNIQUE KEY COMMENT ' ID number '

) comment= ' Student table ';

--Modify the length of the field in the table to 50
ALTER TABLE student MODIFY WeChat VARCHAR (50);

--Delete a field in a table
ALTER TABLE student DROP WeChat;

--Modify the name of the Studentname field to Stuname
ALTER TABLE student Change ' name ' Stuname VARCHAR (20);

Create a subject chart of accounts
CREATE table if not EXISTS ' subject ' (
Subjectno INT (4) auto_increment PRIMARY KEY COMMENT ' Course number ',
Subjectname VARCHAR (COMMENT ' course name '),
Classhour INT (4) COMMENT ' UI ',
Gradeid INT (4) COMMENT ' Grade number '
) comment= ' Chart of accounts ' charset= ' UTF8 ';
The use of auto_increment is a must and primary KEY joint use!

Create a grade Table

CREATE TABLE IF not EXISTS grade (
Gradeid INT (4) COMMENT ' Grade number ',
Gradename VARCHAR (Ten) COMMENT ' Grade name '
) comment= ' Grade table ';

Syntax for adding a primary key
ALTER TABLE grade ADD constraint Pk_grade_gradeid primary key (
Gradeid);

Create a score table

CREATE TABLE IF not EXISTS result (
Studentno INT (4) Not NULL PRIMARY KEY COMMENT ' study number ',
Subjectno INT (4) Not NULL COMMENT ' course number ',
Examdate DATETIME not NULL COMMENT ' test Date ',
Studentresult INT (4) Not NULL COMMENT ' exam results '
Comment= ' score table ' charset= ' UTF8 ' engine=innodb;

Syntax for foreign keys:
ALTER table name ADD CONSTRAINT FOREIGN Key Name
FOREIGN Key (foreign key field)
REFERENCES associated table name (associated field);

Create a relationship between a student table and a grade table

Students should belong to a grade

The foreign key must be built on the FROM table!
ALTER TABLE student ADD constraint
Fk_studnet_grade
Foreign KEY (Gradeid)
References grade (Gradeid);

Problem: The premise has established the primary foreign key relationship

If there are three grades, the numbers are 1 2 3, respectively.
So there are also three students corresponding to the relationship is
1th Student 1 Grade
2nd Student 2 Grade
3rd Student 2 Grade

01. Is there a student's grade number 4 in this case?
There is no data in the main table! Cannot create from table!

02. Can I delete grade 2 or 1 grade???
Cannot be deleted! Because there are associated data from the table!

03. What if we have to delete grade??
Delete the associated data from the table before deleting the data from the main table!

--Set the Union primary key for the score table
ALTER TABLE result add primary key Pk_result (studentno,subjectno,examdate);

Two types of storage engines commonly used in MySQL databases

MyISAM InnoDB
Use the scene:

MyISAM: Can not use transaction, small space, suitable for query!
InnoDB: more suitable for adding and deleting, high security! Concurrent processing of transactions!

Querying the current default storage engine
Show variables like ' strage_engine ';
--Modify the storage engine
--Find the My.ini file under the installation path
--Join
Defaulet-storage-engine=innodb

Where database tables are stored
Innnodb Type of table file
*.FRM: Table structure definition file
*.IBD: Data files

MyISAM Type of table file
*.FRM: Table structure definition file
*. MYD: Data files
*. MYI: Index File

DML and DQL additions and deletions

Seclect * FROM grade
Add insert
--3 new data in the grade table
Insert into Grade (Gradeid,gradename) VALUES (4, ' 4 Grade ');
INSERT into Grade (Gradeid,gradename) VALUES (5, ' 5 grade ');
INSERT into Grade (Gradeid,gradename) VALUES (6, ' 6 grade ');

--Delete 456
Delete from grade where gradeid>3;
Delete from grade where
Gradeid=4 or gradeid=5 or gradeid=6;
Delete from grade where Gradeid in (4,5,6);

--Simultaneous insertion of multiple data The Oracle database does not support
Insert into Grade (Gradeid,gradename) VALUES (4, ' 4 Grade '),
(5, ' 5 grades '), (6, ' 6 grade ');

--Revise Gradeid=1 's grade name to one
Update grade set Gradename= ' one '
where gradeid=1

-----Delete Deletes all data from the table
Delete from grade;

First Knowledge mysql--life if ever meet