mysql语法使用详细代码版

mysql 文章 2022-07-20 10:06 246 0 全屏看文

AI助手支持GPT4.0

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');--更新密码为MD5update testmd5 set pwd=md5(pwd);--插入密码为MD5insert into testmd5 values(4,'xixi',md5('123123'));--查询密码为MD5select * 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注入的本质就是,把传递进来的参数当做字符,如果存在转义字符会被直接转义!

-EOF-

AI助手支持GPT4.0