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

Basic operation of MYSQL:

RDBMS function:

1. Database creation, deletion and deletion

2. Create tables, delete tables, modify tables

3. Creation and deletion of index

4. Users and permissions

5. Add, delete and change data

6. Query

Mysql login command:

-u USERNAME # specifies the user name. If not added, the default is root.

-p # password, left empty by default

-h MYSER_SERVER # specifies the server, not localhost or-h 127.0.0.1 by default

# mysql-u root-p-h localhost # enter will let you enter the password. The password for mysql default root is empty.

How the client and server connect on the same machine:

Linux: socket

Windows: memory

How Mysql clients interact with servers:

1. Interactive mode

2. Batch mode: execute mysql script

Command categories in interactive mode:

2. 1. Client command

2. 2. Server-side commands: all must use statement Terminator, default is semicolon

Database data type:

1. Character type:

CHAR (n): fixed length n, maximum 256characters

VARCHAR (n): variable length less than or equal to n, up to 65536 characters

BINARY (n): case-sensitive fixed length

VARBINARY (n): case-sensitive variable length

TEXT (n): large objects in text format, bulk data

BLOB (n): binary format large object

2. Numerical type:

2.1. Integer:

The range of TINYINT:1 bytes, that is, 8 bits (range from-128to 128b)

SMALLINT:2 byte

MEDIUMINT:3 byte

INT:4 byte

BIGINT:5 byte

2.2, floating point type:

FLOAT

DOUBLE

3. Modifier:

UNSIGNED: positive number or 0

NOT NULL: cannot be empty

4. Date and time

DATE

TIME

DATETIME

STAMP: timestamp

Database language, MYSQL common commands:

1. DDL:Data Defination Lanuage: data definition language

CREATE, ALTER, DROP

1.1. Create a database:

CREATE DATABASE db_name

CREATE DATABASE [IF NOT EXISTS] db_name

Mysql > CREATE DATABASE jmmj; # create a database mysql > SHOW DATABASES; # View all databases mysql > CREATE DATABASE IF NOT EXISTS jmmj; # create the database if it doesn't exist

1.2. Delete the database:

DROP DATABASE [IF EXISTS] db_name

Mysql > DROP DATABASE IF EXISTS jmmj; # delete the database if it exists

1.3. Create a table:

CREATE TABLE tb_name (col1,col2,...)

Mysql > USE jmmj # which database to use mysql > CREATE TABLE test (name CHAR (20) NOT NULL,age TINYINT UNSIGNED,gender CHAR (1) NOT NULL); # create table test

1.4. View the tables in a database:

SHOW TABLES FROM db_name

Mysql > SHOW TABLES FROM jmmj; # View tables in database jmmj

1.5. View the table structure in the database:

DESC tb_name

Mysql > DESC test +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | name | char (20) | NO | | NULL | | age | tinyint (3) unsigned | YES | | NULL | | gender | char (1) | NO | | NULL | | +-+- -+ 3 rows in set (0.00 sec)

1.6. Delete table command:

DROP TABLE tb_name

1.7. Modify the table command:

Mysql > help ALTER TABLE; # Command help

ALTER TABLE tb_name MODIFY | CHANGE | ADD | DROP.

MODIFY: changing field properties

CHANGE: change the field name

ADD: add a field

DROP: delete a field

Mysql > ALTER TABLE test ADD classes VARCHAR (100) NOT NULL; # add a classes field mysql > DESC test # look at the effect +-+-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | name | char (20) | NO | | NULL | | age | tinyint (3) unsigned | YES | | NULL | | gender | char (1) | NO | | NULL | | classes | varchar (100) | NO | | NULL | | +-+-+ 4 rows in set (0.00 sec)

2. DML:Data Manapulate Language: data manipulation language

INSERT, REPLACE, UPDATE, DELETE

2.1. Insert the row command in the table:

INSERT INTO tb_name (col1,col2,...) VALUES | VALUE ('STRING', NUM,...)

INSERT INTO tb_name (col1,col2,...) VALUES | VALUE ('STRING',NUM,...), (' STRING',NUM,...)

Mysql > INSERT INTO test (name,age,gender,classes) VALUE ('liming','19','m','one'); # insert a row of mysql > INSERT INTO test VALUE (' han × ×','18','18', the last one) # if all fields are inserted, the previous field name can be omitted mysql > INSERT INTO test (name,age,gender,classes) VALUE ('lintao','19','m','one'), (' tom','','m','two'); # you can also insert mysql > SELECT * FROM test in bulk # check the effect +-+ | name | age | gender | classes | +-+ | liming | 19 | m | one | | han × × × | 18 | m | one | | lintao | 19 | m | one | | tom | 0 | m | two | +-+ 5 rows in set (0.00 sec) |

2.2. Update the row commands in the table:

UPDATE tb_name SET column=value WHERE

Mysql > UPDATE test SET gender='f' WHERE name='han × ×'; # modify the gender value of the row whose field name is han × × × is fmysql > SELECT * FROM test # check the effect +-+ | name | age | gender | classes | +-+ | liming | 19 | m | one | | han × × × | 18 | f | one | | lintao | 19 | m | one | | tom | 0 | m | two | +-+ 5 rows in set (0.00 sec) |

2.3. Delete the row command in the table:

DELETE FROM tb_name WHERE CONDITION

Mysql > DELETE FROM test WHERE name='liming'

3. SELECT: database query language

Query table command:

SELECT (col1,col2,...) FROM tb_name WHERE CONDITION

*: all fields

WHERE: there is no condition to show all lines

Mysql > SELECT * FROM test # query all fields in the test table +-+ | name | age | gender | classes | +-+ | han × × × | 18 | f | one | | lintao | 19 | M | one | | tom | 0 | m | two | | liming | 20 | m | one | +-+ 4 rows in set (0.00 sec)

4. DCL:Data Control Language: data control language

GRANT, REVOKE

4.1. Command for granting permissions:

GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST' [IDENTIFIED BY' PASSWORD']

Mysql > GRANT ALL PRIVILEGES ON *. * TO root@'%' IDENTIFIED BY '666users; # create (if GRANT does not exist, automatically create a user) A user root (root@'%') that can be logged in on all terminals. Set the password to 666 and grant (ALL PRIVILEGES) mysql > FLUSH PRIVILEGES; # rereading permissions to all places in all databases (*. *)

4.2. Revoke the permission command:

REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM 'USERNAME'@'HOST'

4.3. View authorization commands:

SHOW GRANTS FOR 'USERNAME'@'HOST'

Mysql > SHOW GRANTS FOR 'lee'@'%'

5. MYSQL user management:

5.1. User storage location:

Mysql > SELECT User,Host,Password FROM mysql.user +-+ | User | Host | Password | +- -- + | root | localhost | * 007D50CA06F69776D307B1BEC71CD73D0EA0999C | | root | 127.0.0.1 | root |:: 1 | localhost | | | lee |% | * 007D50CA06F69776D307B1BEC71CD73D0EA0999C | | root |% | * 007D50CA06F69776D307B1BEC71CD73D0EA0999C | +-- + |

5.2. Create user commands:

CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY' PASSWORD']

HOST representation: note that the host here specifies the scope of the client that can log in to the server with this user, and% represents all hosts

IP:

HOSTNAME:

NETWORK:

Wildcard characters:

_: match any single character, 172.16.0._

%: match any character

Mysql > CREATE USER 'lee'@'%' IDENTIFIED BY' 666clients; # create a user with a lee password of 666.You can log in to the server from any client

5.3. Delete user commands:

DROP USER 'USERNAME'@'HOST'

Mysql > DROP USER lee@'%'

5.4. Set and modify user passwords:

1. Mysql > SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD (' password')

Mysql > SET PASSWORD FOR 'root'@'localhost'=PASSWORD (' 666'); mysql > FLUSH PRIVILEGES; # rereading permission

2. # mysqladmin-uUSERNAME-hHOST-p password' password'

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

6. MYSQL graphics client tools:

1 、 phpMyAdmin

# wget https://files.phpmyadmin.net/phpMyAdmin/4.7.7/phpMyAdmin-4.7.7-all-languages.zip# unzip phpMyAdmin-4.7.7-all-languages.zip# cp phpMyAdmin-4.7.7-all-languages/ usr/local/apache/htdocs/-rf# mv phpMyAdmin-4.7.7-all-languages/ phpmyadmin

Http://192.168.8.8/phpmyadmin/ # enter the corresponding address to manage MYSQL

2 、 Workbench

3 、 MySQL Front

4 、 Navicat for MySQL

5 、 Toad

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.