MySQL的一些指令操作[简版]

<div class="cnblogs_code">

 apt-get  mysql-| /==( ,name ( add  dct_name (alter table <span style="color: #0000ff">mv modify name varchar(<span style="color: #800080">20<span style="color: #000000">);

alter table <span style="color: #0000ff">mv change <span style="color: #0000ff">id<span style="color: #000000"> id_num smallint;

<span style="color: #0000ff">select * from <span style="color: #0000ff">mv<span style="color: #000000">;

insert into <span style="color: #0000ff">mv (<span style="color: #0000ff">id,name) values(<span style="color: #800080">1,<span style="color: #800000">"<span style="color: #800000">Fast and Furious<span style="color: #800000">"<span style="color: #000000">);

insert into <span style="color: #0000ff">mv values(<span style="color: #800080">2,<span style="color: #800000">"<span style="color: #800000">The Zootopia<span style="color: #800000">",<span style="color: #800080">2018),(<span style="color: #800080">3,<span style="color: #800000">"<span style="color: #800000">The Gifts<span style="color: #800000">",<span style="color: #800080">2019<span style="color: #000000">);

update <span style="color: #0000ff">mv set yr=<span style="color: #800000">'<span style="color: #800000">2018<span style="color: #800000">'<span style="color: #000000">;

truncate <span style="color: #0000ff">mv<span style="color: #000000">;

delete from <span style="color: #0000ff">mv<span style="color: #000000">;

delete from <span style="color: #0000ff">mv where yr=<span style="color: #800080">2017<span style="color: #000000">;

create table t_pk(<span style="color: #0000ff">id <span style="color: #0000ff">int primary key,name <span style="color: #0000ff">char(<span style="color: #800080">10<span style="color: #000000">));

create table t_atp(<span style="color: #0000ff">id <span style="color: #0000ff">int atuo_increment primary key,name <span style="color: #0000ff">char(<span style="color: #800080">10<span style="color: #000000">));

create table t_un(<span style="color: #0000ff">id <span style="color: #0000ff">int unique,name <span style="color: #0000ff">char(<span style="color: #800080">10<span style="color: #000000">));

create table t_nn(<span style="color: #0000ff">id <span style="color: #0000ff">int,name varchar(<span style="color: #800080">10) not <span style="color: #0000ff">null<span style="color: #000000">);

create table t_dft(<span style="color: #0000ff">id <span style="color: #0000ff">int,name varchar(<span style="color: #800080">10) default <span style="color: #800000">"<span style="color: #800000">WW<span style="color: #800000">"<span style="color: #000000">);
create table t_f(<span style="color: #0000ff">id <span style="color: #0000ff">int primary key,name <span style="color: #0000ff">char(<span style="color: #800080">10<span style="color: #000000">));

create table t_s(<span style="color: #0000ff">id <span style="color: #0000ff">int primary ket auto_increment,name <span style="color: #0000ff">char(<span style="color: #800080">10),fid <span style="color: #0000ff">int,foreign key(fid) references t_f(<span style="color: #0000ff">id<span style="color: #000000">));

drop table t_s;

drop table t_f;
create table t_chk(<span style="color: #0000ff">id <span style="color: #0000ff">int,age <span style="color: #0000ff">int check(age > <span style="color: #800080">0 and age < <span style="color: #800080">150),gender <span style="color: #0000ff">char(<span style="color: #800080">10) check(<span style="color: #800000">'<span style="color: #800000">boy<span style="color: #800000">' or <span style="color: #800000">'<span style="color: #800000">girl<span style="color: #800000">'<span style="color: #000000">));
alter table t_pk add constraint PK_id primary key(<span style="color: #0000ff">id<span style="color: #000000">);

alter table t_pk drop primary key;

alter table t_fk add constraint FK_id foreign key(<span style="color: #0000ff">id<span style="color: #000000">) references t_pk;

desc table t_fk;

alter table t_fk drop foreign key FK_id
mysqldump -uroot -p OriginS ><span style="color: #000000"> bakp.sql

mysqldump -uroot -p OriginS t_s1,t_s2 ><span style="color: #000000"> s1s2.sql

create database OSdb;

exit;

mysql -uroot -p OSdb <<span style="color: #000000"> bakp.sql
<span style="color: #0000ff">select <span style="color: #000000"> from t_s1;

<span style="color: #0000ff">select<span style="color: #000000"> c_id,c_name,c_addr from t_s1;

<span style="color: #0000ff">select<span style="color: #000000"> c_id as 学号,c_name as 姓名,c_addr 地址 from t_s1;

<span style="color: #0000ff">select<span style="color: #000000"> distinct c_addr from t_s1;

<span style="color: #0000ff">select  from t_s1 where c_age < <span style="color: #800080">18<span style="color: #000000">;

<span style="color: #0000ff">select  from t_s1 where c_age = <span style="color: #800080">23 and c_gender = <span style="color: #800000">'<span style="color: #800000">girl<span style="color: #800000">'<span style="color: #000000">;

<span style="color: #0000ff">select  from t_s1 where cname like <span style="color: #800000">''D<span style="color: #800000">"<span style="color: #800000">;

<span style="color: #0000ff">select * from t s1 where c_id <span style="color: #0000ff">in range(<span style="color: #800080">5<span style="color: #000000">);×

<span style="color: #0000ff">select  from t_s1 where c_id <span style="color: #0000ff">in (<span style="color: #800080">1,<span style="color: #800080">3,<span style="color: #800080">8,<span style="color: #800080">9<span style="color: #000000">)

<span style="color: #0000ff">select  from t_s1 where c_age is not <span style="color: #0000ff">null<span style="color: #000000">;

<span style="color: #0000ff">select <span style="color: #000000"> from goods order by c_price asc;

<span style="color: #0000ff">select <span style="color: #000000"> from goods order by c_time desc;

<span style="color: #0000ff">select <span style="color: #000000"> from goods order by c_price asc,c_time desc;

<span style="color: #0000ff">select  from t_s1 limit <span style="color: #800080">3<span style="color: #000000">;

<span style="color: #0000ff">select * from t_s1 limit <span style="color: #800080">2,<span style="color: #800080">10<span style="color: #000000">;
<span style="color: #0000ff">select<span style="color: #000000"> c_gender from t_s1 group by c_gender;

<span style="color: #0000ff">select<span style="color: #000000"> c_gender,c_addr from t_s1 group by c_gender,c_addr;

<span style="color: #0000ff">select<span style="color: #000000"> c_gender,group_concat(name) from t_s1 group by c_gender;

<span style="color: #0000ff">select gender,max(age),min(age),avg(age),<span style="color: #0000ff">sum<span style="color: #000000">(age),count(age) from t_s1 group by gender;

<span style="color: #0000ff">select gender,group_concat(name) from t_s1 group by gender having gender =<span style="color: #800000">'<span style="color: #800000">girl<span style="color: #800000">'<span style="color: #000000">;

<span style="color: #0000ff">select gender,group_concat(name) from t_s1 where age <<span style="color: #800080">25 group by gender having gender = <span style="color: #800000">"<span style="color: #800000">girl<span style="color: #800000">"<span style="color: #000000">;

<span style="color: #0000ff">select<span style="color: #000000"> gender from t_s1 group by gender with rollup
<span style="color: #0000ff">select t_s.name,t_c.name from t_s,t_c where t_s.c_id = t_c.<span style="color: #0000ff">id<span style="color: #000000">;

<span style="color: #0000ff">select s.name 学生姓名,c.name 班级名称 from t_s s,t_c c where s.c_id = c.<span style="color: #0000ff">id<span style="color: #000000">;

<span style="color: #0000ff">select s.name 学生姓名,c.name 班级名称 from t_s as s inner <span style="color: #0000ff">join t_c c on s.c_id = c.<span style="color: #0000ff">id<span style="color: #000000">;

<span style="color: #0000ff">select s.name 学生姓名,c.name 班级名称 from t_s s left <span style="color: #0000ff">join t_c c on s.c_id = c.<span style="color: #0000ff">id<span style="color: #000000">;

<span style="color: #0000ff">select s.name 学生姓名,c.name 班级名称 from t_c c right <span style="color: #0000ff">join t_s s on s.c_id = c.<span style="color: #0000ff">id<span style="color: #000000">;
<span style="color: #0000ff">select  from t_s1 where age > (<span style="color: #0000ff">select<span style="color: #000000"> avg(age) from t_s1);

<span style="color: #0000ff">select  from t_c where <span style="color: #0000ff">id <span style="color: #0000ff">in (<span style="color: #0000ff">select<span style="color: #000000"> c_id from t_s1);

<span style="color: #0000ff">select  from t_s1 where (age,class_id) = (<span style="color: #0000ff">select<span style="color: #000000"> max(age),min(class_id) from t_s1);

create table areas(<span style="color: #0000ff">id <span style="color: #0000ff">int primary key,atitle varchar(<span style="color: #800080">20),pid(<span style="color: #0000ff">int<span style="color: #000000">));

source ./<span style="color: #000000">areas.sql;

<span style="color: #0000ff">select count() from areas where pid is <span style="color: #0000ff">null<span style="color: #000000">;

<span style="color: #0000ff">select c.* from areas as c inner <span style="color: #0000ff">join areas p on c.pid = p.<span style="color: #0000ff">id where p.atitle =<span style="color: #800000">'<span style="color: #800000">湖南省<span style="color: #800000">'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!