Shulou(Shulou.com)06/01 Report--

MySql database operation

-Log in to MySql:

After the Windows environment enters cmd, enter mysql-h localhost-u root-p, and then enter the password to start mysql; where localhost is the ip where the mysql server is located. If it is native, you can use localhost.

-Database related operations

Create database db_name (database name) build a database named db_name

Show databases to view existing databases

Drop database db_name deletes the database named db_name

Use db_name operates on a database named db_name

Show tables displays tables in the database

Desc table_name looks at the structure of the table named table_name

-Operation of TABLE in database

Create table table_name (ID int primary key,NAME varchar (50), AGE int, SEX varchar (10)): create a table and set ID as the primary key

Create table table_name (stu_id int, course_id int,name varchar (20), grade float,primary key (stu_id,course_id)): both stu_id and course_id in the table are set as primary keys

Foreign key if one of the attribute values of table A depends on the primary key of table B, the parent table An is called the foreign key of the field An in the child table A. if the information in the parent table changes, the data of the corresponding child table will also change.

Syntax create table table_name01 (id int primary key,stu_id int,course_id int, score float,grade int,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 with two foreign keys set to the child table

Non-empty constraint create table table_name (id int primary key not null,name varchar (50) not null,stu_id int) of the table field; set not null to indicate that the field cannot be empty, that is, it is non-empty

Unique constraint means that the value in the field cannot repeat create table table_name (id int primary key auto_increment,stu_id int unique,name varchar (20) not null); it sets id to increase automatically and unique sets the value of stu_id to be unique and cannot have the same value.

E, set the default value for the table, that is, replace the default value with the default value when no data is inserted

Create table table_name (id int primary key auto_increment,stu_id int unique,name varchar (50) not null English varchar (20) default 'zero'); that is, the default value for the Englist field is set to zero

Show create table table_name; looks at the detailed structure statement of the table

-the modification of the table-

Modify table name alter table old table name rename [to] new table name; change table name

Modify field properties Alter table table_name modify property name data type (modified type)

Modify field alter table table_name change old field name new field name new data type

Add field alter table table_name add field 1 data type after field 2; add field 1 after field 2; if you change field 2 to FIRST, add it to the front

Delete field alter table table_name drop field name

Modify the location of the field alter table table_name modify field name First (the first location after the field specified in the after field)

Change the engine name of the table alter table table_name engine=Mylsam

Delete table foreign key constraint alter table table_name drop foreign key foreign key alias

Delete the table:

9.1: normal unrelated table drop table table_name

9.2: delete the associated table first use show create table table_name; to view the details of the table and see the alias of the foreign key, delete the foreign key first and then delete the table.

-addition, deletion, modification and search of the database-

Database add (insert into) delete (delete) change (update) search (select) operation

1. Add data insert into

An add data is divided into two types 1 does not specify a specific field name such as insert into table_name values (value 1 value 2 …)

Specify the field name insert into table_name (Field 1, Field 2... .) values (value 1, value 2... .); if you are adding data to a specified field, you only need to write out the field that needs to be added.

Colleagues insert multiple data insert into table_name [field list] values (trade-off list 1) (value list 2).

Insert data from one table into another table

Insert into table_name1 (field list) select (table 2 fields) from table_name2 where conditional expression

two。 Update data (change) operation update

The overall operation is update table_name set field 1 = value 1 field 2 = value 2... Where conditional expression

The data changes in a certain range can be judged mainly from the conditions behind where.

3. Delete data operation delete

Delete from table_name where conditional expression

Delete from table_name; will delete all data

4. Query data query

Select field name 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:

To determine whether the value of a field is in the specified set, find out the select field name or the * 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; look for data that ranges between values 1 and 2; the result contains values at both ends

Matching query with like A complete string can be added by% or;% represents a string of any length such as b% k represents any string that begins with b and ends with k and only represents a single character such as a string of 3 characters that begins with b and ends with k.

Method select * or field name from table_name where field name [not] like condition; when not indicates a mismatch

-Null value query select * or field name from table_name where field name is [not] null;, that is, query data that [no] is empty

And and or multi-conditional query select * or field name from table_name where condition 1 and condition 2; and from table_name where condition 1 and condition 2; so the conditions must be true, while or means that only any one of these conditions can be true

F, the query result does not repeat the select distinct field name from table_name

5. Grouping query

Grouping results with group by alone shows only one record for a group:

Select * or field name from table_name group by field name

The group by and group_concat () functions can display all the fields in each group

Select field name roup_coucat (field name) from table_name group by field name

-group by uses select field name count (field name) from table_name group by field name having count (field name) condition with collection function

Multi-field grouping select * from table_name group by Field 1 Field 2 …

Echelon group by is used with with rollup

Select field name count (field name) from table_name group by field name with rollup

6. Use limit to restrict query data

Select * from table_name limit an or (limit arem b) the former displays data from the first to a, and the latter displays data from a to b

7. Use aggregate functions to query data

Number of count () statistical data entries: Select count (*) from table_name

Sum () summation: Select field name sum (field name) from table_name where condition

Avg () averages: Select avg (field name) from table_name group by field name

Maximum and minimum values of max and min: Select max (field name) / min (field name) from table_name

8. Multi-table join query

Inner join query this field can be used to join tables when there are fields with the same meaning in more than two tables. Such as: select field 1, field 2, field 3. From table_name1,table_name2 where table_name1. Field a=table_name2. Field b

-external join query select field list from table_name01 left/right join table_name02 on table_name01. Field name = talbe_name02. Field name. Letf for left link right for right link

-compound conditional query using multi-conditional accurate query

9. Query with regular expressions

Query records that begin with a specific character select * from table_name where field name regexp'^ a 'begins with a

Query records ending with specific characters Select * from table_name where regexp 'xx$

Use the symbol. In place of any character in the string: Select * from table_name where name regexp'^ l.. y characters'

-aliases for tables or fields--

The alias of the table select * from table_name t where t. Field = value; t is the alias of the table

The alias of the field uses the as keyword such as the select t_id as field id from table_name where t alias = value; t_id is the alias of the corresponding field and can be used like the real name.

-Database backup-

The mysqldump command backs up mysqldump-u username-p db_name table1,table2... . > BackupName.sql;// where db_name is the name of the database table1.. If there is no table name, the entire database will be backed up. Backupname.sql means that the name of the backup file can be preceded by an absolute path.

Back up multiple databases Mysqldump-u username-p-databases db_name1 db_name2... > backupname.sql

Backup 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 is from Brother Company MySQL database training reprint, please indicate the source.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.