事务
事务:事务是不可分割的逻辑单元,多个操作作为一个事务整体向系统提交,要么都执行,要么都不执行。
事务的特性:
原子性(atomicity)
一致性(consistency)
隔离性(isalation)
持久性(durability)
语法:
#开始事务
begin或start transaction;
#提交事务
commit;
#回滚(撤销)
rollback;
关闭/开启默认提交状态
set autocommit=0|1;
0表示关闭自动提交,1表示开启自动提交
视图
创建视图:
create view `viewname` as select 语句;
示例:
create view `newview` as select * from student;
删除视图:
drop view [if exists] `viewname`;
查看视图:
select 字段名 from `viewname`;
索引
创建索引:
create index `indexName` on `表名`(字段名);
示例:
为学生表的学生名字段创建索引?
create index `newIndex` on `student`(studentName);
删除索引:
drop index `索引名` on `表名`;
导入导出数据库
备份(导出):
mysqldump –u root –p myschool student > 路径
导出为txt文件:
SELECT * FROM `tablename`
[WHERE contion]
INTO OUTFILE 'filename'
示例:
将成绩表中“Logic Java”课程的成绩信息导出到文本文件?
SELECT * FROM `result`
WHERE `subjectNo` =
(SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
INTO OUTFILE 'd:/backup/result_Java.txt';
恢复(导入):
方法一:mysql –u root –p schoolDB < 路径
方法二(source):
CREATE DATABASE myschoolDB2; #创建数据库
USE myschoolDB2; #选择数据库
source d:\ myschool.sql; #恢复数据库
用户的创建、授权、修改
创建普通用户:
create user `用户名`@`主机` [identified by '123456'];
示例:
创建本地用户teacher,密码为123456,本地用户student,无密码?
create user `teacher`@`localhost` identified by '123456';
create user `student`@`localhost`;
查看用户:
use mysql;
select * from user;
创建新用户同时授权(grant)
grant 权限 on 库名.表名 to `用户名`@`主机名` [identified by '密码'];
常用权限:
create、drop
insert/delete/update/select(增/删/改/查)
alter(修改)
all(所有权限)
示例:
创建本地用户wangba,密码123,赋予myschool数据库中student表增加和查询权限?
grant insert,select on myschool.student to `wangba`@`localhost` identified by '123';
修改密码
dos命令修改密码:
mysqladmin –u 用户名 –p password "新密码"
示例:
将root密码修改为123?
mysqladmin -u root -p password "123"
set命令修改用户密码:
set password [for `用户名`@`主机名`] =password("新密码");
示例:
#修改当前用户密码为123 ?
set password = password("123")
#修改其他用户密码为123?
set password for `teacher`@`localhost` =password("123");
删除用户(drop)
drop user `用户名`@`localhost`,`用户名`@`localhost`;
示例:
drop user `user `@`localhost`;