Mysql database 1 - basic database commands

Prodigal son Tang Shao 2022-02-13 07:29:12 阅读数:155

mysql database basic database commands

mysql database 1— Database basic command

1.mysql Of shell command

1. Sign in mysql( Get into mysql The installation directory bin Under the table of contents , open cmd):
(1)mysql -u user name -p User password
(2)mysql -u user name -p # Do not enter the password , But when you enter, you are prompted to enter your password 
2. sign out mysql Environmental Science , All three have the same effect :
(1exit2)quit
(3)\q
3. Check the status of the server :
(1status;
4. Show the current mysql Of version All kinds of information ( It can be used in the query window of the client )
(1select version();
5. see MySQL Port number ( It can be used in the query window of the client )
(1show global variables like 'port';
6. Show database list ( It can be used in the query window of the client )
(1show databases;
7. Show the structure of the data table ( It can be used in the query window of the client )
(1describe Table name ;

2. Create database

1. grammar :create database Database name
2. Example :create database test01

3. Delete database

#IF EXISTS Is used to prevent errors when the database does not exist , Not required 
1. grammar :DROP DATABASE IF EXISTS Database name
2. Example :DROP DATABASE IF EXISTS test01

4. Create database tables

1. grammar :CREATE TABLE Table name ( Field list )
2. Example :
CREATE TABLE `user_info` (
`id` int(100) NOT NULL AUTO_INCREMENT COMMENT ' Primary key id',
`phone` varchar(20) NOT NULL DEFAULT '' COMMENT ' Phone number ',
`pass_word` varchar(32) NOT NULL DEFAULT '' COMMENT ' User password ',
`type` tinyint(32) NOT NULL DEFAULT 0 COMMENT ' The user types :0- There is no need to reset the password ;1- Password reset required ',
`create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Modification time ',
PRIMARY KEY (`id`) USING BTREE,-- Set up id Primary key 
KEY `phone` (`phone`) USING BTREE,-- catalog index 
KEY `phoneAndPassword` (`phone`,`password`) USING BTREE -- Set union index 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- AUTO_INCREMENT: Self increasing label 
-- CURRENT_TIMESTAMP: Default current time 
-- CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: Default current time , And use update when , This field changes automatically as the record is updated 
-- USING BTREE: Use BTREE Indexes 
-- ENGINE=InnoDB: Use InnoDB engine 
-- DEFAULT CHARACTER SET utf8mb4: Database character set . Set the default code of the database to utf8mb4
-- COLLATE=utf8mb4_general_ci: Database proofreading rules , Compare and sort , Case insensitive 

5. Delete table

#IF EXISTS Used to prevent errors when the data table does not exist , Not required 
#DROP TABLE You can delete multiple tables at the same time , Just write the table name after it in turn , Separate them with commas 
1. grammar :DROP TABLE IF EXISTS Table name 1, Table name 2, Table name 3
2. Example :DROP TABLE user_info

6. Additions and deletions

(1)SELECT

SELECT Statement to select data from a table .
1. grammar :
grammar 1SELECT Column name FROM The name of the table
grammar 2SELECT * FROM The name of the table
2. Example :SELECT * FROM user_info

(2)UPDATE

Update Statement is used to modify the data in the table .
1. grammar :UPDATE The name of the table SET Column name = The new value WHERE Column name = Certain value
2. Example :UPDATE user_info SET phone = '18318xxxx89' WHERE id = 1

(3)INSERT

INSERT INTO Statement is used to insert a new row into a table .
1. grammar
grammar :INSERT INTO The name of the table VALUES ( value 1, value 2,....)
grammar :INSERT INTO The name of the table ( Column 1, Column 2,...) VALUES ( value 1, value 2,....)
2. Example :INSERT INTO user_info ('phone','pass_word')VALUES ('18318xxxx89','123456');

(4)DELETE

DELETE Statement to delete rows in a table .
1. grammar :DELETE FROM The name of the table WHERE Column name = value
2. Example :DELETE FROM user_info WHERE id = 1

(5)WHERE

WHERE Clause is used to specify the criteria for selection .
1. grammar :SELECT Column name FROM The name of the table WHERE Column Operator value
2. Example :
-- From the table User Selected from name The fields are ' Kobayashi ' The data of 
SELECT * FROM User WHERE name = ' Kobayashi ';

(6)AND and OR

AND - If the first condition and the second condition hold ;
OR - If only one of the first and second conditions holds ;
AND Example :
-- Delete table User Middle field id=1 also Field user_id=8 The data of 
DELETE from meeting where id = 1 and user_id = 8;
OR Example :
-- Query table User The Chinese name is " Kobayashi " perhaps The name is " Xiao Zhang " People who 
SELECT * FROM User WHERE name=' Kobayashi ' OR name=' Xiao Zhang ';

(7)ORDER BY

 By default, statements sort records in ascending order .
ORDER BY - Statement to sort the result set according to the specified column .
DESC - Sort the records in descending order .
ASC - Sort records in ascending order .
Example :
-- Counter table User Middle field age Sort 
SELECT name,age FROM User ORDER BY age;
-- Keep up DESC Is displayed in descending order 
SELECT name,age FROM User ORDER BY age DESC;
-- Name displays the company name in descending order , And the age is displayed in order 
SELECT name,age FROM User ORDER BY age ORDER BY name DESC, age ASC;

(8)IN

IN - The operator allows us to WHERE Multiple values specified in clause .
IN - The operator is used to specify the range , Each item in the scope , All match .IN Value rule , Separated by commas , Put all in parentheses .
grammar :SELECT " Field name "FROM " Table name "WHERE " Field name " IN (' It's worth one ', ' It's worth two ', ...);
Example :
-- From the table User selection Field name be equal to ' Kobayashi '、' Xiao Zhang '
SELECT * FROM User WHERE name IN (' Kobayashi ',' Xiao Zhang ');

(9)NOT

NOT - Operators are always used with other operators , Used in front of the filter
Example :
-- Query table User Not all of Kobayashi's records 
SELECT * FROM User WHERE NOT name = ' Kobayashi ';

(10)UNION

UNION - The operator is used to merge two or more SELECT The result set of the statement .
Example :
-- List all in the user log table 1(User_Log1) And user log table 2(User_Log2) Different user names 
SELECT name FROM User_Log1 UNION SELECT name FROM User_Log2
-- List User In the table id,name, Task list Task Medium id,task_name Alias set to name Avoid error reporting for different fields , Sort by update time 
SELECT id,name FROM User UNION ALL SELECT id,task_name AS name FROM Task ORDER BY update_at;

(11)AS

as - Can be understood as : Used as a 、 As a , As ; Alias
Generally, the column name or table name is renamed .
grammar :select column_1 as Column 1,column_2 as Column 2 from table as surface
Example :
-- This sentence means to find all User The data in the table , And put User The table is named u
SELECT * FROM User AS u.
-- List tables User Middle field age Column maximum , The result set column does not display age, Show chinese ‘ Age ’ 
SELECT MAX(age) AS ' Age ' FROM User;

(12)JOIN

 Used according to the relationship between columns in two or more tables , Look up data from these tables
JOIN: If there is at least one match in the table , Then go back to the line
INNER JOIN: When there is at least one match in the table ,INNER JOIN Keyword return line .
LEFT JOIN: Even if there is no match in the right table , Also returns all rows from the left table
RIGHT JOIN: Even if there is no match in the left table , Also returns all rows from the right table
FULL JOIN: As long as there is a match in one of the tables , Just go back
Example :
SELECT u.name from User u
left join User_Role ur on u.user_id = ur.user_id
left join Role r on r.role_id = ur.role_id

7.SQL basic function

(1)COUNT

COUNT Calculate how many rows of data there are in the database table ,NULL The value is not included in , such as count Calculate a field , Don't count that field null Worth that line
grammar :SELECT COUNT(" Field name ") FROM " Table name ";
for example : select count(*) from User;

(2)MAX

MAX Function returns the maximum value in a column .NULL Values are not included in the calculation .
grammar :SELECT MAX(" Field name ") FROM " Table name "
for example :select MAX('name') from User;

8. Modification of the table after creation

(1) Add columns

 grammar :alter table Table name add Name Column data type [after Insertion position ];
Example :
-- In the table User The last additional column of address 
alter table User add address char(60);
-- In the table User Middle name is name Insert column after column age: 
alter table User add age int(3) not null default 0 after name;
-- In the table User Middle name is age Insert column after column birthday:
alter table User add column birthday date after age;

(2) Modify the column

 grammar :alter table Table name change Column name List new names New data types ;
Example :
-- Will table User in phone The column was renamed telphone
alter table User change phone telphone char(13) default "-";
-- Will table User in name Change the data type of the column to char(16): 
alter table User change name name char(20) not null;
-- Modify table User in name Notes to ,COMMENT Must be preceded by a type attribute 
alter table User change name name char(25) COMMENT ' Here is the name ';
-- Modify table User Middle field age Properties of , When modifying column properties Use modify
alter table User modify age varchar(20) NOT NULL DEFAULT "" COMMENT "";

(3) Delete column

 grammar :alter table Table name drop Column name ;
-- Delete table User Medium birthday Column :
alter table User drop birthday;

(4) rename table

 grammar :alter table Table name rename The new name of the table ;
Example :
-- rename User The table is Sys_User: 
alter table User rename Sys_User;

(5) Clear table data

-- DELETE Method is DML Language , You can go back , It can be deleted conditionally 
-- TRUNCATE Method is DDL Language , Can't go back , All table contents are deleted by default , Delete faster than delete fast 
Method 1 :delete from Table name ;
Method 2 :truncate table " Table name ";
Example :
delete from User where user_name = ' Kobayashi ';
truncate table User;
copyright:author[Prodigal son Tang Shao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130729093877.html