03-Mysql基本语法

Mysql基本语法

基础概念

实体

实际需要关注的物体，想要描述相关的静态特征

数据

计算机中用于描述关注物体的静态特征的符号，将这些特征组合在一起形成了记录(reocrd)

数据库

将数据按照一定的结构组织、存储并管理的一个程序，有关系型数据库和非关系型数据库

数据库系统(DBS)

包含了数据的格式定义、以及数据的各种操作、以及数据库的各种访问

数据库管理系统(DBMS)

只针对的数据操作，通常带有可视化的界面传程序

常见的数据库系统

关系型数据库：Mysql,Oracle,SqlServer

非关系型数据库:Redis,MongoDB,HBase

数据库的连接

DOS界面(Windows):mysql -h ip地址 -P 端口号 -u 账号 -p

查看所有的数据库

show databases;

选定指定的数据库

use 数据库名;

显示数据库中的表

show tables;

使用特定的表

use 表名;

数据库的创建与删除

create database if not exists 数据库名;
drop databases if exists;

SQL语句

1.概念

结构化查询语言。用于操作数据库

2.组成

2.1 数据库定义语言(DDL)，用来定义和管理数据对象，入如数据库等

create drop alter

2.2 数据库操作语言(DML), 用于操作数据库对象中包含的数据

insert、update、delete

2.3 数据库查询语言(DQL),用于查询数据库

select

2.4 数据库控制语言(DCL), 用来管理数据库，包括管理权限和数据更改

grant、commit、rollback

Mysql常用的数据类型

数值型(int、double)、字符串(char、varchar())、日期和时间(date、datetime)、空值(null)

数据表的创建于删除

-- 创建表的语法
create table 表名(
    列名 列数据类型 列 约束(是否为空、是否为主键) 注释
)engine = 存储引擎 charset = 字符串 comment= '注释';

-- 删除表
drop table if exists 表名;



-- 新建数据库
create database if not exists `company`;
-- 使用数据库
use `company`;
-- 创建表格employee
create table if not exists `employee`(
	id varchar(10) primary key not null comment '员工编号, 主键',
	name varchar(20) not null comment '员工姓名',
	sex varchar(1) not null comment '员工性别',
	phone varchar(11) not null comment '联系电话',
	department varchar(20) not null comment '部门名称',
	salary double not null comment '员工薪资',
	join_date date not null comment '入职时间'
)charset = utf8 comment '员工表';



create table department
(
   department_id        int not null auto_increment comment '部门的唯一标识',
   department_name      varchar(20) not null comment '部门的名称',
   department_description varchar(50) not null comment '部门信息概要',
   department_responsibility varchar(100) not null comment '每个部门具体的职责',
   primary key (department_id)
) charset = utf8 ;

alter table department comment '部门信息表';
-- 新增部门
insert into department (department_id, department_name, department_description, department_responsibility)
	values (default, '软件开发部', '开发软件的部门', '关注国内外软件市场的发展动向、最新技术及信息，组织内部技术交流'),
				 (default, '软件销售部', '销售软件的部门', '完成招/投标、商务谈判、报价；测算项目损益，确保项目达到公司要求的业绩利润指标'),
				 (default, '人力资源部', '管理人事的部门', '制订、修改单位各项人力资源管理制度和管理办法，建立制度化、规范化、科学化的人力资源管理体系'),
				 (default, '客户服务部', '负责公司售后服务的管理工作的部门', '提供全面的售后服务');


drop table if exists post;


create table post
(
   post_id              int not null auto_increment comment '部门的唯一标识',
   post_name            varchar(20) not null,
   post_department_id   int not null comment '岗位所属部门的唯一标识',
   post_description varchar(100) not null comment '每个岗位的内容',
   post_responsibility varchar(200) not null comment '每个岗位具体的内容',
   post_salary    double not null comment '每个岗位对应的薪资',
   primary key (post_id)
);

alter table post comment '岗位信息表';

alter table post add constraint fk_post_department_id foreign key (post_department_id)
      references department (department_id) on delete restrict on update restrict;

-- 添加岗位
insert into  `post` (post_id, post_name, post_department_id, post_description, post_responsibility, post_salary)
		values (default, '项目经理', 1, '进度、质量', '严格把控', 30000),
					(default, '架构师', 1, '架构', '严格把控', 20000),
					(default, '程序员', 1, '代码实现', '按时完成代码', 10000),
					(default, '销售主管', 2, '代码实现', '按时完成代码', 10000),

Mysql默认的数据表引擎

InnoDB:支持事务(数据操作提交时支持回滚动作)

mysql运算符

1. 算术运算符 + - * / (div) %
2. 关系运算符 > < > <= >= ！= <> <=>
3. 逻辑逻辑运算符 && || !(and or not xor)
4. 其他运算符 is null is not null

mysql 模糊查询

1. 使用like 字符串
2. 包含:%内容%
3. 开头是:内容%
4. 结尾是:%内容
5. 匹配单个字符: _

mysql 约束

1. 主键约束 primary key
2. 非空约束 not null
3. 外键约束 foreign key (alter table add constraint 外键约束名 foreign key 字段名 reference 要光联的表名(要光联的字段))

mysql增删改查基本语法

1. insert into 表名 (字段名，多个字段名逗号隔开) values (和字段名对应的值)
2. delete from 表名 where 条件
3. update 表名 set 字段名 = 值， 多个值用逗号隔开
4. select * from 表名 where 条件
5. 对结果排序: order by 字段名 [asc 升序; desc 降序]
6. 聚合函数:sum avg max min

mysql使用自定义连接工具类会出现的问题

使用自定义连接工具类的时候，是使用字符串拼接的方式，但是这种方式查找风险,因为可能会导致直接在文本框输入查询条件(where 1 = 1),导致整个数据库的数据泄露或被被修改,所以应该避免 字符串的拼接

 /**
     * 执行查询
     * 使用spring的mysql连接工具类时，必须手动实现转换的代码(RowMapper)，而不是直接使用lambda表达式
     * 查询记录的方法
     *
     * @param sql    sql语句
     * @param mapper 映射器：把ResultSet集合中每一个元素转换成T后返回
     * @param params 参数列表
     * @return {@link List}<{@link T}>
     * @throws SQLException sqlexception异常
     */
    public <T> List<T> executeQuery(String sql, MyFunction<ResultSet, T> mapper, Object[] params) throws SQLException {
        List<T> tList = new ArrayList<>();
        //1、创建连接对象:使用dataSource
        MysqlDataSource dataSource = new MysqlDataSource();
        //没有完整 的构造方法，单独设置
        dataSource.setURL(url);
        dataSource.setUser(user);
        dataSource.setPassword(password);
        //拿到连接对象
        Connection connection = dataSource.getConnection();
        //2、创建语句对象
        PreparedStatement statement = connection.prepareStatement(sql);
        ;
        //Statement statement = connection.createStatement();
        //2.1 设置参数
        if (params != null && params.length != 0) {
            for (int i = 0; i < params.length; i++) {
				//set方法是从1开始的，所以需要加1
                statement.setObject(i + 1, params[i]);
            }
        }
        //3、执行查询语句，返回结果
        ResultSet resultSet = statement.executeQuery();
        //把数据库返回的每一个结果转换成界面层需要的数据：VO
        while (resultSet.next()) {
            tList.add(mapper.apply(resultSet));
        }
        //4、关闭连接
        resultSet.close();
        statement.close();
        connection.close();
        return tList;
    }

测试方法的代码

  @Test
    public void executeQuery() throws SQLException {
		//通过id和名称查询岗位信息
        String sql = "select * from post where post_id = ? and post_name = ?";
		//原本需要拼接的字符串，直接存储在对象数组中
        Object[] objects = {1, "项目经理"};
        //调用工具类的方法查询
        MysqlUtil.getInstance().executeQuery(sql, this::toPostVo, objects).forEach(System.out::println);

    }
    //手动实现转换的方法:ResultSet --> VO
    public PostVO toPostVo(ResultSet resultSet) throws SQLException {
        return new PostVO(
                resultSet.getInt("post_id"),
                resultSet.getString("post_name"),
                resultSet.getInt("post_department_id"),
                resultSet.getString("post_description"),
                resultSet.getString("post_responsibility"),
                resultSet.getDouble("post_salary")
        );

    }

结果如图: