Summary of MySQL basic common commands

Summary of MySQL basic common commands

MySQL basic common commands

Note: The MySQL version installed in centos is 5.7. When editing MySQL, there will be an error message. You need to execute:

set@@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

1. SQL Statement

Each command ends with a semicolon

  • Query all databases: show databases;
  • Switch database: use library name;
  • Create a database: create database [IF NOT EXISTS] library name;
  • Delete database: drop database [IF EXISTS] library name;
  • Query database creation: show database creation statement;
  • Specify the character set used by the database: CHARACTER SET
  • Modify the database code set: alter database database name CHARACTER SET code set;

Note: Do not modify the encoding set of the MySQL server. The encoding set of the table is consistent with the library by default.

2. Create a table

Format:

  • create table [if not exists] table name (
  • Field 1 Data Type Field Properties,
  • Field 2 Data Type Field Properties,...
  • Field N Data Type Field Properties
  • )engine=engine default charset=encoding set;
  • View the current database: select database();
  • View the table creation statement: show create table table name;
  • View the table structure:desc table name;
  • Delete: drop table [if exists] table name;

3. Field attributes

  • not null: No value is given as the default value (the default value of varchar is empty
  • AUTO_INCREMENT defines the column as an auto-increment attribute, generally used for primary keys, and the value will automatically increase by 1
  • The PRIMARY KEY keyword is used to define a column as a primary key. You can use multiple columns to define a primary key, separated by commas.
  • ENGINE sets the storage engine, CHARSET sets the encoding
  • default null: if no value is given, the data is null
  • default value: Set the default value of the field

Note: Primary key columns are not repeated

Here we create a student table:

 create table if not EXISTS student (
 id int auto_increment,
 `name` VARCHAR(32),
  age int,
 sex char(1),
 clazz VARCHAR(32)) charset utf8;



insert into student values ​​(1001,'zs',18,'男','班一');
insert into student values ​​(1002,'ls',19,'女','班二班');
 insert into student(`name`,age,sex,clazz) values ​​('ww',69,'男','班一班');
 insert into student(`name`,age,sex,clazz) values ​​('we',21,'女','班二班');
insert into student(`name`,age,sex,clazz) values ​​('ld ',23,'男','班一班');
insert into student(`name`,age,sex,clazz) values ​​('lq',45,'女','班二班');
insert into student(`name`,age,sex,clazz) values ​​('lwq',23,'男','班一班');
 insert into student(`name`,age,sex,clazz) values ​​('ld',12,'女','班二班');

4. Modify the table: alter table

Modify the table name: alter (rename) table old table name to new table name;

rename table student1 TO `student`;

Add field: alter table table name add field field data type attribute;

 alter table student add job varchar(32) default 'No job';
insert into student (job) VALUES('a');
insert into student (job) VALUES('b');
insert into student (job) VALUES('c');
insert into student (job) VALUES('a');
 insert into student (job) VALUES('b');

Modify fields: alter table table name change old field new field data type attribute;

 alter table student change clazz clazz varchar(255);
 alter table student change age score double;

Modify fields: alter table table name modify field data type attributes;

alter table student MODIFY varchar(356); #This space cannot be smaller than the previous space

Notice:

  • change: modify all (field name, data type, attributes)
  • modify: modify part (data type, attribute)
  • When changing the data type, the varchar->int metadata will become 0

5. Add, delete, modify and check: all strings are wrapped with ''

5.1 Increase

Format:

insert into table name (field) values ​​(value), (value)... (value);
 insert into student values ​​(1001,'zs',18,'男','班一');
insert into student values ​​(1002,'ls',19,'女','班二班');
insert into student(`name`,age,sex,clazz) values ​​('ww',69,'男','班一班');
insert into student(`name`,age,sex,clazz) values ​​('we',21,'女','班二班');
insert into student(`name`,age,sex,clazz) values ​​('ld ',23,'男','班一班');
insert into student(`name`,age,sex,clazz) values ​​('lq',45,'女','班二班');
insert into student(`name`,age,sex,clazz) values ​​('lwq',23,'男','班一班');
10 insert into student(`name`,age,sex,clazz) values ​​('ld',12,'女','二班');

5.2 Delete

 -- Delete the delete from table name where clause;
 delete from student where job='c';

5.3 Change

 -- Change update table name to set field 1 = value 1, field 2 = value 2 ... field N = value N where clause;
update student set job = 'b' where name = 'ls';

5.4 Check

-- Check the select field from table name where clause;
 select * from student ; #Query all SELECT id as di,name,job,score from student where score>18; #Specific query, and display a specific table as: indicates changing the field name (the original table does not change)

Note: Indicates all fields

6. Clauses

  • > < <= >= = <> Greater than, Less than, Greater than (less than) equal to, Not equal to
  • between ...and... displays the value in a certain range (including the beginning and the end)
  • in(set) The value displayed in the in list, for example: in(100,200) can only match 100 or 200
  • Like '张_' Fuzzy query uses % and _ (% means match all _ means match one)
  • Is null determines whether it is empty
  • and multiple conditions are met at the same time
  • or any of the following conditions are met
  • not is not true, for example: where not(expection>10000);
-- > < <= >= = != greater than, less than, greater than (less than) equal to, not equal to SELECT * from student WHERE id>1006;
SELECT * from student WHERE id!=1006;
 
--between ...and... Display the value in a certain range (including the beginning and the end)
select id,name,job from student where id BETWEEN 1002 and 1005;
 select * from student where job BETWEEN 'a' and 'b';
 -- in(set) The value displayed in the in list, for example: in(100,200) can only match 100 or 200
 select * from student where job in('a','b');

-- like '张_' Fuzzy search uses % and _ (% means match all _ means match one)
 SELECT * from student where name like 'l%';
 SELECT * from student where name like 'l_';
select * from student where name is not null;

7. Limit paging

Format:
The statement limit starts the subscript, length;

-- Limit paging statement limit start subscript, length; Note: there is no where
select * from student LIMIT 1,2;
select * from student LIMIT 0,2;
select * from student LIMIT 2;

Notice:
If the data volume is insufficient, display all

8. Deduplication

Format:
DISTINCT field1, field2...fieldN

 -- Remove duplicates DISTINCT field 1, field 2... field N
 select DISTINCT name from student;
 select count(DISTINCT name) from student;

Notice:

The field cannot be before DISTINCT, but can only be after DISTINCT

There are multiple fields after DISTINCT, and duplicate removal is performed according to all fields

9. Aggregate Functions

  • count(field): how many rows of data are required
  • sum(field): sum
  • avg(field): average
  • max(field): maximum value
  • min(field): minimum value

Notice:

  • Varchar can compare the size, but cannot get the avg value (which is meaningless)
  • If the value is Null, it will not be included in the calculation.
  • The data in the sum and avg fields are not numerical, and the results are both 0

 -- count(field): find the number of rows of data select count(*) from student;
 select count(name) from student;

-- sum(field): sum select sum(score) from student;
select sum(job) FROM student;
select name+score as sum FROM student; #score valueSELECT name*score as cheng FROM student; #0

--avg(field): average SELECT avg(score) FROM student;
 -- max(field): maximum value SELECT max(score) FROM student;
SELECT max(job) FROM student; #c
-- min(field): minimum value SELECT min(score) FROM student;

10. Splicing

Format 1

concat(str1,str2...)

Format 2:

concat_WS(separator,str1,str2,...)

-- Format 1: concat(str1,str2...)
 select CONCAT(id,'-',name) as pj FROM student;
 -- Format 2: concat_WS(str1,str2...)
SELECT CONCAT_WS('~',id,name,score,job)FROM student; #separated by ~

11. Date functions

Get the current date:

current_timestamp;--all current_timestamp();--all CURRENT_DATE();--year month day CURRENT_DATE;--year month day CURRENT_TIME();--hour minute second CURRENT_TIME;--hour minute second--Get the current date:
-- current_timestamp;--all SELECT CURRENT_TIMESTAMP from student;
-- current_timestamp();--all SELECT CURRENT_TIMESTAMP() from student;
 -- CURRENT_DATE();-- year month day select CURRENT_DATE() from student;
-- CURRENT_DATE;-- year month day select CURRENT_DATE from student;
-- CURRENT_TIME();-- Hours, minutes, seconds SELECT CURRENT_TIME() FROM student;
-- CURRENT_TIME;-- Hours, minutes, seconds SELECT CURRENT_TIME FROM student;

Time to str

Format:
date_format(date,format)
date:時間
format:格式

str to date

Format:
str_to_date(str,formaat)

SELECT * FROM date;
 -- Convert time to str
 -- Format:
 -- date_format(date,format)
-- date: time -- format: format select DATE_FORMAT('2021-09-01','%Y~%m~%d');
-- str to date -- Format:
 -- str_to_date(str,formaat)
 SELECT STR_TO_DATE('2021-09-01','%Y-%m-%d');

Subtracting dates

Format:
datediff(expr1,expr2);

Note: Only year, month, and day can be subtracted. The result of the operation involving hours, minutes, and seconds is null.

datediff(expr1,expr2);
-- Note: Only year, month, and day can be subtracted. The result of the operation involving hours, minutes, and seconds is null.
SELECT DATEDIFF('2021-09-09','2021-09-01');

This function adds a specified time interval to a date.

Format:
DATE_ADD(date,INTERVAL expr unit);
date: time
INTERVAL:Keyword
expr: the value of the interval
unit: year month day hour minute second (..,...,day,..,..,..)

SELECT DATE_ADD('2021-09-09',INTERVAL +10 YEAR);
SELECT DATE_ADD('2021-09-09',INTERVAL +10 DAY);

12. Array Calculation

round(x,d): rounding
x: value
d: How many decimal places to keep

ceil(x): round up
floor(x): round down
rand(): Random number (between 0 and 1)

-- Array calculation -- round(x,d): rounding -- x: value -- d: how many decimal places to keep SELECT ROUND(1.3,2); #2 means how many decimal places to keep -- ceil(x): round up SELECT ceil(1.2);
-- floor(x): round down SELECT floor(1.2);
-- rand(): random number (between 0 and 1)
 SELECT rand();

13. Sorting

Format:
order by 字段1 asc|desc,字段2 asc|desc...字段n asc|desc;

SELECT * from student ORDER BY score,job;
 SELECT * from student ORDER BY score desc, job desc;

Notice:

  • Default ascending order asc, descending order desc
  • If there are multiple fields, sort them in order.

14. group by

Format:

group by 字段1,字段2...字段n;

Notice:

  • Multiple fields, group by all fields (group together)
  • How many groups are displayed and how many data are displayed (by default, no conditional filtering is performed)
  • The data displayed in the group is the default first data in each group
  • by is usually used with aggregate functions
select max(score) as c from student where score=c;
select max(score) as c from student having score=c;
Neither can run SELECT count(*),job,`name`,id as c from student GROUP BY sex where c>2; #Error SELECT count(*) as c,job,`name`,id from student GROUP BY sex HAVING c>2;

-- select id,name,sex from student where job='a'; # Can be run --select id,name,sex from student having job='a'; # Cannot be run (no job after display)
-- The execution process is from-where-select-having
-- select count(*) c from student where c>1; -- No -- select count(*) c from student having c>1;-- OK select count(*) c,sex from student group by sex where sex='男';
select count(*) c,sex from student group by sex having sex='男';


--where having is used together with SELECT count(*)as c,name,id FROM student where sex='男' HAVING c>3;
where is to filter the data from the table;
Having is to select the selected data in the table;

This is the end of this article about the summary of MySQL basic commonly used commands. For more relevant MySQL commonly used commands, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Summary of Common Commands for Getting Started with MySQL Database Basics
  • MySQL Database Basics: A Summary of Basic Commands
  • MySQL database basic commands (collection)
  • Getting started with MySQL Basics Learn MySQL commands easily
  • Summary of basic commands for MySQL learning

<<:  In-depth analysis of the various backgrounds, usage scenarios and techniques of CSS

>>:  Hide HTML elements through display or visibility

Recommend

Detailed tutorial on installing pxc cluster with docker

Table of contents Preface Preliminary preparation...

Detailed explanation of Vue3's sandbox mechanism

Table of contents Preface Browser compiled versio...

Discussion on horizontal and vertical centering of elements in HTML

When we design a page, we often need to center th...

Concat() of combined fields in MySQL

Table of contents 1. Introduction 2. Main text 2....

Detailed explanation of MySQL event modification events (ALTER EVENT), disabling events (DISABLE), enabling events (ENABLE), event renaming and database event migration operations

This article uses examples to describe the operat...

Usage of Vue filters and timestamp conversion issues

Table of contents 1. Quickly recognize the concep...

js implements some functions of the input component in Element and encapsulates it into a component (example code)

Currently implemented are basic usage, clearable,...

Detailed explanation of the problem of matching even when there is a space at the end of the string in the Mysql query condition

1. Table structure TABLE person id name 1 you 2 Y...

How to modify the default storage engine in MySQL

mysql storage engine: The MySQL server adopts a m...

Some tips on website design

In fact, we have been hearing a lot about web des...

Detailed explanation of the use of the MySQL parameter sql_safe_updates in the production environment

Preface In case of application bug or DBA misoper...

Bootstrap 3.0 study notes grid system case

Preface In the previous article, we mainly learne...

How to quickly query 10 million records in Mysql

Table of contents Normal paging query How to opti...

A brief discussion on docker compose writing rules

This article does not introduce anything related ...

Without too much code, you can use hyperlinks to implement simple and beautiful custom check boxes

Today I suddenly thought that the styles of check ...