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

This article will explain in detail how to add, delete, modify and query Mysql database tables. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Table operation

1. [increase]

Create table DBname.table_name (id int, namevarchar (255)) engine=MyISAM charset=gbk

2. [delete]

Delete tabl

Drop table table_name

3. [modify]

Modify table options

Alter table table_name engine=myisamcharset=utf8;// modifies character set

Rename table Old_tablename to new_tablename;// modifies the table name

4. [query]

Show tables like'% _ name'; / / View the table

Show create table table_name / / View table creation statement

Desc table_name / / View table structure (describe)

Show variables like 'character_set%';// shows variables that start with character_set

Table field operation

1. [increase]

Add table field

Alter table table_name add column field definition / / add field

Alter table table_name add column heightint after name;// adds a word height field after name

Alter table table_name add column snvarchar (10) first;// adds a sn field at the beginning of the most form and uses the keyword first

2. [delete]

Delete a field

Alter table table_name drop columncolumn_name

Alter table table_name drop columnheight;// deletes the height field from the table table_name

3. [modify]

Modify an existing field (modify field definition)

New definition of alter table table_name modify columncolumn_name

Alter table table_name modify column sn intafter name; / / modify the sn field

Modify field

Alter table table_name change column original field name new field name new field definition

Alter table table_name change column snnew_sn varchar (30) after age;// modify field

4. [query]

View tables and table structure

Show tables like'% _ name'; / / View the table

Show create table table_name / / View table creation statement

Desc table_name / / View table structure (describe)

Table data operation

1. [increase]

Insert into table name (field list) values (list of values relative to the field)

Insert into table_name ('name','age','height') values (ssw,22180)

2. [delete]

Delete from table name where condition; (deletion is irreversible)

When delete, order by and limit are supported to restrict the deletion of data records

Delete from table_name where id > 2

Delete from tb_student order by height desc limit 3

Truncate tb_student;// empties the table

3. [modify]

Update table name set field = new value, field n = new value n where condition; (you can also use order by and limit restrictions)

Update table_name set name='php' where id=3

4. [query]

Select [list of field expressions] from table name [where clause] [group by clause] [having clause] [order by clause] [limit clause]

Select field list from table name [where conditional expression] (* for all fields)

Select * from table_name where id > 6

Select name from table_name where id > 2

Select addition logical relation alias (as) / / select operation

Select 1 to 1; select 10 to 20; select 1 to or 0 to select 1 to 1 as a

Select * from tb1,tb2;// multi-table query

Select tb1.id as s_id, tb2.* from tb1,tb2;// field alias multi-table query

Select tb1.id ass_ id, s.class_name, c. * from tb1 ass, tb2 as cinterbank / table alias

Where clause, query condition clause:

Relationship: > > =

< =11 and id300; Select * from tb1 having money>

300

The difference between Having and where: the result of having must be the result that where has filtered! having carries on the secondary processing to the result

Select avg (age), class_id from tb1 where 1 group by class_id having avg (age) > 16

Order by sort clause

Order by field name [asc | desc], [field name [asc | desc],] / a statement that sorts the results! You can sort multiple fields

Select * from tb1 order by class_id desc, age asc

The principle is to sort first by the first field, if the field values are the same, then the second, and so on

Select class_id from tb1 group by class_id asc order by class_id desc

Limit clause: restrict the clause of the result record, limit start (starting position), size (number of records)

Select * from tb1 limit 1 Pol 3

Execution order: field expression, from clause, where clause, group by clause, having clause, order by clause, limit clause

Subquery statement:

Select max (height) from tb1

Select * from tb1 where height=170

Select * from tb1 where height= (select max (height) from tb1)

Two classification bases for subqueries:

According to the location of the subquery!

Where-type subquery, which appears in the where clause!

From-type subquery, which appears in the from clause!

According to the format of the returned data of the subquery!

Scalar quantum query, the return value is a data, called scalar quantum query!

Column subquery that returns a column

Row subquery, which returns a row

Table subquery, which returns a two-dimensional table

Select * from tb1 where height= (selectmax (height) from tb1); / / where

Yes, first use a query to get the height ranking result, and then group the results

Select* from (select * from tb1 order byheight desc) group by class_id;// from

Select* from (select * from tb1 order byheight desc) as tmp group by class_id

Column subquery:

Select * from tb1 where sex='girl' and class_id in (select class_id from tb1 where sex='body' group byclass_id); / / find the information of male students who have female students in the class.

Select * from tb1 where (height,money) = (selectmax (height), max (money) from tb1)

/ / find, high-rich, the highest and the richest!

Exists type subquery:

Select * from tb1 where exists (select * from tb2 where tb1.class_id = tb2.id)

Join query, join:

Join the connection between multiple table records! From Table name 1 join Table name 2 on connection condition

Select name,class_id,age from tb_xue join tb_ban on tb_xue.class_id=tb_ban.id

You need to get data not only from the student table, but also from the class table.

Internal connection, inner join

The default connection for mysql is inner join

Select stu_name,class_id,class_name fromselect_student inner join select_class onselect_student.class_id=select_class.id; (inner can be omitted)

External connection, left join,right join

Select stu_name,class_id,class_name from select_student left join select_classon select_student.class_id = select_class.id

The left table before the join keyword (left) and the right table after the join keyword!

Left outside: if the left table record is not connected to the right table record, the left table record will appear in the final connection result! The record in the right table is set to NULL accordingly.

Right outside: if the right table record is not connected to the left table record, the right table record will appear in the final connection result! The record in the left table is set to NULL accordingly.

Cross-connect, cross join: equivalent to an unconditional internal connection

Natural connection, natural join: mysql, automatically determine the connection conditions to help us complete the connection!

Select stu_name,class_name from select_class natural join select_student

And the natural connection is also divided into internal connection and external connection!

Natural internal connection: natural join

Natural left outside: natural left join

Natural right outside: natual right join

Summary:

The final effect is only: inside, left outside, right outside!

Crossover, special inside!

Naturally, it is equivalent to automatically judging the connection conditions, completing the inside, left outside, and right outside!

Connection conditions, on,using:

On, followed by a join condition expression!

Using (connection field), it is required to use the same name field to connect!

Select class_name,stu_name from tb_class inner join tb_student on tb_class.class_id=tb_student.class_id

Select class_name,stu_name from tb_class inner join tb_student using (class_id)

Union query, federated query:

Juxtaposes the results of multiple queries into a result set!

(select stu_name,height from tb_student where sex='girl' order by height asc limit 1000) union (select stu_name,height from tb_student where sex='box' order by height desc limit 1000)

Union's connection of the two clauses, do not be realistic with the table, only require the same number of columns!

Union will actively delete the same record when joining: at this point, you can use the all keyword to correct it:

Select 1-1 union all select 2

Options for the select statement:

Distinct, cancel the same record

Select class_id from tb_student

Select all class_id from tb_student

Select distinct class_id from tb_student

IV. Coding

1. Build database, table and field settings (encoding the data in the database)

2. PHP, as the client of the mysql server, sets the client code and connection code (set names gbk/utf8)

3. Set the encoding of the data returned by php to the browser, (Content-Type,header (),)

4. The encoding saved by the PHP file itself (file encoding, set through a text editor)

V. View

View: is to get a virtual table through a query statement! Therefore, the view is the result of the select statement

Function: simplify the business logic of the query and hide the real table structure.

Syntax: create view view name as query statement

Create view view_name as select * from tb_student ass left join tb_class using (class_id)

Select * from view_name where id=22

/ / get the highest student information in each class

Create view view_student as select * from view_student order by height desc

Select * from view_student group by class_id

VI. Affairs

Transaction: a collection of SQL, either executed successfully or failed collectively

Syntax:

Open transaction: start transaction (begin can be abbreviated)

Submit: commit (if the sql is successful, submit and save the execution result of sql to the database)

Rollback: rollback (if sql fails, rollback, return the execution result of sql to before the start of the transaction)

Note: regardless of rollback or commit, the transaction will be closed! (you need to open it again to use) the transaction is valid only for the current connection!

Examples:

Start transaction

Update tb_student set money=money+1000 where id=10

Commit

VII. Database backup

1. Back up a single table

Select field list into outfile file address from table name where other select clause

Select * into outfile'dbank b.txt'

Fields terminated by','

Lines terminated by'\ r\ n'

From tb_student

Restore: load data infile filename into tb_name

2. Back up a library

Mysqldump-h227.0.0.1-P3306-uroot-p > dbank _ hand databank back.sqlterbank / backup library

Mysqldump-h227.0.0.1-P3306-uroot-p data_name tb_studnet > d _ Rank _

3. Restore the database

Mysql-h227.0.0.1-P3306-uroot-p data_name source dappled hand datapockback.sql; (restore after login)

3. Forget the root password

Mysqld server program, there is an option, skip authority authentication option! No password is required for client login

This option is required in my.ini. If not, manually add skip-grant-tables (delete this section if you change the password)

Step one:

Restart the mysqld service and go in directly with mysql.

Cmd > mysql

Step 2:

Update the password field of the root user in mysql.user table

Update mysql.user set password=password ('123456') where user='root'; can't do this. Just use the following one.

Update mysql.user set authentication_string=password ('*') where user='**'

Step 3:

Restart mysqld

8. PHP operates mysql server

Php acts as the customer service side of mysql server

Php-- link authentication-send sql-- to execute sql, generate result-process result-close connection

1. [connection Authentication]

$_ host = 'localhost'

$_ port = '3306'

$_ user = 'root'

$_ pass = 'rootpass'

If (! $_ link = mysql_connect ("$_ host:$_port", $_ user, $_ pass)) {

Die ('connection failed!')

}

2. [send sql to mysql server]

$_ sql = 'show databases'

If (! $_ result = mysql_query ($_ sql, $_ link)) {

Echo'sql execution failed'

}

3. [process the returned data]

$_ row = mysql_fetch_assoc ($_ result)

Var_dump ($_ row)

The function taken from the result set:

The mysql_fetch_assoc () function takes a row from the result set as an associative array.

The mysql_fetch_row () function takes a row from the result set as an indexed array.

The mysql_fetch_array () function takes a row from the result set as an associative array, an array of numbers, or both.

The mysql_num_rows () function returns the number of rows in the result set.

Note: any sql operation with a result returns a result set! The result set is the structure of a two-dimensional table! It's made up of rows of records!

4. [release Source]

Mysql_free_result ($_ result); result set

Mysql_close ($_ link); connect resources

About the Mysql database table how to add, delete, change and query to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.