-- mysql系统体系结构： -------------- |SQL Layer| -------------- | | | ----------------------------- |storage engine layer | ----------------------------- 逻辑模块组成 总的来说，mysql可以看成是两层架构，第一层我们通常叫做sql layer,在mysql数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断,query解析，执行计划优化，query cache的处理等；第二层为存储引擎层，控制底层的数据存取。 缓存： 客户端浏览器（缓存)－－》正向代理(缓存)－－internet－－网站反向代理(缓存)－－网站(apache有缓存模块，php也有缓存加速器)－－memcache/redis－－数据库（mysql本身也有query cache)－－存储(缓存) --mysql存储引擎简介 1。MyISAM --5.5版本前的默认引擎 5.5版本之前默认的存储引擎，也是目前使用非常广泛的存储引擎之一，是ISAM的升级版本。 用于读多写少的环境 2。innoDB存储引擎简介 --学了oracle后比较好理解 除了MyISAM外，使用最为广泛的存储引擎，由第三方公司开发，和mysql遵守相同的开源许可协议。 支持事务安全 数据多版本读取 锁定机制的改进 实现外键 它有表空间的概念，但与oracle表空间相比又有较大的不同，首先，innodb的表空间分为两种形式，一种是共享表空间，也就是所有数据都被存放在同一个表空间（一个或多个数据文件）中，通过innodb_data_file_path来指定，增加数据文件须要停机重启。另外一种是独享表空间，也就是每个表的数据和索引都存放在一个单独的.ibd文件中。 3。NDB cluster存储引擎简介 主要用于mysql cluster分布式集群环境。简单地说，mysql cluster就是在无共享存储设备的情况下实现的一种内存数据库cluster环境，主要是通过NDB cluster (简称NDB）存储引擎来实现的。 ------ 其他存储引擎介绍 4.merge 可以简单地理解其功能就是结构相同的MyISAM表通过一些特殊的包装对外提供一个单一的访问入口，从而减小应用复杂度的目的。 要创建merge表，不仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致 merge表本身并不存储数据，仅仅是多个基表提供一个统一的存储入口。所以在创建merge表的时候，mysql只会生成两个较小的文件，一个是.frm的结构定义文件，还有一个.MRG文件，用于存放参与MERGE表的名称（包括所属数据库schema) 5,Memory存储引擎 它是一个将数据存储在内存中的存储引擎。 memory存储引擎不会将任何数据存放到磁盘上，在磁盘上仅仅存放了一个表结构相关信息的.frm文件。所以一旦mysql崩溃或主机崩溃之后,memory的表就只剩下一个结构了。 它实现的是页级锁定XAMP 此引擎所有数据都存放在内存中，那么它消耗的内存量可想而知。 6.BDB存储引擎 BDB存储引擎全称为berkeleyDB存储引擎，和innoDB一样，也是第三方的，同样支持事务安全。 在锁定机制方面，和memory存储引擎一样，实现页级锁定。 7。federated存储引擎 它所实现的功能和oracle的DBLINK基本相似，主要是用来提供对远程mysql服务器上面的数据的访问接口，如果我们使用源码编译来安装mysql,那么必須手工指定启用federated存储引擎，因为mysql默认是不启用该存储引擎的。 8。archive存储引擎 主要用于通过较小的存储空间来存放过期的很少访问的历史数据。archive表不支持索引，包含一个.frm的结构定义文件，一个.ARZ的数据压缩文件，还有一个.ARM的元数据 信息文件。由于其所存放的数据的特殊性，archive表不支持删除，修尽管操作，仅支持插入和查询操作。 锁定机制为行级锁定。 9。blackhole存储引擎 就是一个‘黑洞’，就像/dev/null设备一样，不管写入任何信息，都是有去无回。 比如数据迁移过程，数据要经过一个中转的mysql服务器做一些相关的转换操作，然后再通过复制移植到新的服务器上面。如果中转服务器没有足够的空间，可以用blackhole引擎，它不记录任何数据（这样就不会占用空间了），但是会在binary log中记录下所有的query，把这些记录复制应用到新的服务器上。 10。CSV存储引擎 操作的就是一个标准的CSV文件，它不支持索引，主要用途就是有时候可能要通过数据库导出一份报表文件，而CSV文件是很多软件都支持的一种标准格式，所以可以先在数据库中建立一张CVS表，然后将生成的报表信息插入该表，就能得到一份CSV报表文件了 --查询数据库里所有的表及其对应的engine mysql> select table_name,engine from information_schema.tables; 验证memory存储引擎的功能 mysql> create database aaa; --或者create schema aaa; mysql> use aaa; mysql> create table aaa (id int) engine=memory; --如果不指定engine=memory则使用默认引擎 mysql> show create table aaa; --查看创建这个表的语句 mysql> insert into aaa values(1); mysql> insert into aaa values(2); mysql> select * from aaa; +------+ | id | +------+ | 1 | | 2 | +------+ mysql> create table bbb (id int); --使用默认引擎 mysql> show create table bbb; --查看创建这个表的语句 mysql> insert into bbb values(1); mysql> insert into bbb values(2); mysql> select * from bbb; +------+ | id | +------+ | 1 | | 2 | +------+ # /usr/local/mysql/bin/mysqladmin shutdown -p # /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf & # /usr/local/mysql/bin/mysql -p mysql> select * from aaa.aaa; --发现没有数据了 mysql> select * from aaa.bbb; --发现数据还在 存储引擎相关的操作： mysql> show engines; 里面的default表示默认引擎 YES表示启用的引擎 no表示禁止的引擎 --如果把disable改为yes，把yes改为disable 如：把ndbcluster改为yes，innodb改为disable，并把ndbcluster改为默认引擎 vim /usr/local/mysql/etc/my.cnf --修改配置文件在mysqld参数组下加上下面两句 federated --启用federated skip-blackhole --禁用blackhole default-storage-engine=myisam --默认引擎改为myisam /usr/local/mysql/bin/mysqladmin shutdown /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf & --重启验证 除了create表时可以指定引擎，还可以对已经create的表修改引擎 alter table aaa engine=innodb; --把aaa表改成innodb的存储引擎 ======================================================================== mysql物理文件组成 ( 日志文件，数据文件，配置文件，pid文件,socket文件等) ------------------------------------ 日志文件 1。错误日志 error log 记录了运行过程中所有较为严重的警告和错误信息，以及mysql server每次启动和关闭的详细信息。 在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出（stderr),如果要开启系统记录错误日志的功能，须要在启动时开启--log-error选项 error log默认存放在数据目录下，以hostname.err命名。但是可以使用命令:--log-error[=file_name]修改其存放目录和文件名 mysql> show variables like 'log_error'; --在数据里这样查看你的错误日志 +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | log_error | /mysqldata56/mysql56-err.log | +---------------+------------------------------+ 2。查询日志：query log 查询日志记录mysql中所有的query，可通过 general_log general_log_file=/mysqldata56/mysql56-query.log 来打开此日志。 由于记录了所有的SQL操作，包括所有的select,体积比较大，开启后对性能也有较大的影响，所以请大家慎用此功能。一般只在跟踪一些特殊的query性能问题时才会短暂打开此功能。 默认的查询日志文件名为hostname.log 3。二进制日志： binary log& binary log index(类似oracle里的redo日志) 在通过"--log-bin[=file_name]"打开记录的功能之后，mysql会将所有修改数据库数据的query以二进制形式记录到日志文件中，还包括每一条query所执行的时间，所消耗的资源，以及相关的事务信息，所以binlog是事务安全的 打开方法在配置文件里加上类似下面一句，重启mysql服务 log-bin=mysql56-bin 查看方法：不能用cat，要使用mysqlbinlog命令来看 # /usr/local/mysql/bin/mysqlbinlog /mysqldata56/mysql56-bin.000001 --下面这一段是记录了创建aaa库，并创建aaa表，并插入1-5五条数据的所有二进制日志记录 # at 120 #151121 11:46:28 server id 1 end_log_pos 211 CRC32 0x24e6fee0 Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077588/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1073741824/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create database aaa /*!*/; # at 211 #151121 11:46:53 server id 1 end_log_pos 308 CRC32 0xc199bede Query thread_id=1 exec_time=0error_code=0 use `aaa`/*!*/; SET TIMESTAMP=1448077613/*!*/; create table aaa (id int) /*!*/; # at 308 #151121 11:47:17 server id 1 end_log_pos 385 CRC32 0xa3734a06 Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077637/*!*/; BEGIN /*!*/; # at 385 #151121 11:47:17 server id 1 end_log_pos 482 CRC32 0xbf44b57b Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077637/*!*/; insert into aaa values(1) /*!*/; # at 482 #151121 11:47:17 server id 1 end_log_pos 513 CRC32 0x0e036c2b Xid = 10 COMMIT/*!*/; # at 513 #151121 11:47:18 server id 1 end_log_pos 590 CRC32 0x9793af21 Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077638/*!*/; BEGIN /*!*/; # at 590 #151121 11:47:18 server id 1 end_log_pos 687 CRC32 0x539775cd Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077638/*!*/; insert into aaa values(2) /*!*/; # at 687 #151121 11:47:18 server id 1 end_log_pos 718 CRC32 0xe563ed93 Xid = 11 COMMIT/*!*/; # at 718 #151121 11:47:19 server id 1 end_log_pos 795 CRC32 0x29c0de38 Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077639/*!*/; BEGIN /*!*/; # at 795 #151121 11:47:19 server id 1 end_log_pos 892 CRC32 0xc1bd2226 Query thread_id=1 exec_time=0error_code=0 SET TIMESTAMP=1448077639/*!*/; insert into aaa values(3) /*!*/; # at 892 #151121 11:47:19 server id 1 end_log_pos 923 CRC32 0xff1483be Xid = 12 COMMIT/*!*/; # at 923 #151121 11:47:20 server id 1 end_log_pos 1000 CRC32 0x4c609d0c Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1448077640/*!*/; BEGIN /*!*/; # at 1000 #151121 11:47:20 server id 1 end_log_pos 1097 CRC32 0x6c3b1d60 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1448077640/*!*/; insert into aaa values(4) /*!*/; # at 1097 #151121 11:47:20 server id 1 end_log_pos 1128 CRC32 0x4be76c08 Xid = 13 COMMIT/*!*/; # at 1128 #151121 11:47:25 server id 1 end_log_pos 1205 CRC32 0xd710e1f5 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1448077645/*!*/; BEGIN /*!*/; # at 1205 #151121 11:47:25 server id 1 end_log_pos 1302 CRC32 0x3bbebcda Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1448077645/*!*/; insert into aaa values(5) /*!*/; # at 1302 #151121 11:47:25 server id 1 end_log_pos 1333 CRC32 0x87c279ac Xid = 14 COMMIT/*!*/; # at 1333 假设我现在误删除了这张表 mysql> drop table aaa; 恢复方法一: # /usr/local/mysql/bin/mysqlbinlog /mysqldata56/mysql56-bin.000001 --start-position=211 --stop-position=1333 | /usr/local/mysql/bin/mysql --此方法需要在连续时间内没有其它表操作的影响才可以使用（但是在实际情况一般不可能）。所以它是用于恢复备份后的一个连续时间段的操作。 恢复方法二: # /usr/local/mysql/bin/mysqlbinlog /mysqldata56/mysql56-bin.000001 |grep aaa |tac |sed 1d |tac |sed '1iuse aaa'|awk '{print $0";"}' |/usr/local/mysql/bin/mysql --这里我只是随便写个例子，用grep,awk,sed等命令把相关表的所有操作都找出来，再导入到数据库里（但实际应用会比较复杂，日志要合并，并且表名要标识性较强） 4。慢查询日志：slow query log 此日志中记录的是执行时间较长的query,也就是常说的slow query,通过设--log-slow-queries[=file_name]来打开此功能并设置记录位置和文件名，默认文件名为hostname-slow.log,默认目录也是数据目录。 慢查询日志采用的是简单的文本格式，可以通过各种文件编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。 mysql还提供了专门用来分析慢查询日志的工具程序mysqlslowdump,以帮助数据库管理人员解决可能存在的性能问题。 5。innoDB引擎的在线redo日志：innoDB redo log innoDB是一个事务安全的存储引擎，其事务安全性主要是通过在线redo日志和记录在表空间的undo信息来保证的。redo日志中记录了innoDB所做的所有物理变更和事务信息，通过redo日志和undo信息，innoDB保证了在任何情况下的事务安全性。innoDB的redo日志同样默认存放在数据目录下，可以通过innodb_log_group_home_dir来更改设置日志的存放位置，通过innodb_log_files_in_group设置日志的数量。 －－－－－－－－－－－－－－－－－－－－－－－－ 数据文件 不同的存储引擎有各自不同的数据文件，存放位置也有区别。多数存储引擎的数据文件都存放在和myisam数据文件位置相同的目录下，但是每个数据文件的扩展名却各不一样。如myisam用“.MYD"为扩展名，innoDB用".ibd",archive用".arc",CSV用'.CSV',等等。 1。.frm 文件 不论是什么存储引擎，每一个表都会有一个以表命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等。 所有的.frm文件都存放在所属数据库的文件夹下面。 2。.MYD文件 myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的文件夾下，和.frm文件在一起 create table a (id int); insert into a values(1); insert into a select * from a; ...... 插入几百W行记录，就会发现.MYD很大了 create table b (id int) engine=innodb; insert into b values (2); insert into b select * from b; ...... 插入几百W行记录后，就会发现/data/ibdata1由10M变得很大了 3。.MYI文件 也是myisam存储引擎专用，存放myisam表的索引相关信息。对于myisam存储引擎来说，可以被缓存(cache)的内容主要就是来源于.MYI文件中。每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样 4。.ibd文件和ibdata文件 存放innoDB的数据文件（包括索引）。innoDB存储引擎有两种表空间方式：独享表空间和共享表空间。 独享表空间存储方式使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和myisam数据相同的位置。 共享存储表空间使用.ibdata文件，所有表共同使用一个（或多个，自行配置）ibdata文件。 db.opt --此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规则 ------------------------------------------ 复制(replication)相关文件 --讲mysql AB复制集群时再讨论 1。master.info 存在于slave端的数据目录下，里面存放了该slave的master端的相关信息，包括master的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。 2。relay log 和relay log index mysql-relay-bin.xxxxxn文件用于存放slave端的i/O线程从mater端所读取的binary log信息，然后由slave端的sql线程从该relay log中读取并解析相应的日志信息，转化成master所执行的query语句，接着在slave端应用。 mysql-relay-bin.index文件的功能类似于mysql-bin.index,同样是记录日志存放位置的绝对路径，只不过它所记录的不是binary log,而是relay log. 3。relay-log.info文件 类似于master.info,relay-log.info文件存放通过slave的i/O线程写入本地的relay log相关信息，以便slave端的SQL线程及一此管理操作随时能够获取当前复制的相关信息 --------------------------------------------------- 其他文件 1.系统配置文件 my.cnf,Unix/Linux默认存放在/etc目录下，windows环境一般存放在c:/windows目录下 此文件中可以包含多种参数选项组,每一种参数组都通过中括号给了固定的组名，如[mysqld]组中包括了mysqld服务启动时的初始化参数，［client]组中包含着客户端工具程序可以读取的参数，此外还有其他针对各个客户端软件的特定参数组，如mysql程序使用的[mysqld],mysqlchk使用的[mysqlchk]，等 如果读者编写了某个客户端程序，也可以自己设定一个参数组名，将相关参数配置在里面，然后调用mysql客户端API程序中的参数读取API相关参数 2。pid文件 pid文件是mysqld应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，它存放着自己的进程id 3。socket文件 socket文件也是在Unix/Linux环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接使用Unix Socket来连接mysql. ===================================================================== mysql 数据库的帮助 1，官档 2，man 文档 3，命令行下的帮助文件 --oracle命令行没有，需要去查官方文档 mysql > help; mysql > help create table; mysql> ? contents --分了很多类 Account Management Administration Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Language Structure Storage Engines Stored Routines Table Maintenance Transactions Triggers mysql> ? Account Management --一级一级的向内部查 mysql> ? CREATE USER =============================================================== sql 语句 （结构化查询语言,structure query language) ，在不同数据库都是通用的，但会有少许不同 最早是由ANSI定义的标准 sql-86 DQL(data query language) 数据查询语言 select DDL(data define language) 数据定义语言 create drop alter truncate rename DML(data manipulate language) 数据操纵语言 insert update delete DCL(data control language) 数据控制语言 grant revoke 事务控制 commit 提交 rollback 回滚 DDL mysql> create database/schema aaa; --创建一个叫aaa的库，也就是数据目录下创建了一个aaa的目录，owner为mysql.mysql，并在其内有一个db.opt的默认字符集文件 [root@li ~]# ll /mysqldata56/aaa/ -rw-rw---- 1 mysql mysql 59 07-31 15:51 db.opt ---------------------------------------- 手动在操作系统建库的过程 # mkdir /mysqldata56/bbb --直接在datadir里建立一个bbb目录 # ll /mysqldata56 --但是看到权限不一样 total 86060 drwx------ 2 mysql mysql 4096 Jul 11 14:28 aaa drwxr-xr-x 2 root root 4096 Jul 11 14:30 bbb mysql> use bbb; Database changed mysql> create table bbb (id int); --在数据库里操作，是不能建表的 ERROR 1005 (HY000): Can't create table 'bbb' (errno: 13) # /usr/local/mysql/bin/perror 13 --使用这个命令可以查访这个错误代码，告诉我们是权限拒绝错误（这种错误代码还可能会出现在错误日志里，你可以使用这种方法排错） OS error code 13: Permission denied # chown mysql.mysql /mysqldata56/bbb/ # chmod 700 /mysqldata56/bbb/ --改owner,group,和权限 # cp -a /mysqldata56/aaa/db.opt /mysqldata56/bbb/ --再从别的库拷贝db.opt文件，并保留原属性 到此，手动建库完成 -------------------------------------- mysql> create database aaa character set=latin1; --创建库的时候，指定非默认的字符集 mysql> alter database aaa character set=utf8; --修改一个库的字符集 # vim /data/aaa/db.opt --直接vim打开修改字符集，也可以（需要数据库重启) ----------------- mysql> show create database aaa --可以用show查看创建库或者表的相关参数 mysql> create database if not exists aaa --如果不存在则创建，如果存在，则有一个警告 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; --使用此命令可以查看上一句命令的警告，注意只能查看上一句 +-------+------+-------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------+ | Note | 1007 | Can't create database 'aaa'; database exists | mysql> drop database aaa; --删除aaa库 --库改名的话没有相关命令，可以直接在系统上mv来修改 ---------------------------------------------------------------- mysql> create table emp (ename varchar(20),sex char(1),hiredate date,sal decimal(10,2),deptno tinyint(1)); --创建表 mysql> create table emp -> (ename varchar(20), -> sex char(1), -> hiredate date, -> sal decimal(10,2), -> deptno tinyint(1)); --可以分开写，比较容易阅读，但注意这只是一行 mysql> create table dept (deptno tinyint(1),deptname varchar(30),location varchar(50)); --再创建一个部门表，注意deptno这一列和emp表的deptno列是对应的 --表的其它操作 mysql> show create table emp; --查看创建表的参数，引擎和默认的字符集 mysql> alter table emp engine=memory; --修改表的引擎 mysql> alter table emp default charset=utf8; --修改表的字符集 mysql> drop table emp; mysql> create table emp ( ename varchar(20), sex char(1), hiredate date, sal decimal(10,2), deptno tinyint(1)) engine=memory default charset=utf8; --也可以在创建表时直接指定非默认的引擎或字符集 mysql> alter table emp add empno int;--增加一个列，默认增加到最后 mysql> alter table emp add empno int after hiredate; --在hiredate这列后面增加一列，使用after关键字 mysql> alter table emp add empno int first; --把一列加到最前面，使用first关键字 mysql> alter table emp drop empno; --删除一列 mysql> alter table emp modify ename varchar(40); --使用modify修改一列的数据类型 mysql> alter table emp change ename ename varchar(30); --使用change修改一列的数据类型 mysql> alter table emp change ename name varchar(30); --修改列名要使用change去修改 --modify和change的区别，都可以修改数据类型，但change要写原列名;只有change可以修改列名，modify不可以 mysql> truncate table emp; --截断表，清空了表内的所有数据，但是表的结构还在 例：简述 drop,delete,truncate的区别? drop: 属于DDL（在事务控制里，DDL语句不需要commit)，他能删除对象（表，库） truncate:属于DDL 清大量数据速度快，高水位线（high water mark）下降 delete:属于DML（在事务控制里，DDL语句是需要commit，不提交的话可以rollback） 高水位线不下降 练习：通过上面的命令，把emp表多加三列，成为下面的格式 +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | empno | int(11) | YES | | NULL | | | ename | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birthday | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | tinyint(1) | YES | | NULL | | | managerno | int(11) | YES | | NULL | | +-----------+---------------+------+-----+---------+-------+ mysql> alter table emp add empno int first; mysql> alter table emp add birthday date after sex; mysql> alter table emp add managerno int; ============================================================= DML insert delete update mysql> insert into aaa.emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1') --插入数据，注意在mysql里数字可以不用加引号，别的都加引号 mysql> insert into emp values -> (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'), ->(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'); Query OK, 2 rows affected, 0 warning (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 --一次插入多条记录 还可以插入特定的列（非所有列），那么没有插入的就成了空值（空值不是0，它做任何运算结果还是空值) mysql> insert into emp (ename,sex) values ('lisi','m'); 下面针对上面的新表结构，写成一个脚本，直接插入9列数据 --也可以直接写成一行的脚本，使用mysql工具直接导入进去 vim emp.sh #!/bin/bash insert into aaa.emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),(9,'mm','f','1990-06-08','2008-09-13','4000','4','4'); 上面的脚本导入的方法 方法1： # /usr/local/mysql/bin/mysql < emp.sh 方法2： mysql> source /path/emp.sh 方法3： # cat emp.sh | /usr/local/mysql/bin/mysql mysql> select * from aaa.emp; +-------+----------+------+------------+------------+----------+--------+-----------+ | empno | ename | sex | birthday | hiredate | sal | deptno | managerno | +-------+----------+------+------------+------------+----------+--------+-----------+ | 1 | boss | m | 1964-08-08 | 1995-01-01 | 20000.00 | 1 | 1 | | 2 | zhangsan | m | 1967-04-05 | 1995-04-11 | 15000.00 | 2 | 1 | | 3 | lisi | f | 1973-01-28 | 1998-11-21 | 13000.00 | 3 | 1 | | 4 | wangwu | f | 1975-06-03 | 1999-12-12 | 12000.00 | 4 | 1 | | 5 | maliu | m | 1982-08-18 | 2001-07-03 | 8000.00 | 2 | 2 | | 6 | tianqi | f | 1983-02-15 | 2002-11-01 | 7000.00 | 2 | 2 | | 7 | mark | m | 1984-08-12 | 2003-10-02 | 6500.00 | 3 | 3 | | 8 | john | m | 1985-09-14 | 2005-04-03 | 6000.00 | 3 | 3 | | 9 | mm | f | 1990-06-08 | 2008-09-13 | 4000.00 | 4 | 4 | +-------+----------+------+------------+------------+----------+--------+-----------+ 9 rows in set (0.00 sec) 把dept表也插入记录，方便下面的实验 mysql> insert into dept values -> (1,'manager','beijing'), -> (2,'it','shenzhen'), -> (3,'sale','shanghai'), -> (4,'services','guangzhou'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept; +--------+----------+-----------+ | deptno | deptname | location | +--------+----------+-----------+ | 1 | manager | beijing | | 2 | it | shenzhen | | 3 | sale | shanghai | | 4 | services | guangzhou | +--------+----------+-----------+ 4 rows in set (0.00 sec) =============================================================== update 把wangwu性别改成m mysql> update emp set sex='m' where ename='wangwu'; wangwu的工资加500 mysql> update emp set sal=sal+500 where ename='wangwu'; mark由3号部门换成2号部门，同时工资加1000,上级管理者也改为2号部门的头 mysql> update emp set deptno='2' and sal=sal+1000 and managerno=2 where ename='mark'; --错误写法 mysql> update emp set deptno='2',sal=sal+1000,managerno=2 where ename='mark'; --正确写法 2号部门的工资涨10% mysql> update emp set sal=sal*1.1 where deptno=2; 工资就john和mark涨10%,其它人不涨 mysql> update emp set sal=sal*1.1 where ename='john' or ename='mark'; mysql> update emp set sal=sal*1.1 where ename in ('john','mark'); 工资都涨10%，john和mark犯错误，就他们不涨 mysql> update emp set sal=sal*1.1 where ename!='john' and ename<>'mark'; mysql> update emp set sal=sal*1.1 where ename not in ('john','mark'); delete delete from emp where xxxxx; -------------------------------------------------------------- DQL select mysql> select * from emp; --* 代表查看所有列 mysql> select ename,sal from emp; -- 只查看其中几列 mysql> select ename,sal*2 as "double sal" from emp; --把工资两倍做一个别名 mysql> select distinct deptno from emp; --distinct 去除重复行 查询所有男员工的姓名和工资 mysql> select ename,sal from emp where sex='m'; 查询工资大于8000的员工的所有信息 mysql> select * from emp where sal>8000; 查询工资在4000到8000之间的员工的所有信息（包含4000和8000的) mysql> select * from emp where sal>=4000 and sal<=8000; mysql> select * from emp where sal between 4000 and 8000; 查询入职时间在2001那年的员工的姓名和工资 mysql> select ename,sal from emp where year(hiredate)=2001; mysql> select ename,sal from emp where substr(hiredate,1,4)=2001; mysql> select ename,sal from emp where hiredate>'2000-12-31' and hiredate<'2002-01-01'; mysql> select ename,sal from emp where hiredate>='2001-01-01' and hiredate <= '2001-12-31'; mysql> select ename,sal from emp where hiredate like '2001%'; 查询2002年之后(包括2002年)入职的，并且工资大于8000的员工姓名 mysql> select ename from emp where year(hiredate)>=2002 and sal>8000; －－－－－－－－－－－－－－－－－－－－－－－－－－ 排序操作统计工资总额，最大工资，最小工资，平均工资 mysql> select * from emp order by sal; --以工资排序，默认升序排序 mysql> select * from emp order by sal asc; --加不加asc都是升序 mysql> select * from emp order by sal desc; --desc表示降序排序 mysql> select * from emp order by sex,sal; --先按性别排，再按工资排。结果是女的都在一起，以工资从小到大排。男的都在一起，以工资从小到大排。 mysql> select * from emp order by sex desc,sal desc; TOP－N问题 找出工资最低的三个人的姓名和工资 mysql> select ename,sal from emp order by sal limit 3; 找出工资最高的三个人的姓名和工资 mysql> select ename,sal from emp order by sal desc limit 3; 找出工资最低的女员工的姓名和工资 mysql> select ename,sal from emp where sex='f' order by sal limit 1; 找出工资从高到低第三到第五的人的姓名和工资 mysql> select ename,sal from emp order by sal desc limit 2,3; ------------------------------------------------ 聚合和分组操作： mysql> select count(*) from emp; --统计记录条数 mysql> select count(distinct deptno) from emp; +------------------------+ | count(distinct deptno) | +------------------------+ | 4 | +------------------------+ 1 row in set (0.00 sec) mysql> select count(distinct deptno) deptcount from emp; --别名 +-----------+ | deptcount | +-----------+ | 4 | +-----------+ 统计每个部门的人数 mysql> select deptno,count(*) from emp group by deptno; mysql> select ename,deptno,count(*) from emp group by deptno; --这种在mysql里可以查，但无意义，在oracle里属于错误语法。因为前面select的列名除了count(),max(),min(),avg()，sum()等外，别的列名都必须在group by里 统计男，女员工各有多少人 mysql> select sex,count(*) from emp group by sex; 统计每个部门里男女员工各有多少个 mysql> select deptno,sex,count(*) from emp group by deptno,sex; mysql> select sex,deptno,count(*) from emp group by sex,deptno; 查找部门人数大于2的部门号和人数 mysql> select deptno,count(*) from emp group by deptno having count(*)>2; 求每个部门的工资总额，最大工资，最小工资，平均工资 mysql> select deptno,sum(sal),max(sal),min(sal),avg(sal) from emp group by deptno; ------------------------------------------ 表链接（多表查询) 查出员工姓名和其对应的工资，部门名，部门所在地，并显示 mysql> select ename,sal,deptname,location from emp,dept where emp.deptno=dept.deptno; －－－－－－－－－－－－－－－－－－－－－－－－ 子查询： 查出比wangwu工资高的人的姓名和工资 mysql> select ename,sal from emp where sal>(select sal from emp where ename='wangwu'); ---------------------------------------------------------------- DCL grant 授权 mysql> grant select,insert on aaa.emp to 'aa'@'localhost' identified by '123'; --授权使aa@localhost用户，对aaa库的emp表拥有select和insert权限 mysql> flush privileges; --刷新权限 mysql> revoke select on aaa.emp from 'aa'@'localhost'; --回收aa@localhost用户对aaa库的emp的select权限 mysql> flush privileges; 远程授权 服务端 mysql> grant all on *.* to 'aa'@'%' identified by '123'; --all代表所有权限，*.*代表所有库的所有表 %代表所有IP mysql> flush privileges; 客户端 [root@li ~]# /usr/local/mysql/bin/mysql -u aa -h 10.1.1.35 -p123 -P 3307 -p 参数表示密码，－P参数表示端口 ，如果是使用默认的3306就不用改端口 --------------------------------------------------------- 函数： mysql> select user(); mysql> select database(); mysql> select version(); password() 函数 mysql> show variables like '%password%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | ON | --这里ON了，表示使用的是兼容老密码格式的16位 +---------------+-------+ 字符函数： mysql> select concat(ename,' salary is ', sal) from emp; mysql> select left(ename,2) from emp; --取ename的左两个字符 mysql> select right(ename,2) from emp; --取ename的右两个字符 mysql> select length(ename) from emp; --长度函数 mysql> select substr(ename,2,2) from emp; --从第二个字符开始截取，截取两个字符 mysql> select upper(ename) from emp; --大写函数 mysql> select lower(ename) from emp; --小写函数 mysql> select upper(lower(ename)) from emp; --函数的嵌套 数学函数 mysql> select mod(10,3); --取余 mysql> select round(4.444444); --四舍 mysql> select round(4.544444); --五入 日期函数 mysql> select current_time(); mysql> select current_date(); mysql> select current_timestamp(); mysql> select now(); mysql> select year(now()); mysql> select month(now()); mysql> select day(now()); mysql> select hour(now()); mysql> select minute(now()); mysql> select second(now());