下载MySQL Installer

https://dev.mysql.com/downloads/installer/

配置编码

C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

  1. [mysqld]
  2. character-set-server=utf8

配置好后重启服务
image.png

命令行登录数据库

image.png

查看编码

show variables like '%character%';
image.png

CentOS安装MySQL

下载rpm：wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
安装rpm：yum -y install mysql57-community-release-el7-10.noarch.rpm
安装MySQL：yum -y install mysql-community-server
启动MySQL：systemctl start mysqld.service
查看初始密码：grep "password" /var/log/mysqld.log
登录数据库：mysql -uroot -p
修改密码安全策略为低：set global validate_password_policy=0;
修改root密码：ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
授予root远程管理权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '12345678';
退出：exit

操作数据库基本命令

查看所有数据库：show databases;
创建数据库：create database hello;
使用数据库：use hello;
删除数据库：drop database hello;
修改数据库编码：alter database hello character set utf8;

用户管理

创建用户：create user abc@localhost identified by '123456';
创建用户：create user xyz@'%' identified by '123456';
分配权限：grant all on hello.* to abc@localhost;
分配权限：grant insert,update,select,delete on hello.* to xyz@'%';
查看权限：show grants for abc@localhost;
撤销权限：revoke delete,update on hello.* from xyz@'%';
撤销权限：revoke all on hello.* from xyz@'%';
删除用户：drop user xyz@'%';
删除用户：drop user abc@localhost;

操作表基本命令

查看当前库的所有表：show tables;
创建表：

  1. CREATE TABLE `user` (
  2.   `id` int(11) NOT NULL,
  3.   `name` varchar(4) DEFAULT NULL,
  4.   `age` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查看表字段信息：desc user;
删除表：drop table user;
查看指定表创建语句：show create table user;
修改表名：alter table user rename to user2;
清空表：truncate table user;

操作表列基本命令

增加列：alter table user add (password char(40), address varchar(255));
删除列：alter table user drop address;
修改列：alter table user change password pass char(60);
修改列类型：alter table user modify pass char(32);

操作记录基本命令

插入记录1：INSERT INTOuser(id,name,age) VALUES ('1', '张三', '18');
插入记录2：INSERT INTOuser(id,name,age) VALUES ('2', '李四', '20');
删除记录：DELETE FROMuserWHERE (id='2');
修改记录：UPDATEuserSETage='30' WHERE (id='1');
查询记录：SELECT * FROMuser;

更多查询命令

查询指定列：select name,age from user;
重复的合并为一条记录：select distinct age from user;
列运算：select age+10 from user;
空替换为0参与计算：select IFNULL(age,0)+10 from user;
字符串拼接：select concat(name,age) from user;
别名：select concat(name,age) nameAge from user;
指定范围：select * from user limit 3,5;
指定条件：select * from user where age>18;
指定条件：select * from user where age between 6 and 50;
指定条件：select * from user where name='王丹' and age=7;
指定条件：select * from user where name in ('王丹','李浩');
指定条件：select * from user where name is null;
指定条件：select * from user where name is not null;
模糊查询：select * from user where name like '张_';
模糊查询：select * from user where name like '张%';
升序：select * from user order by age asc;
降序：select * from user order by age desc;
多种排序：select * from user order by age asc, name desc;
聚合函数：select count(*) 计数, sum(age) 总年龄, max(age) 最大年龄, min(age) 最小年龄, avg(age) 平均年龄 from user;
分组查询：select age,count(*) from user group by age;
合并结果集：select * from user1 UNION ALL select * from user2;
内连接方言：select u.name 姓名, f.name 文件名 from user u,file f where u.id=f.id;
内连接标准：select * from user u INNER JOIN file f ON u.id=f.id;
左外连接：select * from user u LEFT OUTER JOIN file f ON u.id=f.id;
右外连接：select * from user u RIGHT OUTER JOIN file f ON u.id=f.id;
子查询：select * from user u where id=(select id from file f where u.id=f.id);
子查询：select * from user u where id in (select id from file f where u.id=f.id);

备份和恢复

  1. 导出hello数据库中所有表：mysqldump -uroot -p hello > hello.sql
  2. 删除hello数据库里面的所有表
  3. 导入所有表到hello数据库中：mysql -uroot -p hello < hello.sql

image.png
另一种导入方式：
image.png

JDBC

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  7. public class Demo {
  8.     public static void main(String[] args) throws Exception {
  9.         Class.forName("com.mysql.jdbc.Driver");
  10.         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello?useSSL=false", "root", "Aomei@1118");
  11.         insert(conn);
  12.         inserts(conn);
  13.         update(conn);
  14.         selects(conn);
  15.         deletes(conn);
  16.         conn.close();
  17.     }
  18.     // 增加1条
  19.     private static void insert(Connection conn) throws SQLException {
  20.         PreparedStatement ps = conn.prepareStatement("INSERT INTO user (id, name, age) VALUES (?,?,?)");
  21.         ps.setInt(1, 1);
  22.         ps.setString(2, "张三");
  23.         ps.setInt(3, 18);
  24.         System.out.println("增加:" + ps.executeUpdate());
  25.         ps.close();
  26.     }
  27.     // 批量增加
  28.     private static void inserts(Connection conn) throws SQLException {
  29.         PreparedStatement ps = conn.prepareStatement("INSERT INTO user (id, name, age) VALUES (?,?,?)");
  30.         for(int i=2; i<=9; i++) {
  31.             ps.setInt(1, i);
  32.             ps.setString(2, "张三"+i);
  33.             ps.setInt(3, 20+i);
  34.             ps.addBatch();
  35.         }
  36.         System.out.println("批量增加:" + ps.executeBatch().length);
  37.         ps.close();
  38.     }
  39.     // 修改1条
  40.     private static void update(Connection conn) throws SQLException {
  41.         PreparedStatement ps = conn.prepareStatement("UPDATE user SET age=? WHERE id=?");
  42.         ps.setInt(1, 30);
  43.         ps.setInt(2, 1);
  44.         System.out.println("修改:" + ps.executeUpdate());
  45.         ps.close();
  46.     }
  47.     // 查询所有
  48.     private static void selects(Connection conn) throws SQLException {
  49.         PreparedStatement ps = conn.prepareStatement("SELECT * FROM user");
  50.         ResultSet rs = ps.executeQuery();
  51.         System.out.println("-------------------------------------");
  52.         while(rs.next()) {
  53.             System.out.println("ID:" + rs.getInt("id") + "\t姓名:" + rs.getString("name") + "\t年龄:" + rs.getInt("age"));
  54.         }
  55.         System.out.println("-------------------------------------");
  56.         rs.close();
  57.         ps.close();
  58.     }
  59.     // 删除所有
  60.     private static void deletes(Connection conn) throws SQLException {
  61.         PreparedStatement ps = conn.prepareStatement("DELETE FROM user");
  62.         System.out.println("删除所有:" + ps.executeUpdate());
  63.         ps.close();
  64.     }
  65. }

事务

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  7. public class Demo {
  8.     public static void main(String[] args) throws Exception {
  9.         Class.forName("com.mysql.jdbc.Driver");
  10.         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hello?useSSL=false", "root", "Aomei@1118");
  11.         try {
  12.             conn.setAutoCommit(false);
  13.             insert(conn);
  14.             insert(conn);
  15.             conn.commit();
  16.         } catch (Exception e) {
  17.             System.err.println(e.getMessage());
  18.             conn.rollback();
  19.         }
  20.         selects(conn);
  21.         conn.close();
  22.     }
  23.     // 增加1条
  24.     private static void insert(Connection conn) throws SQLException {
  25.         PreparedStatement ps = conn.prepareStatement("INSERT INTO user (id, name, age) VALUES (?,?,?)");
  26.         ps.setInt(1, 1);
  27.         ps.setString(2, "张三");
  28.         ps.setInt(3, 18);
  29.         System.out.println("增加:" + ps.executeUpdate());
  30.         ps.close();
  31.     }
  32.     // 查询所有
  33.     private static void selects(Connection conn) throws SQLException {
  34.         PreparedStatement ps = conn.prepareStatement("SELECT * FROM user");
  35.         ResultSet rs = ps.executeQuery();
  36.         System.out.println("-------------------------------------");
  37.         while(rs.next()) {
  38.             System.out.println("ID:" + rs.getInt("id") + "\t姓名:" + rs.getString("name") + "\t年龄:" + rs.getInt("age"));
  39.         }
  40.         System.out.println("-------------------------------------");
  41.         rs.close();
  42.         ps.close();
  43.     }
  44. }