How to lower case a column in mysql
update cityfn set city = LOWER(city);
How to remove characters or string from a mysql table
update tablename set column_name = replace(column_name, ‘find string to replace’, ‘replace with this string’);
Forr example, if you have asterisks in your data you want to delete:
update cities set city = replace(city, ‘*’, ”);
How to copy records back into original table
insert into oldtable select * from newtable;
how to delete duplicate rows in mysql
create table newtable asselect * from oldtable where 1 group by [specific column];
The Database Design Process
Designing a database properly is crucial to developing a strong and efficient information system. Proper planning and design not safeguards against a broad range of problems.
A poorly designed database can cause data to become inaccurate and unreliable. Performance can also take a hit and the database may not function properly. A properly designed database is [...]
How to use Load Data statement in MySQL
Below is a simple example of using a Load Data Local Infile statement in MySQL.
LOAD DATA LOCAL INFILE ‘/sql/products-test.txt’ IGNORE
INTO TABLE old_products
FIELDS TERMINATED BY ‘ ’
LINES TERMINATED BY ‘\n’
(productID, title, description, vendor, domain, media, catalog);
How to Create a Table in SQL
Writing a create table statement in SQL is very straighforward. However, before you can create a table, you need to make sure you have already created your database.
The following is a statement that would create a table named products:
// products table
create table products (
productID varchar(20),
title varchar(100),
description mediumtext,
vendor varchar(100),
domain varchar(100),
media varchar(255),
catalog varchar(200)
);
Each create table statement in [...]
Redirecting a MySQL command to a file
At the Mysql prompt type:
mysql> tee tempfile.txt;
mysql> show tables;
mysql> \t;
using the mysqldump command
Using mysqldump in a mysql database:
You run mysqldump from the Unix / Linux shell command line and not within mysql.
The command below simply dumps the entire mysql database to a file named backupfile.sql
mysqldump –user=username–password=password database-name > backupfile.sql
This command adds the option of a drop table command before creating the tables and limits to copying only [...]
MySQL Basic Commands
Logging into mysql
You can use your server’s main control panel login to log in to mysql
$ mysql -u username -p
(enter your password when prompted)
You know you are logged in when the prompt changes to:
mysql>
The following are some very basic beginning commands used in MySQL:
Use database-name
show tables
desc table-name
