First, you should decide 2D (sprites) or 3D (models). Both can be obtained from the Unity Asset Store.
Some other sites for mostly 2D, but also some 3D assets, a lot are free:
- itch io
- OpenGameArt
- kenny.nl
For custom content, you will have to either collaborate with artists to create the art for you, or learn the respective workflows and software.
For 2D, software like Photoshop or Gimp can be used. Most game’s are created with “pixel” software, but it’s also possible to use vector-based drawings created in Adobe Illustrator and convert them to bitmap/pixel images or use packages to render them directly in Unity. Regular bitmaps are usually png or tga, while vector formats are svg or eps.
For 3D, most people are using the modelling software Blender, because it’s completely free. There’s also industry-standard software like Maya or 3ds Max, which is free for students. When creating 3D models, you not only create the meshes, but also 2D textures to apply to them, potentially also animations. The final models, textures and animations are then exported, e.g. as FBX.
Some people see themselves more as game designers and just want to prototype their ideas by themselves before involving artists. For this kind of work there are some create tools like:
- Asset Forge (by Kenney)
- Figuro
- Sketchup
My lists are far from exhaustive, but you’ll be able to find a lot more information by looking into the respective areas: 2D drawing software, 3D modelling software, game model prototyping.
Have fun and don’t feel intimidated by the vast amounts of choices, we’re all in the same dilemma!