mysql基础
大写为关键字
一、数据库操作
1.创建数据库
create database [要创建的数据库名];
例:
CREATE DATABASE test;
2.如何查看有什么数据库?
show databases;
3.如何选择数据库?
use databasesName;
二、表操作
1.如何创建一个数据表
create table 表名 (
字段名1 varchar(10),
字段名2 char(1),
字段名3 date
);
2.显示数据库中的所有表
show tables;
3.查看数据表结构
describe 表名;
4.查询表
SELECT * from 表名;
5.在创建好的表中添加数据
insert into 表名 values ('要插入的字段名1', '字段名2',字段名3);
//上面的是要对应表字段名
添加指定表字段:
INSERT INTO pet(name,owner,species) VALUES ('puffball', 'Diane', 'hamster');
6.修改数据
UPDATE 数据库 SET name = 'squirrel' where owner = 'Diane';
//where表示条件,Diane修改为squirre
7.删除数据
DELETE FROM pet where name = 'squirrel';
8.删除表
DROP TABLE myorder;
9.建表约束
关键字:primary key
-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 联合主键
-- 联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
-自增约束 关键字:auto_incrment,
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 添加主键约束
-- 如果忘记设置主键,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
-- 删除主键
ALTER TABLE user drop PRIMARY KEY;
非空约束:NOT NULL
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
id INT,
name VARCHAR(20) NOT NULL
);
-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
10.唯一主键 unique
-- 建表时创建唯一主键
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
-- 添加唯一主键
-- 如果建表时没有设置唯一建,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
-- 删除唯一主键
ALTER TABLE user DROP INDEX name;
11.-- 建表时添加默认约束
– 约束某个字段的默认值 :DEFAULT
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);
-- 移除非空约束
ALTER TABLE user MODIFY age INT;
12.外键约束
–涉及到两个表
设置后:主表中没有的数据值,在附表中不能使用
foreign key references
-- 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 这里的 class_id 要和 classes 中的 id 字段相关联
class_id INT,
-- 表示 class_id 的值必须来自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);
-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的记录被副表引用时,主表不可以被删除。
三、数据库的三大设计范式
1NF
只要字段值还可以继续拆分,就不满足第一范式。
范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定。
2NF
在满足第一范式的前提下,其他列都必须完全依赖于主键列。如果出现不完全依赖,只可能发生在联合主键的情况下:
-- 订单表
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);
实际上,在这张订单表中,product_name
只依赖于 product_id
,customer_name
只依赖于 customer_id
。也就是说,product_name
和 customer_id
是没用关系的,customer_name
和 product_id
也是没有关系的。
这就不满足第二范式:其他列都必须完全依赖于主键列!
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);
拆分之后,myorder
表中的 product_id
和 customer_id
完全依赖于 order_id
主键,而 product
和 customer
表中的其他字段又完全依赖于主键。满足了第二范式的设计!
3NF
在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone
有可能依赖于 order_id
、 customer_id
两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);
修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计
四、查询语句
准备数据
-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;
-- 创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班级
);
-- 创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- 职称
department VARCHAR(20) NOT NULL -- 部门
);
-- 创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- 教师编号
-- 表示该 tno 来自于 teacher 表中的 no 字段值
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- 学生编号
c_no VARCHAR(20) NOT NULL, -- 课程号
degree DECIMAL, -- 成绩
-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为联合主键
PRIMARY KEY(s_no, c_no)
);
-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');
-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');
-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');
-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');
1.查看所有表
SHOW TABLES;
-- 查看表结构
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;
2.基本查询
- 查询 student 表的所有行
SELECT * FROM student;
-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
1.-- department: 去重查询
-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询
SELECT DISTINCT department FROM teacher;
2.-- BETWEEN xx AND xx: 查询区间, AND 表示 “并且”
-- 查询 score 表中成绩在60-80之间的所有行(区间查询和运算符查询)
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;
3.-- IN: 查询规定中的多个值
-- 查询 score 表中成绩为 85, 86 或 88 的行
SELECT * FROM score WHERE degree IN (85, 86, 88);
4.-- or: 表示或者关系
-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
5.ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序,从高到低
-- ASC(默认): 升序,从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;
6.LIMIT语句 排序查询
limit r,n;
r, n: 表示从第r行开始,查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
7. GROUP BY: 分组查询
-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
-- AVG: 平均值
8.多表查询
查询所有学生的 name
,以及该学生在 score
表中对应的 c_no
和 degree
。
SELECT name, c_no, degree FROM student, score
WHERE student.no = score.s_no;
五、UNION 和 NOTIN 的使用
案例:
查询 计算机系
与 电子工程系
中的不同职称的教师。
-- NOT: 代表逻辑非
SELECT * FROM teacher WHERE department = '计算机系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '电子工程系'
)
-- UNION:合并两个集
SELECT * FROM teacher WHERE department = '电子工程系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '计算机系'
);
ANY 表示至少一个 - DESC ( 降序 )
查询课程 3-105
且成绩 至少 高于 3-245
的 score
表。
运算符
and操作
AND
操作是将两个或多个逻辑运算符布尔表达式只有当两个表达式为true返回true。如果两个表达式的一个评估为假返回false。
逻辑或运算符(OR 或者 ||)
R 和 || 都是逻辑或运算符,具体语法规则为:
- 当两个操作数都为非 NULL 值时,如果有任意一个操作数为非零值,则返回值为 1,否则结果为 0;
- 当有一个操作数为 NULL 时,如果另一个操作数为非零值,则返回值为 1,否则结果为NULL;
- 假如两个操作数均为 NULL 时,则返回值为 NULL。
异或运算(XOR 运算符)
XOR 表示逻辑异或,具体语法规则为:
- 当任意一个操作数为 NULL 时,返回值为 NULL;
- 对于非 NULL 的操作数,如果两个操作数都是非 0 值或者都是 0 值,则返回值为 0;
- 如果一个为0值,另一个为非 0 值,返回值为 1。
mysql怎么查询数据库版本
方法:
1、利用“select version();”语句查询;2、利用“show variables like ‘%version%’”语句查询;
3、在mysql客户端中利用“status”命令查询;
4、在终端中用“mysql -V”查询。
六、MySQL聚合函数
MySQL聚合函数 - 提供最常用的MySQL聚合函数的简要概述。
avg()函数 - 计算一组值或表达式的平均值。
count()函数 - 计算表中的行数。
instr()函数 - 返回子字符串在字符串中第一次出现的位置。
sum()函数 - 计算一组值或表达式的总和。
min()函数 - 在一组值中找到最小值。
max()函数 - 在一组值中找到最大值。
group_concat()函数 - 将字符串从分组中连接成具有各种选项(如DISTINCT
,ORDER BY
和SEPARATOR
)的字符串。
MySQL标准偏差函数 - 显示如何计算人口标准偏差和样本标准偏差。
mysql 常用函数与参数
=,>,>=,<=,<> | 比较运算符 |
---|---|
and , or | 逻辑运算符 |
version() | mysql数据库版本 |
database() | 当前数据库名 |
user() | 用户名 |
current_user | 当前用户名 |
system_user | 系统用户名 |
@@datadir | 数据库路径 |
@@version_compile_os | 操作系统版本 |
length() | 返回字符串长度 |
substring() | 截取字符串 |
substr() | 参数为1.截取的字符串,2.截取的起始位置,3.截取长度 |
mid | 截取字符串 |
left() 案例: select left(“123456”,3) | 从左侧开始截取字符的个数 |
concat() 案例: select concat(“a”,“b”) | 没有分隔符的连接字符 |
concat_ws 案例: select concat_ws(“_”,“a”,“b”) _为分隔符 | 含有分隔符的连接字符串 |
group_concat() 案例 select group_concat(degree) from score | 连接一个组的字符串,不同列的记录会放到同一记录里面 |
ord() ,asscii() | 返回ASCII码 |
hex() | 将字符转为16进制 |
unhex() | hex反向操作 |
mad5() | 返回MD5值 |
floor(x) | 返回不大于x的最大整数 |
round() | 返回参数x接近的整数 |
rand() | 返回0-1之间的随机浮点数 |
load_file() | 读取文件,并返回文件内容作为一个字符串 |
sleep() | 睡眠时间为指定的秒数 |
if(true,t,f) | if判断,true执行t,不成立执行f |
find_in_set() | 返回字符串在字符串列表中的位置 |
benchmark() | 指定语句执行的次数 |
name_const() | 返回表作为结果 |
文件读写操作 | |
load_file() | 读取函数 |
into outfile 或 dumpfile | 导出函数 |
七、内置数据库 information_schema 表特性:
在mysql5.0以后的版本存在一个information_schema数据库、里面存储记录数据库名、表名、列名的数据库
相当于可以通过
这个数据库获取到数据库下面的表名和列名。
-
information.schema.table
记录所有表名信息的表
-
information.schema.columns
记录所有列名信息的表
其中table表中重要数据字段有:
-
table_name
表名
-
column_name
#列名
-
table_schema #数据库名
记录schemata表中为数据库名,
tables表名,
columns列名。
查询指定表名StormGroup_member下的列名信息:
http://124.70.22.208:40418/new_list.php?id=-1 union select 1,group_concat(table_name),3,4 from information_schema.tables where table_schema=‘mozhe_Discuz_StormGroup’
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CMmCr6C0-1677744301802)(01-mysql.assets/image-20230226225526145.png)]
查询指定表名StormGroup_member下的列名信息:
http://219.153.49.228:43123/new_list.php?id=-1 union select 1,group_concat(column_name),3,4 from information_schema.columns where table_name=‘StormGroup_member’
前提知识:
TABLES 表:存储表名的
——>关键字段:TABLE_SCHEMA表示表所属的数据库名称;
——>关键字段:TABLE_NAME表示所属的表的名称
–》information.schema.columns
MySQL的information_schema库中COLUMNS表,里面记录了mysql所有库中所有表的字段信息,该表主要信息如下:
users是用户名
TABLE_SCHEMA | nvarchar(128) | 表所有者。 |
---|---|---|
TABLE_NAME | nvarchar(128) | 表名。 |
COLUMN_NAME | nvarchar(128) | 列名。 |
ORDINAL_POSITION | smallint | 列标识号。 |
COLUMN_DEFAULT | nvarchar(4000) | 列的默认值 |
IS_NULLABLE | varchar(3) | 列的为空性。如果列允许 NULL,那么该列返回 YES。否则,返回 NO。 |
nvarchar(128) | 表所有者。 |
| -------------------- | ------------------ | ------------------------------------------------------------ |
| TABLE_NAME | nvarchar(128) | 表名。 |
| COLUMN_NAME | nvarchar(128) | 列名。 |
| ORDINAL_POSITION | smallint | 列标识号。 |
| COLUMN_DEFAULT | nvarchar(4000) | 列的默认值 |
| IS_NULLABLE | varchar(3) | 列的为空性。如果列允许 NULL,那么该列返回 YES。否则,返回 NO。 |