mysql代码User表结构如下 保存的信息 列名 类型及长度 是否允许为空 说明 序号 User_id int(10) 否 自动增长、主键 账号名称 User_name varchar(18) 否 不允许重复 密码 User_psw varchar(32) 否 密码是md5加密之后的 手机号 User_phone varchar(11) 是 账号类型 User_type tinyint(1) 否 若是vip客户,则取值1,否则取值0,默认值为0 消费总金额 User_account float 否 默认值为0
时间: 2024-03-26 12:38:15 浏览: 18
可以使用以下的MySQL代码来创建这个用户表:
```sql
CREATE TABLE User (
User_id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
User_name VARCHAR(18) NOT NULL UNIQUE,
User_psw VARCHAR(32) NOT NULL,
User_phone VARCHAR(11),
User_type TINYINT(1) NOT NULL DEFAULT 0,
User_account FLOAT NOT NULL DEFAULT 0
);
```
这个代码会创建一个名为User的表,包含了上述的六个列,并且设置了一些约束条件:
- User_id是主键,自动增长;
- User_name不允许为空并且需要是唯一的,用于作为登录账号;
- User_psw存储的是经过md5加密的密码,不允许为空;
- User_phone是可选的,可以为空;
- User_type用于表示账号类型,不允许为空,取值为0或1,默认为0;
- User_account用于表示消费总金额,不允许为空,默认为0。
需要注意的是,这里使用了AUTO_INCREMENT和PRIMARY KEY来设置User_id列为主键,并且使用了UNIQUE来设置User_name列为唯一的。同时,User_type和User_account列都设置了默认值,这样在插入新数据时如果没有指定这两个列的值,就会自动使用默认值。
相关问题
Subsidy(补贴表) 字段名 类型 注释 其他 id int 序号 主键,非空 subsidyname varchar(50) 考勤 空 Amount decimal(10) 补贴金额 空 根据以上内容用java写出增删改查方法类
以下是使用Java语言实现的增删改查方法类:
```java
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class SubsidyDao {
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;
// 连接数据库
public Connection getConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
conn = DriverManager.getConnection(url, user, password);
return conn;
}
// 关闭连接
public void closeConnection() throws Exception {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
}
// 添加补贴记录
public void addSubsidy(Subsidy subsidy) throws Exception {
conn = getConnection();
String sql = "INSERT INTO subsidy (id, subsidyname, Amount) VALUES (?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, subsidy.getId());
pstmt.setString(2, subsidy.getSubsidyname());
pstmt.setBigDecimal(3, subsidy.getAmount());
pstmt.executeUpdate();
closeConnection();
}
// 删除补贴记录
public void deleteSubsidy(int id) throws Exception {
conn = getConnection();
String sql = "DELETE FROM subsidy WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
closeConnection();
}
// 修改补贴记录
public void updateSubsidy(Subsidy subsidy) throws Exception {
conn = getConnection();
String sql = "UPDATE subsidy SET subsidyname=?, Amount=? WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, subsidy.getSubsidyname());
pstmt.setBigDecimal(2, subsidy.getAmount());
pstmt.setInt(3, subsidy.getId());
pstmt.executeUpdate();
closeConnection();
}
// 查询所有补贴记录
public List<Subsidy> queryAllSubsidy() throws Exception {
conn = getConnection();
String sql = "SELECT * FROM subsidy";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
List<Subsidy> list = new ArrayList<>();
while (rs.next()) {
Subsidy subsidy = new Subsidy();
subsidy.setId(rs.getInt("id"));
subsidy.setSubsidyname(rs.getString("subsidyname"));
subsidy.setAmount(rs.getBigDecimal("Amount"));
list.add(subsidy);
}
closeConnection();
return list;
}
// 根据id查询补贴记录
public Subsidy querySubsidyById(int id) throws Exception {
conn = getConnection();
String sql = "SELECT * FROM subsidy WHERE id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
Subsidy subsidy = null;
if (rs.next()) {
subsidy = new Subsidy();
subsidy.setId(rs.getInt("id"));
subsidy.setSubsidyname(rs.getString("subsidyname"));
subsidy.setAmount(rs.getBigDecimal("Amount"));
}
closeConnection();
return subsidy;
}
}
```
其中,Subsidy类如下所示:
```java
public class Subsidy {
private int id;
private String subsidyname;
private BigDecimal amount;
public Subsidy() {}
public Subsidy(int id, String subsidyname, BigDecimal amount) {
this.id = id;
this.subsidyname = subsidyname;
this.amount = amount;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSubsidyname() {
return subsidyname;
}
public void setSubsidyname(String subsidyname) {
this.subsidyname = subsidyname;
}
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
}
```
使用方法:
```java
public class Main {
public static void main(String[] args) {
SubsidyDao dao = new SubsidyDao();
// 添加补贴记录
Subsidy subsidy1 = new Subsidy(1, "考勤", new BigDecimal("1000.00"));
try {
dao.addSubsidy(subsidy1);
} catch (Exception e) {
e.printStackTrace();
}
// 查询所有补贴记录
List<Subsidy> list = null;
try {
list = dao.queryAllSubsidy();
} catch (Exception e) {
e.printStackTrace();
}
for (Subsidy subsidy : list) {
System.out.println(subsidy.getId() + " " + subsidy.getSubsidyname() + " " + subsidy.getAmount());
}
// 修改补贴记录
Subsidy subsidy2 = new Subsidy(1, "出勤", new BigDecimal("1500.00"));
try {
dao.updateSubsidy(subsidy2);
} catch (Exception e) {
e.printStackTrace();
}
// 查询特定补贴记录
Subsidy subsidy3 = null;
try {
subsidy3 = dao.querySubsidyById(1);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(subsidy3.getId() + " " + subsidy3.getSubsidyname() + " " + subsidy3.getAmount());
// 删除补贴记录
try {
dao.deleteSubsidy(1);
} catch (Exception e) {
e.printStackTrace();
}
}
}
```
有以下MySQL代码,SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS employee_info; CREATE TABLE employee_info ( e_id int(0) NOT NULL COMMENT '工作人员编号', e_name varchar(50) NOT NULL COMMENT '工作人员姓名', m_id int(0) NOT NULL COMMENT '管理员编号', PRIMARY KEY (e_id) USING BTREE, INDEX m_id(m_id) USING BTREE, CONSTRAINT m_id FOREIGN KEY (m_id) REFERENCES hotel_manager_info (m_id) ON DELETE CASCADE ON UPDATE CASCADE ) ; DROP TABLE IF EXISTS hotel_info; CREATE TABLE hotel_info ( h_id int(0) NOT NULL COMMENT '酒店序号', h_name varchar(25) NOT NULL COMMENT '酒店名称', h_address varchar(255) NOT NULL COMMENT '酒店地址', h_phone varchar(50) NOT NULL COMMENT '酒店电话', o_statistics int(0) NULL DEFAULT NULL COMMENT '统计订单量', PRIMARY KEY (h_id) USING BTREE ) ; DROP TABLE IF EXISTS hotel_manager_info; CREATE TABLE hotel_manager_info ( m_id int(0) NOT NULL COMMENT '管理员编号', m_name varchar(50) NOT NULL COMMENT '管理员姓名', h_id int(0) NULL DEFAULT NULL COMMENT '酒店序号', PRIMARY KEY (m_id) USING BTREE, INDEX hotel_id(h_id) USING BTREE, CONSTRAINT hotel_id FOREIGN KEY (h_id) REFERENCES hotel_info (h_id) ON DELETE CASCADE ON UPDATE CASCADE ) ; DROP TABLE IF EXISTS order_info; CREATE TABLE order_info ( o_id int(0) NOT NULL COMMENT '订单编号', r_id int(0) NOT NULL COMMENT '房间号', u_id int(0) NOT NULL COMMENT '用户id', o_checkin_time datetime(0) NULL DEFAULT NULL COMMENT '入住时间', o_checkout_time datetime(0) NOT NULL COMMENT '离开时间', o_status enum('not null','null') NULL DEFAULT NULL COMMENT '为null 时表示未入住', PRIMARY KEY (o_id) USING BTREE, INDEX r_id(r_id) USING BTREE, INDEX u_id(u_id) USING BTREE, CONSTRAINT r_id FOREIGN KEY (r_id) REFERENCES room_info (r_id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT u_id FOREIGN KEY (u_id) REFERENCES user_info (u_id) ON DELETE CASCADE ON UPDATE CASCADE ) ; DROP TABLE IF EXISTS room_info; CREATE TABLE room_info ( r_id int(0) NOT NULL COMMENT '房间号', r_name varchar(50) NOT NULL COMMENT '房间名称', r_type varchar(50) NOT NULL COMMENT '房间类型', r_price varchar(50) NULL DEFAULT NULL COMMENT '房间价格', h_id int(0) NOT NULL COMMENT '酒店序号', PRIMARY KEY (r_id) US
这是一段 MySQL 代码,用于创建四个表:employee_info(员工信息表),hotel_info(酒店信息表),hotel_manager_info(酒店管理员信息表),order_info(订单信息表),和一个 room_info(房间信息表)的外键。其中,hotel_manager_info表和hotel_info表之间建立了外键关系,employee_info表和hotel_manager_info表之间建立了外键关系,order_info表和user_info表以及room_info表之间建立了外键关系。使用SET FOREIGN_KEY_CHECKS = 0;可以关闭MySQL的外键检查,DROP TABLE IF EXISTS则是当表存在时才会删除表。