Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the MySQL database collection commands". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Common commands for databases

1. Connect to the database

Mysql-u username-p password

two。 Show existing database

Show databases

3. Create a database

Create database sqlname

4. Select a database

Use database sqlname

5. Show tables in the database (select the database first)

Show tables

6. Displays the version information of the current database and the connection user name

Select version (), user ()

7. Delete the database (delete directly without prompting)

Drop database sqlname

two。 Commands for tables in the database

1. Create a tabl

(1) grammar:

Create table tablename (

Field 1 data Type Field Properties

...

Field n

);

(2) Note:

1. To prevent conflicts with reserved words when creating a table, enclose it with''

two。 One-line comment: # …

Multiline comment: / … /

3. When creating a table, multiple fields are separated by English commas, and the last line is not comma-free.

(3) Field constraints and attributes

1. Non-null constraint not null (fields are not allowed to be empty)

two。 Default constraint default (set default)

3. Unique constraint unique key (uk) (sets the value of the field to be unique and can be empty, but there can be only one null value)

4. Primary key constraint primary key (contention) (as the unique identity of the table record)

5. Foreign key constraint foreign key (fk) (used to establish a relationship between two tables, you need to specify which field of the main table is referenced. Foreign keys are supported by InnoDB in the storage engine of the database, but not by MyISAM.

The field as a foreign key is required to be the primary key in the primary table (single field primary key)

Add a foreign key constraint:

CONSTRAINT FK_ foreign key name FOREIGN KEY (foreign key field in the word table) REFERENCES associated table name (associated field).

Grandid as the foreign key of the word table

1. Set auto _ increment=n from increment, starting with n.

two。 Set self-increment set @ @ auto_increment_increment=m with a step size of m.

3. Multi-field setting primary key: primary key (field 1, field 2... Field n)

4. Comments / explanatory text in the table:) comment= "explanatory text"

5. Set character set:) charset= "character set"

6. View the structure of the table: describe' table name'/ desc table name

7. View the database definition: show create database sqlname

8. View Datasheet definition: show create table tablename

9. View the default storage engine: show variables like'storage_engine%'

11. Specify the storage engine for the table:) engine= storage engine

10. Delete table: drop table 'tablename'

11. Get current date: now ()

twelve。 Modify the table:

(1) modify the table name: alter table old table name rename new table name

(2) add fields: alter table table name add field name data type. ; (add a new field)

(3) modify fields: alter table table name change original field name new field name data type.

(4) Delete field: alter table table name drop field name

(5) add primary key constraints after creating the table:

Alter table table name add constraint primary key name primary key table name (primary key field)

(6) add a foreign key constraint after creating the table (the field as the foreign key is required to be the primary key in the primary table (single field primary key):

Alter table table name add constraint foreign key name foreign key (foreign key field) references associated table name (associated field)

Insert data

1. Insert a single row of data:

Insert into table name (field name list (comma separated)) values (values list (comma separated))

two。 Insert multiple rows of data:

Insert into table name (field name list) values (values list 1), … , (values list n)

3. Insert the query results into the new table:

Create table new table (select field 1, … , from original table)

Query id,name,sex,phone data from student table and insert it into newstudent table: CREATE TABLE newstudent (SELECT id, `name`, sex,phone FROM student)

3. Update data (modify data):

Update table name set column name = update value where update condition

Modify the data name of id=1001 in newstudent table to tom:UPDATE newstudent SET `name` = 'tom' WHERE id=1001

4. Delete data

(1) delete from table name where deletion condition

Delete deletes the entire piece of data, not just a single column.

Delete the data named tom in the newstudent table: DELETE FROM newstudent WHERE `name` = 'tom'

(2) truncate table deletes data:

Truncate table deletes all the rows in the table, but the table structure, columns, constraints, indexes, etc., will not change. Cannot be used for tables with foreign key constraints. Deleted data cannot be recovered.

Truncate table table name where delete condition

Data query

1. Use select query

Select column name / expression / function / constant from table name where query condition order by sorted column name asc/desc

(1) query all data rows and columns:

Select * from table name

(2) query some rows and columns:

Select listing... From table name where query condition

(3) use column aliases in the query:

Select column name AS new column name form table name where query condition

Calculate and merge to get a new column name:

Select column name 1 room.column + column name 2 AS new column name from table name

(4) query null value:

Determine whether the column value is empty by is null or is not null

Query the name of the student whose Email is empty in the student table: SELECT `name` FROM student WHERE Email IS NULL

two。 Grouping query

# query the average score of different courses, the lowest score and the highest score, and query the course name of SELECT r.subjectno, sub.`SubjectName` course name, AVG (StudentResult) average score, MAX (StudentResult) highest score, MIN (StudentResult) lowest score FROM result r INNER JOIN `account`SubjectNo` = sub.`SubjectNo` GROUP BY r.subjectno#where AVG (StudentResult) > = 80. An error occurred in # grouping query group by after the where statement # group by constraints use the hawing statement HAVING AVG (StudentResult) > = 80

Common function

1. Aggregate function:

(1) AVG (average): select avg (column name) from table name

Assuming that the column is named score, the query will find the average of all the scores in the table.

(2) count returns the number of rows in a field

(3) the maximum number of fields returned by max

(4) min returns the minimum value of a field

(5) sum returns the sum of a field.

two。 String function:

(1) concat () connection string s1pm S2... Sn is a complete string.

(2) insert starts the string S1 from the p1 position and replaces the n-character-long string with the string news.

(3) lower (s) changes all characters in the string s to lowercase.

(4) upper (s) changes all characters in string s to uppercase.

(5) substring returns a substring of length len starting at the num position of the string s.

3. Time and date function:

(1) get the current date: curdate ()

(2) get the current time: curtime ()

(3) get the current date and time: now ()

(4) the return date date is the week of the year: week (date)

(5) return date date year: year (date)

(6) return the hourly value of time time: hour (time)

(7) return the minute value of the time time: minute (time)

(8) return date parameter (number of days between date1 and date2): datediff (date1,date2)

(9) calculate the date parameter date plus the date after n days: adddate (date,n)

4. Mathematical function

(1) returns the smallest integer greater than or equal to the value x: ceil (x)

(2) returns the largest integer less than or equal to the value x: floor (x)

(3) return the random number between 0 and 1: rand ()

Order by clause

The order by clause sorts the query results in a certain order, asc ascending and desc descending.

Limit clause

Displays the record of the specified number of rows at the specified location.

Select field name list form table name where constraint group by grouped field name order by sort column name limit position offset, number of rows

# query the first four records of gid=1 in the student information in ascending order by student number

# query student information: gid=1 sorts the first four records by student number in ascending order (step) SELECT id, `name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4; (query the top four students of gid=1 in all the information in the query table)

Query results:

# query the first four records (position offset, step size) SELECT id, `name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4 in the student information in ascending order of the student number; (all the information in the query table gid=1 the first four student information after the first four items)

Query results:

Fuzzy query

In subquery * not in subquery

Use the in keyword to make the parent query match multiple single field values returned by the child query.

Solve the problem that using the comparison operator (=, >, etc.), the subquery returns a non-unique error message.

Like fuzzy query

LIKE statement syntax format: select * from table name where field name like corresponding value (substring).

It is mainly aimed at character fields, and its function is to retrieve the corresponding substrings in a character field column.

Any string containing zero or more characters: 1. LIKE'Mc%' will search for all strings that begin with the letter Mc (such as McBadden).

2. LIKE'%inger' will search all strings that end with the letter inger (such as Ringer, Stringer).

3. LIKE'%en%' will search for all strings (such as Bennet, Green, McBadden) that contain the letter en anywhere.

Any single character: LIKE'_heryl' will search for the names of all six letters ending with the letter heryl (for example, Cheryl, Sheryl).

C: [] specify any single character in the range ([a murf]) or collection ([abcdef]):,

1MagneLIKE' [CK] ars [eo] n 'will search for the following strings: Carsen, Karsen, Carson and Karson (such as Carson).

2. LIKE' [Mmurz] inger' will search for all names that end with the string inger and begin with any single letter from M to Z (such as Ringer)

* D: [^] any single character that does not fall within the specified range ([a murf]) or collection ([abcdef]): LIKE'M [^ c]% 'will search for all names that start with the letter M and the second letter is not a c (such as MacFeather).

E: it is the same as the wildcard in the DOS command and represents multiple characters: cc represents multiple characters such as cc,cBc,cbc,cabdfec.

F:? The same as in the DOS command? Wildcard characters, representing a single character: breadb for brb,bFb, etc.

Granger # is roughly the same as above, except that generations can only represent a single number. Kenzk stands for k1k.k8k.k0k.

F: [!] Rule out that it only represents a single character

Let's give an example:

Example 1, the query name field contains the word "Ming".

Select * from table1 where name like'% Ming%'

Example 2, the query name field begins with the word "Li".

Select * from table1 where name like'Li'

Example 3, query the number in the name field.

Select * from table1 where name like'% [0-9]%'

Example 4, query for lowercase letters in the name field.

Select * from table1 where name like'% [amurz]%'

Example 5, the query name field does not contain numbers.

Select * from table1 where name like'% [! 0-9]%'

You can customize the transfer character-"escape' custom transfer character"

Distinct- "removes duplicates

Between*and fuzzy query

Operator BETWEEN... AND selects a range of data between two values. These values can be numeric, text, or date.

Null, not null query

-- query user data whose mobile number is not null SELECT * from user where phone is not null;-- query user data SELECT * from user where phone is null with mobile number null

Exists subquery not's subquery

The queries subquery is used to confirm whether the subsequent query continues.

Drop table if exists test- > determines whether the table test exists, and deletes it if it exists.

Not exists implements the reverse operation. For records for which there are no corresponding query conditions.

Multi-table join query

A multi-table join query queries data through the association of common columns between tables.

1. Internal join query

The inner join query matches according to the common columns in the table. Take the intersection of two tables. Two tables stored in the primary foreign key relationship are usually queried using inner joins.

Use inner join for internal connections. On keyword or where clause to associate tables.

Inner can omit on to set conditions.

(1) specify the connection condition in the where clause

(2) use inner join in from. On keyword

# query student names and scores SELECT studentname,studentresult FROM student sforce result rWHERE s.`StudentNo` = r.`StudentNo` # use the inner join....on keywords SELECT s.`StudentName`, r.`StudentResult`, r.`SubjectNo`FROM student sINNER JOIN result r ON s.`StudentNo` = r.`StudentNo` in from

The query results of the two methods are the same.

two。 External join query

The join table in the external join query can be divided into master and slave. Each row of the master table matches the data column of the slave table, and the data that meets the join condition is directly returned to the result set. For the column that does not meet the join condition, it will be filled with null value and then returned to the result set.

(1) left outer join query

Left join... On or left outer join... The on keyword is used for association between tables.

SELECT s.`StudentName`, r.`StudentResult`, r.`SubjectNo`FROM student sLEFT JOIN result r ON s.`StudentNo` = r.`StudentNo`

Find out the grades of the students who have no grades.

(2) right outer join query

The right outer join contains all the matching rows in the right table, and some items in the right table have no corresponding items in the left table that will be populated with null values.

Right join... On or right outer join... The on keyword is used for association between tables.

(3) self-connection

Use one table as two tables.

# create a table CREATE TABLE book (id INT (10), sort INT (10), books VARCHAR (10) NOT NULL) # insert data INSERT INTO book VALUES (2pyrrone 'ancient prose book'), (3pyrrine 'modern book'), (4pyrrine 'three hundred Tang poems'), (6pyrrine'me and The Temple of Earth'), ('visit Dalin Temple'), (8pd2''when Wang Youjun minus ten years old'), ('to Oak Tree') # query results are as follows: # Book type, Book name # Ancient document Tri-character Classic. # Modern Book me and The Temple of Earth. SELECT a.books book type, b.books book name FROM book a book bWHERE a.id=b.sort

Self-join query results:

MySQL transactions, views, indexing, backup and recovery

1. Business

Transaction refers to the unified management of a series of data operations as a whole.

Submit or cancel the group couple request to the system as a whole.

Transaction attributes: atomicity, consistency, isolation, persistence.

The myISA storage engine does not support transactions.

Turn off transaction auto-commit: set autocommit=0

(1) start a transaction: begin/start transaction

(2) commit transaction: commit

(3) rollback / cancel transaction: rollback

Resume autocommit: set autocommit=1

Set the result set to? The encoding format shows: set names?

two。 View

A view is a way to view data in one or more tables in a database. A view is a virtual table created as a subset of rows or columns from one or more tables. The view acts as a table filter in the query.

(1) create a view: create view view name as

(2) Delete view: drop view view name

(3) View view data: select. From View name

3. Indexes

An index is similar to a book catalog, where the database program can find the data it needs without scanning the entire table.

(1) normal data: duplicates and null values are allowed.

(2) unique index: duplication is not allowed. There can be multiple unique indexes.

(3) Primary key index: non-empty, unique. Drop primary key on deletion

(4) Composite index: combine multiple columns as an index.?

(5) full-text index: repeatable and null values, created in char,varchar,text.

Where match (column name) against ('find what')

(6) Spatial index: an index established on columns of a spatial data type.

Create an index:

Create [index type] index index name on table name (column that created the index)

Or add the index type after the column when creating the table.

Or modify the table alter table table name add index index name (index column)

Delete index: drop index index name

View index: show index from table name

4. Database backup and recovery

1. Use the mysqldump command to back up the database

Mysqldump-u-p database name > backup database location and name

Export table data to a text file

Select * from table name where query condition into outfile backup database location and name

two。 Use the mysql command to restore the database (create a new database first)

Mysql-u-p newly created database name

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.