MYSQL之基础操作

-- 创建用户
create user "jestc"@"localhost" identified by "123456";

-- 修改密码
set password for "jestc"@"localhost" = PASSWORD("jestc");

-- 授权操作
grant update, insert, select, create on *.* to "jestc"@"localhost";

-- 登录数据库
mysql -u jestc -p

-- 查看数据库
show databases;

-- 创建数据库
create database xxxxxx;

-- 删除数据库
drop database xxxxxx;

-- 使用数据库
use mysql;

-- 查看表
show tables;

-- 创建一张表
create table student(
id int unsigned not null primary key auto_increment,
name varchar(10) not null,
age tinyint not null
)charset utf8;

-- 查看表结构
describe student;

-- 修改表结构
alter table student add birthday date;
alter table student add tel char(11) default "-";
alter table drop id;
alter table student add id int unsigned not null auto_increment primary key first;
alter table student rename stu;

-- 插入数据
insert into student (id, name, age, birthday, tel) values (1, "张三", 20, "1997-1-1", "12345678900");
insert into student (name, age, birthday) values ("李四", 21, "1997-1-2");

insert into student values
(3, "王五", 22, "1997-1-3", "12345678902"),
(4, "aa", 23, "1997-1-4", "12345678903");

-- 删除表
drop table stu;

-- 查询数据
select * from student;

select name from student where age > 21;

-- 修改数据

update stu set tel = "11111111111" where id = 2;

-- 删除数据
delete from stu where id = 4;
delete from stu where age = 20;

针对表中有关联的修改遇到意外情况无法恢复的问题，引进事务这一概念。

-- 新建一张表
create table my_account(
num char(16) not null unique,
name varchar(8) not null,
money  decimal(10,2) default 0.00
)charset utf8;

-- 插入数据
insert into my_account values
("0000000000000001","张三",1000.00),
("0000000000000002","李四",2000.00);

-- 修改
alter table my_account add id int unsigned auto_increment primary key first;

-- 操作
update my_account set money = money - 1000 where id = 2;       -- 扣除1000

-- 断电（退出），出现问题！

-- 事务操作 1、自动提交 2、手动提交

-- 开启事务
start transaction;

-- 一系列操作
update my_account set money = money - 1000 where id = 2;    -- 扣除1000
update my_account set money = money + 1000 where id = 1;    -- 增加1000

此时通过打开cmd运行，查看

表中内容无改变

-- 提交结束

commit;

-- 回滚到初始状态
rollback;

-- 回滚点
-- 开启事务
start transaction;

-- 一系列操作
update my_account set money = money - 500 where id = 1;

-- 设置回滚点
savepoint sp1;   -- 设置回滚点名字

update my_account set money = money + 500 where id = 2;

-- 操作完发现上一条错误，回滚
rollback to sp1;