2.数据库语言二
文章目录
数据库语言二
Mysql的约束
use 库名;
create table if not exists 表名 (
id int(4) zerofill primary key auto_increment,
name varchar(4) not null default '匿名',
phone int not null unique key,
hobby varchar(50)
);
#if not exists:
表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill:
表示若数值不满4位数，则前面用“0"填充，例0001
这个4 在没有后面 zerofill 时，没有意义
#auto_ increment:
表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增;
自增长字段数据不可以重复;自增长字段必须是主键;
如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key:
表示此字段唯一键约束，此字段数据不可以重复:一张表中只能有一个主键，但是一张表中可以有多个唯一键
#not null:
表示此字段不允许为NULL
##default '默认值'
指定默认值
insert into info (name,phone) values ('张三',123456789);
insert into info (name,phone) values ('小红',123456789);
insert into info (name,phone) values ('李四',2222222);
insert into info (name,phone) values ('王五',3333333);
insert into info (name,phone) values ('赵六',3333333);
insert into info (name,phone) values ('小六',444444444);
insert into info (id,name,phone) values (2,'小七',123456789);
insert into info (id,phone) values (9,456749852);
-
Mysql的常用六大约束
-
primary key：
- 主键约束，字段的值不能重复，不能为null，一个表只能有一个主键
-
unique key .
- 唯一性约束字段的值不能重读，能为null，一个表可有多个唯一键
-
not nu1l
- 非空约束字段的值不能为null
-
default
- 默认值约束字段的值如果没有设置则使用默认值自动填充
-
auto_ increment
- 自增约束，字段的值如果没有设置，默认会从1开始递增，要求自增字段必须为主键字段
-
foreign key
- 外键约束
-
数据表高级操作
克隆表
方法一
create table 新表名 like 旧表名;
#复制格式，通过LIKE方法，复制旧表结构生成新表
##里面数据为空
insert into 新表 select * from 旧表;
#备份内容，表中数据
方法二
CREATE TABLE 新表 (SELECT * from 旧表);
##创建表时备份表中数据，但表结构可能会发生变化
show create table 表名G
#获取数据表的表结构、索引等信息
desc 表名;
SELECT * from 表名;
清空表，删除表内的数据
方法一
delete from 表名;
#DELETE清空表后，返回的结果内有删除的记录条目;
DELETE 工作时是一行一行的删除记录数据的;
如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录ID后面继续自增写入记录。
会调用CPU，影响性能
方法二
truncate table 表名;
#TRUNCATE清空表后，没有返回被删除的条目:
TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上TRUNCATE会比DELETE清空表快;
使用TRUNCATE TABLE 清空表内数据后，ID会从1开始重新记录。
创建临时表
- 临时表创建成功之后，使用SHOWTABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。
- 如果在退出连接之前，也可以可执行增删改查等操作，比如使用DROP TABLE语句手动直接删除临时表。
CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[, ...][, PRIMARY KEY (主键名)]);
##创建临时表
create temporary table 表名 (
id int(4) zerofill primary key auto_ increment,
name varchar(10) not null,
phone int(18) not null unique key,
);
show tables;
##查看不到临时表
desc 表名;
##能看见临时表结构
insert into 表名 values (1,'zhangsan',123456);
select * from 表名;
外键约束
-
外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
-
主键表和外键表的理解
- 以公共关键字作主键的表为主键表(父表、主表)
- 以公共关键字作外键的表为外键表(从表、外表)
- 注意:与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主表外键字段和从表的字段具备相同的数据类型、字符长度和约束。
#创建主表test04
create table test04 (pid int(4),lesson varchar(50));
#创建从表test05
create table test05 (id int(4) primary key auto_increment,name varchar(10),age int(3),class int(4)) ;
##alter table test05 add primary key (id);
##在创建完后，在表中添加主键
#为主表test04添加一个主键约束。主键名建议以"PK_”开头。
alter table test04 add constraint PK_hobid primary key (hobid);
#为从表test05表添加外键，并将test05表的class字段和test04表的pid字段建立外键关联。外键名建议以"FK_”开头。
alter table test05 add constraint FK_hob foreign key (class) references test04 (pid);
##constraint FK_hob
##指定键的名称
desc test04;
desc test05;
alter table 表名 add foreign key (外键字段) references 主键表 (主键字段);
插入数据时，现在主键表插入数据，再在外键表插入对应的数据；
删除数据时，现在外键表删除数据，再在主键表删除对应的数据。
#查看和删除外键约束
#如果要删除外键约束字段先删除外键约束，再删除外键名
alter table 表名 drop foreign key 外键名;
alter table 表名 drop key 外键名;
show create table test05;
alter table test05 drop foreign key FK_hob;
alter table test05 drop key FK_hob;
desc test05;
数据库用户管理
新建用户
CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];
create user 'zhangsan'@'localhost' identified by '123';
'用户名'：指定将创建的用户名.
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录
可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD('密码')；获取密文，再在语句中添PASSWORD '密文'；
若省略“IDENTIFIED BY"部分，则用户的密码将为空(不建议使用)
select passwd('123');
##将明文密码转换成密文密码
select user();
##查看当前登陆用户
查看用户
use mysql;
show tables;
use mysql;
select User,authentication_string,Host from user;
重命名用户
RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';
rename user '旧名字'@'localhost' to '新名字'@'localhost';
删除用户
DROP USER 'lisi'@'localhost' ;
修改用户密码
##修改当前用户密码
set password = password('abc123');
##修改指定用户密码
set password for 'user1'@'localhost' = password('abc123T);
忘记root密码
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加，使登录mysql不使用授权表
systemctl restart mysqld
mysql #直接登录
##使用update修改root密码，刷新数据库
update mysql.user set authentication_string = password('abc123') where user='root' and host='localhost';
privilefes
flush privileges;
quit
mysql -u root -pabc123
-h：指定地址
-P：指定端口
注意:最后再把/etc/my.cnf 配置文件里的skip-grant-tables 删除，并重启mysql服务。
数据库用户授权
授予权限
grant语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时，GRANT语句用于修改用户信息。
grant 权限列表 on 数据库名.表名 to '用户名'@'来源地址' identified by '密码';
#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select, insert, update”。使用"all"表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用“kgc.*"表示授权操作的对象为xyw数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.xyw.com"、“192. 168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY"部分，则用户的密码将为空。
#允许用户zhangsan在本地查询数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。
GRANT select ON 库名.* to 'zhangsan'@'localhost';
grant all privileges on *.* to 'lisi'@'%' identified by '123456';
flush privileges; #刷新权限
quit
mysql -u zhangsan -pabc123
use xyw;
show tables; .
select * from yyy;
这个all privilege都有哪些权限?
all privilege权限如下:
insert (插入数据)
select (查询数据)
update (更新表的数据)
delete (删除表中数据)
create (创建库,表)
drop (删除库, 表)
references
index (建 立索引)
alter (更改表属性)
create temp orary tables
lock tables (锁表)
execute
create view (创建视图)
show view (显示视图)
create routine (创建存储过程)
alter routine (修改存储过程)
event (事件)
trigger on (创建触发器)
查看权限
mysql -u root -pabc123
show grants for 用户名@来源地址;
撤销权限
revoke 权限列表 on 数据库名.表名 from 用户名@来源地址;