Home > Article > Backend Development > PHP learning mysql page 1/2_PHP tutorial
PHP learning mysql page 1/2_PHP tutorial
- WBOYOriginal
- 2016-07-21 15:51:41789browse
Types of data types
Integer: It can be expressed in decimal and hexadecimal formats
Dot shape: A floating point number consists of a number plus a decimal point plus a digit. Two number sequences cannot be empty at the same time.
String: Characters or numbers enclosed by single or double quotes. Special symbols in the string can only be represented by escape characters.
Date and time values: For example, 2005-05-30 12:30:25 Date is year-month-day
NUUL value: It is an untyped value, which means "empty, nothing" ".
______________________________________________________________________________
mysql -h hostname -u username -p password //mysql login run command
DML data operation language
select query select field name, field name, ... (*) from table name where clause
Query select field name, field name, ... (*) from table name where clause unit price》limit 3 of 500 top 3
select *from kehu,yewuyuan where ye_id=ke_id; Query the ID of the salesperson's customer
insert insert insert into badwolf (bad_id,bad_name) values(3,'badbird'); insert
update modify update badwolf (table name) set bad_name(column name)='badwolf';
updata badwolf(table name) set bad_name column name='alex' value where bad_name='badbird';
delete delete from badwolf(show) where bad_id=2 ;
DCL data control language
grant control access permission
revoke access permission
ddl data definition language
create table create data table create table library name;
drop table database object
alter table defines its columns
When mysql enters the end of a statement, there must be a ; sign at the end. -> means that this statement has not been finished and is waiting for continued input. c is used to cancel input.
////////////////////////////////////////////////// ////////////////////////////
Numeric type
TINYTIN Very small integer BIGINT Large integer
SMALLINT Small integer FLOAT Single-precision floating-point number
MEDIUMINT Medium-sized integer DOUBLE Double-precision floating-point number
INT Standard integer DECIMAL A string of floating-point numbers
CHAR Fixed-length string VARCHAR Variable-length string
TINYBLOB Very small BLOB BLOB Small BLOB
MEDIUMBLOB Medium BOLB LONGBLOB Large BLOB
TINYTEXT Very small text string TEXT Small text string
MEDIUMTEXT Medium text LONGTEXT Large text string
ENUM Enumeration: Column can be assigned to an enumeration Raise members
SET Set: Column can be assigned to multiple set members
auto_increment Automatically identify columns
For columns that want to use this attribute, they should be defined as NOT NULL, and defined as PRIMARY KEY or defined as UNIQUE key.
unsigned无符号，此属性禁用负值，是将数值的取值范围从零开始，即前移了取值范围而已.
///////////////////////////////////////////////////////////////////////////////////
创建数据库
create database 库名; 创建一个指定名称的数据库
例如：create database book; 创建了一个名为book的数据库
show database 列出服务器中可用的的数据库
show table 显示数据库的清单
use 库名 选择使用数据库 例如：use book;
not null 非空 primary key 主键 auto_increment 自增
常见的完整性约束
primary key 主码约束（主键） 主码就是主关键字,能唯一标示字段属性
unique 唯一约束 在一个字段或者一组字段里数据与表中其它行的数据相比是唯一的
not null 非空值约束
auto_increment 整数列自增1
default_value 默认值约束
显示结构操作
database 表名
desc 表名
删除数据库或表操作
drop table 表名 删除表 drop table if exists badbird;加if exists 删除不存在表名不会出错
drop database 库名 删除库 同上加if exists不会出错
////////////////////////////////////////////////////////////
更改表结构操作
语法：alter table 表名 action;
说明：action 可以是如下语句：
add 列名 <建表语句> [first | after 列名]
add primary key (列名)为表添加一个主键，如果主键已经存在，则出现错误
alter 列名 set default 默认值 可以更改指定列默认值
----------------------------------------------------------
later table 表名 add 插如列的名 varchar(属性) first(之前)|after（之后） 增加列名
change 旧列名 新列名 <建表语句> [first | after 列名]
可以更改列类型和列名称，如果原列的名字和新列的名字相同，则change和modify的作用相同
-----------------------------------------------------------
modify 列名 <建表语句> [first | after 列名]
更改列属性：alter table badbird(表名) badtitle(列) modif varchar(属性)；
更改列名：alter table badbird(表名) change badtitle(列) bad_sad(改的名) varchar(属性) not null(no 空);
drop 列名 //可以删除一列
drop primary key //可以删除主键
drop index index_name; //可以删除索引
删除列：alter table badbird(表名) drop badtitle;
rename as 新表名 //可以将表名更改
更改表名：alter table badbird(表名) rename badwolf（表名）；
插入记录操作
语法：
insert into 表名(字段名,字段名, ... ) values(字段值,字段值,...);
例如：insert into badwolf (bad_id,bad_name) values(3,'badbird'); 插入
如果表名后面没写字段名，则默认是向所有的字段添加值，另外字符串值应该用‘ '或“ ”引号括起来
多条添加：insert into badwolf values(1,'badbird'),(2,'badbird'),(4,'badbird');
更改记录操作
语法：
update 表名 set 列名=数据值 where 子句；
例如：update badwolf(表名) set bad_name(列名)='badwolf';
------------------------------------------------------------------
where 子句是判断语句，用来设定条件，限制只更新匹配的行,如果不带where子句，则更新所有行数据
删除记录操作 语法： delete from 表名 where子句；
For example: delete from badwolf (show) where bad_id=2; Determine to delete all values with id=2. The judgment condition can be appended