Php basic learning notes mysql (14), learning notes mysql

Php basic learning notes mysql (14), learning notes mysql
Database System Logon

A database is a "Management System". To use a database, you must first "Log on" to the database system.

To log on to the database system, follow these steps:

1. Open the cmd command line window.

2. Enter the following logon command: wamp installation directory \ bin \ mysql \ mysql5.5.8 \ bin \ mysql.exe-hlocalhost-uroot-p

A)-h is followed by "Database Server Name". Here is localhost

B)-u is the "user name" for Logon. The default "root" is used here"

C)-p should be followed by a password, but it is recommended that you do not write or write it, then the system will ask in the next step.

3. After Entering the database system, run the command: set names gbk; to avoid garbled characters.

4. After Entering the database, all commands must end with a semicolon to indicate a command, and all commands are executed one by one.

5. The statements in the database are case-insensitive. We recommend that you use them in a fixed case-insensitive mode.

Database Operations

Create a database:

Create database name charset utf8;

 

Delete database:

Drop database name;

 

Show all databases:

Show databases;

 

Enter (use) A database

Use Database Name;

-- To operate a table or data in a database, you must first "enter" the database.

Data Types in the database

Integer type: tinyint, smallint, mediumint, int, bigint

Float, double

Character Type:

Char: A character type with a fixed length. You must specify a length when using it. The length is up to 255 characters. The length specified by yourself indicates that the data must be stored so long (not many or not ). It is usually used only for fixed-length data, such as ZIP code and mobile phone number.

Varchar: variable-length character type. You must specify a length when using it. The length can be more than 60 thousand characters. The length specified by the user indicates that the data can be stored at most, but can be less (the specified value cannot be exceeded)

-- In the database, the character types are represented by single quotes, such as 'abc', '123', and '123'

Time type:

Date: indicates a date.

Time: indicates a time.

Datetime: indicates a date and time.

-- In an application, if a time is "direct time" (that is, the time expressed by text characters), it must be enclosed by single quotation marks, for example, '2017-10-7' '11: 57: 58 ''2013-10-7 11:57:58'

Operation data table

Create a data table:

Form: create table Name (field name 1 Type 1 [additional attribute 1], field name 2 type 2 [additional attribute 2],… );

Meaning: creating a table is equivalent to setting several fields in a table.

Type is the word of the data type described above.

The additional attributes are as follows:

Auto_increment: indicates that the value of this field will automatically increase. It is used for integer fields.

Primary key: indicates that the data of this field will not be repeated (should not be repeated), that is, the so-called "primary key ", it uniquely identifies a row of data to distinguish it from other rows. It is usually used in combination with auto_increment.

Not null: the value of this field cannot be null)

Default: indicates the default value used to set a field. If this field is not written when data is written, a fixed value is set here to replace it-something similar to the default parameter in php.

-- The preceding four attributes can be listed on one field and separated by spaces.

For example, create a user table, including sequence number, name, zip code, payment, age, and admission date.

Create table userInfo (id int auto_increment primary key, userName varchar (10), postcode char (6), comment float, age int, regDate date );

 

Delete A data table:

Drop table name;

 

Show all tables in the current database:

Show tables;

Display the structure (Definition and shape) of a table):

Desc table name;

 

Data Operations: add data:

Syntax format:

Insert into Table Name (field name 1, field name 2 ,..... ) Values (value 1, value 2 ,..... );

Meaning:

Put the specified values in the specified field (cell) in the specified table as a row of data-we always insert data in the unit of "row, that is to say, each time a row of data is inserted, even if some fields do not have a value.

 

Note:

1. We can only specify some fields in the table to insert data. Then, other fields without specified values either have default values, auto-increment values, or can be empty)

2. The specified field does not need to be written in the actual field order of the data table, but can be any.

3. In the above form, the number of fields and values is equal and must correspond one to one!

4. Fields of the self-growth type should not be inserted manually.

5. Note that if the value is a direct value of the character or time type, you must use single quotation marks.

Example:

Insert into userInfo3 (userName, postcode, age, region, regDate) values ('han', '20170101', 102030, '2017-9-5 ');

Insert into userInfo3 (userName, age, region, regDate) values ('Han ', 22,100.0, '2017-9-5 ');

Data operation: delete data:

Form:

Delete from table name [where condition ];

Meaning:

Delete certain rows in the specified table-Delete according to the given conditions.

Note:

1. Delete all rows, that is, delete one or more rows.

2. Generally, the where condition must be added to the delete operation. Otherwise, all data will be deleted (this requirement is rare)

3. The where condition can use a combination of several conditions, and each condition can be determined using any data in the field.

4. Multiple conditions are combined by logical operators, like this: Condition 1 and condition 2 or condition 3

A) logical operator and: represents "and", the same.

B) logical operator or: "or", same as |

C) logical operator not: "not", followed! Same

5. Common usage of a single condition (example)

A) id = 3; // The data row whose field value is 3

B) age = 22; // The data row whose value of the age field is 22

C) id> 6; // all data rows whose field value is greater than 6

D) id> = 6 ;//.........

E) userName = 'Han xueyu '//

F) postcode = '20140901'

G) regDate = '2017-9-5'

6. Some combination conditions:

A) where id> 6 and id <10; // The data rows whose IDs are greater than 6 and smaller

B) where id> = 3 and age = 25;

C) where id <= 5 and postcode = '000000' and age = 22; // three conditions must be met at the same time

D) where postcode = '000000' or age = 22; // two conditions, one of which can be satisfied.

Example:

Delete from userInfo3 where id <= 5 and postcode = '2013' and age = 22;

Data Operations: modify data:

Syntax format:

Update table name set field name 1 = value 1, field name 2 = value 2 ,...... [Where condition ];

Meaning:

Modify the values of specified fields in a specified table based on the where condition.

Note:

1. We also need to understand that the modification is also in the unit of "rows" and one or more rows are modified at a time (the number of rows meeting the condition)

2. Although it is in the unit of "line", we can specify to modify only some of the fields in the statement.

3. The order of modified fields is not specified. You can set the order by yourself, and set the number by yourself.

4. The value is written in the same way as the insert statement)

5. The meaning and usage of the where condition are the same as that of the delete statement.

Example:

Update userInfo3 set userName = 'slight cold ', postcode = '000000' where id = 3;

Update userInfo3 set postcode = '000000' where userName = 'yu Yu ';

Data Operations: query data:

Syntax format:

Select field name 1, field name 2 ,.... From table name [where condition] [order by sorting setting] [limit quantity]

Meaning:

A select statement is used to extract several rows of data from the database. However, this statement does not affect the database itself. That is to say, the data in the database will be reduced due to the select statement. This also shows that the previous insert, update, and delete statements will affect the data itself, that is, change the database!

Note:

1. The field name of the train after the select Operation indicates the fields to be retrieved from the table. You can take any items at will. However, a "*" is used to retrieve all fields in the table.

2. The usage and meaning of the where condition are the same as those above.

3. The order by statement is used to sort the data obtained under the preceding conditions in the specified way. The sorting syntax is as follows:

A) order by field name sorting method;

B) there are only two sorting methods: Positive Sequence (ASC) and reverse sequence (DESC). asc can be omitted.

4. The limit quantity limit statement removes only some of the rows under the preceding conditions and sets the sorting method !, Syntax:

A) Starting line number of the limit statement, required quantity

B) The row number is similar to the subscript of the array in js. It must be a continuous number starting from 0.

5. The above where clause and the order by clause limit clause can be used or omitted at will, but they must appear in the above order.

Example:

Select * from userInfo3;

Select * from userInfo3 where id> 3;

Select id, userName, region from userInfo3 where id> 3;

Select id, latency, postCode, regDate from userInfo3 where age> 20

Select id, latency, postCode, regDate from userInfo3 where age> 20 order by id desc;

Select * from userInfo3 order by age;

Select * from userInfo3 order by regDate;

Select * from userInfo3 where id> 3 order by regDate desc;

Select * from userInfo3 where id> 3 order by regDate desc limit 0, 2;

Select * from userInfo3 where id> 3 order by regDate desc limit 2, 2;