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

Table of contents:

1. Mysql user account

2. Basic view command

3. Common components of relational database

4. DDL: Data Defination Language data definition statement

Examples of common commands CREATE, DROP, ALTER

5. DML: Data Manipulation Language data processing statement

Examples of common commands INSERT, DELETE, UPDATE

6. DCL: Data Control Language data control statement

Examples of common commands GRANT and REVOKE

7. DQL: Data Query Language data query statement

Examples of common use of SELECT

8. Data type

9. Define the modifier of the table

10. Index

1. Mysql user account

The ① account consists of two parts: 'USERNAME'@'HOST' HOST is used to restrict which remote hosts this user can connect to the mysql service ② mysql option:-uUSERNAME: username Default is root-hHOST: server host; default is localhost-pPASSWORD: user's password; recommended is-p, default is empty password ③ supports the use of wildcard characters:% matches any character of any length 172.16.0.0max 16 or 172.16.%.% _ matches any single character

2. Basic view command

① show databases; is equivalent to show schemas; # View database list ② select user (); # View login user ③ help [Command] # View Command usage each command is completed in × × × format and abbreviated format, mariadb built-in command non-sql statement command can not be added " "ending example: status or\ s help create database help alter table ④ View supports all character sets: show character set; ⑤ View supports all collations: show collation; ⑥ View supported engines: show engines; ⑦ View Table: show tables [FROM db_name] ⑧ view table structure: desc [db_name.] tb_name; ⑨ view indexes on the table: show indexes from [db_name.] tbl_name

3. Common components of relational database

Database: database Table: table rows: row column: column Index: index View: view user: user permissions: privilege stored procedure: procedure, procedure No return value stored function: function, procedure return value trigger: trigger event Scheduler: event scheduler, Task Scheduler

4. DDL: Data Defination Language data definition statement

Used to manage database lists. Common commands CREATE, DROP, ALTER

① create database create database/schema [IF NOT EXISTS] "database name" ② delete database drop database/schema [IF EXISTS] "database name" ③ creation form create table [IF NOT EXISTS] 'tbl_name' (col1 type1 modifier, col2 type2 modifier,...)

Example:

Create table students (id int unsigned not null primary key,name varchar (20) not null,age tinyint unsigned); # id int unsigned id, define non-negative integer # not null does not allow empty # primary key as primary key # name varchar (20) variable string, display 20-bit # not null is not allowed to be empty # age tinyint unsigned age, minimum non-negative integer cretae table test select * from students # create a test table using the students table as a template, and the two tables have the same content cretae table test select * from students where 1: 0; # with a condition that is not valid, you can only copy the table structure and not copy the table content ④ delete table drop table [IF EXISTS] tb_name ⑤ modified Table: alter table 'tbl_name' Field: add Field: add add col1 data_type [FIRST | AFTER col_name] Delete Field: drop modified Field: alter (default), change (Field name), modify (Field property) Index: add Index: add Delete Index: drop

Example:

Alter table students RENAME S1; # form renamed alter table S1 ADD phone varchar (11) AFTER name; # form adds fields alter table S1 MODIFY phone int; # modify form field data type alter table S1 CHANGE [COLUMN] phone mobile char (11); # modify form field name alter table S1 DROP [COLUMN] mobile # Delete the column alter table students ADD gender ENUM ('masking dint') # add the field gender, and the enumeration can only be m or falter table students CHANGE id sid int UNSIGNEDNOT NULL PRIMARY KEY; # rename the id field to sid, and modify the field type alter table students ADD UNIQUE KEY (name); # modify the field to the unique key alter table students ADD INDEX (age) # modify the field to index alter table students DROP age; # delete the age field

5. DML: Data Manipulation Language data processing statement

Used to manage the data content in the form, commonly used commands INSERT, DELETE, UPDATE

① INSERT syntax:

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...), (...),...

Example:

Insert students values (1 magical magetery 18); # if no fields are specified, you need to fill in insert students (name,id) values ('niu',2); insert students (name,id) values (' niu',2), ('ma',3), (' yang',4); # you can add multiple records insert into test2 select id,name from students; # to import the two fields of students into the test2 table, and the corresponding field data types of the two tables should be the same

② UPDATE syntax:

UPDATE tbl_name SET col1=val1, col2=val2,... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,] n]

Example:

Update students set age=20 where id=2

③ DELETE syntax:

DELETE FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,] n]; # LIMIT [m,] n means to skip the first m and only deal with the last n

Example:

Delete from students where id=2

④ clears the contents of the table (no logging, fast):

TRUNCATE TABLE tbl_name

6. DCL: Data Control Language data control statement

Used to manage user rights, commonly used commands GRANT, REVOKE

Create user: CREATE USER 'username'@'host' [IDENTIFIED BY' password']; delete user: DROP USER 'username'@'host'; change password there are three ways: 1) SET PASSWORD FOR' user'@'host' = PASSWORD ('password'); # recommended password method 2) UPDATE user SET password=PASSWORD (' magedu') password Note: method 2 the command to modify the table will not take effect immediately, you need to execute FLUSH PRIVILEGES effective 3) / usr/local/mysql/bin/mysqladmin-u root-poldpassword password' newpassword' # shell to change the method authorization syntax: GRANT priv_type,... ON [object_type] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY' password'] [WITH GRANT OPTION]; example: GRANT SELECT,DELETE on testdb.* TO 'testuser'@'%'IDENTIFIED BY' testpass'; # authorizes and creates user grant select on wangdb.* to 'wang'@'172.16.%.%' Recycling Authorization: REVOKE priv_type,... ON db_name.tb_name FROM 'user'@'host example: REVOKE DELETE ON testdb.* FROM' testuser'@'%'

Note: when the MariaDB service process starts, it reads all authorization tables in the mysql library to memory.

(1) execute permission operations such as GRANT or REVOKE are saved in the system table, and the MariaDB service process usually rereads the authorization table automatically to make it effective

(2) for commands that cannot or cannot reread the authorization form in time, you can manually allow the MariaDB service to enter

Program reread authorization table: mysql > FLUSH PRIVILEGES

7. DQL: Data Query Language data query statement

Used to view data content, commonly used SELECT

① syntax: SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,] n]; ② field representation: *: all fields as: field aliases, col1 AS alias1 ③ WHERE clause: operator: >, =, = 2 and id

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.