day03
-
删除表中所有数据最好的方式是截断表
truncate table 表名
-
删除在子表上的约束
通过show create table 表名 来查看表结构找到约束 然后根据约束名称来删除约束 查询结果: Sales | CREATE TABLE `Sales` ( `SalesID` int NOT NULL AUTO_INCREMENT, `ProductID` int NOT NULL, `CusName` varchar(20) DEFAULT NULL, `ProductNumber` int DEFAULT NULL, `SalesPrice` int DEFAULT NULL, PRIMARY KEY (`SalesID`), KEY `fk_sales_product` (`ProductID`), CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`ProductID`) REFERENCES `Product`(`ProductID`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
根据外键名称删除子表约束
alter table sales drop constraint sales_ibfk_1
-
指定字段插入
insert into (字段名) values (字段值) insert into t_cats (name) values ('cai'); insert into t_cat (name,gender) values ('tom','female')
-
主键约束
-
创建表时就指定约束
create table t_lions(id int ,name varchar(50),gender varchar(20),birthdate date,primary key(id)); -- primary key 中就是主键 可以有一个可以有多个
删除主键约束
alter table t_lions drop primary key;
-
增加主键约束
alter table t_class add constraint t_class_id_pk primary key (id);
-
-
外键约束
-
constraint 约束名
-
定义外键:
create table t_major (id int,name varchar(50),constraint t_major_id_pk primarykey(id)); -- 主表
create table t_class (id int,name varchar(50),major_id int,constraint t_class_id_pk primary key (id),constraint t_class_major_id_fk foreign key (major_id) references t_major (id));
根据约束名称删除外键约束
alter table t_class drop constraint t_class_major_id_fk;
增加外键约束
alter table t_class add constraint t_calss_major_id_fk foreign key (major_id) references t_major (id);
-
-
唯一约束、非空约束
create table t_users(id int ,username varchar(20) not null unique,passwd varchar(10) not null,regist_date date);
-
非空约束 not null
-
唯一约束 unique
-
唯一约束的列不允许有重复的值
-
唯一约束的列允许有一个NULL值
-
-
-
基本查询
-
查询emp表中所有员工的年薪
select empno as 工号,ename 姓名,sal * 12 as 年薪 from emp order by 年薪 DESC; select empno as 工号,ename 姓名,sal * 12 as 年薪 from emp order by 3 DESC;--第三列
-
两列拼接
select concat (first_name,last_name) as name from s_emp; select concat (upper(first_name),lower(last_name)) as name from s_emp;
-
了解最常用的主函数
-
count 用于统计非空行的行数
-
-
-
连接查询
-
内连接:
-
select * from emp,dept where emp.deptno = dept.deptno; 将 emp.deptno = dept.deptno 条件称为 连接条件 将返回满足连接条件的查询称为 内连接(inner join)
-
非等值连接
select e.ename,e.sal,g.grade from emp e inner join salgrade g on e.sal >= g.losal and e.sal <= g.hisal;
select e.ename,e.sal,g.grade from emp e inner join salgrade g on e.sal between g.losal and g.hisal;
-
-
外连接:若查询结果中出了满足连条件的数据外,还包含了不满足连接条件的数据,则将这种查询称为外连接
-
右外连接
join右边 select * from dept d right outer join emp e on e.deptno = d.deptno; select * from dept d right join emp e on e.deptno = d.deptno;
-
左外连接
join左边 select * from dept d left outer join emp e on e.deptno = d.deptno;
-
-