数据库语句

show databases;
create database lyh;
drop database lyh;
use lcc;
show tables;

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table lcc(
     lcc_id int unsigned auto_increment,
     lcc_title varchar(100) not null,
     lcc_author varchar(40) not null,
     primary key(lcc_id)
     )engine=innodb default charset=utf8;


drop table runoob_tbl;

insert into runoob_tbl(runoob_title, runoob_author, submission_date)values("lichuachua","lcc",now());
insert into runoob_tbl(runoob_title, runoob_author, submission_date)values("liyuhao","lyh",now()),("renyu","ry",now());
insert into lcc values(1,"liyuhao","lyh"),(2,"renyu","ry");

select * from runoob_tbl;
select * from runoob_tbl where runoob_id>1;

select * from runoob_tbl where runoob_title like '%yu%';
select * from runoob_tbl where runoob_title like '___yu';
select * from runoob_tbl where runoob_title like '%yu%' and runoob_author like '%v_';

select * from runoob_tbl where runoob_id = 2 union select *from runoob_tbl where runoob_id = 3;

select * from runoob_tbl order by runoob_id desc;
select * from runoob_tbl where runoob_id >2 order by runoob_id desc;

select runoob_title,count(*) from runoob_tbl group by runoob_author;
select runoob_title,sum(runoob_id) from runoob_tbl group by runoob_author;

select a.runoob_id, a.runoob_author ,b.lcc_id,b.lcc_author from runoob_tbl a inner join lcc b on a.runoob_title=b.lcc_title;
select a.runoob_id, a.runoob_author ,b.lcc_id,b.lcc_author from runoob_tbl a ,lcc b where  a.runoob_title=b.lcc_title;
select a.runoob_id, a.runoob_author ,b.lcc_id,b.lcc_author from runoob_tbl a left join lcc b on a.runoob_title=b.lcc_title;
select a.runoob_id, a.runoob_author ,b.lcc_id,b.lcc_author from runoob_tbl a right join lcc b on a.runoob_title=b.lcc_title;


update runoob_tbl set runoob_author='love' where runoob_id=3;

delete from runoob_tbl where runoob_author='lcc';



desc lcc;

alter table runoob_tbl rename to lyh;
alter table lyh change runoob_id lyh_id int(10);
alter table lcc add lcc_date date;
alter table lcc drop lcc_date;

create index lcc_index on lcc(lcc_id);
drop index lcc_index on lcc;