mysql数据库操作命令集合

关系型数据库概念：

Excel可以看做是一张一张的二维表。关系数据库=多张表+各表之间的关系

sql概念
操作数据库里的数据的工具

各个表之间建立联系
关系数据库中，如果一张表要关联其他表，通过对应的列产生了关系。这个关系叫做联结。

数据库管理系统
来管理数据库的计算机软件叫做数据库管理系统

数据库的创建和删除

show databases;  //查看已创建的数据库
create databases test; //创建名为test的数据库
create databases if not exists test; //如果不存在test数据库再创建
create database test default charset utf8mb4; //创建test数据库并指定字符集为utf8mb4
drop database test; //删除test数据库
drop database if not exists test //如果存在test数据就删除
use test2； //使用其他数据库，即切换另外的数据库
select database(); //查看当前使用的是哪个数据库

表操作

create table tb_user(
id int comment '编号',  //comment 为可选项
name varchar(50) comment '姓名',
age int   //注意最后一个不加逗号
) comment '用户表';

#查询表结构
desc tb_uset;  //查询tu_user表的结构
#查询指定表的建表语句
show create table tb_user; //会显示创建表的sql语句，包括注释

修改表名
ALTER TABLE 表名 RENAME TO 新表名;

删除表并重新创建该表
TRUNCATE TABLE 表名

字段操作

字段添加
ALTER TABLE 表名 ADD 字段名 类型(长度) [注释] [约束]; 

字段删除
ALTER TABLE 表名 DROP 字段名；

插入数据

#给指定字段插入数据
INSERT into students(id,`name`,class,gender) VALUES(11,'张三',3,4);
#给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值..);
#批量添加数据
INSERT INTO 表名(字段名1,字段名2, ..) VALUES (值1,值2, ..),(值1,值2..),(值1,值..);
INSERT INTO 表名 VALUES(值1,值...),(值1, 2值...), (值1,值...);

修改数据

UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,...[WHERE  条件];
#示例
update student set name = 'zhangsan' where id = 1; //将id=1的学生姓名修改为zhangsan
注意：如果不加where条件语句，操作的范围为整个表

删除数据

DELETE FROM 表名 [WHERE 条件];
注意：delete语句不加条件会删除整张表
         delete语句不能删除某一个字段的值（可以使用update）

查询数据

#查询多个字段
SELECT 字段1, 字段2,字段3... FROM 表名;
SELECT * FROM 表名;
#设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ... FROM 表名;
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

#条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
#条件
<>或!= 不等于
BETWEEN .. AND... 在某个范围之内(含最小、 最大值) 在in之后的列表中的值，多选一
LIKE占位符         模糊匹配(匹配单个字符，%匹配任意个字符)
IS NULL           是NULL
#查询用户表名字两个字的用户信息
select * from tb_user where name like '__';
#查询用户表id以1结尾的用户信息
select * from tb_user where id like '%1';

#聚合查询
将一列数据作为一个整体,进行纵向计算。
#常见聚合函数
count 统计数量。
max   最大值
min   最小值
avg   平均值
sum   求和
#语法 
SELECT 聚合函数(字段列表) FROM 表名;

#分组查询
SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY 分组字段名 [ HAVING分组后过滤条件];
#where 与having区别
1执行时机不同: where是分组之前进行过滤，不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
2判断条件不同: where不能对聚合函数进行判断，而having可以。
注意
执行顺序: where >聚合函数> having.
分组之后，查询的字段-般为聚合函数和分组字段，查询其他字段无任何意义。
#查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
seLect workaddress, count(*) address_ count from emp where age < 45 group by workaddress having
3.查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
seLect workaddress, count(*) address_ count from emp where age
> 45 group by workaddress having address_ count >= 3;

DML实例

#修改表
INSERT into students(id,`name`,class,gender) VALUES(12,'张三',3,4);
#将所有人的姓名都改为李四，谨慎操作
UPDATE students SET `name` = '李四';
#删除表中所有数据
DELETE FROM students;

#查询students表中所有人姓名并给name起中文别名

SELECT `name` AS '姓名' FROM students;

#查询students表中所有人姓名并给name起中文别名（不重复显示相同姓名）

SELECT DISTINCT `name` AS '姓名' FROM students;

数据类型

INT整型 BIGINT长整型 REAL 浮点型 DOUBLE浮点型
DECIMAL(M,N)高精度小数例：DECIMAL(20,10)表示一共20位，其中小数10位
CHAR(N) 定长字符串存储指定长度的字符串，例CHAR(100)总是存储100个字符的字符串
VARCHAR(N) 变长字符串存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN 布尔类型存储True或者False
DATE 日期类型存储日期，例如，2018-06-22
TIME 时间类型存储时间，例如，12:20:59
DATETIME 日期和时间类型存储日期+时间，例如，2018-06-22 12:20:59

　　主键：关系表中任意两条记录不能重复。通过某个字段唯一区分出不同的记录，这个字段被称为主键
　　联合主键：两个或更多的字段都设置为主键，这种主键被称为联合主键。对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。

　　外键：

#以学生表为例：
id class_id name    other columns...
1    1     小明    ...
2    1     小红    ...
5    2     小白    ...
在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键通过定义外键约束实现：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
要删除一个外键约束，也是通过ALTER TABLE实现的：
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;
注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

索引：关系数据库中对某一列或多个列的值进行预排序的数据结构

#对score列创建索引
ALTER TABLE students
ADD INDEX idx_score (score);
#索引有多列的情况
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

#列添加一个唯一索引
ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

#只对某一列添加一个唯一约束而不创建唯一索引
ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查询数据

#查询数据库表的数据
SELECT 字段1，字段2，字段3 ... FROM 表名；//查询所选的字段
SELECT * FROM <表名> //查询所有字段

#设置别名
SELECT 字段1 [AS 别名1],字段2[AS 别名2] ... FROM 表名; // as可以省略
例：select workaddress '工作地址' from emp；
#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名；
#条件查询的语法
SELECT * FROM <表名> WHERE <条件表达式>
#-- 按AND条件查询students成绩大于80性别为男:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';
#-- 按OR条件查询students:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';
#-- 按NOT条件查询students:
SELECT * FROM students WHERE NOT class_id = 2;
#-- 按多个条件查询students:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
#-- 查询姓名为两个字的学生（lile表示占位符，'_'匹配单个字符，%匹配任意字符）
select * from emp where name like '__';
#-- 查询年龄等于18或20或22的学生
select * from students where age = 18 or age = 20 or age = 22；
select * from students where age in(18,20,22); //和上面语句等价

#投影查询:SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。
SELECT id, score, name FROM students;
#-- 使用投影查询，并将列名score重命名points：
SELECT id, score points, name FROM students;
#-- 使用投影查询+WHERE条件：
SELECT id, score points, name FROM students WHERE gender = 'M';

#排序 ORDER BY，根据某个条件将查询结果排序
SELECT id, name, gender, score FROM students ORDER BY score;
#倒序排序，-- 按score从高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;
#-- 按score, gender排序（成绩相同时女性在前）:
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender DESC;
#-- 带WHERE条件的ORDER BY:
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

#分页查询LIMIT <N> OFFSET <M>
#-- 一页三条数据，从第一条开始(索引是从零开始的)
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

#聚合查询
#使用聚合查询students表一共有多少条记录
SELECT COUNT(*) FROM students;
#使用聚合查询并设置结果集的列名为num:
SELECT COUNT(*) num FROM students;
#-- 使用聚合查询并设置WHERE条件:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
#使用聚合查询计算男生平均成绩:
SELECT AVG(score) average FROM students WHERE gender = 'M';
#查询年龄最小的学生
select min(age) from students;
#查询二班学生年龄和
select sum(age) from students where class = 2;

#分组查询
#语法：SELECT 字段列表 FROM 表明 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];
#根据性别分组，统计男生和女生人数
select gender,count(*) from emp group by gender; //不加gender的话不会在统计的人数前加‘男’，‘女’；
#查询年龄小于45的员工，并根据工作地址分组，获取学生数大于3的城市
select workaddress, count(*) address_count from students where age < 45 group by workaddress having address_count >= 3; //address_count是count(*)的别名

sql语句执行顺序 1FROM 2WHERE 3GROUP BY 4HAVING 5SELECT 6 ORDER BY 7LIMIT

DCL(数据控制语言)

函数
#创建用户tom,只能够在当前主机localhost访问；
create user 'tom'@'localhost' identified by '123456';
#创建用户bob，可以在任意主机访问
create user ‘bob’@‘%’ identified by ‘123456’；
#删除用户
drop user '用户名'@'主机名';

#外键约束
#创建表时添加外键
CREATE TABLE 表名(
字段名数据类型,
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名）REFERENCES 主表（主表列名）//主表列名不是主键无法添加外键
）；
#创建表后添加外键
ALTEE TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表（主表列名);
#删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

事务（Transaction）指一个操作，由多个步骤组成，要么全部成功，要么全部失败。数据库事务（Database Transaction）是指对数据库的一系列操作组成的逻辑工作单元。

脏读（Dirty Read）
读取未提交数据。
A 事务读取 B 事务尚未提交的数据，此时如果 B 事务发生错误并执行回滚操作，那么 A 事务读取到的数据就是脏数据。
不可重复读（Non-repeatable Read）
前后多次读取，数据内容不一致。
A 事务在 B 事务开始前读和 B 事务结束后读的数据不一样，因为数据被事务 B 给修改了。

幻读（Phantom Read）
一个行出现在查询结果集中，但不在较早查询的结果集中。

事务 A 在读取某个范围内的记录时，事务 B 在该范围内插入了新记录，事务 A 再次读取该范围内的记录时，会产生幻行。幻读比不可重复读取更难防范，因为锁定第一个查询结果集中的所有行并不能阻止导致幻像出现的更改。
更新丢失（Update Lost）
指更新结果被其他事务覆盖。两个事务同时读取相同数据并分别修改后，一个事务的修改覆盖了另一个事务的修改。这是因为系统没有执行任何锁操作，因此并发事务没有被隔离开来。
第一类更新丢失（回滚丢失）。
比如 A 事务对某一列 +1，B 事务对某一列 +2。A 事务事务提交后，B 事务回滚了，导致 A 事务更新丢失。
第二类更新丢失（提交丢失）。
比如 A 事务对某一列 +1，B 事务对某一列 +2，A B 事务执行完成后正常预期结果是某一列值被 +3，但是 B 事务的结果覆盖了 A 事务，导致结果只被 +2，A 事务的更新丢失了。

MySQL 可以通过以下办法避免更新丢失。
提升隔离级别至串行化（Serializable）
使用乐观锁，比如版本号的 CAS（Compare And Swap）
使用悲观锁，比如排他锁（X 锁）SELECT xxx FOR UPDATE

数据库事务的使用
把转账操作作为一个显式事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

主动让事务失败。可以用 ROLLBACK 回滚事务，整个事务会失败。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

posted @ 2023-09-07 14:50 ddup123 阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

ddrm

mysql数据库操作命令集合

公告