数据库笔记（自己学习过程中整理）

编程语言是实现对内存的操作比较方便，对硬盘数据的操作数据库比较方便

 

一、数据库如何存储数据的

字段，记录，表，约束（主键，外键，唯一键，非空，check,default）

 

二、如何操作数据

insert update delete T_SQL 存储过程，函数，触发器

 

 

三、如何显示数据

select!!!!!!（重点）

 

忘记root密码怎么办

1.只要打开dos窗口//可以用于修改密码（应用于修改密码界面，不同用户的密码修改）

输入set password for user(或者其他用户名也可以)@localhost=password(‘1234’);1234为自己更改的密码，可以直接改，但是找不回原来的密码

这种情况是在进入mysql的情况下可以改

2.用mysqladmin (前提是知道原密码)
格式：mysqladmin -u用户名 -p旧密码 password 新密码 
例子：mysqladmin-uroot -p123456 password 123 

1.在忘记root密码的时候，可以这样 
1.以windows为例： 

1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysql\bin目录。 （先通过services.msc找到mysql服务，然后找到安装地址）
3. 输入mysqld --skip-grant-tables回车。--skip-grant-tables的意思是启动MySQL服务的时候跳过权限表认证。 
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
6. 连接权限数据库： use mysql;。 
6. 改密码：update user setpassword=password("123") where user="root";（别忘了最后加分号）。 
7. 刷新权限（必须步骤）：flush privileges;　。 
8. 退出 quit。 
9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

2. 另一种方法是先停止mysql服务,修改D:/MySQL/MySQL Server 5.0/ 目录下的my.ini文件,在[mysqld]下添加（这个也是屁用没有）
    skip-grant-tables

    启动mysql服务后就可以以空密码登录,之后别忘记修改root密码还有把skip-grant-tables这一句删掉再重启mysql服务. 

 

 

如何找到mysql的bin目录

点开始－＞运行，输入services.msc

在打开的＂服务管理器＂中找到mysql并双击，会弹出来个属性框框，＂常规＂选项卡里面有个＂执行路径＂，从这个路径你就可以看到它的bin目录在哪．

将其复制添加至系统的环境变量path中

就可以在cmd中执行命令-------------！！！！

 

打开数据库

Mysqld

Mysqld –nt打开数据库服务器

 

服务器端，客户端

客户端向服务器端发送命令

发送命令的简写方式

 

以下都是在数据库监视器中进行的操作

--host  -h

--user  -u

--password -p

mysql -uroot -p

mysql -u（user）root(进入root权限) -p进入输密码的界面

进入输密码界面，密码是被星号表示的,要养成这样的习惯

 

全称输入如下图所示

最强能识别到这个程度

Mysql大小写不区分，但是其后的--userroot –password必须要保证小写；

最强能识别到这种程度

cmd中f7是显示之前输入的命令

 

 

show databases;//分号是表示已经执行完毕，打了分号才会执行，否则继续等待输入命令

直到输入;为止

一直等待补全单引号

尾部加\c，表示输入的语句不起作用，让当前的命令清除掉（在语句很长的情况下使用）

Esc直接清除当前命令

按Esc之前

Esc之后

 

选择命令：

Select * from 表名，where nameis “”;

 

建立一个账号：

Grant all on houdunwang.* to “hdw(用户名)”@”localhost”identified by “hdw”  //hdw是新建的用户名,下划线是表示密码设置的，（不能轻易使用，不然会覆盖原来的密码）

只有用mysql –uhdw才能进入这个账号的内容，但是这个账号也只能操作这一个houdunwang的文件，对于其他文件没有操作权限。

数据库 database

 

 

 

具体操作

Show databases； 显示数据库

create database  名字；建立数据库

use houdunwang； 操作具体数据库  加不加分号没影响

Show tables；展示内部的表单情况

Create table 表明要创建的表单名 （id int(10),namestring()）；

Desc+表单名；//显示具体的表的内部结构,只能针对于具体的表，前提要选择一个数据库

 

目前建立的都是表单的模板，具体内容是空的，没有定义具体的数据

Insert命令用于插入数据

Insert into student(name age) value(“”，)；//对应数据类型的插入数据

Mysqldump导出命令

Mysqldump –uroot –p houdunwang（数据库名）> 导出地址：/文件名

结果导出到指定地址并以文件名存储后缀（.sql）

内部存储的是所有执行的sql语句（命令）所有执行过的命令都会存储，用的时候只要将该文件导入就好了

Mysql –uroot –p houdunwang   <  d:/houdunwang.sql//将外部的文件houdunwang导入到表单中，可以多次使用用来导入导出，导入导出的高效化

导入数据，之前要退出，然后重新进入登录界面，才能导入，后面的文件中对应的是sql语句。

或者直接在内部通过source语句进行调用（在mysql内部，用了一个数据库的情况下use student）

Source 文件路径：/文件名  执行外部的一个文件载入一个文件

 

 

 

删除：

Drop  database  数据库名；删除数据库

Drop   table  student(表单名);//要是删除表单，必须要在进入到当前数据库的情况下，删除内部的表单

（或者直接可以drop table student.form//也可以直接删除）

 

 

字符格式：

设定字符集，character set utf8

 

如果库字符集和表单字符集不符合，已最近的规定相关，比如这种情况既是表单里面的设置优先。如果没有指定字符集，就会应用mysql库的字符集，如果这个也没有，就会应用默认字符集，可能就会出错

 

 

数据类型

整形         十进制，十六进制，八进制

浮点数       

字符串型     “”也是字符串型，0个或多个字符组成的

Null  表示不确定的值

 

类型          大小    

Tinyint     1字节/8位    小整数

Smallint     2字节      大整数

Mediumint   三字节    大整数

Int      4字节      大整形

Bigint    8字节/64位       超大整形

Float     4字节

Double   8字节

Decimal（M,N）    准确的值，一般用来保存货币的值

M 是总位数，N是小数位数，如果长度超过，数据会出现问题

 

 

alter table (表名) modify price decimal(10,2);//修改表内部的格式定义。

然后用desc来展示表中具体的类型定义。

！！！分配的时候要考虑内存空间分配预算

 

Tinyint unsigned

指定非负，是插入不了负值的，插入的值得到是0

系统默认是有符号类型的。

 

Delete&&Drop

Delete是删除表中所有的数据的值，即清空值，也可以选择删除单条记录（where）

Drop是销毁整个表；有区别

 

浮点型

 

 

整形数据插入浮点数，会存储为自动进位的浮点数

 

创建一个库并且自定义字符集类型。

 

字符型

char类型和varchar

Varchar解锁要快一点

 

插入

Insert into friendly(表名)

Insertinto friendly(id,name)values(12,”name”),(13,”name2”);//可以用来插入多条记录

查询

1.       主键默认为非空

 

 

1.Select * from tablename;

2.Select version()显示mysql版本信息

3、select database();//显示当前使用的库

4、还可以用于计算

Set a=2;//定义一个变量的值

Select=a*200;

所以select其实可以看成输出语句，不仅限于查询

 

5、主要查询功能

!!>如果用select *只能按表的结构顺序显示，不能自己掌控顺序

会返回整个字段信息，不能有区分的获取信息

（*通配符）会很大的影响处理效率，

 

Select name , sex  from  student,//选取这两列

 

Select * from student where name=”李四”;//有对应的条件约束，就会选取特定的信息并显示出来。先按条件找出对应内容，然后全部输出。

 

Select * from student where name like  ”李%”；//搜索条件是姓名以李开头的所有记录

%代表任意字符以及任意个字符，不仅仅只代表任意一个字符，还可以代表所有以李开头的字符，不限长度。

 

Select id , name , sex from student Wherename like “%玉%”//姓名里面包含玉的(模糊查询)

Select if(sex,”男生”,”女生”)，name,sex from student;

//三目表达式，满足执行前面，否则执行后面。只是在显示的时候如此显示，对于真实内部存储情况没有影响，而且对于原来的用来判断的数据类型没有局限，比如int类型也可以用来装string类型，

 

 

而且可以更改显示情况，便于查看，数据在其中存储情况是以数组形式存储，改成简便的方式，便于存储，

比如select if(id>123,"男生","女生") as"性别",name from friendly where name like "%李%";

可以在分情况的同时，做到选择语句 where，

多个判断where，与（and）,或（or）,

Select concat(” id: ”,id,”name”,name)fromfriendly where name like “%李%”;

Concat将查询结果用字符串的形式组合起来一并展示，与此同时，不影响其后的where判断

这个显示的内容

也是可以改别名的

其别名的时候不能起到关键字，否则无法通过

 

 

更改

Alter table friendly modify id bigint;//更改类型(类型)

Alter table friendly add birthday date; //增加一列，birthday,并定义为date类型

（date类型，年月日结构 , 输入格式“1995/2/23”）;

 

 

更新(update)

Update friendly set birthday=”1995/2/23”;//所有信息都会被改变,会改变整列的值。

但是如果后面加一个条件 where ，则会针对具体情况修改，然后，如果要更改的值与原来的值不相等，就不会有值被更改，提示是，0条记录受影响

有的时候显示数据，只会一部分一部分的选取，不会全部取出，大部分情况都是分页显示，过度数据，服务器开销过度，不划算，服务器读写开销，返回到客户端有带宽开销，会卡顿

【带宽又叫频宽是指在固定的的时间可传输的资料数量，亦即在传输管道中可以传递数据的能力。对于模拟信号而言，带宽又称为频宽，以赫兹（Hz）为单位。例如模拟语音电话的信号带宽为3400Hz，一个PAL-D电视频道的带宽为8MHz（含保护带宽）。对于数字信号而言，带宽是指单位时间内链路能够通过的数据量。带宽在信息论、无线电、通信、信号处理和波谱学等领域都是一个核心概念。】

 

Select *from friendly limit 2(限定选俩个，一般要限定排序然后选取)order by id desc(由高到低)/asc(由低到高);（默认情况下是由低到高排序，即asc）

 

 

 

特殊用法

 

因为年份越小年龄越大，所以逆向思维

不从开头开始选取：

Limit a,b;//注意！！(0代表第一条记录，1代表第二条记录)

A代表从哪个开始，包括那一个，然后b代表取几条记录

Limit 1,1;查找第二条记录；

如果在查找的时候，出现俩条数据值一样，但是不全在选取范围内的，就可以吧选取条件定义为where<=/>=(刚好卡在线上的值limit n,1);

括号内部需要临时定义一个表

 

 

 

 

 

 

 

 

select * from friendly where id<=(selectid from friendly order by id limit 1,1);

 

如果选取数据的时候，需要得知有那些数据，但是要过滤掉重复的数据（distinct）

distinct需要在参考的参数之前，否则会出现警告，中间也可以添加as命令，更名

年份中根据每个成分进行分别索引

Select year(birthday)………

Select month(birthday)…….

Select day(birthday)……..

这是date的一种特殊方法

一般用法固定顺序       操作 +名 + 类型

 

 

As什么时候都能用来改名，但是必须要放在from friendly前面，

 

 

 

 

 

 

 

 

字符串

二进制(保存图像，声音，图片等二进制数据)把地址以字符串保存到表里面，而不是将文件保存到表里面，

也可以将文件以二进制流的形式存进去

Binary,varbinary,blob

与字符集是无关的

用法：Binary(n)，可以存n个字节

     二进制中其他

Image 一般把图像当成普通文本来显示，会显示一堆乱码

非二进制（有字符集特性 utf8gbk gb2312 big5字符校对规则）只有这个才有字符集

Char,varchar（可变长度）,text，

 

Varchar(n),可以存n个字符。

 

特有的方法，判断所占长度：

 

length(varchar)//按字节计算

char_length(varchar)//不考虑字节，按字符计算，有几个字符

如果将二进制转成非二进制会产生乱码（其上带了数据库附带的属性）

 

字符集(存储字符的仓库)

Gbk（21000多个汉字，繁体简体都融入其中，以及一些常用的符号）--从win98等等开始用的都是这个规范。----2个字节

Gb2312（简体，6700多个汉字，日文，等等）每一个占俩个字节的空间

Big5(繁体字符集)/大五码----2个字节

Utf-8（基于unicode）万国码，涵盖多种语言，长度可变（1-3个字节）字母占一个，汉字最多占三个

Unicode（万国码） 国际标准化组织做的涵盖所有语种，所有符号的编码方案

Show character set;//显示字符集

 

utf-8,在数据库中默认是utf8的写法

Show Character set//显示字符集；

Character set utf8;//具体设置，只需跟在要修改的之后即可

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Show还能展示创建过程用的所有语句

字符集校对规则

类型中

-ci是表示不区分大小写的规则，字符集规则是用于比对，对齐，所以比较的时候不区分大小写。

Show collation;

显示所有的字符集校对规则

Yes表示默认执行。

Collate 用来指定校对规则

比如 collate utf8_bin

 

区分大小写的区别，因为区分不同，所以的储户的满足条件的结果也不同

 

Name2 不区分大小写

Name 区分大小写

会对结果产生很大的影响

 

插入俩个字的时候会产生明显的不同

这个时候已经产生了一个警告；

Binary(3)代表可以存三个字节，是按照字节的单位存储

Varchar(3)代表可以存三个字符，超出一样也会无法显示

如果制定了校对规则(collate)，没有指定字符集，使用校对规则的字符集

如果没有指定校对规则，也没有指定字符集，依据表的字符集与校对规则

如果表也没有指定，依据库的字符集

如果库也没有指定字符集

 

一般建表的时候就要制定字符集,以确保没有设定默认的内容的时候能执行表的默认设置，这样不会出错

库指定的字符集<-表指定的字符集<-校对规则使用的字符集

 

 

 

 

Show collation;//显示字符集的默认校对规则

Show variableslike “%character%”;//显示变量，如。。。。

 

mysql> SHOW VARIABLES LIKE'character_set_%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client |latin1 |

| character_set_connection |latin1 |

| character_set_database |latin1 |

| character_set_results |latin1 |

| character_set_server |latin1 |

| character_set_system | utf8|

| character_sets_dir |/usr/share/mysql/charsets/ |

 

 

Latin1是有的服务器配置的默认字符集;(不能显示中文)西欧字符集

如果表的字符集，库的字符集，字段的字符集都没有定，就会采用数据库默认的字符集（latin1）

没有汉字对应的数据库，所以也就无法显示出来

Character_set_database  //当前用的是哪个库，就对应显示哪个字符集，会随之改变

 

Character_set_system;//存放数据库名字的字符集，所有字段的功能都是通过它指定的，默认是utf-8

 

 

 

客户端的字符集，如果和mysql服务器的字符集不同

客户端发送信息时，会附带其字符集的属性，

先转换成连接字符集character-set-connection（连接字符集）然后转成和表中相同的类型例如（附带utf8属性），然后类型相同之后，就可以互相进行比对

最后得到数据之后，还要转化成原来的字符集，中间的过渡是character-set-result然后返回

客户端-〉character-set-connection->服务器->character-set-result->客户端;

 

也可以直接用set names(表名) utf8;//来更改类型，但是企业内部不提倡这种用法

Set names utf8;

会同时改变

这三个都会被改成utf8

Character_set_client 客户端

连接集(character_set_connection)要和服务器端保持一致

结果集(character_set_result)要和客户端保持一致

 

客户端一般没什么问题的话都是默认gbk，show显示出来以default的形式展示

 

 

 

注意！

Showcreate table(表名)&&desc(表名)；

Desc展示效果只能展示到我们在表中能看到的地方

Show语句能展现一个表中具体的各个属性，比如character，字符集属性

 

 

 

 

 

 

 

Quit/exit退出命令

 

Tinyint(1)default 0,//1或者0,      default后面的内容.是否则下一定会执行的内容

比如create database new default character set utf8

 

Auto_increment 自增

 

? contents  //查看帮助可以做什么

? data types  //查看数据类型,在mysql workbench中实现不了！！！！！！！

? int//  查看int类型的具体类型

 

? 后面要有空格，不然识别不了

Zerofill格式，将空余的位数用零填充   (eg:000000001)

 

TRUNCATE TABLE table_name   完全清空一个表。

 

删除表这个行为是不能回滚的

rename table ... to ...

支持一次重命名多个表。

RENAME TABLE old_table1 TO tmp_table,

new_table TO old_table,

tmp_table TO new_table; －完成表名交换。

 

当查询需要用到的条件是从另一个表中得到的结果，用到的关键字有in,not in,=,!=,exists,not exists.

当子查询条件唯一时，可以用=代替in

某些情况下，子查询还可以转换成表连接

多个查询语句之间，可以通过union,union all将查询得到的结果合并在一起，

Union是将union all得到的结果去掉重复(distinct)后显示

Select a from A unionselect a from B

 

 

 

使用auto-increment属性自增长的列，必须要定义为notnull,以及附加属性primary key/unique

因为序列一般都是正数，所以定义为unsigned使得全部为正数，且增大取数范围

可通过alter auto-increment来修改从哪开始增长

 

 

Default用于规定该字段的默认值

 

数据类型

日期类型

Date

Datetime

Timestamp（timestamp属性的字段，只能有一列被定义为current_timestamp，否则会报错）

Time

Year

 

Enum&&Set

Enum类型   枚举，导入的数据每次只能导一个，而且所有数据都只能在该范围之内

Set 类型   每一次可以导入不止一个的数据，导入的数据可以是列举范围的随机组合

常用函数

字符串函数

Concat()   连接字符串

Insert(str,x,y,instr)   将字符串str从x开始，y个字符串的位置 替换成instr

Lower(str) 小写

Upper(str) 大写

Left(str,x)

Right(str,x)

Lpad(str,n,pad)

Rpad(str,n,pad)

LTRIM(str)

RTRIM(str)

REPEAT(str,x)

REPLACE(str,a,b)   用b替换所有str中出现的a

STRCMP(s1,s2)比较两个字符串

TRIM(str)

SUBSTRING(str,x,y) 返回从字符串strx位置起y个字符串的字串

 

 

所有替换的字符串，绝对不会超过原来被替换的字符串的长度

数值函数

Abs(x) 返回x的绝对值

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

FLOOR(x) 最接近x的大于x的整数值

MOD(x,y) 

RAND() 返回

ROUND(x,y) 四舍五入的有y位小数的值

TRUNCATE(x,y)  直接截断x为y位小数

 

 

 

日期和时间函数

Curdate() 返回当前日期

Curtime() 返回当前时间

NOW() 返回当前的日期和时间

UNIX——TIMESTAMP(date)

FROM_UNIXTIME

WEEK

YEAR

HOUR

MINUTE

MONTHNAME

DATE_FORMAT    格式化日期值

DATE_ADD

DATEDIFF  返回起始时间和结束时间之间相隔的天数

 

流程函数

IF(value,t,f)   是真的（返回true）错的返回false

IFNULL(value1,value2) 

CASE WHEN[value1]THEN[value2]…..ELSE[default]END

CASE[expr]WHEN[value1]THEN[result1]….ELSE[default]END

 

 

其他流程函数

Select+

DATABASE()

VERSION()        返回数据库版本

USER()      返回登录用户名

INET_ATON(IP)         返回IP地址

INET_NTOA(num)    返回数字代表的IP地址

PASSWORD(str)   返回str的加密版本(41位长的字符串)

MD5(str)

 

 

 

Mysql Utilities

Mysqldbcompare --server1=user:password@host1:port–server2=user:password@host2:port  db1:db2;

 

比较两个数据库之间的区别

Mysqldiff

比较两个对象的定义，比mysqldbcompare粒度更小

Mysqlcopy

Mysqldiskusage

 

开发

存储引擎

命令

Show engies\G

Show variables like ‘have%’

Disabled表示是支持这个引擎的，但是在数据库启动的时候被禁用

存储引擎

MYISAM

INNODB

MEMORY

MERGE

TokuDB

权限问题可以通过先构造一个对象（具有部分权限），然后将这个对象应用于不同用户，这样能简化操作

 

 

 

 

 

 

错误代码提示

1．1248. Every derivedtable must have its own alias

Selectdistinct(LAC_CI) from (Select LAC_CI from `dtdb_sh_test`.dt_measurment wherecallid='04021002ms31310379260576' union Select LAC_CI from `dtdb_sh_test`.dt_measurmentwhere callid='04021002ms31310379260576')

 

在执行稍微复杂查询的时候，

MySQL有时会遇到如下报错情况：

Error Code :1248
Every derived table must have its own alias

 

解决方法：

在执行复杂查询处为你的表任意起个别名就可以了。

Selectdistinct(LAC_CI) from (Select LAC_CI from `dtdb_sh_test`.dt_measurment t wheret.callid='04021002ms31310379260576' union Select LAC_CI from`dtdb_sh_test`.dt_measurment  d where d.callid='04021002ms31310379260576')as t

 

此处 as  t  虽然无任何作用和意义，但是SQL语句是万万少不得的。