nodejs之MAC上终端操作mysql数据库（一）

navicat的操作：http://www.navicat.com.cn/manual/online_manual/cn/navicat/mac_manual/#/con_ssh_settings

参考：https://www.cnblogs.com/sbj-dawn/p/6905930.html

https://www.cnblogs.com/gaoguofeng/p/5848347.html

http://blog.csdn.net/a909301740/article/details/62887992

MacOS版下载地址：https://dev.mysql.com/downloads/mysql/

PHP 数据库mysql（一）命令行操作数据库,windows版：http://blog.csdn.net/u011146511/article/details/54293767

php数据库mysql（二）增删改查，Windows版：http://blog.csdn.net/u011146511/article/details/54606490

PHP数据库 mysql(三)事务、存储函数，存储过程，触发器、权限设置：http://blog.csdn.net/u011146511/article/details/54865354

mysql常用的函数：http://blog.csdn.net/sugang_ximi/article/details/6664748

-------------删除卸载mysql数据库---------

 

卸载MySQL，终端执行：

• 打开终端窗口
• 使用mysqldump备份你的数据库将文本文件!
• 停止数据库服务器
• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• edit /etc/hostconfig and remove the line MYSQLCOM=YES-----------这一句可以不执行
• rm -rf ~/Library/PreferencePanes/My*
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*
• sudo rm -rf /private/var/db/receipts/*mysql*

 

查看MySQL服务器配置信息 -------

 

参考：show variables like xxx 详解mysql运行时参数http://blog.csdn.net/mooncarp/article/details/51787694

 

1.*******************启动Mysql应用程序

查看mysql的安装位置：ps -ef|grep mysql

从mysq了官网下载mysql服务安装完成后的路径如下：

启动MySQL服务 sudo /usr/local/MySQL/support-files/mysql.server start

停止MySQL服务 sudo /usr/local/mysql/support-files/mysql.server stop

重启MySQL服务 sudo /usr/local/mysql/support-files/mysql.server restart

2.*************连接mysql***********

 mysql 命令。      -u 后面接用户名

    root超级管理员，拥有最高权限。

    -p 后面接密码

打开终端输入如下命令：

 /usr/local/mysql/bin/mysql -u root -p                         或者

/usr/local/mysql/bin/mysql -u root -p -h127.0.0.1       或者

设置环境变量或者别名后，可以使用  mysql -u root -p

其中root为用户名。这时会出现如下命令：Enter password:  ******(，也可以不输入密码直接回车；这个密码mysql服务安装好后会分配一个密码，后期可以自己改)

这样就可以访问你的数据库服务器了。

断开数据库连接：quit或者exit或者\q；

 

****设置别名：

1)alias mysql=/usr/local/mysql/bin/mysql

2)alias mysqladmin=/usr/local/mysql/bin/mysqladmin

设置了别名后就可以用别名代替原来的一长串；这样设置了后，如果重启终端，就没效果了；想要长期有效果.就要修改文件.让终端启动的时候加载：

在终端输入 : vim ~/.bash_profile

后再这个文件中添加下面两句话：

alias mysql=/usr/local/mysql/bin/mysql

alias mysqladmin=/usr/local/mysql/bin/mysqladmin

保存退出,重启终端或者开新窗口即可（

这要主要看是用什么程序来编辑文件。

如果是vi，则：Esc 退出编辑模式，输入以下命令：

:wq  保存后退出vi，若为 :wq! 则为强制储存后退出（常用）

:w    保存但不退出（常用）

:w!   若文件属性为『只读』时，强制写入该档案

:q    离开 vi （常用）

:q!   若曾修改过档案，又不想储存，使用 ! 为强制离开不储存档案。

:e!   将档案还原到最原始的状态！

）
 

 $ /usr/local/mysql/bin/mysql -u root -p  
Enter password:   
但每次都要输入这么一长串字符，也过于繁琐，需要用alias命令行去简化
$ vim ~/.bash_profile   
添加内容：
alias mysql='/usr/local/mysql/bin/mysql';  (注意等号前后不要有空格)
要使~/.bash_profile文件生效，我们必须使用 source 命令：
$ source ~/.bash_profile  
以后我们使用时，就不需要再输入那么多麻烦的指令了。

或者如下操作配置环境变量，设置mysql的路径：

对于如何快捷访问mysql的方法各不相同，繁简各异，此处本人采用自认比较简单的配置方法：

配置用户文件.bash_profile （如果没有此文件，以下操作，会自动创建）中添加如下配置即可，

打开Terminal，输入：vim .bash_profile

进入编辑界面，在英文输入法下按 i，添加以下内容：export PATH=${PATH}:/usr/local/mysql/bin

按下esc键，输入 :wq 保存退出

使配置文件立即生效：source .bash_profile

 

3.*************修改或设置mysql连接密码*******（cihQh-c+m1pk）

**打开终端，输入sudo -i  命令,并回车，切换ROOT用户；

**进入mysql安装目录的bin目录，mysql默认安装目录为/usr/local/mysql。输入命令  cd /usr/local/mysql/bin/并回车；

**输入./mysqladmin -u root -p password 123456命令，注意123456为您的新密码。命令输入之后回车；

**此时为要求我们输入mysql以前的密码，即mysql初始化的密码。输入好之后回车，弹出如下图，如果内容不同，则没修改成功:

 

或者使用：alter user user() identified by "123456"; 

***********修改密码也可以直接修改mysql数据库的user表：注意mysql5.7没有password字段了，换成了authentication_string；

update mysql.user set authentication_string=password('123456') where User='root' and Host='localhost';

如果报错，尝试一下下面的方法：越过权限直接进入数据库；

1）关闭MySQL服务，Stop MySQL Server，running变为stopped

2）然后，打开Terminal，输入：sudo su回车

输入你当前mac用户的管理密码，回车

./mysqld_safe --skip-grant-tables --skip-networking&回车

此时mysql会启动，这个命令是让我们暂时越过权限机制，不需要password，直接登录MySQL

或者：

1.停止mysqld服务

2.编辑配置文件

/etc/my.cnf或者是/etc/mysql/mysql.conf.d/mysqld.cnf

在配置文件中添加配置项

skip-grant-tables

3.启动mysqld服务

4.执行mysql

注意：正式的应用时要把这一行代码去掉，为了安全；

3）command+T再打开一个Terminal，不要关闭之前的Terminal，

root登录：mysql -u root回车

修改root密码：update mysql.user set password=PASSWORD('你的password') where User='root';

刷新权限：flush privileges;

OK,至此，问题搞定!

 

4.***********MYSQL的使用*******

 

===navicat视图结构====

可以创建多个数据库连接，也就是可以连接多个不同地址的服务器；

每一个连接下面可以创建多个数据库；

***新旧密码机制导致链接不上navicat的解决方法（报错提示Client does not support authentication protocol requested by server; consider upgrading MySQL client）：

USE mysql;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ‘1234567890’;

FLUSH PRIVILEGES;

----------------------------------------数据库的操作-------------------------------------------------------

=====查看数据库服务器上面有哪些数据库==========

   SHOW DATABASES;(大小写都可以，规则是大写，最后的分号不能忘记，分号相当于结束符号)；

======数据库的命名规则========

   关键字与函数名称全部大写

   数据库名称、表名称、字段名称全部小写

   SQL语句必须以分号结尾

 

===创建数据库===

 

create database firstDB;   //firstDB是数据库名

===alter修改数据库====

默认创建的数据库默认不支持中文字符，如果我们需要它支持中文字符，则将它的编码设置为utf8格式：

 

 ALTER DATABASE firstDB  CHARACTER SET UTF8;

===使用数据库==

use firstDB;

====查看当前使用的数据库====

 

select database();

===drop 删除数据库===

drop database firstDB;

 

-----------------------表的操作------------------

修改字段，字段操作很多：新增(add)、修改(modify)、重名(change)、删除(drop)

====创建表===

创建数据库-》use数据库-》创建表：

create table PEOPLE ( ////PEOPLE 是表名

ID int AUTO_INCREMENT PRIMARY KEY,

 NAME varchar(20) not null,

 AGE int not null,

 BIRTHDAY datetime);

====create 利用已有数据创建新表===

create table newTable select * from PEOPLE;//PEOPLE是已经有的标；newTable是新创建的表；

====复制表====

create table 新表名 like 被复制表名; 

=====show 显示当前数据库所有的标====

show tables;

====desc 查看表结构===

desc PEOPLE; //PEOPLE 是表名

 describe 表名;       show columns from  表名;

======alter 修改表结构（增、删、改）=====================

默认创建的表不支持中文字符，所以需将表编码设置为utf8：

=====修改自增长======

加入自动增加:

alter table User modify id bigint auto_increment;

查询firstDB数据库User表的 auto_increment：

select auto_increment from information_schema.tables where table_schema='firstDB' and table_name='User';

修改firstDB数据库User表的auto_increment；

alter table firstDB.User auto_increment=1;

修改原有字段名称及类型：
ALTER TABLE bulletin CHANGE uid username VARCHAR(50) NOT NULL DEFAULT '';

添加新字段：

alter table bulletin add citycode varchar(6) not null default 0; # 城市代码

1.创建数据库时设置编码

create database test character set utf8;

2.创建表时设置编码

create table test(id int primary key)DEFAULT charset=utf8;

3.修改数据库编码

alter database test character set utf8;

4.修改表默认编码

alter table test character set utf8;

5.修改字段编码

alter table test modify col_name varchar(50) CHARACTER SET utf8;

---------添加索引方法---------

1.添加PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (

`column`
)

3.添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE `table_name` ADD FULLTEXT (
`column`
)

5.添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

== alter 添加字段===

alter table  表名  add  字段名  数据类型[位置];

ALTER TABLE user  ADD COLUMN paypassword  VARCHAR(100) DEFAULT NULL;//****这句有效

alter table PEOPLE add star BOOL;//star是添加的字段，在MySQL里，布尔类型会自动转换为tinyint(1)类型。

==alter 修改字段类型====

alter table PEOPLE MODIFY star int;

===删除字段====

  alter  table  表名   drop  [column] 字段名;    

alter table PEOPLE DROP column star;

==rename 重命名表名===

rename命令格式：rename table 原表名 to 新表名;

RENAME TABLE PEOPLE TO NEW_PEOPLE;//NEW_PEOPLE是新的表名称；

===null or not null修改表字段允许为空或不允许为空：===

把 PEOPLE 表的 AGE 字段设置成“允许为空”，即插入记录时这个字段可以不录入。否则相反。

alter table 表名  modify  字段名  数据类型  [位置];

ALTER TABLE PEOPLE MODIFY AGE INT(3) NULL;

alter table 表名 alter column 字段名 drop default; (若本身存在默认值，则先删除)//****这句有效

alter table 表名 alter column 字段名 set default 默认值;(若本身不存在则可以直接设定)//****这句有效

====旧字段修改成新字段名===

 alter table 表名  change  旧字段名  新字段名  数据类型 ;

====删除表======

删除数据表： drop  table  表名1，表名2，……;         一次性删除多个表

------------------------------数据的操作（增删改查）--------------------------------------------

*******insert into添加数据***********

 insert into PEOPLE VALUES (null, 'Anny', 22, '1992-05-22');//向表PEOPLE中出入一组数据，小括号中的数据要和创建表时的字段对应；

 

1、insert into
insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错。

 

2、insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉。insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据。
INSERT IGNORE INTO books (name) VALUES ('MySQL Manual')

 

3、replace into
如果存在primary or unique相同的记录，则先删除掉。再插入新记录。如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空。
replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样。
REPLACE INTO books SELECT 1, 'MySQL Manual' FROM books

 

4、on duplicate key update
当primary或者unique重复时，则执行update语句，在原有记录基础上，更新指定字段内容，其它字段内容保留。如update后为无用语句，如id=id，则同1功能相同，但错误不会被忽略掉。
例如，为了实现name重复的数据插入不报错，可使用一下语句：
INSERT INTO books (name) VALUES ('MySQL Manual') ON duplicate KEY UPDATE id = id;

 

5、insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件。例如：
INSERT INTO books (name) SELECT 'MySQL Manual' FROM dual WHERE NOT EXISTS (SELECT id FROM books WHERE id = 1)

 

注：如果使用的是insert into 发现重复的会报错，而insert ignore  into 发现将要插入的数据行中包含唯一索引的字段值已存在，会丢弃掉这行数据，不做任何处理；REPLACE发现重复的先删除再插入，如果记录有多个字段，在插入的时候如果有的字段没有赋值，那么新插入的记录这些字段为空。

 

===delete 删除数据：=======

 

 

delete from PEOPLE where name = 'one';//删除了name是one的记录

======update 修改数据：=====

update PEOPLE set name='Calvin' where name = 'two';//修改name字段的值

===select表查询=======

select * from PEOPLE; //**select查看表中的所有数据,*代表所有字段。

select NAME, AGE, BIRTHDAY from PEOPLE; //select <字段名,字段名,…> from <表名>。

 

------------------视图的操作------------------------------------

视图是从数据库里导出一个或多个表的虚拟表，是用来方便用户对数据的操作。

当视图数据有变化时（增、删、改），真实的表数据也会随着改变。也就是说，对视图的操作就是对表的数据，所以我们可以把视图当作表。

=======创建视图===

 

CREATE VIEW PEOPLE_VIEW (

 NAME, AGE)

 AS SELECT NAME, AGE FROM PEOPLE;//从PEOPLE表中拿出NAME, AGE两个字段创建视图PEOPLE_VIEW，其中视图包含了NAME, AGE两个字段；

 

=======查看视图=======

 SELECT * FROM PEOPLE_VIEW;//查看视图所有数据

 

 desc PEOPLE_VIEW;//查看视图结构；

 

======创建或替换原有视图=====

 

CREATE OR REPLACE VIEW PEOPLE_VIEW(PEOPLE_ID,PEOPLE_NAME,PEOPLE_AGE) AS SELECT ID,NAME,AGE FROM PEOPLE;//新视图的三个字段分别从表PEOPLE中的ID,NAME,AGE字段获取；

 

=====删除视图=======

 

DROP VIEW PEOPLE_VIEW;

 

 

————————----------—复合操作———————----------

 

 

====查询数据完整语法====

select 字段名/*

from  数据源

[where 条件子句]

[group by 子句]

[having 子句]

[order by 子句]

[limit 子句];

 

 

==去除重复的记录======

select distinct *  from  my_student;

 

=====插入多条数据======

insert into my_student values(null,'itcast01','张三','男'),(null,'itcast02','李四','男'),(null,'itcast03','王五','女'),(null,'itcast03','男');

 

======字段别名======

select id,number as 学号，name as 姓名, sex 性别 

from my_student;

 

=====where子句=====

使用 AND 或者 OR 指定一个或多个条件。

SELECT 字段1，字段2，... FROM 表1, 表2，... WHERE condition1 AND 或者OR condition2.....

where子句中的操作符：

 

=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

 

原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件：从磁盘取出一条记录，开始进行where判断，判断的结果如果成立保存到内存，反之放弃。

 

---找学生id为1，3，5的学生

select  *  from  my_student  where id =1 || id =3 || id =5;

或者select * from my_student where id in(1,3,5);

======like子句===========

 

SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

 

• 可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索

一般like与where一起使用；

SELECT * from 表名 WHERE name LIKE '%COM';//查找表中所有name是COM结尾的记录；

====== UNION==========

 UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

语法格式：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数

• expression1, expression2, ... expression_n: 要检索的列。

• tables: 要检索的数据表。

• WHERE conditions: 可选， 检索条件。

• DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。

• ALL: 可选，返回所有结果集，包含重复数据。

 

 

 

=======group by  字段 [asc/desc];   分组  根据某个字段进行分组，相同的放一组，不同的分到不同的组。统计的是数据，不是记录

--按性别分组

select * from my_student group by sex; 这是绝对错误的，分组是为了统计数据，按分组字段进行数据统计

=======SQL提供了一系列统计函数======

Count();  统计分组后的记录数，每一相同的组有多少行记录

Max();   统计每组最大的值

Min();   统计每组最小的值

Avg();   统计每组的平均值

Sum();   统计每组的和

---身高高矮，平均年龄，和总年龄

select sex,count(*),max(height),min(height),avg(age),sun(age)   from my_student  group by sex;

注意单独使用group by而不使用统计函数，是没意义的!，通常能是一起使用；

多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段分组。

 

=====having子句========

与where子句一样进行条件判断，一般是group by……having……组合； 而不是group by……where???

进行数据统计时，是数据进入内存之后，会进行分组-》统计-》having条件判断。而where是针对从磁盘读取数据时进行判断，此刻还没分组，进行统计就判断了。所以用group by……having……组合；

---求出所有班级人数大于等于2的学生人数

 

====order by 子句=====

order by根据某个字段进行升序或降序排序，依赖校对集

基本语法： order by 字段名 [asc/desc];  asc升序 可不写      desc降序

 

=====limit子句是一种限制结果的语句

1）用来限制数量

--查询学生前两个   select * from my_student limit 2;

2)限制起始位置  limit 起始位置，长度

--查从编号4开始，询找两个学生

select * from my_student limit 4,2;

 

 

======内连接，外连接（左外连接、右外连接），自然连接，交叉连接====

连接查询参考：https://www.cnblogs.com/qiuqiuqiu/p/6442791.html

====内连接，包括相等联接和自然联接。 从左表中取出每一条记录，和右表中所有的记录进行匹配，匹配必须是左表中与右表中某个条件相同，最终会保留结果，否则不保留。----就是交叉连接加上条件后的结果；

基本语法：  左表 [inner] join 右表  on 左表.字段 = 右表.字段;

select * from my_student inner join my_class on my_student.C_id = mu_class.id;

 

 

2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
1）

左外连接：左表为主，然后每条记录与右表进行连接，不管能不能匹配的上，左表都会保留。能匹配，右表某条记录保留，不能匹配，某条记录置为NULL，最终记录数至少不少于左表已有的记录。

基本语法：  左表  left join  右表  on  左表.字段 = 右表.字段;

左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       
2）右外连接：RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）全外连接：FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。   

3、交叉联接   ---就是不设定任何条件的链接结果
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。    

 a表     id   name      b表     id   job   parent_id   
              1   张3                   1     23     1   
              2   李四                 2     34     2   
              3   王武                 3     34     4       
  a.id同parent_id   存在关系   

--------------------------------------------------    
 1） 内连接   
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
    
  2）左外连接   
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null   

 

 3） 右外连接   
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  null                        3     34     4   
    
 4） 完全外连接   ---mysql不支持
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   

  结果是     
  1   张3                  1     23     1   
  2   李四                 2     34     2   
  null               　　  3     34     4   
  3   王武                 null

 

=======查看数据库相关操作的过程======

 

show create table people； 可以创建表的代码以及存储引擎，同时可以看到外键的设置。

------------————————--约束————————————————————

===主键约束====

主键是表的标志列，在mysql中支持将一个或多个字段作为一个主键来使用。关系数据库依赖于主键，主键在物理层面只有两个用途：
唯一的标志一行记录作为一个可以被外键引用的有效对象。 2>设置主键：
创建主键分为单字主键和复合主键

单字主键创建只需在字段后边添加primary key；

******在创建表的时候添加单字主键：age int primary key;

*****在创建表的时候添加复合主键：primary key（age，name）；

********如果有一张表，创建的时候没有添加主键，现在要给它添加主键：
alter table people add primary key(字段名称);

****修改主键：修改主键有两种可能，一种是表中有主键，需要将主键修改在其它字段上，另一种是表中没有主键，需要修改字段类型，使其成为主键。类型的修改要使用modify关键字。
对于已经设置了主键的表，主键的修改不能够直接执行。必须先删除原有主键，再重新设置主键。主键的删除只是删除了指定字段或字段组的主键约束，并不能够删除字段或字段组，一个表中只能有一个主键。

 

前边建的book表有主键字段组（bnum、typeid），现将表中的原有主键删除，将字段bnum修改为主键。

 

**** 删除主键：alter table people drop primary key;

*****添加主键：alter table people add primary key(字段名称);

 

============外键约束==========

 

外键记录了表与表之间字段的联系。外键将不同表的字段关联起来，使这些数据在修改、删除时有着关联，在数据操作时维护这些数据完整性。
外键有两个作用：

让数据自己通过外键来保证数据的完整性和一致性能够增加数据库表关系的可读性

 

外键的定义还要遵循以下的情况：所有的表必须是InnoDB型，它们不能是临时表，mysql中只有InnoDB类型的表才支持外键所有要建立外键的字段必须建立约束对于非InnoDB表，foreign key 子句会被忽略;

 

alter table people add [constraint 外键名] foreign key [id] (主表中的一个作为外键的字段, …) references 另一张表 (另一张表的元一个字段, …)[on delete {cascade | set null | no action | restrict}][on update {cascade | set null | no action | restrict}]---------后面两个方括号是可选的，花括号里面的值是方括号的可选值；

 

可以看到mysql对创建外键要求严格，精确到字段的类型和长度。
对于外键最好采用 on delete restrict 和on update cascade的方式。
cascade：外键表中外键字段值会跟随父表被跟新，或所在的列会被删除。
no action：不进行任何关联操作
restrict：restrict相当于no action，即不进行任何操作。拒绝父表修改外键关联列，删除记录。
set null：在父表的外键关联字段被修改和删除时，外建表的外键列被设置为空（null）。

对于数据的添加，子表的外键列输入的值，只能是父表外键关联列已有的值，否则出错。
**********现有刚刚创建的表student和book。把book中typeid字段对应student表中的sid字段：

alter table book add constraint typeid foreign key(typeid) references student(sid) on delete ;

 

主表：

CREATE TABLE class(

cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,

cla_name VARCHAR(30) NOT NULL UNIQUE

);

从表：

CREATE TABLE students(

stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,

stu_name VARCHAR(30) NOT NULL,

stu_score FLOAT(5,2) DEFAULT 0.0,

cla_id INT(10),

CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id)#添加外键约束

);

也可以这样添加：

ALTER TABLE students ADD CONSTRAINT FK_CLA_ID FROEIGN KEY(cla_id) REFERENCES class(cla_id);

 

**********删除外键：******
alter table people drop foreign key 外键名；

 

一张表的外键可能不止一个，删除多个外键可以重复使用上述语句。

 

#外键中的级联关系有以下几种情况：

ON DELETE CASCADE 删除主表中的数据时，从表中的数据随之删除

ON UPDATE CASCADE 更新主表中的数据时，从表中的数据随之更新

ON DELETE SET NULL 删除主表中的数据时，从表中的数据置为空

#默认 删除主表中的数据前需先删除从表中的数据，否则主表数据不会被删除

CREATE TABLE students(

stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,

stu_name VARCHAR(30) NOT NULL,

stu_score FLOAT(5,2) DEFAULT 0.0,

cla_id INT(10),

CONSTRAINT 外键名 FOREIGN KEY(主表字段) REFERENCES class(从表字段) ON DELETE CASCADE

);

CREATE TABLE students(

stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,

stu_name VARCHAR(30) NOT NULL,

stu_score FLOAT(5,2) DEFAULT 0.0,

cla_id INT(10),

CONSTRAINT 外键名 FOREIGN KEY(主表字段) REFERENCES class(从表字段) ON UPDATE CASCADE

);

CREATE TABLE students(

stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,

stu_name VARCHAR(30) NOT NULL,

stu_score FLOAT(5,2) DEFAULT 0.0,

cla_id INT(10),

CONSTRAINT 外键 FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE SET NULL

);

注：插入数据时，先插入主表中的数据，再插入从表中的数据。

    删除数据时，先删除从表中的数据，再删除主表中的数据。

 

======非空约束=======
被设置了非空约束的字段，在添加数据的时候必须要有数据。非空约束限制字段中的内容不能为空，但可以是空串或0。
在创建表的时候直接在字段类型后边添加 not null 或null，即可设置为字段非空或者空。
**********把已有的表某个字段设置为非空：

 

alter table people modify 字段名 字段类型 not null;

alter table people modify name varchar(20)  not null;

========唯一性约束==========================

唯一性约束（unique constraint）要求添加该约束的列字段的值唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或多列不出现重复值。

创建唯一约束字段：

一个表可以有多个唯一约束，一个约束可以只有一列，当然也可以有多列。

*******在建表时，可以直接创建唯一约束，可以直接在字段的数据类型后边直接添加unique关键字，

 

CREATE TABLE tab( 

fa int NOT NULL unique, 

fb int NOT NULL, 

)

 

*****也可以在所有字段定义之后使用[constraint <约束名>] unique(<字段名>)

 

CREATE TABLE tab( 

fa int NOT NULL, 

fb int NOT NULL, 

CONSTRAINT 约束名称 UNIQUE (fa ,fb ) //可以使用UNIQUE (fa ,fb );如果要为UNIQUE约束分配一个指定的名称，就要使用CONSTRAINT子句

)

 

*******对于已经存在的表和字段，添加唯一约束要使用add关键字：

 

alter table table_name add unique(字段列表);

 

 

*********其他方式添加唯一约束“

 

ALTER TABLE 表名 MODIFY 字段 INT(10) UNIQUE;

ALTER TABLE 表名 CHANGE 旧字段 新字段 INT(10) UNIQUE;

ALTER TABLE 表名 ADD UNIQUE(字段);

ALTER TABLE 表名 ADD UNIQUE KEY(字段);

ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE(字段);

ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE KEY(字段);

**************删除指定名称的约束

 

 

mysql中，一个字段的所有约束都可以同名;

约束名删除，使用drop index语句：

alter table table_name drop index 约束名（或者有唯一约束的字段名）；或者

DROP INDEX 约束名（或者有唯一约束的字段名） ON table_name;

 

======== 自增约束===========

自增约束是一种由系统自动增加并填入字段数值的约束。
auto_increment是数据列的一种属性，只适用于整数类型数据列，
auto_increment数据列必须有唯一约束，以避免序号重复，
mysql一张表中只能有一个auto_increment字段，
当使用truncate table table_name 删除全部数据时，auto_increment会从1重新开始编号，
自增字段的值只能一次加1，而不能像SQL Server数据库中的自增约束一样，每次加2、每次加3，
在InnoDB数据表中，不可通过 create table or alter table 的auto_increment=n 语句来改变自增初值。

 

 

 

 

 

添加自增长

1)在创建表的时候添加

CREATE TABLE 表名称(

 user_id INT(10) AUTO_INCREMENT PRIMARY KEY

 );

2)通过ALTER语句

ALTER TABLE 表名称 MODIFY 字段 INT(10) AUTO_INCREMENT;

ALTER TABLE 表名称 CHANGE 旧字段 新字段 INT(10) AUTO_INCREMENT;

删除自增长

ALTER TABLE 表名 MODIFY 字段 INT(10);

ALTER TABLE 表名 CHANGE 旧字段 新字段 INT(10);

 

一张表只能有一个自增长列，并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)

不过自增长一般配合主键使用，并且只能在数字类型中使用；

 

=============ZEROFILL(零填充)=============

添加零填充

1)在创建表的时候添加

CREATE TABLE t_user(user_id INT(10) ZEROFILL);

2)通过ALTER语句

ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL;

ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL;

删除零填充

ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

注：零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为INT(5),而插入的值为2,那么零填充会显示00002

但是，这个效果在Navicat for MySQL中显示不出来,只有在DOS窗口下才能显示

 

===========DEFAULT(默认值)=======

 

添加默认约束

*****1)在创建表的时候添加

CREATE TABLE 表名(

 user_id INT(10) DEFAULT  3

 );

*****2)通过ALTER语句

ALTER TABLE 表名 MODIFY user_id INT(10) DEFAULT  2;

ALTER TABLE 表名 CHANGE user_id user_id INT(10) DEFAULT  2;

alter table people  alter ages  set default 30;

删除默认约束

ALTER TABLE t_user MODIFY user_id INT(10);

ALTER TABLE t_user CHANGE user_id user_id INT(10);

alter table people  alter ages drop default;

 

=========UNSIGNED(无符号位)==============

添加无符号

1)在创建表的时候添加

CREATE TABLE 表名(

 user_id INT(10) UNSIGNED

 );

2)通过ALTER语句

ALTER TABLE 表名 MODIFY 字段 INT(10) UNSIGNED;

ALTER TABLE 表名 CHANGE 旧字段 新字段 INT(10) UNSIGNED;

删除无符号

ALTER TABLE 表名 MODIFY 字段 INT(10);

ALTER TABLE 表名 CHANGE 旧字段 新字段 INT(10);

注：无符号作用于数值类型

 

======#从查询information_schema中查询指定表中的约束=========

 

USE INFORMATION_SCHEMA;

SELECT CONSTRAINT_NAME FROM TABLE_CONSTRAINTS WHERE TABLE_NAME='student';

 

 

=================数据库其他常用操作====

 

*****更改结束符：delimiter ;//吧定界符更换为分号

*******查看mysql版本：select version(); 

******查看mysql路径：which mysql；

 

******导出数据库：

在已有的数据库导入数据，首先使用use命令进入到该数据库，然后：

mysql> source /Users/baijiawei/Documents/Code/class.sql

导入数据库的格式：source /<路径>/. <sql>.sql，你也可以导出为.dump文件；

******导入数据库：

在已有的数据库导入数据，首先使用use命令进入到该数据库，然后：

mysql> source /Users/baijiawei/Documents/Code/class.sql

导入数据库的格式：source /<路径>/. <sql>.sql，你也可以导出为.dump文件；

*****数据库还原：

如果你误删了一些数据，又或者是更新数据时忘记指定where条件，或者你把数据表都删除了，你也可以把它们还原：

$ mysql -u root -p testDB < testDB.sql
Enter password: 

当然，也是需要输入root密码。

 

*************常见错误：https://blog.csdn.net/wolfofsiberian/article/details/39340981

1.{ Error: ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xE7\x94\xB7' for column 'sex' at row 17

原因：编码格式，utf8对汉字不友好；

解决：修改某个字段的属性为gbk，

alter table s modify sex varchar(10) character set gbk;

alter table s modify SNAME varchar(20) character set gbk;

修改某个表的编码为gbk；

alter table s character set gbk;

修改数据库为gbk；

alter database test character set gbk;

 

2.mysql> update T_USER set ADDRESS ='aaa' where userid= 1;

ERROR 1292 (22007): Truncated incorrect DOUBLE value: '96133dby11'

出错原因：where条件中的值传入了错误的数据类型。userid是varchar类型的，我却传入了int类型值。