MYSQL常用命令

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

-- 查询数据库
show databases;

-- 使用数据库
use mysql                      --用户保存在mysql数据库中

-- 查询表
show tables;

-- 查询用户
select user from user;

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

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

-- 删除用户
drop user "yu"@"localhost";

-- 登录用户
mysql -u yu -p

-- 创建数据库
create database xxxx;

-- 删除数据库
drop database xxxx;

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

-- 查看表结构
describe student;

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

-- 插入数据
insert into student (id, birthday, name, age, tel) values(1, "1994-5-5", "张三", 20, "12345678900");
insert into student (birthday, name, age) values ("1996-6-6", "李四", 23);
insert into student values
(3, "1997-9-9", "王五", 22, "12222222222"),
(4, "1999-9-9", "aa", 23, "11111111111");

-- 删除表
drop table stu;

-- 删除数据
delete from student where id = 3;

-- 查询表中数据
select * from student;
select name from student where id > 2;

--  ******事务操作********

-- 创建表用于保存账户 
create table my_account(
id int unsigned not null primary key auto_increment,
account varchar(16) not null unique,
name varchar(10) not null) charset utf8;

-- 插入数据
insert into my_account values
(1, "0000000000000000", "张三"),
(2, "1111111111111111", "李四"),
(3, "2222222222222222", "王五");

-- 修改表
alter table my_account add money decimal(20, 2) after name;

-- 更新数据
update  my_account set money = 1000 where id = 1;
update  my_account set money = 2000 where id = 2;
update  my_account set money = 3000 where id = 3;

-- 转账功能
update my_account set money = money - 500 where id = 2;

-- 停电   出问题！！！

-- 事务操作  （1、手动提交  2、自动提交（默认））
-- 开启事务
start transaction;

-- 转账
update my_account set money = money - 500 where id = 2;

-- 查询数据
select * from my_account;    -- 结果是减掉500的数据   （结果是经过事务日志处理的）

--停电  回滚

-- 转账
update my_account set money = money + 500 where id = 1;

-- 手动提交
commit;

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

-- 转账 2、3分别转500给1
update my_account set money = money - 500 where id = 2;

-- 设置回滚点
savepoint sp1;

-- 转账 出错
update my_account set money = money - 600 where id = 3;

-- 回滚
rollback to sp1;

-- 转账 正确
update my_account set money = money - 500 where id = 3;
update my_account set money = money + 1000 where id = 1;

-- 提交
commit;