MySQLæ°æ®åº
-
- æ°æ®åºç®¡çå·¥å ·
-
- è¿æ¥Mysqlæ°æ®åº
- ä¿®æ¹MySQLè¿æ¥å¯ç
- SQL è¯å¥åç±»
- æ°æ®åºå®ä¹è¯è¨DDL
-
- æ°æ®åºç¸å ³æä½
- æ°æ®åºè¡¨ç¸å ³æä½
-
- 表çå段类å
- å建æ°æ®åºè¡¨
- ä¿®æ¹æ°æ®åºè¡¨
- å é¤æ°æ®åºè¡¨
- æ¥è¯¢æ°æ®åºè¡¨
- æ°æ®æ纵è¯è¨DML
-
- CRUD
- SELECT
- æ¶é´æ³ä»ç»
- ç¨æ·æ³¨åæ¡ä¾
MySQL æ¯ä¸ä¸ªå ³ç³»åæ°æ®åºï¼ä½¿ç¨ SQL è¯è¨è¿è¡å¢å æ¹æ¥æä½ï¼ç®åå±äº Oracle æä¸ç产åã
MySQL æ°æ®åºå¼æºå è´¹ï¼è½å¤è·¨å¹³å°ï¼æ¯æåå¸å¼ï¼æ§è½ä¹ä¸éï¼å¯ä»¥å PHPãJava ç Web å¼åè¯è¨å®ç¾é åï¼é常éåä¸å°åä¼ä¸ä½ä¸º Web æ°æ®åºï¼ç½ç«æ°æ®åºï¼ã
æ°æ®åºç®¡çå·¥å ·
- Navicat for MySQL
Navicatæ¯ä¸æ¬¾æ¡é¢çMySQL管çå·¥å ·ï¼å®å微软çSQLServerç管çå¨å¾åï¼ç®åæç¨ãNavicatçä¼å¿å¨äºä½¿ç¨å¾å½¢åçç¨æ·çé¢ï¼å¯ä»¥è®©ç¨æ·ç®¡çæ´å è½»æ¾ã
Navicat for MySQLï¼http://www.navicat.com.cn/products/navicat-for-mysql
- phpMyAdmin
éè¿WEBæ¹å¼æ§å¶åæä½MYSQLæ°æ®åº
phpmyadminå®ç½ï¼https://www.phpmyadmin.net/
phpmyadminä¸æç½ï¼http://www.phpmyadmin.org.cn/
- Adminer
Admineræ¯ä¸ä¸ªç±»ä¼¼äºphpMyAdminçMySQL管ç客æ·ç«¯ï¼æ¯æMySQLãMS SQLãPostgreSQLãSQLiteç主æµï¼æ¯æå¤è¯è¨ï¼å·²èªå¸¦11ç§ç¿»è¯è¯è¨æ件ï¼å¯ä»¥æèªå·±çéæ±ç¿»è¯ç¸åºçè¯è¨ï¼ï¼æ´ä¸ªç¨åºåªæä¸ä¸ªPHPæ件ï¼ä½¿ç¨èµ·æ¥é常æ¹ä¾¿ï¼
Adminerå®ç½ï¼https://www.adminer.org/
- SQLyog
SQLyogæ¯ä¸ä¸ªæäºä½¿ç¨çãå¿«éèç®æ´çå¾å½¢å管çMYSQLæ°æ®åºçå·¥å ·ï¼å®è½å¤å¨ä»»ä½å°ç¹ææå°ç®¡çä½ çæ°æ®åºã
SQLyogå®ç½ï¼https://sqlyog.en.softonic.com/
è¿æ¥Mysqlæ°æ®åº
æ ¼å¼ï¼
mysql -h主æºå°å -uç¨æ·å ï¼pç¨æ·å¯ç
1ãè¿æ¥å°æ¬æºä¸çMYSQLã
é¦å win+Rè¾å ¥CMDæå¼DOSçªå£ï¼ç¶åè¿å ¥ç®å½mysql\binï¼åé®å ¥å½ä»¤
mysql -u root -p
ï¼å车åæç¤ºä½ è¾å¯ç .注æç¨æ·ååå¯ä»¥æç©ºæ ¼ä¹å¯ä»¥æ²¡æç©ºæ ¼ï¼ä½æ¯å¯ç åå¿ é¡»æ²¡æç©ºæ ¼ï¼å¦åè®©ä½ éæ°è¾å ¥å¯ç ã
å¦æåå®è£ 好MYSQLï¼è¶ 级ç¨æ·rootæ¯æ²¡æå¯ç çï¼æ ç´æ¥å车å³å¯è¿å ¥å°MYSQLä¸äºï¼MYSQLçæ示符æ¯ï¼ mysql>
注ï¼å¦æä¸æ³æ¯æ¬¡è¾å ¥mysqlå½ä»¤é½è¿å ¥å°mysql/binç®å½ä¸ï¼å¯ä»¥è®¾ç½®ç¯å¢åéï¼ä¸ç¶ç³»ç»ä¼æ示æ¾ä¸å°mysqlå½ä»¤ã
2ãç³»ç»ç¯å¢åéçé ç½®
æ¾å°MySQLå®è£ çè·¯å¾ä¸çbinæ件夹ï¼æè¿é使ç¨çæ¯éæç¯å¢UPUPW ï¼MySQLçå®è£ ç®å½å¨D:\UPUPW_ANK_W64\Modules\MySQL\bin
ç¯å¢åéç设置ï¼æççµèå³é®=>éæ©å±æ§=>éæ©é«çº§ç³»ç»è®¾ç½®=>ç¯å¢åé=>ç³»ç»åéä¸çPath=>ç¹å»ç¼è¾=>ç¹å»æ°å»º=>å°è·¯å¾å¤å¶è¿å»=>ä¿åå³å¯ã设置好ç¯å¢åéï¼å¨ä»»ææ件夹ä¸é½å¯ä»¥æ§è¡MySQL
3ãè¿æ¥å°è¿ç¨ä¸»æºä¸çMYSQLã
å设è¿ç¨ä¸»æºçIP为ï¼127.0.0.1ï¼ç¨æ·å为root,å¯ç 为abcd123ãåé®å ¥ä»¥ä¸å½ä»¤ï¼
mysql -h 127.0.0.1-u root -p;ï¼æ³¨:uä¸rootä¹é´å¯ä»¥ä¸ç¨å ç©ºæ ¼ï¼å ¶å®ä¹ä¸æ ·ï¼
3ãéåºMYSQLå½ä»¤ï¼ exit ï¼å车ï¼
ä¿®æ¹MySQLè¿æ¥å¯ç
æ ¼å¼ï¼
mysqladmin -uç¨æ·å -pæ§å¯ç password æ°å¯ç
1ãç»rootå 个å¯ç 1234
mysqladmin -u root -password 1234
注ï¼å 为å¼å§æ¶root没æå¯ç ï¼æ以-pæ§å¯ç ä¸é¡¹å°±å¯ä»¥çç¥äºã
2ãåå°rootçå¯ç æ¹ä¸ºadmin
mysqladmin -u root -p 1234 password admin
SQL è¯å¥åç±»
- DDL: æ°æ®å®ä¹, create, alter,drop
- DML: æ°æ®æä½, insert, update,delete,select,(CURD)
- DCL: æ°æ®æ§å¶, grant, revoke
- TCL: äºä»¶æ§å¶, commit, rollback
æ°æ®åºå®ä¹è¯è¨DDL
æ°æ®åºæ¨¡å¼å®ä¹è¯è¨DDL(Data Definition Language)ï¼æ¯ç¨äºæè¿°æ°æ®åºä¸è¦åå¨çç°å®ä¸çå®ä½çè¯è¨ã
- CREATE DATABASE å建æ°æ®åº
- CREATE TABLE å建表
- ALTER TABLE ä¿®æ¹è¡¨
- DROP TABLE å é¤è¡¨
- CREATE VIEW å建è§å¾
- ALTER VIEW ä¿®æ¹è§å¾
- DROP VIEW å é¤è§å¾
- TRUNCATE TABLE æ¸ ç©ºè¡¨
- CREATE INDEX å建索å¼
- DROP INDEX å é¤ç´¢å¼
æ°æ®åºç¸å ³æä½
MySQLæ¥çæ°æ®åºï¼SHOW DATABASESè¯å¥ï¼
MySQLå建æ°æ®åºï¼CREATE DATABASEè¯å¥ï¼
MySQLä¿®æ¹æ°æ®åºï¼ALTER DATABASEç¨æ³ç®ä»
MySQLå é¤æ°æ®åºï¼DROP DATABASEè¯å¥ï¼
MySQLéæ©æ°æ®åºï¼MySQL USEè¯å¥ï¼
- å¢
- create database åºå称;
- create database æ°æ®åºå称 charset ç¼ç æ¹å¼;
- å
- drop database åºå称;
- æ¹
- ä¿®æ¹ç¼ç æ¹å¼:alter database åºå称 charset ç¼ç æ¹å¼;
- æ¥
- æ¥çææåº:show databases;
- æ¥çæå®åº:show database åºå称;
- é
- use æ°æ®åºå称;
-- å建æ°æ®åº
# create database æ°æ®åºå称 charset ç¼ç æ¹å¼ collate æåºè§å;
create database phpedu collate utf8mb4_unicode_ci;
-- ä¿®æ¹æ°æ®åº alter database åºå称 charset ç¼ç æ¹å¼;
-- å é¤æ°æ®åº
drop database phpedu;
-- éæ©é»è®¤æ°æ®åº
use phpedu;
-- æ¥çæ以æ°æ®åº
show databases;
-- æ¥ç建åºè¯å¥
show create database phpedu;
-- æ¥çç¶æ
status
-- æ¥çå½åæ°æ®åº
select database();
-- æ¥çç»å½ç¨æ·
select user();
-- è¿æ¥æ°æ®åºæ¶å°±éä¸å½åæ°æ®åºmyblog
mysql -uroot -proot myblog;
对äºæ°æ®åºæ¥è¯´ï¼å符éçéæ©æ´å éè¦ãå 为æ°æ®åºä¸åå¨ç大é¨åå 容é½æ¯åç§æåï¼å符é对æ°æ®åºçåå¨ãå¤çæ§è½ï¼ä»¥åæ¥åç³»ç»ç移æ¤ãæ¨å¹¿é½ä¼åå°å½±åã
utf8mb4 表示è¿ç§å符éç± 1~ 4个åèç»æï¼å¦æéè¦æ¯æ emoji 表æ ï¼é常éè¦éæ© utf8mb4 çå符éæ¥æ¯æãéçäºèç½çåå±ï¼æè¶æ¥è¶å¤çåºç¨éè¦ utf8mb4 çå符éæ¥æ¯æãå¨ææ°ç MySQL8.0 ä¸ï¼é»è®¤å符éå·²ç»ç± latin1 åä¸ºäº utf8mb4ã
æåºè§åï¼ä¸è¬éæ©utf8mb4_unicode_ciæ¯åºäºæ åçUnicodeæ¥æåºåæ¯è¾ï¼è½å¤å¨åç§è¯è¨ä¹é´ç²¾ç¡®æåº
æ°æ®åºè¡¨ç¸å ³æä½
- CREATE TABLE å建表
- ALTER TABLE ä¿®æ¹è¡¨
- DROP TABLE å é¤è¡¨
- å¢
- å¢å 表åcreate table 表å称(å段1 æ°æ®ç±»å,å段2 æ°æ®ç±»åâ¦éå¶æ¡ä»¶);
- è³å°æä¸ä¸ªå段åæ°æ®ç±»å,æåä¸ä¸ªå段已ç»æåä¸ä¸ªæ°æ®ç±»åç»æåä¸å ,éå·
- å
- å é¤è¡¨:drop table 表å称;
- æ¸ ç©ºè¡¨éé¢æ°æ®: truncate table 表å称;
-
æ¹
å段
- æ·»å å段:alter table 表å称 add å段 å段æ°æ®ç±»å
- å é¤å段:alter table 表å称 drop å段
- ä¿®æ¹å段æ°æ®ç±»å:alter table 表å称 modify å段 æ°çæ°æ®ç±»å
- æ¿æ¢å段:alter table 表å称 change æ§å段å称 æ°å段 æ°å段æ°æ®ç±»å
- æ¹è¡¨å称:rename table 表å称 to æ°å称
- æ¹è¡¨çç¼ç :alter table 表å charset æ°ç¼ç ;
- æ¥
- æ¥çææ表:show tables
- æ¥çæå®è¡¨ç»æ:desc 表å称
- æ¥çæå®è¡¨å建信æ¯:show create table 表å称
表çå段类å
MySQLæä¾äºä¸ç»å¯ä»¥èµç»è¡¨ä¸å个åçæ°æ®ç±»åï¼æ¯ä¸ªç±»åé½å¼ºå¶æ°æ®æ»¡è¶³ä¸ºè¯¥æ°æ®ç±»åé¢å ç¡®å®çä¸ç»è§åï¼ä¾å¦å¤§å°ãç±»ååæ ¼å¼ãæ¯æå¤ç§ç±»åï¼å¤§è´å¯ä»¥å为ä¸ç±»ï¼æ°å¼ãæ¥æ/æ¶é´åå符串ï¼å符类åï¼
æ¥æåæ¶é´æ°æ®ç±»å
MySQLæ°æ®ç±»å | å«ä¹ |
---|---|
date | 3åèï¼æ¥æï¼æ ¼å¼ï¼2014-09-18 |
time | 3åèï¼æ¶é´ï¼æ ¼å¼ï¼08:42:30 |
datetime | 8åèï¼æ¥ææ¶é´ï¼æ ¼å¼ï¼2014-09-18 08:42:30 |
timestamp | 4åèï¼èªå¨åå¨è®°å½ä¿®æ¹çæ¶é´ |
year | 1åèï¼å¹´ä»½ |
timestampä¸datetimeï¼
- datetime çæ¥æèå´æ¯è¾å¤§,1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 , timestamp æå åå¨ç©ºé´æ¯è¾å°ï¼åªæ¯ datetime çä¸åã
- TIMESTAMP ç¨äºè¡¨ç¤º å¹´ææ¥ æ¶åç§ï¼ä½æ¯è®°å½ç年份 æ¯è¾çæï¼1970-01-01 00:00:01 ~ 2038, TIMESTAMPåå¿ é¡»æé»è®¤å¼ï¼é»è®¤å¼å¯ä»¥ä¸ºâ0000-00-00 00:00:00âï¼ä½ä¸è½ä¸ºnullã
- TIMESTAMP åæ¶åºç¸å ³ï¼æ´è½åæ å½åæ¶é´ãå½æå ¥æ¥ææ¶ï¼ä¼å 转æ¢ä¸ºæ¬å°æ¶åºåååæ¾ï¼å½æ¥è¯¢æ¥ææ¶ï¼ä¼å°æ¥æ转æ¢ä¸ºæ¬å°æ¶åºååæ¾ç¤ºãæ以ä¸åæ¶åºç人çå°çåä¸æ¶é´æ¯ ä¸ä¸æ ·çã
- TIMESTAMP çå±æ§å Mysql çæ¬åæå¡å¨ SQLMode çå½±åè¾å¤§ã
- å¦æè®°å½çæ¥æéè¦è®©ä¸åæ¶åºç人使ç¨ï¼æå¥½ä½¿ç¨ TIMESTAMPã
- ä¸è¬å»ºè¡¨æ¶åï¼å建æ¶é´ç¨datetime int ï¼æ´æ°æ¶é´ç¨timestampã
æ°å¼æ°æ®ç±»å
æ´å
MySQLæ°æ®ç±»å | å«ä¹ï¼æ符å·ï¼ |
---|---|
tinyint | 1åèï¼èå´ï¼-128~127ï¼ |
smallint | 2åèï¼èå´ï¼-32768~32767ï¼ |
mediumint | 3åèï¼èå´ï¼-8388608~8388607ï¼ |
int | 4åèï¼èå´ï¼-2147483648~2147483647ï¼ |
bigint | 8åèï¼èå´ï¼Â±9.22*10ç18次æ¹ï¼ |
æµ®ç¹å
MySQLæ°æ®ç±»å | å«ä¹ |
---|---|
float(m, d) | 4åèï¼å精度浮ç¹åï¼mæ»ä¸ªæ°ï¼då°æ°ä½ |
double(m, d) | 8åèï¼å精度浮ç¹åï¼mæ»ä¸ªæ°ï¼då°æ°ä½ |
decimal(m, d) | decimalæ¯åå¨ä¸ºå符串çæµ®ç¹æ° |
å符串æ°æ®ç±»å
MySQLæ°æ®ç±»å | å«ä¹ |
---|---|
char(n) | åºå®é¿åº¦ï¼æå¤255个å符 |
varchar(n) | å¯åé¿åº¦ï¼æå¤65535个å符 |
tinytext | å¯åé¿åº¦ï¼æå¤255个å符 |
text | å¯åé¿åº¦ï¼æå¤65535个å符 |
mediumtext | å¯åé¿åº¦ï¼æå¤2ç24次æ¹-1个å符 |
longtext | å¯åé¿åº¦ï¼æå¤2ç32次æ¹-1个å符 |
1.charï¼nï¼åvarcharï¼nï¼ä¸æ¬å·ä¸n代表å符ç个æ°ï¼å¹¶ä¸ä»£è¡¨åè个æ°ï¼æ以å½ä½¿ç¨äºä¸æçæ¶å(UTF8)æå³çå¯ä»¥æå ¥m个ä¸æï¼ä½æ¯å®é ä¼å ç¨m*3个åèã
2.åæ¶charåvarcharæ大çåºå«å°±å¨äºcharä¸ç®¡å®é valueé½ä¼å ç¨n个å符ç空é´ï¼èvarcharåªä¼å ç¨å®é å符åºè¯¥å ç¨ç空é´+1ï¼å¹¶ä¸å®é 空é´+1<=nã
3.è¶ è¿charåvarcharçn设置åï¼å符串ä¼è¢«æªæã
4.charçä¸é为255åèï¼varcharçä¸é65535åèï¼textçä¸é为65535ã
5.charå¨åå¨çæ¶åä¼æªæå°¾é¨çç©ºæ ¼ï¼varcharåtextä¸ä¼ã
6.varcharä¼ä½¿ç¨1-3个åèæ¥åå¨é¿åº¦ï¼textä¸ä¼ã
å建æ°æ®åºè¡¨
CREATE TABLE è¯å¥ç¨äºå建æ°æ®åºä¸ç表
å½ä»¤æ ¼å¼å¦ä¸ï¼
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
- column_name åæ°è§å®è¡¨ä¸åçå称ã
- data_type åæ°è§å®åçæ°æ®ç±»åï¼ä¾å¦ varcharãintegerãdecimalãdate ççï¼ã
- size åæ°è§å®è¡¨ä¸åçæ大é¿åº¦ã
å¨ SQL ä¸ï¼æ们æå¦ä¸çº¦æï¼
- NOT NULL - æ示æåä¸è½åå¨ NULL å¼ã
NOT NULL 约æ强å¶å段å§ç»å å«å¼ãè¿æå³çï¼å¦æä¸åå段添å å¼ï¼å°±æ æ³æå ¥æ°è®°å½æè æ´æ°è®°å½ã
- UNIQUE - ä¿è¯æåçæ¯è¡å¿ é¡»æå¯ä¸çå¼ã
- PRIMARY KEY - NOT NULL å UNIQUE çç»åãç¡®ä¿æåï¼æ两个åå¤ä¸ªåçç»åï¼æå¯ä¸æ è¯ï¼æå©äºæ´å®¹ææ´å¿«éå°æ¾å°è¡¨ä¸çä¸ä¸ªç¹å®çè®°å½ã
PRIMARY KEY 约æå¯ä¸æ è¯æ°æ®åºè¡¨ä¸çæ¯æ¡è®°å½ã主é®å¿ é¡»å å«å¯ä¸çå¼ã主é®åä¸è½å å« NULL å¼ãæ¯ä¸ªè¡¨é½åºè¯¥æä¸ä¸ªä¸»é®ï¼å¹¶ä¸æ¯ä¸ªè¡¨åªè½æä¸ä¸ªä¸»é®ã
- FOREIGN KEY - ä¿è¯ä¸ä¸ªè¡¨ä¸çæ°æ®å¹é å¦ä¸ä¸ªè¡¨ä¸çå¼çåç §å®æ´æ§ã
- CHECK - ä¿è¯åä¸çå¼ç¬¦åæå®çæ¡ä»¶ã
- DEFAULT - è§å®æ²¡æç»åèµå¼æ¶çé»è®¤å¼ã
å称 | ç±»å | 约ææ¡ä»¶ | 说æ |
---|---|---|---|
user_id | int(10) | ä¸å 许为空,æ éå¤ | ç¨æ·æ è¯ï¼ä¸»é®ï¼èªå¨éå¢ |
user_name | varchar(20) | ä¸å 许为空,æ éå¤ | ç¨æ·åï¼å¯ä¸ç´¢å¼ |
user_pwd | varchar(60) | ä¸å 许为空 | ç¨æ·å¯ç |
user_sex | tinyint(1) | é»è®¤å¼ä¸º0 | ç¨æ·æ§å« 1ç·0女 |
create_time | int(10) | ä¸å 许为空 | 注åæ¶é´ï¼æ¶é´æ³ |
update_time | timestamp | ä¸å 许为空 | ä¿®æ¹æ¶é´ |
æ ¹æ®å¦ä¸ç»æå建表
# å建æ°æ®åº
create database mydb;
# éæ©å½åæ°æ®åº
use mydb;
## å建ç¨æ·è¡¨ç»æ
create table `users`(
`user_id` int(10) not null auto_increment comment 'ç¨æ·ID' primary key,
`user_name` varchar(20) not null comment 'ç¨æ·å' unique key,
`user_pwd` varchar(60) not null comment 'ç¨æ·å¯ç ',
`user_sex` tinyint(1) comment '0ç·1女' default '0',
`create_time` int(10) not null,
`update_time` timestamp not null default current_timestamp() on update current_timestamp()
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ä¿®æ¹æ°æ®åºè¡¨
ä¿®æ¹è¡¨æçæ¯ä¿®æ¹æ°æ®åºä¸å·²ç»åå¨çæ°æ®è¡¨çç»æãMySQL ä½¿ç¨ ALTER TABLE è¯å¥ä¿®æ¹è¡¨ã常ç¨çä¿®æ¹è¡¨çæä½æä¿®æ¹è¡¨åãä¿®æ¹å段æ°æ®ç±»åæå段åãå¢å åå é¤å段ãä¿®æ¹å段çæåä½ç½®ãæ´æ¹è¡¨çåå¨å¼æãå é¤è¡¨çå¤é®çº¦æçã
常ç¨çè¯æ³æ ¼å¼å¦ä¸ï¼
ALTER TABLE <表å> [ä¿®æ¹é项]
ä¿®æ¹é项çè¯æ³æ ¼å¼å¦ä¸ï¼
{
ADD COLUMN <åå> <ç±»å> # æ·»å å段
| CHANGE COLUMN <æ§åå> <æ°åå> <æ°åç±»å> # ä¿®æ¹å段å称
| ALTER COLUMN <åå> { SET DEFAULT <é»è®¤å¼> | DROP DEFAULT }
| MODIFY COLUMN <åå> <ç±»å> # ä¿®æ¹å段æ°æ®ç±»å
| DROP COLUMN <åå> # å é¤å段
| RENAME TO <æ°è¡¨å> #ä¿®æ¹è¡¨å
}
-- å¢å é®ç®±å段
alter table users add email int(20) unsigned not null;
alter table users add phone int(13) unsigned not null;
-- ä¿®æ¹å段å®ä¹
alter table users change email user_email varchar(20) not null default '[email protected]';
-- å é¤å段
alter table users drop phone;
å é¤æ°æ®åºè¡¨
å é¤è¡¨ä¸çç´¢å¼
alter table table_name drop index index_name
å é¤è¡¨
drop table table_name
å é¤æ°æ®åº
drop database database_name
å é¤è¡¨å çæ°æ®
truncate table table_name
æ¥è¯¢æ°æ®åºè¡¨
æ¥çæ°æ®è¡¨ç»æ
desc 表å称;
æ¥ç建表è¯å¥
show create table 表å称;
æ¥çåºä¸æåªäºè¡¨
show tables;
æ°æ®æ纵è¯è¨DML
æ°æ®æ纵è¯è¨ï¼Data Manipulation Language, DMLï¼æ¯SQLè¯è¨ä¸ï¼è´è´£å¯¹æ°æ®åºå¯¹è±¡è¿è¡æ°æ®è®¿é®å·¥ä½çæ令éï¼ä»¥INSERTãUPDATEãDELETEä¸ç§æä»¤ä¸ºæ ¸å¿ï¼åå«ä»£è¡¨æå ¥ãæ´æ°ä¸å é¤ï¼æ¯å¼å以æ°æ®ä¸ºä¸å¿çåºç¨ç¨åºå¿ å®ä¼ä½¿ç¨å°çæ令ï¼å æ¤æå¾å¤å¼å人åé½æå ä¸SQLçSELECTè¯å¥çå大æ令以âCRUDâæ¥ç§°å¼ã
- select æ¥è¯¢æ°æ®
- insert æå ¥æ°æ®
- update æ´æ°æ°æ®
- delete å é¤æ°æ®
-
å¢
æå ¥ä¸è¡å¼
- insert into 表å values(v1,v2,â¦) # 该æ¹å¼å¿ é¡»ä¿è¯ æå ¥çæ°æ®ä¸ªæ°ä¸ è¡¨æ ¼åæ®µä¸ ä¸å¯¹åº
- insert into 表å(å段å称1,å段å称2) values(v1,v2) # 该æ¹å¼å¿ é¡»ä¿è¯ æå ¥çæ°æ®ä¸ªæ°ä¸ å¿ é¡»ä¸æ
- insert into 表å values(v1),(v2)â¦â¦
- insert into 表å(å段å称1,å段å称2) values(v1,v1),(v2,v2)â¦â¦
- å
- å é¤ææ:delete from 表å;
- å é¤æå®æ¡ä»¶ä¸:delete from 表å where æ¡ä»¶;
- æ¹
- ä¿®æ¹æææ°æ®:update 表å set å段å称=æ°çå¼,å段å称2=å¼2;
- ä¿®æ¹æ»¡è¶³æ¡ä»¶çæ°æ®:update 表å set å段å称=æ°çå¼,å段å称2=å¼2 where æ¡ä»¶
- æ¥
- æ¥çææå段:select * from 表å;
- æ¥çæå®å段:select å段1,å段2 from 表å ;
- æ¥çæ个æ¡ä»¶ææçå段:select * from 表å where æ¡ä»¶;
- æ¥çæ个æ¡ä»¶ä¸çæ个å段select å段 from 表å where æ¡ä»¶;
CRUD
# insert æå
¥æ°æ®
insert into users (user_name,user_pwd,user_sex,create_time,email) values ('admin','admin',0,1620793062699,'[email protected]');
# å¦ææå
¥å
¨é¨å段æ°æ®å¯ä»¥çç¥å段åï¼ä½æå
¥çæ°æ®ä¸ªæ°ä¸è¡¨æ ¼å段è¦ä¸ä¸å¯¹åº
#insert into users values ();
# update æ´æ°æ°æ®
# delete å é¤æ°æ®
SELECT
SELECT [DISTINCT] 表达å¼1|å段,....(*表示ææå)
FROM 表å å«å [, æ°æ®æº2 å«å2]
WHERE æ¥è¯¢æ¡ä»¶
GROUP BY åç»å段1,...
HAVING åç»çéæ¡ä»¶1,...
ORDER BY æåºå段1 ASC|DESC, æåºå段2 ASC | DESC ...
LIMIT å移é,æ°é
æ¶é´æ³ä»ç»
å¨ UNIX ç³»ç»ä¸ï¼æ¥æä¸æ¶é´è¡¨ç¤ºä¸ºèª 1970 å¹´ 01 æ 01 æ¥ 00 æ¶ 00 å 00 ç§ï¼å京æ¶é´ 1970 å¹´ 01 æ 01 æ¥ 08 æ¶ 00 å 00 ç§ï¼èµ·å°å½åæ¶å»çæ»ç§æ°ï¼è¿ç§æ¶é´ç§°ä¸º UNIX æ¶é´æ³
UNIX æ¶é´æªæä¾äºä¸ç§ç»ä¸ãç®æ´çæ¶é´è¡¨ç¤ºæ¹å¼ï¼å¨ä¸åçæä½ç³»ç»ä¸åæ¯æè¿ç§æ¶é´è¡¨ç¤ºæ¹å¼ï¼åä¸æ¶é´å¨ UNIX å Windows ç³»ç»ä¸å以ç¸åç UNIX æ¶é´æ³è¡¨ç¤ºï¼æ以ä¸éè¦å¨ä¸åçç³»ç»ä¸è¿è¡è½¬æ¢ãåæ¶ï¼UNIX æ¶é´æ³æ¯ä¸ä¸ªæ¶é´å·®ï¼ä¸æ¶åºæ²¡æå ³ç³»ï¼æ 论å½å PHP ä¸ä½¿ç¨çæ¯ä½ç§æ¶åºï¼å ¶ UNIX æ¶é´æ³æ¯å¯ä¸çã
PHP 为 UNIX æ¶é´æ³çå¤çæä¾äºå¤ç§å½æ°ãå°ç®åç PHP çæ¬ä¸ºæ¢ï¼ç±äºä»»ä½å·²ç¥ç Windows çæ¬ä»¥åå ¶ä»ä¸äºç³»ç»åä¸æ¯æè´çæ¶é´æ³ï¼å æ¤å¨ Windows ä¸æ æ³è¡¨ç¤º 1970 å¹´ 1 æ 1 æ¥ä¹åçæ¶é´ã
ä½¿ç¨ PHP ä¸ç time() å½æ°æ¥è·åå½åç UNIX æ¶é´æ³é¿åº¦ä¸º10å符ï¼å½æ°çè¯æ³æ ¼å¼å¦ä¸ï¼
time();
<?php
$time = time();
echo 'å½åçæ¶é´æ³ä¸ºï¼'.$time.'<br>';
date_default_timezone_set('GMT');
$time = time();
echo 'å°æ¶åºè®¾ç½®ä¸ºé¶æ¶åºï¼è·åçæ¶é´æ³ä¸ºï¼'.$time;
?>
å¯ä»¥ä½¿ç¨ date() å½æ°æ¥æ ¼å¼åä¸ä¸ªæ¬å°çæ¶é´ææ¥æï¼è¯¥å½æ°çè¯æ³æ ¼å¼å¦ä¸ï¼
date($format [,$timestamp])
åæ°è¯´æå¦ä¸ï¼
- $formatï¼è¡¨ç¤ºæ ¼å¼ååçæ¶é´æ ¼å¼ï¼å¯ä»¥å å«ä¸äºå ·æç¹æ®å«ä¹çå符ã
- $timestampï¼è¡¨ç¤ºå¾ æ ¼å¼åçæ¶é´æ³ï¼æ¯ä¸ä¸ªå¯éåæ°ï¼é»è®¤ä¸ºå½åæ¶é´ãä¹å¯ä»¥ç解为 $timestamp çé»è®¤å¼ä¸º time()ã
format å符 | 说æ | è¿åå¼ä¾å |
---|---|---|
d | æ份ä¸ç第å 天ï¼ä½¿ç¨ä¸¤ä½çæ°å表示ï¼ä¸è¶³ä¸¤ä½æ¶å¨åé¢è¡¥ 0 | 01 å° 31 |
D | ææå çè±æ缩åï¼ä½¿ç¨ 3 个åæ¯è¡¨ç¤ºï¼ | Mon å° Sun |
l | ææå çè±æåè¯ | Sunday å° Saturday |
S | æ¯æ天æ°åé¢çè±æåç¼ï¼ä½¿ç¨ 2 个åç¬¦è¡¨ç¤ºï¼ | stãndãrd æè thãå¯ä»¥å j ä¸èµ·ä½¿ç¨ |
w | 使ç¨æ°å表示ææä¸ç第å 天 | 0ï¼è¡¨ç¤ºææ天ï¼å° 6ï¼è¡¨ç¤ºææå ï¼ |
z | 使ç¨æ°å表示ä¸å¹´ä¸ç第å 天 | 0 å° 365 |
F | æ份çè±æåè¯ï¼ä¾å¦ January æè June | January å° December |
m | 使ç¨ä¸¤ä½çæ°å表示å½åæ份 | 01 å° 12 |
M | æ份çè±æ缩å | Jan å° Dec |
n | 使ç¨æ°å表示å½åæ份 | 1 å° 12 |
t | æå®æ份çå¤©æ° | 28 å° 31 |
L | æå®ç年份æ¯å¦ä¸ºé°å¹´ | å¦ææ¯é°å¹´å¼ä¸º 1ï¼å¦å为 0 |
Y | ä½¿ç¨ 4 ä½æ°å表示å®æ´ç年份 | ä¾å¦ï¼1999 æ 2019 |
æ ¼å¼åæ¶é´çæ ¼å¼å¾å¤ï¼æ们常ç¨çå°±æ¯è·åå¹´ææ¥æ¶åç§
<?php
echo 'å½åçæ¶é´æ³æ¯ï¼'.time().'<br>';
echo 'å½åæ¶é´ï¼'.date('Y-m-d H:i:s').'<br>';
echo '使ç¨ânowâè·åå½åçæ¶é´æ³ï¼'.strtotime('now').'<br>';
echo 'æ¶é´æ³é¿åº¦ä¸ºï¼'.strlen(time());
/*
å½åçæ¶é´æ³æ¯ï¼1620795120
å½åæ¶é´ï¼2021-05-12 12:52:00
使ç¨ânowâè·åå½åçæ¶é´æ³ï¼1620795120
æ¶é´æ³é¿åº¦ä¸ºï¼10
*/
?>
ç¨æ·æ³¨åæ¡ä¾
regist.html åå°æ³¨å页é¢
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>ç¨æ·æ³¨å页é¢</title>
<link rel="stylesheet" type="text/css" href="css/regist.css">
</head>
<body>
<div class="wrapper">
<article>
<h1><span>个人å客注å</span></h1>
<div class="main">
<form action="" method="post"οnsubmit="return false">
<div class="tel">
<input type="email" name="email" placeholder="请è¾å
¥é®ç®±" id="telephone" required >
</div>
<div class="userName">
<input type="text" name="userName" placeholder="请è¾å
¥ç¨æ·å" id="username" required>
</div>
<div class="password">
<input type="password" name="pwd" placeholder="请è¾å
¥å¯ç " id="pwd" required>
</div>
<div class="againpwd">
<input type="password" name="cpwd" placeholder="请å次è¾å
¥å¯ç " id="cmpwd" required>
</div>
<div class="captcha">
<input type="text" name="captcha" id="captcha" placeholder="请è¾å
¥éªè¯ç " required>
<img width="60" height="37" onclick="this.src='gd_captcha.php?'+new Date().getTime();" style="margin: 10px auto;vertical-align: bottom;" src="gd_captcha.php">
<em id="captchainfo">æ示信æ¯â¦â¦</em>
</div>
<button>注å</button>
</form>
</div>
</article>
</div>
<script type="text/javascript" src="js/reg.js"></script>
</body>
</html>
reg.js ajaxå¼æ¥è¯·æ±
document.querySelector('button').addEventListener("click",function(e){
const formdata = new FormData(document.querySelector('form'));
const xhr = new XMLHttpRequest();
xhr.open("post", "regist_check.php");
xhr.onload = () => {
let json = JSON.parse(xhr.response);
if(json.status==0){
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "red";
}else{
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "green";
setTimeout(()=>{
window.location.href = "http://baidu.com";
},2000)
}
};
xhr.send(formdata);
});
document.querySelector('#captcha').addEventListener('blur',function(e){
// console.log(e.target.value);
const xhr = new XMLHttpRequest();
xhr.open("post", "captcha_check.php");
xhr.onload = () => {
let json = JSON.parse(xhr.response);
if(json.status==0){
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "red";
e.target.focus();
}else{
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "green"
}
};
xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
xhr.send("captcha="+e.target.value);
})
document.querySelector('#username').addEventListener('blur',function(e){
const xhr = new XMLHttpRequest();
xhr.open("post", "uname_check.php");
xhr.onload = () => {
let json = JSON.parse(xhr.response);
if(json.status==0){
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "red";
e.target.focus();
}else{
document.querySelector("#captchainfo").textContent = json.msg;
document.querySelector("#captchainfo").style.color = "green"
}
};
xhr.setRequestHeader("Content-Type","application/x-www-form-urlencoded");
xhr.send("uname="+e.target.value);
})
captcha_check.php æ£æµéªè¯ç æ¯å¦æ£ç¡®
<?php
/**
* æ¥åç¨æ·ç»éæ¶æ交çéªè¯ç
*/
session_start();
//1. è·åå°ç¨æ·æ交çéªè¯ç
$captcha = $_POST["captcha"];
//2. å°sessionä¸çéªè¯ç åç¨æ·æ交çéªè¯ç è¿è¡æ ¸å¯¹,å½æåæ¶æ示éªè¯ç æ£ç¡®ï¼å¹¶éæ¯ä¹åçsessionå¼,ä¸æååéæ°æ交
if(!empty($captcha)){
if(strtolower($_SESSION["captcha"]) === strtolower($captcha)){
echo json_encode(['status'=>1,'msg'=>'éªè¯ç æ£ç¡®']);
}else{
echo json_encode(['status'=>0,'msg'=>'éªè¯ç ä¸æ£ç¡®']);
}
}
uname_check.php æ£æµç¨æ·åæ¯å¦å·²åå¨
<?php
// è¿æ¥æ°æ®åºæå¡
require "./connect.php";
// è·åç¨æ·å
$uname = $_POST["uname"];
if(!empty($uname)){
$sql = "select user_name from users where user_name='{$uname}'";
$res = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
if($res){
echo json_encode(['status'=>0,'msg'=>'ç¨æ·åå·²åå¨']);
}else{
echo json_encode(['status'=>1,'msg'=>'ç¨æ·ååæ³']);
}
}
?>
regist_check.php åéæ°æ®æ³¨åç¨æ·
<?php
require "./connect.php";
session_start();
$user = $_POST;
if(strlen($user['userName'])<4 || !preg_match("/^[A-Za-z]/i",$user['userName']) ){
echo json_encode(['status'=>0,'msg'=>'ç¨æ·åé¿åº¦éä¸å°äºåä½ä¸ä»¥åæ¯å¼å¤´']);
}else if(strcmp($user['pwd'],$user['cpwd'])!== 0){
echo json_encode(['status'=>0,'msg'=>'两次å¯ç è¾å
¥ä¸ä¸è´']);
}else if(strcasecmp($_SESSION["captcha"],$user["captcha"])!== 0){
echo json_encode(['status'=>0,'msg'=>'éªè¯ç ä¸æ£ç¡®']);
}else{
$create_time = time();
$sql = "insert into users (user_name,user_pwd,create_time,user_email) values ('{$user['userName']}','{$user['pwd']}',{$create_time},'{$user['email']}')";
if($pdo->exec($sql)):
echo json_encode(['status'=>1,'msg'=>'注åæåï¼è¯·ç¨åâ¦â¦']);
else:
echo json_encode(['status'=>0,'msg'=>'注å失败~~~']);
endif;
}
?>
connect.php æ°æ®åºè¿æ¥
<?php
namespace pdo_connect;
// æ°æ®åºé
置信æ¯
$config = require __DIR__."./config/database.php";
extract($config);
// $uname = $_POST["uname"];
$dsn = sprintf("%s:host=%s;dbname=%s",$type,$host,$dbname);
$pdo = new \PDO($dsn,$username,$password);
?>
ææå±ç¤ºï¼
1.01ç365次æ¹=37.78343433289 >>>1
0.99ç365次æ¹= 0.02551796445229 <<<1
æ¯å¤©è¿æ¥ä¸ç¹ç¹çç®æ ï¼è´µå¨åæâ¦