数据库操作
创建数据库:
create database 数据库名称;
例如:
创建名为test的测试数据库
create database test;
查看创建好的数据库:
show create database 数据库名称;
例如:
查看创建好的test数据库
show create database test;
使用数据库:
use 数据库名称;
例如:
使用创建好的test数据库
use test;
删除数据库:
drop database 数据库名称;
例如:
删除创建好的test数据库
drop database test;
数据表操作
数据表结构操作
创建表:
create table 表名( 字段名1 类型[(宽度)] [约束条件],
字段名2 类型[(宽度)] [约束条件],
字段名3 类型[(宽度)] [约束条件] )
例如:用SQL语句创建以下员工信息表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wqOxstba-1597138362525)(SQL.assets/image-20200806231324815.png)]
create table emp(
depid char(3),
depname varchar(20),
peoplecount int
);
删除数据表:
drop table 表名称;
例如:
删除创建好的test表
drop table test;
修改表结构:
MySQL使用alter table语句修改数据表结构,包括: 修改表名,修改字段数据类型或
字段名,增加和删除字段,修改字段的排列位置等
- 例如:将数据表emp改名为empdep
alter table emp rename empdep; - 例如:将数据表empdep中depname字段的数据类型由varchar(20)修改varchar(30)
alter table empdep modify depname varchar(30); - 例如:将数据表empdep中depname字段的字段名改为dep
alter table empdep change depname dep varchar(30); - 例如:将数据表empdep中dep字段的字段名改回为depname,并将该字段数
据类型该回为varchar(20)
alter table empdep change dep depname varchar(20); - 例如:为数据表empdep添加新字段maname,新字段数据类型为varchar(10),
约束条件为非空
alter table empdep add maname varchar(10) not null; - 例如:将数据表empdep中maname字段的排列顺序改为第一位
alter table empdep modify maname varchar(10) first; - 例如:将数据表emp中maname字段的排列顺序改到depid字段之后
alter table empdep modify maname varchar(10) after depid; - 例如:删除maname字段
alter table empdep drop maname;
查看数据库所有表:
show tables;
查看数据表结构:
DESC 表名;
表字段的数据类型
数值类型:
- INT:有符号的和无符号的。有符号大小-21474836482147483647,无符号大小04294967295。宽度最多为11个数字- int(11)
- TINYINT:有符号的和无符号的。有符号大小-128127,无符号大小为0255。宽度最多为4个数字- tinyint(4)
- SMALLINT:有符号的和无符号的。有符号大小-3276832767,无符号大小为065535。宽度最多为6个数字- smallint(6)
- MEDIUMINT:有符号的和无符号的。有符号大小-83886088388607,无符号大小为016777215。宽度最多为9个数字- mediumint(9)
- BIGINT:有符号的和无符号的。宽度最多为20个数字- bigint(20)
- FLOAT(M,D):只能为有符号的。默认为(10,2),M是数字的最大数(精度),maximum, D是小数点右侧数字的数目(标度)decimal。
- DOUBLE(M,D):只能为有符号的。默认为(16,4)
- DECIMAL(M,D):只能为有符号的。
日期和时间类型 :
- DATE:YYYY-MM-DD格式,在1000-01-01和9999-12-31之间。例如:
1973-12-30 - DATETIME:YYYY-MM-DD HH:MM:SS格式,位于1000-01-01 00:00:00
和9999-12-31 23:59:59之间。例如: 1973-12-30 15:30:00 - TIMESTAMP:称为时间戳,在1970-01-01 00:00:00和2037-12-3123:59:59之间。例如, 1973年12月30日下午15:30,则在数据库中存储为:19731230153000
- TIME: 以HH:MM:SS格式, -838:59:59~838:59:59
- YEAR(2|4): 以2位或4位格式存储年份值。如果是2位, 1970~2069;如果是4位, 1901~2155。默认长度为4
字符串类型:
- CHAR(M):固定长度字符串,长度为1-255。如果内容小于指定长度,右边填充空格。如果不指定长度,默认为1
- VARCHAR(M): 可变长度字符串,长度为1-255。定义该类型时必须指定长度
- BLOB 或TEXT:最大长度65535。存储二进制大数据,如图片。不能指定长度。两者区别: BLOB 大小写敏感
- TINYBLOB 和TINYTEXT:最大长度255。不能指定长度。
- MEDIUMBLOB 或MEDIUMTEXT:最大长度16777215 字符
- LONGBLOB 或LONGTEXT:最大长度4294967295 字符
- ENUM:枚举。例如: ENUM(‘A’,’B’,’C’)。 NULL 值也可
表字段的约束条件
主键约束:
主键约束:保证表中每行记录都不重复
主键,又称为”主码”,是数据表中一列或多列的组合。 主键约束要求主键列的数据必须是唯一的,并且不允许为空。 使用主键,能够惟一地标识表中的一条记录,并且可以结合外键来 定义不同数据表之间的关系,还可以加快数据库查询的速度。
主键分为两种类型:
- 单字段主键:
create table emp(
depid char(3) primary key,
depname varchar(20),
peoplecount int
);
- 多字段联合主键:
create table emp(
depid char(3),
depname varchar(20),
peoplecount int,
primary key(depname,depid)
);
非空约束:
非空约束,指的是字段的值不能为空
create table emp(
depid char(3) primary key,
depname varchar(20) not null,
peoplecount int
);
唯一约束:
唯一性约束,要求该列的值必须是唯一的
create table emp(
depid char(3) primary key,
depname varchar(20) not null,
peoplecount int unique
);
默认约束:
默认约束,指定某个字段的默认值
create table emp(
depid char(3) primary key,
depname varchar(20) not null default '-',
peoplecount int unique
);
自增字段约束:
一个表只能有一个自增字段,自增字段必须为主键的一部分。默认情况下从1开始自增
CREATE TABLE example(
id INT PRIMARY KEY AUTO_INCREMENT, -- 创建整数型自增主键
name VARCHAR(4) NOT NULL, -- 创建非空字符串字段
math INT DEFAULT 0, -- 创建默认值为0的整数型字段
minmax FLOAT UNIQUE – 创建唯一约束小数型字段
);
数据表数据操作
插入数据:
-
insert插入入数据
insert into 表名(字段1,字段2,…) values ……
例如:
insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2);
-
导入数据外部数据
load data local infile '文件路径.txt’
into table 表名
fields terminated by ‘\t’
ignore 1 lines;例如:
LOAD DATA LOCAL INFILE 'C:\\Users\\19570\\Desktop\\a.txt'
INTO TABLE emp
FIELDS TERMINATED BY ','
IGNORE 0 LINES;
删除数据:
-
delete删除数据:
DELETE FROM 表名 [WHERE Clause],如果省略where的话则删除表中所有数据记录
delete from fruits where f_id = 'b5';
-
截断删除数据:
截断表可以用于删除表中的所有数据
truncate table 表名;
-
区别:
截断表的执行速度与不带where子句的delete(删除)命令相同,但比它还要快。delete(删除)一次删除一行数据,并且将每一行被删除的数据都作为一个事务记录日志;而truncate (截断)表则回收整个数页,只记录很少的日志项。delete(删除)和truncate(截断)都会回收被数据占用的空间,以及相关的索引。只有表的拥有者可以截断表。
另外,truncate表之后,如果有自动主键的话,会恢复成默认值。
更新数据:
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
-
你可以同时更新一个或多个字段。
-
你可以在 WHERE 子句中指定任何条件。
-
你可以在一个单独表中同时更新数据。
UPDATE emp SET depid='103',depname='冯振华',peoplecount=12
WHERE depid='100';
查询数据:
SELECT〈目标列组〉
FROM〈数据源〉
[WHERE〈元组选择条件〉 ]
[GROUP BY〈分列组〉 [HAVING 〈组选择条件〉 ]]
[ORDER BY〈排序列1〉〈排序要求1〉 [, …n]];
select操作符:
- 算术操作符+(加号)、-(减号)、 *(乘号)和 /(除号)
- 比较操作符=(等于)、 >(大于)、 <(小于)、 <=(小于等于)、 >=(大于等于)、 !=或<> (不等于)、 !>(不大于)和 !<(不小于),共9种操作符
select聚合函数:
聚合函数需要先进行group by才能使用,group by后加having用于聚合函数的过滤。
函数名称 | 功能 |
---|---|
avg | 按列求平均值 |
sum | 按列求和 |
max | 按列求最大值 |
min | 按列求最小值 |
count | 按列求个数 |
select连接方式:
内连接:
按照连接条件合并两个表,返回满足条件的行。
SELECT <select_list> FROM A INNER JOIN B ON A.Key = B.Key;
左连接:
结果中除了包括满足连接条件的行外,还包括左表的所有行
SELECT <select_list> FROM A LEFT JOIN B ON A.Key = B.Key;
右连接:
结果中除了包括满足连接条件的行外,还包括右表的所有行
SELECT <select_list> FROM A RIGHT JOIN B ON A.Key = B.Key;
select联合查询:
union: 用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行。
例: 用union合并t1与t2表
select t1.* from t1
union
select t2.* from t2;
union all:用于合并两个或多个 SELECT 语句的结果集,保留重复行。
例: 用union all合并t1与t2表
select t1.* from t1 union all select t2.* from t2;
select操作符:
distinct操作符:
用来消除重复记录
例如: 查询fruits表中所有不重复的s_id
select distinct s_id from fruits;
as重命名:
例如: 用as将fruits表名重命名为f后使用
select f.* from fruits as f;
limit(i,n):
限制查询结果行数
- i : 为查询结果的索引值(默认从0开始)
- n : 为查询结果返回的数量
all与any区别:
all:大于子查询的每一条记录的结果,如>all (select age from student where sex = ‘girl’)上面一句语句表示比所有女生年纪大(比年纪最大的女生的年纪还要大)
any:大于子查询的任意一条记录的结果如>any (select age from student where sex = ‘girl’)这一句表示的意思是,只要比任意一个女生年纪大就可以了(比年纪最小的女生的年纪大)
like:
LIKE运算符用于WHERE表达式中,以搜索匹配字段中的指定内容。
通配符_匹配一个字符:
例如:查三个字的名字,未知的单姓,名为振华的信息
SELECT * FROM username WHERE LIKE BINARY '_振华'
通配符%匹配任意字符(可匹配空字符):
SELECT * FROM username WHERE LIKE BINARY '%振%'
exists:
将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。
例如:有三张表,分别是 学生表(S),课程表(C)和选课中间表(SC)
问题1:查找课程全选的学生的姓名
可以转换为:查询没有一门课没有被该生选择的学生的学号和姓名
select S,SNAME -- 在 S 表里选 S,SNAME
from S
where not exists -- 不存在
(select * -- 课程 from C
where not exists -- 没有
(select * *-- 被该生选择的课程*
from SC
where SC.S=S.S and SC.C=C.C)) *-- 相关查询,三个表进行连接
*
问题2:查找没全选的学生的姓名
可以转换为:查询存在课程没有被该生选择的学生的学号和姓名
select S,SNAME *-- 在 S 表里选 S,SNAME
from S
where exists -- 存在
(select * -- 课程
from C
where not exists -- 没有
(select * -- 被该生选择的课程
from SC
where SC.S=S.S and SC.C=C.C)) -- 相关查询,三个表进行连接
问题3:查找至少选了一个的学生的姓名
可以转换为:查询存在一门课被该生选择的学生的学号和姓名
select S,SNAME -- 在 S 表里选 S,SNAME
from S
where exists -- 存在
(select * -- 课程
from C
where exists -- 有
(select * -- 被该生选择的课程
from SC
where SC.S=S.S and SC.C=C.C)) -- 相关查询,三个表进行连接
问题4:查找全没选的学生的姓名
可以转换为:查询不存在一门课被该生选择的学生的学号和姓名
常用函数:
数学函数:
字符串函数:
日期和时间函数:
其他函数:
GROUP_CONCAT()函数:常与关键字 GROUP BY 一起使用,能够将分组后指定的字
段值都显示出来。
存储结构
存储过程创建方法:
CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics…] routine_body
CREATE PROCEDURE: 用来创建存储函数的关键字
sp_name: 存储过程的名称
proc_parameter: 指定存储过程的参数列表,形式如下
[ IN | OUT | INOUT ] param_name type
IN: 输入参数,默认值 OUT: 输出参数 INOUT: 既可以输入也可以输出
param_name: 参数名称
type: 参数类型,可以是MySQL数据库中的任意类型
chracteristics: 制定存储过程的特性
routine_body: SQL代码内容,可以用BEGIN…END来表示SQL代码的开始和结束
DELIMITER语句:
作用:改变结束符
使用方法: DELIMITER //,将MySQL的结束符设置为//
因为MySQL默认的语句结束符号为分好;,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符,并以END //结束存储过程。存储过程定义完毕之后在使用DELIMITER ;恢复默认结束符。 DELIMITER也可以指定其他符号作为结束符。
局部变量:
局部变量: 在子程序中声明并使用,作用范围是在BEGIN…END程序中。
-
定义变量:
在存储过程中使用DECLARE语句定义变量DECLARE var_name[,varname]…data_type [DEFAULT value]
var_name为局部变量的名称,DEFAULT value子句给变量提供一个默认值,如果没有DEFAULT子句,初始值为NULL
例: DECLARE myparam INT DEFAULT 100; -
为变量赋值:
a. 使用SET语句为变量赋值:
``DECLARE test INT;SET test = 1 + 2;`
b. 使用SELECT…INTO为变量赋值:
DECLARE test INT;
SELECT 1 + 2 INTO test;
用户变量:
用户变量:用户变量与链接有关,一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。
- 用户变量的形式为@var_name
- 用户变量不需要用DECLARE定义
- 用户变量的赋值方法与局部变量相同
例:
a. 使用SET语句为变量赋值:
``SET @test = 1 + 2; SELECT @test;
b. 使用SELECT…INTO为变量赋值:
SELECT 1 + 2 INTO @test;SELECT @test; `
应用IN参数:
DELIMITER //
CREATE PROCEDURE tsum(IN a INT, IN b INT)
BEGIN
SELECT a+b into @sumvalue;
SELECT @sumvalue;
END //
DELIMITER;
应用OUT参数:
DELIMITER //
CREATE PROCEDURE tsum(IN a INT, IN b INT, OUT c INT)
BEGIN
SELECT a+b into c;
SELECT c;
END //
DELIMITER;
流程控制 – IF判断语句:
语法格式:
IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]…
[ELSE statement_list]
END IF
例:
DELIMITER //
CREATE PROCEDURE iftest(IN a INT)
BEGIN
IF a < 0 THEN SET @final = '负数';
ELSEIF a = 0 THEN SET @final = '零';
ELSE SET @final = '正数';
END IF;
END //
DELIMITER ;
CALL iftest(-3);
SELECT @final;
流程控制 – CASE判断语句:
语法格式:
CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list]…
[ELSE statement_list]
END CASE
例:
DELIMITER //
CREATE PROCEDURE casetest(IN a INT)
BEGIN
CASE
WHEN a < 0 THEN SET @final = '负数';
WHEN a = 0 THEN SET @final = '零';
ELSE SET @final = '正数';
END CASE;
END //
DELIMITER ;
CALL casetest(5);
SELECT @final;
流程控制 – WHILE循环语句:
语法格式:
WHILE expr_condition DO
statement_list
END WHILE
例:参数值小于10时执行循环语句
DELIMITER //
CREATE PROCEDURE whiletest(IN a INT)
BEGIN
while a < 10 DO
set a = a + 1;
END WHILE;
END //
DELIMITER ;
调用存储过程:
方法: CALL pro_name([proc_parameter]);
例:
DELIMITER //
CREATE PROCEDURE TEST()
BEGIN
SELECT * FROM TEST;
END //
DELIMITER;
CALL test();
删除存储过程:
方法: DROP PROCEDURE sp_name;