MySQL基础操/下

MySQL基础操

 

一、自增补充

desc (表名)t1;  查看表格信息内容 表的信息
show create table t1(表名):也是查看信息,还不多是横向查看
show  create table t1 \G; 竖向查看自增信息
alter table t1 AUTO_INCREMENT=3; 可以修改自增

MySQL:自增步长

 基于会话级别:

 show session variables like “auto_inc%;查看全局变量
 set session auto_increment_increment=2; 设置绘画步长 
 set global auto_increment_offset=10; 表示自增长字段每次递增的量,其默认值是1;

基于全局级别:

show global variables like 'auto_inc%';	    查看全局变量
set global auto_increment_increment=2; 	    设置会话步长
# set global auto_increment_offset=10;

补充主键:一张表只有一个主键,但主键可以有多列组成;  

CREATE TABLE `t5` (
                  `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= =utf8  //设置步长 及自动增加

 

二、唯一索引

 唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

 主键索引:不允许有空值。一般是在建表的时候同时创建主键索引。

 unique 唯一索引名称 (列名,列名)//联合索引,  unique uq_u1 (user_id), //唯一索引 

 create table t1(
            id int ....,
            num int,
            xx int,
            unique 唯一索引名称 (列名,列名) //联合索引
            constraint ....
        )

 

三、外键的变种

a:用户表和部门表(一对多形式)

用户:
                1 alex     1
                2 root       1
                3 egon       2
                4 laoyao   3
                
            部门:
                1 服务
                2 保安
                3 公关
            ===》 一对多
View Code

b:用户表和博客表(一对一形式) 

用户表:
                1 alex    
                2 root       
                3 egon       
                4 laoyao   
            博客表:
                                  FK() + 唯一
                1   /yuanchenqi/   4
                2    /alex3714/    1
                3    /asdfasdf/    3
                4    /ffffffff/    2
                
            ===> 一对一
View Code
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;
View Code

 c: 用户表(百合网) 相亲记录表

 示例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;
View Code

 

、SQL语句数据行操作补充

创建:

create table tb12(
                id int auto_increment primary key,
                name varchar(32),
                age int
            )engine=innodb default charset=utf8;

增: 

 insert into tb11(name,age) values('alex',12);  //单行插入
  insert into tb11(name,age) values('alex',12),('root',18); //多行插入
  insert into tb12(name,age) select name,age from tb11; //将tb11里面表的内容插入tb12;

 删:

delete from tb12;
delete from tb12 where id !=2 
delete from tb12 where id =2 
delete from tb12 where id > 2 
delete from tb12 where id >=2 
delete from tb12 where id >=2 or name='alex'

 改:

update tb12 set name='alex' where id>12 and name='xx'  
update tb12 set name='alex',age=19 where id>12 and name='xx'

查:

select * from tb12;
select id,name from tb12;
select id,name from tb12 where id > 10 or name ='xxx';
select id,name as cname from tb12 where id > 10 or name ='xxx';  //as可以修改序列名;
select name,age,11 from tb12;  //查看另一张表 

其他:

a:条件
select * from tb12 where id != 1
select * from tb12 where id in (1,5,12); //查看表id是1,5,12的
select * from tb12 where id not in (1,5,12);//查看表id不是1,5,12的
select * from tb12 where id in (select id from tb11) 
select * from tb12 where id between 5 and 12; //闭开间取范围的

b:通配符:
select * from tb12 where name like "a%"  // a开头的所有(%多个字符串)
select * from tb12 where name like "a_"  //a开头的(— 一个字符)

c:限制(分页):
select * from tb12 limit 10;     // 前10行
select * from tb12 limit 0,10;   //从1行开始的10行
select * from tb12 limit 10,10;  //从10行开始的10行
select * from tb12 limit 10 offset 20;  //从第10行开始的20行

d:排序
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;

e:分组:****
select count(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;
特别的:group by 必须在where之后,order by之前

f:连表:********
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id                
//usernfo5 左边全部显示
//department5 左边全部显示

# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示

         userinfo5表所有显示,如果department5中无对应关系,则值为null
            
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id 
将出现null时一行隐藏
         
其他

 

posted @ 2017-06-06 20:12  karina梅梅  阅读(219)  评论(0编辑  收藏  举报