mysql数据库学习总结

(说明，1、2、4是基本的，大家参考一下就行了，对于3请大家多多研究一下，提出意见和方法，共同完善此文档，以作为实验室学习的资料)

目录

1、安装软件

2、Mysql基本语句

2.1、数据库语句

2.1.1列出数据库

2.1.2创建数据库

2.1.3删除数据库

2.1.4连接数据库

2.1.5当前选择（连接）的数据库

2.1.6显示数据库包含的表

2.2、表语句

2.2.1建表

2.2.2获取表字段信息

2.2.3获取表所有信息

2.2.4删除表

2.2.5修改表定义

2.2.6数据操作语句

3、存储过程

3.1、存储过程的定义

3.2、存储过程的调用

3.3、删除存储过程

3.4、DECLARE语句

3.5、举例

4、连接和联合

4.1、连接查询

4.1.1内连接

4.1.2外连接

4.1.3交叉连接

4.2、联合查询

1、安装软件

1）安装mysql软件

mysql软件安装好后，会在服务中新增mysql服务

但此时对mysql操作只能在命令行下面进行

点击MySQL Command Line Client进入命令行界面

当然可以配置环境变量，以可以在“开始——运行”中直接键入“mysql–u root –p ”进入命令行界面，连接远程mysql时需要键入“mysql -h主机地址-u 用户名－p用户密码”如：mysql -h110.110.110.110 –uroot -pabcd123

2）安装mysql可视化操作软件MySQL-Front

如果觉得在命令行下面不方便，可以安装MySQL-Front软件，这样就可以通过MySQL-Front来进行可视化操作，其界面如下：

2、Mysql基本语句

在mysql中，每一条完整的语句以分号“;”结束，当语句以分号结束则mysql才会执行些条语句，如：

mysql> use msgfdb;

Database changed

2.1、数据库语句

此部分主要介绍关于mysql中数据库操作相关语句

2.1.1列出数据库

命令：show databases;（注意：最后有个s）

例如执行此语句我机器上的结果为：

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| house_sl |

| house_v2009 |

| msgfdb |

| mysql |

| school |

| temp |

| test |

| zentao |

+--------------------+

9 rows in set (0.00 sec)

2.1.2创建数据库

命令：create database ;

例如：建立一个名为db_name的数据库

mysql> create database db_name;

Query OK, 1 row affected (0.08 sec)

2.1.3删除数据库

命令：drop database <数据库名>

例如：删除名为xhkdb的数据库

mysql> drop database xhkdb;

2.1.4连接数据库

命令：use <数据库名>

例如：如果db_name数据库存在，尝试存取它：

mysql> use db_name;

Database changed

2.1.5当前选择（连接）的数据库

命令：select database();

例如：如果已经连接了数据库db_name，则有：

mysql> select database();

+------------+

| database() |

+------------+

| db_name |

+------------+

1 row in set (0.00 sec)

2.1.6显示数据库包含的表

命令：show tables; （注意：最后有个s，显示当前数据库中表）show tables from （显示指定数据库dbname中表）例如：显示msgfdb数据库中的表

mysql> show tables from msgfdb;

+------------------+

| Tables_in_msgfdb |

+------------------+

| admintable |

| ausertable |

| ghtable |

| rentmanage |

+------------------+

4 rows in set (0.00 sec)

2.2、表语句

2.2.1建表

命令：create table <表名> ( <字段名1><类型1> [,..<字段名n><类型n>]);

mysql> create table MyClass(

>idint(4) not null primary key auto_increment,

>name char(20) not null,

>sexint(4) not null default ''0'',

> degree double(16,2));

2.2.2获取表字段信息

命令：desc表名，或者show columns from 表名

例如：查看当前数据库下MyClass表字段信息

mysql>DESCRIBE MyClass或者describe 数据库名.表名

mysql>descMyClass;

或：mysql> show columns from MyClass;

2.2.3获取表所有信息

命令：show create table <表名>;

例如：查看当前数据库下MyClass表所有信息

mysql> show create tableMyClass

2.2.4删除表

命令：drop table <表名>

例如：删除表名为MyClass的表

mysql> drop table MyClass;

2.2.5修改表定义（数据定义语言有CREATE、DROP、ALTER等语句，此处用alter语句）

1）更改表名

命令：rename table <原表名> to <新表名>;

例如：把表MyClass名字更改为YouClass

mysql> rename table MyClass to YouClass;

或者：alter table <原表名> rename <新表名>;

例如：把表t1重新命名为t2

mysql> ALTER TABLE t1 RENAME t2;

2）在表中增加字段：

命令：alter table <表名> add<字段><类型>[其他];

例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0

mysql> alter table MyClass add passtestint(4) default ''0'';

3）修改列属性

命令：alter table <表名> modify column <列名><修改类型>;

例如：把fieldname列数据类型varchar(4)改为varchar(14)

Mysql>alter table tablename modify column fieldname varchar(14);

4）更改列名

命令：alter table <表名> change <原来列名><新列名> [数据类型];

例如：将msgfdb数据库中的ausertable中的statu列名改为status

Mysql>Alter table msgfdb.ausertablestatu status int(3);

5）给表添加表级约束

命令：alter table <表名> add constraint <约束名><约束>;

例如：添加主键

Mysql>alter table temptable add constraint pk_temptable primary key(name);

6）删除表级约束

删除主键约束：alter table 表名drop primary key;

删除外键约束：alter table 表名drop foreign key 外键（区分大小写）;

另一种格式：alter table 表名drop constraint 约束名; 运行不通7）总体语句如下：

ALTER [IGNORE] TABLE tbl_name

alter_specification [, alter_specification] ...

alter_specification:

ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...)

| ADD INDEX [index_name] [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,...)

[reference_definition]

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_namecolumn_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP INDEX index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| table_options

| partition_options

| ADD PARTITION partition_definition

| DROP PARTITION partition_names

| COALESCE PARTITION number

| REORGANIZE PARTITION partition_names INTO (partition_definitions) | ANALYZE PARTITION partition_names

| CHECK PARTITION partition_names

| OPTIMIZE PARTITION partition_names

| REBUILD PARTITION partition_names

| REPAIR PARTITION partition_names

ALTER TABLE用于更改原有表的结构。例如，您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型。

2.2.6数据操作语句（insert、update、delete）

1）插入数据

命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]

例如，往表MyClass中插入二条记录, 这二条记录表示：编号为1的名

为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99，编号为

3 的名为Wang 的成绩为96.5.

mysql> insert into MyClass values(1,''Tom'',96.45),(2,''Joan'',82.99), (2,''Wang'', 96.59);

2）更新数据

命令：update <表名> set 字段名=新值[,字段名=新值][where_statements]或者update <表名> set 字段名=replace(字段名,''旧内容'',''新内容'') 例如：往文章前面加4个空格：mysql>update article set content=concat('' '',content);

总体语法：

Single-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

Multiple-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references

SET col_name1=expr1 [, col_name2=expr2 ...]

[WHERE where_definition]

其中：UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。UPDATE语句支持以下修饰符：如果您使用LOW_PRIORITY关键词，则UPDATE的执行被延迟了，直到没有其它的客户端从表中读取为止。如果您使用IGNORE关键词，则即使在更新过程中出现错误，更新语句也不会中断。如果出现了重复关键字冲突，则这些行不会被更新。如果列被更新后，新值会导致数据转化错误，则这些行被更新为最接近的合法的值。如果您在一个表达式中通过tbl_name 访问一列，则UPDATE使用列中的当前值。例如，以下语句把年龄列设置为比当前值多一：

mysql> UPDATE persondata SET age=age+1;

3）删除数据

命令：delete from <表名> [where_statements]

总体语法：

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition]

[ORDER BY ...]

[LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

tbl_name[.*] [, tbl_name[.*] ...]

FROM table_references

[WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

FROM tbl_name[.*] [, tbl_name[.*] ...]

USING table_references

[WHERE where_definition]

说明：如果您指定LOW_PRIORITY，则DELETE的执行被延迟，直到没有其它客户端读取本表时再执行。对于MyISAM表，如果您使用QUICK关键词，则在删除过程中，存储引擎不会合并索引端结点，这样可以加快部分种类的删除操作的速度。在删除行的过程中，IGNORE关键词会使MySQL忽略所有的错误。（在分析阶段遇到的错误会以常规方式处理。）由于使用本选项而被忽略的错误会作为警告返回。

3、存储过程

存储过程是将其一功能的sql代码组织起来进行处理的，它位于数据库中，在程序中可以调用它来完成一些操作。当定义了存储过程后，可以看到：

3.1、存储过程的定义

CREATE PROCEDURE <存储过程名>(参数….)

BEGIN

……..

END;

3.2、存储过程的调用

CALL <存储过程名>(参数);

3.3、删除存储过程

DROP PROCEDURE IF EXISTS <存储过程名>;

3.4、DECLARE语句

1）DECLARE语句被用来把不同项目局域到一个子程序：局部变量、条件和处理程序及光标（游标）。SIGNAL和RESIGNAL语句当前还不被支持。

2）DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

3）光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

4）局部变量

DECLARE var_name[,...] type [DEFAULT value]（mysql存储过程中定义局部变量不需要加上@，这一点与sql server不同。）

4.1）SET语句为局部变量赋值：SET var_name = expr [, var_name = expr] ...

4.2）SELECT….INTO语句为局部变量赋值：SELECT col_name[,...] INTO var_name[,...] table_expr

5）条件

DECLARE condition_name CONDITION FOR

condition_valuecondition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中。

6）处理程序

DECLARE handler_type HANDLER FOR condition_value[,...]

sp_statement

handler_type:

CONTINUE

|EXIT

| UNDO

condition_value:

SQLSTATE [VALUE] sqlstate_value

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

| mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。

?SQLWARNING是对所有以01开头的SQLSTATE代码的速记。

?NOT FOUND是对所有以02开头的SQLSTATE代码的速记。

?SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的

SQLSTATE代码的速记。

?除了SQLSTATE值，MySQL错误代码也不被支持。

7）光标（游标）

7.1）声明光标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

SELECT语句不能有INTO子句。

7.2）打开光标

OPEN cursor_name

7.3）光标遍历

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

7.4）关闭光标

CLOSE cursor_name

7.5)光标结束条件的检测。通过处理程序来检测

3.5、举例

//例一：局部变量、光标、输出多条记录

/*删除存储过程*/

DROP PROCEDURE IF EXISTS contractsql;

/*创建存储过程*/

CREATE PROCEDURE contractsql(IN varcontractid VARCHAR(30))

BEGIN

/*局部变量*/

DECLARE COUNTS INT DEFAULT 0;

DECLARE VARRENT DECIMAL(13,2);

/*实交*/

DECLARE VARTOTAL DECIMAL(13,2) DEFAULT 0;

/*应交*/

DECLARE VARPAY DECIMAL(13,2);

DECLARE VARBEGINS DATE;

DECLARE VARENDS DATE;

/*光标（游标定义）*/

DECLARE cur CURSOR FOR SELECT rent,begintime,endtime FROM rentmanage WHERE contractid=varcontractid;

/*处理程序*/

DECLARE CONTINUE HANDLER FOR NOT FOUND SET COUNTS=1;

/*创建临时表，此处暂没有用到，故注销*/

/*CREATE TEMPORARY TABLE IF NOT EXISTS TEMPTB(rent DECIMAL(13,2),total DECIMAL(13,2));*/

OPEN cur;

FETCH cur INTO VARRENT,VARBEGINS,VARENDS;

WHILE NOT COUNTS DO

SET VARTOTAL=VARTOTAL+(DATEDIFF(VARENDS,VARBEGINS)+1)*VARRENT;

FETCH cur INTO VARRENT,VARBEGINS,VARENDS;

END WHILE;

CLOSE cur;

SELECT (DATEDIFF(totime,fromtime)+1)*VARRENT INTO VARPAY FROM gfcontract WHERE contractid=varcontractid;

/*存储过程返回多条记录*/

SELECT VARPAY AS pay,VARTOTAL AS income,VARPAY-VARTOTAL AS owe; END;

/*调用存储过程*/

CALL contractsql('cd003');

//例二：out参数

drop procedure if EXISTS myproce;

create procedure myproce(in score int,out resutvarchar(20))

begin

declare temp int default 0;

set temp=score;

if temp >= 60 then

setresut='pass';

else

setresut='no';

end if;

end;

callmyproce(40,@endss);

select @endss;

//例三：运用PREPARE，执行一条带参SQL语句，concat是系统函数

DROP PROCEDURE IF EXISTS conditionsql;

CREATE PROCEDURE conditionsql(IN whereCondition varchar(500))

BEGIN

DECLARE stmtvarchar(2000);

IF LENGTH(whereCondition)>0 THEN

BEGIN

SET @sqlstr=concat('SELECT DISTINCT ghtable.houno AS houno,ghtable.situate AS situate FROM msgfdb.ghtable LEFT JOIN msgfdb.ausertable ON ghtable.uid=ausertable.uid WHERE

',whereCondition);

END;

ELSE

BEGIN

SET @sqlstr='SELECT DISTINCT ghtable.houno AS houno,ghtable.situate AS situate FROM msgfdb.ghtable LEFT JOIN msgfdb.ausertable ON ghtable.uid=ausertable.uid';

END;

END IF;

PREPARE stmt FROM @sqlstr;

EXECUTE stmt;

END;

CALL conditionsql("");

4、连接和联合

两者最大的不同可以通过集合的思想来进行区别，连接是集合中元素的改变，而集合不变，而联合是集合元素不变，而集合改变。

4.1、连接查询

通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。

在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。

连接可以在SELECT 语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。

SQL-92标准所定义的FROM子句的连接语法格式为：

FROM join_tablejoin_typejoin_table

[ON (join_condition)]

其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。

join_type指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三

种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

交叉连接(CROSS JOIN)没有WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

连接操作中的ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info

FROM pub_info AS p1 INNER JOIN pub_info AS p2

ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

4.1.1内连接

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：

1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，

其查询结果中列出被连接表中的所有列，包括其中的重复列（重复

列而不是重复数据）。

2、不等连接：在连接条件使用除等于运算符以外的其它比较运算符比

较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，

但它使用选择列表指出查询结果集合中所包括的列，并删除连接表

中的重复列（加上各自的表名做为域限定）。

例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社：

SELECT *

FROM authors AS a INNER JOIN publishers AS p

ON a.city=p.city

又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)：

SELECT a.*,p.pub_id,p.pub_name,p.country

FROM authors AS a INNER JOIN publishers AS p

ON a.city=p.city

4.1.2外连接

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结

果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

如下面使用左外连接将论坛内容和作者信息连接起来：

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b

ON https://www.360docs.net/doc/6216747050.html,ername=https://www.360docs.net/doc/6216747050.html,ername

下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市：

SELECT a.*,b.*

FROM city as a FULL OUTER JOIN user as b

ON https://www.360docs.net/doc/6216747050.html,ername=https://www.360docs.net/doc/6216747050.html,ername

4.1.3交叉连接

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等

于6*8=48行。

SELECT type,pub_name

FROM titles CROSS JOIN publishers

ORDER BY type

4.2、联合查询

UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并（合并并不是连接，它是一种集合操作，将两个集合合并成一个集合）成一个结果集合显示，即执行联合查询。UNION的语法格式为：

select_statement

UNION [ALL] selectstatement

[UNION [ALL] selectstatement][…n]

其中selectstatement为待联合的SELECT查询语句。

ALL选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中的重复行将只保留一行。

联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。

在使用UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。

在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。例如：

查询1 UNION (查询2 UNION 查询3)