守候你守候我

insert into 表名 values(default,"名字","2011-04-15 12:22:25"); //default可以換成null------------------------------insert into 表名 (name,datetime) values("名字","2011-04-15 12:22:25");使用SQL語法大寫，增加可讀性(小寫部分就是自己數據庫寫的表/字段嘍，具體你懂得...)。創建數據庫：CREATE DATABASE mysql_db;刪除數據庫：DROP DATABASE mysql_db;查看數據庫：SHOW DATABASES;使用數據庫：USE mysql_db;查看數據庫中的表:SHOW TABLES;創建表：CREATE TABLE user(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20) UNIQUE NOT NULL,sex ENUM('1','2','3') NOT NULL DEFAULT 3);查看表結構：①SELECT COLUMN FROM user;②DESC user;查看表創建語句(可查看各種自動生成的名字)：SHOW CREATE TABLE user;插入表記錄：①INSERT INTO user(id,username,sex) VALUES(1,'Tom',1);②INSERT user(username) VALUES ('John');查找表：SELECT * FROM user;刪除表中的數據(保留表結構)：TRUNCATE TABLE user;將查詢結果寫入新表:INSERT [INTO] privinces(sex) SELECT sex FROM user GROUP BY sex;(若表中有數據，請不要隨意更改表名、列名)修改表名：①ALTER TABLE user RENAME [AS|TO] users;②RENAME TABLE user TO users;添加單列：ALTER TABLE userADD [COLUMN] age SMALLINT NOT NULL UNSIGNED DEFUALT 18[FIRST | AFTER sex];刪除列：ALTER TABLE user DROP sex[,DROP age];修改列名稱和定義：ALTER TABLE userCHANGE [COLUMN] age a_ge TINYINT NOT NULL UNSIGNED AFTER id;單表更新：UPDATE user SET age = age + 5,sex = 1 [WHERE id = 2];單表刪除：DELETE FROM user [WHERE id =3];單(多)表連接：SELECT p.userid,p.username FROM user AS p LEFT JOIN user AS sON p.userid = s.age;單(多)表刪除：DELETE * FROM user [AS] u1 LEFT JOIN (SELECT p.userid FROM user AS p LEFT JOIN user AS sON p.userid =s.age GROUP BY p.userid HAVING COUNT(s.age)>1 ) [AS] u2ON u1.userid = u2.ageWHERE u1.userid > u2.userid;查找記錄：SELETE select_expr [,select_expr][FROM table_references[WHERE where_condition][GROUP BY {col_name|position}[ASC|DESC],...] //查詢結果分組[HAVING where_condition] //設置分組條件[ORDER BY {col_name|expr|position}[ASC|DESC]] //對結果排序LIMIT {[offset,]row_count|row_count OFFSET offset}]//限制記錄數量]記錄為2、3行：SELECT * FROM user LIMIT 1,2;別名使用(在項目中一般都使用別名)AS alias_name:SELECT id AS uid FROM user AS u ;子查詢：SELECT AVG(age) FROM user WHERE userid =[ALL|SOME|ANY|[NOT] IN|[NOT] EXISTS](SELECT uid FROM privinces WHERE pname = '河南');外鍵約束：(父表為user表,必須先在父表中插入數據，才能在子表中插入數據)CASEADE：從父表中刪除或更新行 且 自動級聯刪除或更新子表中匹配的行；SET NULL:從父表刪除或更新行，并設置子表中的外鍵列為NULL;RESTRICT:拒絕對父表的更新或刪除操作；CREATE TABLE privinces(pid SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL ,uid SMALLINT,FOREIGN KEY (uid) REFERENCES user (id) //privinces的字段uid與user表的字段id外鍵約束ON DELETE CASEADE //刪除時 級聯刪除);增加主鍵、唯一、外鍵約束：ALTER TABLE privincesADD [CONSTRAINT uq_pname] UNIQUE(pname);添加/刪除默認約束：ALTER TABLE privincesALTER pname {SET DEFAULT literal | DROP DEFAULT};刪除主鍵約束：ALTER TABLE privinces DROP PRIMARY KEY;刪除唯一(索引)約束：(查看約束名字key_name: SHOW INDEXS FROM privinces;)ALTER TABLE privinces DROP {INDEX | KEY} 約束名;刪除外鍵約束：(查看約束名字：SHOW CREATE TABLE privinces;)ALTER TABLE privinces DROP FOREIGN KEY 約束名;多表更新：UPDATE 表的參照關系 SET col_name = {[expr | value]};表的參照關系：tab1_reference {[INNER|CROSS] JOIN |{LEFT|RIGHT}[OUTER] JOIN} tab2_refenence ON conditional_expr;內連接：INNER JOIN左外連接：LEFT JOIN右外連接：ROGHT JOINUPDATE user INNER JOIN privinces ON user_privince = pnameSET user_privince = pid;//(更新user內連接privince,條件是user_privince.=pname,設置user_privince=pid)創建數據表的同時將查詢結果寫入數據表：CREATE TABLE table_name[create_definition, ...] //創建語句的定義select_statement; //要插入查詢結果的 查詢語句字符函數：CONCAT()字符連接；CONCAT('I','LOVE','YOU');CONCAT_WS()使用指定的分隔符進行字符連接；CONCAT_WS('|','A','B');第一個為分割符FORMAT();數字格式化;FORMAT(99999.99,1);將數字格式化，并保留1位小數LOWER();轉換成小寫字符UPPER();轉換成大寫字符LEFT();獲取左側字符RIGHT();獲取右側字符LENGTH();獲取字符長度；LTRIN();刪除前導字符；RTRIM();刪除后續字符TRIM();刪除前導、后續字符；SUBSTRING();字符串的截取；SUBSTRING('MYSQL',1,2);從第一位中截取2位；(mysql不允許為負值)[NOT] LIKE;模式匹配SELECT * FROM user WHERE username LIKE '%1%%' ESCAPE '1';REPLACE();替換字符串中的字符REPLACE('M??Y??SQL','?','*');將M??Y??SQL中的？替換成*;數值運算符：CEIL();進一去整；DIV;整數除法；FLOOR();舍一去整；MOD;取余數(模)；POWER();冪運算；ROUND();四舍五入；ROUND(3.562,1);取一位小數TRUNCATE();數字截取；TRUNCATE(125.89,1);小數后截取一位；日期時間：NOW();當前時間；CURDATE();當前日期；CURTIME();當前時間；DATE_ADD();日期變化；DATE_ADD('2015-6-23',INTERVAL 365 DAY);DATEDIFF();倆日期之間的差值；DATE_FORMAT();進行日期格式化；SELECT DATE_FORMAT('2015-6-26','%m/%d/%Y');分類: mysql