Quickly filter by multiple values: SELECT * FROM staff WHERE dept IN ('HR', 'Marketing') Create & Remove Index SELECT * FROM staff WHERE dept LIKE '%i%' Not Likeįind rows where the department name doesn't start with 'd': SELECT * FROM staff WHERE dept NOT LIKE 'de%' IN SELECT * FROM staff WHERE dept LIKE '%r' SELECT * FROM staff WHERE dept LIKE 'des%' Search for departments that start with a 'd' or 'dev', end with a 't', or have an 'e' somewhere: SELECT * FROM staff WHERE dept LIKE 'de%' 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. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |