SQL复习_alter table `chinese`.`information` add constraint

以前Windows使用过SQL Server和MySQL，都用的可视化工具，不经常用，快忘得差不多了，重新复习一下数据库知识。

工作使用Ubuntu电脑，所以也重新学习一下安装、命令操作。

Linux  MySQL安装参考：https://www.cnblogs.com/opsprobe/p/9126864.html

可以连接网络，且没有安装过MySQL。

大部分是在控制台命令行操作。

安装Mysql

1.sudo apt-get install mysql-server
2.安装完成之后可以命令查询监听接口：netstat -tap | grep mysql
3.登录：mysql -u root -p
-u ：选择登陆的用户名，这里是以root用户登录， -p ：登陆的用户密码，此时mysql数据库是没有密码的，Enter password:处直接回车，就能够进入mysql数据库。
后续步骤初始化成功后，在Enter password：处输入密码，再回车登录。
4.数据库初始化操作
(1)安装验证密码插件。mysql——secure_installation
(2)设置root管理员在数据库中的密码。
(3)随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
(4)删除默认的测试数据库，取消测试数据库的一系列访问权限。
(5)刷新授权列表，让初始化的设定立即生效。

在root时登录，不输入密码也可直接回车进入。

https://blog.csdn.net/qq_39234840/article/details/84328987

新建一个非root用户，在进入MySql后执行如下命令：（%的意思是任何电脑都可登录，也可以指定一个IP）

create user '用户名'@'%' identified by '大小写和特殊字符组成的密码';

赋予新账号对所有数据库表的权限，all 包括了删，改，增等权限。

grant all on *.* to 'zhangdingyi'@'%' identified by 'Ensky123.';

退出MySql和root，使用新建的账号登录，可以进入mysql。

5.允许远程连接数据库
(1)编辑/etc/mysql/mysql.conf.d/mysqld.cnf, 用#注释掉bind-address  =127.0.0.1
(2)进入mysql，
执行授权命令，grant all on *.* to root@'%' identified by '密码' with grant option;
刷新权限，flush privileges;
退出mysql
(3)重启mysql，systemctl restart mysql

第五步，还没验证。

创建数据库

create database 数据库名; （sql语句末尾要有分号！这是sql基本语法规则，控制台命令有些习惯了不加分号输完就回车）

查看数据库

show databases;

系统自动创建的名为 information_schema 和 mysql 的两个系统数据库，存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

删除数据库

drop database 数据库名;

修改数据库的字符集

alter database 数据库名 default character set utf8 default collate utf8_unicode_ci;

utf8字符集 utf8_unicode_ci校对规则，

跳转工作数据库（可视化工具的话，双击）

use 数据库名;

数据库引擎

show engines;

mysql，默认是InnoDB。平时够用了。

可以根据以下的原则来选择 MySQL 存储引擎：

1.如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。

2.如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。

3.如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

4.如果只有插入和查询操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

建表时可指定type=Innodb;

建表后可修改alter table 表名 type=Innodb;

数据类型（建表时有用）（前缀带var的都是存储可变长度）

基本数据：tinyint、smallint、mediumint、int、bigint(表示的字节范围从小到大)、float(四字节)、double(八字节)、decimal(10位)；

时间：year(YYYY)、time(HH:MM:SS)、date(YYYY-MM-DD)、

datetime(YYYY-MM-DD HH:MM:SS)、timestamp(格式跟datatime一样，但范围小，且是根据世界标准时);

字符：char(固定长度,1<=指定字节长度<=255)、varchar(变长型，1+(实际存储长度<256))、

字符串：binary、varbinary、blob、text、enum(有索引的表中表？枚举)、set('值1','值2',....)限制列中能够填入的值只能从set中选；

二进制：bit(M位显示/8字节)、binary(M字节)、varbinary(M+1字节)、tinyblob、blob(字节字符串)、mediumblob、longblob;

创建表

create table 表名 (

列名1 数据类型(),...表选项（比如主键、是否允许空值，也可以不填-默认选择）

列名2 数据类型(),...表选项（比如主键、是否允许空值）

...

列名n 数据类型(),...表选项（比如主键、是否允许空值）

);

查看表(可视化，双击或者右键数据库表)

desc 表名;  describe 表名;  （前两者等价，可查看'表'的属性列表）或者show create table 表名; （这句可以查看建表语句）

修改表的一些参数(不是update还不习惯)

alter table 表名 修改选项语的语句;

1.比如在表的某列后增加(add)一个新的列：

alter table 表名 add column 新增列名 数据类型 after 指定的某列;

实际操作后发现没有column参数也能执行成功。加到第一列，after参数改first

alter table 表名 add 新增列名 数据类型 after 指定的某列;

2.修改表中某列的数据类型（modify），通常为了不影响已有的数据，可能不常修改数据类型

alter table 表名 modify  字段名  数据类型;

3.删除字段（说到删除，一定要想到drop）

alter table 表名 drop 字段名;

4.修改列名（change）

alter table 表名 change 旧列名 新列名 新的数据类型;

5.修改表名（rename）

alter table 旧的表名 rename 新的表名;

删除表（drop，建错表，又不想一条条alter改，不如删了重建）

drop table 表名;

Mysql的主键(primary key)

关于主键:

有单列主键或者多列组成的复合主键；一张表有且只有一个主键；唯一性原则：表中不可能存在主键值相同的两行数据；

复合主键不能包含多余的不必要列，即删除复合主键的某一列后，主键依旧保持了唯一性原则，那原本的复合主键就是不正确的

创建表时可添加主键

1.作为列的参数，比如一个主键id字段：id INT(11) PRIMARY KEY,

2.列都定义完成，末尾添加主键，PRIMARY KEY(id) 这种方式用来定义复合主键

表创建完成后添加主键 alter table 表名 add primary key(id);

删除主键：alter table 表名 drop primary key; 

关于外键（foreign key）

本表(从表)的外键对应另一张表(主表)的主键；本表的外键值可以是空，但不为空时一定与主表的主键值相同，

与主键的数据类型要相同，否则创建报错150；

创建表时添加外键约束

constraint 外键名 foreign key(外键字段) references 主表(主表主键);

外键作为约束条件可以在表创建完成后再次添加进去，这个SQL语句参数就稍微多一些了。

alter table 从表名 add constraint 外键名称 foreign key(外键字段)  referneces 主表(主键);

删除外键约束

alter table 从表名 drop foreign key 外键名;

关于唯一性约束unique，跟主键作用很相似（不允许重复值），但允许至多出现一个空值且允许单张表具有多个唯一性约束

一张表可以有多个唯一性约束；创建语句类似于primary key，加在列的属性定义语句之后。

添加唯一性约束：alter table 表名 add constraint 唯一性约束名 unique(列名);

删除唯一性约束：alter table 表名 drop index  唯一性约束名;        

MySql支持index 、SQLServer支持constraint

检查约束check（还不知道原因，自己试验alter时添加check没成功，虽然有OK的回复，但通过show语句查看，却没有相应的约束语句存在，删除的时候总失败，回来检查才发现添加这步根本没成功）

可以起到限制数据列的数据更新的作用，规定非法数据，只有符合要求的数据才能该check列添加或修改。

创建表时添加约束check(SQL语句)，比如id int(10) check(id > 4 and id < 100)，这样id列只能输入大于4小于100的整数值。

添加check约束，类似的alter table 表名 add constraint 约束名 check(sql语句/条件)

删除：同上。

默认值default(增加新行时，若相应列没有值，则填入默认值)

建表时可以在列属性约束后添加default  '默认值'，建表后想改，也是用alter

用修改字段名的方法顺便修改默认值，当然这里新、旧字段名要写一样的。

alter table  表名 change 旧字段名 新字段名 数据类型 default '默认的值';

删除默认值，设为null，不是用drop

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

非空(null)约束

同默认值约束

数据行的'增删改查',先上最基本的

增：

insert into 表名 (字段名1,字段名2,...) values(值1,值2...);  

虽然也可以不写字段名，但values内的值就要按表的列名顺序填写，而且表结构一旦改变……，

insert into 表名 set 列名1=值1, 列名2=值2, ……

查：

最基本的：select 字段列名 from 表1,表2... ;

删：

delete from 表名 条件子句(where、Order by、limit);

条件子句重点。否则就是删除所有数据了。

改：

update 表名 set 字段1=值1,... 条件子句;

条件子句一般都是要的，重点也在这，不然就是修改整列数据为同一个值了。

关于各种查询方法技巧，条件，最重要，也最多。

去重distinct(去掉重复值)

比如想要查出的name字段不重复，关键字distinct放在要找的字段名之前，select distinct name from db_tablename;

别名as

有时候表名、列名不够直观或太长又需要经常使用，可以设置别名。如：select name as student_name from db_table;

列名的别名会在结果显示中出现。

限制结果行数limit

作为条件子句，写在最后，比如：select name from db_table limit 3,5;    显示从第四条记录开始的连续五条数据行。

如果limit后只有一个整数参数M，则结果默认从第一条显示M条记录。

结果排序Order by

可以类比Excel文档的筛选->排序。

select * from db_table order by name ASC, id DESC;  优先以name升序排序, 若有同名再以id列降序排序。

条件查询where

比如查某列成绩大于60小于95的学生select name,result from db_table where result>=60 and result<95;

除了整数可以用比较运算符，日期时间也可以用。

比较运算符

除了大于号小于号那些，还有least(最小值)，greatest(最大值)，between 表达式1 and 表达式2，

like(模糊查询，可使用通配符% _       %匹配0,1,多个字符；_ 匹配一个字符)，regexp|rlike(正则)。

特记一下逻辑判断，异或xor，两个值之间都假或都真，返回假；只有一假一真返回真（只有两者相异返回真）。

内连：连接join,借助相关连的字段，进行多表联合查询（相当于拼在同一张表上），查询结果通常会是不同表的字段结合

select 列名1,列名2,... from 表1, 表2,.. where 表1.列名1=表2.列名2;

内连接inner join   on

select 列名1,列名2,... from 表1 inner join 表2 on 表1.列名3=表2.列名3;

where 比较简单明了，但据说某些时候会影响查询性能，这时可以用join on

外连：左连和右连

以其中一张表为基础，连接的表作为参考表，返回查询记录。

左连以左表为基础，查找右表中符合条件的数据，在结果集中，左表数据会完整显示，对应的右表数据可能为null，意味着右表中没有匹配项

右连以右表为基础，查找左表中符合条件的数据，在结果集中，右表数据会完整显示，对应的左表数据可能为null，意味着左表中没有匹配项

语法是跟内连一样，同时如果表名很长，在on后写条件语句不想再写一遍，可以给'表'设置代号，可以用   代号.字段名。

select 列名1,列名2,... from 表1 [代号1] left join 表2 [代号2] on 代号1.列名3=代号2.列名3;

还有full outer join,把左右两张表的数据都显示出来，没有相互匹配的也是Null值填着；这个不是MySql支持的，试验也没通过。

子查询

查询条件语句里再套一层查询语句,关键字in 、exists

比如select name from db_table  where id in (select id from db_table where type='asd');

分组查group by

按指定的列中的值进行分组，

比如，学生表里，每个学生的学院虽然各不相同，但还是会有重复的，一个学院肯定有很多不同的学生嘛。

如果要统计各个学院的学生名单，可以  select 学院名 group_concat(学生字段名) as names from db_table group by 学院名;

这里就是按不同学院分组，如果没有聚合函数group_concat()，结果集将只有一列不重复的学院名。

group_concat(学生字段名)换成聚合函数sum(学生字段名)，结果显示就是各个学院的学生数量清单。

过滤条件having

只对分组后结果进一步过滤

比如上述group by查询的学院学生数量清单，如果是要进一步查询人数在10以下的学院，可以在末尾再添加语句：

having names<10

正则

http://c.biancheng.net/view/2572.html

^  匹配开始字符  ^f   可以匹配food、flush

$  匹陪结束字符  S$  可以匹配FLAGS、SSS

.   相当于替换任何单字符   b.t    可匹配bit，bat

*   相当于0个、1个、任意多个字符   S*  可匹配SSS SOS Sklhn

+   至少匹配一次前面的字符   a+   可匹配a    ak     aaaa

[字符集]    至少匹配其中一项   [c-z]   mysql默认没有区分大小写，只要是字母不含ab都能配上

[^]   匹配不在[]里的字符,    [^t]   只要包含了除 t 以外的值就算符合条件 比如 take；而像 ttt 肯定就被过滤掉啦

str{n,m}  匹配前面的字符串str至少n次，至多m次   oo{1}  可以匹配google、oooo

直接regexp '字符串1|字符串2|……' 也可以匹配指定字符串

视图

虚拟表，没有实际的物理存储记录；

引用视图时会动态查询真实的表数据；

可以简化输入操作，对于复杂的条件查询，可以一次建立视图，之后在视图查询。

视图的建立删除不影响基本表，供不同用户查看使用，也更具备安全；

视图动态建立，如果太多太复杂，还有嵌套其他视图，可能会影响性能。

创建视图语法：create view 视图名 as select语句;

创建视图时select语句限制：不能有系统或用户变量；不能有子查询；不能预处理。

查询视图，和查询表一样。select  字段名 from 视图名;

修改视图，alter view 视图名 as select语句; 

更新视图数据，其实是更新基本表数据。

删除视图，drop view 视图名;

自定义函数

create function 函数名(参数1,参数2,,,,) returns 数据类型 return (SQL语句);        无参也行。

调用 :    select 函数名(参数);

删除：  drop function 函数名;

存储过程

感觉和自定义函数作用类似

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的流程控制语句组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

delimiter  //

create procedure 过程名 (参数)

begin  SQL语句;

end //

调用：call 过程名(参数)

删除：drop procedure 过程名;

触发器（对数据的变化触发预设动作）

insert触发（before之前/after之后）、update触发、delete触发。

创建触发器：

create trigger 触发器名

on 表名

for  before|after insert|update|delete

as 

触发后要执型的SQL语句 ;

删除触发器：drop trigger 触发器名字;

索引（这个深入一下还需要研究数据结构）

简介：http://c.biancheng.net/view/2604.html

索引是可以在大量数据中快速查找有效数据的方便方法。仅是检索查的话找比较快，会降低修改性能的。

数据库查找数据，平时练习使用的其实都是顺序查找，就是遍历所有数据行进行查找。

创建索引：create index 索引名  on 表名(字段名)

删除索引：drop index 索引名 on 表名;

用户

这个以前学的很少，我们登上一个用户能用就行。版本不同，命令也有些改动。MySql5.7.1可以不用password()

新增用户：create user '用户名'@'localhost'  [identified by] [password(密码)]  <口令>;

create user 'sqa'@'localhost' identified by  'Sqa123.';

修改用户账号：rename uesr 旧账户 to 新账户

修改用户密码：set password [for '用户名'@'主机名']=password('密码')|'加密口令值';

删除用户：drop user 账户;

对用户授权：grant insert|update|select 列名表 on 对象 列权限|表权限|数据库权限|用户权限 to 用户;

比如：grant inert,update on *.* to 'xiaoming'@'localhoast' identified '新密码' with grant option;

给xiaoming插入、修改所有数据库下所有表的权限，顺便重新设定xiaoming的密码。

删除用户权限：

1.revoke insert|update|select 列名表 on 对象类型 权限名  from 用户;

2.revoke all privileges, grant option from 用户;

比如：revoke insert on *.* from 'xiaoming'@'localhoast';

删除xiaoming所有数据库下所有表的插入权限。

事务

原子性：原子工作单元，事物中的操作，要么全执行，要么不执行。

一致性：由用户负责，数据库在事物执行前后的一致，比如：两个账户转账金额之和不变。

隔离性：多事务并发执行时，互不干扰。

持久性：事务执行后的结果可长久保持不变。

执行事务从：begin transaction 事务名称

提交事务（数据变化更新到实际物理存储）：commit transaction 事务名称

撤销事务（事务执行过程中，条件撤销，回滚）：rollback transaction 事务名称|savepoint

备份

导出数据表内容到指定路径文件中。

查看系统默认的导出路径：show variables like 'secure_file_priv';  我查出来是/var/lib/mysql-files/

导出数据表内容：

select * from table_name into outfile '/var/lib/mysql-files/store.txt' fields  terminated by ',' optionally enclosed by '"' lines terminated by '?';

这句就是查询语句在末尾加上into outfile关键参数，再接fields选项，

fields terminated by ',' 字段间以逗号分隔；

optionally enclosed by '"' 字符串以双引号""包含，同时因为optionally，数字不会加上""；

lines terminated by '?' 以问号为每一行的结尾标志。

则默认字段间以空格间隔,存储到store.txt。

回滚数据

load data  infile 路径下文件 into table 表名 [fields 选项];

回滚数据时的fields选项跟导出数据时的一样。

load data infile '/var/lib/mysql-files/store.txt' into table table_name fields terminated by ',' optionally enclosed by '"' lines terminated by '?';

复制一张表（或者只复制特定列到新表）：create table 新表 as select旧表语句;

暂时复习

以上。2019-11-13～2019-11-16

 

2019-11-18

mysql内置的一些函数。

时间：

date() 日期；now() 现在日期和时间；datediff() 返回两个日期之间的天数；date_format() 指定日期时间的显示格式

格式化时间，比如：date_format(now(),'%Y-%m-%d') 输出结果为日期。

null值判断:

ifnull(字段值,默认值) 判断字段值是否是Null值，为空，则可输出默认值，或者coalesec(字段值,默认值);

SQL Server可以用is null判断。

通用函数

avg(字段名)返回平均值

count(字段名)返回行数

first()或者last()应该返回第一个或者最后一个

max(字段名)或者min(字段名)返回最大或者最小

lcase(字段名)字段值转换成小写，ucase(字段名)字段值转换成大写

sum(字段名) 计算指定条件下字段值的和

mid(字段名,start,num)截取字段值从起始位置start开始的num个字符。

len(字段名) 返回字段值的长度

round(字段名,num) 返回字段值四舍五入并保留num小数后的bigint类型的值。

substring(字段，'asdsf')从字段值指定