约束条件和数据库的用户管理

数据库的增删改查

查

约束条件和用户管理

删

约束条件：

主键

主键约束 primary key

用于标识表中的主键列的值，而且这个值是全表当中唯一的，而且值不能为null。

一个表只能有一个主键。

外键：用来建立表与表之间的关系。确保外键中的值与另一个表的主键值匹配。保证数据引用的完整性。

多表联查，不要超过三张。超过三张表，会降低查询效率。

非空约束：保证列中的值不含null值

唯一性约束：

唯一性约束：确保列中的所有值都是唯一的，类似主键，但是一个表可以有多个唯一约束。

自增约束：

自增约束：在列生成的每一行都会自动生成一个唯一标识符，通常和主键一起使用，每次插入新行时，自增列的值会自动增加。

扩展语句：

CREATE TABLE if not exists test01 (
#表示在创建表时自动检测表是否已经存在，如果不存在就创建。
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)
);

INSERT into test01 (name,score,address) VALUES(null,"阿斯顿",87,"南京");
INSERT into test01 VALUES(null,"周星驰",87,"南京");

CREATE TABLE `class` (
  `stu_id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `address` varchar(128) DEFAULT '地址不详',
  `crad_id` int NOT NULL,
  PRIMARY KEY (`stu_id`),
  KEY `crad_id` (`crad_id`),
  CONSTRAINT `class_ibfk_1` FOREIGN KEY (`crad_id`) REFERENCES `student` (`crad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

删除外键：

mysql> show CREATE TABLE class2;
  CONSTRAINT `class2_ibfk_1` FOREIGN KEY (`crad_id`) REFERENCES `student2` (`crad_id`)
 
 ALTER TABLE class2 DROP FOREIGN key class2_ibfk_1;

外键就是和主表进行关联的列，不需要设置为从表的主键，但是不能为空，必须和主表的数据类型保持一致。

外键的值和主键的值要相同。

先插入主表的数据，再插入从表的数据

删除表的外键不是直接删除外键的列名，而是删除外键的索引，show create table 表名; 查看表的详细信息。

删除主键不需要加上主键的列名，如果有extra的额外属性，比如说自增长，要先移除属性，然后才能删除主键。

删除主键的方式：alter进行修改

复制表：

CREATE TABLE stu_012 like stu01;    #只复制表
INSERT INTO stu_012 SELECT * from stu01;    #只复制表中数据
CREATE TABLE stu_02 (SELECT * from stu01);      #既复制表，又复制表中数据

临时表：

临时表创建完成之后，在库里面是看不到的，但是依然可以增删改查，但是重新连接之后，临时表就消失了。

清空表：

drop 删除表

如何删除表内的数据，但是保留表和表内结构：

delete  from  表名;    #是一行一行的清空表数据，速度比较慢，如果有自增长字段，delete清空之后会继续按照原来的序号继续递增

truncate  table  表名;    #也是清空表，保留表结构，但是清空之后原有的记录全部抹去，自增长也将从头开始，速度比较快。

数据库的用户管理：

root都是相同的，host：可以登录的主机，localhost指的是本地登录 %：任意主机（IP地址）。

权限上：localhost > %的权限

创建用户：

root@local host 安装完mysql之后就有了，不需要额外设置。其他的都需要人工创建。

其他的用户也不设置成localhost ，都是设置主机名。

create user 'zxc'@'192.168.60.92' identified by '123456';       #创建用户名和密码

identified by '123456' 就是创建密码

删除用户：

mysql> drop user 'zxc'@'192.168.60.92';

设置用户权限：

grant all privileges on *.* to 'zxc'@'192.168.60.92' ;  #给创建的用户赋权

all 给与所有权限 远程登录，数据库的操作权限（增删改查）

*.* *：表示库 *：库里的表

information_schema：这个库的作用包含了mysql服务器中所有其他数据库，表，列。索引权限等详细的元数据的信息，可以查询数据库的结构和元信息

performance_schema：包含mysql的服务性能和资源的利用情况。查询语句的执行时间和锁定等信息。

usage 允许改用户连接到数据库服务器。

mysql> grant insert,update,alter,delete on xy102.* to 'qwe'@'192.168.60.922';       #设置用户的增删改查权限
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;        #刷新权限
Query OK, 0 rows affected (0.00 sec)

取消权限：

mysql> revoke insert,update,alter,delete on xy102.* from 'qwe'@'192.168.600.92';        #删除用户的增删改查权限
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;        #刷新权限
Query OK, 0 rows affected (0.00 sec)

mysq的免密登录

[root@mysql1 ~]# vim /etc/my.cnf
18 server-id = 1
 19 skip-grant-tables       #加入这一行
 20 sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

补充

insert插入数据时，时间类型

date

datetime

timestamp

CREATE TABLE stu06 (
id int(5) primary key,
name char(10) ,
date_time date not null ,
date_times datetime,
date_timee timestamp 
);

实验：

增删改查日测题(上机实操) 1、创建库，库名 country

2、创建表，表名 province和city 要求如下:

1)、province包含字段： ct_id为区号，最多四位 主键； ct_name 城市名 最长5位，不能为空，不能重复 ct_scale 城市规模，可重复，不能为空。

CREATE TABLE province (
ct_id int(4) ZEROFILL PRIMARY KEY ,
ct_name char(5) not null UNIQUE key,
ct_scale char(10) not NULL
);
desc province;

2)、city包含字段 c_id 主键 c_street 不能为空，不能重复。 c_subway int 不能重复 可以为空 c_weather 字符串 可以空 默认值：晴

CREATE TABLE city (
c_id int(4) ZEROFILL PRIMARY KEY ,
c_street char(20) not null UNIQUE key,
c_subway int(10) UNIQUE key,
c_weather char(5) DEFAULT '晴',
ct_id int(4) ZEROFILL,
FOREIGN key (ct_id) REFERENCES province (ct_id)         #通过修改表city，创建外键关联province的主键，ct_id。
);
desc city;

。

分别插入5条数据，任选。不限制

展示表1的前两行，展示表2的2-4行

SELECT * from province limit 0,2;
SELECT * from city limit 1,3;

有重复数据，去重查询。

SELECT DISTINCT c_weather from city;

修改表city,将c_weather的默认值改为：不详

alter TABLE city MODIFY c_weather char(5) DEFAULT '不详';