MySQL基础操作指南

一、数据库操作

1.1 概念阐述

基本操作	语法结构
创建数据库	create database 数据库名 character set utf8;
删除数据库	drop database 数据库名
选择数据库	use 数据库名；
查看当前选择的数据库	select database();
查看当前数据库包含的数据表	show tables;
查看数据库建表语句	show create database 数据库名;

1.2 相关代码

# 创建数据库
create database mark_test character set utf8;
# 查看数据库建表语句
show create database mark_test;
# CREATE DATABASE `mark_test` /*!40100 DEFAULT CHARACTER SET utf8 */
# 切换数据库
use mark_test;
# 查看数据库表
show tables;
# 查看当前所在的数据库
select database();
# 删除数据库
drop database mark_test;

---

---

二、数据库表操作

2.1 概念阐述

基本操作	语法结构
创建表	create table表名[列及类型]；
查看表结构	desc 表名;
修改表	alter table 表名 add[change]drop 列名；
修改表名称	rename table 原名 to 新名;
查看表的创建语句	show create table 表名；
删除表	drop table 表名;

2.2 数据表约束条件

约束条件	说明
primary key	主键（唯一、不能为空）
not null	不为空
null	允许为空
auto_increment	自增长（一般和主键结合使用）
unique	唯一，不能重复
foreign key	外键，指向另一个表的主键

2.3 相关的代码

# 创建表
create table sutdent(id int primary key auto_increment,name varchar(20),bothday datetime,sex char(1)
)charset =utf8;# 查看表结构
desc sutdent;# 修改表
# 1.添加字段
alter table sutdent add tel int(11);
alter table sutdent add class int after id;
desc sutdent;
# 2. 修改数据类型
alter table sutdent modify class varchar(10);
desc sutdent;
# 3. 删除字段
alter table sutdent drop class;
desc sutdent;
# 4. 修改字段名
alter table sutdent change bothday birthday datetime;
desc sutdent;
# 5. 表重命名(2法)
alter table sutdent rename studen;
desc studen;
rename table studen to student;
desc student;# 删除表
drop table student;
desc student;

2.4 alter表字段操作

• 添加字段(add)
  • alter table 表名 add 字段名 数据类型;
  • alter table 表名 add 字段名 数据类型 first;
  • alter table 表名 add 字段名 数据类型 after 字段名; # 在那个字段名之后添加
• 删除字段(drop)
  • alter table 表名 drop字段名;
• 修改数据类型(modify)
  • alter table 表名 modify 字段名 新数据类型;
• 修改字段名(change)
  • alter table 表名 change 旧字段名 新字段名 新数据类型;
• 表重命名(rename)
  • alter table 表名 rename 新表名;

2.5 练一练

作业要求：

• 1、创建老师表
• 2、给教师表添加 tel char (11) not null 这个字段；
• 3、在age之后添加height字段；
• 4、删除height字段；
• 5、修改age字段为最小的整数字段；
• 6、把sex字段改成gender;
• 7、把老师表改名。

# 创建老师表
create table LaoShi(id int primary key auto_increment,name varchar(20) not null,sex char(1) not null
)charset = utf8;
desc LaoShi;
# 添加tel字段
alter table LaoShi add tel char(11) not null;
# 添加age字段
alter table  LaoShi add age int not null after sex;
# 添加height
alter table LaoShi add height float not null after age;
# 修改age类型
alter table LaoShi modify age TINYINT;
# 修改sex字段名为gender
alter table LaoShi change sex gender char(5);
# 修改表名
alter table LaoShi rename teachers;

---

---

三、数据库DML操作

3.1 DML基本操作

• 插入数据（增）
  • 全列插入：insert into 表名 values(值1，值2，…)；
  • 缺省插入：insert into 表名(列1，列2，…) values(值1，值2，…)；
  • 批量插入：insert into 表名 values (值1，值2，…), (值1，值2，…), …；
• 更新数据（改）
  • update 表名 set 列1=值1，… where 条件；
• 删除数据（删）
  • delete from 表名 where 条件；
  • delete from 表名；
  • truncate table 表名；

3.2 相关代码

create table students(id int primary key auto_increment,age int not null,name varchar(10)
)engine =innodb charset =utf8;# 查看表结构
desc students;
# 插入数据# 1、 全列插入
insert into students values(0, 15, "小明"); # 要与表字段名一一对应
select *from students;# 查看表已有数据# 2、 缺省插入
alter table students modify age int; # 修改约束
insert into students(id, name) values(0, "小芳");# 3、 批量插入
insert into students values(0, 13, "张三"),(0, 14,"李四"),(0, 18,"王五");# 更新数据
update students set age=18 where age>=14;
update students set age=5 where name="小芳";
# 删除数据
delete from students where age<=5;
select *from students;

---

---

四、数据库DQL操作

4.1 数据库数据表常见查询语句

• 全部查询

  select *from 表名
  

• 条件查询

• select *from 表名 where 条件;
  

• 算法运算符：+、-、*、/、%(求余)…

• 比较运算符：>、<、=、!= <>(不等于)、<= ，>=， between(两者之间) and … ，in…

• 逻辑运算符：and，or，not/! ，…

• 位运算符：&，|，^，！，<<，>>

4.2 其他查询

• 模糊查询

• 分页查询

• 排序查询

4.3 题目练习

4.3.1 常见查询操作练习

创建学生表，并插入数据

# 创建表
create table student(id int auto_increment primary key ,age int,sid char(20),sex bit,name char(20),isDelete bit default 0
)engine =innodb charset =utf8;
# 查看表结构
desc student;
# 插入数据
insert into student(age, sid, sex, name) values
(18, "0001", 1, "liudehua"),
(20, "0002", 0, "zhanghuimei"),
(23, "0003", 1, "daotang"),
(15, "0004", 0, "yunduo"),
(30, "0005", 1, "zhangxueyou"),
(32, "0006", 1, "zhangguorong"),
(22, "0007", 0, "liruotong"),
(40, "0008", 1, "zhongminghgiu"),
(50, "0009", 1, "zhouxingchi"),
(30, "0010", 1, "zhazhahui"),
(60, "0011", 1, "renxiaoqi");# 缺省插入
insert into student(age,sex,name) values(18, 1, "liudehua");
insert into student(age,sid, sex,name) values(18, '', 1, "liudehua");
insert into student(age,sex,name) values(36, 1, "高磊"),(20, 1, "高鹏鹏"),(30, 1, "高子龙"),(20, 1, "高兴") ;
insert into student(age,sex,name) values(34, 1, "高%");
insert into student(age,sex,name) values(34, 1, "高%1"),(34, 1, "高%2"),(34, 1, "高%3");

1、查询年龄在30岁以下的学生信息

2、查询年龄在20~30岁之间的学生信息（包含）

3、查询学生年龄小于20岁或大于30岁的学生信息

4、查询学生id为1，3，5的学生信息。

5、查询学生sid不为空的学生信息

# 全部查找
select *from student;# 1、查询年龄在30岁以下的学生信息
select *from student where age<=30;# 2、查询年龄在20~30岁之间的学生信息（包含）
select * from student where age between 20 and 30;
select * from student where age>=20 and age<=30;
# 3、查询学生年龄小于20岁或大于30岁的学生信息
select *from student where age<20 or age>30;
# 4、查询学生id为1，3，5的学生信息。
select *from student where id in(1,3,5);
# 5、查询学生sid不为空的学生信息
select *from student where sid is not null;  # 会把空字符的也保留下来。
select *from student where sid !=''; # 应该使用这种形式查看非空信息

4.3.2 时间查询操作练习

• 时间类型相关概述

类型	大小（字节）	范围	格式	用途
date	3	1000-01-01~9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59’ ~ 838:59:59’	HH:MM:SS	时间值或持续时间
year	1	1901~2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00 ~9999-12-31 23:59:59’	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:00 ~2038结束时间是第2147483647秒，北京时间2038-1-18 11：14：07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

• 时间日期函数：

  • now()返回服务器当前时间，格式对应datetime型
  • curdate()返回当前日期，格式对应date型
  • curtime()返回当前时间，格式对应time型

• 时间操作

• 日期运算：select *from 表名 where 字段名 运算符(时间-interval 时间 间隔单位)

练习1

'''
题目要求：1、创建马拉松表 marathon2、编号，姓名，注册时间，成绩；id int, name varchar(32), registDateTime, score time3、给表添加数据，注册时间分别 now、curdate、curtime。对比效果。
'''
# 建表
create table marathon(id int auto_increment primary key ,name varchar(32),regist datetime,score time
)engine =innodb charset =utf8;
desc marathon;# 插入数据
insert into marathon values(0, "aaa", now(),now());
insert into marathon values (0, "bbb", curdate(),curdate());
insert into marathon values (0, "ccc", curtime(),curdate());
select *from marathon;

练习2：在练习1的基础上增加操作

"""
题目要求：1、向表marathon中添加一些数据，查询马拉松成绩大于2：30分的人（两种写法）2、查找马拉松表距现在一周以内的报名信息。
"""
# 添加一些数据
insert into marathon(id, name, regist, score) values
(null, "ddd", "2022-12-01", "20:50:30"),
(null, "eee", "2022-11-01", "20:50:25"),
(null, "fff", "2022-12-03", "20:50:30"),
(null, "ggg", "2022-12-14", "20:10:30"),
(null, "mmm", "2023-03-03", "20:10:00"),
(null, "ggg", "2022-03-04", "20:40:20");
select *from marathon;# 查找马拉松成绩时间大于2:30分的人
select * from marathon where score>='2:30:00';
select * from marathon where score>(time('3:00:00') - interval 30 minute );# 查找马拉松表距现在一周以内的报名信息。
select *from marathon where regist>(now() - interval 7 day);

4.3.3 其他查询操作练习

题目要求：在之前创建的学生表中，完成以下操作：

​ 1、查询姓高的学生信息
​ 2、查询姓高并且姓名由两个汉字组成的学生信息
​ 3、查询学生姓名为高%的学生信息
​ 4、查询前5条学生信息
​ 5、查询名字不为空，sid不为空的所有学生信息，按照年龄降序输出。

# 1、查询姓高的学生信息
select * from student where name like '高%'; # %代表以”高“开头的任意字符
# 2、查询姓高并且姓名由两个汉字组成的学生信息
select *from student where name like '高_'; # _代表以”高“开头的一个字符
# 3、查询学生姓名为高%的学生信息
select * from student where name='高%';
# 4、查询前5条学生信息
select * from student limit 5;
# 5、查询名字不为空，sid不为空的所有学生信息，按照年龄降序输出。
select *from student where name!='' and sid!='' order by age desc;