MySQLの開発に必要なsql文

データベースの作成

create database db1;

データベースの削除

drop database db1;

データテーブルの作成

create table tb1   (
    id int not null auto_increment primary key,
    name char(10),
    department_id int,
    p_id int)engine=innodb default charset=utf8;

プライマリ・キー(primary key)テーブルには1つのプライマリ・キーしかありません.プライマリ・キーは1つのカラムまたは複数のカラムから構成できます.
外部キーの作成

CREATE TABLE t5 (
    nid int(11) NOT NULL AUTO_INCREMENT,
    pid int(11) not NULL,
    num int(11),
    primary key(nid,pid) 

データ行の操作
データの挿入

insert into tb1(name,age) values('ax',8);
insert into tb12(name,age) select name,age from tb11;

テーブル内のデータの削除

delete from t1;
truncate table t1;
drop table t1;
delete from tb1 where id > 10;
delete from tb12 where id >=2 or name='alex';

データの更新

update tb1 set name='root' where id > 10

データのクエリー

select * from tb;
select id,name from tb;`

表構造の表示

show create table t1;
desc t1;

その他

select * from tb12 where id != 1;
select * from tb12 where id in (1,5,12);
select * from tb12 where id not in (1,5,12);
select * from tb12 where id in (select id from tb11);
select * from tb12 where id between 5 and 12;

ワイルドカード

select * from tb12 where name like "a%";
select * from tb12 where name like "a_";

ページング

select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20;  
# page = input('         ')  
# page = int(page)  
# (page-1) * 10  
# select * from tb12 limit 0,10; 1  
# select * from tb12 limit 10,10;2

ツールバーの

select * from tb12 order by id desc;    
select * from tb12 order by id asc;     
select * from tb12 order by age desc,id desc;  
  
  10     
select * from tb12 order by id desc limit 10;

グループ化

select count(id),max(id),part_id from userinfo5 group by part_id;
         ：
    count
    max
    min
    sum
    avg

****                  ？    having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

自己付加価値設定
表自己付加価値の設定

alter table t1 auto_increment=20;--         20   ，    show create table t1\G             。

セッション・レベルベース

--         
show session variables like 'auto_incre%'

--       
set session auto_increment_increment=2;

--      
set session auto_increment_offset=10;

グローバル設定に基づく

--         
show global variables like 'auto_inc%';

--        
set global auto_increment_increment=3;

--      
set global auto_increment_offset=11;

sql serverは、テーブル作成時に自分で設定できる柔軟性が高いREATE TABLE t 5(nid int(11)NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4、ステップ長=2 DEFAULT CHARSET=utf 8
CREATE TABLE `t6` (
nid int(11) NOT NULL AUTO_INCREMENT,pid int(11) NOT NULL,num int(11) DEFAULT NULL,PRIMARY KEY (nid,pid)) ENGINE=InnoDB AUTO_INCREMENT=4、ステップ長=20 DEFAULT CHARSET=utf 8
ユニークインデックス

create table t1(
    id int,
    num int,
    xx int,
    unique qu1 (num ,xx) --                   ，    1,1,   
);

≪ユニーク・インデックス｜Unique Index｜oem_src≫:制約は重複できません(空でも構いません)プライマリ・キー索引:制約は重複できません(空ではありません)彼らの特徴はすべてクエリーの高速化です
外部キー1対1

create table userinfo1(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8;

create table admin(
    id int not null auto_increment primary key,
    username varchar(64) not null,
    password VARCHAR(64) not null,
    user_id int not null,
    unique uq_u1 (user_id),
    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;

外部キーの多対多

  1：
       
       

  2：
       
       
           
===》   

create table userinfo2(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8;

create table host(
    id int auto_increment primary key,
    hostname char(64)
)engine=innodb default charset=utf8;

create table user2host(
    id int auto_increment primary key,
    userid int not null,
    hostid int not null,
    unique uq_user_host (userid,hostid),
    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
                CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;

連結テーブル操作

select * from userinfo5,department5;

select * from userinfo5,department5 where userinfo5.part_id = department5.id;

select * from userinfo5 left join department5 on userinfo5.part_id = department5.id;

select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;
# userinfo5      

# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5      

select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
   null     

select * from department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id;

select score.sid,student.sid from score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.ti;

select count(id) from userinfo5;