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

This article mainly gives you a brief talk about some commonly used mysql sentences and usage, related technical terms you can check online or find some related books to supplement, here is not involved, we will go straight to the topic, I hope some commonly used mysql sentences and usage This article can bring you some practical help.

1. Open MySQL database service

Format: net start mysql

Example: net start mysql57

2. Close mysql database service

Format: net stop mysql

net stop mysql57

3. Login to mysql service

Format: mysql -u root -p

for example: mysql -u root -p after successful execution, enter the password,

4. View version of current database xinxi

format: select version();

5. View current time

format: select now();

6. quit,

quit,exit

2. Operation of database

1. See which databases are currently available

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 name;

Example: drop database wen;

4. using the database

format:use database name;

Example: use wen;

5. View current usage database

format: select database();

3. Table operations

1. See which tables are in the current database

Format: show tables;

2. create a table

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

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

3. View table structure

format: desc table name;

Example: desc student;

4. View statements that create tables

Format: show create table name;

5. delete table

Format: drop table name;

Example: drop table student;

6. modify the table

1. Change 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 Add a new field

Format: alter table name add field name type;

Example: alter table student add phone int;

2. change

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(value1, value2,...)

Note: The order of merit needs to correspond to the structure of the table, and 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(value1, value2,...)

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

3. Insert multiple data

Format: insert into table name values(value1, value2,...), (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 condition

Example: delete from student where age = 105;

delete from student

Unconditional means all deleted, please use with caution

3. modify data

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

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. Search by condition

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

1. Select is followed by the field name that needs to be displayed in the query result

2. * Indicates that all field names should be displayed

3. From followed by table name, indicating from which table to look up

4. where followed by yes filter condition

5. You can use the field name as alias to alias a field name

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

= greater than or equal to

40;

select * from student where not (age

40);

3. Fuzzy operator like

% means multiple arbitrary characters

_represents an arbitrary character

Requirement: Match all small people

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

Format: select * from student where name like "small_";

Requirements: Match names with the word "small" in them

select * from student where name like "% Small %";

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 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);

Requirements: Find data between 25 and 45

select * from student where age between 25 and 45;

5. null judgment

is null is null

is not null is not null

Format: select Field Name 1, Field Name 2,... from table name where field name is null;

insert data: insert into student(name,age) values("small·super",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. aggregate function

count(*) Find the total number of items in the current result

sum(column name) Find the sum of the columns corresponding to the column name

avg(column name) Average the current column

max(column name) Find the maximum value of the current column

min(column name) Find the minimum value of the current column

How many items of data does the current table have?

select count(*) from student;

Looking for the youngest?

select min(age) from student;

8. Group by

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

See how many genders there are.

Select gender from student group by gender;

Demand: Statistics on how many boys and girls there are

select gender,count(*) from student group by gender;

Need: Counting the number of girls?

Example: select gender,count(*) from student group by gender having gender = 1;

where query condition, is the query condition executed first

Having query conditions is based on query results

9. sort

Format: select Field Name... from table name where condition order by field name 1, field name 2...

Example: From young to old

select * from student order by age;

The default is from small to large

ASC from small to large

Desc from large to small

select * from student order by age asc

10. paging

Format: select Field Name... from table name where condition limit starting value, how many pieces of data

The starting value can start from 0

Example: select * from student limit 0,3;

Some commonly used mysql sentences and usage will be mentioned to you first. For other related issues, you can continue to pay attention to our industry information if you want to know. Our section content captures some industry news and expertise to share with you every day.

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.