Scribd Logo
Upload

Welcome to Scribd!

  • 65 views

    Handy Mysql Commands Description Command

    Uploaded by

    Shanju Amath
    This document provides useful MySQL commands for logging in, creating and managing databases and tables, inserting and modifying data, joining tables, setting permissions, backing up and restoring data, and examples for creating tables. Key commands covered include selecting, inserting, updating, and deleting data; creating, dropping, and describing databases and tables; loading data from files; and dumping databases or tables for backup. Common tasks like filtering results, sorting data, and counting rows are also demonstrated through example MySQL queries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Handy Mysql Commands Description Command

    Uploaded by

    Shanju Amath
    65 views3 pages
    This document provides useful MySQL commands for logging in, creating and managing databases and tables, inserting and modifying data, joining tables, setting permissions, backing up and restoring data, and examples for creating tables. Key commands covered include selecting, inserting, updating, and deleting data; creating, dropping, and describing databases and tables; loading data from files; and dumping databases or tables for backup. Common tasks like filtering results, sorting data, and counting rows are also demonstrated through example MySQL queries.

    Original Description:

    Mysql

    Original Title

    MySQL Commands

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides useful MySQL commands for logging in, creating and managing databases and tables, inserting and modifying data, joining tables, setting permissions, backing up and restoring data, and examples for creating tables. Key commands covered include selecting, inserting, updating, and deleting data; creating, dropping, and describing databases and tables; loading data from files; and dumping databases or tables for backup. Common tasks like filtering results, sorting data, and counting rows are also demonstrated through example MySQL queries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    65 views3 pages

    Handy Mysql Commands Description Command

    Uploaded by

    Shanju Amath
    This document provides useful MySQL commands for logging in, creating and managing databases and tables, inserting and modifying data, joining tables, setting permissions, backing up and restoring data, and examples for creating tables. Key commands covered include selecting, inserting, updating, and deleting data; creating, dropping, and describing databases and tables; loading data from files; and dumping databases or tables for backup. Common tasks like filtering results, sorting data, and counting rows are also demonstrated through example MySQL queries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    _

    | \/ |_ _/ | / _ \| |
    | |\/| | | | \ \| | | | |
    || | | |_| | ) | |_| | |
    |_| |_|\ , | / \ \_\ |
    | /

    Handy MySQL Commands


    Description Command
    To login (from unix shell) use
    [mysql dir]/bin/mysql h hostname u root p
    h only if needed.
    Create a database on the sql
    create database [databasename]
    server.
    List all databases on the sql
    show databases
    server.
    Switch to a database. use [db name]
    To see all the tables in the db. show tables
    To see database's field
    describe [table name]
    formats.
    To delete a db. drop database [database name]
    To delete a table. drop table [table name]
    Show all data in a table. SELECT * FROM [table name]
    Returns the columns and
    column information pertaining show columns from [table name]
    to the designated table.
    Show certain selected rows
    SELECT * FROM [table name] WHERE [field name] = "whatever"
    with the value "whatever".
    Show all records containing
    SELECT * FROM [table name] WHERE name = "Bob" AND phone_number =
    the name "Bob" AND the
    '3444444'
    phone number '3444444'.
    Show all records not
    containing the name "Bob"
    SELECT * FROM [table name] WHERE name != "Bob" AND phone_number =
    AND the phone number
    '3444444' order by phone_number
    '3444444' order by the
    phone_number field.
    Show all records starting with
    SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number =
    the letters 'bob' AND the
    '3444444'
    phone number '3444444'.
    Use a regular expression to
    find records. Use "REGEXP
    BINARY" to force case SELECT * FROM [table name] WHERE rec RLIKE "^a$"
    sensitivity. This finds any
    record beginning with a.
    Show unique records. SELECT DISTINCT [column name] FROM [table name]
    Show selected records sorted
    in an ascending (asc) or SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC
    descending (desc).
    Count rows. SELECT COUNT(*) FROM [table name]
    select lookup.illustrationid, lookup.personid,person.birthday from lookup
    Join tables on common
    left join person on lookup.personid=person.personid=statement to join birthday in
    columns.
    person table with primary illustration id
    Switch to the mysql db. INSERT INTO [table name] (Host,User,Password)
    Create a new user. VALUES('%','user',PASSWORD('password'))
    Change a users password.
    [mysql dir]/bin/mysqladmin u root h hostname.blah.org p password 'newpassword'
    (from unix shell).
    Change a users password.
    SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere')
    (from MySQL prompt).
    INSERT INTO [table name]
    Switch to mysql db.Give user
    (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)
    privilages for a db.
    VALUES ('%','db','user','Y','Y','Y','Y','Y','N')
    To update info already in a UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y'
    table. where [field name] = 'user'
    Delete a row(s) from a table. DELETE from [table name] where [field name] = 'whatever'
    Update database
    FLUSH PRIVILEGES
    permissions/privilages.
    Delete a column. alter table [table name] drop column [column name]
    Add a new column to db. alter table [table name] add column [new column name] varchar (20)
    Change column name. alter table [table name] change [old column name] [new column name] varchar (50)
    Make a unique column so you
    alter table [table name] add unique ([column name])
    get no dupes.
    Make a column bigger. alter table [table name] modify [column name] VARCHAR(3)
    Delete unique from table. alter table [table name] drop index [colmn name]
    LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name]
    Load a CSV file into a table.
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3)
    Dump all databases for
    backup. Backup file is sql [mysql dir]/bin/mysqldump u root ppassword opt >/tmp/alldatabases.sql
    commands to recreate all db's.
    Dump one database for [mysql dir]/bin/mysqldump u username ppassword databases databasename
    backup. >/tmp/databasename.sql
    [mysql dir]/bin/mysqldump c u username ppassword databasename tablename >
    Dump a table from a database.
    /tmp/databasename.tablename.sql
    Restore database (or database
    [mysql dir]/bin/mysql u username ppassword databasename < /tmp/databasename.sql
    table) from backup.
    CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial
    VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),
    Create Table Example 1. officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email
    VARCHAR(35),phone VARCHAR(25), groups
    VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255))
    create table [table name] (personid int(50) not null auto_increment primary
    Create Table Example 2. key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default
    'bato')

    You might also like