一、约束条件和用户管理
1.1、约束条件:
主键
主键约束 primary key :用于标识表中的主键列的值,而且这个值是全表当中唯一的,而且值不能为null。
一个表只能有一个主键。对设置主键的列(具有唯一性)。
主键第一种:
create table stu02 (
id int(5),
NAME char(10),
score DECIMAL(5,2),
address VARCHAR(128),
primary KEY (id)####对id设置主键约束,生成唯一性。
);
insert into stu02 values(1,'ly1',88,'nan');
主键第二种:
create table stu03 (
id int(5) primary key,####对id设置主键约束,生成唯一性。
NAME char(10),
score DECIMAL(5,2),
address VARCHAR(128)
);
外键:用来建立表与表之间的关系,确保外键中的值与另一个表的主键值匹配。保证数据引用的完整性。
多表联查,不要超过三张,超过三张表,会降低查询效率。
非空约束:保证列中的值不含null值。
1.2、唯一性约束:not null unique key
唯一性约束:确保列中的所有值hi都是唯一的,类似主键,但是一个表可以有多个唯一约束。
设置唯一性约束,特点可以设置多个唯一。
列尾部+not null unique key
create table stu04 (
id int(5) primary key,
NAME char(10) not null unique key,##设置唯一性约束
score DECIMAL(5,2),
address VARCHAR(128)
);
insert into stu04 values(3,'ly3',88,'nan');
insert 插入数据时,时间类型
date
datetime
timestamp自动添加时间
create table stu05 (
id int(5) primary key,
NAME char(10),
date_time date not NULL,
date_times VARCHAR(128)
);
desc stu05;
insert into stu05 values(1,2,'2024-7-14','2024-7-14 14:30:23');
select * from stu05;
获取当前时间
create table stu06 (
id int(5) primary key,
NAME char(10),
date_time date not NULL,
date_times TIMESTAMP not NULL
);
insert into stu06 values(2,3,now(),NOW());##now()按照列的格式获取当前日期和时间;
select * from stu06;
1.3、主键自增1,扩展语句
自增约束,在列生成的每一行都会自动生成一个唯一标识符,通常和主键一起使用,每次插入新行时,自增列的值会自动增加。
create table if not EXISTS test1 (
#表示在创建表时自动检测表是否已经存在,如果不存在就创建。
id int(5) zerofill primary KEY auto_increment,
#zerofill,5位,不足5位会自动填充0.auto_increment 自增长字段。
#每条记录自动递增1,自增长必须是主键,也不可用重复,如果添加失败,
#下一条记录也会自动增加。
NAME char(10),
score DECIMAL(5,2),
address VARCHAR(128)
);
desc test1;
insert into test1 values(1,'ly1',88,'nan');
SELECT * FROM test1;
insert into test1 values(NULL,'ly2',88,'nan');##null自动添加1,id
insert into test1 (name,score,address) values('ly3',86,'hsh');
1.4、外键
外键第一种
create table if not EXISTS stu1 (
card_id int(5) primary KEY auto_increment,
stu_name char(10) not NULL,
stu_email VARCHAR(128) not null unique KEY
);
create table if not EXISTS stu2 (
stu_id int(11) zerofill primary KEY auto_increment,
address VARCHAR(128) default '地址不详',
card_id int(5) NOT NULL,
foreign key (card_id) references stu1 (card_id)
);
外键第二种:
create table if not EXISTS stu3 (
card_id int(5) zerofill primary KEY auto_increment,
stu_name char(10) not NULL,
stu_email VARCHAR(128) not null unique KEY
);
create table if not EXISTS stu4 (
stu_id int(11) zerofill primary KEY auto_increment,
address VARCHAR(128) default '地址不详',
card_id int(5) zerofill NOT NULL,
foreign key (card_id) references stu3 (card_id)
);
INSERT into stu3 values(34523,'ly1','sdfds');
SELECT * FROM stu3;
INSERT into stu4 values(998,'nan','34523');
SELECT * FROM stu4;##外键必须一致才能插入
show create table 表名;
第一种方法:
alter table stu4 drop foreign key stu4_ibfk_1;##删除外键
第二种方法:
alter table stu4 MODIFY column card_id int(5) not NULL;##更改列的类型
alter table stu4 drop card_id;##删除列
desc stu4;
alter table stu4 MODIFY column card_id int(5) not NULL;##更改列的类型
alter table stu4 drop card_id;##更改列的类型
alter table stu4 add card_id int(5) zerofill NOT NULL;##增加列
ALTER TABLE stu4 ADD FOREIGN KEY (card_id) REFERENCES stu3(card_id);##增加列为外键
alter table stu4 drop foreign key stu4_ibfk_1;##删除外键
alter table stu4 drop foreign key stu4_ibfk_1;
删除外键
alter table test1 modify column id int(5);
##删除自增长
alter table test1 drop PRIMARY KEY;
##删除主键
alter table stu4 modify stu_id int(11);##去除自增长
alter table stu4 drop primary key;##删除主键
主键和外键的区别:
-
外键就是和主表进行关联的列,不需要设置为从表的主键,但是不能为空,必须和主表的数据类型保持一致。
外键的值和主键的值要相同。 -
先插入主表的数据,再插入从表的数据。
-
删除表的外键不是直接删除外键的列名,而是删除外键的索引,show create table 表名;查看表的详细信息。
-
删除主键不需要加上主键的列名,如果有extra的额外信息,比如说自增长,要先移除属性,然后才能删除主键。
-
删除主键的方式:alter 进行修改。
alter table stu4 drop foreign key stu4_ibfk_1;
desc stu4;删除外键
alter table stu4 modify stu_id int(11);##去除自增长
alter table stu4 drop primary key;##删除主键
1.5、复制表:
create table stu_01 like student;
##复制表结构,不复制表的内容。
desc stu_01;
insert into stu_01 select * from student;
##复制表的内容,复制是student到stu_01。
create table stu_02 (select * from student);
##复制结构+内容
=create table stu_02 like student;
+insert into stu_02 select * from student;
1.6、临时表:
临时表:
临时表创建完成之后,在库里面是看不到的,但是依然可以增删改查,但是重新连接之后,临时表会消失。
create temporary table if not EXISTS stu5 (
stu_id int(11) zerofill primary KEY auto_increment,
address VARCHAR(128) default '地址不详',
card_id int(5) NOT NULL
);
select * FROM stu5;
insert into stu5 values(1,'nanjing',32343);
1.7、清空表:—面试题
drop删除表
如何删除表内数据,但是保留表和表结构?
delete from 表名;是一行一行的清空表数据,速度比较慢,如果有自增长字段,delete清空之后,会继续按照原来的序号,继续递增。
TRUNCATE table 表名;清空表,保留表的结构,但是清空之后原有的记录全部抹去,自增长也将从头开始,速度比较快。
二、数据库的用户管理:
-
创建用户
-
设置用户权限
-
取消权限
select * from user;
root都是相同的,host:可以登录的主机,localhost指的是本地登录%:任意主机(ip地址)
权限上:localhost>%的权限。
create user 'ly'@'192.168.168.12';##创建用户没有密码。
create user 'ly'@'192.168.168.12' IDENTIFIED BY '123456';创建用户并设置密码
root@localhost 安装完mysql之后就有了,不需要额外设置,其他的都需要人工创建。
其他的用户也不设置成localhost,都是设置主机名
??有问题
grant all privileges on *.* to 'ly'@'192.168.168.12' IDENTIFIED by '123456';
##这个权限适用于mysql5.7使用,8.0只能在创建用户时创建密码。
all 给与所有权限 远程登录,数据库的操作权限(增删改查)
*.* *:表示库,*:表示库里面的表
IDENTIFIED by '123456':创建登录密码
1、给与用户所有权限
grant all privileges on *.* to 'ly'@'192.168.168.12';##给与用户所有权限
`flush privileges`##刷新
information_schema:这个库的作用包含了mysql服务器中所有其他数据库,表,列。索引权限等详细的元数据的信息。可以查询数据库的结构和元信息。
performance_schema:包含mysql的服务性能和资源利用情况,查询语句的执行时间和锁定等信息。
2、展示用户的权限
show grants for 'ly'@'192.168.168.12';##展示权限
3、取消用户的权限
revoke all privileges on *.* from 'ly'@'192.168.168.12';##取消权限
flush privileges;##刷新
4、给与用户查询权限
grant select on xy102.* to 'ly'@'192.168.168.12';##给与查询权限
5、终端指定登录的主机的数据库
mysql -h 192.168.168.11 -u ly1 -p ##终端指定登录的主机的数据库,注意只能在指定得主机上登录,不一定在数据库得主机上。
6、给与查询,插入,更改,修改,删除权限
grant select,insert,update,alter,delete on xy102.* to 'ly'@'192.168.168.12';##给与查询,插入,更改,修改,删除权限
flush privileges;
7、取消查询,插入,更改,修改,删除权限
revoke select,insert,update,alter,delete on xy102.* to 'ly'@'192.168.168.12';取消查询,插入,更改,修改,删除权限
flush privileges;
8、删除用户
drop user 'ly'@'192.168.168.12';##删除用户
9、创建用户
create user 'ly'@'192.168.168.12' IDENTIFIED BY '123456';##创建用户,指定192.168.168.12登录,到创建用户得数据库
mysql -h 192.168.168.11 -u ly -p123456##只能在192.168.168.12主机上登录
10、免密登录:
[root@mysql1 ~]# vim /etc/my.cnf
server-id = 1
skip-grant-tables ##增加这行可以实现免密登录
[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# mysql -u root -p
Enter password: 回车
三、实操:增删改查日测题(上机实操)
1、创建库,库名 country
2、创建表,表名 province和city 要求如下:
1)、province包含字段:
ct_id为区号,最多四位 主键;
ct_name 城市名 最长5位,不能为空,不能重复
ct_scale 城市规模,可重复,不能为空。
2)、city包含字段
c_id 主键
c_street 不能为空,不能重复。
c_subway int 不能重复 可以为空
c_weather 字符串 可以空 默认值:晴
通过修改表city,创建外键关联province的主键,ct_id。
分别插入5条数据,任选。不限制
展示表1的前两行,展示表2的2-4行
有重复数据,去重查询。
修改表city,将c_weather的默认值改为:不详
create DATABASE country;
#创建库,库名 country
CREATE TABLE province (
ct_id int(4) zerofill primary KEY auto_increment,
ct_name VARCHAR(5) NOT null UNIQUE KEY,
ct_scale varchar(128) not null
);
select * from province;
desc province;
CREATE TABLE city (
c_id int(4) zerofill not null primary KEY auto_increment,
c_street VARCHAR(5) NOT null UNIQUE KEY,
c_subway INT(5) UNIQUE KEY,
c_weather varchar(15) default '晴'
);
desc city;
select * from city;
select * from province;
ALTER table city add ct_id int(4) zerofill NOT NULL;
ALTER TABLE city ADD CONSTRAINT FOREIGN KEY (ct_id) REFERENCES province(ct_id);
ALTER TABLE city ADD FOREIGN KEY (ct_id) REFERENCES province(ct_id);
foreign key (ct_id) references province (ct_id);
select * from province;
insert into province values(1,'niu','123');
insert into province values(2,'niu1','123');
insert into province values(3,'niu3','234');
insert into province values(4,'niu4','456');
insert into province values(5,'niu5','56');
insert into city values(1,'xin','1',NULL,2);
insert into city values(2,'xin2','2','晴',1);
insert into city values(3,'xin3','3','晴',3);
insert into city values(4,'xin4','4','晴',4);
insert into city values(5,'xin5','5','晴',3);
insert into city values(7,'xin7','7','晴',5);
select * from province limit 0,2;
select * from city limit 1,3;
SELECT DISTINCT c_weather FROM city;
ALTER TABLE city MODIFY COLUMN c_weather varchar(15) default '不详';
ALTER TABLE city MODIFY COLUMN c_weather VARCHAR(255) DEFAULT '不详';
ALTER TABLE city ADD CONSTRAINT FOREIGN KEY (ct_id) REFERENCES province(ct_id);