百木园-与人分享,
就是让自己快乐。

mysql语法使用详细代码版

mysql

1.什么是数据库

数据库:(DB,DataBase)

作用:用来存储数据,管理数据。Txt,Excel,word是在数据库出现之前存储数据的。

概念:数据仓库,安装在操作系统上的软件。

数据库是所有软件体系中最核心的存在

2.数据库分类

关系型数据库(SQL)

  • Mysql,oracle,sqlServer,Sqlite
  • 通过表和表之间,行与列之间的关系进行数据的存储……

非关系型数据库(NoSql)

  • redis,MongDb
  • 对象存储,通过对象的自身属性来决定。

DBMS(数据库管理系统)

  • 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据。
  • Mysql

3.数据库连接

命令行连接:

mysql -uroot -p123123 --数据库注释

数据库基本操作:

show databases; --显示所有数据库
 use mysql;     --切换数据库
 show tables;  --显示所有表
 describe user; --显示表的具体数据
 exit;  --退出

3.数据库的操作

数据库的字段属性

Unsigned:

  • 无符号整数
  • 声明了该列值不能为负数

zeroFill:

  • 0填充的
  • 不足位数的,使用0来填充

自增:

  • 自增
  • 可以自定义增加的数量和步长

非空:

  • NUll not null
  • 设置为非空不给赋值就会报错

默认:

  • 如果不赋值则为默认设置的值

操作数据库>操作数据库的表>操作表中的数据

操作数据库:

创建数据库

create database westos;

删除数据库

drop database westos;

使用数据库

use shop;

查看所有数据库

show databases;

操作数据库的表:

创建表

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment \'id\',
 
`name` varchar(30) not null default \'匿名\' comment \'姓名\',
 
`pwd` varchar(20) not null default \'123123\' comment \'密码\',
 
`sex` varchar(2) not null default \'nv\' comment \'性别\',
 
`birthday` datetime default null comment \'出生日期\',
 
`address` varchar(100) default null comment \'住址\',
 
`email` varchar(50) default null comment \'邮箱\',
 
primary key(`id`)
 
)engine=innodb default charset=utf8;

格式:

create table if not exists `表名`(
    `字段名` 列类型 [属性][索引][注释],
    `字段名` 列类型 [属性][索引][注释],
        ......
    `字段名` 列类型 [属性][索引][注释]
);

删除表

drop table teacher if exists

修改表

修改表名: alter table 表名 as 新表名

alter table student as teacher

添加一个字段:alter table 表名 add 字段名 属性

alter table teacher add age int(11)

修改表的字段 类型:alter table 表名 modify 字段名 列属性

alter table teacher modify age varchar(10)

重名字:alter table 表名 change 旧名字 新名字

alter table teacher change age age1 int(1)

删除字段

alter table teacher drop age1

查看表

desc 表名

数据表类型

数据库引擎:

innodb:默认使用

myisam:早期使用

MYSAIM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间大小 较小 较大约为2倍

4.mysql数据管理

外键

create table `grade`(
 
 `gradeId` int(10) not null auto_increment comment \'年级id\',
 
`gradeName` varchar(20) not null comment \'年级名称\',
 
primary key(`gradeId`)
 
)engine=innodb default charset=utf8;

--学生表的gradeId引用年级表的gradeId
--定义外键key
--给外键添加约束reference引用

create table if not exists `student`(
 
`id` int(4) not null auto_increment comment \'id\',
 
`name` varchar(30) not null default \'匿名\' comment \'姓名\',
 
`pwd` varchar(20) not null default \'123123\' comment \'密码\',
 
`sex` varchar(2) not null default \'nv\' comment \'性别\',
 
`birthday` datetime default null comment \'出生日期\',
 
`address` varchar(100) default null comment \'住址\',
 
`email` varchar(50) default null comment \'邮箱\',
 
primary key(`id`)
 key `fk_gradeId` (`gradeId`),
constraint `fk_gradeId` foreign key(`gradeId`)references`grade`(`gradeId`)        
)engine=innodb default charset=utf8;


外键在开发中不建议使用,因为会在删除更新的时候产生级联,造成错误!

DML语言

数据库管理语言:

插入: insert into 表名(字段名) values(属性)

insert into grade(gradeName) values(\'大四\');

insert into grade (gradeName) values(\'大二\'),(\'大三\');

更新:update set 表名 修改的字段和值 where 条件,不指定条件会修改所有的列。

update grade set gradeName=\'大一\' where gradeId=1;

删除:delete from 表名

delete from grade where gradeId=1;

清空:truncate 表名

truncate grade;

truncate和delete删除数据的区别:

  • truncate会清空自增归零,从1开始,delete不会(除非断电重启,因为mysql在内存中)。
  • 不会影响事务。

查询:select 字段 from 表名

-- 查询表的所有字段
select * from grade; 
-- 查询id字段
select gradeId from grade;
-- 起别名gradeid为id 
select gradeId as id from grade;
-- 在查出来的数据前面加上字符串 
select concat(\'加上字符串:\',gradeName) as 名字 from grade;
-- 按照id条件查询
select gradeName as 名字 from grade where gradeId=2;
-- 按照name条件进行查询
select gradeName as 名字 from grade where gradeName=\'大二\';
-- 去重
select distinct gradeId from grade;
-- 查询系统版本
select version();
-- 用来计算
select 100*3-1 as jisuanjieguo;
-- 查询自增的步长
select @@auto_increment_increment

模糊查询

like结合: %代表0到任意个字符,结合_代表一个字符

select gradeName from grade where gradeName like \'大%\';

select gradeName from grade where gradeName like \'%er\';

select gradeName from grade where gradeName like \'er_\';

in:查询具体的一个或者多个值

select gradeName from grade where gradeName in(\'大er\',\'大san\');

联表查询

join(要连接的表)……on(等值判断)

select gradeName,name,sex from student inner join grade on where grade.gradeId=student.id;

操作 描述
inner join 如果表中至少有一个匹配,就返回
left join 会从左表中返回所有的值,即使右表中没有
right join 会从右表中返回所有的值,即使左表中没有

自连接

自己的表和自己的表连接,一张表拆为两张表。

select a.categoryName as father ,b.categoryName as son from category as a,category as b where a.categoryid=b.pid

排序

order By通过哪个字段排序。desc降序,asc升序。

select email from student order by email asc;

select email from student order by email desc;

分页

limit 起始页,页面的大小。

将数据分页显示,使用户体验更好。

select email from student limit 1,1;

子查询

在where语句中嵌套一个子查询。

select gradeName from grade where gradeName=(select name from student where name=\'匿名\');

group by

通过什么字段来分组。

having

分组之后的条件

5.mysql函数

常用函数

--绝对值
select abs(-8);
--向上取整
select ceiling(9.4);
--向下取整
select floor(9.4);
--随机数
select rand();
--判断正负数
select sign(-1);
--字符串长度
select char_length(\'lllll\');
--拼接字符串
select concat(\'w\',\'s\');
--字符串替换
select insert(\'woaibiancheng\',1,4,\'llll\');
--转换大小写
select lower(\'SS\');
select upper(\'hh\');
--第一次出现的字串的索引
select replace(\'sss\',\'ss\',\'ww\');
--返回指定的字符串 从第几个开始截取几个
select substr(\'ssssss\',4,6);
--获取当前时间
select current_date();
select now();
select localtime();
select current();
select year(now());
select month(now());
       ......

聚合函数

函数名称 描述
sum() 总和
avg() 平均值
max() 最大值
min() 最小值
count() 计数
select count(*) from student;
select count(1) from student;
select count(name) from student;
select sum(name) from student;
select avg(name) from student;
select min(name) from student;
select max(name) from student;

数据库级别的md5加密算法

--建表
mysql> create table testmd5(
 
`id` int(4) not null,
 
`name` varchar(20) not null,
 
`pwd` varchar(50) not null,
 
primary key(`id`)
 
)engine=innodb default charset=utf8;

--插入数据
insert into testmd5 values(1,\'zhangsan\',\'123123\'),(2,\'lisi\',\'123123\'),(3,\'wangwu\',\'123123\');

--更新密码为MD5
update testmd5 set pwd=md5(pwd);

--插入密码为MD5
insert into testmd5 values(4,\'xixi\',md5(\'123123\'));

--查询密码为MD5
select * from testmd5 where name=\'xixi\' and pwd=md5(\'123123\');
+----+------+----------------------------------+
| id | name | pwd                              |
+----+------+----------------------------------+
|  4 | xixi | 4297f44b13955235245b2497399d7a93 |
+----+------+----------------------------------+
1 row in set

6.事务

要么都成功,要么都失败。

1.sql执行 a给b转账 2.sql执行 b接受a的转账

同一事务一起执行。

事务的原则

  • 原子性:要么都成功,要么就失败
  • 一致性:事务前后数据保持一致
  • 持久性:事务一旦提交不可逆
  • 隔离性:数据库为每一个用户开启一个事务,不同事务之间要相互隔离。

脏读:一个事务读取了另一个事务未提交的数据。

虚读:一个事务读取到了另一个事务正在插入的事务。

事务执行流程

--关闭自动提交
set autocommit=0;
--事务开启
start transaction;
     --update
     --delete
--提交持久化 成功
commit;
--回滚 回到原来的样子 失败
rollback
--事务结束
set autocommit=1;

模拟转账

--建表
create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`money` decimal(9,2) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;
--插入数据
insert into account(`name`,`money`) values(\'A\',2000),(\'B\',10000);
--关闭自动提交
set autocommit=0;
--开启事务
start transaction;
--更新数据
update account set money=money-500 where `name`=\'A\';
update account set money=money+500 where `name`=\'B\';
--提交成功
commit;
--回滚失败
rollback;
--开启自动提交
set autocommit=1;

7.索引的分类

  • 主键索引
    • 唯一的标识,主键不可重复,只有一个列作为主键
  • 唯一索引
  • 常规索引
  • 全文索引

创建索引在查询数据较多的时候可以大大提升查询速度。

索引创建:create index 索引名 on 表(字段)

create index id_app_user_name on app_user(name)

索引原则

  • 索引不是越多越好
  • 不要对数据进行变动
  • 小数据的表不需要加索引
  • 索引一般加在常用查询的字段

8.规范数据库设计

当数据库比较复杂的时候,就需要规范设计了。

糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库的删除和插入都会麻烦,异常
  • 程序性能差

良好的数据库

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中关于数据库的设计

  • 分析需求:分析业务和需要处理的数据库的需求
  • 概要设计:设计关系图e-r图

三大范式

第一范式1NF

  • 保证每一列不可再分

第二范式2NF

  • 已达到第一范式
  • 每张表的只描述一个事情

第三范式3NF

  • 满足第二范式
  • 数据库的每一列数据都和主键直接相关,而不能间接相关。

规范性和性能问题

在实际开发中也不能一味地追求满足三大范式

关联查询的表不能超过三张

  • 考虑商业化的需求和目标,数据库的性能更加重要
  • 在规范性能问题的时候,需要适当的考虑一下规范性
  • 故意给某些表增加一些冗余的字段。(使之从多表查询变为单表查询)

jdbc

数据库驱动

不同的数据库有不同的驱动,应用程序需要先连接到驱动,才能连接到数据库。

jdbc

为了简化开发人员对数据库的操作,提供了一个java操作数据库的规范,俗称jdbc。

第一个jdbc程序

1.创建一个数据库

create table account(
 
`id` int(10) not null auto_increment,
 
`name` varchar(10) not null,
 
`pwd` varchar(10) not null, 
 primary key(`id`)
)engine=innodb default charset=utf8;

2.添加数据库驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rJ96yyY1-1658113311722)(../AppData/Roaming/Typora/typora-user-images/image-20220718090403041.png)]

3.编写代码

package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author panglili
 * @create 2022-07-18-9:05
 */
//我的第一个jdbc程序
public class demo1 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName(\"com.mysql.cj.jdbc.Driver\");
        //2.用户信息和url
        String url=\"jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false\";
        String name=\"root\";
        String pwd=\"123123\";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.执行sql对象
        Statement statement = connection.createStatement();
        //5.查看结果
        String sql=\"select * from account\";
        ResultSet resultSet =statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println(\"id=\"+resultSet.getObject(\"id\"));
            System.out.println(\"id=\"+resultSet.getObject(\"name\"));
            System.out.println(\"id=\"+resultSet.getObject(\"money\"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }


}

分析jdbc对象

  • Class:加载驱动,固定写法
  • url:连接的统一资源定位符
  • username:用户名
  • password:密码
  • connection:获取数据库对象,代表数据库
  • statement:执行sql的对象
  • resultset:sql对象执行方法获取返回的集合

sql注入:

web应用程序对用户输入数据的合法性没有判断或者过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾添加额外的sql语句,在管理员不知情的情况下进行非法操作,以此实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。

java解决的方法:

  • 使用prepareStatement
package com.sql;

import java.sql.*;

/**
 * @author panglili
 * @create 2022-07-18-9:57
 */
public class demo2 {
    public static void main(String[] args) throws Exception {
        //1.加载驱动
        Class.forName(\"com.mysql.cj.jdbc.Driver\");
        //2.用户信息和url
        String url=\"jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf-8&userSSL=false\";
        String name=\"root\";
        String pwd=\"123123\";
        //3.连接成功
        Connection connection = DriverManager.getConnection(url, name, pwd);
        //4.预编译sql先不赋值
        String sql=\"delete  from account where id=?\";
        PreparedStatement st = connection.prepareStatement(sql);

        //5.手动给参数赋值
        st.setInt(1,2);
        //6.执行 删除语句会返回受影响的行数
        int i = st.executeUpdate();

        if(i>0){
            System.out.println(i+\"行删除成功\");
        }
        //7.释放连接
        st.close();
        connection.close();
    }
}

prepareStatement防止sql注入的本质就是,把传递进来的参数当做字符,如果存在转义字符会被直接转义!


来源:https://www.cnblogs.com/lumanmanqixiuyuanxi/p/16489702.html
本站部分图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » mysql语法使用详细代码版

相关推荐

  • 暂无文章