MySQL Tips

[minecraftyeet1]

Does anyone have any tips for learning mysql for a database admin job, and how long would it take?

 

e.g learning mysql to fulfill all  the roles of a typical database admin.

 

and are there any guides / walkthroughs to learn all the knowledge?

 

i'm just having trouble right now knowing when to use certain lines of codes with what e.g 

 

CREATE TABLE users(

(do i always need to create a id? e.g user_id SERIAL PRIMARY KEY)

first_name VARCHAR (How do i know to either use var char,  tinytext(200), unique, notnull? 

etc.

 

[Needfuldoer]

This classic site is still around:

https://sqlzoo.net/wiki/SQL_Tutorial

 

Oracle also has books worth of documentation for their database product:

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/introduction-to-oracle-database.html

 

And here's Microsoft's page on MSSQL (including trial downloads):

https://learn.microsoft.com/en-us/sql/?view=sql-server-ver16

 

But if you should really know this stuff and already have some experience if you're applying for database admin jobs. That's not entry-level.

[Eigenvektor]

2 hours ago, minecraftyeet1 said:

i'm just having trouble right now knowing when to use certain lines of codes with what e.g 

 

CREATE TABLE users(

(do i always need to create a id? e.g user_id SERIAL PRIMARY KEY)

first_name VARCHAR (How do i know to either use var char,  tinytext(200), unique, notnull? 

etc.

That entirely depends on the data you want to store in that table.

 

A DBA is typically more concerned with server operation, not database design.

 

What tables need to be created and what their schema should be is usually decided by the people designing the app(s) that run on top of the database server.

 

Of course it certainly doesn't hurt of you know your way around SQL as well.

 

As a rule of thumb, every table should have a primary key. But that primary key does not need to be an int. For example we use UUIDs, to ensure they are unique in a distributed context.

 

Whether a column should be non-null depends on whether it is "legal" in a business logic sense for that value to be undefined, or whether a value must always be present.

 

What data type to use depends what data you want to store. Is it a name, a number, does it have a maximum length etc.

[minecraftyeet1]

38 minutes ago, Eigenvektor said:

That entirely depends on the data you want to store in that table.

 

A DBA is typically more concerned with server operation, not database design.

 

What tables need to be created and what their schema should be is usually decided by the people designing the app(s) that run on top of the database server.

 

Of course it certainly doesn't hurt of you know your way around SQL as well.

 

As a rule of thumb, every table should have a primary key. But that primary key does not need to be an int. For example we use UUIDs, to ensure they are unique in a distributed context.

 

Whether a column should be non-null depends on whether it is "legal" in a business logic sense for that value to be undefined, or whether a value must always be present.

 

What data type to use depends what data you want to store. Is it a name, a number, does it have a maximum length etc.

So it's basically not my job to worry about that? what should I learn for the role basically

 

 

[Eigenvektor]

39 minutes ago, minecraftyeet1 said:

So it's basically not my job to worry about that? what should I learn for the role basically

As I said above, a database admin is more concerned with the operation of the database.

 

Ensure there are regular backups, verify that they can be restored, scheduled maintenance (vacuum/optimize table) and also security (i.e. who should have access to what)

[minecraftyeet1]

9 hours ago, Eigenvektor said:

As I said above, a database admin is more concerned with the operation of the database.

 

Ensure there are regular backups, verify that they can be restored, scheduled maintenance (vacuum/optimize table) and also security (i.e. who should have access to what)

Thanks, how can I learn about these, do you have any websites or places?

 

[saintlouisbagels]

5 hours ago, minecraftyeet1 said:

Thanks, how can I learn about these, do you have any websites or places?

There are plenty of free online resources.

 

I'm in my first database class (Intro to Structured Database Environments) right now for my Computer Science major, and I genuinely thought you were someone in my class asking this forum for help on the first homework assignment, because we literally passed in this assignment this past Sunday night

22 hours ago, minecraftyeet1 said:

CREATE TABLE users(

(do i always need to create a id? e.g user_id SERIAL PRIMARY KEY)

first_name VARCHAR (How do i know to either use var char,  tinytext(200), unique, notnull? 

etc.

We're only 2 weeks into class (8 weeks total) and these have been the 2 resources so far
https://www.mysqltutorial.org/mysql-basics/
https://www.khanacademy.org/computing/computer-programming/sql/sql-basics/v/welcome-to-sql

[Eigenvektor]

8 hours ago, minecraftyeet1 said:

Thanks, how can I learn about these, do you have any websites or places?

Sorry, I don't. While I need to work with databases a lot in my role as a developer, I have no real experience with the dba side of things other than a rough idea what their job is.

 

A starting point might be the online documentation of MariaDB. Specifically about things like server configuration and monitoring.

[minecraftyeet1]

15 hours ago, Eigenvektor said:

Sorry, I don't. While I need to work with databases a lot in my role as a developer, I have no real experience with the dba side of things other than a rough idea what their job is.

 

A starting point might be the online documentation of MariaDB. Specifically about things like server configuration and monitoring.

Thanks. What do you do in your role?

19 hours ago, saintlouisbagels said:

There are plenty of free online resources.

 

I'm in my first database class (Intro to Structured Database Environments) right now for my Computer Science major, and I genuinely thought you were someone in my class asking this forum for help on the first homework assignment, because we literally passed in this assignment this past Sunday night

We're only 2 weeks into class (8 weeks total) and these have been the 2 resources so far
https://www.mysqltutorial.org/mysql-basics/
https://www.khanacademy.org/computing/computer-programming/sql/sql-basics/v/welcome-to-sql

Thanks.  I might be

[minecraftyeet1]

19 hours ago, saintlouisbagels said:

There are plenty of free online resources.

 

I'm in my first database class (Intro to Structured Database Environments) right now for my Computer Science major, and I genuinely thought you were someone in my class asking this forum for help on the first homework assignment, because we literally passed in this assignment this past Sunday night

We're only 2 weeks into class (8 weeks total) and these have been the 2 resources so far
https://www.mysqltutorial.org/mysql-basics/
https://www.khanacademy.org/computing/computer-programming/sql/sql-basics/v/welcome-to-sql

This video may help.

 

[minecraftyeet1]

bump