MySQL数据库以及相关知识点总结大全

一、MySQL(My Structured Query Language，我的结构化查询语言)

1.JDBC:Java DataBase Connection,java数据库连接

2.数据库：储存数据的库。

3.数据库类型：
a.关系型数据库
b.网状型数据库
c.面向对象型数据库

4.关系型数据库：
Oracle 大型数据库
Sql server
MySQL分支：Percona、Server、MariaDB、Drizzle

非关系型数据库（常用于运维）
MongoDB
Redis：Remote Dictionary Server（远程数据服务）
memcached：高性能的分布式内存缓存服务器

5.DBA:DataBaseAdministrator,数据库管理员

6.MySQL注意事项：在dos中每条命令必须以；或者\g结尾

7.进入MySQL数据指令：在dos环境下，切换到bin目录，然后输入mysql -uroot -p,然后回车，进入mysql数据库系统

8.数据库各种命令：DDL

DDL(Database Define Language)：用来定义数据库，一般有建立数据库，建立表。
a.create database db_name:建立数据库。
b.show databases: 查看所有的数据库。
c.use db_name: 使用db_name数据库。
d.show tables: 查看该数据库内的所有表。
e.create table table_name(field1 type, field2 type…)：在当前数据库下建立一个table_name的表，具有多个字段。
f.desc table_name: 查看表的结构；
g.drop database dbname: 删除数据库
h.drop table tablename: 删除数据表
i.alter table tablename modify columnName type: 修改tablename的columnName字段类型。
示例：alter table first_table modify name char(30);//修改了name字段的数据类型
j.alter table tablename add columnName type:向tablename表内添加一个字段。
示例：alter table first_table add salary int(10);// 向first_table表添加了一个salary字段。
k.alter table tablename change oldcolumnName newcolumnName type:修改tablename的oldcolumnName，改为newcolumnName,类型是type
示例：alter table first_table change salary money int(5);//first_table表内的salary字段名被改为了money，类型是int（5）
l.alter table tablename rename new_tablename:修改表名

DML(Database Manipulate Language):一般指查看数据库表的数据信息

  a.select * from table_name:查看mytable内的所有数据。

  b.select filed from table_name:查看mytable内的field字段数据。

   c.insert into table_name values(field, field1)：向table_name插入一条记录。

   d.insert into table_name（field1,field2...）values(field1, field2)：向table_name插入一条记录,没有被赋值的字段显示默认值。

   e.insert into tablename (fieldname...) values(value1...), (value1...)：向tablename插入多条记录。
示例：insert into firt_table values('陈','男'),('张','女'),('李','男');//向firr_table插入多条记录

分组查询： select 函数 （字段） from 表名 group by 组名
示例： select avg（age）from S group by sgroup；

子查询： 参考上题表结构，检索选修课程“C2”的学生中成绩最高的学生的学号。正确的SELECT语句是。
SELECT S# FORM SC WHERE C#=“C2” AND GRADE＞＝ALL(SELECT GRADE FORM SCWHERE C#=“C2”)

  f.update tablename set field1 = value1... (where conditon):更新表格中field字段值（所有的filed1字段值）。如果加上condition，就是更新了符合               condition的记录的字段值。
 示例：（1）update firt_table set name='李权';//更新firt_table中所有的name为李权的字段值。
      （2）update firt_table set name='李权' where department='研发部';//更新firt_table表格中，department是研发部的记录的name值，name值为李权。

g.delete from tablename (where condition):删除tablename表格中的所有记录。如果加上condition，就是删除了符合condion条件的记录。
 示例：
     （1）delete from firt_table;//删除了firt_table内的所有记录。此时表是空的了。
     （2）delete from firt_table where name='小张';//删除了firt_table内的name是小张的记录。


 h.select * from tablename where condition:显示条件是condition的记录。
      示例：select * from firt_table where name='小李';//只显示小李所在的记录。

降序或升序
i.select * from tablename (where condition) (order by field desc/asc):按照field的desc（降序）/asc(升序)对查询结果进行显示。
示例：select * from forth_table order by age desc;//按照age的降序对查询结果进行显示。
j.select…limit row_count:对查询结果显示row_count行。
示例：select * from forth_table limit 3;//显示forth_table的前3行。

k.表连接
(1).内连接：找出相同的记录。（一般是多个表进行查询连接）
select tablename.fiedl1,tablename1.field1 from tablename,tablename1 where tablename1.field1 = tablename.field1:找出tablename及tablename1中，field1相同的记录。
示例：select second_table.name,third_table.name from second_table,third_table where second_table.name=third_table.name;//显示second_table及third_table中，name属性相同的记录。

(2).外连接
<1>左连接
select one_table.fiedl1,two_table.field1 from one_table left join on two_table onetable.field1 = twotable.field1:显示one_table、two_table中相同（符合one_table.field1=two_table.field1）的记录，同时会显示左表中（left前的one_table）与条件（one_table=two_table）不符的记录。
<2>右连接
select one_table.fiedl1,two_table.field1 from one_table right join on two_table onetable.field1 = twotable.field1:显示one_table、two_table中相同（符合one_table.field1=two_table.field1）的记录，同时会显示右表中（right后的two_table）与条件（one_table=two_table）不符的记录。

l.union及union all 及 distinct:

（1）union 去除重复
如：select name from second_table union select name from third_table;//将两个表的name显示到一个表中，会去除重复的记录。
(2)union all 不会去除重复
如：select name from second_table union all select name from third_table;//将两个表的name显示到一个表中，不会去除重复的记录。
(3) distinct关键字，用于去除重复的值
如：select distinct uname from user

m.子查询
select field… from tablename where condition (select field from tablename);

DCL(Database Control Language):数据库控制语言。如，分配数据库操作的权限
grant:对非root用户进行权限分配
grant select,insert on my_table.* to ‘chn’@’localhost’ identified by ‘123’;

9.mysql的数据类型
a.字符串类型：
（1）char(M)：字符串。在不设置char的宽度时，默认宽度是1。字段值后的空格在存储时被去掉了。
（2）varchar(M):可变（自动变）宽度的字符串。主要指在存储时的具体长度。
注意：测试两者区别案例
create table test(name char(5),age varchar(5));
insert into values(‘1 ‘,’2 ‘);//注意1和2后各跟了3个空格
select length(name),lenght(age);//显示：1和3
(3)tinyblob:可表示的字符串范围：0-255bytes。
(4)blob:可表示的字符串范围是：0-65535bytes。
(5)mediumblob:可表示的字符串范围是：0-16777215bytes。
(6)longblob:可表示的字符串范围是：0-4294967295bytes。
(7)tinytext:可表示的字符串范围是：0-255字符。
(8)text:可表示的字符串范围是：0-65535字符
(9)mediumtext:可表示的字符串范围是：0-16777215字符
(10)longtext:可表示的字符串范围是：0-4294967295字符
(11)varbinary(M):长度是自定义的。二进制字符串。
示例：insert into forth_table set count=’10’;//count宽度是1，所以插入报错，因为超出了count的宽度1,’10’的二进制是1010,宽度是4。
(12)binary(M):长度是自定义的。二进制字符串。
b.数值类型
(1)tinyint:取值范围是0-255或-128-127
(2)smallint:取值范围是0-65535。
(3)mediumint：取值范围是0-16777215
(4)int(默认11位):取值范围是0-4GB
(5)integer:长度随着操作系统不同而不同
(6)bigint:取值范围，自查。
浮点数
(7)float：范围自查
(8)double：范围自查
定点数
(9)dec(m,d)(存储时以字符串形式存储，更精确)：
(10)decimal(m,d):m是小数总位数，d是小数点后共几位，m精度，d标度
示例：insert into fifth_table set salary=1.345;//salary(4,3)
(11)bit(m):m是几位二进制
示例：insert into fifth_table set rank=2;//rank类型是bit(2);

c.日期时间型(出错所有位以0替代每一位)
date(最小值1000-01-01，最大值9999-12-31):示例：insert into fifth_table set day=’2016-11-21’;//插入2016-11-21时间

datetime()(最小值1000-01-01 00:00:00，最大值9999-12-31 23:59:59):示例：insert into fifth_table set daytimes='2016-12-21 15:00:00';//插 入时间。如果格式与时间格式不符（可以多，但不可以少），直接改为当前时间格式。

timestamp(最小值19700101080001，最大值2038年的某个时刻):在插入时间时，不需要按照格式插入。默认值，不是空，是当前系统时间
示例：insert into fifth_table set yeardaytimes='20161121150707';//插入时间，最后显示格式：2016-11-21 15:07:07

time(最小值-838:59:59，最大值838:59:59):   示例：insert into times_table set times='13:57:00';

year(最小值1901，最大值2155):   示例：insert into times_table set years='2016';

d.enum类型
enum:
示例：
create table ab(gender enum(‘M’,’F’))；
insert into ab set gender=’M’;//gender只能被赋值‘M’或’F’
insert into ab set gender=’F’;
insert into ab values(‘M’,’F’),(‘n’,’l’);//插入了M,F,及M,M记录(不是’M”F’以’M’显示)

类型一些特性：
(1)zerofill(整数):用0补齐整形宽度位数。create table tablename(id int zerofill)

(2)auto_increment(整数)：自动增长。对已经指定的字段，值会自动增长。
<1>create table tablename(id int auto_increment not null,primary key(id));
<2>create table tablename(id int auto_increment not null primary key);
<3>create table tablename(id int auto_increment not null,unique(id));
(3)default:对默认值进行修改，重新指定默认值。
create table db_s(info char default ‘不能为空’); //info的默认值是‘不能为空’，不是null了

10.运算符
a.算数运算符
+：
示例：
insert into sums values(12,13,age+height);//age和height类型是int。
select 1+3;

-：
示例：
insert into sums values(12,13,age-height);//age和height类型是int。

*：
示例：
insert into sums values(12,13,age*height);//age和height类型是int。

/,div:
示例：
insert into sums values(12,13,age div height);//age和height类型是int，第三个字段被插入了0。

%,mod:
示例：
insert into sums values(12,13,age%height);//第三字段被插入0,12%13=12；

b.比较运算符：
=：等于

<>、!=:不等于

示例：
select 1<>2;//显示1，代表true
select 1<>1;//显示0，代表false

<=>：NULL安全的等于
示例：
select null<=>null;//显示1
<:
<=:

:
=:
示例：
select 1<2,1<=2,1>2,1>=2;//显示1,1,0,0

between(存在指定范围):between min and max
示例：
select 11 between 10 and 20;//显示1
select 1 between 10 and 20;//显示0

in(存在于指定集合)：
示例：
select name from web_table where name in(select name from a_b);
//显示web_table当中与a_b name相等的name。

is null(为null)：
示例：
select 1 is null;//显示0,1不是null

is not null(不为null):
示例：
select 1 is not null;//显示1,1不是null

like:
select ’123456‘ like ‘123%’;//显示1，123456含有123

c.逻辑运算符
not或者!:非
示例：select not 0;//显示1，MySQL中，0是假，其他都是真。
and或&&:与
示例：select 44 && 0;//显示0
or或||:或
示例：select 13 or 0;//显示1
xor（异或）：
示例：select 12 xor 12;//显示0,两个相同，为假，即0。两个数不同，为真，即1.

运算符的规定：
:=，||、OR、xor、&&，and，not,between

11.常用函数

a.字符串函数
(1)concat(s1,s2…)：连接s1,s2…为一个字符串
示例：select concat(‘123’,’456’);//显示123456

(2)insert(str,x,y,instr):将字符串str从第x位置开始，y个字符长的子串替换为字符串instr;
示例：select insert(‘123’,1,3,’456’);//456，在第1个位置开始替换，数3个(包括1)，是456.

(3)upper(str):将字符串str转换成大写
示例：select upper(‘abc’);//显示ABC

(4)lower(str):将字符串str转换成小写
示例：select lower(‘ABC’);//显示abc

(5)left(str,x):返回字符串str最左边的x个字符
示例：select left(‘abc’,1);//显示a,可以取超过str长度的字符串。最后是全部显示。

(6)lpad(str,n,pad):如果长度n小于str的长度，显示n个字符(str的字符)。如果长度n大于str的长度，显示原(n-str的长度)的pad字符+str。

(7)rpad(str,n,pad):如果长度n小于str的长度，显示n个字符(str的字符)。如果长度n大于str的长度，显示原str+(n-str的长度)的pad字符。

(8)ltrim(str):去掉str最左边的空格
示例：select ltrim(’ abc’);//显示abc,abc左边的空格被去掉了。

(9)rtrim(str):去掉str最右边的空格
示例：select ltrim(‘abc ‘);//显示abc,abc右边的空格被去掉了。

(10)repeat(str,x):重复字符串str，x次
示例：select repeat(‘abc’,2);//显示abcabc

(11)replace(str,a,b)：用字符串b替换字符串str中所有的字符串a。
示例：select replace(‘abc’,’ab’,’vb’);//显示vbc

(12)strcmp(s1,s2):比较s1和s2大小,如果s1大于s2，返回1，如果s1小于s2，返回-1，如果s1等于s2，返回0
示例：select strcmp(‘a’,’b’);//显示-1
select strcmp(‘b’,’a’);//显示1
select strcmp(‘a’,’a’);//显示0

(13)trim(str):去掉字符串头尾的空格
示例：select trim(’ a ‘);//显示a，前后无空格了

(14)substring(str,x,y):返回从字符串str的x位置起，y个字符长度的子串。
示例：select substring(‘123’,1,2);//显示12

b.数值函数
abs(x):返回x的绝对值，absolute
示例：select abs(-112);//显示112

ceil(x):返回大于x的最小整数值
示例：select ceil(1.5);//显示2
select ceil(1);//显示1

floor(x):返回小于x的最大整数值
示例：select floor(1.2);//显示1

mod(x,y):返回x/y的模
示例：select mod(1,2);//显示1

rand():返回0-1的随机值
示例：select rand();//显示随机0-1的小数

round(x,y):返回参数x的四舍五入的有y为小数的值
示例：select truncat(‘1,19’,1);//显示1.2

truncate(x,y):返回保留x的小数点后y位。
示例：select truncat(‘1.19’,1);//显示1.1

c.日期和时间函数

curdate():返回当前日期
示例：select curdate();//显示当前日期，如2016-11-23

curtime():返回当前时间;
示例：select curtime();//显示当前时间，如14:05:01

now():返回当前的日期和时间
示例：select now();//显示当前日期和时间，如2016-11-23 14:06:08

unix_timestamp(date):返回日期的unix时间戳，从19700101 08:01至当前时间的总秒数。
示例：select unix_timestamp(now());//显示从19700101 08:01至当前时间的总秒数。

from_unixtime(unixtime):返回unix时间戳的日期和时间值
示例：select from_unixtime(unix_timestamp(now()));//显示当前年月日及时分秒，如2016-11-23 14:16:12

week(date)：返回日期date为一年中第几周。
示例：select week(now());//显示当前是一年当中第几周，如2016.11.23是第47周。

year(date):返回日期date的年份。
示例：select year(now());//显示当前的年数，如2016

hour(time):返回time的小时值。
示例：select hour(now());//显示当前的小时数，如14

minute(time):返回time的分钟值。
示例：select minute(now());//显示当前分钟数，如37

monthname(date):返回date的月份名
示例：select monthname(now());//显示当前的英文月份名称,如，November

date_formate(date,fmt):返回按字符串fmt格式化日期date值。