数据库入门(从0到0.1)

一、MySQL 简单到不能再简单的基础语句: 

• information_schema  对象信息   列信息，用户信息，权限信息，字符集
• mysql   用户的权限信息
• test 测试
• cluster 集群信息 

①DDL

数据定义语言  create drop  alter

②DML

数据操纵语言  insert delete update select

③DCL

数据控制语言  grant  revoke

 

1).DDL（create   drop  alter）

(1.创建数据库        create database Cy1703;

(2.查询数据库        show databases;

(3.删除数据库        drop databases Cy1703;

1.建表   

create TABLE Stu(

id  varchar(20) primary key,

name varchar(20) not null,

age  int default null,

sex  varchar(8),

score float );

2.删除表

drop TABLE tablename;

3.查询表结构

show Create table tablename;

4.表的修改alter

1.修改字段属性

alter table Stu MODIFY sex varchar(10);

2.添加字段

alter table Stu ADD height2 float not NULL AFter sex;

3.删除一个字段

alter table Stu DROP height2;

4.修改字段名称

alter table Stu CHANGE sex mysex enum("man","woman");

5.修改表名

alter table Stu RENAME student;

 

2).DML（ insert|load|replace  delete  update  select）

1.数据插入

insert into Stu value("zhangsan",10,"man",98.6);

insert into stu(name,mysex) value("lisi","man");

insert into stu value("zhangsan",10,"man",78.1),

("lisi",10,"man",98.6),

("lisi",10,"man",59.4),

("lisi",10,"man",65.5);

2.数据的删除

1.删除表中的所有数据

delete from  stu;

2.删除符合条件的数据

delete from stu where name = "lisi";

3.数据的修改

update stu SET score = 76 where name = "zhangsan";

4.数据的查询

• select * from Stu;//查询所有
• select name,age,mysex,score from Stu;

1.去重 distinct

select distinct name from stu;

2.排序 order by  asc  desc  

分组 group by

select name,score from stu

order by score desc;

 select name, Sum(score) all_score from stu

 group by name

 having Sum(score) > 100;

 

3.等值查询

1.基本信息表

id  name  zhangsan

2.工资表

id  mysalary

select mysalary from salary a,

(select id infoid from info where name = "zhangsan") b

where a.id = b.infoid;

 

select info.id,name,mysalary from info,salary

where info.id = salary.id;

 

create table info(

id int primary key,

name varchar(20) not null

);

insert into info value(1,"zhangsan"),

(2,"lisi"),

(3,"wangwu"),

(4,"maliu");

insert into salary value(1,5000),

(1,7800),

(3,6000),

(3,6000),

(3,6100);

create table salary(

id int,

mysalary float);

4.链接查询

1).左连接

select b.name,a.mysalary from salary a

left join (select id,name from info where name= "zhangsan") b

on a.id = b.id

where b.name is not null;

2).右连接

select b.name,a.mysalary from salary a

right join (select id,name from info where name in("lisi","zhangsan")) b

on a.id = b.id

where a.mysalary is not null;

3).内连接

inner

5.子查询

select mysalary from salary

where id in (select id from info where name = "zhangsan");

6.联合查询

         union          union all

create table money

(

id int,

mymoney float

);

insert into money value(1,2000),

(3,5000);

select id,mysalary from salary where id = 1

union all

select id,mymoney from money where id = 1;

 

insert into salary value(1,2000);

select sum(mysalary) + sum(mymoney)

from salary,money

where salary.id = 1 and money.id = 1;

3).DCL

grant  给某个用户赋予针对于某个库或者表的某个权限

revoke 回收权限

all         all       all

1.root  ==> user1   ==> user2 ==> user3

 

二、MySQL的数据类型:

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

1、整型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节  范围(-128~127)
smallint(m)	2个字节  范围(-32768~32767)
mediumint(m)	3个字节  范围(-8388608~8388607)
int(m)	4个字节  范围(-2147483648~2147483647)
bigint(m)	8个字节  范围(+-9.22*10的18次方)

2、浮点型

MySQL数据类型	含义
float(m,d)	单精度浮点型    8位精度(4字节)     m总个数，d小数位
double(m,d)	双精度浮点型    16位精度(8字节)    m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.

3、定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。 

decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位。

4、字符串

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	固定长度，最多65535个字符
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符

①char和varchar：

1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 
2. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。 
3. char类型的字符串检索速度要比varchar类型的快。

②varchar和text： 

1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字

节。 

2.text类型不能有默认值。 

3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。

2._BLOB存储的数据只能整体读出。 

3._TEXT可以指定字符集，_BLO不用指定字符集。

三、MySQL中的常用函数：

①conv(N,from_base,to_base);第一参数为要转换的字符，第二个为字符的进制，第三个为要转换的进制

BIN(N),OCT(N),HEX(N)返回N的二进制，八进制，十六进制

②case-when-then 
… 
case-when-then 
end , 
case 字段名 when 值 then 值的替换者 
end 后面可跟 除了case中提到的情况以外的情况的替换者，也可为空。

例如:case when 2>1 then 8 else 10 end;

     case 101 when bin(5) then 1 else 0 end;

③Cast(字段名 as 转换的类型)

其中类型可以为：

CHAR[(N)] 字符型 
DATE 日期型
DATETIME 日期和时间型
DECIMAL float型
SIGNED int
TIME 时间型

④round(x,y)返回参数x的四舍五入的有y位小数的值

⑤mod(x,y) 返回x/y的模（余数）

⑥substr(string string,num start,num length);

string为字符串；start为起始位置；length为长度。

⑦insert(str,x,y,instr)

将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果

⑧length(str)返回字符串str中的字符数

⑨concat(str1,str2,str3,...)字符串拼接函数

⑩ABS(x)函数返回x的绝对值

sign(x) 返回代表数字x的符号的值

⑪ln(x) 返回x的自然对数

log(x,y)返回x的以y为底的对数

sqrt(x) 返回一个数的平方根

⑫floor(x) 返回小于x的最大整数值

ceiling(x) 返回大于x的最小整数值

⑬lcase(str)或lower(str) 返回将字符串str中所有字符改变为小写后的结果

ucase(str)或upper(str) 返回将字符串str中所有字符转变为大写后的结果

⑭avg(col)返回指定列的平均值

⑮count(col)返回指定列中非null值的个数

⑯min(col)返回指定列的最小值

⑰max(col)返回指定列的最大值

⑱sum(col)返回指定列的所有值之和

⑲left(str, x)返回字符串str中最左边的x个字符

right(str,x) 返回字符串str中最右边的x个字符

⑳reverse(str) 返回颠倒字符串str的结果

四、难点答疑：

一）on与where有什么区别，两个表连接时用on，在使用left  jion时，on和where条件的区别如下：

①on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

②where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left  join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

二）INNER JOIN、LEFT JOIN、RIGHT JOIN的区别

INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。

LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

三）mysql中having的用法（和where的区别）

1、SQL标准要求HAVING必须引用GROUP BY子句中的列或用于总计函数中的列。

2、HAVING子句必须位于GROUP BY之后ORDER BY之前。

3、如果HAVING子句引用了一个意义不明确的列，则会出现警告。

4、HAVING子句可以引用总计函数，而WHERE子句不能引用。

5、不要将HAVING用于应被用于WHERE子句的条目。

6、where后面不能接聚合函数，所以引出having进行使用

（聚合函数是针对结果集进行的，但是where条件并不是在查询出结果集之后运行，所以主函数放在where语句中，会出现错误）

7、group by 子句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。

 

四）当一个查询语句同时出现了join，where,group by,having,order by的时候，执行顺序和编写顺序是：

1.先连接from后的数据源(若有join，则先执行on后条件，再连接数据源)。

2.执行where xx对全表数据做筛选，返回第1个结果集。

3.针对上一个结果集使用group by分组，返回第2个结果集。

4.针对上一个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

5.针对上一个结果集执行having xx进行筛选，返回第4个结果集。

6.针对上一个结果集执行order by进行排序。

7.输出结果

五、后续：

    下次带领大家数据库精通(从0.1到99.9)