关于数据库的操作语句
-----作者：ymkey
引用请标明出处
SQL注意点 :
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
create
drop
alter
以上语句统称DDL语句 -> 数据定义语
-- 查看服务器中有哪些数据库
show databases;
-- 创建新的数据库
create database company;
-- 查看当前数据库中的表
show tables;
-- 导入.sql文件中的数据
source d:/company.sql;
-- 练习 : 创建world数据库, 并切换, 再导入world.sql文件中的数据到world库.
create database world;
use world;
source d:/world.sql;
-- 查看当前数据库中的表
show tables;
-- 丢弃数据库
drop database company;
drop database world;
-- 查看当前工作数据库
select database();
-- 查看当前工作数据库的版本
select version();
- 查看服务器所有参数
show variables;
-- 和字符集设置相关的参数 :
character_set_client | gbk
character_set_connection | gbk
character_set_database | utf8
character_set_filesystem | binary
character_set_results | gbk
character_set_server | utf8
character_set_system | utf8
-- 如果client,connection和result的设置不是gbk, 必须执行以下语句
-- 修改客户端编码
set names gbk;
数据库结构
mysqld 服务器
数据库1(目录形式)
表1
数据(记录)1
数据(记录)2
数据(记录)3
....
表2
表3
....
数据库2
......
关于表的操作
DML 数据操纵语言.
insert C
select R
update U
delete D
-- 查看表中数据
select * from 表名;
-- 查看表结构
describe employees;
desc employees;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| employee_id | int(6) | NO | PRI | NULL | auto_increment |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | YES | | NULL | |
| email | varchar(25) | YES | | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| job_id | varchar(10) | YES | MUL | NULL | |
| salary | double(10,2) | YES | | NULL | |
| commission_pct | double(4,2) | YES | | NULL | |
| manager_id | int(6) | YES | | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+--------------+------+-----+---------+----------------+
--创建表
create table customer( create + table +表名（列名 数据类型，列名 数据类型，。。。。。）；
id int,
name varchar(20),
age int,
phone varchar(15),
birthday date
);
--向表中添加数据
insert into customer ( insert into 表名（列名，列名....)values(数据，数据，.....);
id,
name,
age,
phone,
birthday
) values (
1,
'张三',
30,
'134234234',
'1992-5-8'
);
-- 更新记录(修改数据)
-- 如果更新时不加where过滤, 会导致所有数据被修改
update customer set update 表名 set 列名 = '新数据'，列名 = '新数据'；
name = '某人',
age = 10;
update customer set update 表名 set 列名 = '新数据'，列名 = '新数据' where 条件控制；
name = '张三',
age = 30
where
id = 1;
-- 删除记录
-- 如果没有where过滤, 会全部删除.
delete from customer; delete from 表名；
delete from customer delete from 表名 where 条件控制；
where id = 2;
查询语句
--查看表中所有数据
SELECT * select * from 表名；
FROM departments;
跨库操作表 : 只需要在表名上加上库.限定
select * from company.employees;
--查看表中指定列的数据 select 列名 ，列名...from 表名
SELECT department_id, location_id
FROM departments;
-- AS用于给列起别名, AS关键字可以省略 select 列名 别名 ，列名 别名.... from 表名 别名
SELECT last_name AS name, commission_pct comm
FROM employees;
-- 过滤行 where 条件布尔 : 工作流程就是从基本表中测试每行数据, 都经过条件布尔测试一下, 如果结果为真留下, 为假则丢弃.
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ; select 列名 ，列名...from 表名 where 条件
执行顺序 : from => where => select
-查询所有中国城市的人口和名称和id号和国家代码, 给国家代码起别名, 尝试在where中使用别名.
select
population,
name,
id,
countrycode code
from
city
where
countrycode = 'chn';
-- between a and b --等效于salary >= a && salary <= 3500
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
-- id in(a, b, c) -- 等效于id = a || id = b || id = c
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201);
--like模糊查询 后面可以加_和%通配符 _下划线代表任意一个字符 %代表任意数量的任意字符
-- 查询国家名称中只要包含ch的都行
select
code,
population,
name,
continent
from
country
where
name like '%ch%'
-- 名字中第3个和第4个是in的国家
select
code,
population,
name,
continent
from
country
where
name like '__in%'
-- 查询城市表中, 名称的第2个和3个字母是or的城市.
select
id,
name,
countrycode
from
city
where
name like '_or%';
-- 查看哪些国家没有首都
-- null和任意的比较运算, 结果一定是false, 处理null, 必须使用特殊的is来判断
-- null值在进行统计时会被自动忽略.
select
code,
name,
continent,
capital
from
country
where
capital = null;
select
code,
name,
continent,
capital
from
country
where
capital is null;
select
code,
name,
continent,
capital
from
country
where
capital is not null;
多表查询
-- 多表联接 : 会产生笛卡尔集
-- 为了学习, 创建简单表
create table city2
select * from city where name='london';
+------+--------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 456 | London | GBR | England | 7285000 |
| 1820 | London | CAN | Ontario | 339917 |
+------+--------+-------------+----------+------------+
create table country2
select * from country where code in('gbr', 'can');
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------
| CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 | 79.4 | 598862.00 | 625626.00 | Canada
| GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingd
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+-------------
select * from city2, country2;
结果集中的绝大多数都是垃圾. 必须要过滤掉
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--
| ID | Name | CountryCode | District | Population | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | L
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--
| 456 | London | GBR | England | 7285000 | CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 |
| 1820 | London | CAN | Ontario | 339917 | CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 |
| 456 | London | GBR | England | 7285000 | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 |
| 1820 | London | CAN | Ontario | 339917 | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+--
select 无法分辨查询的是哪个表的列
name,
population,
name,
continent,
population
from
city2,
country2
where
countrycode = code;
--多表查询应该带着表名区分列名
select
city2.name as cityName,
city2.population cityPop,
country2.name countryName,
country2.continent,
country2.population countryPop
from
city2,
country2
where
city2.countrycode = country2.code;
-- 表名一旦指定了别名, 原名就不可以使用了.
select
city2.name as cityName,
city2.population cityPop,
country2.name countryName,
country2.continent,
country2.population countryPop
from
city2 as ci,
country2 as co
where
city2.countrycode = country2.code;
内联时最好带上jion on jion 后跟表名 on 后加联接条件
-- 最好的写法是下面的. inner关键字可以省略
select
ci.name as cityName,
ci.population cityPop,
co.name countryName,
co.continent,
co.population countryPop
from
city2 as ci
join
country2 co
on -- on后面只跟 联接条件
ci.countrycode = co.code
where -- where 后面只跟 普通行过滤
ci.population > 1000000;
-- 外联接可以保证某张表的数据完整, 即使联接条件为假, 也要保留数据
外联有左外联和右外联，前面的表为左后面表为右，左联保证左数据完成，右联保证右表数据完整，outer可以省略
select
co.name country,
ci.name capital,
ci.district
from
country2 co
left outer join
city2 ci
on
co.capital = ci.id;
select
co.name country,
ci.name capital,
ci.district
from
country2 co
right outer join
city2 ci
on
co.capital = ci.id;
// 伪代码
// 内联接
Set leftTable;
Set rightTable;
Set resultSet = new Set();
for (int i = 0; i < leftTable.length; i++) {
Row leftRow = leftTable[i];
for (int j = 0; j < rightTable.length; j++) {
Row rightRow = rightTable[j];
if (联接条件(leftRow, rightRow)) {
Row newRow = leftRow + rightRow;
resultSet.add(newRow);
}
}
}
// 外联接
Set leftTable;
Set rightTable;
Set resultSet = new Set();
for (int i = 0; i < leftTable.length; i++) {
Row leftRow = leftTable[i];
boolean flag = false;
for (int j = 0; j < rightTable.length; j++) {
Row rightRow = rightTable[j];
if (联接条件(leftRow, rightRow)) {
Row newRow = leftRow + rightRow;
resultSet.add(newRow);
flag = true; // 表明联接条件为真的记录保存起来
}
}
if (!flag) {
Row newRow = leftRow + 空行(右表);
resultSet.add(newRow);
}
}
-- 单行函数, 查询结果中对每一行都单独执行的函数
select
upper(name),
lower(continent)
from
county;
select
concat(code, name),
substr(name, 3, 2)
from
country;
-- 把国家的代码和名称及大洲用@符号连接. 如 : CHN@China@asia
select
concat(concat(concat(concat(code, '@'), name), '@'), continent)
from
country;
select
concat(code, '@', name, '@', continent)
from
country;
-- 分组函数 : 作用于一组数据. 默认情况下虚表中的所有记录被当作一个组.
分组函数都是统计运算, 特点就是一组数据只能有一个结果. 结果也必须是所有数据处理完后才有的.
max() 最大值
min() 最小值
avg() 平均值
sum() 求和
count() 计数
select
max(population),
min(surfacearea)
from
country ;
select
-- name, 不可以再把普通的列放在这里
max(name),
max(population),
min(surfacearea)
from
country ;
--group by 分组语句
-- 解决的问题类似于, 查看 各大洲 的情况.
select
continent, -- 群体概念
max(name),
avg(surfacearea)
from
country
group by
continent;
--having 用来过滤分组结果
-- 对分组的结果进行过滤, 只能使用having
select
countrycode,
sum(population) sumPop
from
city
group by
countrycode
having
sumPop > 10000000
order by
sumPop;
解决SQL步骤 :
1) 最基础的基表, from
2) 考虑一张表的数据够不够, 如果不够进行连接 join
3) 再考虑是外还是内, 如果是外还得考虑保证哪张表完整
4) 有join必须要有on
5) 是否需要对当前的大基表进行基础的行过滤. where
6) 是否需要分组, 分组依据的列是什么. group by
7) 如果有分组,第一时间,把分组的列放在 select 后面, 并同时继续分析要选择哪些列或统计运算.
8) 是否要再对分组结果进行进一步过滤, 如果需要使用having
9) 是否需要排序, 如果需要使用order by .
-- 子查询 : 解决一次查询不能解决的问题.
-- 找出哪个城市的人口是最多的.
select
countrycode,
name,
population
from
city
where
population = (select max(population) from city);
select
name,
surfacearea,
continent
from
country
where surfacearea >
(select
avg(surfacearea)
from
country)
修改数据
-- 修改数据库, 只能修改默认字符集
alter database school charset utf8;
-- 基于子查询建表, 把虚表变成实表
create table country2
as select * from world.country; -- 复制表, 只是机械的把虚表变实, 不能复制约束(主键)
-- 复制表结构建表, 没有数据
create table country3
like world.country;
- 修改表
alter table 表名
-- 支持若干子名
-- 添加新列
add column 新列名 数据类型(长度) 其他选项.
alter table student
add column address varchar(200) not null; -- 新列追加在所有列的最后.
alter table student
add column email varchar(50) after age; -- 在指定列后面添加新列
alter table student
add column cardNo char(18) first; -- 在最前面添加新列
-- 修改列, 数据类型, 长度, 其他选项
modify 列名 新数据类型(新长度) 新其他选项
alter table student
modify name varchar(10) not null;
-- 重命名列
change 列名 新列名 新数据类型(新长度) 新其他选项;
alter table student
change phone mobile char(11) unique; -- unique就是唯一.
-- 丢弃列
drop column 列名; -- 会导致删除的列所对应的所有数据全部丢失, 无法找回
alter table student
drop column cardNo;
-- 重新命名表
rename to 新表名
alter table student
rename to students;
alter table teacher
rename to teachers;
-- 修改约束
以上都是alter table 的子句, 用于修改表
-- 丢弃表 操作很危险, 如果没有数据备份, 就永远找不回来.
drop table 表名;
-- 丢弃表时可以批量丢弃
drop table country2, country3;
-- 清空表 DDL, 表中数据不能恢复. 效率高
truncate table 表名;
delete 是DML, 是可以后悔的. 效率低.
-- 实现表的完全克隆!!!
create table if not exists country2 like world.country;
-- 基于子查询插入数据, 好处就是一次性插入多条记录.
insert into country2
select * from world.country;
事务
A Atomic 原子性 所有操作不可分割.
C Consistence 一致性 数据在事务前和事务后数据是一致的.
I ISOLATION 独立性 不同事务之间具有隔离性
D Duration 持久性 事务一旦提交, 永久生效.
DCL : 数据控制语言 : commit, rollback, grant, revoke...
显式启动事务 :
set autocommit = false;
set @@autocommit = off;
多条DML语句;
commit; 事务提交, 成功
rollback; 事务回滚, 失败
ddl语句执行 : 事务提交
会话正常结束 提交
异常结束 : 回滚
还原设置
set autocommit = true;
set @@autocommit = on;
预编译
执行预编译
execute p1;
sql中的?相当于方法中的形参, ?在SQL中只能替换值的部分.
prepare p2 from '
insert into classes(
name,
room,
begindate,
master
) values (
?,
?,
?,
?
)
';
##
- 标题
如果sql中有?, 必须使用用户变量来传值
set @name='BigData0621', @r='306', @b='2020-06-21', @master=2;
执行预编译时使用变量, 用户变量就相当于方法调用时的实参.
execute p2 using @name,@r,@b,@master;
丢弃预编译
drop prepare p1;
-- 如果在sql中也有', 必须要使用''来进行转义.
prepare p2 from '
insert into classes(
name,
room,
begindate,
master
) values (
?,
''301'',
now(),
?
)
';
为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
约束
有以下六种约束:
NOT NULL 非空约束，规定某个字段不能为空
UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一), 一张表只允许有一个主键
FOREIGN KEY 外键
DEFAULT 默认值, 只能写成列级的.
create table if not exists test(
id int primary key, -- 可以写成表级, 最好写成表级
name varchar(20) unique, -- 可以写成表级, 最好写成表级
age int not null, -- 必须是写成列级
gender enum('男', '女') default '男', -- 必须写成列级
email varchar(20)
);
insert into test(
id,
name,
age
) values (
1,
null,
20
);
insert into test(
id,
name,
age
) values (
2,
null,
20
);
create table if not exists test2(
id int,
name varchar(20), -- 可以写成表级, 最好写成表级
age int not null, -- 必须是写成列级
gender enum('男', '女') default '男', -- 必须写成列级
email varchar(20),
primary key(id, email), -- 联合主键, 两个列的组合值要 唯一且非空.
unique(name)
);
insert into test2(
id,
name,
age,
email
) values (
1,
null,
20,
'aa'
);
insert into test2(
id,
name,
age,
email
) values (
2,
null,
20,
'aa'
);
丢弃主键
alter table test2
drop primary key;
添加主键
alter table test2
add primary key(id)
-- 查询班级及班主任信息
select
c.*,
t.*
from
classes c
left join
teachers t
on
c.master = t.id;
create table if not exists teacher(
id int auto_increment, -- 将来这个列对应的数据可以自动生成. 必须作成主键
name varchar(20), -- varchar数据类型必须要有长度.
age int,
salary double,
gender enum('男', '女') default '男',
primary key(id) -- 表级主键
);
drop table if exists classes;
create table if not exists classes(
id int auto_increment,
name varchar(20) not null, -- not null 就是非空
room char(3),
begindate date,
master int, -- 班主任是一个整数, 将来是某个老师的id, 为了强制要求, 必须给它加上外键
primary key(id),
-- foreign key(本表的外键的列) references 父表(父表的主键)
foreign key(master) references teachers(id)
);
set @name='h50727', @master=2;
set @name='bigdata0727', @master=20;
一旦使用了外键约束, 子表插入数据时必须要参考父表的记录, 会导致效率降低.
在删除父表被引用的记录时, 也无法直接删除!!!
delete from teachers where id = 2;
-- 丢弃外键, 必须要提供外键名称.
-- 查看外键名
show create table classes;
alter table classes drop foreign key `classes_ibfk_1`;
-- 添加外键
alter table classes
-- add constraint fk foreign key(master) references teachers(id) on delete cascade; -- set null(子表中的引用值置空) do nothing(不允许删除父表记录) cascade(级联);
add constraint fk foreign key(master) references teachers(id) on delete set null; -- set null(子表中的引用值置空) do nothing(不允许删除父表记录) cascade(级联);
175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
