MySQL 学习笔记
mysql数据库的内部存储结构
数据库操作语句
1.查看所有数据库
1、查询mysql服务软件的所有数据仓库 show databases;
2、查看mysql数据仓库的编码 show create database mysql;
- 1
- 2
- 3
nformation_schema数据库:
其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
performance_schema 数据库：
存储引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。
mysql 数据库:
mysql库是系统库，里面保存有账户信息，权限信息，存储过程，event,时区等信息。
test 数据库:
这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。
2.创建数据库
1、创建一个名称为mydb1的数据库。 create database mydb1;
2、创建一个使用utf8字符集的mydb2数据库。 create database mydb2 character set utf8;
- 1
- 2
- 3
3.删除数据库
需求：删除前面创建的mydb1数据库 drop database mydb1;
- 1
4.修改数据库编码集
需求：修改mydb2字符集为gbk alter database mydb2 character set gbk;
- 1
5.切换数据库和查看正在使用的数据库
需求1：查看正在使用的数据库 select database();
需求2: 切换数据库 use mydb2;
- 1
- 2
- 3
6.数据表结构的sql语句
【示例】
需求：创建一个员工表，员工表有工号、姓名、年龄、性别、生日。
Java 代码编写 :
class Employee {
private int id;
private String name;
private int age;
private char gender;
private Date birthday;
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
MySql 创建数据表 : variable char 长度
create table employee (
id int,
name varchar(30),
age int,
gender varchar(10),
birthday date
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
7.查看表 :
需求1 查看该数据库的所有的表 show tables;
需求2: 查看建表语句以及字符集 show create table employee;
需求3: 查看表的列信息 (查看表结构) desc emloyee; description 描述
- 1
- 2
- 3
- 4
约束 :
意义：保证数据的有效性和完整性。可以确保数据库满足业务规则。
【示例】
需求：创建一个员工表，员工有工号、姓名、年龄、性别、生日和住址。
1、要求工号是主键并且设置主键自增长
2、姓名必须是唯一性的
3、年龄必须是非空的
4、地址必须是唯一的，并且非空。
create table emp (
id int primary key auto_increment,
name varchar(30) unique,
age int not null,
gender varchar(10),
birthday date,
address varchar(30) unique not null
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
数据表结构的修改
语法：alter table 表名 增/删/改 列名 类型(长度) 约束； add/modify/drop/change/rename
需求1：在emp表上增加salary列 alter table emp add salary int;
需求1：修改name列的长度为20 alter table emp modify name varchar(20) unique;
需求2：修改birthday列不能为null alter table emp modify birthday date not null;
需求：修改列名name为username alter table emp change name username varchar(20) unique;
需求：删除age列 alter table emp drop age;
需求：将emp表名修改为emp2表 rename table emp to emp2;
需求7： 将employee的编码修改成utf8 alter table employee character set utf8;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
数据表的删除
需求：删除employee表 drop table employee;
数据记录的增删改
注意：我们需要再创建一张，建表语句如下：
create table user (
id int primary key auto_increment,
name varchar(20) unique,
age int not null,
gender varchar(10),
birthday date,
address varchar(50) unique not null
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查看表中数据的指令 : select * from user;
insert 语句—-数据记录的增加
- insert into user(列1, 列2, 列3, 列4, 列5) values(值1, 值2, 值3, 值4, 值5); 所有列全部定义.
- insert into user(列1, 列2, 列3) values(值1, 值2, 值3); 部分列选择定义
- insert into user values(值1, 值2, 值3, 值4, 值5); 省略列名, 值需要全部提供.
select * from user; 查询表中的所有数据.
insert into user(id,name,age,gender,birthday,address) values(null,张三,18,male,1999-9-9,南京东路100号);
update 语句—-修改表记录
格式 : update 表名 set 列名 = 值 [where 条件];
需求 : 将所有人的年龄修改为20岁。 update user set age = 20;
需求 : 将姓名为张三的人的年龄改为18岁。 update user set age = 18 where name = ‘张三’;
需求 : 将姓名为李四的人的年龄改为30，地址改为航都路18号 update user set age = 30, address = ‘航都路18号’ where name = ‘李四’;
需求 : 将王五的年龄在原基础上增加2岁。 update user set age = age + 2 where name = 王五;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
delete语句—–删除表中数据的语句
格式 : delete from 表名 [where 条件];
需求 : 删除表中名称为’王五’的记录。 delete from user where name = 王五;
需求 : 删除年龄是30岁的员工。 delete from user where age = 30;
需求 : 删除表中所有记录。 delete from user;
- 1
- 2
- 3
- 4
- 5
- 6
Truncate 语句—-删除数据
删除表记录 : truncate table user; user表还在, 删除的是user中的所有数据.
直接将 user 表删除. 不是删除表中的行记录.
然后新建了一张和 user 表一模一样的数据表.
说明1 : delete from 语句删除是逐行依次删除. (一条一条删除的), 主键记录依然存在.
说明2 : truncate table 直接将表删除, 然后创建一张一模一样的数据表. 主键从头开始.
DCL 用户操作 :
创建用户 :
CREATE USER ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’;
-- Jack 用户只能在localhost这个IP登录mysql服务器
CREATE USER 'Jack'@'localhost' IDENTIFIED BY '123';
-- Rose 用户可以在任何电脑上登录mysql服务器
CREATE USER 'Rose'@'%' IDENTIFIED BY '123';
- 1
- 2
- 3
- 4
授权用户
GRANT 权限1, 权限2… ON 数据库名.表名 TO ‘用户名’@’主机名’;
给 Jack 用户分配对test这个数据库操作的权限
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'Jack'@'localhost';
给 Rose 用户分配对所有 数据库操作的权限
CREATE USER 'Rose'@'%' IDENTIFIED BY '123';
GRANT ALL ON *.* TO 'Rose'@'%';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看权限 :
SHOW GRANTS FOR ‘用户名’@’主机名’;
查看 Jack 用户的权限
SHOW GRANTS FOR 'Jack'@'localhost';
- 1
- 2
- 3
- 4
撤销权限 :
REVOKE 权限1, 权限2… ON 数据库.表名 FROM ‘用户名’@’主机名’;
撤销 Jack 用户对test操作的权限
REVOKE ALL ON test.* FROM 'Jack'@'localhost';
- 1
- 2
- 3
删除用户 :
DROP USER ‘用户名’@’主机名’;
删除 Jack
DROP USER 'Jack'@'localhost';
- 1
- 2
- 3
修改用户名密码 :
1.修改管理员密码 :
mysqladmin -u root -p password 新密码 – 新密码不需要加上引号
mysqladmin -u root -p password 123456
输入老密码
- 1
- 2
2.修改普通用户密码 :
set password for ‘用户名’@’主机名’ = password(‘新密码’);
set password for 'Rose'@'%' = password('666666');
- 1
事务 transction
事务：就是将一组sql语句当作一个整体。要成功一起成功，要失败一起失败。
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
money DOUBLE
);
-- 添加数据
INSERT INTO account (name, money) VALUES ('Jack', 1000), ('Rose', 1000);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
假设在数据库中存在以上account表，记录了姓名和金钱，现在如果Jack要借1000元给Rose，应该如何操作？
我们需要进行的操作是把Jack的金额减去100，然后给Rose的金额加上一百：
update account set money = money -1000 where name = 'Jack';
update account set money = money -1000 where name = 'Rose';
- 1
- 2
这个看起来似乎是没有问题的，但是如果两条语句中有一条执行失败了呢？
update account set money = money -1000 where name = 'Jack';
update account set money = money +1000 where nmae = 'Rose';
- 1
- 2
这就尴尬了，钱我给了，你却没收到，怎么办？
这个时候事务的作用就体现出来了，依然是之前的数据：
在Dao窗口执行命令，然后分别在DOS窗口和SQLyog里面查看表格内容
start transaction;
update account set money = money -1000 where name = 'Jack';
update account set money = money -1000 where name = 'Rose';
select * from account;
- 1
- 2
- 3
- 4
- 5
通过SQLyog查询我们发现，表中的数据并没有发生修改，所以我们开启事务之后，进行所有的命令虽然都执行了，但是结果却没有保存到数据库中，此时如果我们发现结果是我们想要的我们可以执行commit命令提交代码；
commit;
- 1
刷新表格：
此时，结果才真正的保存到数据库中。
如果命令执行后的结果不是我们想要的结果，可以执行rollback命令，这样就不会对数据库数据进行更改。
再次将数据恢复到初始状态
执行以下代码
start transaction;
update account set money = money -1000 where name = 'Jack';
update account set money = money -1000 where nmee = 'Rose';
select * from account;
- 1
- 2
- 3
- 4
- 5
第三行语句出错，结果不想保存，怎么办？Rollback
rollback;
- 1
这样，数据库数据就能恢复到开启事务之前的状态。
需要强调的是：一旦执行了commit命令或者rollback命令，事务都会关闭，再次使用需要重新开启。
MySQL 的事务操作 :
1. start transaction; 开始事务.
2. commit 提交事务. (数据库中数据进行了永久性修改) 全部成功.
3. rollback 回滚事务. 失败. (数据回到了开启事务之前)
