mysql

MYu
41 阅读10分钟

常用类型

在进入语句之前，我们先来看看一些类型：

  • INT:存储整数
  • VARCHAR（100）：存储变长字符串，可以指定长度
  • CHAR：定长字符串，不够自动在末尾填充空格
  • DOUBLE：存储浮点数
  • DATE：存储日期
  • TIME：存储时间
  • DATETIME：存储时间和日期

语句

1.插入

insert into hello-mysql.student (key) values (value)

2.更新

update hello-mysql.student set number=311 where id=10

3.删除

delete from hello-mysql.student where id=10

4.查询

select * from hello-mysql.student

select name,score from student

可以通过as修改返回的列名

select name as "名字"，score as "分数" from student

这些语句都可以通过where来指定条件，如果指定多个条件就用and连接，通过%拼接来找到以某个字开头的

select score from student where id<=10 and name like '王%'

可以通过in来指定一个集合，而not in指定不在这个集合里面的数据

select * from student where class in ('一班'，'二班')

可以通过between and来指定一个范围

select * from student age between 18 and 30

实现分页

select * from student limit 0,5

从0开始的五个

通过order by来指定排序.asc升序，desc降序

select age,score from student order by age asc,score desc

还可以进行分组统计

  • 分组：group by
  • 求平均数：avg()
  • count(*):一般用来统计当前行数

select count(*) from student group by class

这里就会统计出每个班级的数据

  • having:统计之后用来对统计的结果进行过滤的

select score from student group by class having score>80

  • distinct:去重

select distinct class from student

  • sum(),max(),min()
  • 用于对字符串进行处理的

concat():在字符串添加前后缀，如concat('xx',name,'yy'),查询结果就是xx张三yy

length():字符串长度，如length(name)

upper(),lower()分别是变成大写和小写

substr():截取字符串，如substr(strObj,start,length),注意了mysql的下标是从1开始的，而通过contat()添加的前后缀就不算在内

  • 数值函数：

round():四舍五入，ceil():向上取整，floor():向下取整，abs():绝对值，mod():取模

  • 日期函数：year,month,day,date,time
  • 条件函数：if,case

select name,if(score>=60,'及格','不及格') from student

select name,score,case when score>=90 then '优秀' when score<=60 then '不及格'

  • 系统函数

version():版本号，database():数据库的名字，user():用户名及IP

  • 其他函数

nullif():如果相等返回null,不相等返回第一个值。

coalesce():返回第一个非null的值。

greatest,least:返回最大最小值。

  • 类型转化函数:convert,cast

select greatest(1,cast('123'as signed),3)

signed:整型，unsigned:无符号整型，decimal:浮点型，char:字符类型，date:日期类型，time:时间类型，datetime:日期时间类型，binary:二进制类型。

str_to_date,date_fromat

select date_fromat('2023-11-07','%Y年%m月%d日')

select str_to_date('2023-11-07',%Y-%m-%d)

关系对应

1.一对一

1.先把要创建的表给创建好，然后再关联外键（foreign keys）。

2.选择主表update或者delete的时候该怎么办。

cascade:主表主键更新，从表记录跟着更新，删除操作一样。

set null:主表主键更新或者主表记录删除，从表外键设置为null

restrict:只有没有从表的关联记录时，才允许删除主表记录或更新主表记录中的主键id

no action:和restrict一样

3.查询（join on,join on 默认是inner join）

select * from user（主表） Join id_card（关联的表） ON user.id=id_card.user_id（主表的哪一项和关联表的哪一项关联）

表user的user.id和表id_card的user_id关联。

inner join on :不返回未关联的数据

left join on :额外返回左表中未关联上的对象

right join on :额外返回右表中未关联上的对象

from后面是左表，join后面是右表

一对多

和一对一是一样的，只不过外键是加在多的那一边，比如有employ和department,一个部门对应多个员工，所以设置外键的时候就加在employ这边。

多对多

1.先把需要建立的两个表弄出来，然后再建立第三个表（中间表）

2.最后在这个中间表设置外键。分别指向那两个表。

3.关联查询：

select * from article a join article_tag at on a.id=at.article_id join tag t on t.id=at.tag_id where a.id=1

子查询

就是把多条查询语句结合在一起。比如查询成绩高于全校平均成绩的学生记录。

select * from student where score > (select avg(score) from student)

1.exists:符合条件的就返回结果，如：

select name from department where exist(select * from employee where department.id=employee.department_id)

对每个department,再子查询里查询它所有的employee，如果存在员工，那么条件成立，返回这个部门的name.

2.not exist:刚好和exist相反。如：

select name from depatment where not exist(select * from employee where department.id=employee.department_id)

查询没有员工的部门

3.子查询也可以是select和其他查询语句的结合体。

如：

insert into avg_price_by_category (category,avg_price) select category,avg(price) from product group by category

查询product的种类和平均数通过种类分组，然后把查询到的结果放到avg_price_by_category里面。（写的时候先外层再里层，理解的时候可以先里层在外层,执行的时候也是先执行里层然后再执行外层）

再如：

update employee set name = concat('技术-',name) where department_id = (select id from department where name='技术部')

查询部门名字等于技术部的id，然后更新department_id这个id的所有employee的名字加上'技术部'

再如：

delete from employee where department_id=(select id from department where name='技术部')

删除技术部。

事务（transaction)和隔离级别

事务内的几条sql语句要么全部执行成功要么远不执行失败，这样就能保证数据的一致性。比如我们要一次性更新两个或者多个有关联的表格的数据，后一个表格的数据是根据前一个表格的数据进行变更的，假如二者前一个更新成功而后一个没有更新成功，那么两个表格的数据就不一致了。

1.开启事务

start transaction

2.回滚（这个回滚是回滚所有数据）

rollback

如果不想回滚所有的数据那么可以手动告诉mysql一些保存的点

start transaction
savepoint aaa
update order_item set quantity=1 where order_id=3
savepoint bbb
update orders set total_amount=200 where_id=3
savepoint ccc

回滚语句

rollback to savepoint bbb

3.提交：提交之后就没有回滚了

commit

如果我们已经修改表格了，但是还没有提交表格，这时候我们想要查询修改后的数据，要怎么做呢？这就要用到mysql的4种隔离级别了。

  • read uncommitted:可以读到别的事物尚未提交的数据。

这容易出个问题就是第一次读到的时候是aaa，假如此时修改数据之后读取就变成bbb，这个叫不可重复读，而且如果别人回滚的时候你读取数据，读取到的只是一个临时数据，这是脏读。

  • read commited:只读取别的事物已提交的数据。

因为已经提交了，就不会出现脏读的问题，但是第一次读到aaa，修改后提交再次读取，读到的就是bbb，不可重复读取的问题依旧存在，而且此时读到的记录行数也可能不一样，这是幻读。

  • repeatable read:在同一事物内，多次读取数据将保证结果相同。

这个就解决了不可重复读取的问题，但是幻读的问题依旧存在。

  • serializable:在同一时间只允许一个事物修改数据。

性能较差，因为只能一个一个事务的执行。

typeORM

了解几个常见的属性
  • type是数据库的类型
  • user,password是登录数据库的用户名和密码
  • host,port指定数据库服务器的主机和端口号
  • database指定操作的数据库
  • synchronize根据同步建表，数据库里没有与Entity对应的表的时候会自动生成建表的sql语句并执行，有就不需要
  • logging打印生成的sql语句
  • entities指定有哪些和数据库对应的Entity

除了entities:[User,Role],还可以entities:['./**/entity/*.ts']

  • poolSize指定数据库连接池中连接的最大数量
  • connectorPackage指定用什么驱动包
  • extra是额外发送给驱动包一些选项
entity
  • @Entity指定表名
  • @PrimaryGeneratedColumn指定是一个自增的主键
  • @Column映射属性与字段的对应关系
  • createdate创造时间
  • UpdateDate更新时间
AppDataSource.manager
  • .save(要保存的类)：保存。可以通过这个来对数据库进行增加和更新，如：

增加AppDataSource.manager.save(User(要保存的类)，{FirstName：'aaa',LastNmae:'bbb'})

更新AppDataSource.manager.save(User(要保存的类)，{id:1,FirstName：'aaa111',LastNmae:'bbb111'})

虽然EntityManager有update和insert方法，但是他们不会select查询一次，而save方法会先select查询一次。

  • .delete()和.remove()
//delete
AppDataSource.manager.delete(User,[1,2])

//remove
const user=new User()
user.id=1
AppDataSource.manager.remove(User,user)

delete与remove不同的地方在于delete传入的id，而remove传入的entity对象。

  • .find(Class):查询，.findBy(Class,{条件})：根据条件查询数据，.findCount(Class):查询到的数据的条数，findCountBy(Class,{条件})：指定条件,.findOne(Class，{}):查询一条，如

AppDataSource.manager.findOne(User,{ select:{ firstName:true, age:true }, where:{ id:4 }, order:{ age:'ASC } })

  • findOneOrFail和findOneByOrFail这两个在没有找到都会抛出一个EntityNotFoundError异常。
try{
const user=await AppDataSource.manager.findOneOrFail(User,{
where:{id:6}
})
console.log(user)
}catch(e){
console.log(e)
}
  • 可以用query直接执行sql语句，如：

AppDataSource.manager.query('sql语句'，[传入的参数]) 这里面的sql语句书允许有占位符的，所以传入的参数对应的就是占位符的数.

  • query build()复杂的sql语句就会用这个。
//指定查询的字段
queryBuild.select('user')
//指定表为User实体，并取别名为user
.from(User,'user')
//指定条件，其中:age表示的是占位符，{age:21}表示的是占位符的那个数据应该为21
.where('user.age=:age',{age:21})
//获取查询数据
.getOne()
  • 开启事务，用.transaction()包裹，如：

AppDataSource.manager.transation(async manager=>{ await manager.save(User,{ id:4 }) })

  • getRepository

大家也许都发现了，每调用一个sql方法就要传入一个实体类User,这时候就可以先调用getRepository传入Entity,然后再进行一系列的操作。

AppDataSource.manager.getRepository(User).find(User)

外键
  • DataSource.initialize的时候就会和数据库建立联系
  • @JoinColumn():指定外键，@JoinColumn({name:'user_id'})可以修改外键名称
  • @OneToOne():给外键添加一个对应的关系，如：
@OneToOne(
//要关联的表
()=>User，
{
//设置CASCADE
onDelete:cascade
//自动按照关联关系来进行保存
cascade:true
}
)
user:User
  • 关联查询：relations

AppDataSource.manager.find(IdCard,relations:{user:user})

这样在查询IdCard的时候就会把与它有关联关系的user表也一起查询出来。也可以手动关联.leftJoinAndSelect(要关联的表)

如果想在user里面访问IdCard那就得在user里面添加@OneToOne装饰器，但是这时候就得添加第二个参数。

@OneToOne(()=>Idcard,
//为什么需要这个参数呢？因为如果是维护外键的那个表（也就是有@JoinColumn的那
//个Entity）,它是可以根据外键查询到另一方的，但是没有的话，就需要这个第二个参数。
(idCard=>idCard.user))
idCard:IdCard
  • @ManyToOne()

因为一对多需要在多的那一边保持关系关系，所以并不需要@JoinColumn,正是因为在多的那一边维护，所以在一的那一边要查询关于多的那一边，就得传入第二个参数。要注意一点，只能在一方设置cascade，不然会造成无限循环。

  • @ManyToMany
//在里面给中间表起名字，@JoinTable({name:user_role_relation})
@JoinTable()
@ManyToMany(()=>Tag)
//多的那一方都会有这个
tags:Tag[]

多对多的时候，由于双方都不维护外键，这就需要双方都要添加第二个参数，指定外键在哪里.

avatar
文章
阅读
粉丝