Basic mysql - Common sql statements

Collation of common sql statements

In mysql, common sql statements are:

Change Password:
alter user 'root'@'localhost' identified with mysql_native_password BY 'New password';
alter user 'root'@'localhost' identified with mysql_native_password BY '123456';

DDL (Data Definition Language): Data Definition Language, which is used to define database objects: libraries, tables, columns, etc.

DML (data management language): data operation language, which is used to define the addition, deletion and modification of database records (data).

DCL (Data Control Language): Data Control Language used to define access rights and security levels.

DQL (Data Query Language): a Data Query Language used to query records (data).

Supplement: sql statements are not case sensitive

DDL

DDL operation database

CREATE DATABASE is used to create a database

#create database database name
CREATE DATABASE mydb1;

#create database database name character set encoding method
CREATE DATABASE mydb2 character SET GBK;

#create database database name set encoding method collate collation
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

view the database

#View all databases in the database server
show databases;

#show CREATE database database name;
#View the definition information of the mydb2 database created earlier 
show CREATE database mydb2;

modify the database

#alter database database name character set encoding method
#Check the database in the server and modify the character set of mydb2 to utf8;
ALTER DATABASE mydb2 character SET utf8;

4. Delete database

#drop database database name;
#Delete mydb3
drop database mydb3;
drop database mydb2;

5. Others

#View the database currently in use
select database();

#Switch database
#use database name;
use mydb2;

DDL operation table

establish

#create table name (column name 1 data type [constraint]... Column name n data type [constraint]);
#Note: the table name and column name are user-defined. Multiple columns are separated by commas. The comma of the last column cannot be written [constraint] indicates optional

#Example:
create table student(
sno int primary key ,#Student number, primary key
sname varchar(5)#Student name
);

View table information

#View all tables in the current database
show tabels;

#View table creation details show create table name;
SHOW CREATE TABLE student;

#View the field information of the table
#desc table name
desc student;

Modify table

#Modify column information
#Modify the table name alter table old table name rename new table name;
alter table student rename stu;

#Modify the character set of the table alter table table name character set encoding method
ALTER TABLE stu CHARACTER SET gbk;

#Add 1 column alter table name add new column name data type [constraint];
alter table stu add phone varchar(12);

#Modify column alter table table name change old column name new column name new data type
alter table stu change sno sid int primary key;

#Delete column alter table table name drop column name
aleter table stu drop phone;

Delete table

#drop table name;
drop table stu;

DML

DML is used to add, DELETE and modify data in a table. Don't confuse it with DDL. It mainly includes INSERT, UPDATE and DELETE

Note: in mysql, both string type and date type should be enclosed in single quotation marks; Null value: null

Insert operation insert

#insert into table name (column name) values (data value);
insert into stu(sid,sname) values(1,'Zhang San');

/*
be careful:
1.Multiple columns and column values are separated by commas 
2.The column name should correspond to the column value one by one
3.Non numeric column values need to be enclosed in single quotation marks. The inserted date, like characters, is enclosed in quotation marks.
4.If you insert a null value, use null
*/

#When adding data, you can omit column names - > when adding data to all columns, the order of column values is executed according to the order of columns in the data table
insert into stu values(2,'Li Si');

#Add multiple rows at the same time
insert into stu values(3,'Wang Wu'),(4，'Zhao Liu');

Modify (update) operation update

#update table name set column name 1 = column value 1, column name 2 = column value 2... where condition;
#Change Zhao Liu's name to Chen Liu
update stu set sname='Chen Liu' where sname='Zhao Liu';

delete operationdelete

#Single table deletion: delete from table name where condition
#Delete the student information with student number 3
delete from stu where sid=3;
#Delete all student information in the table
truncate table stu;
/*
be careful:
DELETE Delete the data in the table, and the table structure is still in use; The deleted data can be retrieved
TRUNCATE table Table name; DROP the table directly, and then create the same new table. The deleted data cannot be retrieved. The execution speed is faster than DELETE.
*/

DCL

Create user

#create user username @ specify ip identified by password;
create user test@localhost IDENTIFIED by 'test123';

#create user username @ client ip identified by password; Specify the IP address to log in
create user test@10.4.10.18 IDENTIFIED by 'test123';

#create user username @ '%' identified by password any IP can log in
create user test@'%' IDENTIFIED by 'test123';

User authorization

#grant permission 1, permission 2, Permission n on database name* To user name @ IP; grant the specified database permissions to the specified user
#The test user is authorized to query, insert, update, delete and create the table stu in the database mydb1
grant select,insert,update,delete,create on mydb1.stu to 'test'@'127.0.0.1';

#grant all on . to user name @ IP grants all permissions to all databases to the specified user
grant all on *.* to 'test'@'127.0.0.1'

#User authority query
#show grants for username @ IP;
show grants for 'root'@'%';

#Revoke user rights
#revoke permission 1, permission 2, Permission n on database name* from username @ IP;
REVOKE SELECT ON *.* FROM 'test'@'%' ;

#delete user
#drop user username @ IP;
drop user test@localhost;

DQL

Executing DQL statements in the database will not change the data, but let the database send the result set to the client. The result set returned by the query is a virtual table.

Query keyword: SELECT

SELECT Listing FROM Table name [WHERE --> BROUP BY-->HAVING--> ORDER BY];

/*
Note: the difference between having and where:
1.having It is used to filter data after grouping, and where is used to filter data before grouping
2.having Grouping function (statistical function) can be used later
3.where Grouping functions cannot be used later.
4.where It refers to the conditions of records before grouping. If a line of records does not meet the conditions of where clause, this line of records will not participate in grouping; having is a constraint on the grouped data.
*/

wildcard

/*
_(Underline): any character
%:Any 0~n characters
*/

Aggregate function

/*
Aggregate functions are functions used for vertical operations:
COUNT(Column name): count the number of record rows whose specified column is not NULL;
MAX(Column name): calculate the maximum value of the specified column. If the specified column is of string type, the string sorting operation is used;
MIN(Column name): calculate the minimum value of the specified column. If the specified column is of string type, the string sorting operation is used;
SUM(Column name): calculates the numerical sum of the specified column. If the specified column type is not a numerical type, the calculation result is 0;
AVG(Column name): calculates the average value of the specified column. If the specified column type is not a numeric type, the calculation result is 0;
*/

limit

/*
limit It is used to limit the starting row and total row number of query results.
limit Start subscript to display the number of entries// Start subscript starts at 0
limit Display the number of// Indicates that data is obtained from 0 by default
*/

Common data types:

value type

MySQL supports all standard SQL numeric data types.

These types include strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC) and approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).

The keyword INT is synonymous with INTEGER, and the keyword DEC is synonymous with DECIMAL.

BIT data type saves BIT field values and supports MyISAM, MEMORY, InnoDB and BDB tables.

As an extension of the SQL standard, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT.

The following table shows the storage and range of each integer type required:

type	size	Range (signed)	Range (unsigned)	purpose	Corresponding to basic data types in java
TINYINT	1 byte	(-128，127)	(0，255)	Small integer value	byte
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	Large integer value	short
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	Large integer value	/
INT or INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	Large integer value	int
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	Maximum integer value	long
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	Single precision floating point value	float
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	Double precision floating point value	double
DECIMAL	For DECIMAL(M,D), if M > D, it is M+2, otherwise it is D+2	Values dependent on M and D	Values dependent on M and D	Small value

Date and time type

The DATE and TIME types representing TIME values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a valid value range and a "zero" value. When specifying an illegal value that MySQL cannot represent, the "zero" value is used.

The TIMESTAMP type has a proprietary automatic update feature, which will be described later.

type	Size (bytes)	Range	format	purpose
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	Date value
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	Time value or duration
YEAR	1	1901/2155	YYYY	Year value
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Mixed date and time values
TIMESTAMP	4	1970-01-01 00:00:00 / 2038 the end time is 2147483647 seconds, 2038-1-19 11:14:07 Beijing time, 03:14:07 AM GMT, January 19, 2038	YYYYMMDD HHMMSS	Mixed date and time values, timestamp

String type

String types refer to CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. This section describes how these types work and how to use them in queries.

type	size	purpose
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
TINYBLOB	0-255 bytes	Binary string up to 255 characters
TINYTEXT	0-255 bytes	Short text string
BLOB	0-65 535 bytes	Long text data in binary form
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Medium length text data in binary form
MEDIUMTEXT	0-16 777 215 bytes	Medium length text data
LONGBLOB	0-4 294 967 295 bytes	Maximum text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Maximum text data

Keywords: Java MySQL

Added by sxiix on Sun, 19 Dec 2021 09:50:00 +0200

Programming VIP