[大数据]MySQL基本知识02

数据库C[create]R[read]U[update]D[delete]语句

Insert 语句 ?添加数据

INSERT INTO?? table_name [(column [, column. . .])]

VALUES??????? (value [ , value. . . ]);

注意：1.插入的数据应与字段的数据类型相同。

比如把'abc'添加到int类型会错误

2.数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中。

3.在values中列出的数据位置必须与被加入的列的排列位置相对应。

4.字符和日期型数据应包含在单引号中。???????

5.列可以插入空值[前提是该字段允许为空]，insert into table value(null)

6. insert into tab_name(列名..) values （）（）（）形式添加多条记录

7.如果是给表中的所有字段添加数据，可以不写前面的字段名称

8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

如果某个列没有指定not null ,那么当添加数据时，没有给定值，则会默认给nul1

如果我们希望指定某个列的默认值，可以在创建表时指定???????????

使用update语句修改表中数据

UPDATE?? tbl_name

????????? SETcol_name1=expr1 [ , col_name2=expr2 ... ]

[WHERE where_definition]

使用细节:

1.UPDATE语法可以用新值更新原有表行中的各列。

2.SET子句指示要修改哪些列和要给予哪些值。

3.WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。慎用

4.如果需要修改多个字段，可以通过set字段1=值1,字段2=值2.….

Delect语句

使用delete语句删除表中数据。

delete from tbl_name

??????? WHERE? where_definition]

使用细节

1.如果不使用where子句，将删除表中所有数据。

2. Delete语句不能删除某一列的值（可使用update 设为null或者‘ ’)

3.使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。drop table表名;

Select语句

SELECT [DISTINCT]*l ?{ column1,column2 . column3. . }

??????? ??????FROM? table_name;

注意事项(创建测试表学生表)

1. Select 指定查询哪些列的数据。

2. column指定列名。

3.*号代表查询所有列。

4.From指定查询哪张表。

5. DISTINCT可选,指显示结果时，是否去掉重复数据

使用表达式对查询的列进行运算:

SELECT* l ?{ column1 l expression,column2 l expression,..}

???????? FROM table_name;

在select语句中可使用as语句

?????? SELECT column_name as 别名from表名;

在where子句中经常使用的运算符

使用order by子句排掌查询结果:

SELECT column1, column2 . column3. .

??????? FROM table;

??????? order by column ascl desc,...

1.Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。

2. Asc升序[默认]、Desc降序

3.ORDER BY子句应位于SELECT语句的结尾。

合计/统计函数- count

Count返回行的总数

??select count(* ) ?l? count(列名from table_name

??????? [WHERE?? where_definition]

注意: sum仅对数值起作用，否则会报错。

注意:对多列求和，“, ”号不能少。

合计函数- avg

AVG函数返回满足where条件的一列的平均值

select avg(列名){,avg(列名)...}? from table_name

????????? [WHERE? where_definition]

合计函数-Max/min

Max/min函数返回满足where条件的一列的最大/最小值

select max(列名from table_name

????? [WHERE? where_definition]

使用group by子句对列进行分组[先创建测试表]

SELECT column1, column2. column3. . FROM table

?????? Group by column

使用having子句对分组后的结果进行过滤

SELECT column1, column2 . column3 . .

?????? FROM table

?????? group by column having ...

字符串相关的知识

数学相关的函数

注意：rand(返回一个随机浮点值v,范围在0到1之间(即,其范围为0svS 1.0)。若已指定一个整数参数N, 则它被用作种子值，用来产生重复序列。

时间日期相关函数date.sql

注意：

1.DATE ADD()中的interval后面可以是year minute second day等

2.DATE SUB()中的interval后面可以是year minute second hour day等

3.DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数

4.这四个函数的日期类型可以是date, datetime或者timestamp

注意：在实际开发中，我们也经常使用int来保存一个unix时间戳,

然后使用from_unixtime()进行转换，还是非常有实用价值的

加密和系统函数pwd.sql

流程控制函数

mysql表查询--加强

基本语法:select ...limit start,rows

表示从start+1行开始取，取出rows行, start 从0开始计算

总结

数据分组的总结

如果select语句同时包含有group by ,having , limit? order by那么他们的顺序是group by , having , order? by,limit

SELECT column1 , column2 . column3. . FROM? table

????????? group by column

????????? having condition

order by column

limit start, rows，

MySQL数据库多表查询

多表查询是指基于两个和两个以上的表查询.在实际应用中,查询单个表可能不能满足你的需求,

在默认情况下:当两个表查询时，规则

1.从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列].

2.一共返回的记录数第一张表行数*第二张表的行数

3. .这样多表查询默认处理返回的结果，称为笛卡尔集

4. 解决这个多表的关键就是要写出正确的过滤条件where 需要进行分析

注意：多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集

自连接：自连接是指在同一张表的连接查询。

mysql表子查询

（1）子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

（2）单行子查询

单行子查询是指只返回一行数据的子查询语句

（3）多行子查询

多行子查询指返回多行数据的子查询使用关键字in

多列子查询

多列子查序则是指查询返回多个列数据的子查询语句

(字段1，字段2.…)= (select字段1，字段2 from……)

表复制

自我复制数据

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

mysql表外连接

●外连接

1.左外连接(如果左侧的表完全显示我们就说是左外连接)

??select .. from表1 left join表2 on 条件[表1：就是左表 表2：就是右表]

2.右外连接(如果右侧的表完全显示我们就说是右外连接)

??select .. from表1 right join表2 on 条件[表1：就是左表 表2：就是右表]

mysql约束

约束用于确保数据库数据满足特定的商业规则。在mysql中，约束包括: not null、unique,primary key,foreign key,和check五种.

（1）primary key(主键)-基本使用

字段名 ??字段类型primary key

用于唯一的标示表行的数据,当定义主键约束后，该列不能重复

在实际开发中，每个表往往都会设计一个主键.

（2）not nulI(非空)

如果在列上定义了not null,那么当插入数据时，必须为列提供数据。

字段名 ?字段类型not null

（3）unique(唯一)

当定义了唯一约束后，该列值是不能重复的.。

字段名 ?字段类型unique

（4）foreign key(外键)

用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

FOREIGN ?KEY(本表字段名)REFERENCES

主表名(主键名或unique字段名)

外键注意事项：

1.外键指向的表的字段，要求是primary key 或者是unique

2. 表的类型是innodb,这样的表才支持外键

3.外键字段的类型要和主键字段的类型一致(长度可以不同)

4.外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]

5.一旦建立主外键的关系，数据不能随意删除了.

（5）Check

用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间如果不再1000~2000之间就会提示出错。

oracle和sql server 均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。check.sql

基本语法:列名类型 ??????check (check条件)

user表

id, name, sex(man,woman), sal(大于100小于900)

在mysql中实现check的功能,一般是在程序中控制,

或者通过触发器完成。

自增长

字段名整型primary key auto_increment

添加自增长的字段方式

insert into xxx(字段1，字段2.….) values(null,"值'...);

insert into xxx(字段2....) values('值1，"值2'.…);

insert into xxx values(null, "值1',.....)

细节：

1.一般来说自增长是和primary key配合使用的

2自增长也可以单独使用[但是需要配合一个unique]

3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)

4.自增长默认从1开始，你也可以通过如下命令修改alter

table表名auto increment = xxx;

5. 如果你添加数据时,给自增长字段(列)指定的有值，则以指定的值为准,如果指定了自增长，一般来说，就按照自增长的规则来添加数据

mysql索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍干倍。

注意：

没有索引为什么会慢?因为全表扫描.

使用索引为什么会快?形成一个索引的数据结构，比如二叉树

索引的代价

1.磁盘占用

2.对dml(update（修改） delete（删除） insert（添加）)语句的效率影响

索引的类型

1.主键索引，主键自动的为主索引(类型Primary)

create table t1 (

??id int primary key, --主键，同时也是索引，称为主键索引.

name varchar(32));

2.唯一索引(UNIQUE)

create table t2(

id int unique, -- id是唯一的，同时也是索引，称为unique索引.

name varchar(32));

3.普通索引 (INDEX)

4.全文索引(FULLTEXT)[适用于MyISAM]

一般开发，不使用mysql自带额全文索引,开发中考虑使用:全文搜索Solr 和 ElasticSearch (ES)

索引使用

1.添加索引(建小表测试id , name ) index_use.sql

create [UNIQUE] index index_name on tbl_name (col_name[(length)]

[ASC | DESC] ，...…);

alter table table_name ADD INDEX [index_name] (index_ col name..)

2.添加主键(索引)ALTER TABLE表名ADD PRIMARY KEY(列名.…);

3.删除索引

DROP INDEX index name ON tbl_name

alter table table _name drop index index_name;

4.删除主键索引比较特别: alter table t_b drop primary key:

5.查询索引(三种方式)

show index(es) from table name;

show keys from table name;

desc table Name;

小结:哪些列上适合使用索引

1.较频繁的作为查询条件字段应该创建索引

select * from emp where empno = 1

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询·条件

select * from emp where sex ='男‘

3．更新非常频繁的字段不适合创建索引

select * from emp where logincount =1

4、不会出现在WHERE子句中字段不该创建索引