PHP、MySQL与JavaScript学习手册

# @Date    : 2017-11-15 13:18:34
# @Author  : 师兵范 (shibingfan@163.com)
 
#常用MySQL数据库命令
+------------+----------------+
| COMD       |   DESCRIPTION  | 
+------------+----------------+
|ALTER       |修改            |
|BACKUP      |备份            | 
|\C          |取消输入        | 
|CREATE      |创建            | 
|DELETE      |删除行          | 
|DESCRIBE    |表结构          | 
|DROP        |删除            | 
|EXIT(CTRL-C)|退出            | 
|GRANT       |修改权限        | 
|HELP(\h,\?) |帮助            | 
|INSERT      |插入            | 
|LOCK        |锁住            | 
|QUIT(\q)    |退出            | 
|RENAME      |重命名          | 
|SHOW        |项目说明        | 
|SOURCE      |从文件名执行命令| 
|STATUS(\s)  |显示当前状态    | 
|TRUNCATE    |清空表          | 
|UNLOCK      |解锁表          | 
|UPDATE      |更新数据        | 
|USE         |打开数据库      | 
+------------+----------------+
#显示已有数据库
mysql> SHOW databaseS; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| drupal7            |
| mysql              |
| northwind          |
| performance_schema |
| shibingfan         |
| sys                |
| testdb             |
+--------------------+
8 rows in set (0.06 sec)
#创建数据库
mysql> CREATE DATABASE publications;
Query OK, 1 row affected (0.03 sec)
#使用数据库
mysql> USE publications;
Database changed
#数据库授权
mysql> GRANT ALL ON publications.* TO 'shibingfan'@'localhost' IDENTIFIED BY 'shibingfan';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#创建数据库表
mysql> CREATE TABLE classics(
    -> author VARCHAR(128),
    -> title VARCHAR(128),
    -> type VARCHAR(16),
    -> YEAR char(4))ENGINE  MyISAM;
Query OK, 0 rows affected (0.08 sec)
#查询表结构
mysql> DESCRIBE classics;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| author | varchar(128) | YES  |     | NULL    |       |
| title  | varchar(128) | YES  |     | NULL    |       |
| type   | varchar(16)  | YES  |     | NULL    |       |
| YEAR   | char(4)      | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
#数据类型
CHAR(n)   VARCHAR(n)
BINARY(n) VARBINARY(n)
TEXT(n)   TINYTEXT(n)   MEDIUMTEXT(n)  LONGTEXT(n)
TINYBLOB(n)  BLOB(n)
TINYINT   SMALLINT      MEDIUMINT      INT/INTEGER    BIGINT
FLOAT     DOUBLE/REAL
DATETIME  DATE          TIMESTAMP      TIME           YEAR
INT UNSIGNED,  NOT NULL,  AUTO_INCREMENT,  KEY
#添加表列id
mysql> ALTER TABLE classics ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
#删除表列
mysql> ALTER TABLE classics DROP id;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
#向表中添加数据
mysql> INSERT INTO classics(author,title,type,year)VALUES('Shibingfan', 'LEARN MYSQL', 'EDUCATION', '1887');
Query OK, 1 row affected (0.00 sec)
#重命名表
mysql> ALTER TABLE classics RENAME mytable ;
Query OK, 0 rows affected (0.02 sec)
#改变列数据类型
mysql> ALTER TABLE mytable MODIFY year SMALLINT;
Query OK, 5 rows affected (0.08 sec)
#添加新列
mysql> ALTER TABLE mytable ADD pages SMALLINT UNSIGNED;
Query OK, 5 rows affected (0.08 sec)
#重命名列
mysql> ALTER TABLE mytable CHANGE type category VARCHAR(16);
Query OK, 0 rows affected (0.31 sec)
#删除列
mysql> ALTER TABLE mytable DROP pages;//DROP命令不可逆
Query OK, 5 rows affected (0.33 sec)
#删除表
mysql> DROP TABLE mytable;
Query OK, 0 rows affected (0.02 sec)
#创建索引
mysql> ALTER TABLE mytable ADD INDEX(author(20));
mysql> CREATE INDEX author ON mytable(author(20));//也可在创建表时添加索引
Query OK, 5 rows affected (0.08 sec)
#创建全文索引
mysql> ALTER TABLE mytable ENGINE = MyISAM;		//将表转换为MyISAM
mysql> ALTER TABLE mytable ADD FULLTEXT(author,title);
#数据库查询
SELECT * FROM mytable;
SELECT author,title FROM mytable;
SELECT COUNT(*) FROM mytable;			//计数
SELECT DISTINCT author FROM mytable; 	//去重
DELETE FROM mytable WHERE title = 'LEARN MYSQL';
SELECT * FROM mytable WHERE author='Shibingfan';
SELECT * FROM mytable WHERE author LIKE "Shi%";		//like关键字，模糊匹配
SELECT author FROM mytable LIMIT 3;					//LIMIT关键字，限定返回的行数
SELECT author FROM mytable LIMIT 3，1;				//LIMIT关键字，限定从表的什么位置开始返回多少行
SELECT * FROM mytable WHERE MATCH(author,title) AGAINST('Shi'); //自然语言在FULLTEXT索引列上搜索
SELECT * FROM mytable WHERE MATCH(author,title) AGAINST('+Shi -bing' IN BOLLEAN MODE);//布尔模式下搜索
UPDATE mytable SET author='Shibf' WHERE author='shibingfan'; 	//更新数据
SELECT author,title FROM mytable ORDER BY author;				//排序
SELECT category,COUNT(author) FROM mytable GROUP BY category;	//分组
#连接表，新建customers表
SELECT * FROM mytable,customers WHERE mytable.isbn=customers.isbn;
SELECT * FROM customers NATURAL JOIN mytable;		//自然连接，将相同列名的表自动合并
SELECT * FROM customers JOIN mytable ON mytable.isbn=customers.isbn;
SELECT author,title FROM mytable AS au,ti;			//AS重命名
SELECT * FROM mytable,customers WHERE mytable.isbn=customers.isbn AND author='shibingfan';
 
 
#事物
#事物存储引擎InnoDB
mysql> CREATE TABLE accounts(number INT, balance FLOAT, PRIMARY KEY(number))ENGINE InnoDB;
mysql> INSERT INTO accounts(number,balance) VALUES(12345,1025.50);
mysql> INSERT INTO accounts(number,balance) VALUES(67890,140.00);
mysql> SELECT * FROM accounts ;
+--------+---------+
| number | balance |
+--------+---------+
|  12345 |  1025.5 |
|  67890 |     140 |
+--------+---------+
#事物处理BEGIN, COMMIT, ROOLBACK
mysql> BEGIN;
mysql> UPDATE accounts SET balance=balance+25.11 WHERE number=12345;
mysql> COMMIT; 		//确认提交，数据库发生更改
mysql> ROLLBACK;	//回滚操作，数据库恢复到BEGIN之前的状态
#使用EXPLAIN，得到查询快照，进而对查询进行优化
mysql> EXPLAIN SELECT * FROM accounts WHERE number='12345';
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | accounts | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
#备份和恢复
mysql> LOCK TABLES mytable READ,accounts READ;		//锁定表UNLOCK TABLES;
mysql> mysqldump -u user -ppassword publications;
mysql> mysqldump -u user -ppassword publications mytable > mytable.sql;
mysql> mysqldump -u user -ppassword publications --all-database > all_db.sql;
mysql> mysqldump -u user -ppassword < all_db.sql;
mysql> mysqldump -u user -ppassword -D publications < mytable.sql;