金沙js8331mysql入门之1小时学会MySQL基础

MySQL入门

mySQL

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于
Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB
应用方面,MySQL是最好的 RDBMS (Relational Database Management
System,关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL
软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择
MySQL 作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache
可组成良好的开发环境。

1.
数据库是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。

2.
使用关系型数据库管理系统来存储和管理的大数据量。关系型数据库是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

3. RDBMS特点:

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database

4. RDBMS 术语

冗余:存储两倍数据,冗余可以使系统速度更快。

主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

外键:外键用于关联两个表。

复合键:复合键将多个列作为一个索引键,一般用于复合索引。

索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

参照完整性:
参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性

5.
MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

MySQL管理6. 启动及关闭 MySQL服务器:

检查MySQL服务器是否启动:

ps -ef | grepmysqld

启动MySQL服务器:

root@host# cd/usr/bin

./safe_mysqld&

关闭目前运行的 MySQL 服务器:

root@host# cd/usr/bin

./mysqladmin-u root -p shutdown

Enterpassword: ******

7. MySQL 用户设置

在 MySQL 数据库中的 user 表添加新用户:

root@host# mysql -u root –p //选择数据库Enter password:*******mysql use mysql;Database changedmysql INSERT INTO user(host, user, password,select_priv, insert_priv,update_priv) //设置权限YVALUES ('localhost', 'guest',PASSWORD('guest123'), 'Y', 'Y','Y');Query OK, 1 row affected (0.20 sec)mysql FLUSH PRIVILEGES;

注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。

另外一种添加用户的方法为通过SQL的 GRANT命令

mysql GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP- ON TUTORIALS.*- TO 'zara'@'localhost'- IDENTIFIED BY'zara123';

8. 管理MySQL的命令

USE 数据库名 :选择要操作的MySQL数据库:

mysql use W3CSCHOOL;

Database changed

SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表:

mysql SHOWDATABASES;

SHOW TABLES: 显示指定数据库所有表,用该命令前需用 use
命令选择操作的数据库。

mysql useW3CSCHOOL;

Database changed

mysql SHOW TABLES;

SHOW COLUMNS FROM
数据表:显示数据表的属性,属性类型,主键信息,是否NULL,默认值等其他信息。

mysql SHOW COLUMNSFROM W3Cschool_tbl;

+—————–+————–+——+—–+———+——-+

| Field | Type | Null | Key | Default | Extra |

+—————–+————–+——+—–+———+——-+

SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY。

SHOW TABLE STATUS LIKE 数据表G:
该命令将输出MySQL数据库管理系统的性能及统计信息。

mysql SHOW TABLESTATUS FROM W3CSCHOOL; # 显示数据库 W3CSCHOOL
中所有表的信息

mysql SHOW TABLESTATUS from W3CSCHOOL LIKE ‘W3Cschool%’;
#表名以W3Cschool开头的表的信息

mysql SHOW TABLESTATUS from W3CSCHOOL LIKE ‘W3Cschool%’G; #加上
G,查询结果按列打印

9. PHP MySQL函数格式:mysql_function(value,value,…);

MySQL 连接10.使用MySQL二进制方式连接

[root@host]# mysql -uroot -pEnter
password:******登录成功后会出现mysql
命令提示窗口,你可以在上面执行任何 SQL 语句。退出 mysql
命令提示窗口可以使用exit 命令:mysql exit

11.使用 PHP脚本连接 MySQL

PHP 提供了 mysql_connect()
函数来连接数据库。connectionmysql_connect(server,user,passwd,new_link,client_flag);5各参数均可选使用PHP的mysql_close()
函数来断开与MySQL数据库的链接。bool mysql_close (resource
$link_identifier
);通常不要用mysql_close(),因为已打开的非持久连接会在脚本执行完毕后自动关闭。mysql_close()
不会关闭由 mysql_pconnect() 建立的持久连接

$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){die('Could not connect: ' .mysql_error());}echo 'Connected successfully';mysql_close($conn);

MySQL 创建/删除数据库12.使用 mysqladmin创建数据库

用root用户登录,root用户拥有最高权限,可以使用 mysql mysqladmin
命令来创建数据库。[root@host]#mysqladmin -u root -pcreate/drop
W3CSCHOOLEnter password:*****

13.使用 PHP脚本创建数据库

PHP使用 mysql_query 函数来创建或者删除 MySQL 数据库。

bool mysql_query( sql, connection); $conn = mysql_connect($dbhost,$dbuser, $dbpass);if(! $conn ){ die('连接错误: ' . mysql_error());}echo '连接成功br /';$sql= 'CREATE/DROP DATABASE W3CSCHOOL';$retval= mysql_query( $sql, $conn );if(! $retval ){ die('创建数据库失败: ' . mysql_error());}echo "数据库 W3CSCHOOL创建成功n";mysql_close($conn);

MySQL 选择数据库14.使用PHP脚本选择MySQL数据库

PHP 提供了函数 mysql_select_db来选取一个数据库。

bool mysql_select_db( db_name,connection );$conn = mysql_connect($dbhost,$dbuser, $dbpass);if(! $conn ){ die('连接失败: ' . mysql_error());}echo '连接成功';mysql_select_db('W3CSCHOOL' );mysql_close($conn);

MySQL 创建/删除数据表15.MySQL 创建数据表

创建MySQL数据表需要以下信息:

表名

表字段名

定义每个表字段

创建语法:CREATE TABLE table_name (column_name column_type);

删除语法:DROP TABLE table_name ;

以下例子中我们将在 W3CSCHOOL 数据库中创建数据表w3cschool_tbl:

tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE, PRIMARY KEY ( w3cschool_id ));

16.通过命令提示符创建表

使用 SQL 语句 CREATE TABLE 来创建数据表。

mysql CREATE TABLE w3cschool_tbl( - w3cschool_id INT NOTNULL AUTO_INCREMENT, - w3cschool_titleVARCHAR(100) NOT NULL, - w3cschool_authorVARCHAR(40) NOT NULL, - submission_date DATE, - PRIMARY KEY (w3cschool_id ) - );

MySQL命令终止符为分号 (;) 。

17.使用PHP脚本创建/删除数据表或插入数据

语法:bool mysql_query( sql, connection);

$sql = "CREATE TABLEtutorials_tbl( "创建 "tutorial_id INT NOTNULL AUTO_INCREMENT, ". "tutorial_titleVARCHAR(100) NOT NULL, ". "tutorial_authorVARCHAR(40) NOT NULL, ". "submission_dateDATE, ". "PRIMARY KEY (tutorial_id )); ";$sql = "DROP TABLEw3cschool_tbl";删除 mysql_select_db( 'TUTORIALS' );$retval = mysql_query( $sql, $conn); //判断是否成功而设置的参数;if(! $retval ){ die('数据表创建失败: ' . mysql_error());}echo "数据表创建成功n";mysql_close($conn);

MySQL 插入数据

18.向MySQL数据表插入数据通用的 INSERT INTO SQL语法:

INSERT INTO table_name (field1, field2,…fieldN ) VALUES (
value1,value2,…valueN );

如果数据是字符型,必须使用单引号或者双引号,如:”value”。

w3cschool_tbl表插入一条数据:

mysql INSERT INTOw3cschool_tbl -(w3cschool_title,
w3cschool_author,submission_date) -VALUES -(“Learn PHP”, “JohnPoul”,
NOW());

(-)不是SQL语句的一部分,它仅表示一个新行,如SQL语句太长,可通过回车键创建一个新行编写SQL语句,SQL语句的命令结束符为分号。

19.使用PHP脚本插入数据

$sql = “INSERT INTO w3cschool_tbl “.
“(w3cschool_title,w3cschool_author,submission_date) “. “VALUES “.
“(‘$w3cschool_title’,’$w3cschool_author’,’$submission_date’)”;

MySQL 查询数据

20.为在MySQL数据库中查询数据通用的 SELECT语法:

SELECT column_name,column_nameFROM table_name[WHERE Clause][OFFSET
M ][LIMIT N]

可以通过SELECT 命令读取一条或者多条记录。

可以通过OFFSET指定SELECT语句开始查询的数据偏移量默认情况下偏移量为0。

可以使用 LIMIT 属性来设定返回的记录数。

你可以使用星号来代替其他字段,SELECT语句会返回表的所有字段数据

mysql SELECT * from w3cschool_tbl

21.使用PHP脚本来获取数据

使用PHP函数的mysql_query()及SQL
SELECT命令来获取数据。该函数用于执行SQL命令,然后通过 PHP 函数
mysql_fetch_array() 来使用或输出所有查询的数据。

尝试以下实例来显示数据表w3cschool_tbl 的所有记录

$sql = ‘SELECT w3cschool_id,w3cschool_title,
w3cschool_author,submission_date FROM
w3cschool_tbl’;mysql_select_db(‘W3CSCHOOL’);$retval = mysql_query(
$sql, $conn );if(! $retval ){ die(‘Could not get data: ‘
.mysql_error());}while($row =mysql_fetch_array($retval,
MYSQL_ASSOC))while($row =mysql_fetch_assoc($retval))

用MYSQL_NUM参数显示数据表

while($row =mysql_fetch_array($retval, MYSQL_NUM))

用MYSQL_NUM参数显示数据表

{ echo “Tutorial ID:{$row[‘w3cschool_id’]} br”.
“Title:{$row[‘w3cschool_title’]} br “.
“Author:{$row[‘w3cschool_author’]} br “. “Submission Date :
{$row[‘submission_date’]}br “.
“——————————–br”;}mysql_free_result($retval);
释放游标内存echo “Fetched data successfullyn”;mysql_close($conn);

MYSQL_ASSOC,
设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引。

MySQL 选择数据

22.从MySQL表中使用SQL SELECT语句来读取数据。

如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中以下是SQL
SELECT语句使用 WHERE 子句从数据表中读取数据的通用语法:

SELECT field1,field2,…fieldN FROM table_name1, table_name2…[WHERE
condition1 [AND[OR]] condition2…..

WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。使用 LIKE
来比较字符串,否则MySQL的WHERE子句的字符串比较是不区分大小写的。
你可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。

23.使用PHP脚本读取数据

使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE
子句的命令来获取数据。该函数用于执行SQL命令,然后通过
mysql_fetch_array() 来输出所有查询的数据。

$sql = ‘SELECT w3cschool_id,w3cschool_title, w3cschool_author,
submission_date FROM w3cschool_tbl WHEREw3cschool_author=”Sanjay”‘;

MySQL UPDATE

24.修改或更新MySQL中的数据,我们可以使用SQL UPDATE 命令来操作。

通用SQL语法:

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

更新数据表中 w3cschool_id 为 3 的 w3cschool_title 字段值:

mysqlUPDATE w3cschool_tbl – SET w3cschool_title=’Learning JAVA’- WHERE
w3cschool_id=3;

使用PHP脚本更新数据

$sql = ‘UPDATE w3cschool_tbl SETw3cschool_title=”Learning JAVA” WHERE
w3cschool_id=3’;

MySQL DELETE

25.DELETE FROM table_name[WHERE Clause

如果没有指定 WHERE 子句,MySQL表中的所有记录将被删除。

可以在 WHERE 子句中指定任何条件

删除w3cschool_tbl 表中 w3cschool_id 为3 的记录

mysql DELETE FROMw3cschool_tbl WHERE w3cschool_id=3;

用 PHP 脚本删除数据

$sql = ‘DELETE FROMw3cschool_tbl WHERE w3cschool_id=3’;

MySQL LIKE 子句

QL LIKE 子句中使用百分号(%)字符来表示任意字符

没有使用百分号(%),LIKE 子句与等号的效果是一样的。26.QL SELECT语句使用
LIKE子句从数据表中读取数据的通用语法:SELECT field1,field2,…fieldN
table_name1, table_name2…WHERE field1 LIKEcondition1 [AND [OR]]
filed2 = ‘somevalue’LIKE 通常与 % 一同使用,类似于一个元字符的搜索

在PHP脚本中使用 LIKE 子句

$sql = ‘SELECTw3cschool_id, w3cschool_title,
w3cschool_author,submission_date FROM w3cschool_tbl WHERE
w3cschool_author LIKE”%jay%”‘;

MySQL 排序

SELECT field1,field2,…fieldN table_name1, table_name2…

ORDER BY field1,[field2…] [ASC [DESC]]

ASC 或 DESC
关键字来设置查询结果是按升序或降序排列。默认情况下,它是按升排列。

MySQL 分组

SELECT column_name,function(column_name)FROM table_nameWHERE
column_nameoperator valueGROUP BY column_name;

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计。

coalesce 来设置一个可以取代NUll 的名称,coalesce 语法:

select coalesce(a,b,c);

参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a
b c 都为null ,则返回为null。

mysql SELECTcoalesce(name, ‘总数’), SUM(singin) as singin_countFROM
employee_tbl GROUP BY name WITHROLLUP;

+————————–+————–+

| coalesce(name, ‘总数’) | singin_count |

+————————–+————–+

| 小丽 | 2 |

| 小明 | 7 |

| 小王 | 7 |

| 总数 | 16 |

+————————–+————–+

MySQL 多表查询

27.在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类:

INNER JOIN:获取两个表中字段匹配关系的记录。LEFT
JOIN:获取左表所有记录,即使右表没有对应匹配的记录。RIGHT JOIN:与 LEFT
JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

| w3cschool_author |w3cschool_count |

+—————–+—————-+

| mahran | 20 |

| mahnaz | NULL |

| Jen | NULL |

| Gill | 20 |

| John Poul | 1 |

| Sanjay | 1 |

+—————–+—————-+

mysql SELECT * fromw3cschool_tbl;

+————-+—————-+—————–+—————–+

| w3cschool_id | w3cschool_title | w3cschool_author |submission_date
|

+————-+—————-+—————–+—————–+

| 1 | Learn PHP | John Poul |2007-05-24 || 2 | LearnMySQL | Abdul S |
2007-05-24 || 3 | JAVATutorial | Sanjay | 2007-05-06 |

连接以上两张表来读取w3cschool_tbl表中所有w3cschool_author字段在tcount_tbl表对应的w3cschool_count字段值:

mysql SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count
FROM w3cschool_tbl aINNER JOIN tcount_tbl b ON a.w3cschool_author =
b.w3cschool_author;

+———–+—————+————–+

| w3cschool_id | w3cschool_author | w3cschool_count |

+———–+—————+————–+

| 1 | John Poul | 1 |

| 3 | Sanjay | 1 |

w3cschool_tbl 为左表,tcount_tbl 为右表,

mysql SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count
FROM w3cschool_tbl a LEFTJOIN tcount_tbl b ON a.w3cschool_author =
b.w3cschool_author;

+————-+—————–+—————-+

| w3cschool_id | w3cschool_author | w3cschool_count |

+————-+—————–+—————-+

| 1 | John Poul | 1 |

| 2 | Abdul S | NULL |

| 3 | Sanjay | 1 |

左边的数据表w3cschool_tbl的所有选取的字段数据,即便在右侧表tcount_tbl中没有对应的w3cschool_author字段值Abdul
S。

MySQL NULL

IS NULL: 当列的值是NULL,此运算符返回true。IS NOT NULL: 当列的值不为NULL,
运算符返回true。

NULL值与任何其它值的比较永远返回false,

使用PHP脚本处理 NULL 值:

PHP脚本中你可以在 if…else
语句来处理变量是否为空,并生成相应的条件语句。

MySQL 正则表达式

28.MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^
也匹配 ‘n’ 或 ‘r’ 之后的位置。$
匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$
也匹配 ‘n’ 或 ‘r’ 之前的位置。. 匹配除”n”
之外的任何单个字符。要匹配包括 ‘n’ 在内的任何字符,请使用象 ‘[.n]’
的模式。

实例(表名:person_tbl )来加深我们的理解:

查找name字段中以’st’为开头的所有数据:

mysql SELECT name FROM person_tbl WHERE name REGEXP ‘^st’;

查找name字段中以’ok’为结尾的所有数据:

mysql SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’;

查找name字段中包含’mar’字符串的所有数据:

mysql SELECT name FROM person_tbl WHERE name REGEXP ‘mar’;

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据:

mysql SELECT name FROM person_tbl WHERE name REGEXP’^[aeiou]|ok$’;

MySQL 事务

29.MySQL 事务主要用于处理操作量大,复杂度高的数据。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务

事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行

事务用来管理insert,update,delete语句

事务必须满足4个条件: Atomicity、Consistency、Isolation、Durability

1、事务的原子性:一组事务,要么成功;要么撤回。

2、稳定性: 有非法数据,事务撤回。

3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。

4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,
innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

在MySQL控制台使用事务来操作:

1,开始一个事务

start transaction

2, 做保存点

savepoint 保存点名称

3, 操作

4,可以回滚,可以提交,没有问题,就提交,有问题就回滚。

PHP中使用事务实例

mysql_query(“SETAUTOCOMMIT=0”);//设置为不自动提交,因为MYSQL默认立即执行mysql_query(“BEGIN”);//开始事务定义

if(!mysql_query("insertinto trans (id) values('2')")){mysql_query("ROOLBACK");//判断当执行失败时回滚}mysql_query("COMMIT");//执行事务mysql_close($handler);MySQL ALTER

30.修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。

使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i字段:

mysql ALTER TABLEtestalter_tbl DROP i;

数据表中只剩余一个字段则无法使用DROP来删除字段。

ADD 子句来想数据表中添加列,在表 testalter_tbl 中添加 i
字段,并定义数据类型:

mysql ALTER TABLEtestalter_tbl ADD i INT;

以下 ALTERTABLE 语句, 在执行成功后,使用 SHOW COLUMNS 查看表结构的变化:

ALTER TABLEtestalter_tbl DROP i;ALTER TABLEtestalter_tbl ADD i INT
FIRST;ALTER TABLEtestalter_tbl DROP i;ALTER TABLEtestalter_tbl ADD i
INT AFTER c;

FIRST 和 AFTER 关键字只占用于 ADD
子句,所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用
ADD 来添加字段并设置位置。

修改字段类型及名称:

31.在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

把字段 c 的类型从 CHAR(1) 改为 CHAR(10),可以执行以下命令:mysql ALTER
TABLEtestalter_tbl MODIFY c CHAR(10);使用 CHANGE 子句,
语法有很大的不同。 在 CHANGE
关键字之后,紧跟着的是你要修改的字段名,然后指定新字段的类型及名称。尝试如下实例:mysql
ALTER TABLEtestalter_tbl CHANGE i j BIGINT;p如果你现在想把字段 j 从
BIGINT 修改为 INT,SQL语句如下:mysql ALTER TABLEtestalter_tbl CHANGE j
j INT;

ALTER修改字段的默认值,mysql ALTER TABLEtestalter_tbl ALTER i SET
DEFAULT 1000;ALTER 及DROP删除字段的默认值, ALTER TABLEtestalter_tbl
ALTER i DROP DEFAULT;ALTER及 TYPE修改数据表类型,mysql ALTER
TABLEtestalter_tbl TYPE = MYISAM;ALTER TABLE
使用RENAME修改数据表的名称,mysql ALTER TABLEtestalter_tbl RENAME TO
alter_tbl;

MySQL 索引

索引可以大大提高MySQL的检索速度索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。创建索引,确保该索引是应用在
SQL 查询语句的条件(一般作为 WHERE
子句的条件)。索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。3方式创建普通索引

CREATE INDEX indexName ONmytable(username(length));

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定
length。

修改表结构

ALTER mytable ADD INDEX[indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOTNULL, INDEX [indexName](username(length)) ); 

删除索引的语法

DROP INDEX [indexName] ONmytable;

唯一索引:前面加UNIQUE

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引:

ALTER TABLE tbl_name ADD PRIMARYKEY
(column_list):该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。ALTER
TABLE tbl_name ADD UNIQUEindex_name
(column_list):这条语句创建索引的值必须是唯一的。ALTER TABLE tbl_name
ADD INDEXindex_name
(column_list):添加普通索引,索引值可出现多次。ALTER TABLE tbl_name ADD
FULLTEXTindex_name (column_list):该语句指定了索引为 FULLTEXT
,用于全文索引。

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空。实例如下:

mysql ALTER TABLEtestalter_tbl MODIFY i INT NOT NULL;

mysql ALTER TABLEtestalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键:

mysql ALTER TABLEtestalter_tbl DROP PRIMARY KEY;

SHOW INDEX 命令列出表中的相关的索引信息。可以通过添加 G
来格式化输出信息。

mysql SHOW INDEX FROMtable_nameG

MySQL 临时表

临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。

mysql CREATE TEMPORARY TABLE SalesSummary

mysql DROP TABLE SalesSummary;

MySQL 复制表

创建新的克隆表clone_tbl。如果你想拷贝数据表的数据你可以使用 INSERT
INTO… SELECT 语句来实现。

mysql INSERT INTOclone_tbl (w3cschool_id, – w3cschool_title, –
w3cschool_author, – submission_date) – SELECT
w3cschool_id,w3cschool_title, –
w3cschool_author,submission_date-FROM w3cschool_tbl;

MySQL 元数据

想知道MySQL以下三种信息:

查询结果信息: SELECT,UPDATE 或
DELETE语句影响的记录数。数据库和数据表的信息:
包含了数据库及数据表的结构信息。MySQL服务器信息:
包含了数据库服务器的当前状态,版本号等。

使用do( ) 执行 $query

my $count = $dbh-do($query);

使用prepare( )及 execute( ) 执行 $query

my $sth =$dbh-prepare ($query);

my $count =$sth-execute ( );

在PHP中,使用mysql_affected_rows( ) 函数获取查询语句影响的记录数。

$result_id =mysql_query ($query, $conn_id);# 如果查询失败返回$count = ($result_id mysql_affected_rows ($conn_id) : 0);print ("$countrows were affectedn");

数据库和数据表列表

PERL 实例

# 获取当前数据库中所有可用的表。

my @tables =$dbh-tables ( );foreach $table (@tables){ print "Table Name $tablen";}

PHP 实例:

$db_list =mysql_list_dbs($con);while ($db = mysql_fetch_object($db_list)){ echo $db-Database . "br/";}

MySQL 序列

MySQL序列是一组整数:1, 2, 3, …,

用 MySQLAUTO_INCREMENT 来定义列。

mysql CREATE TABLEinsect

– (

– id INT UNSIGNED NOT NULLAUTO_INCREMENT,

用 SQL中的LAST_INSERT_ID() 函数来获取最后的插入表中的自增列的值。

PERL实例

使用mysql_insertid 属性来获取 AUTO_INCREMENT 的值。实例如下:

$dbh-do(“INSERT INTO insect (name,date,origin)

VALUES(‘moth’,’2001-09-14′,’windowsill’)”);

my $seq =$dbh-{mysql_insertid};

PHP实例

PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中
AUTO_INCREMENT列的值。

mysql_query("INSERT INTO insect (name,date,origin)VALUES('moth','2001-09-14','windowsill')",$conn_id);$seq = mysql_insert_id($conn_id);

重置序列

删除了数据表中的多条记录,并对剩下数据的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增的列,然后重新添加来实现。

mysql ALTER TABLEinsect DROP id;

mysql ALTER TABLEinsect

– ADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST,

– ADD PRIMARY KEY (id);

一般情况下序列的开始值为1,但如果你需要指定一个开始值100:

– id INT UNSIGNEDNOT NULL AUTO_INCREMENT = 100,

或在表创建成功后,通过以下语句来实现:

mysql ALTER TABLE tAUTO_INCREMENT = 100;

MySQL 处理重复数据

防止表中出现重复数据

在MySQL数据表中设置指定的字段为PRIMARY
KEY或者UNIQUE索引保证数据的唯一性。

设置表中字段first_name,last_name数据不能重复,你可以设置双主键模式来设置数据的唯一性,
如果你设置了双主键,那么那个键的默认值不能为NULL,可设置为NOT
NULL。如下所示:

CREATE TABLE person_tbl( first_name CHAR(20) NOT NULL, last_name CHAR(20) NOT NULL, sexCHAR(10), PRIMARY KEY (last_name, first_name));

INSERT IGNOREINTO与INSERT INTO的区别就是INSERT
IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

用了INSERT IGNORE INTO,执行后不会出错,也不会向数据表中插入重复数据:

mysql INSERT IGNORE INTO person_tbl(last_name, first_name)

-VALUES( ‘Jay’, ‘Thomas’);

Query OK, 1 row affected (0.00 sec)

REPLACE INTO into如果存在primary 或
unique相同的记录,则先删除掉。再插入新记录。

UNIQUE(last_name, first_name)

查询重复记录

select user_name,count(*) as count fromuser_table group by user_name
having count1;

select * from people

where peopleId in (select peopleId from peoplegroup by peopleId having
count(peopleId) 1)

统计重复数据

统计表中 first_name 和 last_name的重复记录数:

mysql SELECT COUNT(*) as repetitions,last_name, first_name -FROM person_tbl -GROUP BY last_name, first_name -HAVING repetitions  1;

过滤重复数据

读取不重复的数据可以在SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql SELECT DISTINCT last_name, first_name

-FROM person_tbl

-ORDER BY last_name;

也可以使用 GROUP BY 来读取数据表中不重复的数据:

mysql SELECT last_name, first_name -FROM person_tbl -GROUP BY (last_name, first_name);

删除重复数据

删除数据表中的重复数据,你可以使用以下的SQL语句:

mysql CREATE TABLE tmp SELECT last_name,first_name, sex - FROMperson_tbl; - GROUP BY(last_name, first_name);mysql DROP TABLE person_tbl;mysql ALTER TABLE tmp RENAME TO person_tbl;

也可以在数据表中添加 INDEX 和 PRIMAY
KEY这种简单的方法来删除表中的重复记录。方法如下:

mysql ALTER IGNORE TABLE person_tbl -ADD PRIMARY KEY (last_name, first_name);

MySQL 及 SQL 注入

没有过滤特殊字符时,出现的SQL情况:

// 设定$name 中插入了我们不需要的SQL语句

$name = “Qadir’;DELETE FROM users;”;

mysql_query(“SELECT* FROM users WHERE name='{$name}'”);

以上的注入语句中,我们没有对 $name
的变量进行过滤,$name中插入了我们不需要的SQL语句,将删除 users
表中的所有数据。

防止SQL注入,注意以下几个要点:

1.永远不要信任用户的输入。对用户的输入进行校验,可以通过正则表达式,或限制长度;对单引号和双”-“进行转换等。

2.永远不要使用动态拼装sql,可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放,加密或者hash掉密码和敏感的信息。

5.应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装

6.sql注入的检测方法一般采取辅助软件或网站平台来检测,软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具。MDCSOFT
SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入,XSS攻击等。

在脚本语言,如Perl和PHP可以对用户输入的数据进行转义从而来防止SQL注入。

PHP的MySQL扩展提供了mysql_real_escape_string()函数来转义特殊的输入字符。

if (get_magic_quotes_gpc()){ $name = stripslashes($name);}$name =mysql_real_escape_string($name);mysql_query("SELECT * FROMusers WHERE name='{$name}'");

Like语句中的注入

like查询时,如用户输入的值有”_”和”%”,则会出现这种情况:用户本只想查询”abcd_”,查询结果中却有”abcd_”、”abcde”、”abcdf”等等;用户要查询”30%”时也会出现问题。

在PHP脚本中我们可以使用addcslashes()函数来处理以上情况,如下实例:

$sub =addcslashes(mysql_real_escape_string(“%something_”), “%_”);

// $sub == %something_

mysql_query(“SELECT * FROMmessages WHERE subject LIKE ‘{$sub}%'”);

addcslashes() 函数在指定的字符前添加反斜杠。

语法格式:

addcslashes(string,characters)

MySQL 导出数据

使用 SELECT …INTO OUTFILE 语句导出数据

将数据表w3cschool_tbl 数据导出到 /tmp/tutorials.txt 文件中:

mysql SELECT * FROMtutorials_tbl

– INTO OUTFILE ‘/tmp/tutorials.txt’;

生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTOOUTFILE '/tmp/result.text'FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY'n'FROM test_table;

SELECT … INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT … INTO
OUTFILE的逆操作,SELECT句法。为了将一个数据库的数据写入一个文件,使用SELECT
… INTO OUTFILE,为了将文件读回数据库,使用LOAD DATA INFILE。

SELECT…INTO OUTFILE
‘file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则SELECT … INTO OUTFILE
不会起任何作用。

在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。这意味着,虽然你就可以读取该文件,但可能无法将其删除

导出表作为原始数据

mysqldump是MySQL用于转存储数据库的实用程序。

将数据表tutorials_tbl 导出到 /tmp 目录中:

$ mysqldump -u root -p–no-create-info

–tab=/tmp W3CSCHOOL w3cschool_tbl

password ******

导出整个数据库的数据,可以使用以下命令:

$ mysqldump -u root -pW3CSCHOOL database_dump.txt

password ******

备份所有数据库,可以使用以下命令:

$ mysqldump -u root -p–all-databases database_dump.txt

password ******

在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令,将数据备份至 dump.txt 文件中:

$ mysqldump -u root -pdatabase_name table_name dump.txt

password *****

将备份的数据库导入到MySQL服务器中,可以使用以下命令,使用以下命令你需要确认数据库已经创建:

$ mysql -u root -pdatabase_name dump.txt password *****

以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:/p

$ mysqldump -u root -pdatabase_name

| mysql -h other-host.com database_name

MySQL 导入数据

从当前目录中读取文件dump.txt ,将该文件中的数据插入到当前数据库的 mytbl
表中。

mysql LOAD DATALOCAL INFILE ‘dump.txt’ INTO TABLE mytbl;

如果用户指定一个FIELDS 子句,它的子句(TERMINATED BY、[OPTIONALLY]
ENCLOSED BY 和 ESCAPED BY)
也是可选的,不过,用户必须至少指定它们中的一个。

mysql LOAD DATALOCAL INFILE ‘dump.txt’ INTO TABLE mytbl

– FIELDS TERMINATED BY ‘:’

– LINES TERMINATED BY ‘rn’;

指定列的顺序。

如,在数据文件中的列顺序是a,b,c,但在插入表的列顺序为b,c,a,则数据导入语法如下:

mysql LOAD DATALOCAL INFILE ‘dump.txt’

– INTO TABLE mytbl (b, c, a);

使用mysqlimport 导入数据

mysqlimport客户端提供了LOADDATA
INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA
INFILE子句。

从文件 dump.txt 中将数据导入到 mytbl数据表中, 可以使用以下命令:

$ mysqlimport -u root-p –local database_name dump.txt

password *****

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下:

$ mysqlimport -u root-p –local –fields-terminated-by=”:”

–lines-terminated-by=”rn” database_name dump.txt

password *****

mysqlimport 语句中使用 –columns 选项来设置列的顺序:

$ mysqlimport -u root-p –local –columns=b,c,a

database_name dump.txt

password *****

发表评论

电子邮件地址不会被公开。 必填项已用*标注