数据库:(DB,DataBase)
作用:用来存储数据,管理数据。Txt,Excel,word是在数据库出现之前存储数据的。
概念:数据仓库,安装在操作系统上的软件。
数据库是所有软件体系中最核心的存在
关系型数据库(SQL)
非关系型数据库(NoSql)
DBMS(数据库管理系统)
命令行连接:
mysql -uroot -p123123 --数据库注释
数据库基本操作:
show databases; --显示所有数据库 use mysql; --切换数据库 show tables; --显示所有表 describe user; --显示表的具体数据 exit; --退出
数据库的字段属性:
Unsigned:
zeroFill:
自增:
非空:
默认:
操作数据库>操作数据库的表>操作表中的数据
操作数据库:
创建数据库
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倍 |
外键
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删除数据的区别:
查询: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
分组之后的条件
常用函数
--绝对值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
要么都成功,要么都失败。
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;
创建索引在查询数据较多的时候可以大大提升查询速度。
索引创建:create index 索引名 on 表(字段)
create index id_app_user_name on app_user(name)
索引原则
当数据库比较复杂的时候,就需要规范设计了。
糟糕的数据库设计:
良好的数据库
软件开发中关于数据库的设计
三大范式
第一范式1NF
第二范式2NF
第三范式3NF
规范性和性能问题
在实际开发中也不能一味地追求满足三大范式
关联查询的表不能超过三张
数据库驱动
不同的数据库有不同的驱动,应用程序需要先连接到驱动,才能连接到数据库。
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.添加数据库驱动
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对象
sql注入:
web应用程序对用户输入数据的合法性没有判断或者过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾添加额外的sql语句,在管理员不知情的情况下进行非法操作,以此实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
java解决的方法:
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注入的本质就是,把传递进来的参数当做字符,如果存在转义字符会被直接转义!
原文出处:mysql语法使用详细代码版