MySQL database base operations-database, user creation, table creation, modification, etc.

MYSQL is a handy, lightweight database. Because of his small size, speed, installation and use of simple, open source and other advantages, is currently the most widely used database. Currently located in Oracle Oracle Corporation. Let's introduce the basic operation of the database today. Describes how to manipulate the database by instruction.

First, there are many software to connect the database, such as Navicat software. The database can be easily and conveniently operated by these software, but today's focus is not here.

Today we are going to operate the MySQL database in a way that commands operations.

We mainly introduce from the following 4 aspects:

1. Database creation, deletion, query and other statements to jump

2. User's creation, deletion, configuration permissions and other statements jump

3. Table creation, deletion, query jump

4. Delete, modify, etc. of the table fields jump

5. Modify constraint Jumps

Then let's start with a concrete introduction.

1. Database creation, deletion, query and other statements

CREATE DATABASE IF  not EXISTS CHARACTER SET ' UTF8 ';

The above statement is judged if the MyDB database does not exist, create it and set it to the UTF8 Character set encoding. If it already exists, it will not be created again.

DROP DATABASE IF EXISTS mydb;

If the MyDB database exists, delete the database, do not delete

SHOW DATABASES;

The query shows that all the databases in this machine

 use Mybiji;

Open using the Mybiji database, all operations on the database must have a database open.

SHOW TABLES from   Mybiji;

Display all data tables for the Mybiji database

2. User creation, deletion, configuration permissions and other statements

CREATE USER ' Fei '@ '%' by'fei';

This is the creation of user Fei. The hostname can be empty, and the default is the% permission, which means that all hosts can connect.

CREATE USER ' Fei '@'localhost'by'fei';

Create User Fei. LocalHost is a local host to access

GRANT  All  on *. *  to ' Fei '@'localhost';

Fei the user with permissions * * All tables for all databases

GRANT SELECT INSERT DELETE  on MySQL. 'userto'feige' @'localhost ';

To add permissions for the specified database MySQL to the user

REVOKE  All  on *. *  from ' Fei '@'localhost';

Delete the user's permissions. All permissions are represented here.

3. Table creation, deletion, query

CREATE TABLE IF  not EXISTSmyTable (IDINTUNSIGNEDPRIMARY KEYAuto_increment,--UNSIGNED unsigned; auto_increment settings auto-grow; PRIMARY key is set to keyword;--PRIMARY KEY (id),' Name 'VARCHAR(255) not NULL,--The name here is the System keyword, so wrap it up in inverted quotes. Normally, the fields are unsigned, just like height.AgeTINYINT UNIQUE, HeightDOUBLE(3,2)DEFAULT 1.2        )

This is the Create data table directive. The parentheses are the fields of the table.

DROP TABLE IF EXISTS myTable;

Delete myTable Data Sheet

 from MyTable;

Display the contents and structure of the table myTable

4. Delete, modify, and manipulate the fields of the table

-- Modify the name of the table. This is  ALTER TABLE The name of the previous name RENAME want; ALTER TABLE tb3 RENAME tb33;

Modify the name of the table. ALTER TABLE The name of the previous name RENAME want;

-- This is to modify multiple table names at the same time.    RENAME TABLE previous name to present name, desired name to desired name; TABLE  to  to Tb22;

This is the name that modifies multiple table names at the same time, RENAME table previous name to now, desired name to want;

-- ALTER table name change old column name the new column list definition is followed by an optional;    First adjusts the current bar to the top column of the table. After is adjusted to a column behind -  where this field does not matter, the primary key at the end of the same ALTERTABLEVARCHAR(200  notNULL after ID;

ALTER table name change old column name the new column list definition is followed by an optional; First adjusts the current bar to the top column of the table. After is adjusted to a column behind
　　

-- Add a new column ALTER TABLE ADD DOUBLE (6,2DEFAULT1.2; -- Delete a column ALTER TABLE DROP height;

Add a column; Delete a column

-- add multi-column fields ALTER TABLE ADD (    DOUBLE(6,2DEFAULT1.2,    int  DEFAULT)

Add multi-column fields

5. Modifying constraints

-- Delete a primary KEY constraint ALTER TABLE DROP PRIMARY KEY ; -- increase the PRIMARY KEY constraint ALTER TABLE ADD PRIMARY KEY (age);

Increase the primary key constraint, and increase the primary KEY constraint

-- New Uniqueness Constraint ALTER TABLE ADD UNIQUE KEY (ID); -- Remove Uniqueness Constraint Note When you delete a uniqueness constraint, the index is created by default, so you delete the index at the same time.  ALTERTABLEDROPINDEX ID;

New uniqueness constraint; Remove uniqueness constraint

-- Set default value Constraints ALTER TABLE ALTER SET DEFAULT  - ; -- Delete a default value constraint ALTER TABLE ALTER DROP DEFAULT;

Setting a default constraint; Deleting a default value constraint

--setting foreign KEY constraintsALTER TABLETb33ADD FOREIGN KEY(CLSID)REFERENCESClasses (ID) on DELETE SET NULL  on UPDATE CASCADE;--Delete a foreign key constraintALTER TABLETb33DROP FOREIGN KEYTb33_ibfk_1;--The runtime is faulty, but the function is implemented, and it does delete the foreign key constraint.--Note that when you delete a foreign key constraint, you also delete the corresponding foreign key indexALTER TABLETb33DROP INDEXTb33_ibfk_1;

Set FOREIGN KEY constraints, delete foreign KEY constraints

-- He can only modify the location of column definitions and columns. Cannot change the name of the column ALTER TABLE VARCHAR (notNULL first ClsID;

　　

The above is the basic database operation instructions, if there are mistakes please friends can correct, let us communicate together.

MySQL database base operations-database, user creation, table creation, modification, etc.