![]() ![]() It fetches rows where the age is between 18 and 35. How about filtering users within a specific age range? SELECT * FROM staff WHERE age BETWEEN 18 AND 35 This weeds out duplicates and shows unique cities only. Tired of duplicate locations in your data? SELECT DISTINCT city FROM staff This combines the first and last names into a new column called FullName. Need to merge columns? Say you want to combine first_name and last_name: SELECT CONCAT(first_name, ' ', last_name) AS 'FullName', dept FROM staff SELECT * FROM members ORDER BY last DESC Concatenate Columns View sorted data in ascending or descending order: SELECT * FROM members ORDER BY last ASC If you want the age to be an integer, change it like this: ALTER TABLE members MODIFY COLUMN member_age INT(3) Sort Data with Order By Want to store ages? Add an 'age' column: ALTER TABLE members ADD member_age VARCHAR(3) Modify Existing Column Need to remove a user? Use their ID: DELETE FROM members WHERE member_id = 4 Ĭhange an email address for a specific user ID: UPDATE members SET user_email = WHERE member_id = 1 Add a New Column You get rows where the location is Boston, and possibly further filter by the 'Tech' department. SELECT * FROM members WHERE city='Boston' AND department='Tech' Filter Data with Where Clauseįind users based on location or department like so: SELECT * FROM members WHERE city='Boston' You either get all data or just the first and last names. Retrieve all user data, or just some columns: SELECT * FROM members Insert Multiple RowsĪdd more users in one go like this: INSERT INTO members (first, last, user_email, pass_hash, city, department, admin_flag, date_joined) VALUES You just added a user named Jane Doe, among other details. Populate your 'members' table with this: INSERT INTO members (first, last, user_email, pass_hash, city, department, admin_flag, date_joined) VALUES ('Jane', 'Doe', 'pass123', 'Boston', 'Tech', 1, NOW()) You'll see a list of every table in the currently selected database. Wonder what tables you've got? This shows them: SHOW TABLES ![]() The table named 'some_table' will vanish, along with all its data. Need to delete a table? It's easy, but be careful: DROP TABLE some_table This sets up a table named 'members' with various fields like first, last, user_email, and so on. Want to set up a new table for user data? Here's how: CREATE TABLE members ( Now you'll be operating within 'my_new_db,' ready to manage its tables and data. To work with a specific database, type: USE my_new_db Need to get rid of a database? Use: DROP DATABASE my_new_db īe careful with this one it will completely delete the database named 'my_new_db.' Switch to a Different Database This creates a database called 'my_new_db,' which you can then populate with tables and data. To build a new database, run: CREATE DATABASE my_new_db This command reveals all the databases currently set up on your MySQL server. ![]() Want to see what databases exist? Go with: SHOW DATABASES If you need to remove a user completely, you'll use: DROP USER wipes the user from the MySQL server, so make sure you really want them gone. To pull back permissions from a user, you can run: REVOKE ALL, GRANT OPTION FROM command will strip 'newuser' of all special access. This allows 'newuser' to do pretty much anything-so be careful who you grant this power to! View User PrivilegesĬurious about what access a specific user has? Just type: SHOW GRANTS FOR lists all the permissions assigned to 'newuser.' Revoke User Privileges To give a user full reign over all databases, you'd use: GRANT ALL ON *.* TO PRIVILEGES Just swap out 'newuser' and 'newpassword' with your chosen username and password. This sets up a new MySQL user with login credentials. If you want to add a new user, run: CREATE USER IDENTIFIED BY 'newpassword' This will reveal which users have access to your MySQL server, as well as their hostnames. ![]() To see a list of all MySQL users, execute: SELECT Username, Server FROM er Note: Replace your_username with your actual MySQL username. This command prompts you to enter your password, giving you a backstage pass to your databases. To gain access to your MySQL server, you'll use: mysql -u your_username -p New Table With 2 Foreign Keys (Replies). ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |