Home sql basic
Post
Cancel

sql basic

fozu on Feb 20, 20232023-02-20T00:00:00+08:00
8 min read

DDL(Data Definition Language)

CREATE/ALTER/DROP 数据库、表

创建数据库

1
2
3

CREATE DATABASE example_01;
CREATE DATABASE IF NOT EXISTS example_01;
CREATE DATABASE IF NOT EXISTS example_01 CHARACTER SET charset_name;

创建表

1
2
3
4
5

CREATE TABLE t_user (
	name VARCHAR(20),
	age INT,
	height DOUBLE
);

修改（Alter）

1
2
3
4
5
6
7
8
9
10
11
12

# 修改数据库的charset
ALTER DATABASE db_name CHARACTER SET charset_name;
# 修改表的charset
ALTER TABLE t_name CHARACTER SET charset_name;
# 修改表名
ALTER TABLE t_name RENAME TO new_table_name;
# 修改某列的数据类型
ALTER TABLE t_name MODIFY column_name new_data_type;
# 修改某列的字段名称及数据类型
ALTER TABLE t_name CHANGE column_name new_column_name new_data_type;
# 删除某个字段
LATER TABLE t_name DROP column_name;

DROP

1
2
3
4
5
6

# 删除数据库
DROP DATABASE db_name;
# 删除数据库
DROP DATABASE IF EXISTS db_name;
# 删除表
DROP TABLE t_name;

插入记录

1
2
3
4

INSERT INTO 
	t_user(name, age, height)
VALUES
	('张三'， 23， 169);

查询

1
2
3
4
5

SHOW DATABASES;
# 查询数据库的创建语句
SHOW CREATE DATABASES;
# 查询正在使用的数据库
SELECT DATABASE()

DELETE

1

DELETE FROM t_name;

TRUNCATE

1
2
3
4

# 先删除后重新创建表（效率高）
# 绕过了删除数据的DML方法，因此，不能被回滚，不会导致ON DELETE触发器的触发
# 并且不能对InnoDB具有父子外键关系的表执行
TRUNCATE [TABLE] t_name;

SELECT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

SELECT DINSTINT * FROM t_name;
SELECT COUNT(*) FROM t_name;
SELECT COUNT(DISTINCT*) FROM t_name;
SELECT SUM(age) FROM t_user;
SELECT MIN(age) FROM t_user;
SELECT MAX(age) FROM t_user;

# 非连接多表查询
# 笛卡尔乘积
SELECT * FROM customer cr, company cy WHERE cr.company_id = cy.id;

# 内连接1
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr JOIN company cy
ON
	cr.company_id = cy.id
	
# 内连接2
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr INNER JOIN company cy
ON
	cr.company_id = cy.id
	
# 内连接3
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr CROSS JOIN company cy
ON
	cr.company_id = cy.id	
	
# 左外连接1
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr LEFT OUTER JOIN company cy
ON
	cr.company_id = cy.id	

# 左外连接2
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr LEFT OUTER JOIN company cy
ON
	cr.company_id = cy.id	
WHERE
	cr.company_id is NULL;
	
# 右外连接1
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr RIGHT OUTER JOIN company cy
ON
	cr.company_id = cy.id
	
# 右外连接2
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr RIGHT OUTER JOIN company cy
ON
	cr.company_id = cy.id	
WHERE
	cy.id is NULL;
	
# 全外连接1
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr FULL OUTER JOIN company cy
ON
	cr.company_id = cy.id;
	
# 全外连接2
SELECT 
	cr.NAME cr_name,
	cy.NAME cy_name
FROM
	customer cr FULL OUTER JOIN company cy
ON
	cr.company_id = cy.id;	
WHERE 
	cr.company_id is NULL OR cy.id is NULL:
	
# UNION
合并结果集

# OERER BY
# 优先按age排序，如果age相等再按id排序
SELECT * FROM student ORDER BY age DESC, id DESC where id > 20;

# 分页 （start, len)
SELECT * FROM customer LIMIT 0, 10;
SELECT * FROM customer LIMIT 10;
SELECT * FROM customer LIMIT 10 OFFSET 0;

WHERE

1
2
3
4
5
6
7
8
9
10
11
12
13

WHERE age = 20;
WHERE age != 20;
WHERE age <> 20;
WHERE age IS [NOT] NULL;
WHERE age > 18 AND age < 20;
WHERE age > 18 && age < 25;
WHERE age BETWEEN 20 AND 30;
WHERE age IN (18, 20, 22);
WHERE age = 18 OR age = 20 OR age = 22;
WHERE NOT (age < 18);
# '_'代表任意一个字符
# '%'代表任意个数的任意字符
WHERE name LIKE '王__';

赋值表

1
2
3
4

# 只复制表结构不赋值记录
CREATE TABLE new_table LIKE old_table;
# 复制表结构且复制记录
CREATE TABLE new_table AS SELECT * FROM old_table;

列属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

# 非空
NOT NULL
# 添加字段说明
COMMENT '名字'
# 默认值
DEFAULT '0'
# 唯一约束，非空值唯一，允许多个NULL
xxx UNIQUE [KEY]
UNIQUE [KEY] (column_name);
# 主键；保证表中的每条记录都是唯一的 
# 相当于NOT NULL 且 UNIQUE
# 建议每张表都有主键
# 主键最好跟业务无关 常设置INT AUTO_INCREMENT
# 一张表只能有一个主键
# 主键可以为复合主键
xxx PRIMARY KEY
PRIMARY KEY (column_name);
# 使用于INT\FLOAT\DOUBLE
# 不能有默认值
# 插入记录时不指定该列的值或指定为NULL，会在此前的基础上自动增长1
# 一张表中只能有一个AUTO_INCREMENT字段
# 该列必须被索引（UNIQUE\PRIMARY KEY\FOREIGN KEY等）
AUTO_INCREMENT
# 外键可以用来消除数据冗余
# 一般用外键来引用其他表的主键
FOREIGN KEY （column_name） REFERENCES 表名（列名）
# 定义外键时可以设置级联
# 当删除被引用的记录时，引用了此记录的其他记录都会被自动删除
FOREIGN KEY （column_name） REFERENCES 表名（列名） ON DELETE CASCADE
# 当修改被引用的记录时，引用了此记录的其他所有记录都会被自动更新
FOREIGN KEY （column_name） REFERENCES 表名（列名） ON UPDATE CASCADE

DML(Data Manipulation Language)

INSERT/DELETE/UPDATE 记录

DQL(Data Query Language)

SELECT 记录

DCL(Data Control Language)

GRANT/REVOKE 控制访问权限

数据类型

数字

1字节

TINYINT\BOOL\BOOLEAN

2字节

SMALLINT

3字节

MEDIUMINT

4字节

INT\INTEGER

FLOAT

8字节

BIGINT

DOUBLE\DOUBLE PRECISION

DECIMAL（存储的小数更加精确）

DECIMAL\DEC\NUMERIC\FIXED

1
2
3
4
5
6

TYPE[(m)][UNSIGNED][ZEROFILL]
# ZEROFILL, 在显示数值时，若数字不足M位，就在前面用0填充
age INT(3) UNSIGNED ZEROFILL

# decimal
money DECIMAL(10, 2)

字符串类型

CHAR

  • 长度可以指定为0~255

  • 查询数据时，会省略后面的空白字符

VARCHAR

  • 长度可以指定为0~65535
  • 查询数据时，不会省略后面的空白字符

BLOB

用于存储二进制数据（照片、文本、大文本等）

TEXT

用于存储大文本

日期

YEAR

YYYY

DATE

YYYY-MM-DD

TIME

HH:MM:SS[.fraction]

  • 小数部分用0-3个字节存储

DATETIME

  • YYYY-MM-DD HH:MM:SS[.fraction]
  • 支持的范围 1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999
  • 小数部分用0-3个字节存储

TIMESTAMP

  • YYYY-MM-DD HH:MM:SS[.fraction]
  • 小数部分用0-3个字节存储
  • 支持的范围： 1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999
Database
sql
This post is licensed under CC BY 4.0 by the author.
Recent Update
Contents

java RMI

tomcat