数据库基础---->表操作、数据操作、单表查询1-11 第三十八天 2018.11.29

表操作

表操作 SQL语句---->关系型数据库通用(90%+)

表的增删改查

创建表

create table 表名(
  id int not null auto_increment PRIMARY key,
  name VARCHAR(50) not null,
    sex CHAR(2) NULL
 )
auto_increment  自增   PRIMARY key 主键---->不能有重复的字段       自增需要和主键一起使用

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

查看表中数据　　

#查询表数据
select 字段(多个以","间隔) from 表名;
例:  select name,sex from student;
或者: select * from student;
   
#查看表结构
desc 表名;
例: desc student;
 
#查看创建表信息
show create table student;　

注释   语句后加空格   或者#

删除表　

#删除表
drop table 表名;
 
#清空表
truncate table 表名;　　

修改表

#添加表字段
alter table 表名 add 字段名 类型 约束;
例如: alter table student add age int not null default 0 after name;
ps: after name 表示在name字段后添加字段 age.
  
#修改表字段
方式一: alter table student modify 字段 varchar(100) null;
方式二: alter table student change 旧字段 新字段 int not null default 0;
ps:二者区别:
change 可以改变字段名字和属性
modify只能改变字段的属性
    
#删除表字段 :
alter table student drop 字段名;
  
#更新表名称:
rename table 旧表名 to 新表名;

补充：
    alter table 表名 add PRIMARY key(主键名称); # 添加主键
                        两个以上为联合主键---->联合主键所有主键+一起不能一样
    alter table 表名 drop PRIMARY key;          # 删除主键
    alter table 表名 alter 字段名 set default 值;   # 设置字段默认值
    alter table 表名 alter 字段名 set drop default 值;   # 删除字段默认值

复制表　　

#只复制表结构和表中数据
CREATE TABLE tb2 SELECT * FROM tb1 (where id =1);   # *表的所有   可以带数据进行复制
ps:主键自增/索引/触发器/外键 不会 被复制    where 1 ！=1 条件不成立，表中数据不会被复制
   
#只复制表结构(不会复制表中数据)
create table tb2 like tb1;
ps: 数据/触发器/外键 不会被复制　

数据类型　　

MySQL支持多种类型，大致可以分为四类：数值、字符串类型、日期/时间和其他类型。   

二进制类型:          # 基本上用不到，了解
    bit[(M)]
       二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

整数类型:
    tinyint[(m)] [unsigned] [zerofill]

        小整数，数据类型用于保存一些范围的整数数值范围：
        有符号：-128 ～ 127.
        无符号：255

        特别的： MySQL中无布尔值，使用tinyint(1)构造。

    int[(m)][unsigned][zerofill]    # 宽度---->给不给无所谓，给小了，放的大了会自动扩充到

        整数，数据类型用于保存一些范围的整数数值范围：
        有符号： -2147483648 ～ 2147483647
        无符号：4294967295

    bigint[(m)][unsigned][zerofill]     # 太大了，基本上用不到
        大整数，数据类型用于保存一些范围的整数数值范围：
        有符号：-9223372036854775808 ～ 9223372036854775807
        无符号：18446744073709551615
        
作用：存储年龄，等级，id，各种号码等

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，所以我们使用默认的就可以了
    有符号和无符号的最大数字需要的显示宽度均为10，
    而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的
    
小数型:      UNSIGNED  长度      ZEROFILL  小数点
    decimal[(m[,d])] [unsigned] [zerofill]
        准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

        特别的：对于精确数值计算时需要用此类型
        decaimal能够存储精确值的原因在于其内部按照字符串存储。

    FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]     # 小数越长越不精准
        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
        有符号：(-3.402823466E+38 to -1.175494351E-38),0,(1.175494351E-38 to 3.402823466E+38)
        无符号：0，(1.175 494 351 E-38，3.402 823 466 E+38)

        **** 数值越大，越不准确 ****

    DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
        双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

        有符号：(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，
               (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
        无符号：0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
        
        **** 数值越大，越不准确 ****
        
作用：存储薪资、身高、体重、体质参数等
枚举类型(了解):      
    enum
     
        An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
        
        示例：
            CREATE TABLE user (
             name VARCHAR(40),
             sex ENUM('男', '女', '未知')
            );
            INSERT INTO user (name, sex) VALUES ('人1','男'), ('人2','女'),
                                                ('人3','未知'),('人4','人妖');
            PS:人4会插入成功吗??        
集合类型(了解):
    set
    
        A SET column can have a maximum of 64 distinct members.
        示例：
        CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
        
        INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

日期/时间类型:
                
    DATE     日期值
    
        YYYY-MM-DD（1000-01-01/9999-12-31）

    TIME  时间值或持续时间
        HH:MM:SS（'-838:59:59'/'838:59:59'）

    YEAR 年份值
        YYYY（1901/2155）

    DATETIME 混合日期和时间值

        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

    TIMESTAMP 时间戳

        YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
        
作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

面试题char与varchar　　

字符型:       面试必考
    char (m)
        char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
        
        PS: 即使数据小于m长度，也会占用m长度,但是在查询时，查出的结果会自动删除尾部的空格
        
★☆★☆特点:定长，简单粗暴，浪费空间，存取速度快---->查询快
            
    varchar(m)
        varchar 数据类型用于变长的字符串，可以包含最多达65535个字符
        (理论上可以,但是实际上在超出21845长度后,mysql会自动帮您转换数据类型为文本类型)。
        其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

        PS: varchar类型存储数据的真实内容，例如:如果'ab  ',尾部的空格也会被存起来
        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数
        （1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
        特点:变长，精准，节省空间，存取速度慢
        
        sql优化：创建表时，定长的类型往前放，变长的往后放
                        比如性别          比如地址或描述信息
        PS：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，
            有时甚至可以超出varchar处理速度的50%。
            因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡            
        
    text
        text数据类型用于保存变长的大字符串，可以最多到65535 (2**16 − 1)个字符。

数据操作　

数据操作增删改查

增、表中插入数据

#语法一: 按字段进行插入
insert into 表(字段1,字段2 ...) values (值1,值2 ...);
 
#语法二:按字段顺序插入
insert into 表 values (值1,值2 ...);
 
#语法三: 插入多条记录
insert into 表 values (值1,值2 ...) ,(值1,值2 ...)  ,(值1,值2 ...);
 
#语法四:插入查询结果
insert into 表(字段1,字段2 ...)  select 字段1,字段2 ... from 表;

删除数据　　

#语法一:整表数据删除
delete from 表 ;
 
#语法二:删除符合 where后条件的数据
delete from 表 where 字段1=值1;

面试题truncate和delete的区别　　

1、TRUNCATE 在各种表上无论是大的还是小的都非常快。而DELETE 操作会被表中数据量的大小影响其执行效率.
2、TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。
3、TRUNCATE不能触发触发器，DELETE会触发触发器。
4、当表被清空后表和表的索引和自增主键将重新设置成初始大小，而delete则不能。

修改数据　更新操作

#语法一: 更新整表数据
update 表 set 字段1= '值1', 字段2='值2' ... ;        # set设置
 
#语法二:更新符合条件字段3的数据
update 表 set 字段1= '值1', 字段2='值2' ... where 字段3 = 值3;

查询数据　　

创建一个表例子

-- 创建表
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) DEFAULT '0',
  `sex` enum('男','女','人妖') NOT NULL DEFAULT '人妖',
  `salary` decimal(10,2) NOT NULL DEFAULT '250.00',
  `hire_date` date NOT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

-- 创建数据

-- 教学部
INSERT INTO `person` VALUES ('1', 'alex', '28', '人妖', '53000.00', '2010-06-21', '1');
INSERT INTO `person` VALUES ('2', 'wupeiqi', '23', '男', '8000.00', '2011-02-21', '1');
INSERT INTO `person` VALUES ('3', 'egon', '30', '男', '6500.00', '2015-06-21', '1');
INSERT INTO `person` VALUES ('4', 'jingnvshen', '18', '女', '6680.00', '2014-06-21', '1');

-- 销售部
INSERT INTO `person` VALUES ('5', '歪歪', '20', '女', '3000.00', '2015-02-21', '2');
INSERT INTO `person` VALUES ('6', '星星', '20', '女', '2000.00', '2018-01-30', '2');
INSERT INTO `person` VALUES ('7', '格格', '20', '女', '2000.00', '2018-02-27', '2');
INSERT INTO `person` VALUES ('8', '周周', '20', '女', '2000.00', '2015-06-21', '2');

-- 市场部
INSERT INTO `person` VALUES ('9', '月月', '21', '女', '4000.00', '2014-07-21', '3');
INSERT INTO `person` VALUES ('10', '安琪', '22', '女', '4000.00', '2015-07-15', '3');

-- 人事部
INSERT INTO `person` VALUES ('11', '周明月', '17', '女', '5000.00', '2014-06-21', '4');
-- 鼓励部
INSERT INTO `person` VALUES ('12', '苍老师', '33', '女', '1000000.00', '2018-02-21', null);

--准备表和数据　

单表查询

1、简单查询

#查询语法: 
select [distinct]*(所有)|字段名,...字段名 from 表名;
 
#查询所有字段信息
select * from person;
 
#查询指定字段信息
select id,name,age,sex,salary from person;
 
#别名查询，使用的as关键字，as可以省略的
select name,age as'年龄',salary '工资' from person;
 
#直接对列进行运算,查询出所有人工资,并每人增加100块.
select (5/2);
select name, salary+100 from person;
 
#剔除重复查询
select distinct age from person;  

2、条件查询　　

条件查询:使用 WHERE 关键字 对简单查询的结果集 进行过滤
    1. 比较运算符: > < >= <= = <>(!=)
    2. null 关键字: is null , not null
    3.逻辑运算符: 与 and 或 or (多个条件时,需要使用逻辑运算符进行连接)
#查询格式:
select [distinct]*(所有)|字段名,...字段名 from 表名 [where 条件过滤]
 
#比较运算符: > < >= <= = <>(!=)    is null 是否为null
select * from person where age = 23;
select * from person where age <> 23;
select * from person where age is null;
select * from person where age is '';
select * from person where age is not null;
 
#逻辑运算符: 与 and 或 or
select * from person not(where age = 23 and salary =29000);
select * from person where age = 23 and salary =29000;
select * from person where age = 23 or salary =29000;

3、区间查询　　

关键字 between 10 and  20 :表示 获得10 到 20 区间的内容
# 使用  between...and  进行区间 查询
select * from person where salary between 4000 and 8000;
ps: between...and 前后包含所指定的值
等价于 select * from person where salary >= 4000 and salary <= 8000;

4、集合查询　　

关键字: in, not null
#使用 in 集合(多个字段)查询
select * from person where age in(23,32,18);
等价于: select * from person where  age =23 or age = 32 or age =18;
 
#使用 in 集合 排除指定值查询
select * from person where age not in(23,32,18);

5、模糊查询　　

关键字 like , not like
     %  :  任意多个字符
     _  : 只能是单个字符
#模糊查询  like %:任意多个字符,  _:单个字符
 
#查询姓名以"张"字开头的
select * from person where name like '张%';
#查询姓名以"张"字结尾的
select * from person where name like '%张';
#查询姓名中含有"张"字的
select * from person where name like '%张%';
 
#查询 name 名称 是四个字符的人
select * from person where name like '____';
#查询 name 名称 的第二个字符是 'l'的人
select * from person where name like '_l%';
 
#排除名字带 a的学生
select * from student where name not like 'a%'

6、排序查询　　

关键字: ORDER BY  字段1 DESC, 字段2 ASC
#排序查询格式:
select 字段|* from 表名 [where 条件过滤] [order by 字段[ASC][DESC]]
 
升序:ASC 默认为升序
降序:DESC
PS:排序order by 要写在select语句末尾
 
#按人员工资正序排列,注意:此处可以省略 ASC关键字(默认正序排序)
select * from person order by salary ASC;
select * from person order by salary;
 
#工资大于5000的人,按工资倒序排列
select * from person where salary >5000 order by salary DESC;
 
#按中文排序
select * from person order by name;   # 无法排序，需要加个编码集条件
 
#强制中文排序
select * from person order by CONVERT(name USING gbk);
ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序

7、聚合查询 

聚合: 将分散的聚集到一起.

聚合函数: 对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外，都会忽略空值

COUNT：统计指定列不为NULL的记录行数；

SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

#格式:
select 聚合函数(字段) from 表名;
 
#统计人员中最大年龄、最小年龄，平均年龄分别是多少
select max(age),min(age),avg(age) from person;

8、分组查询

分组的含义: 将一些具有相同特征的数据 进行归类.比如:性别,部门,岗位等等

怎么区分什么时候需要分组呢?  

　　套路: 遇到 "每" 字,一般需要进行分组操作

　　例如: 1. 公司每个部门有多少人

　　         2. 公司中有 多少男员工 和 多少女员工

#分组查询格式:
select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]
ps: 分组查询可以与 聚合函数 组合使用.

#查询每个部门的平均薪资
select avg(salary),dept from person  GROUP BY dept;

#查询每个部门的平均薪资 并且看看这个部门的员工都有谁?
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept;
#GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来

#查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?  
select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000;

where 与 having区别:

执行优先级从高到低：where > group by > having 

1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

9、分页查询　　

#查询前5条数据
select * from person limit 5;     #limit分页参数1(起始位置0开始)参数2(这页有几条)
 
#查询第5条到第10条数据
select * from person limit 5,5;
 
#查询第10条到第15条数据
select * from person limit 10,5;
 
ps: limit (起始条数),(查询多少条数);

10、正则表达式

# ^  匹配 name 名称 以 "e" 开头的数据
select * from person where name REGEXP '^e';
 
# $  匹配 name 名称 以 "n" 结尾的数据
select * from person where name REGEXP 'n$';
 
# . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意字符
select * from person where name REGEXP '.x';
 
# [abci] 匹配 name 名称中含有指定集合内容的人员
select * from person where name REGEXP '[abci]';
 
# [^alex] 匹配 不符合集合中条件的内容 , ^表示取反
select * from person where name REGEXP '[^alex]';
#注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
#注意2 : 简单理解 name  REGEXP '[^alex]' 等价于 name != 'alex'
 
# 'a|x' 匹配 条件中的任意值
select * from person where name REGEXP 'a|x';　　
 
#查询以w开头以i结尾的数据
select * from person where name regexp '^w.*i$';
#注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

　　

11、SQL语句执行顺序　　

select name, max(salary) 
from person    
where name is not null  
group by name  
having max(salary) > 5000   
order by max(salary)
limit 0,5

 

(1). 首先执行 FROM 子句, 从 person 表 组装数据源的数据
(2). 执行 WHERE 子句, 筛选 person 表中 name 不为 NULL 的数据
(3). 执行 GROUP BY 子句, 把 person 表按 "name" 列进行分组
(4). 计算 max() 聚集函数, 按 "工资" 求出工资中最大的一些数值
(5). 执行 HAVING 子句, 筛选工资大于 5000的人员.
(6). 执行 ORDER BY 子句, 把最后的结果按 "Max 工资" 进行排序.
(7). 最后执行 LIMIT 子句, . 进行分页查询