Linux relational database interpretation and MySQL basic command detailed

Linux relational database interpretation and MySQL basic command detailed

1.RDBMS: relational database, such as the following function:

Database creation, deletion, modification

Create a table, delete a table, modify a table

Creation, deletion of indexes

User and permissions creation, modification, deletion

Data additions, deletions, modifications

Inquire

2.MYSLQ is a relational database and the Data Engine command is as follows: command is case-insensitive

Dml:data manapulate Language: Data Manipulation language

INSERT, REPLACE, UPDATE, DELETE

Ddl:data defination lanuage: Data Definition language

CREATE, ALTER, DROP

Dcl:data control Language: Data Controls language

GRANT, REVOKE

SELECT: Query command

3.mysql Client Login Command:

Option:-u: Username, default to [email protected] or [email protected]

User name format: [Email protected]

-P: User's password, default is empty

-h:mysql Server

For example: in MySQL server, the local login server command, the following two command functions:

3.1.mysql

3.2.mysql-u root-p-h localhost

MySQL client is divided into interactive and batch processing modes

Interactive mode is also divided into client and server-side command categories

Client commands such as: Quit (or \q), you can enter Help or \h query client commands

Server-side command: You must use the statement terminator, which defaults to a semicolon, such as show DATABASES;

4.mysql name interpretation;

The 4.1.mysqld service listens on the 3306/tcp port, the genus belongs to MySQL, the data is stored in/var/lib/mysql/

4.2. Relational database objects: libraries, tables, indexes, views, constraints, stored procedures, stored functions, triggers, cursors, users, permissions, transactions

4.3. Table: Consists of multiple fields field or column columns and multiple rows row

4.4. Field: Consists of field name, data type and type decoration (restriction)

4.5. Data types are divided into characters, values, date and time, built-in and other components

Character: CHAR (n), VARCHAR (n), BINARY (n), VARBINARY (n), TEXT (n), BLOB (n)

CHAR (n): character, case insensitive

VARCHAR (n): Changeable characters, case-insensitive

Binary (n): binary character, case-sensitive

VARBINARY (n): Changeable binary characters, case-sensitive

TEXT (N): Large object data, case insensitive

BLOB (N): Large object binary data, case-sensitive

Note: n represents the character length

Numerical values: Exact values and approximate values

Exact value: Represents a shape and decimal

Integral type: Tinyint,smallint,mediumint,int,bigint

Tinyint:1 bytes

Smallint:2 bytes

Mediumint:3 bytes

Int:4 bytes

Bigint:8 bytes

Modifier: UNSIGNED, unsigned, only 0 or positive integer

Not empty:NOT NULL

Decimals: Decimal

Approximate value: Represents float, float, and double

Date Time: Date,time,datetime,stamp

Built in: Enum,set

5. Create a database

Format:

CREATE database name;

CREATE database [IF not EXISTS] DB name;

Example: Create a database MyDB

CREATE DATABASE MyDB; #如果数据库存在, you will get an error, you can use the following clause

CREATE DATABASE IF not EXISTS mydb; #如果数据库存在, there will be no error.

View the database you just created

SHOW DATABASES;

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M01/80/86/wKioL1dDyojAn0b0AACk8RKfGKY640.jpg "title=" 1.jpg " alt= "Wkiol1ddyojan0b0aack8rkfgky640.jpg"/>

6. Deleting a database

Format: DROP database [IF EXISTS] DB name;

Example: Deleting a database MyDB

DROP DATABASE MyDB;

7. Create a table

Format: CREATE table table name (Field 1, Field 2,...); Use database name #切换默认数据库

CREATE TABLE Data Inventory . Table name (field 1, Field 2,...);

Example: Create a Table students

Use MyDB; #切换数据库mydb为当前默认数据库

CREATE TABLE Students (Name CHAR (null,age) TINYINT unsigned,gender CHAR (1) not NULL); View the tables in the library: show TABLES [from database name]; Display the current data by default

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/80/87/wKiom1dDye3QJ4JBAAFWOzXf_3g289.jpg "title=" 2.jpg " alt= "Wkiom1ddye3qj4jbaafwozxf_3g289.jpg"/>

View the structure of the table: DESC table name;

DESC students;

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/80/86/wKioL1dDzeOxcOEyAAHamM3FLas691.jpg "title=" 3.jpg " alt= "Wkiol1ddzeoxcoeyaahamm3flas691.jpg"/>

8. Delete tables: drop table name;

9. Modify tables: ALTER TABLE name

MODIFY: Preserve field names and modify other properties

Change: Even fields are modified

Add: Adding fields

Drop: Delete Field

Example: Add Course field to table students in database MyDB

ALTER TABLE mydb.students ADD course VARCHAR (100);

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/80/8C/wKiom1dD5S2Sb9O9AAJkpZimcko774.jpg "title=" 4.jpg " alt= "Wkiom1dd5s2sb9o9aajkpzimcko774.jpg"/>

Modify the course field name to courses

ALTER TABLE Students Change course Courses VARCHAR (+) after Name;

DESC Studens;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/80/8A/wKioL1dD54SCZHPEAAFcQQL4wYA545.jpg "title=" 5.jpg " alt= "Wkiol1dd54sczhpeaafcqql4wya545.jpg"/>

10.DML: Data Manipulation command:

INSERT into table name (Field 1, Field 2,...) values| Value (' String value ', numeric,...);

INSERT into table name (Field 1, Field 2,...) values| Value (' String 1 ', value 1,...), (' String 2 ', value 2,...);

Example: Insert Name field Willow and Amy and its gender fields related property content

INSERT into students (Name,gender) VALUE (' Willow ', ' M '), (' Amy ', ' F ');

SELECT * from students;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/80/8C/wKiom1dD5yKRWbdlAAIAy51_bRE491.jpg "title=" 2.jpg " alt= "Wkiom1dd5ykrwbdlaaiay51_bre491.jpg"/>

If you do not specify a field, all fields will be assigned values, such as adding the name Rob and all of its properties

INSERT into students VALUE (' Rob ', ' CCNP ', +, ' M ');

SELECT * from students;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/80/8A/wKioL1dD6LmyTWHZAAD0Ko096M0605.jpg "title=" 1.jpg " alt= "Wkiol1dd6lmytwhzaad0ko096m0605.jpg"/>

UPDATE table name SET field = value [WHERE condition];

For example: Modify the Courses field of name Willow user to RHCA;

Update students SET courses= ' RHCA ' WHERE name= ' Willow ';

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M01/80/8C/wKiom1dD6xChcPH5AAF0uqSP83U668.jpg "title=" 6.jpg " alt= "Wkiom1dd6xchcph5aaf0uqsp83u668.jpg"/>

DELETE from table name WHERE condition;

Example: Remove all data from a user named Amy

DELETE from students WHERE name= ' Amy ';

SELECT * from students;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/80/8D/wKiom1dD8nOST0qoAAFhs7t97j8462.jpg "title=" 7.jpg " alt= "Wkiom1dd8nost0qoaafhs7t97j8462.jpg"/>

Selection: Select field from table name WHERE condition;

*: All fields

WHERE: No condition indicates all rows are displayed;

For example: Select View name Willow, and view only the contents of name and courses two fields

SELECT name,courses from students Where name= ' Willow ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/80/8C/wKioL1dD9a7Cxzn3AAGHSIacGV0043.jpg "title=" 8.jpg " alt= "Wkiol1dd9a7cxzn3aaghsiacgv0043.jpg"/>

11. Create a User:

CREATE user ' username ' @ ' HOST ' [identified by ' Password '];

DROP user ' username ' @ ' HOST ';

HOST: Indicates a ip,hostname,network, wildcard character

_: Matches any single character, 172.16.0._

%: matches any character;

[email protected] '% ' on behalf of user User1 user login on all hosts

12.DCL: Data Control command

GRANT permission 1, permission 2,... on database name. Table name to ' user name ' @ ' HOST ' [identified by ' Password '];

REVOKE Pri1,pri2,... on database name. Table name from ' username ' @ ' HOST ';

To view the user's authorization: Show GRANTS for ' username ' @ ' HOST ';

All privileges: Represents the meaning of all permissions

Example: Create a user willow on all hosts can log in to the MySQL server in the MyDB database

CREATE USER ' willow ' @ '% ' identified by ' Redhat ';

SHOW GRANTS for ' willow ' @ '% ';

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/80/90/wKiom1dD_eniKfFuAAGt7V6TBN4517.jpg "title=" 9.jpg " alt= "wkiom1dd_enikffuaagt7v6tbn4517.jpg"/> Authorize Willow user to have all permissions on all tables in mydb data

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/80/90/wKiom1dD_s3SE5nlAAHgVCZL1Qg619.jpg "title=" 10.jpg "alt=" Wkiom1dd_s3se5nlaahgvczl1qg619.jpg "/>

13. Set the password for the user:

13.1.mysql>set PASSWORD for ' username ' @ ' HOST ' =password (' password ');

mysql> FLUSH privileges; #刷新让mysql数据库重读授权表

For example: Set the password for the [email protected] user to Redhat

SET PASSWORD for ' root ' @ ' localhost ' =password (' Redhat ');

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/80/DB/wKioL1dEDZvyS-qJAAMwNuy7w7w465.jpg "title=" 1.jpg " alt= "Wkiol1dedzvys-qjaamwnuy7w7w465.jpg"/>

13.2.# mysqladmin-u user name-hhost-p password ' password ' #bash下命令设定密码

13.3.mysql> UPDATE user SET Password=password (' password ') WHERE user= ' root ' and host= ' 127.0.0.1 ';

For example: Set the password for the [email protected] user to Redhat

UPDATE user SET Password=password (' Redhat ') WHERE user= ' root ' and host= ' 127.0.0.1 ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/80/DC/wKioL1dEE0fhHB98AAIpcMJCP8Y016.jpg "title=" 2.jpg " alt= "Wkiol1dee0fhhb98aaipcmjcp8y016.jpg"/> 13.4. Create a root user with all permissions and allow only the MySQL server to be connected within the 1.1.1.0/24 subnet

GRANT all privileges on * * to ' root ' @ ' 1.1.1.% ' identified by ' Redhat ';

FLUSH privileges;

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/80/DE/wKiom1dEFi6Skk4oAADNRGs4JKY479.jpg "title=" 3.jpg " alt= "Wkiom1defi6skk4oaadnrgs4jky479.jpg"/>

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/80/DC/wKioL1dEF2PgkviKAAGU7QQrsOQ756.jpg "title=" 4.jpg " alt= "Wkiol1def2pgkvikaagu7qqrsoq756.jpg"/>

This article is from the "Xavier Willow" blog, please be sure to keep this source http://willow.blog.51cto.com/6574604/1782514

Linux relational database interpretation and MySQL basic command detailed