PHP Basic Learning Note MySQL (14), Learning note mysql_php Tutorial

Source: Internet
Author: User

PHP Basic Learning notes MySQL (14), Learning notes for MySQL


Login to the database system

The database is a "management system" and we must first "log in" the database system using the database.

The methods for logging into the database system are:

1. Open cmd Command Line window

2, enter the login command as follows: Wamp installation directory \bin\mysql\mysql5.5.8\bin\mysql.exe-hlocalhost-uroot–p

A) after –h is "database server name", this is localhost

b) After –u is the login "username", here is the default "root"

c) –p back should be the password, but the proposal does not write, do not write, then the system will be asked in the next step.

3, after entering the database system, execute the command first: set names GBK; In this way, it is possible to avoid garbled problems.

4, after entering the database, all commands need to end with a semicolon to represent a command;

5, the statements in the database are not case-sensitive, and it is still recommended that you use a fixed case pattern.

manipulating databases

To create a database:

Create database name CharSet UTF8;

To delete a database:

drop database name;

Show all databases:

show databases;

Enter (use) a database

Use database name;

--Any operation of a table or data in a database must first be "entered" into the database.

Data types in the database

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

Floating-point type: float, double

Character type:

Char: A fixed-length character type that is used with a length of up to 255. The length you specify indicates that the data must be stored so long (not many and many). Usually only used for fixed length data, such as ZIP code, mobile phone number.

VARCHAR: A variable-length character type that needs to specify a length of up to 60,000 in length. The length you specify indicates that the data can be stored at most, but it can be less (you cannot specify this value more)

--In the database, character types are represented by single quotes, such as: ' abc ', ' 102033 ', ' 13910581085 '

Time Type:

Date: Represents a day

Time: Represents a period

DateTime: Represents a Date time

--In the application, if a time is a "direct time" (that is, the time expressed in literal characters), it needs to be represented by single quotation marks, for example: ' 2013-10-7 ' 11:57:58 ' 2013-10-7 11:57:58 '

Operating Data Sheet

To create a data table :

Form: The name of the Create TABLE table (field name 1 Type 1 [additional property 1], field name 2 Type 2 [additional property 2], ...);

Meaning: In fact, the so-called create a table, which is equivalent to setting a table of several fields.

Type is the word of the data type described earlier

Attached properties are as follows:

Auto_increment: Indicates that the value of this field is "autogrow" for fields of integer type.

Primary key: Indicates that the data for this field is not duplicated (should not be duplicated), so-called "primary Key", which is used to uniquely identify a row of data to distinguish it from other rows-often in conjunction with auto_increment.

Not NULL: Indicates that the value of the field cannot be null (NULL)

Default value: Represents the defaults used to set a field. If the field is not written to when the data is written, a fixed value is used here instead--a bit like the default parameter in PHP.

---The above 4 attributes can be listed on a field, separated from each other by a direct space.

Example: Create a user table that includes sequence number, name, postal code, payment, age, entry date

CREATE TABLE userInfo (ID int auto_increment PRIMARY KEY, UserName varchar (TEN), postcode char (6), fee float, age int, Regda Te date);

To delete a data table :

drop table name;

Show all tables in the current database :

Show tables;

show the structure of a table (definition, shape):

DESC table name;

Data manipulation: Adding data:

Grammatical form:

Insert into table name (field name 1, field Name 2, ...) VALUES (value 1, value 2, ...);

Meaning:

The specified fields (cells) in the specified table in the grid are placed in the specified values as a row of data--we insert the data always in "Rows", that is, each time it must be done inserting a row of data, even if some fields do not give a value.

Precautions:

1, we can specify only some of the table's fields to insert data, the rest of the field does not specify the value of the default value, either has a self-growth value, or can not value (can be empty)

2, the fields we specify do not have to be written exactly in the order of the actual fields in the data table, but can be arbitrary.

3, in the above form, the number of fields and values are equal and must be one by one corresponding!

4, fields that are normally self-growing type should not be artificially inserted into the data.

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

Example:

Insert into UserInfo3 (UserName, postcode, age, fee, regdate) VALUES (' Korean ', ' 102030 ', 22,100.0, ' 2014-9-5 ');

Insert into UserInfo3 (userName, age, fee, regdate) VALUES (' Han ', 22,100.0, ' 2014-9-5 ');

Operation of data: deleting data:

Form:

Delete from table name "Where condition";

Meaning:

Delete some rows from the specified table-delete according to the given criteria.

Precautions:

1, the deletion is in the "line" unit, that is, the deletion of one or more lines.

2, usually delete work must add a where condition, otherwise all data will be deleted (very rarely this requirement)

3, where conditions can use several combinations of conditions, and each condition can be arbitrarily used in the field of data to judge

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

A) The logical operator and: means "and", as with &&.

b) logical operator OR: means "or", with | | The same

c) Logical operator not: means "no", same as!

5, common usage of single conditions (example)

a) id=3;//id the data row for which the value of this field is 3

b) age=22; Age the value of this field is 22 (those) data row

c) ID > 6; ID This field has a value greater than 6 for all rows of data

d) ID >=6; .........

e) username= ' Han Xuiyu '//

f) postcode = ' 000000 '

g) RegDate = ' 2013-9-5 '

6, some combination conditions:

A) where ID > 6 and ID < 10; Those data rows that correspond to IDs greater than 6 and less than

b) where ID >=3 and age = 25;

c) where ID <=5 and postcode= ' 000000 ' and age = 22; 3 conditions must be met at the same time

d) where postcode= ' 000000 ' or age = 22; 2 conditions to satisfy one of them can be

Example:

Delete from UserInfo3 where ID <=5 and postcode= ' 000000 ' and age = 22;

Data manipulation: Modify the data:

Grammatical form:

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

Meaning:

Modifies the value of certain fields specified in the specified table-based on the Where condition.

Precautions:

1, we should also understand that: the modification is also in "line" units, one or more lines to modify (according to the number of rows satisfied by the condition)

2, although in "line" units, but we can specify in the statement to modify only some of these fields.

3, the Modified field order is not specified, you can set their own, the number is not specified, their own settings.

4, the value is written in the same way as the INSERT statement (INSERT statement)

5, the meaning and usage of the Where condition is the same as the where of the DELETE statement.

Example:

Update UserInfo3 set username= ' Xiaohan ', postcode= ' 123456 ' where id=3;

Update UserInfo3 set postcode= ' 123123 ' where username= ' Yu Yu ';

Data manipulation: Querying data:

Grammatical form:

Select Field Name 1, field Name 2, .... From table name "Where Condition" "ORDER by Sort Set" "Limit quantity Limit"

Meaning:

The SELECT statement refers to "fetching" (identifying/locating) Several rows of data from the database, but note that this fetch does not affect the database itself, that is, the data in the database is reduced by the SELECT statement-which also insert,update the front, The DELETE statement is the data itself that will affect the data, which will change the database!

Precautions:

1, the field name of the train after select indicates those fields to be removed from the table, and any item can be arbitrarily taken. But using a "*" means that all the fields in the table are taken out.

2, the use and meaning of the Where condition is still the same as before.

3, the ORDER BY statement is used to sort the data taken out of the previous condition in the specified manner, with the following sort syntax:

A) Order By field name sorting method;

b) There are only two types of sequencing: Positive order (ASC) and reverse (DESC), where ASC can actually be omitted.

4, the Limit number limit statement is the order under the preceding conditions and set down to restrict only the removal of some of these lines! , the syntax is as follows:

A) limit start line number, required quantity

b) The line number is similar to the subscript of the array in JS, which must be a continuous number starting from 0.

5, the WHERE clause above, the ORDER BY clause limit clause can be arbitrarily used or omitted, but must appear in the order described above.

Example:

SELECT * from UserInfo3;

SELECT * FROM UserInfo3 where id>3;

Select ID, userName, fee from UserInfo3 where id>3;

Select ID, fee, postcode, regdate from UserInfo3 where age > 20

Select ID, fee, postcode, regdate from UserInfo3 where > an 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;

http://www.bkjia.com/PHPjc/973826.html www.bkjia.com true http://www.bkjia.com/PHPjc/973826.html techarticle PHP Basic Learning notes MySQL (14), learn notes MySQL database system login database is a management system, we use the database must first log on to the database system. Log in to the database ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.