MYSQL Common operations

1. Log in to Mysql:mysql-u root-p

2. Creating a Database: Create database name [other options];
Such as: Create Database firstbase character Set GBK;

3.show databases; command to see which databases have been created.

4. To operate on a database, you must first select the database, or you will be prompted with an error.
Two different ways:
1. Specify when logging in to the database, command: mysql-d selected database name (-h hostname)-u user name-P
such as: mysql-d firstbase-u root-p
2. After logging in using the USE statement to specify, command: used database name;

5. Creating a database table: Create TABLE indicates (list name);
such as: CREATE TABLE Student
(
ID int unsigned NOT NULL Auto_increment primary key,
Name Char (8) ont null,
Age tinyint unsigned is not NULL,
);

For some longer statements at the command prompt it may be easy to get wrong, so we can write the statement through any text editor and save it as a createtable.sql file, executing the script through the file redirection at the command prompt.
Open a command prompt, enter: mysql-d samp_db-u root-p < Createtable.sql

The "id int unsigned NOT NULL auto_increment primary key" line is described:

"id" is the name of the column;

"int" Specifies that the column is of type int (with a value range of 8388608 to 8388607), which is then decorated with "unsigned" to indicate that the type is unsigned, at which time the column has a value ranging from 0 to 16777215;
"Not NULL" indicates that the value of the column cannot be empty and must be filled, and the default can be null if the property is not specified;
The "auto_increment" needs to be used in an integer sequence, and the effect is that if the column is NULL when inserting the data, MySQL will automatically produce a unique identifier value that is larger than the existing values. Only one such value can be in each table and the column must be an indexed column.
Primary key "means that the column is the primary key of the table, the value of this column must be unique, and MySQL will automatically index the column.
CHAR (8) indicates that the stored character length is 8, the value of the tinyint range is 127 to $, and the default property specifies a value when the column value is empty.

6. You can create a file name with a suffix of. SQL to hold the SQL statement, and then use the command: source file path to execute.


7. Solve garbled problem: Add character SET gb2312 after variables that require Chinese

Here's an example:
CREATE TABLE Carlist (
Number char CHARACTER SET gb2312 NOT NULL,
Name Char (CHARACTER) SET gb2312,
Price float,
Year date,
PRIMARY KEY (number)
);

*8. When the table is already built, but the encoding format is not set, you can use the following command to change the encoding format of the entire table.

ALTER TABLE Table-name convert to character set GBK;

9. Changes to the post-creation table:
The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

9.1
To add a column:
Basic form: ALTER TABLE name add column list data type [after insertion position];
For example: The last appended column of the table Address:alter table students add address char (60);
Inserts a column after the column named Age birthday:alter table students add birthday date after age;

10.CURD: (Adding and removing check and change operation)

Add: INSERT into table name (column name 1, column name 2) VALUES (value 1, value 2);

Delete: Delete from table name (where ...);

Check: Select Column Name 1, column Name 2 (*) from table name (where Name= ');

Change: Update table name set column Name 1 = ' ', Column name 2= ' (where name= ');


I. Modifications to the post-creation table:

The ALTER TABLE statement is used to modify the table after it is created, using the following basic usage:

1. Add Column: Basic form: ALTER TABLE name add column list data type [after insertion position];

Such as:

The last appended column of the table Address:alter table student add address char (a) NOT null default '-';

Insert column after column named Age: Birthday:alter Table student add birthday date not null after age;

2. Modify columns: Basic form: ALTER TABLE name change column Name column new name new data type;

Such as:
Rename the table Tel column to Telphone:alter table student Change Tel telphone char (dafault '-');

Change the data type of the Name column to char (+): ALTER TABLE name name CHAR (+) not null;

3. Delete Column: Basic form: ALTER TABLE name drop column name;

Such as:

Delete birthday column: ALTER TABLE student drop birthday;

4. Rename table: Basic form: ALTER TABLE name rename a new name;


5. Delete entire table: Basic form: drop table name;


6. Delete the entire database: Basic form: drop database name;

7. Use ALTER to modify the default value of a field

Alter TABLE TESTALTER_TBL ALTER I SET DEFAULT 1000;

8. Use the ALTER command and the drop clause to remove the default value for the field

Alter TABLE TESTALTER_TBL ALTER I DROP DEFAULT;

9. View the data table type you can use the Show Table STATUS statement.

SHOW TABLE STATUS like ' Carlist ' \g

10. Get the complete structure of the data table:

SHOW CREATE TABLE runoob_tbl \g;



----Change the root user password:

Set password for user name @localhost = password (' new password ');

Example:mysql> set password for [email protected] = password (' 123 ');


Two.
1. Like clause:

The percent% character is used in the SQL like clause to denote any character, similar to the asterisk * in UNIX or regular expressions.

If percent% is not used, the LIKE clause has the same effect as equals sign =.

such as: SELECT * from Runoob_tbl WHERE runoob_author like '%com '; the sentence will find all ends in COM.


2. UNION operator

The MySQL UNION operator is used to concatenate the results of more than two SELECT statements into a result set.
Multiple SELECT statements remove duplicate data.

Such as:
Select Country from Websites UNION (All) Select country from Apps ORDER by country;
If you bring all, duplicate data is not removed. Order BY IS sort.

3. ORDER BY clause

You can use the ASC or DESC keyword to set the query result to be sorted in ascending or descending order. By default, it is sorted in ascending order.

such as: SELECT * from Carlist ORDER by date ASC;

4. DROP TABLE IF EXISTS ' employee_tbl ';

Usually put in the first sentence in the. sql file.

5. Use of JOIN: used to query data in two or more tables.

You can federate multiple table queries using Mysql JOIN in SELECT, UPDATE, and DELETE statements.

INNER join (inner JOIN, or equivalent connection): Gets a record of the field matching relationship in two tables.
Left join: Gets all the records of the left table, even if the right table does not have a matching record.
Right join: The opposite of the left join is used to get all the records of the right table, even if there are no matching records for the table.

Example:
1. Connect two tables to read the Runoob_count field values for all Runoob_author fields in the RUNOOB_TBL table corresponding to the TCOUNT_TBL table

SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl a
INNER JOIN
Tcount_tbl b on a.runoob_author = B.runoob_author;

The above SQL statements are equivalent to:
SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl A, tcount_tbl b
WHERE
A.runoob_author = B.runoob_author;

2. With RUNOOB_TBL as the left table and TCOUNT_TBL as the right table, the left join reads all the data from the data table on the right side, even if there is no corresponding data in the table on it.

SELECT a.runoob_id, A.runoob_author, B.runoob_count from Runoob_tbl a
Left JOIN
Tcount_tbl b on a.runoob_author = B.runoob_author;


6. NULL Value Processing:

MySQL uses the SQL SELECT command and the WHERE clause to read the data in the data table.

However, the command may not work correctly when the supplied query condition field is NULL.

To handle this situation, MySQL provides three large operators:

Is null: This operator returns True when the value of the column is null.

is not NULL: the operator returns True when the value of the column is not NULL.

<=&gt: The comparison operator (unlike the = operator) returns True when the two value of the comparison is NULL.

The conditional comparison operation on NULL is quite special. You cannot use = NULL or! = NULL to find a null value in the column.

In MySQL, a comparison of null values with any other value (even null) always returns FALSE, that is, NULL = NULL returns FALSE.

Example:
Find out if the RUNOOB_TEST_TBL column in the datasheet is NULL:

SELECT * from Runoob_test_tbl WHERE runoob_count is NULL;

SELECT * from runoob_test_tbl WHERE runoob_count = NULL; (The sentence does not work)

7.MySQL Regular Expressions:

The REGEXP operator is used to match regular expressions in MySQL.

1. ^: matches the starting position of the input string.
2. $: Matches the end position of the input string.
3.. : matches any single character except "\ n". To match any character including ' \ n ', use a pattern like ' [. \ n] '.
4. [...]: Character set. Matches any one of the characters contained.
5. [^ ...]: Negative character set. Matches any character that is not contained.
6. P1|P2|P3: Match P1 or P2 or p3. For example, ' Z|food ' can match "z" or "food". ' (z|f) Ood ' matches "Zood" or "food".
7. *: Matches the preceding subexpression 0 or more times. For example, zo* can match "z" and "Zoo". * Equivalent to {0,}.
8. +: Matches the preceding subexpression one or more times. For example, ' zo+ ' can match "Zo" and "Zoo", but not "Z". + equivalent to {1,}.
9. {n}: N is a non-negative integer. Matches the determined n times. For example, ' o{2} ' cannot match ' o ' in ' Bob ', but can match two o in ' food '.
10.{n,m}: M and n are non-negative integers, where n <= m. Matches at least n times and matches up to M times.

Example:
Look for all the data in the Name field that begins with ' St ':
mysql> SELECT name from person_tbl WHERE name REGEXP ' ^st ';

Look for all the data that ends with ' OK ' in the Name field:
mysql> SELECT name from person_tbl WHERE name REGEXP ' ok$ ';

Find all data that contains the ' Mar ' string in the Name field:
mysql> SELECT name from person_tbl WHERE name REGEXP ' Mar ';

Find all data in the Name field that begins with a vowel character or ends with an ' OK ' string:
mysql> SELECT name from person_tbl WHERE name REGEXP ' ^[aeiou]|ok$ ';

8. mysql transaction is mainly used to deal with large-scale and high-complexity data.
For example, in the Personnel Management system, you delete a person, you need to delete the basic information of people,
Also to delete information related to the person, such as mailbox, articles, etc., so that these database operation statements constitute a transaction!

There are two main methods of MYSQL transaction processing:

1, with BEGIN, ROLLBACK, commit to achieve

Begin a transaction
ROLLBACK transaction Rollback
Commit TRANSACTION Acknowledgement

2, directly with SET to change the MySQL automatic submission mode:

SET autocommit=0 prohibit auto-commit
SET autocommit=1 turn on auto-commit


9. Get Server metadata:

SELECT version () Server release information

SELECT database () name (or null)

SELECT User () current username

SHOW Status Server state

SHOW VARIABLES Server Configuration variables

10. Duplicate data:

Example:
CREATE TABLE Person_tbl
(
First_Name CHAR () not NULL,
Last_Name CHAR () not NULL,
Sex CHAR (10),
PRIMARY KEY (last_name, first_name)
);

1. Prevent duplicate data from appearing in the table:

Set the specified field in the MySQL data table PRIMARY key (primary key) or unique (unique) index to guarantee the uniqueness of the data.

2. Statistical data duplication:

SELECT COUNT (*) as repetitions, last_name, first_name
From Person_tbl
GROUP by Last_Name, first_name
Having repetitions > 1;

In general, query for duplicate values, do the following:

Determine which column contains values that may be duplicated.
In the column selection list, use the columns listed by COUNT (*).
The columns listed in the GROUP BY clause.
The HAVING clause sets the number of repetitions greater than 1.

3. Filter duplicate data:

You can use the DISTINCT keyword in a SELECT statement to filter for duplicate data:

SELECT DISTINCT last_name, first_name from Person_tbl;

You can also use GROUP by to read data that is not duplicated in a data table:

Elect last_name, first_name from Person_tbl GROUP by (last_name, first_name);

4. Delete duplicate data:

CREATE TABLE tmp SELECT last_name, first_name,sex from Person_tbl GROUP by (last_name, first_name, sex);
DROP TABLE person_tbl;
ALTER TABLE tmp RENAME to PERSON_TBL;

MYSQL Common operations