PHP basic study notes mysql (14), study notes mysql

php basic study notes mysql (14), study notes mysql

Login to the database system

The database is a "management system". To use the database, we must first "log in" to the database system.

The method to log in to the database system is:

1. Open cmd command line window

2. Enter the login command as follows: wamp installation directory binmysqlmysql5.5.8binmysql.exe -hlocalhost -uroot –p

a) -h is followed by "database server name", here is localhost

b) -u is followed by the "user name" for login, here is the default "ｋｏｽｔ"

c) -p should be followed by the password, but it is recommended not to write it. If you do not write it, the system will ask in the next step.

3. After entering the database system, first execute the command: set names gbk; This way, garbled characters may not occur.

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

5. Statements in the database are not case-sensitive. It is still recommended that you use fixed case patterns.

Operation database

Create database:

create database database name charset utf8;

Delete database:

drop database database name;

Show all databases:

show databases;

Enter (use) a database

use database name;

——Any time you want to operate on a table or data in a database, you must first "enter" the database.

Data types in database

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

Floating point type: float, double

Character type:

CHAR: The character type of the fixed length needs to specify a length when used, and its length is up to 255. The length specified by yourself means that the data must be stored this long (no more, no less). Usually only used for fixed-length data, such as postal codes and mobile phone numbers.

varchar: A variable-length character type. You need to specify a length when using it. The length can be up to 60,000. The length you specify means that the data can be stored up to this much, but it can be less (no more than this specified value)

——In the database, character types are represented by single quotes, such as: ‘abc’, ‘102033’, ‘13910581085’

Time type:

date: represents a date

time: represents a time

datetime: represents a date and time

-In application, if one time is "direct time" (that is, the time expressed in text characters), you need to add a single quotation number to represent, for example: '2013-10-7' ''11: 57: 58' '2013-10-7 11:57:58'

Operating Data Sheet

Create data table

: Format: create table table name (field name 1 type 1 [additional attribute 1], field name 2 type 2 [additional attribute 2],...);

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

Type is the word for the data type introduced earlier

Additional attributes are as follows:

Auto_increment: It means that the value of this field will "automatically grow" for the field of integer type.

Primary Key: It means that the data of this field will not be repeated (should not be repeated), which is the so -called "primary key". The role is to use the only line of data to distinguish from other rows -usually combined with Auto_increment.

not null: Indicates that the value of this field cannot be null (null)

DEFAULT: indicates the default value for setting a field. If the field is not written when writing data, the fixed value set here will be used instead - somewhat similar to the default parameters in PHP.

— The above four attributes can be listed side by side in one field, and they can be separated by spaces.

Example: Create a user table, including serial number, name, zip code, payment, age, enrollment date

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

Delete data table :

drop table table name;

Display all tables in the current database :

show tables;

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

desc table name;

Data operations: Add data:

Grammar form:

insert into table name (field name 1, field name 2, ….. ) values (value 1, value 2, …..) ;

Meaning:

Put the specified corresponding values into the specified fields (cells) in the specified table as a row of data - we always insert data in units of "rows", that is, one row of data must be completed each time The insert works even if some fields are not given values.

Note:

1. We can only specify some fields of the table to insert data, and the remaining fields without specified values will either have default values, auto-increment values, or no values (that is, 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 is equal and must correspond one to one!

4. Generally, auto-increasing fields should not insert data manually.

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

Example:

insert into userInfo3(userName, postcode, age, fee, regDate)values(‘韩’,’102030’,22,100.0,’2014-9-5’);

insert into userInfo3(userName, age, fee, regDate)values('Han',22,100.0,'2014-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. Deletion is based on "line" units, that is, one or more lines will be deleted once deleted.

2. Usually deletion work must include a where condition, otherwise all data will be deleted (this is rarely required)

3. The where condition can use several combined conditions, and each condition can be judged arbitrarily using the data in the field

４, Multiple conditions are combined through logical operators, similar to this: Condition 1 and Condition 2 or Condition 3

a) Logical operator and: means "and", the same as &&.

b) Logical operator or: means "or", the same as ||

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

5, Common usage of a single condition (example)

A) ID = 3; // The value of this field of this field is 3 (those) data lines

b) Age=22; // The data row (those) whose age field value is 22

C) ID & gt; 6; // All data rows with value greater than 6

d) id >=6; //…………

e) userName=’Han Xueyu’ //

f) Postcode = ‘000000’

g) regDate = ‘2013-9-5’

６， Some combination conditions:

a) Where id > 6 and id < 10; //Data rows corresponding to id greater than 6 and less than id

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;

Example:

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

Data operations: modify data:

Grammar form:

update table name set field name 1=value 1, field name 2=value 2, ... [where condition];

Meaning:

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

Note:

1, we must also understand: the modification is also based on the "line" unit, and the one or multiple lines are modified (the number of rows that meets the conditions)

2. Although the unit is "row", we can specify in the statement to modify only certain fields.

3. The order of the fields to be modified is not specified, you can set it yourself, and the number is not specified, you can set it yourself.

4. The writing form of the value is the same as the insert statement (insert statement)

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

Example:

update userInfo3 set userName='Xiaohan', postcode='123456' where id=3;

update userInfo3 set postcode='123123' where userName='Yu Yu';

Data operations: query data:

Grammar form:

select field name 1, field name 2,…. from table name [where condition] [order by sort setting] [limit quantity limit]

Meaning:

The select statement refers to "retrieving" (finding out/finding) several rows of data from the database, but note that this retrieval will not affect the database itself, that is to say, the data in the database will not be changed due to the select statement. Reduce data - This also reflects that the previous insert, update, and delete statements will affect the data itself, that is, it will change the database!

Note:

1. The field names of the train after select represent the fields to be taken out from the table. You can choose any item at will. But using a "*" means to retrieve all fields in the table.

2. The use and meaning of where condition are still the same as before.

3, The order by statement is used to sort the data taken out under the previous conditions in a specified way. The sorting syntax is as follows:

a) order by field name sorting method;

b) There are only two sorting methods: forward order (ASC) and reverse order (DESC), of which asc can actually be omitted.

4. The limit quantity limit statement is to restrict the removal of only certain rows based on the previous conditions and the set sorting method! , the syntax is as follows:

a) limit starting line number, required quantity

b) The line number is similar to the subscript of the array in js, it must be a consecutive number starting from 0.

5. The above where clause, order by clause and 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, fee from userInfo3 where id>3;

select id, fee, postCode, regDate from userInfo3 where age > 20

select id, fee, 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;