MySQL的几个简单使用_如何更简单的使用mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_44728197/article/details/103282331

本文详细介绍如何使用UTF-8字符集在MySQL中创建数据库和表，并设置主键自增及外键。提供了完整的建表语句示例，包括学生信息表和选课表。此外，还介绍了几种常见的SQL操作语句，如插入、更新、删除和查询，以及级联查询和去重技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表语句

MySQL的默认字符集不是utf-8，所以一旦元组有中文就报错，所以要
以utf-8类型建数据库和表，设置主键自增，外键写法

create database student_1 CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE student (
Sno varchar(100) PRIMARY KEY, 
Sname varchar(100) DEFAULT NULL, 
Ssex int(11) DEFAULT NULL, 
Sage int(11) DEFAULT NULL, 
Sdept varchar(100) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE choice (
Cid int(11) PRIMARY KEY AUTO_INCREMENT,
Sno varchar(100),
Cname varchar(100) DEFAULT NULL, 
Ccredit int(11) DEFAULT NULL,
Cgrade int(11) DEFAULT NULL,
Cteacher varchar(100) DEFAULT NULL,
FOREIGN KEY (Sno) REFERENCES student (Sno)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

几个简单语句

第一组：这里面几个带问号的是在JAVA里面写查询语句，然后可以对每个问号赋值来方便或批量改变语句用的，最后一句group可以用来去掉重复的（也不能说去掉，只是看起来重复的没了）

insert into student values('1001','李勇',1,20,'CS');
insert into choice values(default,'1001','数据结构',5,68,'陈老师');
INSERT INTO student(Sno,Sname,Ssex,Sage,Sdept) VALUES(?,?,?,?,?);
UPDATE student set Sname=?,Ssex=?,Sage=?,Sdept=? WHERE Sno=?
delete from student where Sno = ?
"select * from student where Sno = " + studentNumber
//级联查询
select student.Sno,student.Sname,choice.Cteacher from student,choice where student.Sno=choice.Sno
select student.Sno,choice.Cteacher from student,choice where student.Sno=choice.Sno group by choice.Cgrade

第二组：因为字符型的写sql时要加单引号，一定要记住

sql = "delete from " + table + " where " + para + " = \"" + studentNumber + "\"";
sql = "delete from " + table + " where " + para + " = " + text;