123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136 |
- # show engines
- show engines;
- # create database
- create database database_name;
- # use database
- use database_name;
- # create table
- create table table_name(fname1 datatype, fname2 datatype) engine=engine_name;
- # unsigned integer type
- create table table_name(fname1 datatype, fname2 datatype unsigned) engine=engine_name;
- # insert data into table
- insert into table_name(value1, value2)
- # data types: mysql supports Numeric, String, Date & time
- # integer
- data type bytes minimum value maximum value
- tinyint 1 -128 127
- smallint 2 -32768 32767
- mediumint 3 -8388608 8388607
- integer 4 -2147483648 2147483647
- bigint 8 -9223372036854775808 9223372036854775807
- # float - single precision floating poing number (4 bytes)
- # double - double precision floating point number(8 bytes)
- # decimal
- in decimal[M, D] the M is the maximum number of digits, the precision. The D is the
- number of digits to the right of the decimal point. It is the scale. If you have a
- column with decimal(3, 1), you can insert numbers with maximum of three digits: two
- before and one after the decimal point.
- create table Numbers(fname1 tinyint, fname2 float, fname3 decimal(3,2));
- insert into Numbers values (1, 1.1, 1.1), (2, 1.1, 1.1), (3, 1.1, 1.1);
- select * from Numbers;
- select sum(fname2), sum(fname3) from Numbers;
- # Date & Time Values
- mysql has data types for storing dates and times: date, time, datetime, year, and timestamp
- date(YYYY-MM-DD) - 1000-01-01 to 9999-12-31
- select curdate();
- select date('2017-01-31 12:01:00');
- select adddate('2017-01-20', 8);
- create table <table_name> (Id tinyint, Date_of_Admission date);
- # time
- select curtime(); // display current time
- # create table Persons
- create table Persons(PID smallint unsigned auto_increment primary key, Last_Name varchar(15) not null, First_Name varchar(15) not null, Age tinyint unsigned not null);
- describe Persons;
- insert into Persons (Last_Name, First_Name, Age) values ('Hansen', 'Ola', 20), ('Svendson', 'Ola', 23), ('Pettersen', 'Kari', 20);
- select * from Persons;
- # create table Orders
- create table Orders(OID smallint unsigned auto_increment primary key, Order_Number integer not null, Person_ID smallint unsigned not null references Persons(PID));
- describe Orders;
- insert into Orders (Order_Number, Person_ID) values (77895, 3), (44678, 3), (22456, 2), (24562, 1);
- select * from Orders;
- # in
- select * from Orders where Order_Number in (22456, 24562);
- # like
- select * from Orders where Order_Number like '2%';
- select * from Orders where Order_Number like '_____'; // five characters
- # between
- select * from Orders where Order_Number between 30000 and 50000;
- # count
- select count(column_name) from table_name where condition;
- select count(Order_Number) from Orders;
- # average
- select avg(column_name) from table_name where condition;
- select avg(Order_Number) from Orders;
- # sum
- select sum(column_name) from table_name where condition;
- select sum(Order_Number) from Orders;
- # maximum
- select max(column_name) from table_name where condition;
- select max(Order_Number) from Orders;
- # minimize
- select min(column_name) from table_name where condition;
- select min(Order_Number) from Orders;
- # order by
- select * from table_name order by column_name asc; // by default ascending
- select * from table_name order by column_name desc;
- select * from Orders order by Order_Number;
- # truncating table with foreign key
- set foreign_key_checks = 0;
- truncate table table_name;
- set foreign_key_checks = 1;
- # add user in mysql
- create user 'username'@'localhost' identified by "password";
- grant all privileges on *.* to 'username'@'localhost';
- flush privileges;
- #
- mysql> load data infile '/home/raman/Downloads/iris.csv'
- into table Iris
- fields terminated by ','
- enclosed by '"'
- lines terminated by '\n'
- ignore 1 rows;
- ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
- mysql> show variable like 'secure_file_priv';
- cd /etc/mysql/
- sudo vi my.cnf
|