MySQL数据库的使用（二）

MySQL连接的使用

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

                        

• 内连接

  <pre class="sql" name="code">select * from emp inner join demp on emp.dempnum = demp.dempnum;

• 左连接

  <pre class="sql" name="code">select * from emp left join demp on emp.dempnum = demp.dempnum;

• 右连接

  select * from emp right join demp on emp.dempnum = demp.dempnum;

MySQL null值的处理

• <pre class="sql" name="code">select * from emp where address is null;

• select * from emp where address is not null;
  

MySQL正则表达式

1. 查找name字段中以'a'为开头的所有数据：

   select * from emp where name regexp '^a';

2. 查找name字段中以'ok'为结尾的所有数据：

   select * from emp where name regexp 'ok$';

3. 查找name字段中包含'xyz'字符串的所有数据：

   select * from emp where name regexp 'xyz';

4. 查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据：

   select * from emp where name regexp '^[aeiou]|ok$';

5. 查找name字段中以'a'为开头且a出现2次的所有数据：

   select * from emp where name regexp '^a{2}';

6. 查找name字段中以'a'为开头且a至少出现一次，最多出现4次的所有数据：

   select * from emp where name regexp '^a{1,4}';

7. 查找name字段中以x或y或z结尾的所有数据：

   select * from emp where name regexp '[x|y|z]$';
   

MySQL事务

       需要安装一个InnoDB引擎：

mysql.ini文件中，mysqlid下配置

[mysqld]
default-storage-engine=INNODB

1. 开始一个事务：

   start transaction; 
   

2. 创建一个事务回滚点：

   savepoint 保存点名称

3. 操作：insert、update、delete......

   insert into emp(id,name) values(10,"倪好");

4. 可以回滚，可以提交，没有问题，就提交，有问题就回滚

   rollback;//回滚
   commit;//执行事务
   

MySQL alter命令

1. 删除、添加或修改表字段：

   <pre class="sql" name="code">alter table emp drop address;
   alter table emp add name varchar;

   alter table emp add name varchar first;

   alter table emp add name varchar after sal;
   

2. 修改字段名称及类型：

   alter table emp modify id int(10);//把字段id的类型从int(20)改为int(10)
   alter table emp change name username varchar//将字段name varchar改为username varchar
   

3. ALTER TABLE 对 Null 值和默认值的影响：

   alter table emp modify sal float not null default 1000;//指定字段 sal 为 NOT NULL 且默认值为1000

4. 修改字段默认值：

   alter table emp alter sal set default 1000;
   show columns from emp;

5. 修改表名：

   alter table user rename to emp;

MySQL索引

        创建一个索引：

	create index empIndex on emp(username(20));

        一般创建一张表时，表中的主键默认就是一个索引

        删除一个索引：

	delete index empIndex on emp;

        显示索引信息：

	show index from emp\G;//\G用来格式化输出信息

MySQL临时表

        创建一个临时表：

create temporary table SalesSummary (
  	product_name varchar(50) not null, 

	total_sales float(12,2) not null default 0.00, 

	avg_unit_price float(7,2) not null default 0.00,

	total_units_sold int unsigned not null default 0
);

注意：当你使用 show tables 命令显示数据表列表时，你将无法看到 SalesSummary表。

如果你退出当前MySQL会话，再使用 select 命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

        删除一个临时表：

	drop table SalesSummary;

MySQL视图      

	create view myemp_view as
	select e.id,e.dempnum,e.name,e.sal,d.name dname,d.address from emp e inner join demp d on e.dempnum = d.dempnum and e.dempnum = 10;

MySQL复制表

	create table emp_bak as select * from emp;

         快速创建表结构：

	create table emp_bak as select * from emp where 1=2;         

MySQL处理重复数据

         过滤重复数据：

	select distinct column1,column2... from emp;

         MySQL数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一）索引来保证数据的唯一性

create table person_tbl(
   	first_name char(20) not null,
   	last_name char(20) not null,
   	sex char(10),
   	primary key (last_name, first_name)
);

create table person_tbl(
   	first_name char(20) not null,
   	last_name char(20) not null,
   	sex char(10),
   	unique(last_name,first_name)
);

MySQL数据的备份和恢复

• 导出数据：

  <pre class="sql" name="code">mysqldump -u root -p test > d:\mysql.sql  
  password ******
  mysqldump -u root -p --all-databases > database_dump.sql//导出所有数据库
  password *****
  

• 导入数据：

  load data local infile 'mysql.sql' into table emp;

  mysqlimport -u root-p--local database_name mysql.sql
  password *****
  

• 备份数据：mysql下有一个文件夹： data,备份一下这个文件夹
• 查看MySQL数据文件存储的位置：

  show global variables like '%datadir%';//查看文件存放的路径