MySQL Database operations

MYSQL Database Operations

– Login mysql:--–

After the Windows environment into CMD, enter Mysql–h localhost–u root–p, and then enter the password to start MySQL, where localhost is the MySQL server is the IP, if it is native can use localhost.

– Database-related operations--–

Create DATABASE Db_name (database name): Built as Db_name

Show databases: View a database that already exists

Drop database db_name: Delete the databases named Db_name

Use db_name: operation of a database named Db_name

Show tables: Show tables in the database

Desc table-_name: View the structure of a table with table name table_name

– Operation of tables (table) in the database--–

CREATE TABLE table_name (idint primary key,name varchar (), age int, SEX varchar (10)): Creating tables and setting IDs as primary keys

Create Tabletable_name (stu_id int, course_id int,name varchar (), Grade Float,primarykey (stu_id,course_id)): Set the table in Stu _ID and course_id two are primary keys

FOREIGN key: If a property value of table A depends on the primary key of table B, then B is the parent table, A is a child table, A is a foreign key in a, and if the information in the parent table changes, the data for the corresponding child table will also change

Syntax: Create TABLETABLE_NAME01 (ID int primary key,stu_id int,course_id int, score Float,gradeint,constraint C_FK (foreign key alias) Foreign KEY (stu_id,course_id) references Table_name02 (stu_id,course_id)); Table_name02 is the parent table, TABLE_NAME01 is the child table, Set two foreign keys for a child table

Non-null Constraint on table field: Create Tabletable_name (ID int primary KEY not null,name varchar () not null,stu_id int); Set NOT NULL indicates that the field cannot be empty. i.e. Non-empty

Unique constraint, which means that the value in a field cannot be duplicated: Create tabletable_name (ID int primary key auto_increment,stu_id int unique,name varchar () not NULL); Where the ID is automatically incremented, and the unique setting of the STU_ID value must be unique, cannot have the same value exists

E, set the default value for the table, that is, the default value is substituted when the data is not inserted;

Create Table table_name (idint primary KEY auto_increment,stu_id int unique,name varchar () not nullenglish varchar (a) de Fault ' zero '); that is, the Englist field is set to the default value of zero;

Show CREATE TABLE table_name; View detailed structure statements for tables

– Table modification Operation--–

Modify table name: ALTER TABLE name of the old table rename [to] new table name; Change table name

Modify Field Properties: Alter tabletable_name Modify Property name data type (modified type)

Modify fields: Alter TABLE table_namechange old field names new field name new data type

Add field: Alter TABLE table_nameadd field 1 data type after field 2, add Field 1 after field 2; If you change field 2 to first, add to the front

Delete field: ALTER TABLE table_namedrop field name

Modify the position of the field: Alter tabletable_name modify field name first (after field, after the specified field)

Change the engine name of the table: Alter Tabletable_name Engine=mylsam;

Delete foreign KEY constraint for table: Alter tabletable_name drop FOREIGN key foreign key alias

To delete a table:

9.1: Normal no associated table: drop table table_name;

9.2: Delete the associated table: First Use show CREATE TABLE table_name; View table details, see the foreign key name, first delete the foreign key, and then delete the table is OK.

– Database additions and deletions change operation--–

Add (insert INTO) Delete (delete) change (select) Operation:

1. Add data INSERT INTO

A add two kinds of data: 1, do not specify a specific field name such as: INSERT INTO table_name values (value 1, value 2 ...)

Specify field name: Insert Intotable_name (field 1, field 2 ...) VALUES (value 1, value 2 ...); If you are adding data to the field you specify, just write the fields you want to add data to

Colleagues insert multiple data: Insert intotable_name [Field List]values (Trade-offs List 1), (List of values 2) ...

Insert data from one table into one table:

Insert into table_name1 (field list) Select (Table 2 field) fromtable_name2 where conditional expression;

2. Update data (change) operation update

The overall operation is: Update table_name SET field 1= value 1, field 2= value 2...where conditional expression

You can change the data in a range, primarily judging by the conditions behind the where

3. Delete Data operation

Delete from table_namewhere conditional expression

Delete from table_name; All data will be deleted;

4. Querying data query

Select field list from table_name [where conditional expression 1] [Group BY field name [having conditional expression 2]][order by field name [ASC (Ascending)/desc (descending)]]

Single-Table query: Select field name from table_name where condition

Query with in keyword:

Determines whether the value of a field is in the specified collection, and the word is: Select field name or * table_name where field name in (value 1, value 2 ...)

Query with between and keyword: SELECT * or field name from table_name where field name between value 1 and value 2; looks for the data that corresponds to the value 1 and the value 2; The result is a value that contains both ends.

A match query with like is a complete string, which can be added as% or;% means a string of any length, such as B%k, that represents an arbitrary string that begins with B, with a K pair of things, and only represents a single character, such as a string of 3 characters that b_k the end of K starting with B

Method: SELECT * or field name from table_name where field name [not]like condition; not = mismatch

-NULL query: SELECT * or field name from table_name where field name is [not]null; query [not] empty data

Multi-criteria query for and and OR: SELECT * or field name from table_name where Condition 1 and condition 2, and the from table_name where Condition 1 and condition 2;and so that the condition must be true, and or means that only any If a condition is established, you can

F, query results are not duplicated: SELECT DISTINCT field name from table_name;

5. Group queries

Group by groups alone, the result shows only one record for a group:

Select * or field name from the TABLE_NAME Group by field name

The group BY and Group_concat () functions use: All fields of each group can be displayed

Select field name, Roup_coucat (field name) from TABLE_NAME Group BY field name

-group by with aggregate function: Select field name, Count (field name) fromtable_name Group By field name having count (field name) condition

Multi-field Grouping: SELECT * Fromtable_name Group By field 1, Field 2 ...

E,group by and with rollup

Select field Name, Count (field name) from TABLE_NAME Group BY field name with rollup

6. Limit query data with limit

Select * from Table_namelimit A or (limit A, b), which shows the data from the first to the A, which shows the data from A to bar B

7. Querying data using aggregate functions

COUNT () Statistics Bar Number: Select count (*) from table_name

SUM () Summation: Select field name, sum (field name) from table_name where condition

AVG () Averaging: Select avg (field name) from TABLE_NAME Group BY field name

Max and min maximum and minimum values: Select max (field name)/min (field name) from table_name;

8. Multi-Table Connection query

-Internal connection query: More than two tables in which the same field exists, you can use this field to join the table for querying. For example: Select field 1, Field 2, field 3...from table_name1,table_name2 wheretable_name1. Field a= table_name2. Field B

-Outer JOIN query: Select field List from Table_name01 left/right jointable_name02 on table_name01. Field name =talbe_name02. Field name. LETF represents the left link, right means the link

-Compound condition query: Using multi-criteria exact query

9. Querying with regular expressions

Query for records beginning with a specific character: SELECT * fromtable_name where field name RegExp ' ^a ' to a header

Query for records that end with a specific character: Select * fromtable_name where regexp ' xx$;

Use the symbol "." To replace any one of the characters in the string: Select * FROM table_name where name RegExp ' ^l. Y$ ';

– Table or field take alias--–

Table Aliases: SELECT * Fromtable_name t where t. field = value; T is the alias of the table

Alias of field: Use as keyword, such as: Select t_id as field ID from table_name wheret_id= value; t_id is the alias of the corresponding field, alias can be used as the real name

– Database Backup--–

mysqldump command backup: Mysqldump–u username–p db_nametable1,table2....>backupname.sql;//where db_name is the name of the database, Table1. is the name of the table, and if no table name is used to back up the entire database, Backupname.sql represents the backup file, preceded by an absolute path

Backing up multiple databases: mysqldump-uusername–p–databases db_name1 db_name2 ... > Backupname.sql

Back up all databases: Mysqldump–u root–p–all-databases > C:\all.sql

Quick Backup with Mysqlhotcopy tool

Database restore: Mysql–u root–p < backup.sql//where Backup.sql is the saved database file

The above sharing from the brother even MySQL database training, reproduced please indicate the source.

MySQL Database operations