我是灼灼,一只初学Java的大一金渐层。
向往余秀华和狄兰·托马斯的疯狂,时常沉溺于将情感以诗相寄;追逐过王尔德、王小波的文字,后陷于毛姆和斯蒂芬·金不可自拔;热爱文学的浪潮,白日梦到底却总在现实里清醒;艳羡平静又极度渴盼奔跑的力量。
一篇来自小白的sql小项目记录日志
想要把第一次做小项目的经验记下来 (其实是想夸奖一下自己的成功哈哈)
过程思路有点混乱,错误层出不穷,时间紧张的小可爱可以直接看最后
我的所有命令
本次任务旨在培养构建项目的思维,完成过程中一定要站在构建项目的角度去添加记录和信息,通过SQL语句完成以下内容并书写一份记录文档
项目基本要求:
使用SQL语句完成以下内容,所有字段都设置为notnull,并设置中文注释
SQL语句要求:
- 创建用户表user,包括字段:ID,player_username,player_password,play_createtime(ID为主键并自动递增)
- 创建记录表record,包括字段:game_ID,game_begintime,game_exittime,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,
game_user2cards - 通过SQL语句添加用户1:player_username= wang 用户2:player_username= li
- 通过SQL语句显示所有用户
- 通过SQL语句在record表中添加任意信息的3条用户记录
- 通过SQL语句更新用户2的用户名为:LALALA
- 通过SQL语句查询用户ID为2的record中的所有记录
- 查询record表以game_user1ID 作为索引进行排序显示
为了能够正常的操作实现预期功能,所以先弄清楚每一个命令的具体内容,然后再上手;
要弄清命令必须先梳理思路。
思路
*完成过程中要站在构建项目的角度去添加信息,这个不太懂,没有构建过项目-_-参考了一下网上的回答;查到了一些奇怪的高深的东西,项目复盘?
*所有的字段都设置为notnull非空,还要设置中文注释~
NULL表示字段数据不存在,一个整型字段如果为NULL不表示它的值为0,一个字符串型字段为NULL也不表示它的值为空串,只是表示字段数据不存在,那么notnull就表示字段数据存在,则整型字段的值可以是零,字符型字段的值可以是空串;
这个中文注释应该是要使得每个字段的含义清楚明了,因为它提供的都是英文,所以应该是给字段设置中文注释,注意是所有字段!!!
*创建用户表user,包括字段:ID,player_username,player_password,play_createtime(ID为主键并自动递增)
数据库是Database,但是一个数据表并不是,在学过的东西里面,有展示所有数据库命令,展示当前数据库的所有数据表命令,还有创建数据库命令(这个应该在创建表的前面,因为不知道不创建数据库直接创建表会到哪里去,如果进入了系统库就麻烦了,所以先创建数据库再创建数据表),删除数据库命令(这个如果后面出错了可能要重来一次)
创建一个新数据库命令(我们也把它叫做test吧,因为没有指定):
mysql> CREATE DATABASE test;
要删除一个数据库,使用命令:
mysql> DROP DATABASE test;
这里如果后面弄错了就直接全删没了!!!
创建用户表和删除表的命令:
mysql> CREATE TABLE user;
mysql> DROP TABLE user;
列出当前数据库的所有表的命令(可能之后拥有两个表,会在test数据库里面查看这两个表):
mysql> SHOW TABLES;
查看一个表的结构的命令:
mysql> DESC user;
这个应该要到后面写好很多字段之后才会用到,先记下来;
*创建记录表record,包括字段:game_ID,game_begintime,game_exittime,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,
game_user2cards
这个也放在test数据库里面,因为和user表有联系;
*通过SQL语句添加用户1:player_username= wang 用户2:player_username= li
这个是不是要进入对应的表里面去操作?
噢,不需要,基本插入语句就会写表名的~那只要确定在test数据库里面就行了!
这两个字段的values明显是在user表中的,所以插入语句到user表;
INSERT语句的基本语法是:
INSERT INTO <表名> (字段1, 字段2, …) VALUES (值1, 值2, …);
这里id字段会自增,不需要出现,然后player_createtime如果真的是当前时间的话,那么有默认值,我们也不用考虑了!
就插入两个字段的值就可以了~
*通过SQL语句显示所有用户;
上面刚刚写过这个命令~
其实就是想要展示一下目前的user表,就可以看到刚才添加进去的两个用户了,其它未添加的数据应该都被初始化为notnull值了,那么以后想要添加新数据,就必须用更新来实现!
*通过SQL语句在record表中添加任意信息的3条用户记录
第二个表的插入信息语句,和上上步差不多,不同的是一次插入多条数据(3条),啊不对,其实一样,之前的是一次插入两条数据;
这里要首先明确插入的数据类型,哪一些字段不需要我们插入,比如时间应该让他自动填充:
所有字段展示见问题四,分析也补充到里面了!
*通过SQL语句更新用户2的用户名为:LALALA
UPDATE
语句的基本语法是:
UPDATE <表名> SET 字段1=值1, 字段2=值2, … WHERE …;
更新之后的确认查看,用户2的id=2:
SELECT * FROM user WHERE id=2;
*通过SQL语句查询用户ID为2的record中的所有记录
但是这里存在这个ID=2的用户吗?我是赋值的时候确实有写,好奇怪?
这显然是一个条件查询,命令:
SELECT * FROM record WHERE game_ID=2;
*查询record表以game_user1ID 作为索引进行排序显示
需要指定索引,索引为game_user1ID,单列索引,这个不是唯一索引,它的值是可以重复的!
命令:
ALTER TABLE record
ADD INDEX idx_game_user1ID (game_user1ID);
还需要进行排序显示:
如何排序,排序和索引不能在同一个过程里进行,所以先创建索引如上,再根据它排序(实际上就无关啦),排序命令,只考虑通常的升序,还要考虑指定出哪些字段来显示,这里我是主观挑选的:
SELECT game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,
game_user2cards ORDER BY game_user1ID;
解决过程中出现的问题和解决方法
因为整个数据库都是现在刚刚创建的,所以应该不用担心写错,写错了就再来一次;
问题一:可不可以在navicat里面直接做这些操作?
好像没有提到可不可以,如果可以的话应该比MySQL客户端的命令行好操作一点~
应该不行,要求是sql语句,那应该在命令提示符里面以管理员身份运行,然后转到bin目录下(直接进入不会,不能成功)开启MySQL服务,命令:
net start mysql
进行登录验证,命令:
mysql -u root -p
正确输入password之后,目录就会变成”mysql“的形式!
说明已经可以正常的运行MySQL客户端了,那么就可以进行之后的数据库操作了!
但是操作我们可以借助navicat看到这些表表!
问题二:怎么在创建表的同时写字段,或者可以把主键也联系进来吗?
教程里面有个bug,它是讲述查看创建表的sql语句命令的,但是正好可以看到它创建表的时候的具体命令,可以借鉴一下!我们把这个展示的命令和上面显示出来的表表详细信息对应起来看,加一点注释来理解一下:
mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| class_id | bigint(20) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| gender | varchar(1) | NO | | NULL | |
| score | int(11) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` ( |
| | `id` bigint(20) NOT NULL AUTO_INCREMENT, |
| | `class_id` bigint(20) NOT NULL, |
| | `name` varchar(100) NOT NULL, |
| | `gender` varchar(1) NOT NULL, |
| | `score` int(11) NOT NULL, |
| | PRIMARY KEY (`id`) |
| | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1)auto_increment自增,表示第一个Record id是自增,这里只是做个标记,表示id字段被设置为自增;后面看到AUTO_INCREMENT=1,说明指定的初始自增值是1,我们仿照这个来写!
2)观察这些创建字段的命令发现:
表名和字段名都加了单引号,不知道为什么,只创建表不加字段是不需要加的?这里必须加!
表名后面的所有字段都被括在括号里面,每个字段后面都要写NOT NULL ,括号外面还有东西,解释如下:
bigint和int差不多嘛,教程中写道:
如果使用INT自增类型,那么当一张表的记录数超过2147483647(约21亿)时,会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。
所以我们通常使用bigint就可以满足需求,仿照!
varchar(变长字符串)和char差不多,那么就懂了,我们也用varchar!
varchar(N)代表的是所占的字节数,20就是20个字节,性别就是一个字节,name用了100个字节,这里我们还是仿照,参考它的字节设置!
根据这两个东西,那么首先就要弄清楚每个字段的类型:ID是自增主键(bigint(20)),player_username和name差不多(varchar(100)),player_password这是密码吗?那么更大,放宽点设置为(varchar(225)),play_createtime这个查询得知是创建字段时自动生成时间?不太懂,会获取当前时间,更正为timestamp,这个不需要括号
3)PRIMARY KEY (id
) 这个的意思是主键id,把第一个字段id设置成主键了,然后它本身是自增的,所以就都搞定了,学习!
4)ENGINE=InnoDB
innodb是一个引擎,创建表的时候通常会这么写,仿照!
博客解释
5)括号外面所有关键字的解释来啦~
那么我们照着写就好!
写完这个创建命令之后,我们也可以用展示命令瞧瞧这个二维表!
问题三:如何设置主键?
这个好像没有学过,教程里面只讲述了什么是主键,然后哪种类型的字段适合被设置为主键,但是具体怎么设置?怎么确定自增的id为主键呢?
看上一个问题解决!
问题四:分析一下record表的字段
game_ID没有说具体要求,暂且把它看成和id一样类型,但是是普通的字段(bigint(20)),毕竟没有要求是自增主键,所以我就手动初始化为1,2,3…吧,应该是游戏的局数?
game_begintime这个是和user字段中的player_createtime有关系的吧,设置成一样的timestamp,自动填充
game_exittime同上timestamp,自动填充
game_user1ID这个和game_ID一定有关系!所以同上,可不可以把user表的搬过来,我感觉他们有关系,但是是三个用户,还是算了,合起来就六个了!不对不对,不能搬user,我这个是数字id,写成7,8,9
game_user2ID同上(bigint(20)),写成3,4,5
game_winner_ID同上(bigint(20))写成7,4,9
game_user1cards这个感觉不太对,不应该和player_cards对应吗,但是并不存在这个字段,所以(varchar(225))写成spade5,heart9,club3;
game_user2cards同上(varchar(225))【这个可以重复吗?要想不重复的话就要既写花色又写数字,那就写上吧,不过我还是要写四种花色的】写成diamond3,club4,club5;
问题五:不加主键的表表括号前面最后一个字段写完了要不要加逗号?
不知道,加了怪怪的,我就删了
问题六:player_createtime到底是什么,深究一下?
这个应该是时间类型,那么就不要用bigint和varchar来表示了(这样也可以,便于计算),但是可以试一下DATETIME,TIMESTAMP和时区有关,就不需要了;
二改:
发现datetime好像不能自动填充时间戳,所以我们换成了timestamp;它只需要插入这个字段之后就不用管了!
问题七:player_password没有指定初始值,但是它也没有默认值,怎么插入数据呢?
该字段我指定的是varchar(225),那就弄成空字符串可不可以?试试
查询结果:
如果sql_mode没有设置STRICI_TRANS_TABLES,在插入记录时,如果有非空字段没有值,而且没有设置默认值,那么引擎会自动填充(int类型:0;char类型:‘’;timestamp类型 当前时间戳)
我没有设置这个,之前在配置文件中写入这个是为了想要不转bin目录也可以直接打开MySQL,但是后来发现它会导致mysql服务不能开启,所以就删了;后面的条件我都满足,所以书写插入语句的时候只需要插入一个字段的值就行了!
这里有个注意事项:
标准推荐设置,在mysql配置文件中添加;
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES;
我也不知道…
问题八:只针对该数据库的命令
USE test;
使用之后所有操作都只针对这个数据库;
新建好数据库就执行他!
mysql> USE test;
Database changed
问题九:bigint,varchar最大长度考虑
之前写的时候没有深入了解,导致搞出了岔子;
查询得知,bigint最大长度20;varchar最大长度225;int基本可以满足普通的使用(尤其是我这种-_-)
问题十:注释的考虑?
加注释的方法,因为我是要给字段加,所以在参考笔记之后,发现有两种方法:
我觉得应该是写在字段的前面吧,肯定不是翻译字段的吧
#和-- 两种办法!
我选择第二种—>
问题十一:注意到每条语句后面都有分号!
我的所有命令:
命令提示符-以管理员身份运行;
转到bin目录下:
cd D:\Download\code about apps\mysql+navicat\mysql\mysql-822\bin
D:
开启服务:
net start mysql
登录:
mysql -u root -p
创建数据库test:
mysql> CREATE DATABASE test;
展示所有数据库:
新建数据库:
仅使用该数据库:
创建该数据库下的表user并初始化所有字段加中文注释(注意单引号和逗号)(中文注释真的可以加吗):
CREATE TABLE ‘ user ’ (
– id
‘ id ’ bigint(20) NOT NULL AUTO_INCREMENT,– 玩家用户姓名
‘ player_username ’ varchar(100) NOT NULL,– 玩家密码
‘ player_password ’ varchar(225) NOT NULL,– 玩家创建时间
’ play_createtime ‘ timestamp NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
第一次符号报错(反单引号`和单引号’):
报错信息:
mysql> CREATE TABLE 'user'(
-> -- id
-> 'id' bigint(20) NOT NULL AUTO_INCREMENT,
-> -- 玩家用户姓名
-> 'player_username' varchar(100) NOT NULL,
-> -- 玩家密码
-> 'player_password' varchar(225) NOT NULL,
-> -- 玩家创建时间
-> 'play_createtime' timestamp NOT NULL,
-> PRIMARY KEY(id)
-> )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
->
-> ;
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 ''user'(
'id' bigint(20) NOT NULL AUTO_INCREMENT,
'player_username' varchar(100' at line 1
错误原因及改正方法:
反单引号`用于表名两侧及字段名两侧;而单引号’用于values值两侧;
创建用户成功:
创建该数据库下的表record并初始化所有字段加中文注释(这要注意当前目录是test还是user,不能搞乱):
CREATE TABLE ‘record ’(
– 游戏id
‘game_ID’ bigint(20)NOT NULL,
– 游戏开始时间
‘game_begintime’ timestamp NOT NULL,
– 游戏结束时间
‘game_exittime’ timestamp NOT NULL,
– 游戏用户一id
‘game_user1ID’ bigint(20)NOT NULL,
– 游戏用户二id
‘game_user2ID’ bigint(20)NOT NULL,
– 游戏获胜者id
‘game_winner_ID’ bigint(20)NOT NULL,
– 游戏用户一手牌
‘game_user1cards’ varchar(225)NOT NULL,
– 游戏用户二手牌
‘game_user2cards’ varchar(225)NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8
创建record表踩坑:注释-- 忘记空格
创建record表成功:
查看创建好的表:
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| record |
| user |
+----------------+
2 rows in set (0.02 sec)
在user表中添加用户
INSERT INTO user (player_username) VALUES
(‘wang’),
(‘li’);
一错:用户密码无默认值–>
mysql> INSERT INTO user(player_username) VALUES
-> ('wang'),
-> ('li');
ERROR 1364 (HY000): Field 'player_password' doesn't have a default value
二错:玩家游戏时间无默认值–>密码初始化为空串
mysql> INSERT INTO user(player_username,player_password)VALUES
-> ('wang',''),
-> ('li','');
ERROR 1364 (HY000): Field 'player_createtime' doesn't have a default value
发现bug,原来time stamp是需要初始值的!!!
改正方法,在timestamp NOT NULL 后面加上:DEFAULT CURRENT_TIMESTAMP 即可,那么开始改!
首先删除record表~
mysql> DROP TABLE record;
Query OK, 0 rows affected (0.02 sec)
重新创建record表~
mysql> CREATE TABLE `record`(
-> -- 游戏id
-> `game_ID` bigint(20) NOT NULL,
-> -- 游戏开始时间
-> `game_begintime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> -- 游戏结束时间
-> `game_exittime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> -- 游戏用户一id
-> `game_user1ID` bigint(20) NOT NULL,
-> -- 游戏用户二id
-> `game_user2ID` bigint(20) NOT NULL,
-> -- 游戏获胜者id
-> `game_winner_ID` bigint(20) NOT NULL,
-> -- 游戏用户一手牌
-> `game_user1cards` varchar(225) NOT NULL,
-> -- 游戏用户二手牌
-> `game_user2cards` varchar(225) NOT NULL
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 5 warnings (0.04 sec)
再次尝试添加用户信息~
mysql> INSERT INTO user(player_username,player_password)VALUES
-> ('wang',''),
-> ('li','');
ERROR 1364 (HY000): Field 'player_createtime' doesn't have a default value
嗯?怎么报错还是这样?
噢,是我太傻,这是user表,我做了什么????、
再次重来!
删除user表–>
mysql> DROP TABLES user;
Query OK, 0 rows affected (0.03 sec)
重新创建user表–>
mysql> CREATE TABLE `user`(
-> -- id
-> `id` bigint(20) NOT NULL AUTO_INCREMENT,
-> -- 玩家用户姓名
-> `player_username` varchar(100) NOT NULL,
-> -- 玩家密码
-> `player_password` varchar(225) NOT NULL,
-> -- 玩家创建时间
-> `play_createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY(id)
-> )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
再次添加用户数据,成功!
mysql> INSERT INTO user(player_username,player_password)VALUES
-> ('wang',''),
-> ('li',''); //这就是最终的正确添加代码啦!
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
查看当前的所有用户(只有刚添加的两名用户)
mysql> DESC user;
在record表中添加任意信息的三条用户
INSERT INTO record(game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,game_user2cards)VALUES
(1,7,3,7,‘spade5’,‘diamond3’),
(2,8,4,4,‘heart9’,‘club4’),
(3,9,5,9,‘club3’,‘club5’);
成功添加啦啦啦!
更新用户2的用户名为LALALA
UPDATE user SET player_username=‘LALALA’ WHERE id=2;
更新数据成功–>
更新之后的确认查看,用户2的id=2:
SELECT * FROM user WHERE id=2;
查询record表中用户ID=2的所有信息
SELECT * FROM record WHERE game_ID=2;
查询record表以game_user1ID 作为索引进行排序显示
ALTER TABLE record
ADD INDEX idx_game_user1ID (game_user1ID);
SELECT game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,
game_user2cards ORDER BY game_user1ID;
这里出现的错误:
order拼写错误报错:
mysql> SELECT game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,game_user2cards OEDER BY game_user1ID;
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 'BY game_user1ID' at line 1
忘记写从那个表里面查报错:
mysql> SELECT game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,game_user2cards ORDER BY game_user1ID;
ERROR 1054 (42S22): Unknown column 'game_ID' in 'field list'
最终正确代码如下:
mysql> SELECT game_ID,game_user1ID,game_user2ID,game_winner_ID,game_user1cards,game_user2cards FROM record ORDER BY game_user1ID;
+---------+--------------+--------------+----------------+-----------------+-----------------+
| game_ID | game_user1ID | game_user2ID | game_winner_ID | game_user1cards | game_user2cards |
+---------+--------------+--------------+----------------+-----------------+-----------------+
| 1 | 7 | 3 | 7 | spade5 | diamond3 |
| 2 | 8 | 4 | 4 | heart9 | club4 |
| 3 | 9 | 5 | 9 | club3 | club5 |
+---------+--------------+--------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
完毕!
exit
这个小小的项目到这里就结束啦!虽然很简单,但是对于我这个小白来说还是很有难度哒~继续努力进步呀!
如果对你有帮助的话不要忘记一键三连噢~
谢谢鸭~
初次编写于2021/1/20日.