MySQL笔记（二）

一、SQL语句“数据行”操作补充

增　

　　insert into tb1(name,age) values('peng',20);　　　增加一条数据

　　insert into tb1(name,age) values('peng',20),('sheng',19);　　增加多条数据，用逗号隔开

　　insert into tb2(name,age) select  name,age from tb1;　　将t2表的name，age列全部插入到t1表中

 

 

删　

     delete from tb1;

　　delete from db1 where id>5;  　　where后面是条件语句，在很多地方都适用

　　delete from db1 where id!=5 ;       在MySQL中不等号的另外一种写法： id<>5 

　　delete from db1 where id<5 or name='jack'; 

 

 

改

　　update tb2 set name='jack' where id>5 and name='lucy';

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

 

 

查　

   select * from tb1;

　　select name,age from tb1;

　　select where id>2 name='jack';

   select id,name as cname from tb12 where id > 10 or name ='xxx';  # 加个as表示给表头取一个别名，但是里面的内容不会变（name as cname）只是name取了别名

    select name,age,11 from tb12;  # 写一个常量（11）表示加了一列，并且这一列都是11

关于‘查’的其他：

select * from tb1 where id!=1;

select * from tb1 where id<>1;

select * from tb1 where id in (1,5,12);

select * from tb1 where id not in (1,5,12);

select * from tb1 where id between 2 and 6;  # 闭区间

select * from tb12 where id in (select id from tb11)   # in里面还可以去别的表查  会先执行括号里面的语句

通配符：下划线和百分号

以a开头 两种写法：
1、a% 表示%后面可以有任意多的字符 （ab,abc,acbhsjhj)
2、a_ 表示后面只能有一个位置（ab，ac）
拓展：%a%表示中间包含a的（只要有a就行）

select * from tb12 where name like "a%" 表示以a开头的
select * from tb12 where name like "%a" 表示以a结尾的
select * from tb12 where name like "a_"

分页：(翻页查看，比如百度查阅）limit

select * from tb3 limit 10；  查看前十条数据

select * from tb3 limit 0,10；0表示起始位置，10表示查看多少条数据

select * from tb12 limit 10 offset 20;  offset表示从哪开始，limit后面表示取多少条（这条代码和上面那条功能是一样的）

排序：order by  desc  asc

select * from 表 order by 列 asc              - 根据 “列” 从小到大排列
select * from 表 order by 列 desc             - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序


 select * from tb12 order by id desc; 大到小     by后面表示通过id排列
 select * from tb12 order by id asc;  小到大 
 记忆方法：abcd  a在前面--asc从小到大排， d在后面--desc从大到小排。

 select * from tb12 order by age desc,id desc;  ★优先按age排，age如果重复的话再根据id --》desc排
                     
 取后10条数据：
 select * from tb12 order by id desc limit 10;

分组：group by

select count(id),max(id),part_id from userinfo5 group by part_id;   【group by表示根据谁分组 max(id)重合的话取最大的id拿过来】
聚合函数：把相同的放一块
count 计数
max
min
sum
avg
                    
***** 如果对于聚合函数结果进行二次筛选时？必须使用having关键字，不能用where *****
elect count(id),part_id from userinfo5 group by part_id having count(id) > 1;  where里面一定不能出现聚合函数
                    
 select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

 

连表操作：重要

 两个表或多张表放在一起查看

连表操作（重要）：  两个表或多张表放在一起查看
                
                    select * from userinfo5,department5;  # 把两张表都拿出来，但没有告诉两张表的关系
                    写法1：
                    select * from userinfo5,department5 where userinfo5.part_id = department5.id;  # 建立关系userinfo5.part_id = department5.id
                    
                    写法2：（推荐）
                    select * from userinfo5 left join department5 on userinfo5.part_id = department5.id; # left join 左连接  两张表关系用on
                    # userinfo5左边全部显示 左边是userinfo5
                    select * from department5 left join userinfo5 on userinfo5.part_id = department5.id;
                    #相当与伪造了一个右连接  左边是department5
                    
                    
                    # select * from userinfo5 right join department5 on userinfo5.part_id = department5.id;
                    # department5右边全部显示 右边是department5
                
                
                inner：
                    select * from userinfo5 inner join department5 on userinfo5.part_id = department5.id;
                    将出现null时的一行隐藏

 

对于自增补充：

 2         desc t10; 看每个字段什么意思
        
        show create table t10; 查看这个表是怎么创建的   是横的显示
        
        show create table t10 \G;  竖的显示 看的更好
        
        alter table t10 AUTO_INCREMENT=20; 修改自增值
            

            
        MySQL: 自增步长
            基于会话级别： （一次登录就是一个会话）
                show session variables like 'auto_inc%';  ---->    查看全局变量
                set session auto_increment_increment=2;   ---->    设置会话步长
                # set session auto_increment_offset=10;
            基于全局级别：
                show global variables like 'auto_inc%';        查看全局变量
                set global auto_increment_increment=2;         设置会话步长
                # set global auto_increment_offset=10;
                
                
        SqlServer：自增步长：
            基础表级别：
                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
                
                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=utf8

 

 

desc t10; 看每个字段什么意思

show create table t10; 查看这个表是怎么创建的 是横的显示

show create table t10 \G; 竖的显示 看的更好

alter table t10 AUTO_INCREMENT=20; 修改自增值

MySQL: 自增步长
基于会话级别： （一次登录就是一个会话）
show session variables like 'auto_inc%'; ---->    查看全局变量
set session auto_increment_increment=2; ---->    设置会话步长
# set session auto_increment_offset=10;
基于全局级别：
show global variables like 'auto_inc%';    查看全局变量
set global auto_increment_increment=2; 设置会话步长
# set global auto_increment_offset=10;


SqlServer：自增步长：
基础表级别：
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

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=utf8

单列唯一索引和联合唯一索引

 

唯一索引：1、约束 2、加速查找 （索引就是为了加速查找）
唯一索引：单列唯一索引和联合唯一索引

create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名), # 限制这个列名是唯一的 （联合唯一：这两个值不能完全一样） 只写一个列名是单列的唯一
constraint ....
)

唯一：
约束不能重复（可以为空）
PS: 主键不能重复（不能为空）
加速查找