赵工的个人空间

要使用MySQL数据库，必须先创建数据库，然后在数据库中创建相应的数据表。

一、数据库操作：

1.创建数据库：

通常，每一个应用程序需要一个数据库。要创建一个数据库，可使用命令Create DataBase。格式为：
mysql>create database name;
其中，name表示被创建的数据库名，数据库名的长度最大不能超过64，名字中不能含/ .等特殊字符。示例：
mysql>create database studyphp;

2.查看数据库：

成功创建数据库后，可以使用show命令来查看MySQL服务器中的所有数据库信息。语法：
mysql>show databases;

3.选择数据库：

使用use语句选择一个数据库，使其成为当前默认数据库。语法：
mysql>use database name;
4）删除数据库：
如果数据库创建后，发现没有用，可以使用drop命令将其删除：
mysql>drop database name;

4.备份数据库：

在创建完一个数据库和其中的数据表之后，为了数据完全，用户通常会备份一份数据库。一旦数据库被破坏了，就可以通过备份的数据库还原成原来的数据库。在MySQL数据库中，备份数据库的方法有很多。
①复制数据文件时使用LOCK TABLES锁定这些表：
LOCK TABLES TABLE lock_type[, table lock_type…]
其中，table是要锁定的数据库名，可以同时锁定多个表，而lock_type为锁定的类型，包含read和write两个类型。如果用于备份，只需要使用read锁定方式即可。同时在执行备份之前，必须要执行“flush tables”命令，确保对索引所做的任何修改都将写入磁盘。但使用锁定方式来备份并不常用。
②使用mysql_dump命令：
该命令是在操作系统的命令下使用的。mysql_dump命令能够将整个数据库以SQL语句的方式导出到一个.sql文件中，保存在日志文件中的更新将给出上次备份后数据库发生的变化。为了完整地备份数据库，最好备份所有的日志文件。格式为：
mysql_dump [option] database [table]
其中，database为数据库名，table为要备份的数据表名。如果不给出任何表名，整个数据库将被备份。通过执行mysql_dump -help命令，能得到命令的选项表：

如果没有--quick或--opt选项，mysqldump会将整个结果先写到内存中，如果正在备份一个大的数据库，这可能是一个问题。
最常见的mysqldump命令是制作整个数据库的一个备份。例如：
mysqldump --opt database > backup.sql
比如备份bookstore数据库：
mysqldump -uroot -p123456 --opt bookstore > d:\aa.sql
③直接复制数据库文件：
直接将需要备份的数据库文件全部备份即可，只是备份时要关闭MySQL服务器，确保没有用户在操作。

5.还原数据库：

当数据库遭到破坏后，可以通过还原数据库来修复。不同的备份数据库方式采用不同的还原数据库方式。
如果采用了第1种方法来备份数据库，可以直接将数据库文件重新复制到安装MySQL的相同位置。如果采用第2种方法来备份数据库，可以直接运行备份后的SQL文件，使得数据库恢复到备份时的状态；接着还应该将数据库更新到保存在二进制日志文件中的状态，可以使用mysqlbinglog命令。

二、数据表操作：

在对MySQL数据表进行操作前，必须首先使用USE语句选择数据库，才可在指定的数据库中对数据进行操作。

1.创建数据表：

创建数据表可以使用SQL命令CREATE TABLE来完成。格式为：
CREATE [temporary] TABLE [if not exists] tablename
[(create_definition,...)][table_options][select_statement]
参数说明：

列属性create_definition部分，每一列定义的具体格式如下：
col_name type [NOT NULL|NULL][DEFAULT default_value][AUTO_INCREMENT]
        [PRIMARY KEY][reference_definition]

实际应用中使用最基本的格式创建数据表：
create table table_name (列名1 属性, 列名2 属性, ...);
示例：
CREATE TABLE ‘student’ (
‘studentid’  int,
‘stuname’  varchar(50),
‘sex’  varchar(10),
‘addr’  varchar(50),
‘pho’  varchar(11),
PRIMARY KEY (‘studentid’)
 );
在MySQL模式下输入大量的SQL语句不是很方便，这时可以将SQL语句保存成为一个.sql文件，然后通过MySQL调用外部文件来执行这些SQL语句。格式为：
mysql>source SQL文件;
例如：source d:\stu.sql;

2.查看表结构SHOW：

可以使用SHOW方法来查看指定数据表的表结构。
SHOW [FULL] COLUMNS FROM tablename FROM databasename;
SHOW [FULL] COLUMNS FROM databasename.tablename;
通过SHOW命令可以查看数据库中的所有表。示例：
mysql>use studyphp
Database changed
mysql>show tables;
上述代码中，首先使用use语句选择要查询的数据库，然后再通过show语句显示要查询数据库中的数据表。
如果想查看系统中的所有数据库列表，可以使用shou databases命令。还可以使用“show tables from 数据库名称”来检索数据库中的表。下表列出show命令的基本功能：

3.查看表结构DESCRIBE：

DESCRIBE命令用于查看某个特定表的详细设计信息。
DESCRIBE tablename;
DESCRIBE tablename column_name;
其中DESCRIBE可以简写为DESC。例如：
mysql>describe shopping;
mysql>describe shopping item_id;
上述语句分别是查看shopping数据表的结构信息和查看一个列的信息。另外，也可以通过show column命令来查看数据表的列名：
show column from 表名 from 数据库名
show column from 数据库名.表名
示例：
mysql>show columns from shopping from studyphp;

4.查询数据表信息EXPLAIN：

EXPLAIN命令类似于SHOW和DESCRIBE命令，可以查询数据表的信息。示例：
mysql>explain shopping;
EXPLAIN命令还能用于监视查询语句的执行过程。

5.修改表结构：

修改表结构，指增加或者删除字段、修改字段名称或字段类型、设置或者取消主键/外键、设置或者取消索引以及修改表的注释等。语法：
ALTER [IGNORE] TABLE tablename alter_spec[, alter_spec]...
当指定IGNORE时，如果出现重复关键的行，则只执行一行，其他重复的行被删除。
其中，alter_spec子句定义要修改的内容，语法为：
alter_specification:
ADD[COLUMN]create_definition [FIRST|AFTER column_name] //添加新字段
  | ADD INDEX [index_name](index_col_name,...) //添加索引名称
  | ADD PRIMARY KEY (index_col_name,...) //添加主键名称
  | ADD UNIQUE [index_name](index_col_name,...) //添加唯一索引
  | ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT} //修改字段名称
  | CHANGE [COLUMN] old_col_name create_definition //修改字段类型
  | MODIFY [COLUMN] create_definition //修改子句定义字段
  | DROP [COLUMN] col_name //删除字段名称
  | DROP PRIMARY KEY //删除主键名称
  | DROP INDEX index_name //删除索引名称
  | RENAME [AS] new_tbl_name //更改表名
  | table_options
ALTER TABLE语句允许指定多个动作，其动作间使用逗号分隔，每个动作表示对表的一个修改。示例：
alter table tb_admin add email varchar(50) not null, modify user varchar(40);
使用ALTER TABLE语句可能完成的修改如下表：

6.重命名表：

RENAME TABLE tablename1 To tablename2;
该语句可以同时对多个数据表进行命名，多个表之间以逗号分隔。

7.删除表：

DROP TABLE tablename;
删除不存在会产生错误，如果加入IF EXISTS关键字可避免此错误：
DROP TABLE IF EXISTS tablename;
一但删除了数据表，表中的数据将会全部清除，无法恢复，所以要谨慎使用。
其他对数据表的删除操作为：

三、SQL语句操作：

在创建完数据库、数据表后，数据表中的记录全是空的。在数据表中插入、查询、修改和删除记录可以使用SQL语句完成。

1.插入记录：

在MySQL数据库中，使用INSERT命令插入数据。语法：
INSERT INTO tablename (column_name1,column_name2,...) values (value1,value2,...)
在MySQL中，一次可以同时插入多行记录，各行记录的值清单在values关键字后以逗号分隔，而标准的SQL语句一次只能插入一行。
INSERT有几种常见使用方法：
INSERT [INTO] table [(column1, column2, column3…)] VALUES (value1, value2, value3…)
[ON DUPLICATE KEY UPDATE col_name=expression,…]
或者
INSERT [INTO] table [(col_name, …)] SELECT …
或者
INSERT [INTO] table SET col_name=(expression|DEFAULT),…
[ON DUPLICATE KEY UPDATE col_name=expression, …]
INSERT语句是将新行插入到已存在的表中。INSERT…VALUES形式的语句是明确地将值插入记录行；INSERT…SELECT形式的语句是用另一个或多个表中选取数据，并将其值插入。
INSERT INTO student (studentid, stuname, sex, addr) VALUES (‘12’, ‘乖乖’, ‘女’, ‘哈尔滨’)
INSERT INTO tblTemp2 (fidID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID >120

2.修改记录：

UPDATE tablename SET column_name=new_value1,column2=new_value2,...WHERE condition
其中，set子句指出要修改的列和它们给定的值；where子句是可选的，如果给出，将指定记录中哪行应该被更新，否则所有记录行都将被更新。
修改数据表，要应用ALTER TABLE语句。基本格式为：
ALTER TABLE [IGNORE] ALTERATION [, ALTERATION…]
如果指定了IGNORE子句并且尝试的修改可能会产生重复的主键，第一个重复的主键进入修改后的表，而其他重复的主键将被删除。如果没有指定（默认情况），该修改将失败并且被回滚。

3.删除记录：

DELETE FROM tablename WHERE condition
如果没有指定where条件将删除所有记录，指定了where条件将按照指定的条件进行删除。

4.查询数据库记录：

SELECT语句功能强大，方式很复杂。语法为：
SELECT [DISTINCT] [concat(col1,":",col2) as col] selection_list //要查询的内容，选择哪些列
FROM table_list //指定数据表
WHERE primary_constraint //查询时需要满足的条件
GROUP BY grouping_columns //对结果分组
ORDER BY sorting_columns //对结果进行分组
HAVING secondary_constraint //查询时满足的第二条件
LIMIT count //限定输出的查询结果
参数说明：
·selection_list：设置查询内容。如果要查询表中所有列使用*，如果要查询某一列或多列就用列名，以逗号分隔。示例：
select * from tb_mrbook;
select user,pass from tb_mrbook;
·table_list：指定查询数据表。既可以从一个数据表中查询，也可以从多个数据表中查询，多个数据表之间用逗号分隔，并通过where子句使用连接运算来确定表之间的关系。
select tb_mrbook.id,tb_mrbook.bookname,author,price from tb_mrbook,tb_bookinfo
where tb_mrbook.bookname=tb_bookinfo.bookname and tb_bookinfo.bookname="PHP";
上面的SQL语句中，因为两个表都有id字段和bookname字段，需要加上表名前缀。
·WHERE条件子句：指定查询条件。使用时需要使用一些比较运算符，见下表：

·GROUP BY：对结果分组。配合avg()或sum()函数一起使用作用更大。
select bookname,avg(price),type from tb_mrbook group by type;
·DISTINCT：在结果中去除重复行。
select distinct type from tb_mrbook;
·ORDER BY：对结果排序。默认情况下按升序输出结果，需要降序排序使用DESC。
select * from tb_mrbook order by id desc limit 3;
·LIKE：模糊查询。有%和_两种通配符，%可以匹配一个或多个字符，而下划线只匹配一个字符。
select * from tb_mrbook where bookname like('_h%');
·CONCAT：联合多列构成一个总的字符串。
select id,concat(bookname,":",price) as info,type from tb_mrbook;
通过AS关键字给concat函数合并的字段取别名。
·LIMIT：限定结果行数。
select * from tb_mrbook order by price desc limit 3;
使用LIMIT还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取的第一条记录编号，参数2是要查询记录的个数。记录编号从0开始。
select * from tb_mrbook where id limit 1,4;
·使用函数和表达式：计算各列的值作为输出结果，可以使用一些函数。
select sum(price) as total,type from tb_mrbook group by type;
在对MySQL数据库进行操作时，有时需要对数据库中的记录进行统计，如求平均值、最小值、最大值等，这时可以使用MYSQL中的统计函数。

还可以使用算术运算符、字符串运算符，以及逻辑运算符来构成表达式。
select *,(price*0.8) as '80%' from tb_mrbook;