Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the basic commands of the MySQL database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I. Overview of database structure database-> data tables-> rows (records): information columns (fields) used to describe an object: a common data type used to describe an object description int integer float single-precision floating point-4-byte 32-bit double double-precision floating point-8-byte 64-bit char fixed-length character type varchar variable Character type of length text text image picture decimal (5Jing 2) 5 valid length digits There are 2 places after the decimal point

Supplement

Char can store a maximum of 255characters. If the actual length of the data stored in char is less than the specified length, it will fill in the blanks to the specified length. If the actual length of the stored data is greater than the specified length, the lower version will be intercepted and the higher version will report an error.

The length of char is immutable, while the length of varchar is variable, that is to say, define a char [10] and varchar [10]. If what is saved is' csdn', then the length of char is still 10, except for the character 'csdn', followed by six spaces, and varchar immediately changes the length to 4.

Varchar storage rules:

Below version 4.0, varchar (20) refers to 20 bytes. If you store UTF8 Chinese characters, you can only store 6 characters (3 bytes each).

Version 5.0 and above, varchar (20), refers to 20 characters, regardless of whether it is storing numbers, letters or UTF8 Chinese characters (each Chinese character 3 bytes), can store 20, the maximum size is 65532 bytes.

2. View the database structure to view the database in the current server SHOW DATABASES; # is not case-sensitive, semicolon ";" indicates the end

View the USE database name of the table contained in the database; SHOW TABLES

View table structure (field) method 1USE database name; can be abbreviated to: DESC table name; method 2DESCRIBE database name. Table name

III. Introduction of SQL sentence

SQL statements are used to maintain and manage the database, including data query, data update, access control, object management and other functions.

Classification description DDL data definition language, used to create database objects, such as libraries, tables, indexes, etc. DML data manipulation language, used to manage data in tables DQL data query language, used to find qualified data records from data tables DCL data control language, used to set or change database user or role permissions 4. Create and delete databases and tables (DDL) create new database CREATE DATABASE database names For example: create database arts

Create a new table CREATE TABLE table name (field 1 data type, field 2 data type [,...] [, PRIMARY KEY (primary key name)]); # Primary key generally choose fields that can represent uniqueness and are not allowed to take null values (NULL), and a table can only have one primary key. Example: create database arts;use arts;create table star (id int not null,name char (20) not null,sex char (2), primary key (id)); desc star

Delete the specified data table if you do not need USE to enter the library, add the database name DROP TABLE database name. Table name; if you enter the database, add the table name drop table table name directly

Delete the specified database DROP DATABASE database name

5. Manage the data records in the table (DML) insert a new data record INSERT INTO table name into the data table (field 1, field 2 [,...]) VALUES (the value of field 1, the value of field 2,...); for example: create database market;use market;create table star (id int (3) not null,name char (20), sex char (2), age int (3), passwd varchar (50), primary key (id); insert into star (id,name,sex,age,passwd) values (12345678); select * from star

Supplementary password encryption

Query data record SELECT field name 1, field name 2 [,...] FROM table name [WHERE conditional expression]; example: select * from star;select name,sex from star where id=1

Display vertically as a list

Show only the first 2 lines

Show the first three lines after line 2

Modify and update data records in the data table UPDATE table name SET field name 1 = field value 1 [, field name 2 = field value 2] [WHERE conditional expression]; for example: update star set age=33 where name='pp';select * from star

Delete the specified data record DELETE FROM table name from the data table [WHERE conditional expression]; for example: delete from star where id=6;select * from star

Modify table name and table structure modify table name ALTER TABLE old table name RENAME new table name; example: alter table star rename art

Extended table structure (added field) ALTER TABLE table name ADD address varchar (50) default 'address unknown'; # default 'address unknown': indicates that the default value for this field is unknown; can be used with NOT NULL example: alter table star add address varchar (50) default 'address unknown'

Modify the field (column) name to add a unique key ALTER TABLE table name CHANGE old column name new column name data type [unique key]; for example: alter table star change name art_name varchar (20) unique key;select * from star

Delete field ALTER TABLE table name DROP field name; example: alter table star drop address

Extended CREATE DATABASE school;use school;create table if not exists info (id int (4) zerofill primary key auto_increment, # the second way to specify a primary key name varchar (10) not null,cardid int (18) not null unique key,hobby varchar (50)) #-Command interpretation-- # if not exists: checks whether the table to be created already exists. If it does not exist, continue to create # int (4) zerofill: indicates that if the value is less than 4 digits, it will be filled with "0". For example, 0001#auto_increment: indicates that this field is self-growing. That is, each record is automatically incremented by 1, starting from 1 by default. The self-growing field data cannot be repeated; the self-growing field must be a primary key; if the added record data does not specify the value of this field and the addition fails, it will be automatically incremented once # unique key: indicates that this field has a unique key constraint, and the data in this field cannot be repeated; there can be only one primary key in a table, but there can be multiple unique keys in a table # not null: this field is not allowed to be NULL.

7. Data table Advanced Operation Clone Table, generate the data record of the data table into a new table method-create table test1 like info; # through the LIKE method, copy the info table structure to generate test1 table insert into test1 select * from info

Method 2: CREATE TABLE test2 (SELECT * from info); show create table test2\ G; # get the table structure, index and other information of the data table SELECT * from test2

Clear the table and delete all the data in the table method 1: record ID does not delete delete from test1;#DELETE emptying the table, the returned result contains deleted record entries; when DELETE works, it deletes record data row by row; if there is a self-growing field in the table, after deleting all records using DELETE FROM, the newly added records will continue to add and write records from the original largest record ID.

Method 2: delete records after IDtruncate table test2;#TRUNCATE empties the table, no deleted entries are returned; when TRUNCATE works, the table structure is re-established as it is, so TRUNCATE is faster than DELETE in emptying the table; after using TRUNCATE TABLE to empty the data in the table, ID will re-record from 1.

Create a temporary table

After the temporary table is created successfully, the temporary table created cannot be seen using the SHOW TABLES command, and the temporary table is destroyed after the connection exits. You can also perform operations such as additions, deletions, modifications and queries before exiting the connection, such as manually deleting temporary tables using the DROP TABLE statement.

CREATE TEMPORARY TABLE table name (field 1 data type, field 2 data type [,...] [, PRIMARY KEY (primary key name)]); for example: create temporary table test3 (id int (4) zerofill primary key auto_increment,name varchar (10) not null,sex char (2) not null); insert into test3 values (1 from test3;show tables;quit;mysql-u root-pselect * from test3

Create foreign key constraints to ensure data integrity and consistency

Definition of foreign key: if the same property field X is the primary key in Table 1, but not in Table 2, Field X is called the foreign key in Table 2.

Understanding of primary key tables and foreign key tables:

A table with a public key as its primary key (parent table, primary table)

A table with a public keyword as a foreign key (from table, appearance)

Note: the fields of the primary table associated with the foreign key must be set as the primary key. It is required that the slave table cannot be a temporary table, and the fields of the master-slave table have the same data type, character length, and constraints.

# create master table test4create table test4 (hobid int (4), hobname varchar (50)); # create slave table test5create table test5 (id int (4) primary key auto_increment,name varchar (10), age int (3), hobid int (4)); # add a primary key constraint to master table test4. It is recommended that the primary key name start with "PK_" alter table test4 add constraint PK_hobid primary key (hobid) # to add a foreign key from the table test5 and establish a foreign key association between the hobid field of the test5 table and the hobid field of the test4 table. It is recommended that the foreign key name start with "FK_" alter table test5 add constraint FK_hob foreign key (hobid) references test4 (hobid); desc test5

When inserting a new data record, the master table is the first and then the slave table

Insert into test4 values (1 recordable reading'); insert into test5 values (1 recordable admiring recorder 18 pr 1)

When deleting a data record, you must first delete the slave table and then the master table, that is, when you delete the primary key table, you must first delete other tables associated with it.

Drop tables test5;drop tables test4

View and delete foreign key constraints

Show create table test5\ Gram alter table test5 drop foreign key FK_hob;alter table test5 drop key FK_hob;desc test5

Six common constraints in MySQL: primary key constraint (primary key) Foreign key constraint (foreign key) non-empty constraint (not null) uniqueness constraint (unique [key | index]) default value constraint (default) self-increasing constraint (auto_increment) VIII. Database user authorization new user USER 'username' @ 'source address' [IDENTIFIED BY [PASSWORD] 'password] #-explanation part-'user name': specify the user name 'source address' to be created: specify the hosts on which the newly created user can log in, in the form of IP address, network segment, and host name Local users can use localhost to allow any host to log in with the available wildcard% 'password': if you use a plaintext password, enter the 'password' directly, which will be automatically encrypted by Mysql when inserted into the database If you use an encrypted password, you need to first use SELECT PASSWORD ('password'); get the ciphertext, and then add PASSWORD 'ciphertext' to the statement If the "IDENTIFIED BY" section is omitted, the user's password will be empty (not recommended) #-for example: create user 'test1'@'localhost' IDENTIFIED BY' 123456password select password ('123456') Create user 'test2'@'localhost' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

View user information # the created user saves use mysql;select user,authentication_string,Host from user in the user table of the mysql database

Rename user rename user 'test1'@'localhost' to' zz'@'localhost'

Delete user drop user 'test2'@'localhost'

Modify the password of the currently logged in user

The current password is abc123

Set PASSWORD = PASSWORD ('123456')

Modify the password of other users set PASSWORD for 'zz'@'localhost' = PASSWORD (' abc123')

The first solution to forget the root password is to modify the / etc/my.cnf configuration file and log in to mysqlvim / etc/ my.cnf [mysqld] skip-grant-tables # without using the password, so that the login mysql does not use the authorization table systemctl restart mysqld.servicemysql # to log in directly.

Use update to change the root password and refresh the database update mysql.user set AUTHENTICATION_STRING = PASSWORD ('abc123') where user='root';FLUSH PRIVILEGES;quit;mysql-u root-pabc123 Note: finally, delete or comment the skip-grant-tables in the / etc/my.cnf configuration file and restart the mysql service.

IX. Database user authorization grants permissions

GRANT statement: specifically used to set the access rights of database users.

When the specified user name does not exist, the GRANT statement will create a new user

The GRANT statement is used to modify user information when the specified user name exists.

Format GRANT permission list ON database name. Table name TO 'user name' @ 'Source address' [IDENTIFIED BY 'password'] #-Parameter interpretation-permission list: for columns Various database operations authorized to be used Separated by a comma, such as "select,insert,update". Use "all" to indicate all permissions and can authorize any operation. Database name. Table name: the name of the database and table used to specify the authorized operation, where the wildcard "*" can be used. For example, use "test.*" to indicate that the object that authorizes the operation is all tables in the test database. 'username'@ 'Source address': used to specify the user name and the client address to which access is allowed, that is, who can connect and where to connect. The source address can be a domain name, an IP address, or you can use the "%" wildcard to indicate all addresses in an area or network segment, such as "% .test.com", "192.168.19.%", and so on. IDENTIFIED BY: used to set the password string that the user uses to connect to the database. When you create a new user, if you omit the "IDENTIFIED BY" section, the user's password will be empty. For example: # allows the user lili to query the data records of all tables in the kky database locally, but forbids querying the records of tables in other databases. GRANT select ON kky.* TO 'lili'@'localhost' IDENTIFIED BY' abc123';flush privileges;quit;mysql-u lili-pabc123use kky;show tables;select * from info; other authorization examples: # allow user pp to remotely connect to mysql on all terminals and have all permissions. GRANT ALL PRIVILEGES (but not to write) ON *. * TO 'pp'@'%' IDENTIFIED BY' 123456'

View permission SHOW GRANTS FOR user name @ source address; for example: SHOW GRANTS FOR 'lili'@'localhost'

Revoke permissions REVOKE permission list ON database name. Table name FROM user name @ source address; for example, REVOKE SELECT ON kky.* FROM 'lili'@'localhost';SHOW GRANTS FOR' lili'@'localhost';#USAGE permission can only be used for database login and cannot perform any operations; USAGE permission cannot be recovered, that is, REVOKE cannot delete users. Flush privileges;REVOKE ALL ON *. * FROM 'lili'@'localhost'

The above is all the contents of the article "what are the basic commands of MySQL database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.