I saw a project without much in the way of technical details where you connect an arduino to a sensor on your door and you get a text on your phone that the door opened. What is involved in this?
In the Arduino world, add-on boards are referred to as shields. There are shields for Ethernet, Wi-Fi, GSM cellular, LoRA, etc. to enable communication via these methods.
First you would need a chip with a radio for wifi. Then you would need to connect to that something that can do ip? Or is that an all in one device?
Typically, the shield would have support for the required protocols, thereby reducing the amount of RAM needed by the microcontroller.
Then that somehow communicates with the Arduino that sends data to it that its IO line has changed?
Software libraries (from the Arduino project or third-party) enable you to interact with the shield to send/receive data.
What's the protocol for the Arduino to communicate with outside devices can it handle ethernet or USB?
Yes, just buy or build the appropriate shield for the type of communication you desire. Alternatively, look for an Arduino-compatible board with what you need already built onboard.
I always try to explain to people that an Arduino can really do anything but how and the limitations of a microcontroller vs a raspberry pi baffle me. What kinds of computer stuff can it do?
Note that Arduino is a platform (hardware and software specifications/standards) and there are many boards that are compatible. The processor on the boards vary and, hence, their capabilities can vary quite a bit as well.
Typically, microcontrollers have very little memory and less processing capability as compared to microprocessor-based systems. So, although microcontrollers can do many things, they can't do everything. A Raspberry Pi uses a microprocessor, has much more memory, and typically runs a desktop or server operating system. It's essentially a computer on a PCB, which is very different (significantly more powerful) from a microcontroller.
Microcontrollers are good for small, simple tasks that may be time-sensitive.
Microprocessors are good for large, computationally intensive tasks that aren't highly time-sensitive.
Very vague but still working gout the differences in a micro vs FPGA which really can do anything within limits of how many arrays it has. Do all micros fit some sort of standard that sets them apart from asic's? I know can it can do more then then: this input is low so set this output high. How much can it think or can't it and it's just a glorified set of relays that react to inputs?
ASIC: Application-specific integrated circuit. It can contain whatever is needed to accomplish a computational task such as processors or various kinds of memory. It's manufactured in a factory and cannot be altered thereafter.
FPGA: Field-programmable gate array. It can be thought of as an ASIC that can be changed after it's manufactured. It's between a microprocessor (very flexible and general purpose as it's primarily software driven) and an ASIC (fixed to the purpose it was initially made for, purely hardware).
A microcontroller or microprocessor can be implemented in an ASIC. Add the RAM, ROM, and other support hardware into the ASIC and you get an SoC (System on Chip).
If an Arduino was a computer would it be like a commodore 64 needing a video card?
Yes, an Arduino board would be like the C64's motherboard, only much more compact due to modern electronics. In fact, one could make a rudimentary computer with an Arduino board like this
https://makezine.com/2014/11/13/an-arduino-based-computer/