mysql打卡

systemctl status mysqld;

systemctl start mysqld;

mysql -u root -p

show databases;

create database istester;

create database istester2;

show databases;

drop database istester2;

show databases;

use istester;

create table istester (

id int (10) not null unique primary  key,

uname varchar(20) not null,

sex varchar(4),

birth year,

department  varchar(20),

address varchar(50),

idoxu varchar(20)

）；

create table idoxu(

id int(10) not null unique primary key auto_increment,

stu_id int(10) not null,

c_name varchar(20),

istester varchar(20),

grade int(10)

);

create table istester2 like istester;

create table idoxu2 as select * from idoxu where1=2;

create table idoxu2 as select id,stu_id,istester from idoxu where1<>1;

drop table istester2;

drop table idoxu,idoxu2,istester,istester99;

alter table istester add column istester6 varchar(20) not null;

alter table istester add primary key(idoxu);

alter table istester drop primary key (idoxu);

insert into istester (id,uname,idoxu)values (1,'idoxu',2020);

insert into istester(id,uname,idoxu)values(2,'idoxu2',2020);

intsert into istester (id,sex,idoxu) values(11,11,2020),(12,2,2020),(13,2,2020),(14,1,2020);

select *from istester;

select *from isterster where id=1;

update  istester set uname='idoxu666'where id=12;

select *from istester where uname like '%idoxu%';

select *from istester order by id desc;

select count(id) as totalcount from istester;

select sum(grade)from idoxu;

select avg(id) as avgvalue from istester;

select max(id) as 'maxvalue' from istester;

select min(id) as 'minvalue' from istester;

select *from istester where uname like'%ido%' order by id desc limit 10;

select *from istester t where t.name like'%ido%'order by id desc limit 10;

select *from istester.idoxu s where s.stu_id like'%2%';

insert into istester2 select *from istester;

insert into idoxu(id,stu_id,grade)select id,idoxu from istester;

alter table idoxu3 rename to idoxu6;

alter table istester change uname aname varchar(60);

create table istester10 like istester.idoxu;

select *from idoxu where grade between 1 and 30;

select id,stu_id,c_name,grade from idoxu where not between 1and 30;

select id ,stu_id,c_name,grade from idoxu where grade in(10,20,30);

select id,stu_id.c_name,grade from idoxu where grade not in 10,20,90);

select id,stu_id,c_name,grade from idoxu where id in (select id from istester);

select i.id,i.stu_id,i.grade from idoxu i left join isterster on i.id=t.id;

select i.id,i.stu_id,i.grade from idoxui right  join isterster on i.id=t.id;

select i.id,i.stu_id,i.grade from idoxui inner join isterster on i.id=t.id;

select *from (select id,stu_id,c_uname,grade from idoxu) d where d.id>10;

select i.id i.stu_id.i.c_uname,i.grade from idoxu i left join istester t on i.id-t.id right join istester2 c on i.id=c.id inner join idoxu2 d on i.id=d.id where 1=1;

select d.* from (select d.id,d.stu_id,d.c_uname,d.grade from idoxu order by grade desc limit 10) i,idoxu d where i.id=d.id order by stu_id desc limit 10;

select *from (select * from idoxu order by id asc limit 15) i order by id desc limit 5;

create view istester_view as select id ,stu_id,c-uname,grade from  idoxu where id in (select id from istester);

drop view;