MySQL Database common operations

Currently the most popular database:

Oracle, MySQL, SQL Server, DB2, Sqline

--: Single line comment

#: Also single-line comment

/* Comment Content */: Multi-line Comment

Mysql-uroot-p Password: Log in to MySQL

Service mysqld Restart
Restart MySQL

/etc/my.cnf
MySQL configuration file

/var/lib/mysql
Database file storage location for MySQL database

/var/log
MySQL database log output storage location

Create database name default character set =utf8;: Creating databases

Show databases: View all databases

Drop database name: delete data

Syntax one for backup:
Mysqldump–uroot–p which database to back up > Where to back up the path;

Backup syntax Two:
Mysqldump–uroot–p--database which database to back up > Where to back up the path;

Source+sql file: Recovering a Database

Use database name: Using the Business database

Show tables: See how many tables are in the business database

DESC Table Name: Description Table structure

Build table Syntax:
CREATE TABLE table name (column name data type, l column name 2 data type,..., column name n data type) Engine=innodb Charset=utf8;

After the table is created, add one or more columns:
ALTER table name ADD (column name data type,..., column name n data type);

Change column name syntax:
Alter Table name change column the data type corresponding to the new column name

To delete a column:
ALTER TABLE table name drop column to delete

To add a comment to a column:
Alter table name Modify column name corresponds to data type comment ' What you want to annotate '

Add data syntax:
Insert into table name (column 1, column 2 ...) column n) VALUES (value 1, value 2, ...) Value N)

Add more than one data at a time:
Insert into table name (column 1, column 2,.., column N) values (value 1, value 2,....., value N), (value 1, value 2,....., value N)

To modify the data:
Update table name set column name = value WHERE condition

Update can modify multiple columns at once:
Update table Name set column 1= value 1, column 2= value 2 where condition

Delete data:
Delete from table name where condition

To delete data from an entire table:
TRUNCATE TABLE name

To delete a database:
Drop

To add a default value:
CREATE TABLE table name (column name data type default defaults)

To add a non-null constraint syntax:
ALTER TABLE name modify column name corresponds to the data type not NULL;

To add a primary key constraint to a table:
Alter table name add constraint primary key Pk_ table name _ column name to add constraint (column to add constraint)

To add a foreign key constraint to a table:
ALTER TABLE name ADD constraint foreign key Fk_ table name _ Column name (column to add constraint) references referenced table (which column is referenced)

Select * FROM table name: Query all the data in this table

Select Column name 1,..., column name n from table name: Multi-column Query

Select Column name from table name: Single row query

Select column Name 1 as alias 1, column name N as alias n from table name: name alias when queried

Select distinct (need to remove duplicate columns) from table name: Deduplication (only single-column de-duplication)

SELECT * FROM table name WHERE Condition: query with condition

SQL consists of four parts:

DQL Data Query Language Select

DML Data Manipulation Language Insert,update,delete

DDL Data Definition Language Create,drop,alter

DCL Data Control Language Grant,revoke,commit,rollback

Between and: In ... Between

Select * FROM table name where column name in (range): Value within range

Select * FROM table name where column name is null: Determine if the column has a null value

Select * FROM table name where column name is NOT NULL: Determine if the column has a non-null value;

SELECT * FROM table name where column name like ' _ or% value ': Fuzzy query
_ Match only one character
% matches any character

Sort order BY in database
The Order by is followed by a two word ASC DESC
Syntax: SELECT * FROM table name order by column to sort;

SELECT * FROM table name limit m,n: Starting from M row to take n rows display

Table Connection Syntax:
Inside link Syntax: Select * FROM table 1 join table 2 on PRIMARY key = foreign key
Left outer link syntax: Select * FROM table 1 left JOIN table 2 on PRIMARY key = foreign key
Right outer link syntax: Select * FROM table 1 Right join table 2 on PRIMARY key = foreign key

Table connections:

Step one: See if these are the same columns.
Step two: Determine which table is the primary key for this column
The third step: OK after the direct write on the primary key = foreign key
Fourth Step: Modify *, replace the * number with some need to display the column

Self-connect:

Step one: Split the table into two tables in a named alias
Step two: Find out the relationship between garbage data
Step three: Distinguish which table the data from the left comes from, and which table the data on the right comes from
Fourth step: Start writing on PRIMARY key = foreign key
Fifth Step: Change the * number, show the need to show the data

Character functions:
Concat (column 1, ' character '); Merging functions
Select Concat (username,userpwd) ' username and password ' from user;
Select Concat (' Hello ', ' world ') from dual;

Date function:
Sysdate (); Query the current system time function
Select Sysdate () from dual;
select * from user;
Dual??
Dual is a pseudo-table provided by the database in order to allow us to experiment with functions or functions of the function;
The pseudo-table is actually nonexistent, but can still be used in the database, the table name is called dual
Conversion functions

numeric functions

Other functions

Statistical functions, also known as aggregation functions, are developed specifically for the purpose of counting the values of a column.
Count counts the number of non-empty rows in a column
Sum counts the sum of a column
Max counts the maximum value of a column
Min Statistics the minimum value of a column
Avg counts the average of a column

Count can omit the data type of the column
Avg sum Max min can only be used on numeric types only;

The difference between having and where:
Same point: Having and where are used for conditional filtering
Different points: Having many and group by combined to filter the filter where it is used before grouping

Sub-query syntax:
SELECT * FROM table name where column name in (Select same column from table name)
SELECT * FROM table name where column name comparison operator (select numeric column from table name)
Note: If you nest subqueries in the back of where, be sure to change the * number to the column name behind the where

Syntax for adding indexes:
Alter table name The name of the type index of the add index (the column that needs to be indexed)
ALTER TABLE ' OrderDetail '
ADD INDEX ' orderdetailnum ' (' orderdetailnum ');
Syntax for dropping an index:
Alter table Table Drop Index type index name

Transaction:
Set autocommit=0;
Turn off auto-commit, turn on transactions
Set autocommit=1;
Turn on auto commit, close transaction;
Start transaction; --Open a transaction point, record the data in the original table, and if it goes wrong, go directly above the transaction point
Set autocommit=0; --Open transaction

View: a virtual table that stores the query results;
Features: Hide real table names so people don't know what your table name or column name is really called

Create attempted syntax: CREATE VIEW name as query statement

Delete View syntax: Drop view name

MySQL queries all column names in the table, separated by commas:

Select Group_concat (column_name SEPARATOR ",") from INFORMATION_SCHEMA. COLUMNS
WHERE table_schema = ' database name ' and table_name = ' table name '

MySQL Database common operations