mysql与php使用基础复习

错误

using a password on the command line interface can be insecure.

在命令行中输入账号密码信息是不安全的。

#mysql -uroot -p回车，输入密码。

You can’t specify target table for update in FROM clause.

不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。

mysql基础原生语句

#mysql -uroot -ppassword;//连接数据库

#CREATE DATABASE database_name;//创建数据库

#CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;//如果数据库不存在则创建，创建并设置编码集为utf8

#DROP DATABASE database_name;//删除数据库

#USE database_name;//选择数据库

#CREATE TABLE table_name(COLUMN_NAME COLUMN_TYPE);//创建MySQL数据表

#DROP TABLE database_name;//删除数据表，删除表内容和结构，空间释放

#TRUNCATE TABLE table_name;//删除表全部数据，保留表结构，立即释放磁盘空间（不能回滚，不能恢复数据，不能用于参与了索引视图的表）

#DELETE FROM table_name;//删除表全部数据，表结构不变

delete a from person a,person b where a.email=b.email and a.id>b.id//还可以这个用法？！

#OPTIMIZE TABLE TABLE_NAME;//delete操作后，该命令指示立即释放磁盘空间

#INSERT INTO table_name(field) VALUES (value),(value);//插入数据【表名与字段名用反单引号（为区分MySQL与普通字符），字符串等根据需求用单双引号】

INSERT INTO test1 (`id`,`name`) VALUES (1,'zarm');

#SELECT column_name,column_name FROM table_name;//在MySQL数据库中查询

#SELECT * FROM table_name WHERE name='1';//查询字句

#UPDATE table_name SET field=new_value,field=new_value;//更新

#DELETE FROM table_name;//删除数据

#SELECT * FROM table_name WHERE field LIKE '';//模糊查询

#SELECT * FROM table1 UNION [ALL] SELECT * FROM table2;//组合查询结果

#SELECT * FROM table ORDER BY id ASC|DESC;//排序

#SELECT * FROM table WHERE GROUP BY field;//分组

#SELECT * FROM table GROUP BY field WITH ROLLUP;//在分组统计数据基础上再进行相同的统计。

#SELECT * FROM table1 a INNER JOIN table2 b ON a.id = b.id;//inner join查询

#SELECT * FROM table1,table2 WHERE table1.id=table2.id;//同上

#SELECT * FROM table1 a LEFT JOIN table2 b ON a.id=b.id;//左连接

#SELECT * FROM table1 a RIGHT JOIN table2 b ON a.id=b.id;//右连接

左右连接取决于哪一张表需要全部行显示

#SELECT * FROM table WHERE name IS [NOT] NULL;//null值处理

#ALTER TABLE table_name DROP i;//删除i字段

#ALTER TABLE table_name ADD i int [FIRST|AFTER a];//添加字段

#ALTER TABLE table_name MODIFY c CHAR(10) MODIFY NOT NULL DEFAULT 100;//修改字段类型

#ALTER TABLE table_name CHANGE i j INT;//修改字段名，指定新字段名及类型

#ALTER TABLE table_name ALTER i SET DEFAULT 1000;//修改字段的默认值

#ALTER TABLE table_name ALTER i DROP DEFAULT;//删除字段默认值

#ALTER TABLE table_name ENGINE = MYISAM;//修改表类型

#CREATE INDEX indexname ON table_name;//创建索引

#ALTER TABLE table_name ADD INDEX index_name;//添加索引

#DROP INDEX [index_name] ON table_name;//删除索引

#CREATE UNIQUE INDEX index_name ON table_name;//创建唯一索引

#ALTER TABLE table_name ADD UNIQUE [index_name];//修改表结构

#ALTER TABLE table_name ADD PRIMARY KEY (i);//添加主键

#ALTER TABLE table_name DROP PRIMARY KEY;//删除主键

#SELECT COUNT(*) AS field FROM table_name GROUP BY field;//统计重复数据

#SELECT DISTINCT field FROM table_name;//过滤重复数据

#ALTER IGNORE TABLE table_name ADD PRIMARY KEY(field);//通过添加索引和主键删除表中的重复记录

#SELECT * FROM table_name INTO OUTFILE 'file_name';//导出数据

#SELECT * FROM table_name INTO OUTFILE 'file_name' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';//通过命令项来设置数据输出的指定格式。

having&where

having筛选成组后的数据，where在聚合前先筛选记录。

如having写在group by后面，where写在group 不要前面

临时表

用于保存临时数据，只有当前连接可见，当关闭连接时，会自动删除并释放所有空间。若使用php脚本创建临时表，当php脚本执行完毕后，自动销毁临时表。当使用SHOW TABLES命令时，无法看到临时表。如果退出当前MySQL会话，该临时表已经被销毁。也可以在当前会话使用DROP TABLE命令手动删除临时表。

CREATE REMPORARY TABLE 临时表名 AS{

        SELECT * FROM 旧表名

        LIMIT 0,1000

};//用查询直接创建临时表

复制表

1. CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;????只复制表结构到新表
2. CREATE TABLE 新表 LIKE 旧表;
3. CREATE TABLE 新表 SELECT * FROM 旧表；//复制表结构及数据到新表
4. INSERT INTO table_name SELECT * FROM table_name;
5. CREATE TABLE name ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY )  AS (SELECT * FROM name);//创建表的同时定义表中的字段信息

php与mysql

mysqli_connect(host,username,password,dbname,port,socket);//使用php连接mysql

host：主机名或IP地址

username：使用MySQL用户名

password：规定MySQL密码

dbname：规定默认使用的数据库

port：尝试连接到MySQL服务器的端口号

socket：规定socket或要使用的已命名pipe

mysqli_close(mysqli $link)//断开数据库连接

【已打开的非持久连接会在脚本执行完毕后自动关闭】

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_connet($dbhost,$dbuser,$dbpass);
if(!$conn){
    die('error:'.mysqli_error());
}
mysqli_close($conn);

mysqli_query(connection,query,resultmode);//php脚本执行MySQL语句

connection:规定使用的MySQL连接

query：查询字符串

resultmode：MYSQLI_USE_RESULT【检索大量数据】MYSQLI_STORE_RESULT【默认】

mysqli_select_db(connection,dbname);//选择MySQL数据库

mysqli_fetch_array();//从结果集中取得一行作为数字数组或关联数组

mysqli_fetch_assoc();//从结果集中取得一行作为关联数组

mysqli_affected_rows();//查询语句影响的记录数

序列

重置序列

ALTER TABLE table_name DROP id;

ALTER TABLE table_name ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,ADD PRIMARY KEY(id);

重置序列的开始值

ALTER TABLE id AUTO_INCREMENT = 100;

处理重复数据

防止表中出现重复数据需要设置指定的字段为主键或唯一索引来保证数据的唯一性。

INSERT IGNORE INTO table_name () VALUES ();//会忽略数据库中已经存在的数据，如果没有数据旧插入，有就跳过此数据

REPLACE INTO table_name () VALUES ();//如果存在与primary或unique相同的记录，先删除掉再插入新纪录

MySQL函数

LAST_INSERT_ID()获取最后的插入表中的自增列的值

防止MySQL注入

通过把SQL命令插入到web表单递交或输入域名或页面请求的查询字符串，达到欺骗服务器执行而已的SQL命令。必须认定用户输入的数据都是不安全的，需要对用户输入的数据进行过滤处理。

注意：

对用户的输入进行校验（正则表达式，限制长度，单双引号进行转换）；

不使用动态拼装sql，使用参数化的sql或直接使用存储过程进行数据查询存取；

不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接；

不要直接存放机密信息，加密或者hash掉密码和敏感的信息；

尽可能少地提示异常信息，最好使用自定义的错误信息对原始错误信息进行保证；

可以采用辅助软件或网站平台来检测。

mysqli_real_escape_string($conn,$name);根据当前连接的字符集，对sql语句中的特殊字符进行转义

addcslashes(mysqli_real_escape_string($conn,'string').'%_');//在指定的字符前添加反斜杠(/%,/_)

函数

排名：

rank 并列跳跃排名 

 dense_rank 并列连续排名

row_number 连续排名

 其他

 UTF-8编码中，一个汉字为3字节；GBK中，一个汉字为2字节。

delete\drop\truncate:

drop和truncate是删除表数据，drop删除数据与结构。速度上drop>truncate>delete