面试康复训练-SQL语句

一，数据库操作

1查看所有库

show databases;    --查看所有库

2使用数据库

use 数据库名;  --使用数据库；

3查看当前使用数据库

select database(); --查看当前使用的数据库

4 创建数据库

create databse 数据库名 charset=utf8; --创建数据库

5删除数据库

drop database 数据库名; --删除数据库

6修改utf8，gbk等格式

alter database数据库名称 default charset=新的编码格式；

场景：导入数据库

source 具体地址/aresa.sql

一、导入前的准备工作

1. 确认数据格式：

   • SQL 文件：包含表结构和数据的 .sql 文件。

   • CSV/Excel 文件：结构化数据文件。

   • 其他数据库备份文件：如 MySQL 的 .dump、PostgreSQL 的 .pgdump。

2. 创建目标数据库和表结构：

   • 如果导入的是纯数据文件（如 CSV），需先手动创建匹配的表结构。

   • 如果导入的是 SQL 文件，通常会自动创建表。

3. 检查权限：

   • 确保数据库用户有权限执行导入操作（如 FILE 权限、写入权限）。

二，常见错误

1. 错误：Access denied for file import

   • 原因：数据库用户无文件读取权限。

   • 解决：

     • MySQL：在 my.cnf 中设置 secure_file_priv = '' 并重启服务。

     • PostgreSQL：使用 \copy 代替 COPY。

2. 错误：Incorrect datetime format

   • 原因：CSV 中的日期格式与数据库不匹配。

   • 解决：预处理数据或指定格式：

二，数据表操作

1查看所有表

show tables;

2查看表结构

desc 表名;

3创建表

create table 表名 （
    
    字段，类型，约束，

    字段，类型，约束
)
                                
--int unsigned 无符号整形
--auto_increment 自动增长
--not null 不能为空
--parmary key 表示主键
--default 默认值

例
CREATE TABLE `teachers` (
`Tid` varchar(11) NOT NULL,
`Tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`Tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4查看建表语句

show create table 表名；

5修改表-添加字段

alter table 表名 add 列名 类型；

6修改表-修改字段，不重命名

alter table 表名 modify 列名 类型及约束

7修改表-修改字段-重命名

alter table 表名　change　字段原名字　字段新名字　类型及约束

８修改表－删除字

alter table 表名 drop 列名 

9删除表，库

drop table 表名;
drop databese 数据库名;

三，数据表的ＣＵＲＤ

一　Ｃ－插入操作

１全列插入，多行插入

insert into 表名 （字段1，字段2） values （值1，值2）;
例子
INSERT INTO students (name, sex, hometown, age, class, card)
VALUES ('王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
( '张飞', '男', '南京', '24', '3班', '340322199003247654'),
( '白起', '男', '安徽', '22', '4班', '3403221 99005247654');

省略写法，值对应上就行。
INSERT INTO courses
VALUES ('1', '数据库'),
('2', 'qtp'),
('3', 'linux'),
('4', '系统测试');

主键字段可用0 null，default，占位。

二　Ｕ－改操作

１全部修改

update 表名 set 字段 = 值;

２按条件修改

update 表名 set 字段 = 值  where 条件

update students set age = 18 where name='百里守约'

３按条件修改多个值

update student set gender = 4，high =1.80 where id = 3;

三　Ｄ－删除操作

１物理删除

delete from 表名 where 条件;

delete from student where id =4;

２逻辑删除

--用一个字段标识是否删除
--给student表添加一个is_delete字段bit类型。
--alter table 表名 add 字段 类型 default 默认值

alter table students add is_delete bit defalult 0;

四　Ｒ－查看（读）操作

1查询所有字段

select * from 表名

2查询指定字段

select 列1，列2 from 表名;

3使用as给字段起别名

select 字段 as 别名 from 表名;
select name as "名字"，age from students;

4 distinct字段，消除重复行

select distinct gender from students;

5条件查询-比较运算符 > ,<, >= , <=

select * from students where age > 18;
select * from students where age < 18;
select * from students where age >= 18;
select * from students where age >= 18;
-- !=和 <>均表示不等于，使用较少，了解即可
SELECT * FROM users WHERE age != 30;
SELECT * FROM users WHERE age <> 30;

6条件查询-逻辑运算符and ，or，not

-- and，18和28之间的学生信息
select * from students where age>18 and age<28;

--and ,18岁以上女性
select * from students where age>18 and gender="女";

--or 18以上或者身高180以上
select * from students where age >18 or height>=180;
 
--not 不在18岁以上的女性
select * from students where not(age>18 and gender="女")

7条件查询-模糊查询like%__

--like
--%替换任意个， _ 替换一个

--查询姓名中，以‘小’开头的名字
select * from students where name like'小';

--查询姓名中有‘小’字的所以名字;
select * from students where name like'%小%';

--查询有2个字的名字
select * from students where name like '__';

--查询至少有2个字的名字
select * from students where name like'__%';
select * from students where name not like '__';

8条件查询-范围查询in， not in， between ... and ...

--in(1,3,8)表示在一个非连续范围内
--查询年龄为18或35的名字
select * from students where age in (18,35);
select * from students where age>18 or age<35;
--not in 不在范围内
--查询年龄为18或35的名字
select * from students where age not in (18,35);

--between and在连续范围之间
--查询年龄在18到36之间的信息;
select * from students where age between 18 and 34;
select * from students where age >18 and age<35 ;

9空判断is null

--判空
--查询身高信息为空的信息
select * from student where heigh is null;

--判非空

select * from student where heigh  is not null;

10条件查询-排序order by，asc desc

--asc 从小到大，升序
-desc 从大到小排序，降序

--查询年龄在18到35之间的男性，按年龄从小到大排序
select * from students where (age between 18 and 35) order by age asc


--查询年龄在18到35之间的女性，按身高高到矮，年龄小到大排序
select * from students where (age between 18 and 35) and gender='女' order by height desc ,age asc;

11条件查询-聚合函数count max min sum avg  round

--count 总数

--查询男性有多少人
select count(*) from students where gender ='男';

--最大值max

--查询最大的年龄
select max(age) from students;

--查询女性最高身高
select max(hieght) from students where gender="女";


--最小值min

--求和 sum
--计算所有人的年龄和
select sum(age) from students;

--平均值avg
--计算年龄平均值
select avg(age) from students;

--四舍五入 round  round（123.23， 1）保留一位小数
--计算男性平均身高保留2位小数
select round( avg(height),2) from students;

12条件查询-分页分组 group by,group_concat,having

select 分组字段 from 表名 group by 分组字段

having子句用于在group by之后设置条件，与where类似但能与聚合函数一起使用。

group_concat 函数：将多行数据合并成一行,查询一对多

13条件查询左链接 left join ， inner join

inner join 返回2表中匹配行
left join ，返回左表所有行，右表无匹配返回NULL

四， 进阶一  分页查询limit关键字

SELECT * FROM 表名
ORDER BY 排序字段  -- 分页必须明确排序规则
LIMIT 每页数量 OFFSET 偏移量;

PS 加餐：python封装的mysql操作。项目中将操作，连接等封装。方便执行，和代码易读

#数据库连接
def dbConnect(hostname,username,password,database):
    db = pymysql.connect(host=hostname,user=username,
                         password=password,database=database,
                         charset='utf8mb4',#mysql字符格式
                         cursorclass=pymysql.cursors.DictCursor)
    return db;