数据库层级
1. mysql安装
$ sudo apt update
//安装mysql-server
$ sudo apt install mysql-server
//查看mysql版本
$ mysql --version
//查看mysql服务
$ sudo systemctl status mysql
//执行mysql安全安装脚本
$ sudo mysql_secure_installation
//根据系统用户登录mysql plugin类型是auth_socket
$ sudo mysql
# 创建用户
> CREATE USER 'user_name'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
//授予权限
> GRANT ALL PRIVILEGES ON *. * TO 'user_name'@'localhost';
//登录
$ mysql -u username –p
//创建数据库
> CREATE DATABASE database_name;
//查看数据库
> show databases;
2. 数据管理
3. 用户管理
数据层级
1.基本使用 (增删改查)
-
增加(插入)数据
insert into table_name (
field1
,field1
…fieldn
) values (valus1
,valus2
…valusn
); -
删除数据
delete from table_name where …
-
修改数据
update table_name set field=value where …;
-
查找数据
select * from table_name where …;
2.事务
完场一个动作需要执行多个sql操作时,我们期望他们都成功或者都失败。
-
参考文章
ACID原则 https://blog.csdn.net/dengjili/article/details/82468576
3.数据备份还原
1.mysqldump
mysqldump -h -u -p database table >save path;
4.表设计
5. JDBC 测试程序
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//连接数据库
//协议:主机号:端口/数据库名
String url = "jdbc:mysql://localhost:3306/database_name";
String user = "ccc";
String pwd = "123";
Connection connection = DriverManager.getConnection(url, user, pwd);
Statement statement = connection.createStatement();
//拼装sql语句并执行
String sql = "select * from `user`";
ResultSet query = statement.executeQuery(sql);
//取返回值
while (query.next()) {
System.out.println("------------");
System.out.println(
"id:" + query.getObject("id") +
"name:" + query.getObject("name") +
"male:" + query.getObject("male")
);
System.out.println("------------");
}
//释放资源
query.close();
statement.close();
connection.close();
}