快速了解MySQL基础知识
1. 基本的命令行操作
mysql -uroot -p123456 -- 连接数据库update mysql.user set authentication_string=password('123456') where user='root' and host='localhost'; -- 修改用户密码flush privileges; -- 刷新权限
----------------------------
-- 所有的语句都使用;结尾show databases; -- 查看所有的数据库mysql> use school; -- 切换数据库 use 数据库名
Database changedmysql> show tables; -- 显示数据库中所有表的信息mysql> describe student; -- 显示表的详细信息create database zyy; -- 创建一个数据库（这里过于简洁，后面详细介绍）exit -- 退出连接-- 单行注释
/**
多行注释
*/
2.操作数据库
创建数据库
create database [if not exists] 数据库名; []表示可选的
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
使用数据库
-- 如果你的表名或者字段名是一个特殊字符，就需要带上
USER `数据库名`;
查看数据库
SHOW DATABASES;-- 查看所有的数据库
3. 数据库的字段属性
Unsigned
- 无符号的整数
- 声明了该列不能声明为负数
Zerofill
- 0填充的
- 不足的位数，使用0来填充 int(3) 5 — 005
自增
- 通用理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键，index，必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 null/not null
-
- not null，如果不给他赋值，就会报错
- null，如果不给他赋值，默认就是null
默认
- 设置默认的值
- 如果不赋值，就会存默认值
4. 创建数据库表
CREATE TABLE [IF NOT EXISTS] `表名` (`字段名` 列类型[属性] [索引] [注释],`字段名` 列类型[属性] [索引] [注释],`字段名` 列类型[属性] [索引] [注释],...
)[表类型][字符集设置][注释]-- 目标：创建一个school数据库
-- 创建student学生表，使用sql创建
-- 学号 姓名 性别 出生日期 家庭地址 email-- 注意点：使用英文() 表的名称和字段尽量使用``括起来
-- AUTO_INCREMENT 自增
-- 字符串使用单引号括起来
-- 所有的语句后面加上英文逗号，最后一个不加
-- PRIMARY KEY主键，一个表一般只有一个唯一的主键
-- default 默认显示
-- comment 注释
CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATETIME NOT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
常用命令
SHOW CREATE DATABASE `school` ; -- 查看创建数据库的语句
SHOW CREATE TABLE `student`; -- 查看student数据表的定义语句
DESC `student`; -- 查看表的结构
5. 修改和删除数据表的字段
修改
-- 修改表名 ALTER TABLE `原表名` RENAME AS `新表名`;
ALTER TABLE `teacher` RENAME AS `teacher1`;-- 新增表的字段 ALTER TABLE `表名` ADD 字段名 列属性
ALTER TABLE `teacher1` ADD age INT(3); -- 修改表的字段（重命名，修改约束！）
-- ALTER TABLE `表名` MODIFY 字段名 列属性;
-- ALTER TABLE `表名` CHANGE 原字段名 现字段名 列属性;
ALTER TABLE `teacher1` MODIFY age VARCHAR(3); -- 修改约束 MODIFY修改的意思，只能修改字段类型，不能修改字段名称ALTER TABLE `teacher1` CHANGE age age1 INT(3);-- 字段重命名-- 删除表的字段
ALTER TABLE `teacher1` DROP age1;-- 总结：alter table 表名 rename as/add/modify/change/drop
删除
-- 删除表（如果存在再删除）
DROP TABLE IF EXISTS teacher1;
所有的创建和删除操作尽量加上判断，以免报错~
注意点：
- ``字段，使用这个包裹
- 注释 – /**/
- sql关键字大小写不敏感，建议大写写小写
- 所有的符号全部用英文
6. 数据库级别的外键
创建表的时候，增加约束
-- 年级表
CREATE TABLE `grade` (`id` INT(30) NOT NULL AUTO_INCREMENT COMMENT '年级id',`name` VARCHAR(50) NOT NULL COMMENT '年级名称',PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8-- 先删除之前的学生表
DROP TABLE `student`;-- 学生表 id_grade 字段 需要引用年级表的 id字段
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATETIME NOT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',`id_grade` INT(30) NOT NULL COMMENT '年级id',PRIMARY KEY(`id`), KEY `fk_id_grade`(`id_grade`),CONSTRAINT `fk_id_grade` FOREIGN KEY (`id_grade`) REFERENCES `grade` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8注意点：
删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）
7. insert语句详解
数据库意义：数据存储，数据管理
DML语言：数据库操作语言
insert(增加)
-- 插入语言-- INSERT INTO `表名`(`字段1`,`字段2`,`字段3`...) VALUES('值1','值2','值3'...);
INSERT INTO `grade`(`name`) VALUES('大四');
INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('张三','1995-11-20','1');
-- 一般写插入语句，我们一定要数据和字段一一对应。-- 插入多条
-- INSERT INTO `表名`(`字段1`,`字段2`...) VALUES('值1','值2'...),('值1','值2'...)...;
INSERT INTO `grade`(`name`) VALUES('大一'),('大二');INSERT INTO `student`(`name`,`birthday`,`id_grade`) VALUES('李四','1994-11-20','2'),('王五','1995-11-20','1');注意事项：
- 字段和字段之间使用逗号隔开
- 字段是可以省略，但是后面的值必须要一一对应，不能少
- 可以同时插入多条数据，VALUES后面的值，需要使用逗号分开`VALUES(),()...`
8. update语句详解
update(修改)
-- 修改学员名字
UPDATE `student` SET NAME='zyy' WHERE id='1';
-- 通过多个条件定位数据
UPDATE `student` SET NAME='hehe' WHERE NAME='zyy' AND id_grade='2';
-- 不指定条件的情况，会改动所有的表
UPDATE `student` SET NAME='all';
-- 语法
-- UPDATE 表名 SET 列名=值[,列名=值,列名=值,列名=值...] [WHERE 条件]注意事项:
- 列尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，可以是一个具体的值，也可以是一个变量UPDATE `student` SET birthday=CURRENT_TIME WHERE NAME='hehe' AND id_grade='2';
- 多个设置的属性之间，使用英文逗号隔开
9.delete和truncate
delete(删除)
语法： delete from 表名 [where 条件]
-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id=1;
truncate
作用：完全清空一个数据库表,表的结构和索引约束不会变！
-- 清空表
TRUNCATE `student`;
10. 基本的select语句
select 语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias][left | right | inner join table_name2] -- 联合查询[WHERE ...] -- 指定结果需满足的条件[GROUP BY ...] -- 指定结果按照哪几个字段来分组[HAVING] -- 过滤分组的记录必须满足的次要条件[ORDER BY ...] -- 指定查询记录按一个或多个条件排序[LIMIT {[offset,]row_count | row_countOFFSET offset}];-- 指定查询的记录从哪条至哪条
11. 去重
去重 distinct
-- 查询全部的考试成绩SELECT * FROM result;-- 查询有哪些同学参加了考试SELECT `student_no` FROM result;-- 发现重复数据，去重SELECT DISTINCT `student_no` FROM result;
12. 联表查询join on
七种join理论
13. 分页和排序
排序
-- 排序： 升序 ASC 降序 DESC
-- ORDER BY 通过那个字段排序，怎么排
分页
-- 语法 ： limit 起始值，页面的大小
14. mysql的备份
使用命令行导出 mysqldump 命令行使用
# 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql# 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql# 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql# 导入
# 登录的情况下，切换到指定的数据库
# use 数据库名字
# source 备份文件（绝对路径）
# 也可以这样导入
mysql -u用户名 -p密码 库名<备份文件
15. SQL CRUD
CRUD指的是增(insert into)、删(delete)、改(update)、查(select)
create table class(
id varchar(32) not null,
name varchar(45) not null comment '班级名称',PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE student (id varchar(32) NOT NULL,name varchar(45) NOT NULL comment '学生姓名',num int NOT NULL comment '学生学号',class_id varchar(32) not null comment '班级id',PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO class VALUES ('1','1班'),('2','2班'),('3','3班'),('4','4班');INSERT INTO student VALUE
(replace(UUID(),'-',''),'学生1',2013001,'1')
,(replace(UUID(),'-',''),'学生2',2013002,'1')
,(replace(UUID(),'-',''),'学生3',2013003,'1')
,(replace(UUID(),'-',''),'学生4',2013004,'1')
,(replace(UUID(),'-',''),'学生5',2013005,'1')
,(replace(UUID(),'-',''),'学生6',2013006,'2')
,(replace(UUID(),'-',''),'学生7',2013007,'2')
,(replace(UUID(),'-',''),'学生8',2013008,'2')
,(replace(UUID(),'-',''),'学生9',2013009,'2')
,(replace(UUID(),'-',''),'学生10',2013010,'2')
,(replace(UUID(),'-',''),'学生11',2013011,'3')
,(replace(UUID(),'-',''),'学生12',2013012,'3')
,(replace(UUID(),'-',''),'学生13',2013013,'3')
,(replace(UUID(),'-',''),'学生14',2013014,'3')
,(replace(UUID(),'-',''),'学生15',2013015,'3')
,(replace(UUID(),'-',''),'学生16',2013016,'4')
,(replace(UUID(),'-',''),'学生17',2013017,'4')
,(replace(UUID(),'-',''),'学生18',2013018,'4')
,(replace(UUID(),'-',''),'学生19',2013019,'4')
,(replace(UUID(),'-',''),'学生20',2013020,'4')
增加(Create)
增加数据只用INSERT INTO语句：
INSERT INTO TABLE VALUE (value1,value2)
查询(Retrieve)
查询语句使用SELECT命令即可：
SELECT column1,column2 FROM table
更新(Update)
更新表使用UPDATE语法，将已有的老数据更新为新数据。
UPDATE table SET column1 = value1 WHERE 条件
删除(Delete)
删除使用DELETE语法
DELETE FROM table WHERE column1 = value1
16. 子查询
SQL子查询也叫嵌套SELECT语句，一个SELECT语句的查询结果能够作为另一个语句的输入值。子查询能够出现的地方有：
查询1班、2班的所有学生：
SELECT stu.name,stu.num FROM
student as stu where stu.class_id in (SELECT id FROM class as cla where cla.name in ('1班','2班'));
学生1 2013001
学生2 2013002
学生3 2013003
学生4 2013004
学生5 2013005
学生6 2013006
学生7 2013007
学生8 2013008
学生9 2013009
学生10 2013010
JOIN子句：作为一个临时表
从1班、2班所有学生中查询出学号在201304到201406之间的学生：
SELECT name,num
FROM (SELECT stu.name,stu.num FROM student as stu where stu.class_id in (SELECT id FROM class as cla where cla.name in ('1班','2班'))) temp_talbe
WHERE num BETWEEN 2013004 AND 2013006
17. JDBC技术
JDBC简介(官网简介)
SUN公司为了简化、统一对数据库的操作，定义了一套java操作数据库的规范，称之为JDBC，JDBC不能直接操作数据库，JDBC通过接口加载数据库的驱动，然后操作数据库。JDBC：Java Data Base Connectivity，它主要由接口组成。组成JDBC的2个包为java.sql和javax.sql。开发JDBC应用需要这2个包的支持外，还需要导入相应JDBC的数据库实现（即数据库驱动）。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** @ClassName: JDBCDemo01* @Description: 我的第一个JDBC程序* @Author: zyy* @Date: 2021/07/13 21:59* @Version: 1.0*/
public class JDBCDemo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1.加载驱动
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());//推荐这种写法加载驱动Class.forName("com.mysql.jdbc.Driver");//2.用户信息和URL// useSSL=true可能会报错String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";String userName = "root";String passWord = "123456";//3.连接成功，数据库对象 Connection代表数据库Connection connection = DriverManager.getConnection(url, userName, passWord);//4.执行SQl的对象 Statement 执行的sql对象Statement statement = connection.createStatement();//5.执行SQL的对象 去 执行SQL ，可能存在结果，查看返回的结果String sql = "SELECT * FROM users";//返回的结果集 结果集中封装了我们全部的查询的结果ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {System.out.println("id="+resultSet.getObject("id"));System.out.println("name="+resultSet.getObject("name"));System.out.println("password="+resultSet.getObject("password"));System.out.println("email="+resultSet.getObject("email"));System.out.println("birthday="+resultSet.getObject("birthday"));System.out.println("===============================");}//6.释放连接resultSet.close();statement.close();connection.close();}
}
18. JDBC实现增删改查
JDBC技术主要是同数据库打交道，那么免不了增删改查，由上面的代码可以看出，在对数据库进行操作之前需要先建立连接，在操作之后都需要释放资源，所以我们可以把这两部分内容抽取出来，写到JDBCUtils类中来实现：
public class JDBCUtils {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;//加载驱动static{try {//db.properties是一个配置文件，里面有连接数据库所需要的信息InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties prop = new Properties(); prop.load(in);//加载配置文件driver = prop.getProperty("driver");url = prop.getProperty("url");username = prop.getProperty("username");password = prop.getProperty("password");Class.forName(driver);//加载驱动} catch (Exception e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url, username, password);//获得connection}public static void release(Connection conn, Statement st, ResultSet rs){ //释放资源if(rs != null){try{rs.close();}catch(Exception e) {e.printStackTrace();}rs = null;}if(st != null){try{st.close();}catch(Exception e) {e.printStackTrace();}st = null;}if(conn != null){try{conn.close();}catch(Exception e) {e.printStackTrace();}conn = null;} }
}
db.properties文件：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day14
username=root
password=root
这样我们就完成了数据库的连接和资源的释放工具类。下面我们开始编写对数据库的增删改查：
//使用JDBC对数据库的增删改查
public class Demo2 {private Connection conn = null;private Statement st = null;private ResultSet rs = null;@Testpublic void insert(){try {conn = JDBCUtils.getConnection();st = conn.createStatement();String sql = "insert into user(id,name,password,email,birthday) values(3,'ww','123','ww@sina.com','1982-06-14');";int num = st.executeUpdate(sql);//返回的是该sql语句会影响数据库的几行if(num > 0){System.out.println(num);System.out.println("插入成功");}} catch (Exception e) {e.printStackTrace();}finally {JDBCUtils.release(conn, st, rs);}}@Testpublic void delete(){//监控区域try {conn = JDBCUtils.getConnection();st = conn.createStatement();String sql = "delete from user where password='123'";int num = st.executeUpdate(sql);if(num > 0){System.out.println(num);System.out.println("删除成功");}} catch (Exception e) {//捕获区域e.printStackTrace();}finally {//善后区域JDBCUtils.release(conn, st, rs);}}@Testpublic void update(){//监控区域try {conn = JDBCUtils.getConnection();st = conn.createStatement();String sql = "update user set password='456' where name='ww'";int num = st.executeUpdate(sql);if(num > 0) {System.out.println(num);System.out.println("修改成功");}} catch (Exception e) {//捕获区域e.printStackTrace();}finally {//善后区域JDBCUtils.release(conn, st, rs);}}@Testpublic void find(){//监控区域try {conn = JDBCUtils.getConnection();st = conn.createStatement();String sql = "select id,name,password,email,birthday from user";rs = st.executeQuery(sql);while(rs.next()){int id = (Integer)rs.getObject("id");String name = (String)rs.getObject("name");String password = (String)rs.getObject("password");String email = (String)rs.getObject("email");Date birthday = (Date)rs.getObject("birthday");System.out.println(id+","+name+","+password+","+email+","+birthday);}} catch (Exception e) {//捕获区域e.printStackTrace();}finally {//善后区域JDBCUtils.release(conn, st, rs);}}
}
19. Druid数据库连接池技术
- Druid :数据库连接池实现技术，由阿里巴巴提供的
- 步骤∶
1．导入jar包druid-1.0.9.jar
2．定义配配置文件:
1.是properties形式的
2.可以叫任意名称，可以放在任意目录下（也就是不会自动加载，必须手动加载）
3．手动加载配置文件。使用（Properties集合）
sqlProperties pro = new Properties();
InputStream ras = Demo10.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(ras);
4．获取数据库连接池对象∶通过工厂类来获取DruidDataSourceFactory
DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
5．获取连接∶getConnection()
Connection connection = dataSource.getConnection();
配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db4
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000
示例
package com.ningxiao.day17;
import com.alibaba.druid.pool.DruidDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/*
author :wt
@create 2020-08-27 15:28
*/
public class Demo10 {public static void main(String[] args) throws Exception {
//1. 导入druid-1.0.9.jar包//2. 设置配置文件Properties pro = new Properties();InputStream ras = Demo10.class.getClassLoader().getResourceAsStream("druid.properties");//捕获raspro.load(ras);//3. 获取连接池对象DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);//4. 创建连接对象Connection connection = dataSource.getConnection();String sql = "select * from account";PreparedStatement ps = connection.prepareStatement(sql);ResultSet rs = ps.executeQuery();while (rs.next()){int id = rs.getInt("id");String name = rs.getString("name");
double balance = rs.getDouble("balance");
System.out.println(id+":"+name+":"+balance);}//断开连接对象，将连接对象归还给数据库连接池connection.close();}
}
20. Druid工具类
- 定义工具类
1．定义一个类JDBCUtils
2．提供静态代码块加载配置文件，初始化连接池对象
3．提供方法
1．获取连接方法∶通过数据库连接池获取连接
2．释放资源
3，获取连接池的方法
示例
package com.ningxiao.day18;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/*
author :wt
@create 2020-08-27 16:37
*/
public class JDBCUtils {
private static DataSource ds;//静态代码块加载配置文件，初始化连接池对象static {Properties pro = new Properties();InputStream rs = JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
try {pro.load(rs);ds = DruidDataSourceFactory.createDataSource(pro);} catch (IOException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}
}//创建连接对象public static Connection getConnection() throws SQLException {return ds.getConnection();}//关闭连接对象public static void close(Connection conn, Statement stmt){if (conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}if (stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}}
}
//Demo
package com.ningxiao.day18;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*
author :wt
@create 2020-08-27 16:44
*/
public class Demo01 {public static void main(String[] args) {
Connection connection = null;ResultSet rs = null;PreparedStatement ps = null;try {//监控区域connection = JDBCUtils.getConnection();String sql = "select * from account";ps = connection.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()){int id = rs.getInt("id");String name = rs.getString("name");
double balance = rs.getDouble("balance");
System.out.println(id+":"+name+":"+balance);}
} catch (SQLException e) {//捕获区域e.printStackTrace();}finally {//善后区域JDBCUtils.close(connection,ps);}}
}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！