mysql入门之1小时学会MySQL基础
mysql入门
mysql (关系型数据库管理系统)
mysql是一个关系型数据库管理系统,由瑞典mysql ab 公司开发,目前属于 oracle 旗下产品。mysql 是最流行的关系型数据库管理系统之一,在 web 应用方面,mysql是最好的 rdbms (relational database management system,关系数据库管理系统) 应用软件。
mysql是一种关系数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
mysql所使用的 sql 语言是用于访问数据库的最常用标准化语言。mysql 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 mysql 作为网站数据库。
由于其社区版的性能卓越,搭配 php 和 apache 可组成良好的开发环境。
1. 数据库(database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的api用于创建,访问,管理,搜索和复制所保存的数据。
2. 使用关系型数据库管理系统(rdbms)来存储和管理的大数据量。关系型数据库是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
3. rdbms特点:
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
4. rdbms 术语
冗余:存储两倍数据,冗余可以使系统速度更快。
主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性
5. mysql是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
mysql管理
6. 启动及关闭 mysql服务器:
(1) 检查mysql服务器是否启动:
ps -ef | grepmysqld
(2) 启动mysql服务器:
root@host# cd/usr/bin
./safe_mysqld&
(3) 关闭目前运行的 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 changed mysql> insert into user (host, user, password, select_priv, insert_priv,update_priv) //设置权限y values ('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 -p
enter 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 w3cschool
enter 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_name
from 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 successfully\n";
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 字段值:
mysql>update 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_name
where column_nameoperator value
group by column_name;
with rollup 可以实现在分组统计数据基础上再进行相同的统计(sum,avg,count…)。
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值与任何其它值的比较(即使是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个条件(acid): 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):这条语句创建索引的值必须是唯一的(除了null外,null可能会出现多次)。
alter table tbl_name add indexindex_name (column_list):添加普通索引,索引值可出现多次。
alter table tbl_name add fulltextindex_name (column_list):该语句指定了索引为 fulltext ,用于全文索引。
使用 alter 命令添加和删除主键
主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空(not null)。实例如下:
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_name\g
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服务器信息: 包含了数据库服务器的当前状态,版本号等。
(1)使用do( ) 执行 $query
my $count = $dbh->do($query);
(2)使用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 affected\n");
数据库和数据表列表
perl 实例
# 获取当前数据库中所有可用的表。
my @tables =$dbh->tables ( ); foreach $table (@tables){ print "table name $table\n"; }
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 count>1;
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 '\r\n';
指定列的顺序。
如,在数据文件中的列顺序是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="\r\n" database_name dump.txt
password *****
mysqlimport 语句中使用 --columns 选项来设置列的顺序:
$ mysqlimport -u root-p --local --columns=b,c,a \
database_name dump.txt
password *****