Mysql create table integer column1/16/2024 ![]() Viewing the data-type for the userid column we notice that is shows as tinyint(3). The output is much more verbose than the DESCRIBE command as seen in the following screen capture: We can see the out put from the screen capture below:įor a complete listing of the table schema and options that were used including the inherited defaults we can use the code: To view the table schema we can simply use the SQL command : To add to our row data we also have a column for the username, this can be character data up to 50 characters in length, varchar(50) as we do not mention null-ability it will default to NULL, allowing empty values for usernames. Each user can be identified by this the userid. This enforces the uniqueness of the userid value stored in the users table. PRIMARY KEY : We add a primary key constraint the the userid column meaning the each values entered into this column in different rows or entries must contain unique values.NOT NULL : Here we set the null-ability of the column and we disallow NULL or empty values.A tiny integer using 1 byte (8 bits) of storage and can be any whole number from: Unsigned means that we can only have positive data values as we don’t allow for storage of the signing +/1 character. TINYINT UNSIGNED : This sets the data-type to be an unsigned tiny integer.If we look a little closer we can see that the userid column has been defined as: The two columns that we are defining have the column names of: Userid TINYINT UNSIGNED NOT NULL PRIMARY KEY, In continuing with the table creation we need to open are parentheses to define the columns and their options, each column is separated from the next with a comma. Note that the quotes used are the back-tick and not a single quote. When using databasename.tablename format the identifiers are quoted and not the dot: The quotes we use are back-ticks and we use these object names or identifiers match system names or hold special characters. Quoted identifiers are just the posh names for object names, identifiers that are within quotes. Where possible if we keep object names to use standard characters and do not use system names in the object names then we will not need to use quoted identifiers. If our current database context was something other than the database tup we could use code starting as this: We could then create the users table within the tup database using code that starts: ![]() ![]() To enter a database named tup and make that database out current context we can use the SQL command: If we are in the same database that we want the tale created in then we can refer to the table directly using the table name only, if we are not in the database that we wish the tab le to be in we refer to the database.tablename. To create the sample table it is a simple matter of defining the table and the columns that we wish. For the purpose of this exercise we will be looking at creating a simple two column table, we are more concerned for the exercise the values that can be used for a specific Integer data-type rather than any longevity of the data being stored. In this section we will look at the use of Integer values and the use of signed and unsigned values. When creating tables within MySQL databases we have to consider the data type that we use for each column. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |