1小时学会MySQL基础
1小时学会mysql基础。
mysql入门
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 '连接成功
';
$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']}
".
"title:{$row['w3cschool_title']}
".
"author:{$row['w3cschool_author']}
".
"submission date : {$row['submission_date']}
".
"--------------------------------
";
}
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选项 决定什么时候吧事务保存到日志里。