您的位置: 首页  >  文章  >  面试准备——MySql基础篇

面试准备——MySql基础篇

分类: 文章 • 2024-01-13 08:04:22

Mac终端配置环境变量后需要输入source ~/.bash_profile再mysql -u root -p？？

面试准备——MySql基础篇

在MySQL Client中输入的SQL语句通过TCP连接发送到MySQL Server，默认端口号是3306，即如果发送到本机MySQL Server，地址就是127.0.0.1:3306，也可以只安装MySQL Client，然后连接到远程MySQL Server。假设远程MySQL Server的IP地址是10.0.1.99，那么就使用-h指定IP或域名：mysql -h 10.0.1.99 -u root -p

SQL对大小写不敏感，关系型SQL的关系体现在表的一对多、多对一、一对一，一对多表 — 中间表 — 一对多表 形成多对多关系

通过主键记录唯一区别不同的记录，通常不使用与业务相关的字段，主键不允许为NULL，通常使用自增类型，联合主键允许一列有重复，只要不是所有主键都重复

在表中通过某个字段，可以把数据与另一张表关联起来，这种列称为外键，外键通过定义外键约束实现，外键约束保证无法插入无效数据

定义外键约束

ALTER TABLE students  #ALTER TABLE用于在表students中添加删除修改列

ADD CONSTRAINT fk_class_id  #ADD CONSTRAINT用于添加外键约束

FOREIGN KEY(class_id)  #FOREIGN KEY定义约束字段

REFERENCES classes(id);  #这个外键关联到classes表的主键id

删除外键约束

ALTER TABLE students

DROP FOREIGN KEY fk_class_id;  #DROP FOREIGN KEY删除约束

索引是关系型数据库中对某一列或者多个列的值进行预排序的数据结构，加快查询速度，索引的效率取决于索引列的值是否散列，可以对一张表创建多个索引，缺点是插入删除修改记录时，需要同时修改索引，因此索引越多，插入更新和删除记录的速度就越慢

创建索引

ALTER TABLE students

ADD INDEX idx_score(score);  #索引如果是多列，可以在括号里依次写上

某列字段具有唯一性约束，就可以创建唯一索引，唯一索引列没有索引，但仍然具有唯一性保证

创建唯一索引

ALTER TABLE students

ADD UNIQUE INDEX uni_name(name); 

查询数据

SELECT id , name FROM students;

SELECT * FROM students;

条件查询&投影查询

SELECT * FROM students WHERE score >=80;

SELECT name FROM students WHERE score >=80 AND class_id=1;  #投影查询

SELECT * FROM students WHERE score >=80 OR class_id=1;

SELECT name,id,class_id FROM students WHERE score >=80 NOT class_id=1;  #投影查询

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

面试准备——MySql基础篇

投影查询结果可以给每一列起个别名，这样结果集的列名就可以与原表的列名不同。语法是SELECT 列1 别名1, 列2 别名2  FROM 表明 WHERE 条件;

ORDER BY 语句排序

SELECT id, name, gender, score FROM students ORDER BY score;  #正序

SELECT id, name, gender, score FROM students ORDER BY score DESC;  #倒序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;  #如果有相同score，再按gender列排序

SELECT id, name, gender, score FROM students WHERE class_id = 1 ORDER BY score DESC;

分页查询LIMIT <M> OFFSET <N>，LIMIT总是设定为pageSize，OFFSET计算公式为pageSize * (pageIndex - 1)

SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 0;  #结果集从0号记录开始，最多取3条

SELECT id, name, gender, score FROM students ORDER BY score DESC LIMIT 3 OFFSET 3;  #结果集从3号记录开始，最多取3条

聚合查询，使用聚合函数对数据表的数据做统计

SELECT COUNT(*) FROM students;  #统计表中一共有多少列，返回值是一个表

SELECT COUNT(*) number FROM students;  #给结果表的字段重命名为number

SELECT COUNT(*) boys FROM students WHERE gender = 'M'; #统计班级中男生的总人数

面试准备——MySql基础篇

SELECT AVG(score) average FROM students WHERE gender =‘M’

分组聚合查询

SELECT COUNT(*) num FROM students GROUP BY class_id;

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

SELECT * FROM students, classes;

连接查询 students表中只存放了class_id没有存放class_name，class_name在表class中

SELECT s.id, s.name, s.class_id, s.gender, s.score FROM students s;

SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score FROM students s INNER JOIN classes c ON s.class_id = c.id;  #INNER JOIN确定需要连接的表，ON确定连接条件

INSERT 添加记录

INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80); #没有列出id是因为id是自增主键

UPDATE 更新记录

UPDATE students SET name='大牛', score=66 WHERE id=1;

UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

UPDATE students SET score=score+10 WHERE score<80;

WHERE没有匹配到任何值时，UPDATE没有报错也没有更新任何值

DELECT 删除记录

DELETE FROM students WHERE id=1;

DELETE FROM students WHERE id>=5 AND id<=7;

WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除

SHOW DATABASES;显示所有数据库

CREAT DATABASE test; 创建一个新数据库

DROP DATABASES test; 删除数据库

USE test; 切换到当前数据库

SHOW TABLES; 列出当前所有表

DESC students; 查询表结构

SHOW CREATE TABLE students; 查看创建表的SQL语句

CREAT TABLE students; 创建表

DROP TABLE students; 删除表

ALTER TABLE students CHANGE COLUMN new VARCHAR(20) NOT NULL;修改表，给表添加一列

EXIT; 退出数据库连接

REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);插入一条新记录但如果记录已经存在，就先删除原记录，再插入新记录INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;插入一条新记录，但如果记录已经存在，就更新该记录

INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);插入一条新记录，如果记录已经存在，就直接忽略

在执行SQL语句的时候，当一系列操作必须全部执行，不能仅执行一部分，这种语句作为一个整体进行操作的功能，数据库软件提供的事务实现

用BEGIN开始一个事务，用COMMIT提交一个事务

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；

对于两个并行执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题，因为并发操作会带来数据的不一致性，因此数据库系统提供了隔离级别来让我们有针对性地选择隔离级别

Read Uncommitted 是隔离最低的一种事务级别，在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读

Read Committed 是不可重复读隔离级别，在该隔离级别下，一个事务可能会遇到不可重复读的问题。不可重复度是指在一个事务内，多次读同一个数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么第一个事务中，两次读取的数据可能不一致

Repeatable Read隔离级别下一个事务可能会遇到幻读的问题。幻读是指在一个事务中，第一次查询某条记录发现没有，但是当试图更新这条不存在的记录时，竟然能成功，并且再次读同一条记录，它就出现了，MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read

Serializable是最严格的隔离级别，在该隔离级别下，所有事务按照次序依次执行，具有最高的安全性

相关推荐