Mysql常见命令集合

 

 

-- 创建用户

create user "jsetc"@"localhost" identified by "jsetc";

 

-- 查询数据库

show databases;

 

-- 使用数据库

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

 

-- 查询表

show tables;

 

-- 查询用户

select user from user;

 

-- 修改密码

set password for "jsetc"@"localhost" = PASSWORD("123456");

 

-- 授权操作

grant insert, create, update on *.* to "jsetc"@"localhost";

 

-- 删除用户

drop user "jsetc"@"localhost";

 

-- 登录用户

mysql -u jsetc -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;