Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually.

DataGrid 连接MySql数据库报错:

参考:

MySQL报错:Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' property manually.

 

 

2、导入sql的命令行如下:
source 路径名+/mytest_emp_dept.sql 

注意路径名不要引号

 

3、 Data too long for column 'userName' at row 1

解决:set userName utf8 ; 

或者CREATE DATABASE IF NOT EXISTS databaseName DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

第二种方法更方便,如果有多个字段(例如userName)报错,第一种方法很麻烦。

参考:ERROR 1406 (22001): Data too long for column 'c_name' at row 1&&ERROR 1366 (HY000): Incorrect string

 

4、You are not allowed to create a user with GRANT

 

5、Column check constraint 'orders_chk_1' references other column.

解决:

Making check constraint in table level

CREATE TABLE orders
(
	# 订单号
	orderNumber CHAR(32) NOT NULL  , 
	# 订单状态
	orderStatus CHAR(18) check (value in ('预订中','已入住','已退房')) ,
	# 客户身份证
	customerIDCard CHAR(18),
	# 入住房间号
	roomNumber CHAR(6) NOT NULL,
	# 入住时间
	checkInTime DATE NOT NULL,
	# 离店时间
	checkOutTime DATE NOT NULL,
	# 需付金额(由于可能续费,不能作为外键)
	totalMoney INT UNSIGNED NOT NULL,
	# 服务员工号
	waiterID VARCHAR(10) NOT NULL,
	# 备注
	remarks VARCHAR(32),
	orderTime DATE NOT NULL,
	# 主键
	PRIMARY KEY (orderNumber),
	# 外键
	FOREIGN KEY (customerIDCard) REFERENCES customers(customerIDCard),

	FOREIGN KEY (roomNumber) REFERENCES room(roomNumber),

	FOREIGN KEY (waiterID) REFERENCES waiter(waiterID)

) ENGINE=InnoDB;

改为:

CREATE TABLE orders
(
	# 订单号
	orderNumber CHAR(32) NOT NULL  ,
	# 订单状态
	orderStatus CHAR(18) ,
	# 客户身份证
	customerIDCard CHAR(18),
	# 入住房间号
	roomNumber CHAR(6) NOT NULL,
	# 入住时间
	checkInTime DATE NOT NULL,
	# 离店时间
	checkOutTime DATE NOT NULL,
	# 需付金额(由于可能续费,不能作为外键)
	totalMoney INT UNSIGNED NOT NULL,
	# 服务员工号
	waiterID VARCHAR(10) NOT NULL,
	# 备注
	remarks VARCHAR(32),
	orderTime DATE NOT NULL,
	# 主键
	PRIMARY KEY (orderNumber),
	# 外键
	FOREIGN KEY (customerIDCard) REFERENCES customers(customerIDCard),

	FOREIGN KEY (roomNumber) REFERENCES room(roomNumber),

	FOREIGN KEY (waiterID) REFERENCES waiter(waiterID),

	CONSTRAINT orders_ck CHECK (orderStatus in ('预订中','已入住','已退房'))

) ENGINE=InnoDB;

6、授予权限 grant语句格式:

grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"; 

7、新增用户:

grant select,insert,update,delete on book.* to test2@localhost Identified by "abc"

参考:MySQL添加用户、删除用户与授权

8、

grant SELECT,INSERT on timeextension to hotel IDENTIFIED by '1234';语句报错:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED by '1234'' at line 1

原因:此版的的mysql版本把将创建账户和赋予权限分开了。

首先创建用户,然后赋予权限。创建hotel用户后,赋予权限(去掉IDENTIFIED by '1234'):

 grant SELECT,INSERT on timeextension to hotel;

参考:mysql版本:'for the right syntax to use near 'identified by 'password' with grant option'

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值