-- 创建用户
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;