MySQL Database common commands

1. Linux Installation Database

# yum install mysql* -y

2. Start database/Close Database/Restart database/view database status

# /etc/init.d/mysqld start# /etc/init.d/mysqld stop# /etc/init.d/mysqld restart# /etc/init.d/mysqld status

3. Boot Database

# chkconfig mysql on

4, configuration modify the database root password

# mysqladmin -uroot password "123456"或：mysql> SET PASSWORD FOR ‘root‘@‘localhost‘=PASSWORD(‘123456‘);

5. Log in to the database using the root user

# mysql -uroot -p123456

6. Create DATABASE Xiessdb

mysql> CREATE DATABASE xiessdb;

Note: In the MySQL statement ";" means "\g" or "\g", "\g" and ";" Same, "\g" can be displayed to make the results more intuitive

7. Use Show to view the database xiess

mysql> show DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               || xiessdb            |+--------------------+4 rows in set (0.00 sec)

8. Delete Database Xiessdb

mysql> DROP DATABASE xiessdb;

9. View the storage engine types supported by MySQL database

Mysql> Show engines;+------------+---------+------------------------------------------------------------+---- ----------+------+------------+|Engine |Support |Comment |Transactions |XA |savepoints |+------------+---------+------------------------------------------------------------+--------------+ ------+------------+|Mrg_myisam |YES |Collection of identical MyISAM tables |NO |NO |NO | |CSV |YES |CSV Storage Engine |NO |NO |NO | | MyISAM |  DEFAULT |  Default engine as of MySQL 3.23 with great performance |  NO |  NO |  NO | |  InnoDB |  YES |  Supports transactions, Row-level locking, and foreign keys |  YES |  YES |  YES | |  MEMORY |  YES |  Hash based, stored in memory, useful for temporary tables |  NO |  NO |  NO |+------------+---------+---------------------------------------------------------- --+--------------+------+------------+ 

The engine parameter is the name of the storage engines; The support parameter refers to whether MySQL supports the class engine; the comment parameter refers to the comment on the engine; The transactions parameter indicates whether event handling is supported; The savepoints parameter indicates whether the save point is supported. So that the transaction is rolled back to the savepoint. Another show statement queries the supported storage engine information.

VARIABLES LIKE ‘have%‘;

Querying the default storage engine using the show statement

VARIABLES LIKE ‘storage_engine‘;

Modify the default storage engine to InnoDB

Modify the MY.CNF configuration file and add it after [mysqld]

default-storage-engine=InnoDB

Restart database

10. Database tables

A table is the basic unit of data stored in a database. A table contains a number of fields or records.

Note: When you create a table, you first select the database, and the statement that selects the database is the use database name.

use xiessdb;

Create a table named MYDB01 under the XIESSDB database

mysql> CREATE TABLE mydb01(id INT,name VARCHAR(20),sex BOOLEAN);

Where the ID field represents shaping; the Name field represents a string type; the Sex field represents a Boolean.

Delete Table syntax, example: Delete Table mydddd0

DROP TABLE mydddd0;

Sets the primary key for the table. A primary key is a special field of a table that uniquely identifies each piece of information in the table.

The primary key is made up of a field that can be set directly after the field with the PRIMARY key, the syntax rule: property name data type PRIMARY key

New table MYDB02, setting stu_id as the primary key

mysql> CREATE TABLE mydb02(stu_id INT PRIMARY KEY,stu_name VARCHAR(20),stu_sex BOOLEAN);

Multi-field primary key. A primary key is a combination of multiple attributes that are set uniformly after the property is defined. Syntax rules: PRIMARY KEY (property name 1, property name 2,... ）

New table mydb03, setting stu_id,course_id as the primary key

mysql> CREATE TABLE mydb03(stu_id INT,course_id INT,grade FLOAT,PRIMARY KEY(stu_id,course_id));

Sets the foreign key for the table. A foreign key is a special field of a table that establishes an association between the table D and its parent table. The principle is that the primary key of the parent table exists, and the foreign key can be a null value. Basic syntax rules: CONSTRAINT alias FOREIGN KEY (Attribute 1.1, Property 1.2 ...) Prferences Table Name (property 2.1, Property 2.2 ...)

Sets the non-null constraint for the table. Non-nullability refers to the basic statement rule that the value of a field cannot be null (NULL) to set a non-null constraint: The property name data type is not NULL

INT NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL);

Sets the uniqueness constraint for a table. Uniqueness constraint means that the value of the field in all records cannot recur. The basic syntax rule for uniqueness constraints is: Property name data type unique

INT PRIMARY KEY,stu_id INT UNIQUE,name VARCHAR(20) NOT NULL);

Sets the property value of the table to automatically increase. Auto_increment is a special constraint in the MySQL database that automatically generates a unique ID for the new record inserted in the table. By default, the value of this field is increased from 1 onwards. The basic syntax rule is: Property name data Type Auto_increment

INT PRIMARY KEY AUTO_INCREMENT,stu_id INT UNIQUE,name VARCHAR(20) NOT NULL);

Sets the default value for a table property. The default values are set by the default keyword, and the basic syntax rule is: Property name data Type default defaults

Second, view the table structure

1. Statements that view the table structure include the describe statement and the show CREATE table statement. The describe statement can query the basic definition of a table. Includes the field name, the field data type, whether the primary key and the default value, and so on. The basic syntax is: DESCRIBE table name;

Mysql> DESCRIBE mydb05;+--------+-------------+------+-----+---------+-------+|Field |Type | Null |  Key |  Default |  Extra |+--------+-------------+------+-----+---------+-------+|  ID |  int (11) |  NO |  PRI |  NULL |  | |  stu_id |  int (11) |  YES |  UNI |  NULL |  | |  name |  varchar (20) |  NO |  |  NULL |  |+--------+-------------+------+-----+---------+-------+       

2. View table Detail Structure Statement show CREATE table. The statement can view information such as the field name of the table, the data type of the field, the integrity constraint, and the default storage engine and character encoding for the table. The basic syntax is: SHOW CREATE table table name;

mysql> SHOW CREATE TABLE mydb05;+--------+--------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------+| Table |                                                                                                                                                                                                   Create Table |+--------+-------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------+| mydb05 | CREATE TABLE' mydb05 ' ( ' id ' int (one) not null, ' stu_id ' int (one) ' DEFAULT null, ' name ' varchar () not NULL, PRIMARY Key (' id '), UNIQUE key ' stu_id ' (' stu_id ')) engine=innodb DEFAULT charset=latin1 |+--------+------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------+

Third, modify the table name

The table name is unique in the database. Implemented by the statement alter TABLE. The basic syntax is: ALTER table old table name RENAME [to] new table name;

TABLE mydb06 RENAME mysqldb006;

The ALTER Table statement can also modify the data type of the field, with the basic syntax: Altel table name MODIFY property name data type;

The Altel Table statement can also modify the field name of the table, with the basic syntax: Altel table name change old property name new data type;

The Altel table statement can also increment a field with the basic syntax: Altel table name Add property name 1 data type [integrity constraint] [first | After property name 2];

ALTER TABLE mydb06 ADD phone VARCHAR(20);mysql> ALTER TABLE mydb06 ADD age INT(4) NOT NULL;

Note: By default, the new field is in the last field of the table, plus the first parameter, which allows you to set the new field to the field in the table. Specify the location increment field, plus the After property name 2 parameter, the new field is inserted after property name 2.

The Altel Table statement can also delete the fields of the tables, the basic syntax is: Altel table name DROP property name;

Altel Table statement modifies the position of the field, the basic syntax is: Altel table name MODIFY property name 1 data type First | After property name 2;

The Altel table statement changes the storage engine of the table, with the basic syntax: Altel table name engine= storage engine name;

mysql> ALTER TABLE mydb06 ENGINE=MyISAM;

The Altel Table statement deletes the FOREIGN KEY constraint for the table, the basic syntax is: Altel table name DROP FOREIGN key foreign key alias;

Iv. deletion of tables

MySQL uses the Dorp table statement to delete tables, the basic syntax is: drop table name;

DROP TABLE mydb06;

Note: Delete the parent table that is associated with another table. Delete the constraints of the other tables before deleting the parent table.

MySQL Database common commands