linux回炉第二十一天

show databases;
SELECT version();
SELECT database();
select user,host from mysql.user;
use mysql;
show tables;
help
help contents;
show character set;
show charset;
show variables like 'character%';
show collation;
show collation like 'utf8%';

help create
help create database

create database db1;
create database IF NOT EXISTS db1;

show warinings;
show create database db1;

create database IF NOT EXISTS db3 CHARACTER SET 'utf8';

alter database db1 character set 'utf8mb4';

drop database db3;

help create table
create table student (id int unsigned auto_increment primary key, name varchar(20) not null,age tinyint unsigned, gender enum('M','F') default 'M')engine=InnoDB auto_increment=10 default charset=utf8;
desc student;
show tables;
select * from student;
insert student (name,age)values('wang',36);

create table teacher like student;
show table status like 'student'\G

show engines;
show tables from mysql;
show create table student;
show columns from student;
show table status;

alter table student add phone char(11) after name;

drop table teacher;

insert into students (name,age,classid) values('wanghehe',20,3);
insert into students values(26,'wanghaha',18,'M',1,null);
insert students (name,age,classid) values('lier',18,2),('eryou',28,3);

help update;
update students set age=28,gender='M' where name='lier';

help delete;
delete from students where name='liyou';

help select
select stuid,name,gender from students where gender='F';

select stuid,name,age from students where age < 30;
select stuid,name,age from students where age >= 18 and age < 30;
select stuid,name,age from students where age between 18 and 20;
select stuid,name,age from students where name like 'zhang%';
select stuid,name,age from students where name like '%zheng%';
select age from students;
select distinct age from students;
select distinct age from students order by age;
select distinct age from students order by age desc;
select gender,avg(age) from students group by gender;
select gender,max(stuid),avg(age) from students group by gender;
select gender,max(stuid),avg(age) from students group by gender having gender='M';
select gender,max(stuid),avg(age) from students where gender='M' group by gender;
select classid,gender,max(age) from students group by gender,classid order by classid;

select * from students where age > (select avg(age) from teachers);

select stuid,name,age from students union select tid,name,age from teachers;

select * from students cross join teachers;  #慎用

select * from students inner join teachers on students.gender=teachers.gender;
select * from students inner join teachers on students.teacherid=teachers.tid;
select stuid,students.name,tid,teachers.name from students inner join teachers on students.teacherid=teachers.tid;
select stuid,s.name,tid,t.name from students s inner join teachers t on s.teacherid=t.tid;

selectl * from students left outer join students.teacherid=teachers.tid;

select * from students right outer join teachers on students.teacherid=teachers.tid union select * from students left outer join teachers on students.teacherid=teachers.tid;

select count(*) from students;
show processlist;