Mysql database Quick Start (1), mysql database Quick Start
1. Database Operations
1.1 connect to the mysql server
Mysql-u root (User Name)-p
1.2 exit the mysql Command Prompt window.
Exit
1.3 view version
Select version ();
1.4 list databases
Show databases;
1.5 create a database
Create database if not exists [database] default charset UTF8;
1.6 Select the database to operate
USE [database];
1.7 delete a database
Grop database [database];
2. Table operations (created on the selected database)
2.1 create a table
Create table if not exists ['tablename'] ('columnname' columnType primary key/...) CHARSET UTF8;
Primary key: Set the field as the primary key;
AUTO_INCREMENT: auto-increment;
Not nul: The field data is not null; otherwise, an error is returned;
CHARSET: sets the encoding format.
2.2 list tables
Show tables;
2.3. query data table Field Information
Show columns from [tableName];
2.4. query detailed index information of a data table
Show index from [tableName];
2.5. query the performance and statistical information of the Management System
Show table status from [database] LIKE '** %' \ G;
** %: Start with something;
\ G: displays by column.
2.6 delete a data table
Drop table [tableName];
2.7. For details about table modification, refer to 4.9.
3. Data Operations (add, delete, modify, and query)
3.1 insert data
Insert into [tableName] (key1, key2) VALUES (val1, val2 );
3.2 delete data
Delete from [tableName] condition;
Condition: WHERE id = 1 /...
3.3 modify and update data
UPDATE [tableName] SET key1 = val1, key2 = val2 condition;
3.4. query data
SELECT */(key1, key2) FROM [tableName];
*: Check all;
Key1, key2: The field to be queried.
4. Clause (combined with addition, deletion, modification, and query operations)
4.1. WHERE
AND: AND conditions
Eg: SELECT */(key1, key2) FROM [tableName] WHERE id = 1 AND name = 'newname ';
OR: OR condition
Eg: SELECT */(key1, key2) FROM [tableName] WHERE id = 2 OR name = 'newname ';
4.2. LIKE % -- fuzzy query allowed
The table contains the following data:
None %: exact query:
,
% ** Before: ended **: SELECT * FROM [tableName] WHERE key LIKE '% val ';
** % After: started with **: SELECT * FROM [tableName] WHERE key LIKE 'val % ';
% ** %: Include **: SELECT * FROM [tableName] WHERE key LIKE '% val % ';
4.3. union all/DISTINCT -- get a set of more than two select statements.
There are two tables:
ALL: Not heavy
DISTINCT: deduplication
4.4. order by key ASC/DESC -- asc ascending and desc descending
4.5. group by -- GROUP statistics
SELECT name COUNT (*)/SUM (key)/AVG (key) AS newName FROM [tableName] group by name with rollup; (the number of group statistics names is displayed in the newName field)
4.6 JOIN -- JOIN
There are two tables:
Inner join (get matching relationship records of two tables): SELECT a. name, B. count FROM [tableName1] a inner join [tableName2] B ON a. name = B. name;
Equivalent statement: SELECT a. name, B. count FROM [tableName1] a, [tableName2] B WHERE a. name = B. name;
LEFT JOIN)
Right join (right join to obtain the records of the RIGHT table, even if the left table does not exist)
4.7, NULL
Is null: The value is null;
Is not null: The value is not null.
4.8. REGEXP -- Regular
SELECT * FROM [tableName] WHERE name REGEXP 'regexp ';
4.9. ALTER -- add, delete, and modify Fields
Original table structure:
ADD a column: alter table [tableName] ADD newKey INT /... not null default 'val 'first/AFTER key; Add a new field newKey. The DEFAULT value is val AFTER the FIRST column or key column.
FIRST: add the field to the FIRST column;
AFTER: add the field to the ** field;
DEFAULT: Set the DEFAULT value of the field.
Delete a column: alter table [tableName] DROP newKey;
Alter table [tableName] CHANGE key newKey INT /...;
Alter table: alter table [tableName] rename to [newTableName];
------- If you have any questions, please point out. For more information, please describe the source.