一些简单的mysql SQL语句备忘

放在这里备忘,老是忘记怎么写。

 

mysql> create database book; mysql> use book; Database changed mysql> create table email_message(key_mail_messages INTEGER, -> date_created VARCHAR(19), -> date_updated varchar(19), -> date_email varchar(19), -> addr_from varchar(100), -> addr_reply_to varchar(100), -> subject varchar(100), -> message mediumtext); mysql> describe email_message; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | key_mail_messages | int(11) | YES | | NULL | | | date_created | varchar(19) | YES | | NULL | | | date_updated | varchar(19) | YES | | NULL | | | date_email | varchar(19) | YES | | NULL | | | addr_from | varchar(100) | YES | | NULL | | | addr_reply_to | varchar(100) | YES | | NULL | | | subject | varchar(100) | YES | | NULL | | | message | mediumtext | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ mysql> alter table email_message add column temp_field integer; mysql> describe email_message; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | key_mail_messages | int(11) | YES | | NULL | | | date_created | varchar(19) | YES | | NULL | | | date_updated | varchar(19) | YES | | NULL | | | date_email | varchar(19) | YES | | NULL | | | addr_from | varchar(100) | YES | | NULL | | | addr_reply_to | varchar(100) | YES | | NULL | | | subject | varchar(100) | YES | | NULL | | | message | mediumtext | YES | | NULL | | | temp_field | int(11) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ mysql> alter table email_message change column temp_field temp_field varchar(25); mysql> describe email_message; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | key_mail_messages | int(11) | YES | | NULL | | | date_created | varchar(19) | YES | | NULL | | | date_updated | varchar(19) | YES | | NULL | | | date_email | varchar(19) | YES | | NULL | | | addr_from | varchar(100) | YES | | NULL | | | addr_reply_to | varchar(100) | YES | | NULL | | | subject | varchar(100) | YES | | NULL | | | message | mediumtext | YES | | NULL | | | temp_field | varchar(25) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ mysql> alter table email_message drop column temp_field; mysql> describe email_message; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | key_mail_messages | int(11) | YES | | NULL | | | date_created | varchar(19) | YES | | NULL | | | date_updated | varchar(19) | YES | | NULL | | | date_email | varchar(19) | YES | | NULL | | | addr_from | varchar(100) | YES | | NULL | | | addr_reply_to | varchar(100) | YES | | NULL | | | subject | varchar(100) | YES | | NULL | | | message | mediumtext | YES | | NULL | | +-------------------+--------------+------+-----+---------+------- mysql> create table employee( firstname varchar(20), -> lastname varchar(20), -> age integer); mysql> insert into employee (firstname,lastname,age) values ('Florence','Alexander',86); mysql> insert into employee (firstname,lastname,age) values ('Charles','Berlowitz',1); mysql> insert into employee (firstname,lastname,age) values ('Charles','Brown',10); mysql> insert into employee (firstname,lastname,age) values ('Florence','Longstein',8); mysql> insert into employee (firstname,lastname,age) values ('Florence','Henderson',64); mysql> select firstname,age from employee; +-----------+------+ | firstname | age | +-----------+------+ | Florence | 86 | | Charles | 1 | | Charles | 10 | | Florence | 8 | | Florence | 64 | +-----------+------+ mysql> select concat(concat(concat(concat(concat(concat( -> '<option value=''',age),'''>') -> ,firstname) -> ,' ') -> ,lastname) -> ,'</option>') -> from employee; +--------------------------------------------------------------------- mysql> select firstname,age from employee where upper(firstname) like 'F%'; +-----------+------+ | firstname | age | +-----------+------+ | Florence | 86 | | Florence | 8 | | Florence | 64 | +-----------+------+ mysql> select firstname,age from employee where upper(firstname) like 'F%'or upper(firstname) like 'C%'; +-----------+------+ | firstname | age | +-----------+------+ | Florence | 86 | | Charles | 1 | | Charles | 10 | | Florence | 8 | | Florence | 64 | +-----------+------+ mysql> select firstname,age from employee where upper(firstname) like 'F%' and age > 65; +-----------+------+ | firstname | age | +-----------+------+ | Florence | 86 | +-----------+------+ mysql> select * from employee order by firstname; +-----------+-----------+------+ | firstname | lastname | age | +-----------+-----------+------+ | Charles | Berlowitz | 1 | | Charles | Brown | 10 | | Florence | Alexander | 86 | | Florence | Longstein | 8 | | Florence | Henderson | 64 | +-----------+-----------+------+ mysql> select * from employee order by firstname desc; +-----------+-----------+------+ | firstname | lastname | age | +-----------+-----------+------+ | Florence | Alexander | 86 | | Florence | Longstein | 8 | | Florence | Henderson | 64 | | Charles | Berlowitz | 1 | | Charles | Brown | 10 | +-----------+-----------+------+ mysql> select *,count(*) from employee group by firstname; +-----------+-----------+------+----------+ | firstname | lastname | age | count(*) | +-----------+-----------+------+----------+ | Charles | Berlowitz | 1 | 2 | | Florence | Alexander | 86 | 3 | +-----------+-----------+------+----------+ mysql> select *,max(age) as oldest from employee group by firstname; +-----------+-----------+------+--------+ | firstname | lastname | age | oldest | +-----------+-----------+------+--------+ | Charles | Berlowitz | 1 | 10 | | Florence | Alexander | 86 | 86 | +-----------+-----------+------+--------+ mysql> delete from employee where upper(firstname) = 'Charles'; mysql> select count(*) from employee where upper(firstname) = 'Charles'; mysql> select last_insert_id() from employee; mysql> alter table employee change column age age integer primary key; mysql> exit