Mysql common statements and usage

This article mainly introduces the commonly used statements of mysql. Interested friends can refer to it. I hope it will be helpful to everyone.

1. Basic operations of mysql

1. Start the mysql database service
Format: net start mysql

For example: net start mysql57

2. Close the mysql database service
Format: net stop mysql
For example: net stop mysql57

3. Log in to the mysql service
Format: mysql -u root -p
Such as : mysql -u root -p After successful execution, enter the password,

4. View the version of the current database xinxi
Format: select version();


5. View the current time
Format: select now();


6. Quit,

2. Database database operation

1. Check which databases currently exist

Format: show databases;

2. Create a database

Format: create database database name charset=utf8;

Example: create database wen charset=utf8;

3. Delete a database

Format: drop database database name;

Example: drop database wen;

4. Use database

Format: use database name;

Example: use wen;

5. View the currently used database

Format: select database();

3. Table operations

1. View the current databases Table

Format: show tables;

2. Create a table

Format: create table Table name (field name and type);

Example: create table student(id int auto_increment primary key,name varchar(20) not null,age int not null,address varchar(20),gender bit default 1);

3. View the structure of the table

Format: desc table name;

Example: desc student;

## 4. View the statement that creates the table

Format: show create table Table name;

5. Delete table

Format: drop table Table name;

Example: drop table student;

## 6. Modify the table

        1. Modify the name of the table

Format: rename table old table name to new table name;

Example: rename table student to students;

2. Modify the structure of the table, add | drop | change

1.add A new field

Format: alter table table name add field name type;

Example: alter table student add phone int;

2.change Modification

Format: alter table table name change old field name new field name new field type;

Example: alter table student change phone number varchar(20);

3.drop delete

Format: alter table table name drop field name;

Example: alter table student drop number;

4.Data operation

1. Insert data

1. Insert full column data

Format: insert into table name values(value 1, value 2,....)

Note: The order of values ​​needs to correspond to the structure of the table. The main key can be written as 0.

## Example: insert into student values(0,"Xiaoming",36,"Beijing Chaoyang District",1);

2. Default insertion

Format: insert into table name (field name 1, field Name 2,....) values(value 1, value 2,....)

Example: insert into student(name,age,address) values("小东",48,"Shenzhen Nanshan District");

3. Insert multiple pieces of data

Format: insert into table name values(value 1, value 2,. ...), (value 1, value 2, ....), (value 1, value 2, ...)

Example: insert into student values(0," Xiaoyun",45,"Hangzhou",0),(0,"Xiaoyu",105,"Russia",0),(0,"Xiaoqing",99,"Japan",0);

2. Delete data

Format: delete from table name where conditions

Example: delete from student where age = 105;

delete from student

means deleting all without any conditions, please use with caution

3. Modify data

Format: update table name set field name 1=value, field name 2=value,... where condition;

update student set gender = 1 where name = "Xiaoqing";

update student set age = 52,address="Shenzhen" where name = "Xiaodong";

4. View all data

Format: select * from table name;

Example: select * from student;

5. Query data

1. Query format

1. Query all

Format: select * from table name;

2. Query by condition

Format: select field name 1, field name 2,. .. from table name where condition;

1.select is followed by the field names that need to be displayed in the query results

2. * means that all field names must be displayed

3. From is followed by the table name, indicating which table to query from

4. Where is followed by the filtering conditions

5. You can use the field name as alias to give the field name an alias

Example: display name and age

select name,age from student;

select name as "name" from student;

2. Operations in conditions

1. Comparison operators

        > Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

= Equal to

!= Not equal to

Format: select field name 1, field name 2,... from table name where field name comparison operator value;

Example: select * from student where age < 50;

2. Logical operators

and and

##or or

not

## Format: select field name 1, field name 2,... from table name where field name comparison operator value logical operator field name comparison operator value...;

Example: select * from student where age < 50 and age > 40;

select * from student where not (age < 50 and age > 40);

3. Fuzzy operator like

% represents multiple arbitrary characters

_ represents one arbitrary character

Requirement: Match all people with small surnames

Format: select * from student where name like "小%";

Format: select * from student where name like "小_";
Requirement: Match the names with the word "小"
select * from student where name like "%小%";

4. Range query
in (multiple values) determines whether a value is one of multiple values ​​
between value 1 (inclusive) and value 2 (inclusive) determines whether a value is between value 1 and Between value 2
Format: select field name 1, field name 2,... from table name where field name range operator value;
Example: Find 25 or 45 or 80 or 90
select * from student where age in (25,45,80,90);
Requirement: Find the data between 25 and 45
select * from student where age between 25 and 45;
5. Null judgment
is null is empty
is not null is not empty
Format: select field name 1, field name 2,... from table name where field name is null;
Insert Data: insert into student(name,age) values("小·超",60);
Example: select * from student where address is null;
select * from student where address is not null;

6. Remove duplicate values ​​
Format: select distinct field name from table name where condition;
Example: select distinct gender from student;

7. Aggregation function
count (*) Find the total number of pieces of data in the current result
sum(column name) Find the sum of the columns corresponding to the column name
avg(column name) Find the average of the current column
max(column name) Find the current The maximum value of the column
min(column name) Find the minimum value of the current column
Example: How many pieces of data are there in the current table?
Select count(*) from student;
Find the youngest one ?
select min(age) from student;

8. Group group by

Format: select field name... from table name where condition group by field name

Check how many genders there are

Example: select gender from student group by gender;

Requirement: Count how many boys and girls there are

select gender,count(*) from student group by gender;
Requirement: Count the number of all girls?
Example: select gender,count(*) from student group by gender having gender = 1;
where query condition, is executed first Query condition
Having query condition is based on the query result

9. Sorting
Format: select field name... from table name where condition order by field name 1, field name 2...
Example: age from youngest to oldest
select * from student order by age;
The default is from smallest to largest
asc asc from smallest to largest
desc from largest to smallest
select * from student order by age asc;

10. Paging

Format: select field name... from table name where condition limit starting value, how much Piece of data

The starting value can start from 0
Example: select * from student limit 0,3;

Related recommendations:

Introduction to common MySQL statements

Explanation summary of common MySQL statements

Php queries MySQL common statements by time

The above is the detailed content of Mysql common statements and usage. For more information, please follow other related articles on the PHP Chinese website!