MySQL, PHP introductory tutorial

Log in to MySQL mysql -hlocalhost-uroot -proot

Exit MySQL exit

A semicolon must be added after each statement: ----- ---------------------------------------

Show database SHOW DATABASES;; Check how many databases there are on the MySQL server

Create database

 CREATE DATABASE db_name [IF NOT EXITSTS][CHARSET utf8]；

Separate each command with spaces

   db_name // 自定义数据库名字  字母数字下划线
   [IF NOT EXITSTS] //如果创建的数据库已经存在，则不会报错
   [CHARSET utf8]  //指定当前数据库的字符集，默认为‘拉丁’，

                                                                                                                                                                                                                       

#

show create database db_name；   //查看创建数据库的语句过程

删除数据库   DROP DATABASE db_name [IFEXITSTS]；
       [IF EXITSTS]  //如果存在删除就不会报错

Data table operation:

Select current dataLibrary------>USEdb_name; Display data table--- ------>SHOW TABLES; Create data table---------->CREATETABLE table_name(column 1 type column attribute, column 2 type column attribute,...);

Example:

修改mysql数据库的字符集：ALERT DATABASE db_name DEFAULT CHARACTERSET 字符集；

auto_increment----automatic growth primarykey----primary key

Attributes of the id field: required----NOT null auto_increment primary key

Column type (mysql data type): integer, floating point, character, Attributes of text type, date type

columns

: whether it is empty, NOTnull || NULL

Automatic growth: AUTO_INCREMENT can only be used for id, and one table can only have An automatic growth attribute Default value: DEFAULT value

Primary key: (primary index) There is only one, PRIMARYKEY is generally assigned to the id field, and a table can only have one

Integer type: tinyint---one byte (0-255) smalimt----two bytes (0-65535) int----four bytes (0-2.1 billion) bigint----8 bytes ()

Floating point type: float (M, D) can be accurate to 7 decimal places, M represents the total width, and D represents the decimal place

double (M,D) can be accurate to 15 digits after the decimal point

Character type and text

Char (M) fixed-length character, M represents the length

Varchar(M) variable length character, M represents the length

Char can access data faster than varchar

Text Type

tinytext 0-255 Minimum text type

text 0-16.77 million Medium text

longtext 4.2 billionDate time

create table table1(id int not null auto_increment primarykey,title varchar(100) not null,author varchar(20) not null,source varchar(30)not null,
hits int(5) not null default 0,
is_ppt tinyint(1) not null default0,content text null,addate int(16) not null);

Display data table

date  如：“YYYY-MM-DD”
time  如：“HH：MM：SS”
datetime 如：“YYYY-MM-DD HH：MM：SS”

Display table structure

SHOW TABLE FROM table_name;

Delete table

DESCRIBE table_name;

Modify

Use phpmyadmin to modify

Data processing SQL

Structured query language

The main functions of SQL

Add Delete Modify Check

DROP TRABLE table_name;

You can specify the ID field and assign a value to the id

Set the character set requested by the client (Chinese garbled solution): set names gbk;

增加：----------- INSERT  INTO  table_name（字段1，字段2，….）  VALUE（值1，值2，…..）；

WHERE field> value

If the where condition is omitted, all must be deleted

删除：-----------  DELETE  FROM table_name [WHERE 条件];

Query: --------- select field from table_name;

describe command to view the fields in the database table

Import the saixinjituan.sql file into the mysql database

First create a

database

: Then select the current database; Select import from the menu bar;

SELECT field list|* FROMtable_name [WHERE condition][ORDER BY][LIMIT]

Field list|*FROM ------ Query to specify several Field information, use wildcard * to query all fields

If the where condition is omitted, all records will be displayed

order by ---------- The queried records are sorted by that field ASC ascending order (default) DESC descending order

delete from table1 where id>5；删除id大于5的所有
delete from table1 where id>15 andcontent='上市大公司';

LIMIT limits the number of output records -----LIMIT starting line number, number of records (for data paging)

Fuzzy query is replaced by %

select* from news order by ID desc；

Modification:-------- UPDATE table_name SET field 1=new value 1, Field 2=new value 2[WHERE condition];

update table1 set title='Entertainment',author='As',addate='14399999'where id=33;

PHP connects to MySQL server

php connects to MySQL server: mysql_connect(), exit(), mysql_error()

Select database ------- -----mysql_select_db()

Set mysql return data character set--------mysql_query("set names utf8")

Execute SQL statement----- ----mysql_query()

Retrieve the total number of records from the result set-----------mysql_num_rows()

Retrieve one row of data from the result set----- ----mysql_fetch_row(), mysql_fetch_array(), mysql_fetch_assoc()

PHP supplementary function----------include(), require(), md5(), urlencode(), urldecode();

PHP+MySQL database programming steps---------

一, Log in to the mysql server

2. Select the current database

3. Set the request character set

四、执行SQL语句

PHP函数连接mysql数据库-------- mysql_connect()

resource $link =mysql_connect($db_host,$db_user,$db_pwd)

resource$link = mysql_connect(“localhost”,”root”,””)  不推荐

resource  连接成功，返回一个资源类型的标识符，如果失败，返回false

$db_host 代表mysql服务器的主机名或IP地址，本地为localhost

$db_user 代表mysql服务器的用户账号

$db_pwd 代表mysql服务器的用户密码

 

exit()输出一个信息，并终止程序运行

void exit([string $status]);

exit(“程序出错了”)

mysql_error()主要用于测试，一旦上线就不可能再使用

输出上一次操作mysql出错的文本信息

语法：mysql_error([resource $link])

$link代表当前的活动链接

@屏蔽系统的错误信息

选择数据库mysql_select_db()

选择要挫折的数据库------返回布尔值

bool  mysql_select_db(string $database_name [,resource $link_indentifier])

 

$database你要操作的数据库名称

[$link]可选 ，代表当前的活动链接

 

返回值：成功------true   失败-----false

 

设置MySQL返回的数据字符集

mysql_query(“set names utf8”)

执行SQL语句

mysql_query()---------执行各种SQL语句

语法：

resource $result =mysql_query($sql[,$link]);

$sql  各种SQL语句

增$sql =  “insertinto table_name(title,id) values(‘title’,’id’)”;
删$sql  = “delete from table_name [where id=5]”;
改$sql  = “update table_name set title=’新title’[where id=5]”;
查$sql  = “select * from table_name”;

 

$link当前活动的链接，如果省略，就以上一个打开的链接为准

 

返回值：当执行SQL语句时，成功返回结果集 是一个资源的数据类型，失败返回FALSE

 

 从结果集中读取数据

取出一行----mysql_fetch_row() 每次读一行数据

从结果集中读取一行数据，并以枚举数组返回

返回一个数组    array mysql_fetch_row(resource$result)

$result代表返回的结果集    返回一个枚举数组，也就是说这一行是存在数组中的，

$row= mysql_fetch_row($result,$link);

 

 

The above is the detailed content of MySQL, PHP introductory tutorial. For more information, please follow other related articles on the PHP Chinese website!