MySQL database basic commands-1

Chapter One: Database overview
1. Data database (DB) database management system (DBMS) database system (DBS)
2. Functions provided by the database management system:
(1) Data definition language: DDL
(2) Data Manipulation language: DML basic data operations are available in two categories: Search (query) and update (INSERT, delete, update)
Check-Insert-delete-more
(3) Data Control Language (DCL): Data integrity control, data security control and database recovery
Chapter II: MySQL Installation and configuration

Chapter Three. MySQL Database basic operations
system databases and user databases
System database: Information_schema,performance_schema,mysql,test
Database objects: Tables, views, stored procedures, functions, triggers, and events
1. Create a database
CREATE DATABASE databasetest;
2. View and select a database
View: Show DATABASES;
Choice: Use Databasetest;
3. Deleting a database
DROP DATABASE Databasetest1;

Fourth chapter. Storage engine and data type in MySQL database
1.SHOW ENGINES \g Display storage engine
MYSQL5.5 supports 9 types of storage engines: Federated,mrg_myisam,myisam,blackhole,csvmmemory,archive,innodb and Performance_schema, respectively
P53: Storage Engine Application recommendations
2. Data type: integer type, floating-point type, fixed-point number type and bit type, date and time type, String type

(1) Integer type: Tinyint,smallint,mediumint,int and Integer,bigint
(2) floating-point type: float,double
Fixed-point number types: DEC (m,d) and decimal (M,D)
(3) Bit type: bit (M)
(4) Date and practice type: date,datetime,timestamp,time,year
(5) String type: CHAR (m), VARCHAR (m)

Fifth. Operation of the table
Basic concepts of tables: database objects in tables contain columns, indexes, and triggers
1. Create a table
(CREATE DATABASE Company;)
(use company;)
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40)
);
2. View table structure
(use company;)
DESCRIBE t_dept; Describe statement view table definition describe: description

(use company;)
Show CREATE TABLE t_dept \g Show CREATE TABLE statement view table Detail definition
3. Delete a table
(use company;)
DROP TABLE t_dept;
(DESCRIBE t_dept;)
4. Modify the table
(1) Modify table name
ALTER TABLE t_dept RENAME tab_dept;
(2) Add field
(use company;)
(DESC t_dept;)
ALTER TABLE t_dept ADD descri VARCHAR (20);
(DESC t_dept;)

(use Company;desc t_dept;)
ALTER TABLE t_dept ADD Descri VARCHAR (a) first;
(DESCRIBE t_dept;)

(use Company;desc t_dept;)
ALTER TABLE t_dept ADD Descri VARCHAR (a) after Deptno;
(3) Delete a field
(use Company;desc t_dept;)
ALTER TABLE t_dept DROP deptno;
(DESCRIBE t_dept;)
(4) Modifying a field
1. Modifying the data type of a field
(use Company;desc t_dept;)
ALTER TABLE t_dept MODIFY deptno VARCHAR (20);
(DESC t_dept;)
2. Change the name of the field
ALTER TABLE t_dept Change loc location VARCHAR (40);
3. Modify the name and attributes of the field at the same time
ALTER TABLE t_dept Change loc location VARCHAR (20);
4. Modify the order of the fields
ALTER TABLE t_dept MODIFY Loc VARCHAR (+) first;
ALTER TABLE t_dept MODIFY deptno INT (one) after dname;

5. Constraints of the Operation table: when using MySQL software specifically, if you want to do some integrity checking on the data in the table, you can do it through the constraints of the table.
(1) Integrity constraints:
Not NUL (NK)
DEFAULT
UNIQUE KEY (UK)
PRIMARY KEY (PK)
Auto_increment
FOREIGN KEY (FK)
(1) Set non-null constraint (NOT NULL, NK)
(CREATE DATABASE Company; Use company;)
CREATE TABLE t_dept (
Deptno INT (a) is not NULL,
Dname VARCHAR (20),
Loc VARCHAR (40)
);
(DESC t_dept;)
(2) Set default value for field
(CREATE DATABASE Company; Use company;)
CREATE TABLE t_dept (
Deptno INT not NULL,
Dname VARCHAR (DEFAULT ' Cjgong '),
Loc VARCHAR (40)
);
(DESC t_dept;)
(3) Set UNIQUE constraint (unique, UK)
...
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (a) UNIQUE,
Loc VARCHAR (40)
);
...
If you want to set a name for the UK constraint on the field dname, you can execute the SQL statement constraint, create the table t_deptconstraint: Constraints, constraints, coercion
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
CONSTRAINT uk_dname UNIQUE (dname)
);
(4) Setting the PRIMARY KEY constraint (PRIMARY key, PK)
Single Field primary key
(CREATE DATABASE Company; Use company;)
CREATE TABLE t_dept (
Deptno INT PRIMARY KEY,
Dname VARCHAR (20),
Loc VARCHAR (40)
); default not NULL after setting primary key
(DESC t_dept;)
Set a name for the PK constraint: CONSTRAINT
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
CONSTRAINT pk_dname PRIMARY KEY (dname)
);
Multi-field Primary key
...
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
CONSTRAINT pk_dname_deptno PRIMARY KEY (deptno, Dname)
);
...
(5) Set field value auto-increment (auto_increment)
...
CREATE TABLE t_dept (
Deptno INT PRIMARY KEY auto_increment,
Dname VARCHAR (20),
Loc VARCHAR (40)
);
...
(6) Setting foreign KEY constraints (FOREIGN key, FK)
...
CREATE TABLE t_dept (
Deptno INT PRIMARY KEY,
Dname VARCHAR (20),
Loc VARCHAR (40)
);
CREATE TABLE T_employee (
Empno INT PRIMARY KEY,
Ename VARCHAR (20),
Job VARCHAR (40),
MGR INT,
HireDate DATE,
Sal DOUBLE (10,2),
Comm DOUBLE (10,2),
Deptno INT,
CONSTRAINT fk_deptno FOREIGN KEY (DEPTNO)
REFERENCES t_dept (DEPTNO)
);
...

Sixth. Operation of the Index
Why use an index:
Six indexes: Normal index, unique index, full-text index, single-column index, multicolumn Index, and spatial index
1. Creating and viewing indexes
Three ways to create an index: Create an index when creating a table, create an index on a table that already exists, and create an index by using an SQL statement ALTER TABLE
(1). Create a normal index when creating a table
(use company;)
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
INDEX Index_deptno (DEPTNO)
);
(SHOW CREATE TABLE t_dept \g)
(2). Create a normal index on a table that already exists
...
CREATE INDEX Index_deptno
On t_dept (DEPTNO);
...
(2). Create a normal index from the SQL statement ALTER TABLE
ALTER TABLE t_dept ADD INDEX index_deptno (DEPTNO);
2. Create and view unique indexes
(1). Create a unique index when creating a table
...
CREATE TABLE t_dept (
Deptno INT UNIQUE,
Dname VARCHAR (20),
Loc VARCHAR (40),
UNIQUE INDEX Index_deptno (DEPTNO)
);
(SHOW CREATE TABLE t_dept \g; EXPLAIN SELECT * from t_dept WHERE deptno=10 \g)
(2). Create a unique index on a table that already exists
CREATE UNIQUE INDEX Index_deptno on t_dept (DEPTNO);
(3). Create a unique index from the SQL statement ALTER TABLE
ALTER TABLE t_dept ADD UNIQUE INDEX index_deptno (DEPTNO);
3. Create and view full-text indexes
(1). Create a full-text index when creating a table
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
Fulltext INDEX Index_loc (Loc)
) Engine=myisam;
(2). Create a full-text index on a table that already exists
CREATE fulltext INDEX Index_loc on T_dept (LOC);
(3). Create a full-text index from an SQL statement ALTER TABLE
ALTER TABLE t_dept ADD fulltext INDEX index_loc (LOC);
4. Create and view multi-column indexes
(1). Create a multicolumn index when creating a table
CREATE TABLE t_dept (
Deptno INT,
Dname VARCHAR (20),
Loc VARCHAR (40),
KEY Index_dname_loc (Dname,loc)
);
(SHOW CREATE TABLE t_dept \g)
(2) Create a multicolumn index on a table that already exists
CREATE INDEX Index_dname_loc on t_dept (Dname,loc);
(3). Create a multicolumn index from the SQL statement ALTER TABLE
ALTER TABLE t_dept ADD INDEX index_dname_loc (dname,loc);
5. Deleting an index
(Use company; SHOW CREATE TABLE t_dept \g; EXPLAIN SELECT * from t_dept WHERE dname= ' Cjgong ' \g)
DROP INDEX index_dname_loc on t_dept;
(SHOW CREATE TABLE t_dept \g)

MySQL database basic commands-1