Shulou(Shulou.com)06/01 Report--

Explanation of Relational Database and detailed explanation of mysql basic commands under linux

1.RDBMS: relational database with the following functions:

Database creation, deletion, modification

Create tables, delete tables, modify tables

Creation and deletion of index

Create, modify, delete users and permissions

Add, delete, modify data

Query

2.myslq is a relational database. The data engine commands are as follows: commands are not case-sensitive.

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 control language

GRANT, REVOKE

SELECT: query command

3.mysql client login command:

Option:-u: user name, default is root@localhost or root@127.0.0.1

User name format: USERNAME@HOST

-p: user's password. Default is empty.

-h:mysql server

For example, in the mysql server, the local login server command has the same function as the following two commands:

3.1.mysql

3.2.mysql-u root-p-h localhost

Mysql clients are divided into interactive and batch modes

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

Client command such as: quit (or\ Q), enter help or\ h query client command

Server-side commands: statement Terminator must be used, default is semicolon, such as SHOW DATABASES

4.mysql name interpretation

The 4.1.mysqld service listens on the 3306/tcp port, the master groups are all mysql, and 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 column and multiple row row

4.4. Fields: consists of field names, data types, and type modifiers (restrictions)

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

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

CHAR (n): characters, case-insensitive

VARCHAR (n): variable characters, case-insensitive

BINARY (n): binary characters, case sensitive

VARBINARY (n): variable binary characters, case sensitive

TEXT (n): large object data, case-insensitive

BLOB (n): large object binary data, case sensitive

Note: n represents character length

Numerical values: exact values and approximate values

Exact value: represents × × and decimal

Integer: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT

TINYINT:1 byte

SMALLINT:2 byte

MEDIUMINT:3 byte

INT:4 byte

BIGINT:8 byte

Modifier: UNSIGNED, unsigned, only 0 or positive integer

Not empty: NOT NULL

Decimal: DECIMAL

Approximate values: represent floating-point, FLOAT and DOUBLE

Date time: DATE,TIME,DATETIME,STAMP

Built-in: ENUM,SET

5. Create a database

Format:

CREATE DATABASE database name

CREATE DATABASE [IF NOT EXISTS] Database name

For example: create a database mydb

CREATE DATABASE mydb; # if the database exists, an error will be reported. The next statement is available.

CREATE DATABASE IF NOT EXISTS mydb; # if the database exists, no error will be reported

View the database you just created

SHOW DATABASES

6. Delete database

Format: DROP DATABASE [IF EXISTS] database name

For example: delete database mydb

DROP DATABASE mydb

7. Create a tabl

Format: CREATE TABLE table name (field 1, field 2...); switch default database with USE database name #

CREATE TABLE data inventory. Table name (field 1, field 2, etc.)

For example: create table students

USE mydb; # switch database mydb to the current default database

CREATE TABLE students (Name CHAR (15) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR (1) NOT NULL); view the table in the library: SHOW TABLES [FROM database name]; current data is displayed by default

View the structure of the table: DESC table name

DESC students

8. Delete table: DROP TABLE table name

9. Modify table: ALTER TABLE table name

MODIFY: keep field names and modify other properties

CHANGE: even the fields are modified

ADD: adding field

DROP: deleting field

For example: add course field to table students in database mydb

ALTER TABLE mydb.students ADD course VARCHAR (100)

Change the course field name to Courses

ALTER TABLE students CHANGE course Courses VARCHAR (100) AFTER Name

DESC studens

10.DML: data manipulation commands:

INSERT INTO table name (field 1, field 2, etc.) VALUES | VALUE ('string value', numeric value,...)

INSERT INTO table name (field 1, field 2, etc.) VALUES | VALUE ('string 1, numeric 1, etc.), (' string 2, numerical 2, etc.)

For example: insert Name fields to be Willow and Amy and their Gender field related attribute content

INSERT INTO students (Name,Gender) VALUE ('Willow','M'), (' Amy','F')

SELECT * FROM students

If no field is specified, all fields will be assigned values, such as adding the name to Rob and all its properties

INSERT INTO students VALUE ('Rob','CCNP',26,'M')

SELECT * FROM students

UPDATE table name SET field = value [WHERE condition]

For example: change the Courses field of Name to willow user to RHCA

Update students SET Courses='RHCA' WHERE Name='Willow'

DELETE FROM table name WHERE condition

For example: delete all data of a user whose name is Amy

DELETE FROM students WHERE Name='Amy'

SELECT * FROM students

Select: SELECT field FROM table name WHERE condition

*: all fields

WHERE: there is no condition to show all lines

For example: choose to view the name Willow, and only view the contents of the Name and Courses fields

SELECT Name,Courses FROM students Where Name='Willow'

11. Create a user:

CREATE USER 'username' @ 'HOST' [IDENTIFIED BY' password']

DROP USER 'user name' @ 'HOST'

HOST: indicates that there is an IP,HOSTNAME,NETWORK, wildcard

_: match any single character, 172.16.0._

%: match any character

User1@'%' logs in on all hosts on behalf of the user user1 user

12.DCL: data control command

GRANT permission 1, permission 2. ON database name. Table name TO 'username' @ 'HOST' [IDENTIFIED BY' password']

REVOKE pri1,pri2,... ON database name. Table name FROM 'username' @ 'HOST'

View the user's authorization: SHOW GRANTS FOR 'username' @ 'HOST'

ALL PRIVILEGES: stands for all permissions

For example: create a mydb database where user willow can log in to a mysql server on all hosts

CREATE USER 'willow'@'%' IDENTIFIED BY' redhat'

SHOW GRANTS FOR 'willow'@'%'

Authorized willow users have all permissions on all tables in mydb data

13. Set the password for the user:

13.1.mysql > SET PASSWORD FOR 'username' @ 'HOST'=PASSWORD (' password')

Mysql > FLUSH PRIVILEGES; # refresh let mysql database reread the authorization table

For example: set the password to redhat for root@localhost users

SET PASSWORD FOR 'root'@'localhost'=PASSWORD (' redhat')

13.2.# mysqladmin-u username-hHOST-p password 'password' # bash command to set password

13.3.mysql > UPDATE user SET Password=PASSWORD ('password') WHERE USER='root' AND Host='127.0.0.1'

For example: set the password to redhat for root@127.0.0.1 users

UPDATE user SET Password=PASSWORD ('redhat') WHERE USER='root' AND Host='127.0.0.1'

13.4. Establish a root user with all permissions and only allow connections to the mysql server within the 1.1.1.0 / 24 subnet

GRANT ALL PRIVILEGES ON *. * TO 'root'@'1.1.1.%' IDENTIFIED BY' redhat'

FLUSH PRIVILEGES

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.