海阔天空

手机扫码查看

2020Javaweb教程之SQL语言

2.1 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。

各数据库厂商都支持ISO的SQL标准，(普通话)

各数据库厂商在标准的基础上做了自己的扩展，(方言)

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建数据库、表等等，查询内容，更新内容，并删除条目等操作。

Create, Read, Update, and Delete 通常称为CRUD操作。

2.2 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）。
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

MySQL的使用注意：

sql语句以 ; 结尾。

MySQL注释：（1）#开头 （2）–空格 开头 （3）/* 多行注释 */

2.3 DDL操作数据库

show databases;   #显示当前mysql中的数据库

系统默认数据库：

information_schema：

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。 别动！

mysql：这个是mysql的核心数据库,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息，不可以删除。

performance_schema：性能优化的数据库

test:这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。

创建数据库：

CREATE DATABASE语句用于创建新的数据库：

语法：

CREATE DATABASE [IF NOT EXISTS]  自己起的表名

SQL> CREATE DATABASE mydb1;
SQL> CREATE DATABASE IF NOT EXISTS mydb2 character SET GBK;
SQL> CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

2查看

查看当前数据库服务器中的所有数据库SQL> SHOW DATABASES;

查看前面创建的mydb2数据库的定义信息SQL> ShOW CREATE DATABASE mydb2;

3修改

查看服务器中的数据库，并把mydb2的字符集修改为utf8;

SQL> ALTER DATABASE mydb2 character SET utf8;

4删除

语法：DROP DATABASE [IF EXISTS] db_name

SQL> DROP DATABASE IF EXISTS mydb3;

5其他语句

查看当前使用的数据库

SQL> Select database(); #没有选择数据 null

切换数据库

SQL> USE mydb2;

退出MySQL

quit;或exit;

2.4 DDL操作表

CREATE TABLE语句用于创建新表。

语法：

CREATE TABLE [IF NOT EXISTS] 表名(
​
字段1 字段类型(长度) 约束,
​
字段2 字段类型(长度) 约束,
…
字段n 字段类型(长度) 约束
​
) [charset=utf8];

示例：

SQL> CREATE TABLE IF NOT EXISTS student
(
   id INT NOT NULL,
   name VARCHAR(50),
   age INT,
   address VARCHAR(100)
) CHARSET=utf8;
或
SQL> CREATE TABLE  IF NOT EXISTS `student`
(
   `id` INT NOT NULL,
   `name` VARCHAR(50),
   `age` INT,
   `address` VARCHAR(100)
) CHARSET=utf8;

注意：数据库名、表名、字段名可以使用反勾号` 括住，也可以不括。如果SQL关键字一般要括住。

常用数据类型：

int：整型（4个字节）

double：浮点型（8个字节），近似值

例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99

decimal：精确数值数据，最大位数可以是65

例如decimal(5,2)能够存储具有五位数和两位小数的任何值，因此可以存储范围为

-999.99至999.99。

char：固定长度字符串类型； char(10) ‘aaa ‘ 固定10个字符，不足补空格， 长度0-255

缺点：浪费空间，优点:查询速度快

varchar：可变长度字符串类型； varchar(10) ‘aaa’ 最多存储10个字符

缺点：查询速度慢，优点：节省空间

text：大文本字符串类型；有字符编码，存储比较大的文本数据。

blob：Binary Large Object二进制大对象数据；可以存储图片、音频、视频

date：日期类型，格式为：yyyy-MM-dd；

time：时间类型，格式为：hh:mm:ss

timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

常用约束：

主键约束：primary key (保证数据唯一性),不能重复，不能为null

唯一约束：unique [key],不能重复，可以为null

非空约束：not null

默认约束：default

外键约束：foreign key

自动增长：auto_increment

DROP TABLE语句用于删除现有表。

语法： DROP TABLE [IF EXISTS] table_name;

SQL> DROP TABLE table_name;

当前数据库中的所有表

SHOW TABLES;

查看表的字段信息

DESC student;

在上面学生表的基础上增加一个image列。

ALTER TABLE student ADD image blob;

修改address列，使其长度为60。

ALTER TABLE student MODIFY address varchar(60);

删除image列,一次只能删一列。

ALTER TABLE student DROP image;

表名改为user。

RENAME TABLE student TO user;

查看表的创建细节

SHOW CREATE TABLE user;

修改表的字符集为gbk

ALTER TABLE user CHARACTER SET gbk;

列名name修改为username

ALTER TABLE user CHANGE name username varchar(100);

2.5 DML操作（重要）

DML是对表中的数据进行增、删、改的操作。不要与DDL混淆了。

主要包括：INSERT 、 DELETE、UPDATE

小知识：

在mysql中，字符串类型和日期类型都要用单引号括起来。

空值：null。

（1）插入操作：INSERT:

语法： INSERT INTO 表名（列名1，列名2 …）VALUES(列值1，列值2…);

注意：列名与列值的类型、个数、顺序要一一对应。

可以把列名当做java中的形参，把列值当做实参。

参数不要超出列定义的长度。

如果插入空值，请使用null

插入的日期和字符一样，都使用单引号括起来。

SQL> CREATE TABLE student
(
id INT NOT NULL,
name VARCHAR(50),
age INT,
address VARCHAR(100)
);

一次添加一条数据

INSERT INTO student(id,name,age,address) values (1,’zhangsan’,20,’北京海淀’);
INSERT INTO student(id,name,age,address) values (2,’lisi’,22,’上海浦东’);
INSERT INTO student(id,name,age,address) values (3,’wangwu’,23,’北京昌平’);

一次添加多条数据

INSERT INTO student(id,name,age,address) values (4,’曹操’,27,’北京海淀’),
(5,’周瑜’,28,’北京朝阳’),
(6,’赵云’,30,’北京大兴’);

思考：命令行中为什么不能添加中文数据？

命令行窗口默认字符集是GBK。MySQL默认是UTF8

小知识：

查看数据库编码的具体信息

Show variables like ‘character%’;

临时更改客户端和服务器结果集的编码

Set character_set_client=gbk;
​
Set character_set_results=gbk;

或者

SET NAMES ‘gbk’; // client connection results

（2）修改操作：UPDATE:

语法：UPDATE 表名 SET 列名1=列值1,列名2=列值2 … WHERE 列名=值

练习：

将所有学生的年龄修改为25。

UPDATE student SET age=25;

将id为’1’的地址修改为“河北保定”。

UPDATE student SET address=’河北保定’ WHERE id=1;

将姓名为’曹操’’的学生年龄改为50，地址改为”河南郑州”’。

UPDATE student SET age=50, address=’河南郑州’ WHERE name=’曹操’;

将所有学生的年龄加5岁。

UPDATE student SET age=age+5;

（3）删除操作：DELETE

语法 ：

DELETE FROM 表名 【WHERE 列名=值】

练习 ：

删除表中名称为’zhangsan’的记录。

DELETE FROM student WHERE name=‘zhangsan’;

删除表中所有记录。

DELETE FROM emp;

使用truncate删除表中记录。(先把表删除，然后再创建空表)

TRUNCATE TABLE emp;

DELETE 删除表中的数据，表结构还在;删除后的数据使用日志可以找回。

TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。

TRUNCATE 删除的数据不能找回。执行速度比DELETE快。

2.6客户端工具

使用命令虽然可以完成基本操作，为了更方便用户操作和使用MySQL数据，市面上出现很多第三方的客户端管理工具比如:SQLyog和Navicat