基础语法 | sql语句 | 完整性约束【数据库】

1、基础语法

mysql -h【主机名】 -u【用户名】 -p         //登录MySQL

exit或quit;        //退出MySQL

show database;       //查看MySQL下的所有数据库

use 【数据库名】;       //进入数据库

show tables;        //查看数据库下的所有表名

*MySQL的启动和关闭

（1）Windows系统

NET START MYSQL80    net start mysql80       //mysql80是MySQL服务的名字

NET STOP MYSQL80     net stop mysql80

（2）Linux系统

service mysql start

service mysql stop

service mysql restart        //重启MySQL服务

*Windows系统下的MySQL使用命令（了解）

create user 'user01' @'localhost' identified by 'user01';         //创建用户“identifie鉴定”

drop user ‘user01’@’localhost’;          //删除用户

select user,host from mysql.user;             //查看所有用户

select current_user();    select user();        //查看当前用户

rename user 'user01'@'localhost' to 'User01'@'localhost';     //将用户名user01 改为User01

alter user 'User01'@'localhost' identified by 'User01';     //将密码user01改为User01

show grants for ‘User01’@’localhost’;     //查看当前登录用户所属的条件

grant all on test.score to 'User01'@'localhost';          //这里的all是指将test库里的score表的所有权限都给User01

revoke all on test.score from 'User01'@'localhost';      //撤销权限

flush privileges;       //刷新系统权限表，即时生效

2、SQL语句的介绍

*创建表

   

describe student;       desc student   //显示详细的表的信息结构

select * from student;   //显示表中所有的信息

show create table student;   //显示创建该表的SQL语句

drop table student;     //删除整张表

truncate table student;     //删除表

delete from user;     //删除表中的记录

delete from user where user_id = 1;      //有条件的删除表中的记录

insert into student (id,name,age) values(null,'zhangsan',23);      //在表中插入数据

select name,id from student where id=20201304023;    //有条件的查询表中的内容

update student set sex = ‘男’ where sno = ‘20201304023’;     //有条件地在表格修改数据

update student set sex = ‘男’;      //把表格中选定的整列数据都给替换了

alter table 表名 rename to 新表名;         //修改表名       

alter table 表名 drop column 列名;        //删除表中的具体列

alter table 表名 change 列名 新列名 varchar(30);    //修改表中列的名字

alter table 表名 modify 列名 varchar(22);     //修改表中具体列的属性

alter table 表名 add 字段名 字段类型 first;       //将新增加的列设定为第一列

alter table 表名 add 字段名 字段类型 after 字段名;       //将新增加的列设置在某个列的后面（字段名：列名   字段属性：列里面存储的数据类型）

show columns from 表名;          //查看表中所有的列名

alter table student engine= myisam;     //修改数据表的类型，将其表的类型修改为myisam

create database 数据库名;       //创建数据库

show databases;        //查看所有的数据库

drop database 数据库名;       //删除具体的数据库

//快速添加一张表，使其与已存在的表student数据和结构一致

create table student2 as select * from student;

//快速添加一张表，使其与已存在的表student结构一致，但里面没有任何数据

create table student3 as select * from student where 1 = 2;

//快速添加一张表，只要部分列、部分数据

create table student3 as select sno,sname,age from student where sno =2;

//删除数据    

（1）drop删除整张表

（2）delete只删除表中的数据，保留表中的结构

（3）truncate只删除表中的数据，保留表中的结构

//起别名的注意事项“AS”

select column_name(s) from table_name AS alias_name;     //给表格起别名

SELECT column_name AS alias_name FROM table_name;     //给列起别名

*使用别名的好处

（1）在查询中涉及超过一个表

（2）在查询中使用了函数

（3）列名称很长或者可读性差

（4）需要把两个列或者多个列结合在一起

*给表取别名的语法

SELECT w.name, w.url, a.count, a.date

FROM Websites AS w, access_log AS a

WHERE a.site_id=w.id and w.name="菜鸟教程";    //起了别名

SELECT Websites.name, Websites.url, access_log.count, access_log.date

FROM Websites, access_log  

WHERE Websites.id=access_log.site_id and Websites.name="菜鸟教程";      //没起别名的

（表：Websites和access_log）

（表：Websites里的列为name,url）

（表：access_log里的列为count,date）

//有条件的查询

//去重查询

表格中的有些列里的元素重复了，想要显示该列中的所有元素

（对多个列进行去重查询，这些列组成的数组是不相同的。）    distinct不同的，区别

//排序

（先对sal进行升序排序。如果某几行的sal值相同，则会按deptno进行降序排序。）

//有条件的查询“where”

（1）where +“算数运算符”

（2）where + “逻辑运算符”（与、或、非---------“and”“or”“!”）

（3）模糊查询--------（“%”代表任意字符，“_”代表单个字符）

（4）非空判断

（5）小括号-------划分判断语句的优先级

（SQL解析器在处理操作时会优先处理and操作，在处理or）

3、完整性约束--------针对表格

3.1、完整性约束的用法

（1）列级约束（针对某一个特定的列）

（2）表级约束（用于对多个列一起的约束）

*注意

表级约束：可以约束表中任意一个或多个字段。与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称；

列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名；

（3）在创建表后添加约束

（4）外键约束--------------------（难）

//有些表格之间有相同的列，当我们修改了其中一列时，其它的表要挨着修改吗？

我们可以引入外键约束，用来在两个表数据之间建立链接，其中一张表的一个字段被另一张表中对应的字段约束。也就是说，设置外键约束至少要有两种表，被约束的表叫做从表（子表），另一张叫做主表（父表），属于主从关系。

//其中主表中的关联字段叫做主键，子表中的关联字段叫做外键。

//外键约束主要作用就是能够让表与表之间的数据建立关联，使数据更加完整，关联性更强。

//外键创建规则

（1）必须有主表才可以设置从表。

（2）主表必须实际存在。

（3）必须为主表定义主键。

（4）外键列的数据类型必须和主键列的数据类型相同。

（5）外键列的数量必须和主键列的数量相同。

（6）外键可以不是外表中的主键，但必须和主表关联字段相对应。

//语法（创建外键约束的）

第一步、建立主从表（划分外键，主键）-------加约束

   

class（主表）            constraint 约束          reference提及、谈到

student（从表）      fk_stu_classno（外键名字）

第二步、设置外键约束

//单充建立外键，不加外键约束，可以吗？

从表添加一条主表不存在的数据成功了？

主表删除数据，从表对应的那个数据还存在？

答案：不可以

外键约束只有表级约束，没有列级约束。

先删主表，在删从表。

//参数说明：

constraint ：用于设置外键约束名称（可以省略）

foreign key：外键设置，用于指定外键字段

references：主表及主键设置，用于指定主表和主键

//属性说明：

cascade：主表删除或修改记录时，从表也会对关联记录的外键字段进行修改。

restrict：删除或修改主表记录，子表中若有关联记录，则不允许主表删除或修改。

set null：主表删除或修改主表记录时，从表会将关联记录的外键字段设为null。

on update cascade：主表修改记录时，从表关联记录的外键字段也会修改。（将cascade改为restrict，意思相反）

on delete cascade：主表删除记录时，从表关联记录的外键字段也会删除。（将cascade改为restrict，意思相反）

//语法

constraint 外键名称foreign key 外键字段references class 主键字段

on update cascade on delete cascade       //外键约束创建（在表格里写该语法）

alter table 表名 drop foreign key 外键名称;       //删除外键（在表格外面写的）

//主从表之间的关系--------------当约束条件为（on update cascade on delete cascade）

（1）从表中删除记录不会对主表造成影响。（从删主不删）

（2）主表的修改根据属性会对从表造成影响。（主删从不见）

（3）从表的修改不影响主表，但主表的修改会影响从表

（4）无法插入主键关联中不存在的记录。

cascade（v.）串联，倾斜，传授，垂下

       （n.）瀑布

restrict （v.）限制，控制

//级联操作

on delete cascade:主表中的数据被删，从表中相关数据也会被删

on update cascade:主表中的数据被改后，从表中的相关数据也会被修改

on delete cascade on update cascade:删除和修改有级联操作

on delete set null:主表总数据被删除后，从表中相关数据为null

on update set null：

*外键策略

在外键约束的情况下，如何合理的删除外键

//不允许操作

//级联操作

//置空操作

3.2、完整性约束常见的错误

（1）列级约束

（当我们插入数据时发生报错，插入的数据会丢失，但是引用的主键却不会丢失而会保存）

（2）表级约束

（3）外键约束

//单充建立外键，不加外键约束，可以吗？

从表添加一条主表不存在的数据成功了？

主表删除数据，从表对应的那个数据还存在？

答案：不可以

3.3、约束的分类

primary 私有的       union 单独的       check检查        increment自增的

default默认      foreign外部的

//主键约束(primary key) PK     主键的定义

//自增长约束(auto_increment)      只能搭配主键，键值（整型）自增加一

//非空约束(not null)     键值不能为空

//唯一性约束(unique)     键值必须是唯一的

//默认约束(default)      键值是默认的

//零填充约束(zerofill)            插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0

//外键约束(foreign key) FK  

补充：

列级约束：只能应用于一列上。

列级约束：包含在列定义中，直接跟在该列的其它定义之后 ，用空格分隔；不必指定列名 

表级约束：可以应用于一列上，也可以应用在一个表中的多个列上。

表级约束：与列定义相互独立，不包含在列定义中；与定义用‘，’分隔；必须指出要约束的列的名称

（1）外键约束只有表级约束，没有列级约束。

（2）列级约束有六种：主键、外键、唯一、检查、默认、非空/空值

（3）表级约束有四种：主键、外键、唯一、检查

有constraint的是列级约束，没有constraint是表级约束