1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| mysql> create table mytesttable -> ( -> rollno int, -> firstname varchar(50), -> lastname varchar(50) -> ); Query OK, 0 rows affected (0.25 sec)
or
create table mytesttable( rollno int primary key auto_increment, firstname varchar(50) not null, lastname varchar(50) not null, CreatedDTTM DATETIME DEFAULT NOW() );
SELECT CONCAT(firstname, ' ', lastname) as fullname FROM mytesttable;
-- salary numeric(10, 2) not null, -- IsDeleted bit not null default 0, -- 8 digits for integer part and 2 digits for fraction part -- varchar cost less than char
mysql> SHOW COLUMNS FROM mytesttable; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | rollno | int | YES | | NULL | | | firstname | varchar(50) | YES | | NULL | | | lastname | varchar(50) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.06 sec)
mysql> select rollno, firstname, lastname from mytesttable; Empty set (0.00 sec)
-- select distinct lastname from mytesttable; -- select sum(rollno) as s from mytesttable; -- sum(), min(), max(), avg(), count()
mysql> insert into mytesttable(rollno, firstname, lastname) values(1, 'Eric', 'Lu'); Query OK, 1 row affected (0.03 sec)
-- insert into mytesttable(rollno, firstname, lastname, CreatedDTTM) values(1, 'Eric', 'Lu', now());
mysql> select firstname from mytestdb.mytesttable; +-----------+ | firstname | +-----------+ | Eric | +-----------+ 1 row in set (0.01 sec)
-- select * from mytestdb.mytesttable order by rollno desc;
mysql> select firstname from mytestdb.mytesttable where lastname like 'hsu'; Empty set (0.01 sec)
mysql> select firstname from mytestdb.mytesttable where lastname like 'u'; Empty set (0.00 sec)
mysql> select firstname from mytestdb.mytesttable where lastname like 'Lu'; +-----------+ | firstname | +-----------+ | Eric | +-----------+ 1 row in set (0.00 sec)
mysql> insert into mytesttable(rollno, firstname, lastname) values(2, 'Shvara', 'Lunski'); Query OK, 1 row affected (0.04 sec)
mysql> insert into mytesttable(rollno, firstname, lastname) values(3, 'Lunski', 'Henry'); Query OK, 1 row affected (0.01 sec)
|