mysql快速上手之入门教程.docx

第一篇：结构化查询语言SQL是高级的非过程化编程语言，它允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解其具体的数据存放方式。而它的界面，能使具有底层结构完全不同的数据库系统和不同数据库之间，使用相同的SQL作为数据的输入与管理。描述说明数据定义语言（DDL）CREATE DATABASETABLE、ALTER DATABASETABLE、DROP TABLEINDEX数据操作语言（DML）SELECT、UPDATE、DELETE、INSERT INTO数据查询语言（DQL）WHERE、ORDER BY、GROUP BY数据控制语言（DCL）GRANT、REVOKE、COMMIT、ROLLBACK一.SELECT查询语句SELECT语句是查询语句，其语法规则如下：SELECT ALL | DISTINCT | DISTINCTROW HIGH_PRIORITY STRAIGHT_JOIN SQL_SMALL_RESULT SQL_BIG_RESULT SQL_BUFFER_RESULT SQL_CACHE | SQL_NO_CACHE SQL_CALC_FOUND_ROWSselect_expr, . FROM table_references WHERE where_condition GROUP BY col_name | expr | position ASC | DESC, . WITH ROLLUP HAVING where_condition ORDER BY col_name | expr | position ASC | DESC, . LIMIT offset, row_count | row_count OFFSET offset1.select语句SELECT column_name,column_name FROM table_name;或者SELECT * FROM table_name;2. SELECT DISTINCT语句在表中,可能会包含重复值,但有时也许希望仅仅列出不同（distinct）的值.关键词DISTINCT用于返回唯一不同的值.SELECT DISTINCT column_name,column_name FROM table_name;3. WHERE ClauseSELECT column_name,column_name FROM table_name WHERE column_name operator value;下面的运算符可以在WHERE从句中出现：运算符描述运算符描述运算符描述=等于=大于等于BETWEEN在某个范围内不等于大于AND且IN规定若干个列可能值1Note:HAVING关键字与WHERE关键字都用来过滤数据的,但HAVING是在数据分组之后进行过滤来选择分组,而WHERE在分组之前用来选择记录.MySQL中可以在GROUP BY字节中使用GROUP_CONCAT()函数,将每个分组中各个字段的值显示出来.(3).在GROUP BY子句中使用WITH ROLLUP使用WITH ROLLUP关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量.例如根据s_id对fruits表中的数据进行分组,并显示记录数量：Select s_id,COUNT(*) AS total from fruits GROUP BY s_id WITH ROLLUP;Note: GROUP BY分组后,在显示结果的最后面新添加了一行,该行total列的值正好是上面所有数值之和。5. ORDER BYORDER BY 语句用于根据指定的列对结果集进行排序。默认按照升序(ASC)对记录进行排序。如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。SELECT Company, OrderNumber FROM Orders ORDER BY Company DESCNote:默认情况下，查询数据按字母升序进行排序（从AZ），使用关键字DESC可以对查询结果进行降序排序（从ZA）6.LIMITSELECT返回所有匹配的行,有可能是表中所有的行,如仅仅需要返回第一行或者前几行,使用LIMIT关键字,基本语法如下：SELECT select_expr, .ORDER BY col_name | expr | position ASC | DESC, .LIMIT offset, row_count | row_count OFFSET offsetNote:offset为LIMIT中可选的位置偏移量参数,指示MySQL从哪一行开始显示,如果不指定位置偏移量,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的偏移量是1依次类推)；第二个参数row_count为行数指示返回的记录条数。带一个参数的LIMIT指定从查询结果的首行开始，唯一的参数表示返回的行数,即LIMIT n与LIMIT 0,n等价.带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。二、INSERTINSERT INTO 语句用于向表格中插入新的行。INSERT INTO table_name VALUES (value1,value2,value3,.);我们也可以指定所要插入数据的列：INSERT INTO table_name (column1,column2,column3,.) VALUES (value1,value2,value3,.);三、UPDATEUpdate 语句用于修改表中的数据。UPDATE table_name SET column1=value1,column2=value2,. WHERE some_column=some_value;Note:当使用UPDATE语句时请小心，如果我们忘记了WHERE从句，结果改变是整个字段的值。UPDATE Customers SET ContactName=Alfred Schmidt, City=Hamburg;四、DELETEDELETE语句用于删除表中的行。DELETE FROM table_name WHERE some_column=some_value;1. 删除某行Fred Wilson 会被删除：DELETE FROM Person WHERE LastName = Wilson2.删除所有行可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：DELETE FROM table_name五、CREATE 1.CREATE DATABASE创建数据库CREATE DATABASE | SCHEMA IF NOT EXISTS db_nameDEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name2.CREATE TABLE创建表CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name(create_definition,.) table_option . partition_options3.CREATE INDEX创建索引CREATE UNIQUE|FULLTEXT|SPATIAL INDEX index_name index_typeON tbl_name (index_col_name,.) index_option .4.CREATE USER创建用户CREATE USER user_specification , user_specification .user_specification:userhost IDENTIFIED BY PASSWORD password | IDENTIFIED WITH auth_plugin AS auth_stringNote:user表示创建的用户的名称；host表示允许登录的用户主机名称；IDENTIFIED BY表示用来设置用户的密码；PASSWORD该参数可选，表示使用哈希值设置密码；password表示用户登录时使用的普通明文密码。IDENTIFIED WITH语句为用户指定一个身份验证插件；auth_plugin是插件的名称，插件的名称可以是一个带有单引号的字符串，或者带引号的字符串；auth_string是可选的字符串参数，该参数将传递给身份验证插件，由该插件解释该参数的意义【IDENTIFIED WITH只能在PHP 5.5.7及以上版本中使用，IDENTIFIED BY和IDENTIFIED WITH是互斥的，所以对于一个账户来说只能使用一个验证方法】。l 使用CREATE USER创建一个用户，用户名是jeffrey，密码是mypass，主机名是localhost，命令如下：CREATE USER jeffreylocalhost IDENTIFIED BY mypass;Note:如果只指定用户名部分jeffrey，主机名部分则默认为%(即对所有的主机开放权限)。通过CREATE USER语句可以在user表中添加一条新的记录，但是CREATE USER语句创建的新用户没有任何权限，还需要使用GRANT语句赋予用户权限。【建议使用GRANT语句创建新用户】六、DROP删除1.DROP DATABASE删除数据库DROP DATABASE IF EXISTS db_name2.DROP TABLE删除数据表DROP TEMPORARY TABLE IF EXISTS tbl_name , tbl_name .Note:使用DROP TABLE命令可以一次删除一个或多个没有被其他表关联的数据表，只需将要删除的表名依次写在后面，想到之间用逗号隔开即可。如果要删除的数据表不存在，则MySQL会提示一条错误信息: ERROR 1051 (42S02): Unknown table tb12;参数IF EXISTS用于在删除前判断删除的表是否存在，加上该参数后再删除表的时，如果表不存在，SQL语句可以顺利执行，但是会发出警告：Query OK, 0 rows affected, 1 warnings (0.00 sec)。u 删除被其他表关联的主表数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。原因是直接删除，将破坏表的参照完整性。如果必须要删除，可以先删除与它关联的子表，再删除父表，只是这样同时删除了两个表中的数据。但有的情况下可能要保留子表，这时如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表。3.DROP INDEX删除索引DROP INDEX index_name ON tbl_name七、ALTER1.ALTER DATABASEALTER DATABASE | SCHEMA db_name DEFAULT CHARACTER SET charset_name | DEFAULT COLLATE collation_name.2.ALTER TABLE修改数据表修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表，常用的修改表的操作有：修改表名、修改字段数据类型/字段名、增加/删除字段、修改字段的排列位置、更改表存储引擎、删除表的外键约束等。1.修改表名ALTER TABLE tbl_name RENAME TO new_tbl_name2.修改字段的数据类型ALTER TABLE tbl_name MODIFY COLUMN column_definition3.修改字段名ALTER TABLE tbl_name CHANGE COLUMN old_col_name column_definition4.添加字段ALTER TABLE tbl_name ADD COLUMN column_definition FIRST | AFTER col_name 5.删除字段ALTER TABLE tbl_name DROP COLUMN col_name6.修改字段的排列位置ALTER TABLE tbl_name MODIFY COLUMN column_definition FIRST | AFTER col_namel column_definition：col_name data_typel FIRST为可选参数，指将column_definition中的col_name字段修改为表的第一个字段。l AFTER col_name，指将column_definition中的col_name字段插入到col_name后面。7.更改表的存储引擎ALTER TABLE tbl_name ENGINE = engine_name8.删除表的外键约束ALTER TABLE tbl_name DROP FOREIGN KEY fk_symboll fk_symbol为外键约束名，指在定义表时CONSTRAINT关键字后面的参数。八、GRANT权限设置GRANT priv_type (column_list) , priv_type (column_list) . ON object_type tbl_name | * | *.* | db_name.* TO userhost IDENTIFIED BY PASSWORD password- priv_type为赋予用户的权限类型,如下:Select_priv|Insert_priv|Update_priv|Delete_priv| Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections- tbl_name | * | *.* | db_name.*表示用户的权限所作用的数据库中的表；IDENTIFIED BY关键字用来设置密；password表示用户密码。l 下面为my_db数据指定用户名为admin，密码为admin888.grant all on my_db.* to adminlocalhost identified by admin888;l 创建一个新的用户myuser，密码为mypwd，并授于用户所有数据表的SELECT和UPDATE权限。mysql grant select,update on *.* to myuserlocalhost identified by mypwd;执行成功后，使用SELECT语句查询用户myuser的权限：mysql select host,user,select_priv,update_priv from mysql.user where user=myuser;+-+-+-+-+| host | user | select_priv | update_priv |+-+-+-+-+| localhost | myuser | Y | Y |+-+-+-+-+Note:此时添加新用户成功，但新添加的用户还无法使用账号密码登录MySQL，需要使用执行FLUSH PRIVILEGES命令，告诉服务器重新加载授权表（重启服务器也会重新加载授权表）。使用GRANTIDENTIFIED BY语句设置密码时，会自动加密密码，此时不需要使用PASSWORD()函数。这点应注意与UPDATE和SET语句修改密码时需要使用PASSWORD()函数加密密码区别开来。帐户管理一、创建普通用户1.使用CREATE USER语句创建新用户CREATE USER user IDENTIFIED BY PASSWORD password, user IDENTIFIED BY PASSWORD password .2.使用GRANT语句创建新用户GRANT priv_type (column_list) , priv_type (column_list) . ON object_type tbl_name | * | *.* | db_name.* TO user IDENTIFIED BY PASSWORD password , user IDENTIFIED BY PASSWORD password .REQUIRE NONE | SSL| X509 CIPHER cipher AND ISSUER issuer ANDSUBJECT subject WITH with_option with_option .3.使用INSERT INTO语句操作mysql.user表二、删除普通用户1.使用DROP USER语句删除用户DROP USER user , user .2.使用DELETE语句删除用户DELETE LOW_PRIORITY QUICK IGNORE FROM tbl_name WHERE where_condition ORDER BY . LIMIT row_count三、root用户修改自己的密码1.修改mysql数据库的user表因为所有帐户信息都保存在user表中,因此可以通过直接修改user表来改变root用户的密码。l 第一步：root用户登录MySQL服务器：C:UsersAdministratormysql -uroot -pEnter password: *Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 5Server version: 5.1.28-rc-community MySQL Community Server (GPL)Type help; or h for help. Type c to clear the buffer.l 第二步：使用update语句修改mysql数据库的user表的password字段，从而修改用户密码：mysql update mysql.user set password=password(pwd) where host= and user=root;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql flush privileges;Query OK, 0 rows affected (0.00 sec)Note:使PASSWORD()函数用来加密用户密码(新密码必须使用PASSWORD()函数加密)；执行完UPADTE语句后，需要执行FLUSH FRIVILEGES语句重新加载用户权限。2.使用SET语句修改root用户的密码SET PASSWORD语句可以用来重新设置其他用户的登录密码或者自己使用的账号的密码,其语法如下：SET PASSWORD FOR userhost = PASSWORD(some password)使用SET语句修改自身密码时可省略FOR从句,语法如下：SET PASSWORD = PASSWORD(admin666);Note:SET语句执行成功后,为了使更改生效,需要重新启动MySQL或者使用FLUSH PRIVILEGES语句刷新权限,重新加载权限表。四、root用户修改普通用户密码l 第一步：使用root用户登录到MySQL服务器C:UsersAdministratormysql -uroot -pl 第二步：执行SET语句，如下：mysql set password for adminlocalhost = password(admin);l 第三步：执行FLUSH PRIVILEGES语句：mysql flush privileges;Query OK, 0 rows affected (0.00 sec)下面是mysql数据库中user表信息：mysql select host,user,password from mysql.user;+-+-+-+| host | user | password |+-+-+-+| % | root | | production | root | | | root | | localhost | admin | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | localhost | myuser | *6F1F0C7FA57187E0D959C29F4BC7F826ED5A9169 |+-+-+-+5 rows in set (0.00 sec)Note:还可以使用UPDATE语句修改普通用户的密码；使用GEANT语句修改普通用户密码。五、普通用户修改密码普通用户登录MySQL服务器后，通过SET语句设置自己的密码。C:UsersAdministratormysql -uadmin -pEnter password:*mysql set password= password(admin888);Query OK, 0 rows affected (0.00 sec)Note:当我们修改好密码后,执行FLUSH命令需要RELOAD权限,该命令执行之后,用户即可登录服务器.出现以下错误提示时,只能重启服务器。mysql flush privileges;ERROR 1227 (42000): Access denied; you need the RELOAD privilege for this operationC:UsersAdministratormysql -uadmin -pEnter password: *ERROR 1045 (28000): Access denied for user adminlocalhost (using password: YES)Note:使用GRANT则不需要使用FLUSH PRIVILEGES。权限管理权限管理主要是对登录到MySQL的用户进行权限验证，所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能是证实连接到一台给定主机的用户并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。账户权限信息被存储在MySQL服务器中的mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中,在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存。数据备份与还原尽管采取了一些管理措施来保证数据库的安全，但是不确定的意外情况总是有可能造成数据的损失,例如意外的停电、管理员不小心的操作失误都可能会造成数据的丢失,保证数据安全的最重要的一个措施是确保对数据进行定期备份.如果数据库中的数据丢失或者出现错误,可以使用备份的数据进行还原,这样就尽可能的降低了意外原因导致的损失.一、数据备份数据备份是数据库管理员非常重要的工作，系统意外崩溃或者硬件的损坏可能导致数据库的丢失，因此MySQL管理员应该定期地备份数据库，使得在意外情况发生时，尽可能减少损失。1.使用mysqldump命令备份mysqldump是MySQL提供的一个非常有用的数据库备份工具。mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。基本语法规则如下：Dumping definition and data mysql database or tableUsage: mysqldump OPTIONS database tablesOR mysqldump OPTIONS -databases OPTIONS DB1 DB2 DB3.OR mysqldump OPTIONS -all-databases OPTIONSa.使用mysqldump备份单个数据库中的所有表mysqldump uroot p booksbd -skip-lock-tables /www/backup/mysql/20131203booksbd.sqlEnter password: *Note:备份booksbd数据库。mysqldump提供许多选项,包括用于调试和压缩的,运行帮助命令mysqldump help,可以获得特定版本的完整选项列表。如果运行mysqldump没有-quick或-opt选项,mysqldump在转储结果前将整个结果集装入内存.如果转储大数据库可能会出现问题.该选项默认启用,但可以用-skip-opt禁用.如果使用最新版本的mysqldump程序备份数据,并用于还原到比较旧版本的MySQL服务器中,则不要使用opt或-e选项。b.使用mysqldump备份数据库中的某个表mysqldump uroot p booksdb books c:backupbooks20131203.sqlEnter password: *Note:备份booksbd数据库中的books表。c.使用mysqldump备份多个数据库(1)使用mysqldump备份多个数据库，需要使用-databases参数，语法格式如下：mysqldump uroot p -databases booksdb test c:booksdb_test_20131203.sqlEnter password: *(2)使用mysqldump备份系统中所有的数据库，需要使用-all-databases参数，语法格式如下：mysqldump uroot p -all-databases c:alldb_20131203.sqlEnter password: *Note:使用-databases参数之后,必须指定至少一个数据库的名称,多个数据库名称之间用空格隔开;使用使用-all-databases参数时,不需要指定数据库名称。2.直接复制整个数据库目录因为MySQL表保存为文件方式,所以可以直接复制MySQL数据库的存储目录及文件进行备份.这是一种简单、快速、有效的备份方式,要想保持备份的一致性,备份前需要对相关表执行LOCK TABLES操作(锁表操作),然后对表执行FLUSH TABLES(刷新表操作).这样当复制数据库目录中的文件时,允许其他客户继续查询表.需要FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘.当然也可以停止MySQL服务再进行备份操作。Note:这种方法虽然简单,但并不是最好的方法,因为这种方法对InnoDB存储引擎的表不适用。3.使用mysqlhotcopy工具快速备份mysqlhotcopy是一个Perl脚本,它在Unix系统中运行.它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库,它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。语法格式如下：mysqlhotcopy u user -p db_name1,db_name2, /path二、数据还原当数据丢失或意外破坏时，可能通过还原已经备份的数据尽量减少数据丢失和破坏造成的损失。1.使用mysql命令还原a.对于已备份的包含CREATE、INSERT语句的文件文件,可以使用mysql命令导入到数据库中,语法如下：mysql u user p dbname mysql -uroot -pEnter password:*mysql use booksdbDatabase changedmysql source d:/mysql/20131203_booksbd.sqlNote:执行source命令前,必须使用use语句选择数据库,否则恢复过程中会出现错误.数据库迁移数据库迁移就是把数据从一个系统移动到另一个系统上,数据迁移有以下原因：需要安装新的数据库服务器、MySQL版本更新、数据库管理系统的变更(如从SQL Server迁移到MySQL)。1.相同版本的MySQL数据库之间的迁移相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动，迁移过程其实就是在源数据库备份和目标数据库还原过程的组合。最常用最有效的方式是使用mysqldump命令导出数据，然后在目标数据库服务器使用mysql命令导入。例如：将主机上的MySQL数据库全部迁移到主机上。在主机上执行的命令如下：mysqldump h uroot ppassword dbname | mysql h www.def.co uroot -ppasswordNote:mysqldump导入的数据直接通过管道符|传给mysql命令导入到主机数据库中，dbname为需要迁移的数据库名称，如果要迁移全部的数据库，可使用参数all-databases。2.不同版本的MySQL数据库之间的迁移因为数据库升级等原因,需要将较旧版本MySQL数据库中的数据迁移到较新版本的数据库中,MySQL服务器升级时需要先停止服务,然后卸载旧版本并安装新版本的MySQL,这种更新方法很简单.如果想保留旧版本中的用户访问控制信息,则需要备份MySQL中的mysql数据库,在新版本MySQL安装完成之后，重新读入mysql备份文件中的信息。Note:旧版本与新版本的MySQL可能使用不同的默认字符集,例如MySQL 4.x中大多使用latin1作为默认字符集,而MySQL 5.x的默认字符集为utf8.如果数据库中有中文数据的,迁移过程中需要对默认字符集进行修改,不然可能无法正常显示结果。3.不同数据库之间的迁移不同类型的数据库之间的迁移,是指把MySQL的数据库转移到其他类型的数据库,例如从MySQL迁移到ORACLE,从ORACLE迁移到MySQL,从MySQL迁移到SQL Server等。Note:迁移之前,需要了解不同数据库的架构,比较它们的差异。SQL 函数SQL拥有很多可用于计数和计算的内建函数。一、内建SQL函数的语法是：SELECT function(列) FROM 表二、函数的类型a. Aggregate(合计函数)Aggregate函数的操作面向一系列的值,并返回一个单一的值。函数描述AVG(column)返回某列的平均值COUNT(column)返回某列的行数（不包括 NULL 值）COUNT(*)返回被选行数MAX(column)返回某列的最高值MIN(column)返回某列的最低值SUM(column)返回某列的总和NOTE：如果在SELECT语句的项目列表中的众多其它表达式中使用SELECT语句,则这个SELECT必须使用GROUP BY语句！1.COUNT()函数COUNT()函数统计数据表中包含的记录行的总数,或者根据查询结果返回列中包含的数据行数,其使用方法有两种：l SQL COUNT(column_name)语法COUNT(column_name)函数返回指定列的值的数目(NULL不计入)：SELECT COUNT(column_name) FROM table_namel SQL COUNT(*)语法COUNT(*)函数返回表中的记录数：SELECT COUNT(*) FROM table_namel SQL COUNT(DISTINCT column_name)语法COUNT(DISTINCT column_name)函数返回指定列的不同值的数目：SELECT COUNT(DISTINCT column_name) FROM table_nameNote:COUNT(DISTINCT)适用于ORACLE和Microsoft SQL Server,但是无法用于Micr