MySQL 数据库（二）—— 常用操作

最新推荐文章于 2022-10-29 13:44:33 发布

流放深圳

最新推荐文章于 2022-10-29 13:44:33 发布

阅读量507

点赞数

分类专栏：数据库文章标签： MySQL 常用数据库语句

本文链接：https://blog.csdn.net/BiandanLoveyou/article/details/80590926

版权

数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

1、建表

drop table if exists t_user;
create table t_user(
  id int(11) not null auto_increment primary key comment '主键，自动增长',
  stuId int(11) not null comment '学生学号',
  stuName varchar(20) not null comment '学生名字',
  age int(3) default null comment '年龄',
  joinTime date default null comment '入学时间',
  introduce text default null comment '自我介绍'
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生信息表';

2、在某个字段后面，增加字段，如果不用after关键字，则默认在后面追加

alter table t_user add grade varchar(10) comment '年级' default null after age;/*在年龄字段后，增加年级*/

3、删除主键

注意，如果在建表或者修改表的时候，设置主键自动增长，无法删除主键，看错误示例：

alter table t_user drop primary key; 提示错误：[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

需要先将自动增长的列删除id的auto_increment约束：

alter table t_user change id id int(11);

再执行删除主键操作：

alter table t_user drop primary key;

4、增加主键

alter table t_user add primary key(id);

5、创建索引，索引名：idIndex，如果是多个列的索引，只需要在括号里增加多个列名，用逗号隔开

alter table t_user add index idIndex(id);

或者：

create index coureIndex on t_course (courseId);

6、删除索引

alter table t_user drop index idIndex;

设置唯一约束：

alter table 表名 add unique (字段名);

7、创建课程表

drop table if exists t_course;
create table t_course(
  id int(11) not null auto_increment primary key comment '主键，自动增长',
  courseId int(11) not null comment '课程id',
  courseName varchar(30) not null comment '课程名字',
  courseCount int(5) default null comment '课程数量',
  introduce text default null comment '课程介绍'
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='课程信息表';

创建学生——课程关联表

drop table if exists t_stu_cour;
create table t_stu_cour(
  id int(11) not null auto_increment primary key comment '主键，自动增长',
  stuId int(11) not null comment '学生id',
  courseId int(11) not null comment '课程id',
  constraint FK_name_t_stuId foreign key (stuId) references t_user (stuId),
  constraint FK_name_t_courId foreign key (courseId) references t_course (courseId)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生-课程信息关联表';

关于MyISAM和InnoDB的对比

在创建学生——课程关联表的时候，我把数据库引擎设置成“ENGINE=InnoDB”，发现报错：

[Err] 1005 - Can't create table 'db_user.t_stu_cour' (errno: 150)

然后去网上搜索答案，得到的答案统一都是：

1、MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持
2、MyISAM表不支持外键
3、在执行数据库写入的操作（insert,update,delete）的时候，MyISAM表会锁表，而InnoDB表会锁行
4、当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择InnoDB表。
当你的数据库主要以查询为主，相比较而言更新和写 入比较少，并且业务方面数据完整性要求不那么严格，就选择MyISAM表。
因为MyISAM表的查询操作效率和速度都比InnoDB要快

我没研究过数据库底层实现，但是实践告诉我，当我把数据库引擎设置为“ENGINE=MyISAM”时，创建外键关联可以通过。还是邓小平那句话“实践是检验真理的唯一标准”。如有不当，恳请指出。

8、单独设置外键

alter table t_stu_cour add constraint FK_name_t_course foreign key (courseId) references t_course (courseId);

9、插入数据

insert into t_user
(id,stuId,stuName,age,grade,joinTime,introduce)
values
(null,201801,'张三',9,'三年级','2018-09-01','大家好，我叫张三'),
(null,201802,'李四',10,'三年级','2018-09-01','大家好，我叫李四'),
(null,201803,'王五',8,'三年级','2018-09-01','大家好，我叫王五')

insert into t_course
(id,courseId,courseName,courseCount,introduce)
values
(null,201,'小学语文',50,'这是小学语文'),
(null,202,'小学数学',48,'这是小学数学'),
(null,203,'小学英语',80,'这是小学English'),
(null,204,'小学品德',55,'这是小学品德'),
(null,205,'小学美术',72,'这是小学美术')

insert into t_stu_cour
(id,stuId,courseId)
values
(null,201801,201),
(null,201801,202),
(null,201801,203),
(null,201801,204),
(null,201801,205),
(null,201802,201),
(null,201802,203),
(null,201803,201),
(null,201803,202),
(null,201803,203)

10、查询数据

①查询年龄大于等于9岁的学生id和学生名字：

select stuId,stuName from t_user where age >= 9;

结果：

②查询课程超过2门的学生，输出学生id，学生姓名以及课程数量

select t1.stuId,t1.stuName,t2.courseCount from
(select stuId,stuName from t_user) as t1,
(select stuId,courseId,count(*)as courseCount from t_stu_cour group by stuId having count(*) > 2 )as t2
where t1.stuId=t2.stuId;

结果：

③查询选了“小学语文”但没有选“小学品德”的学生，输出学生id，并按照学号降序输出

select a.stuId from t_stu_cour as a,t_course as b
where a.courseId=b.courseId and b.courseName='小学语文'
and not exists
(select a1.stuId from t_stu_cour as a1,t_course as b1 where a1.courseId=b1.courseId
and b1.courseName='小学品德' and a1.stuId = a.stuId)
order by a.stuId desc;

结果：

④将“三年级”的同学的年龄都增加1岁，入学时间都增加1天

update t_user set age=age+1,joinTime=DATE_ADD(joinTime,interval 1 day) where grade='三年级';

结果：

⑤从课程表中删除没有选课的记录

delete t from t_course t where not exists (select courseId from t_stu_cour where courseId=t.courseId);

⑥like关键字用法：查询选了“小学语文”且姓“李”的学生，输出学号，学生姓名和所选全部课程名称

select t2.stuId,t2.stuName,t3.courseName from
(select distinct stuId from t_stu_cour where courseId=(select courseId from t_course where courseName='小学语文'))as t1,
(select stuId,stuName from t_user where stuName like '李%')as t2,
(select courseName from t_course where courseId in 
(select courseId from t_stu_cour where stuId in (select stuId from t_user where stuName like '李%')))as t3
where t1.stuId=t2.stuId;

结果：

⑦查询各个课程的选课数量，按照选课数量倒序输出课程id，课程名称，选课数量

select t1.courseId,t1.courseName,t2.count from 
(select courseId,courseName from t_course) as t1
left join
(select courseId,count(*)as count from t_stu_cour group by courseId)as t2
on t1.courseId=t2.courseId order by t2.count desc;

结果：

⑧分页查询学生信息表：查询前2个学生信息

select * from t_user limit 0,2;

⑨MySQL日期、字符串、时间戳互转

时间转字符串：

select date_format(now(),'%y-%m-%d') as today;/*输出：18-06-06*/
select date_format(now(),'%Y-%m-%d') as today;/*输出：2018-06-06*/

时间转时间戳：

select UNIX_TIMESTAMP(now());/*输出：1528277322*/

字符串转时间：

select STR_TO_DATE('2018-06-06 10:25:30','%Y-%m-%d %H:%i:%s');/*输出：2018-06-06 10:25:30*/

时间戳转时间/字符串：

select FROM_UNIXTIME(1528273422);/*输出：2018-06-06 16:23:42*/
select FROM_UNIXTIME(1528276232,'%Y-%m-%d');/*输出：2018-06-06*/

⑩插入重复数据，然后统计重复数据的数量

insert into t_user
(id,stuId,stuName,age,grade,joinTime,introduce)
values
(null,201804,'张三',9,'三年级','2018-09-01','大家好，我叫张三，来自深圳。'),
(null,201805,'张三',10,'三年级','2018-09-01','大家好，我叫张三，来自上海。'),
(null,201806,'李四',11,'四年级','2018-09-01','我叫李四，来自北京。'),
(null,201807,'朱刘',8,'二年级','2018-09-01','我叫朱刘，来自韩国。')

统计学生名字重复的数量：

select stuName,count(*)as repeatCount from t_user
group by stuName
having repeatCount > 1

结果：

防止SQL注入，我们需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装