Sql语句操作数据库

前言：

上回讲到已经装好了数据库。当然也可以直接在黑框中使用sql语句操作数据库，但是在这里我就不演示了，代码很容易敲错也比较难记，编写过得代码也无法保存。在工作中一般都是用图形化界面，目前我使用过得sql图形化界面工具有两个，一个是Navicat，一个是Idea直接连接Mysql。说得idea连接让我想到一开始工作得时候看着组长项目经理玩不明白idea得MySql，我有很努力得在憋笑。不管使用哪种工具重在对Sql语句得熟练，如果是学习得话，我觉得先在idea上连接数据库就ok了。

1.Idea图形化工具使用

1、打开IDEA，点击右上角得加号，然后点击数据源，在找到MySql，因为我连接过了，所以直接出现在了最顶端。

2、输入相关信息

3、测试数据库连接

4.最后保存配置点击确定即可。

默认情况下，连接上了MySQL数据库之后， 数据库并没有全部展示出来。 需要选择要展示哪些数据库。具体操作如下：

2. 操作数据库

创建数据库：

有了图形化界面工具后，就可以方便的使用图形化工具：创建数据库，创建表、修改表等DDL操作。

其实工具底层也是通过DDL语句操作的数据库，只不过这些SQL语句是图形化界面工具帮我们自动完成的。

查看所有数据库：

以上操作其实就是SQL分类中得DDL语句，

DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)。

DDL中数据库的常见操作：查询、创建、使用、删除。可以直接在CMD黑框中输入得指令，相信大家更喜欢图形化界面一些。也不用死记硬背这是DDL语句，在工作中创建数据库或者创建表删除表，一般都是直接通过图形化界面操作。待会儿还有个DML语句，说白了DDL就是操作数据库，DML就是操作数据库的中数据。

3.数据表操作

学习完了DDL语句当中关于数据库的操作之后，接下来我们继续学习DDL语句当中关于表结构的操作。

关于表结构的操作是包含四个部分：创建表、查询表、修改表、删除表。

3.1创建表

create table 表名(

字段1 字段1类型 [约束] [comment 字段1注释 ],

字段2 字段2类型 [约束] [comment 字段2注释 ],

......

字段n 字段n类型 [约束] [comment 字段n注释 ]

) [ comment 表注释 ] ;

注意： [ ] 中的内容为可选参数； 最后一个字段后面没有逗号

案例：创建NBA球员名单表

对应的结构如下：

建表语句：

create table tb_NBA (

id int comment 'ID,唯一标识', # id是一行数据的唯一标识（不能重复）

username varchar(20) comment '球衣号码',

name varchar(10) comment '姓名',

age int comment '年龄'

) comment 'NBA球星表';

数据表创建完成，接下来我们还需要测试一下是否可以往这张表结构当中来存储数据。

双击打开tb_nba表结构，大家会发现里面没有数据：

点击加号添加数据：

此时我们在插入一条数据：

我们之前提到过：id字段是一行数据的唯一标识，不能有重复值。但是现在数据表中有两个相同的id值，这是为什么呢？

其实我们现在创建表结构的时候， id这个字段我们只加了一个备注信息说明它是一个唯一标识，但是在数据库层面呢，并没有去限制字段存储的数据。所以id这个字段没有起到唯一标识的作用。

想要限制字段所存储的数据，就需要用到数据库中的约束。

3.1.1 约束

概念：所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据。

作用：就是来保证数据库当中数据的正确性、有效性和完整性。（后面的学习会验证这些）

在MySQL数据库当中，提供了以下5种约束：

约束描述关键字非空约束限制该字段值不能为nullnot null唯一约束保证字段的所有数据都是唯一、不重复的unique主键约束主键是一行数据的唯一标识，要求非空且唯一primary key默认约束保存数据时，如果未指定该字段值，则采用默认值default外键约束让两张表的数据建立连接，保证数据的一致性和完整性foreign key

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例：创建tb_BasketBallPlayer表

对应的结构如下：

在上述的表结构中:

id 是一行数据的唯一标识

username 用户名字段是非空且唯一的

name 姓名字段是不允许存储空值的

gender 性别字段是有默认值，默认为男

建表语句：

create table tb_BasketBallPlayer (

id int primary key comment 'ID,唯一标识',

username varchar(20) not null unique comment '球员号码',

name varchar(10) not null comment '姓名',

age int comment '年龄',

gender char(1) default '男' comment '性别'

) comment '篮球运动员表';

数据表创建完成，接下来测试一下表中字段上的约束是否生效

大家有没有发现一个问题：id字段下存储的值，如果由我们自己来维护会比较麻烦(必须保证值的唯一性)。MySQL数据库为了解决这个问题，给我们提供了一个关键字：auto_increment（自动增长）

主键自增：auto_increment

每次插入新的行记录时，数据库自动生成id字段(主键)下的值

具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)

create table tb_BasketBallPlayer (

id int primary key auto_increment comment 'ID,唯一标识',

username varchar(20) not null unique comment '球员号码',

name varchar(10) not null comment '姓名',

age int comment '年龄',

gender char(1) default '男' comment '性别'

) comment '篮球运动员表';

​

测试：主键自增

在这里有了第一条数据以后，在增加第二条点击上传符号，ID就会自增，我刚才增加了过一条又删除了所以这里得id是3

在上面建表语句中，我们在指定字段的数据类型时，用到了int 、varchar、char，那么在MySQL中除了以上的数据类型，那么数据类型存在的意义最直接的比如说年龄岁数，你不可能出现－1岁吧，这样就可以限制了。那么还有哪些常见的数据类型呢？ 接下来,我们就来详细介绍一下MySQL的数据类型。 在下列数值类型中，提前说一下如果未来入行Java，是计算金钱的场景直接使用DECIMAL，不要用DOUBLE。

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

3.1.2 数值类型

类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述TINYINT1byte(-128，127)(0，255)小整数值SMALLINT2bytes(-32768，32767)(0，65535)大整数值MEDIUMINT3bytes(-8388608，8388607)(0，16777215)大整数值INT/INTEGER4bytes(-2147483648，2147483647)(0，4294967295)大整数值BIGINT8bytes(-2^63，2^63-1)(0，2^64-1)极大整数值FLOAT4bytes(-3.402823466 E+38，3.402823466351 E+38)0 和 (1.175494351 E-38，3.402823466 E+38)单精度浮点数值DOUBLE8bytes(-1.7976931348623157 E+308，1.7976931348623157 E+308)0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)双精度浮点数值DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)示例:

年龄字段 ---不会出现负数, 而且人的年龄不会太大

age tinyint unsigned

分数 ---总分100分, 最多出现一位小数

score double(4,1)

字符串类型

类型大小描述CHAR0-255 bytes定长字符串(需要指定长度)VARCHAR0-65535 bytes变长字符串(需要指定长度)TINYBLOB0-255 bytes不超过255个字符的二进制数据TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes极大文本数据char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

示例：

用户名 username ---长度不定, 最长不会超过50

username varchar(50)

手机号 phone ---固定长度为11

phone char(11)

日期时间类型

类型大小范围格式描述DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值TIME3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间YEAR11901 至 2155YYYY年份值DATETIME81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳示例:

生日字段 birthday ---生日只需要年月日

birthday date

创建时间 createtime --- 需要精确到时分秒

createtime datetime

关于表结构的查询操作，工作中一般都是直接基于图形化界面操作。

3.2查询当前数据库所有表

show tables;

或者直接双击表，也能展示出所有表

查看指定表结构

desc 表名 ;#可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息

查询指定表的建表语句

show create table 表名 ;

3.2修改

关于表结构的修改操作，工作中一般都是直接基于图形化界面操作。

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

案例： 为tb_emp表添加字段qq，字段类型为 varchar(11)

alter table tb_basketballplayer add number varchar(55) null comment '电话号码';

图形化操作：添加字段

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

案例：修改qq字段的字段类型，将其长度由11修改为13

alter table tb_basketballplayer modify number varchar(55) comment ' 电话号码';

案例：修改qq字段名为 qq_num，字段类型varchar(13)

alter table tb_basketballplayer change number qq_num varchar(55) comment 'qq号码';

图形化操作：修改数据类型和字段名

删除字段

alter table 表名 drop 字段名;

案例：删除tb_emp表中的qq_num字段

alter table tb_basketballplayer drop qq_num;

图形化操作：删除字段

修改表名

rename table 表名 to 新表名;

案例：将当前的tb_emp表的表名修改为emp

rename table tb_basketballplayer to basketballplayer;

图形化操作：修改表名

3.4 删除

关于表结构的删除操作，工作中一般都是直接基于图形化界面操作。

删除表语法：

drop table [ if exists ] 表名;

if exists ：只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

案例：如果tb_emp表存在，则删除tb_emp表

drop table if exists basketballplayer ; -- 在删除表时，表中的全部数据也会被删除。

图形化操作：删除表

4. 数据库操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

说白了DML语句就是对数据库中的数据表中的数据进行增删改查，工作中常用的也是这个

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

4.1 增加(insert)

insert语法：

向指定字段添加数据

insert into 表名 (字段名1, 字段名2) values (值1, 值2);

全部字段添加数据

insert into 表名 values (值1, 值2, ...);

批量添加数据（指定字段）

insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);

批量添加数据（全部字段）

insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

案例1：向basketballplayer表的username、name、gender字段插入数据

-- 因为设计表时create_time, update_time两个字段不能为NULL，所以也做为要插入的字段

insert into tb_emp(username, name, gender, create_time, update_time)

values ('52', 'HCJ', 1, now(), now());

案例2：向basketballplayer表的所有字段插入数据

insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)

values (null, 'zhirou', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());

Insert操作的注意事项：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

字符串和日期型数据应该包含在引号中。

插入的数据大小，应该在字段的规定范围内。

4.2 修改(update)

update语法：

update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

案例1：将tb_emp表中id为1的员工，姓名name字段更新为'张三'

update basketballplayer set name='利拉德',update_time=now() where id=1;

注意事项:

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

4.3 删除(delete)

delete语法：

delete from 表名 [where 条件] ;

案例1：删除basketballplayer表中id为1的球员

delete from basketballplayer where id = 1;

案例2：删除basketballplayer表中所有球员

delete from basketballplayer;

注意事项:

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

• 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。